sql 子查询优化

发布网友发布时间：2022-05-02 01:41

共2个回答

懂视网时间：2022-05-02 06:02

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 id >0 limit 20;

视图方式：
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 解决。

本文出自 “晴空” 博客，谢绝转载！

SQL优化-子查询&case&limit

标签：子查询优化

热心网友时间：2022-05-02 03:10

加并行可以不：/*parallel(+8)*/
或者你在外面再嵌套一个，里面的那2个子查询和主查询用union all 并列跑