SQL优化-子查询&case&limit
load 导数据.notesdxtdb 数据库 total_time475.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=1order 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 desclimit 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 desclimit 0, 20;
card_type 列要做运算。这里 应该存 enum0；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 desclimit 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 descwhere> 视图方式：
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 解决。
页:
[1]