SQL优化-子查询&case&limit

榕叶

　　load 导数据.notesdxtdb 数据库    total_time  475.60秒。 监控服务：仓颉

select t_.*,

　　a.name acquirer_name,
　　m.merchant_name,
　　am.merchant_name acq_merchant_name,
　　ag.name agency_name
　　from
　　(
　　select t.* ,
　　c.cardbin_name,
　　c.issuer_name cardbin_issuer_name,
　　CASE c.card_type
　　WHEN 'debit' THEN 'XXX'
　　WHEN 'prepaid' THEN 'XXX'
　　WHEN 'credit' THEN 'XXX'
　　WHEN 'semiCredit' THEN 'XXX'
　　END 'card_type'
　　from trans_history t
　　join dict_cardbin c on t.cardbin_id = c.id
　　where 1=1  order by t.id desc ) t_
　　left join cm_merchant m on t_.merchant_id=m.id
　　left join acquirer a on t_.acquirer_id = a.id
　　left join acq_merchant am on t_.acq_merchant_id = am.id
　　left join agency ag on m.agency_id = ag.id
　　order by t_.id desc  limit 0, 20;
　　表：
　　trans_history t
　　子查询的 结果集是  t_
　　dict_cardbin c
　　cm_merchant m
　　acquirer a
　　acq_merchant am
　　agency ag
　　关联：
　　c on t.cardbin_id = c.id
　　t_.merchant_id=m.id
　　t_.acquirer_id = a.id
　　t_.acq_merchant_id = am.id
　　m.agency_id = ag.id
　　子查询结果集要：
　　c.cardbin_name,
　　c.issuer_name
　　cardbin_issuer_name,
　　外部结果要：
　　t_.*,
　　c.cardbin_name,
　　c.issuer_name
　　t.cardbin_issuer_name,
　　a.name acquirer_name,
　　m.merchant_name,
　　am.merchant_name
　　t.acq_merchant_name,
　　ag.name agency_name
　　以下SQL未经验证，全凭个人经验 对其SQL改写。
　　（刚来新公司，还没有权限登录 跟查验表结构 执行计划。）
　　目测 第一步设计的6个表的left join  以及判断
　　第二步  如这个查询业务功能上来说 属于频繁性的，需要设计视图方式解决。
　　视图采用 CASCADED 方式。
　　查询需要：
　　trans_history t  全字段数据；   其他表 都个需要某几个字段数据。
　　select t.*,c.cardbin_name,c.issuer_name,t.cardbin_issuer_name,a.name acquirer_name,m.merchant_name,am.merchant_name,t.agency_name from ((((trans_history t join dict_cardbin c on t.cardbin_id = c.id ) left join cm_merchant m on t.merchant_id=m.id) left join acquirer a on t.acquirer_id = a.id ) left join acq_merchant am on t.acq_merchant_id = am.id ) left join agency ag on m.agency_id = ag.id order by t.id desc  limit 0, 20;
　　card_type 列要做运算。这里 应该存 enum  0；1；2；3 这样的字段，逻辑端拿到数值，前端做渲染。  否则 这个CASE 没办法在第一个版本当中优化掉。
　　card_type 字段，我这里就简写了。没有加入case 判断。
　　select t.*,c.cardbin_name,c.issuer_name,a.name,acquirer_name,m.merchant_name,am.merchant_name,t.agency_name from ((((trans_history t join dict_cardbin c on t.cardbin_id = c.id ) left join cm_merchant m on t.merchant_id=m.id) left join acquirer a on t.acquirer_id = a.id ) left join acq_merchant am on t.acq_merchant_id = am.id ) left join agency ag on m.agency_id = ag.id order by t.id desc  limit 0, 20;

　　select t.*,c.cardbin_name,c.issuer_name,t.cardbin_issuer_name,c.card_type,a.name acquirer_name,m.merchant_name,am.merchant_name,t.agency_name from ((((trans_history t join dict_cardbin c on t.cardbin_id = c.id ) left join cm_merchant m on t_.merchant_id=m.id) left join acquirer a on t_.acquirer_id = a.id ) left join acq_merchant am on t_.acq_merchant_id = am.id ) left join agency ag on m.agency_id = ag.id order by t.id desc  where>　　视图方式：
　　LOCAL只要满足本视图的条件就可以更新；
　　CASCADED则必须满足所有针对该视图的所有视图的条件才可以更新
　　create view card as select t.*,c.cardbin_name,c.issuer_name,t.cardbin_issuer_name,a.name acquirer_name,m.merchant_name,am.merchant_name,t.agency_name from ((((trans_history t join dict_cardbin c on t.cardbin_id = c.id ) left join cm_merchant m on t_.merchant_id=m.id) left join acquirer a on t_.acquirer_id = a.id ) left join acq_merchant am on t_.acq_merchant_id = am.id ) left join agency ag on m.agency_id = ag.id with local check option;
　　解决哪些问题：
　　1 解决了 表结构类型的问题。使用了enum  并利用前端做判断展示出来。
　　2 解决了子查询 用left join方式替换。
　　3 解决了limit 查询效率差的问题，用where 解决。