MySQL的经典SQL优化13例（更新到2024年5月21日）

下列优化的SQL案例，区别于平常加SQL索引的方法优化，大部分都是通过改写SQL语句方法优化，都是日常优化线上慢SQL的实际案例，有比较好的代表性（思路和方法），也是对自己这些年来做SQL优化的总结，对后面优化SQL有很好的提示和借鉴，案例会持续更新中。

说明：

最近优化慢SQL，执行计划错误和OR条件查询优化实战经验，提供优化SQL思路和方法：

1，利用exists来优化SQL（利用exists减少回表查询次数和确定驱动表）

2，OR语句优化（OR条件，字段有索引，无法使用索引的）

案例1：

SELECT sum( CASE WHEN ols.check_status NOT IN ( 2, 3, 4 ) THEN 1 WHEN ols.check_status IS NULL THEN 1 END ) AS 
lesson_num, sum( ols.check_status = 1 ) AS attend_num FROM  ol_live_student_time_chapter_list olstcl LEFT JOIN 
ol_live_student ols ON olstcl.live_student_id = ols.id inner JOIN ol_user u ON u.id = olstcl.user_id
WHERE  olstcl.class_course_type = '2'  AND olstcl.attend_status <> 4
 AND olstcl.start_time >= 1621526400  AND olstcl.start_time < 1621612799   AND u.is_test_user = '0'  AND 
counselor_id = '796' AND u.pay_status = '0'  AND u.type = '1'  AND u.STATUS = '1';

执行超过2.2秒，执行计划如下，从执行计划看：返回的rows也很少。key里用到了索引，按理说这个SQL是最优的SQL，没有优化的空间

但实际查看ol_user表，表有430万条数据，核心还是查询ol_user表的数据，如果能去掉ol_user表查询，就更好，如果不能去掉，能有其他优化方法：

通过仔细分析3个表，各个条件查询查出的数据：

select count(*) ol_live_student_time_chapter_list olstcl where olstcl.start_time>= 1621526400 AND 
olstcl.start_time< 1621612799 and  olstcl.class_course_type = '2'  AND olstcl.attend_status <> 4

发现ol_live_student_time_chapter_list 查询，这个时间查出只有8000多条。而查询oL_user表查询：

select count(*) from ol_user u where u.is_test_user = '0' AND counselor_id = '796' AND u.pay_status = '0'
 AND u.type = '1' AND u.STATUS = '1'

查出300多条，但仔细计划显示：Using intersect(pay_status_user,counselor_id,type,is_test_user); Using where; Using index。的确用很多索引，

这样看，表面看执行计划没问题，但实际看一下，这个ol_user表查询，其实只是一个条件，ol_user没有字段在select查出显示，从某种意义上讲，只需要查出符合条件的就可以，如果满足其中

一个就返回效率是否更高，不需要每个条件都查完，这样我们完全可以用EXISTS 替换inner join 来提高查询效率，SQL修改如下：

SELECT sum( CASE WHEN ols.check_status NOT IN ( 2, 3, 4 ) THEN 1 WHEN ols.check_status IS NULL THEN 1 END ) AS
 lesson_num, sum( ols.check_status = 1 ) AS attend_num FROM  ol_live_student_time_chapter_list olstcl  LEFT JOIN 
ol_live_student ols ON olstcl.live_student_id = ols.id WHERE olstcl.class_course_type = '2' AND 
olstcl.attend_status <> 4  AND olstcl.start_time >= 1621526400 AND olstcl.start_time < 1621612799  and 
EXISTS (select 1 from ol_user u  where u.is_test_user = '0'  AND u.counselor_id 
= '796'  AND u.pay_status = '0'  AND u.type = '1' AND u.STATUS = '1' and u.id = olstcl.user_id) ;

使用优化的SQL，时间只有0.9秒左右

优化原理：利用 EXISTS 来替换 inner join，减少查询循环回表次数，提高效率。

案例2：

select count(distinct u.id) from ol_user u left join ol_user_related_info uri on u.id = uri.user_id where 
u.type=1 and u.status =1 and u.pay_status=0 and uri.recovery_time_no_pay>=1622649600 and 
uri.recovery_time_no_pay<1622736000 and u.counselor_id in  (403);

执行超过1.3秒，执行计划如下：

从这里看：查出ol_user的u.id，distinct汇总，而ol_user_related_info 是left join，仔细看逻辑，虽然是left join，但有uri.recovery_time_no_pay条件，就这个left join 可以改成inner join

可以改成：

select count(distinct uri.user_id) from ol_user u inner join ol_user_related_info uri on u.id = uri.user_id 
where u.type=1 and u.status =1and u.pay_status=0 and uri.recovery_time_no_pay>=1622649600 and 
uri.recovery_time_no_pay<1622736000 and u.counselor_id in  (403);

修改完，2者的执行效率，查不多，改了也没优化，如上面的优化，我们知道ol_user表的数据量太大，要减少回表的查询，

该SQL就可以改成exists查询，如下：

select count(distinct uri.user_id) from  ol_user_related_info uri   where   uri.recovery_time_no_pay>=1622649600 
and uri.recovery_time_no_pay<1622736000 and exists (select 1 from ol_user u where u.id = uri.user_id and 
u.type=1 and u.status =1 and u.pay_status=0 and u.counselor_id in  (403) )

改成这样，SQL执行只需0.6秒左右，未加索引，改写SQL，left join修改成 inner join，再改成exists查询。

案例3

SELECT f.flow_type,count(1) as num FROM `ol_admin_flow` `f` LEFT JOIN `ol_admin_flow_node` `n` ON `f`.`flowid`=
`n`.`flowid` WHERE( `n`.`adminid` = 7417 OR `f`.`post_adminid` = 7417 ) GROUP BY `f`.`flow_type`;

执行超过2.5秒，post_adminid加索引，SQL也用不到索引，将or改写成 union all写法

select flow_type,sum(num) num from (SELECT f.flow_type,1 num FROM ol_admin_flow f LEFT JOIN ol_admin_flow_node n 
ON f.flowid=n.flowid WHERE f.post_adminid = 7418 union all SELECT f.flow_type,1 num FROM ol_admin_flow f LEFT 
JOIN ol_admin_flow_node n ON f.flowid=n.flowid WHERE n.adminid = 7418 ) a group by flow_type

改成这样写法后。SQL效率大幅提升，查询只需0.3秒

案例4

SELECT  au.GROUP group_id,sum(IF  (o.STATUS = 3  AND o.attach_pay_time BETWEEN 1625068800  AND 1627747199 AND
 o.rebate_time BETWEEN 1625068800  AND 1627747199, 0, oap.price ) ) real_renewal_money, sum(IF ( o.STATUS = 3
AND o.attach_pay_time BETWEEN 1625068800  AND 1627747199   AND o.rebate_time BETWEEN 1625068800  AND 
1627747199,    0,    oap.order_count )  ) renewal_num FROM  `ol_order_attach_pay` `oap` INNER JOIN 
`ol_order` `o` IGNORE INDEX ( idx_pay_time_type ) ON `o`.`order_number` = `oap`.`order_number` AND `o`.`status` 
IN ( 1, 3 )  AND ( `o`.`attach_pay_time` >= 1625068800 AND `o`.`attach_pay_time` < 1627055999 )  AND 
`o`.`package_course_type` = 1    INNER JOIN `online`.`ol_admin_user` `au` ON `oap`.`adminid` = `au`.`id` WHERE
    `oap`.`status` = 1     AND `oap`.`adminid` IN (    728,    818,    870,    1497,    2019,    2021,
    2465,    2557,    2679,    3228,    3231,    3916,    3419,    3423,    3412,    3417,    3500,    4165, 
   477,    4163,    1030,    562    ) 
GROUP BY    `au`.`group`     LIMIT 100;

该SQL线上执行需要18秒以上，执行计划如下：

从执行计划看，慢的地方在查询ol_order的索引，有76万条。表中有以下索引：

KEY `attach_pay_time` (`attach_pay_time`) KEY `package_course_type` (`package_course_type`)，表中有20多个索引，太多，真不想再建索引。但不加索引无法优化，用 force index 索引优化效果又不太好，一开始研发建立复合索引：KEY `idx_pay_time_type` (`attach_pay_time`,`package_course_type`), 按理应该优化，但实际优化效果不好，后续将复合索引改成：

KEY `idx_pay_time_type` (`package_course_type`,`attach_pay_time`),

加好后，执行计划如下，查询在0.5秒：

案例5

利用id主键来排序优化

select  q.q_id, q.q_title from   bbs_question q where     q.app_id = 'xtep'  and q.delete_flag = 0   and 
( q.ban_flag is null or q.ban_flag = 0 )   
and ( q.q_title like '%圆领%' or q.q_context like '%圆领%' )  order by   q.create_time desc   limit 10

优化说明：优化前因有OR和like查询，不好再建索引优化，查询需要2秒以上，因创建时间和主键自增长二者排序数据一致，order by q.create_time desc 改成 order by q.q_id desc ，SQL中create_time排序改成利用主键排序，优化后只需0.4秒。

案例6

利用全文索引优化

update sys_org_shop set path = REPLACE(path, '1806874642_1,6006991214_3,4006896323_4,4006910831_4,6006945801_6,
5007352247_5,', '1806874642_1,6006991214_3,4006896323_4,4006910831_4,6006945737_6,5007352247_5,'),operation='
1658136067459_method:SysOrgEnterprise.editOrgType'  where path like '%1806874642_1,6006991214_3,4006896323_4,
4006910831_4,6006945801_6,5007352247_5,%' and app_id='dep'

该语句执行平均1.9秒，查询更新path字段，在path建索引，效果不大，而且必须使用前后的%，效率很低，只能在path建立全文索引，运MySQL的版本是

5.6的，MySQL5.6全文索引不支持中文，但这个字段只支持数字和“，”。对优化该SQL足够，like模糊查询字段建立全文索引优化。

alter table sys_org_shop add FULLTEXT idx_path(path)

优化后SQL如下；注意AGAINST的挂号里面的有一对""，否则全文查询的数据不对

update sys_org_shop set path = REPLACE(path, '1806874642_1,6006991214_3,4006896323_4,4006910831_4,6006945801_6,
5007352247_5,', '1806874642_1,6006991214_3,4006896323_4,
4006910831_4,6006945737_6,5007352247_5,'),
operation='1658136067459_method:SysOrgEnterprise.editOrgType' where 
MATCH(`path`) AGAINST ('"1806874642_1,6006991214_3,4006896323_4,4006910831_4,6006945801_6,5007352247_5,"') 
and app_id='dep'

案例7

利用普通索引优化like模糊查询

select t.id,t.title,t.text_info,t.create_user,t.create_time,t.app_id,t.flag,u.name from sys_img_text t,sys_user
u where t.create_user=u.sys_user_id  and t.app_id = '3S'  and t.title like '%27C01010%'    and t.flag = 1 order
by t.create_time desc limit 5

该SQL执行计划：

查询先走的create_time排序索引，再查询数据，执行起来要38秒+，很慢，可以在title字段建全文索引查询，但MySQL版本是5.6，

不支持中文，建了如查询中文的就查不出来，这里有app_id 有索引，可以用这个索引做一下过滤，过滤后在title字段模糊查询会快很多。

修改SQL 如下：

select t.id,t.title,t.text_info,t.create_user,t.create_time,t.app_id,t.flag,u.name from sys_img_text t 
force index(idx_app),sys_user u where t.create_user=u.sys_user_id  and t.app_id = '3S'  and t.title 
like '%27C01010%' and t.flag = 1 orderby t.create_time desc limit 5

查询先过滤一层数据，在无全文索引情况下，增加force index(idx_app)强制索引查询，提高高查询效率，执行只需要0.011秒，

有几千倍的性能提升。

案例8

SQL在MySQL5.5执行正常，到了MySQL5.6和5.7执行一直很慢，执行查不出结果：（2023年3月31日补充案例）

SELECT
    count( DISTINCT t1.seafarer_id ) AS hisNum,
    count( DISTINCT t2.seafarer_id ) AS jyNum 
FROM
    cy_cy_qf t1
    LEFT JOIN (
    SELECT
        t3.comp_id,
        t3.seafarer_id,
        t4.const_key,
        t4.const_value 
    FROM
        gt_cp t4
        LEFT JOIN cy_cy_mf t3 ON t3.comp_id = t4.comp_id 
        AND t3.memo_kind_id = t4.const_key 
    WHERE
        t3.comp_id = 'COMP2967' 
        AND t3.delete_flag = '0' 
    ) t2 ON t1.comp_id = t2.comp_id 
    AND t1.seafarer_id = t2.seafarer_id 
    AND t2.const_value = '就医/看诊' 
WHERE
    t1.delete_flag = '0' 
    AND t1.comp_id = 'COMP2967'

SQL的执行计划

从这看出gt_cp t4，这个表扫描的行数很多，通过t1.seafarer_id = t2.seafarer_id 查询，comp_id的值都相等，无过滤性。优化能从gt_cp优化，同时如果distinct t2.seafarer_id查出的数量很多，如果有很多重复的数据，在关联嵌套循环时，要多次循环，可以考虑尽量把t2的子循环数据减少，同时减少seafarer_id的数，修改后SQL如下：

select  count(distinct t1.seafarer_id) as hisNum,
        count(distinct t2.seafarer_id) as jyNum
        from
            cy_cy_qf t1
        left join 
           (select   (distinct t3.seafarer_id)   seafarer_id
              from   gt_cp t4
              left join cy_cy_mf t3 
              on  t3.comp_id = t4.comp_id and 
                  t3.memo_kind_id = t4.const_key
              where
                  t3.comp_id = 'COMP2967' and
                  t3.delete_flag = '0' and t4.const_value = '就医/看诊') t2
        on t1.seafarer_id = t2.seafarer_id                 
        where 
            t1.delete_flag = '0' and
            t1.comp_id = 'COMP2967'

SQL修改如下后，执行从无法查出数据降到只需0.2秒，同时没有建任何索引，性能大幅提升。

案例9

SQL执行一直很慢，需要6秒才能有结果，SQL如下：（2023年4月25日补充案例）

SELECT
    m.id,
    m.comp_id,
    m.vessel_id,
    m.dept_id,
    m.vessel_name,
    m.device_id,
    d.device_name,
    m.order_type,
    m.plan_date,
    t.execution_content,
    t.plan_content,
    t.sp_out_store_id spOutStoreId,
    t.ma_out_store_id maOutStoreId,
    d.parent_device_id,
    d.parent_device_name,
    d.device_level_flag 
FROM
    mw_view m
    LEFT JOIN mw_order t ON m.id = t.id 
    AND m.comp_id = t.comp_id
    LEFT JOIN md_info d ON m.device_id = d.id 
WHERE
    1 = 1 
    AND m.comp_id = 'e98aab51fcd940f3b7aee670a2ea4207' 
    AND (
        m.vessel_id = '570ca38b05344c5daa30d00fccae8512' 
        OR m.vessel_id = 'd66417caacdd46a4918619eee0239d50' 
        OR m.vessel_id = '5a67345cb19c4e449243423285c60550' 
        OR m.vessel_id = 'e34a6304d1cc4fbd962cf852a04bcba3' 
        OR m.vessel_id = '35c9f507cd9645338c45588f00f24b6f' 
        OR m.vessel_id = 'f668e782b2b14e58b5346998d9a3be75' 
        OR m.vessel_id = '455393531f28411c86eaab071685664b' 
        OR m.vessel_id = 'cf5b84600d38494aa97c1824666d2eca' 
        OR m.vessel_id = '5156d56d648f471eb5ac4173db63842c' 
        OR m.vessel_id = 'ee5d5d871f6045088c5b5817193d5962' 
        OR m.vessel_id = '2fe2b46255ca4cad855f2476af496d76' 
        OR m.vessel_id = '0aa93557f6794c13ab14a4a432c5b7ed' 
        OR m.vessel_id = 'a2d587d2247d4426bec020776226f827' 
        OR m.vessel_id = '18437c78e58e4768bcec849f0b7bc8c4' 
        OR m.vessel_id = '5415db21fa7e47629d6563885aea92b5' 
        OR m.vessel_id = '6c534cd5ebfd42159c3a852c078ccc99' 
        OR m.vessel_id = 'e2d6568bfd614f5a92d2c25c13d56c02' 
        OR m.vessel_id = 'e87f3c7b33de4779a8bd5ad4c9fab00c' 
        OR m.vessel_id = '7114381f6bf2467dac1cb26d4a56ca71' 
        OR m.vessel_id = '48af2c42c3284c76b88cf2f57d34dc35' 
        OR m.vessel_id = 'b2fd6818be5346399a8db6785db1b20e' 
        OR m.vessel_id = '1d11b69b76224b5cbef8dfc0f5d37dda' 
        OR m.vessel_id = 'dd96e7b0658043889b67378552f91950' 
        OR m.vessel_id = '6ede3b68b0aa4aed9f54c90162701a80' 
        OR m.vessel_id = 'bda55c44da6f4b28a79e0eb2451afe7b' 
        OR m.vessel_id = '561d8e443e234507b72177fd65e947d5' 
        OR m.vessel_id = '5d52046476164e84af8998496c2d1227' 
    ) 
    AND ( m.order_type = 'delay' OR m.order_type = 'warn' ) 
    AND (
        m.status_flag = '00' 
        OR m.status_flag = '55' 
        OR m.status_flag = '45' 
        OR m.status_flag = '35' 
        OR m.status_flag = '07' 
        OR m.status_flag = '50' 
        OR m.status_flag = '40' 
        OR m.status_flag = '30' 
    ) 
ORDER BY
    m.order_type,
    m.vessel_name,
    m.plan_date 
    LIMIT 0,
    10;

查看执行计划，表中加了 idx_cvs(comp_id,vessel_id,status_flag)索引也没有用，特别vessel_id 是in太多数据，和业务开发聊，能否减少vessel_id 值，回答是不能减少，测试发现去掉一半的vessel_id 值，就很快，查询就只需要查询0.2秒内，那如果不减少值，怎么才能提高效率，有其他办法？

后来考虑是否把这个vessel_id 的值，分成2个SQL再Union all 起来，这样满足程序逻辑，又提高效率，测试的确可以，修改的SQL如下，SQL从以前的6秒降到0.2秒左右：

select * from (
SELECT
    m.id,
    m.comp_id,
    m.vessel_id,
    m.dept_id,
    m.vessel_name,
    m.device_id,
    d.device_name,
    m.order_type,
    m.plan_date,
    t.execution_content,
    t.plan_content,
    t.sp_out_store_id spOutStoreId,
    t.ma_out_store_id maOutStoreId,
    d.parent_device_id,
    d.parent_device_name,
    d.device_level_flag 
FROM
    mw_view m  
    LEFT JOIN mw_order t ON m.id = t.id 
    AND m.comp_id = t.comp_id
    LEFT JOIN md_info d ON m.device_id = d.id 
WHERE
    1 = 1 
    AND m.comp_id = 'e98aab51fcd940f3b7aee670a2ea4207' 
    AND (
        m.vessel_id in('570ca38b05344c5daa30d00fccae8512' ,
     'd66417caacdd46a4918619eee0239d50' ,
      '5a67345cb19c4e449243423285c60550' ,
     'e34a6304d1cc4fbd962cf852a04bcba3' ,
     '35c9f507cd9645338c45588f00f24b6f' ,
     'f668e782b2b14e58b5346998d9a3be75' ,
     '455393531f28411c86eaab071685664b' ,
     'cf5b84600d38494aa97c1824666d2eca' ,
      '5156d56d648f471eb5ac4173db63842c' ,
     'ee5d5d871f6045088c5b5817193d5962' ,
     '2fe2b46255ca4cad855f2476af496d76' ,
     '0aa93557f6794c13ab14a4a432c5b7ed' ,
     'a2d587d2247d4426bec020776226f827' ,
     '18437c78e58e4768bcec849f0b7bc8c4' 
     )
    ) 
    AND ( m.order_type = 'delay' OR m.order_type = 'warn' ) 
    AND (
    m.status_flag = '00' 
    OR m.status_flag = '55' 
    OR m.status_flag = '45' 
    OR m.status_flag = '35' 
    OR m.status_flag = '07' 
    OR m.status_flag = '50' 
    OR m.status_flag = '40' 
    OR m.status_flag = '30' 
    ) 
union all 
    SELECT
    m.id,
    m.comp_id,
    m.vessel_id,
    m.dept_id,
    m.vessel_name,
    m.device_id,
    d.device_name,
    m.order_type,
    m.plan_date,
    t.execution_content,
    t.plan_content,
    t.sp_out_store_id spOutStoreId,
    t.ma_out_store_id maOutStoreId,
    d.parent_device_id,
    d.parent_device_name,
    d.device_level_flag 
FROM
    mw_view m  
    LEFT JOIN mw_order t ON m.id = t.id 
    AND m.comp_id = t.comp_id
    LEFT JOIN md_info d ON m.device_id = d.id 
WHERE
    1 = 1 
    AND m.comp_id = 'e98aab51fcd940f3b7aee670a2ea4207' 
    AND (
        m.vessel_id in(
     '5415db21fa7e47629d6563885aea92b5' ,
     '6c534cd5ebfd42159c3a852c078ccc99' ,
     'e2d6568bfd614f5a92d2c25c13d56c02' ,
     'e87f3c7b33de4779a8bd5ad4c9fab00c', 
     '7114381f6bf2467dac1cb26d4a56ca71' ,
     '48af2c42c3284c76b88cf2f57d34dc35' ,
     'b2fd6818be5346399a8db6785db1b20e' ,
     '1d11b69b76224b5cbef8dfc0f5d37dda', 
     'dd96e7b0658043889b67378552f91950' ,
     '6ede3b68b0aa4aed9f54c90162701a80' ,
     'bda55c44da6f4b28a79e0eb2451afe7b' ,
     '561d8e443e234507b72177fd65e947d5' ,
     '5d52046476164e84af8998496c2d1227' )
    ) 
    AND ( m.order_type = 'delay' OR m.order_type = 'warn' ) 
    AND (
    m.status_flag = '00' 
    OR m.status_flag = '55' 
    OR m.status_flag = '45' 
    OR m.status_flag = '35' 
    OR m.status_flag = '07' 
    OR m.status_flag = '50' 
    OR m.status_flag = '40' 
    OR m.status_flag = '30' 
    ) 
) t
  ORDER BY
    t.order_type,
    t.vessel_name,
    t.plan_date 
    LIMIT 0,
    10

案例10

该SQL执行2秒左右，优化方法：改变驱动表SQL如下，从depot_transfer_goods 表改成查询depot_transfer 表，改变和减少驱动表行数，来优化SQL。（案例日期：2023年12月28日）

SELECT a.*, b.type, b.status, b.allotTime, b.from
FROM depot_transfer_goods a
    INNER JOIN depot_transfer  b ON a.evidenceNo = b.no
WHERE b.depotId = 1
    AND b.deleteStatus = 0
    AND a.deleteStatus = 0
    AND b.type IN (1)
    AND b.status IN (3)
    AND b.createTime BETWEEN 1672502400 AND 1702223999 
ORDER BY b.createTime DESC
LIMIT 0, 12

优化后0.2秒左右，

select a.*, b.type, b.status, b.allotTime, b.from from (
select * from depot_transfer b where  createTime BETWEEN 1672502400 AND 1702223999
and  b.depotId = 1
    AND b.deleteStatus = 0 
    AND b.type IN (1)
    AND b.status IN (3)
    ) b inner join depot_transfer_goods a on a.evidenceNo = b.no
ORDER BY b.createTime DESC
LIMIT 0, 12

案例11

改变SQL的关联字段来优化，该SQL执行5秒左右，优化方法：改变关联字段，因mysql优化引擎不能传递相等字段（没有谓词下推），将ON t8.materialCode = t.materialCode 改成 ON t8.materialCode = t6.materielCode ，

因t.materialCode，这t表是查询表，需t查询出来才能关联t8，改成实体表t6，性能更好。 （案例日期：2023年12月28日）

SELECT t6.materielCode, t6.cargoOwnerCode, t6.depotCode
    , CONCAT(t6.materielCode, '-', t6.cargoOwnerCode, '-', t6.depotCode) AS threeCode
    , CAST(SYSDATE() AS DATE) AS outDate, t6.stockAvailable
    , IF(t8.id, 1, 0) AS isHighProfit
FROM (
    SELECT t1.materialCode, t1.shopId, t3.supplier_code
    FROM  oms_order_parent t
        INNER JOIN  oms_order_goods t1 ON t1.orderParentNo = t.orderNo
        INNER JOIN  cloud_shop t3 ON t3.id = t1.shopId
    WHERE t.createTime >= 1699545600
        AND t.warehouseCode = 'CK0001'
        AND (t.returnStatus IN (1, 3, 5)
            OR (t.returnStatus = 4
                AND t.warehouseOutStatus = 2))
    GROUP BY t1.materialCode, t.warehouseCode, t3.supplier_code
) t
    INNER JOIN  wms_depot_stock_collect t6
    ON t6.materielCode = t.materialCode
        AND t6.cargoOwnerCode = t.supplier_code
        AND t6.depotCode = 'CK0001'
        AND t6.stockAvailable <= 20
        AND t6.commodityType != 2
    LEFT JOIN  customer_material_factor_recommend t8
    ON t8.materialCode = t.materialCode
        AND t8.factorType = 'HIGHPROFIT'

修改SQL，优化后0.42秒

SELECT t6.materielCode, t6.cargoOwnerCode, t6.depotCode
    , CONCAT(t6.materielCode, '-', t6.cargoOwnerCode, '-', t6.depotCode) AS threeCode
    , CAST(SYSDATE() AS DATE) AS outDate, t6.stockAvailable
    , IF(t8.id, 1, 0) AS isHighProfit
FROM (
    SELECT t1.materialCode, t1.shopId, t3.supplier_code
    FROM  oms_order_parent t
        INNER JOIN  oms_order_goods t1 ON t1.orderParentNo = t.orderNo
        INNER JOIN  cloud_shop t3 ON t3.id = t1.shopId
    WHERE t.createTime >= 1699545600
        AND t.warehouseCode = 'CK0001'
        AND (t.returnStatus IN (1, 3, 5)
            OR (t.returnStatus = 4
                AND t.warehouseOutStatus = 2))
    GROUP BY t1.materialCode, t.warehouseCode, t3.supplier_code
) t
    INNER JOIN  wms_depot_stock_collect t6
    ON t6.materielCode = t.materialCode
        AND t6.cargoOwnerCode = t.supplier_code
        AND t6.depotCode = 'CK0001'
        AND t6.stockAvailable <= 20
        AND t6.commodityType != 2
    LEFT JOIN  customer_material_factor_recommend t8
    ON t8.materialCode = t6.materielCode
        AND t8.factorType = 'HIGHPROFIT'

案例12

该SQL执行8秒左右，还使用了like的%查询，优化方法：将like语句的查询作为in条件，不要使用关联，避免优化引擎走错误的执行计划。 （案例日期：2023年12月28日）

SELECT count(DISTINCT t2.channelId) AS channelIdNum, count(DISTINCT t2.districtId) AS districtIdNum
FROM (
    SELECT t.customerCode, t.materialCode
    FROM  crm_behavior_recommend t
        INNER JOIN  erp_base_customer t6
        ON t.customerCode = t6.customerCode
            AND t6.isStaff = 0
    WHERE t.createTime >= FROM_UNIXTIME(1701964800)
        AND t.createTime <= FROM_UNIXTIME(1702051199)
        AND t.behaviorType IN (2, 3)
    UNION
    SELECT t.buyerCode, t2.materialCode
    FROM  oms_order_parent t
        INNER JOIN  oms_order_picking t1 ON t.orderNo = t1.orderNo
        INNER JOIN  oms_order_goods t2 ON t2.orderParentNo = t.orderNo
        INNER JOIN  erp_base_customer t6
        ON t.buyerCode = t6.customerCode
            AND t6.isStaff = 0
    WHERE t.createTime >= 1701964800
        AND t.createTime <= 1702051199
        AND t.returnStatus != 4
        AND t1.returnStatus != 4
) t
    INNER JOIN  oms_material_basics t1 ON t.materialCode = t1.materialCode
    INNER JOIN  erp_base_customer t2
    ON t.customerCode = t2.customerCode
        AND t2.isStaff = 0
    LEFT JOIN (
        SELECT t.id, t.name_path AS newCategoryName, t.category_name AS categoryName, t.id_path
        FROM `db_baseinfo`.xdnet_basic_category t
        WHERE t.category_level = 5
    ) t7
    ON t7.id = t1.newCategoryId
WHERE CONCAT(',', t7.id_path, ',') LIKE CONCAT('%,', 2394, ',%')

优化后0.3秒左右

select count(DISTINCT t1.channelId) AS channelIdNum, count(DISTINCT t1.districtId) AS districtIdNum from (
SELECT  DISTINCT t2.channelId ,  t2.districtId,t1.newCategoryId
FROM (
    SELECT t.customerCode, t.materialCode
    FROM  crm_behavior_recommend t
        INNER JOIN  erp_base_customer t6
        ON t.customerCode = t6.customerCode
            AND t6.isStaff = 0
    WHERE t.createTime >= FROM_UNIXTIME(1702224000)
        AND t.createTime <= FROM_UNIXTIME(1702310399)
        AND t.behaviorType IN (2, 3)
    UNION
    SELECT t.buyerCode, t2.materialCode
    FROM  oms_order_parent t
        INNER JOIN  oms_order_picking t1 ON t.orderNo = t1.orderNo
        INNER JOIN  oms_order_goods t2 ON t2.orderParentNo = t.orderNo
        INNER JOIN  erp_base_customer t6
        ON t.buyerCode = t6.customerCode
            AND t6.isStaff = 0
    WHERE t.createTime >= 1702224000
        AND t.createTime <= 1702310399
        AND t.returnStatus != 4
        AND t1.returnStatus != 4
) t
    INNER JOIN  oms_material_basics t1 ON t.materialCode = t1.materialCode
    INNER JOIN  erp_base_customer t2
    ON t.customerCode = t2.customerCode
        AND t2.isStaff = 0
    ) t1
 where  t1.newCategoryId in (     
        SELECT t.id 
        FROM  xdnet_basic_category t
        WHERE t.category_level = 5 and  CONCAT(',', t.id_path, ',') LIKE CONCAT('%,', 2394, ',%')
    )

案例13

研发兄弟发来一个SQL，说以下SQL执行很慢，到从库执行很快，他说是写库上执行慢，查不出来结果，试了的确在写库执行很慢，因为要select数据后直接insert，无法直接把SQL改到从库上，对比和从库的执行计划 （案例日期：2024年5月21日）

SELECT
    su.sys_user_id,
    11292938 AS group_id 
FROM
    sys_user AS su
    LEFT JOIN sys_user_info AS sui ON su.sys_user_id = sui.sys_user_id 
WHERE
    su.app_id = '61' 
    AND su.work_flag = '1' 
    AND su.sys_user_id IN (
    SELECT
        sys_user_id 
    FROM
        sys_user_tree_org 
    WHERE
        parent_id IN ( SELECT biz_id FROM group_executive_extension WHERE 
        app_id = '61' AND group_id = 11292938 
        AND type = 'ORG' AND biz_ext_type = 3 ) 
        AND parent_type = 3 
    ) 
    AND su.user_post IN (
    SELECT
        type 
    FROM
        sys_position 
    WHERE
        del_flag = 0 
    AND app_id = '61' 
    AND post_id IN ( SELECT biz_id FROM group_executive_extension 
    WHERE app_id = '61' AND type = 'POSITION_ID' AND group_id = 11292938 ))

写库的执行计划如下：

执行计划说明：

id列:

1， id值相同 — 执行顺序由上至下, 2，id值不同 — id值越大优先级越高，越先被执行，3，id值既有相同，又有不同 — id值大的先执行,4，如果id值相同则由上到下执行

table列 :

1，直接显示表名或者表的别名, 2，<unionM,N> 由 ID 为 M，N 进行union 产生的表，3，< derivedN > 由 ID 为 N 衍生出来的表,4，< subqueryN> ：由ID为N查询出来的表

执行计划解释： 先sys_user_tree_org 和group_executive_extension关联查询查出结果（第一步），并物化存储，再作为驱动表查询sys_user ，再查询sys_user_info ，再查询sys_position，最后查询 group_executive_extension

这里第一步查询出的结果有1.6万条，作为驱动表，有点大，并且是直接驱动表sys_user(有300万条+），这样嵌套循环太慢。看从库的执行计划如下：

这里可以看出从库执行计划，是将3个查询，分成2个查询结果后，再合并。执行只需0.15秒

知道SQL在主库的执行计划数据库选择错了，如何解决？ 使用force index或者 ignore index都不能改变执行计划，这时只能修改SQL，修改如下：

SELECT
    sys_user_id,
    group_id 
FROM
    (
    SELECT
        su.*,
        11292938 AS group_id 
    FROM
        sys_user AS su
        LEFT JOIN sys_user_info AS sui ON su.sys_user_id = sui.sys_user_id 
    WHERE
        su.app_id = '61' 
        AND su.work_flag = '1' 
    ) su 
WHERE
    su.sys_user_id IN (
    SELECT
        sys_user_id 
    FROM
        sys_user_tree_org 
    WHERE
        parent_id IN ( SELECT biz_id FROM group_executive_extension WHERE app_id = '61' 
        AND group_id = 11292938 AND type = 'ORG' AND biz_ext_type = 3 ) 
        AND parent_type = 3 
    ) 
    AND su.user_post IN (
    SELECT
        type 
    FROM
        sys_position 
    WHERE
        del_flag = 0 
    AND app_id = '361' 
    AND post_id IN ( SELECT biz_id FROM group_executive_extension WHERE app_id = '61' 
    AND type = 'POSITION_ID' AND group_id = 11292938 ))

修改后，执行计划如下：和从库的类似，也是把3个查询，合并结果后再查，这里把前面sys_user 和sys_user_info ，做为一个子查询整体，这样就先过滤掉直接嵌套循环300万条的sys_user，只是循环2者的结果1.6万条,优化后执行只需0.37秒，和从库直接多了一倍的时间，但比以前有了很大的改进。

总结：

1，优化SQL，MySQL需要选择正确的驱动表，如果执行计划不正确，可用exists来明确驱动表

2，加复合索引，最好先测试执行效果，有时预想和实际不一样

说明：