112.mysql in导致的索引失效优化案例

1.原来的SQL;

# Query_time: 100.474167  Lock_time: 0.000165 Rows_sent: 167  Rows_examined: 3287596
SET timestamp=1706061618;
select * from b_vehicle_loss_component lcomp where lcomp.OperateStatus<>'D' 
and lcomp.id in ( select lkk.lossid from b_case_loss_coverage lkk 
 where lkk.lossapprovalid in ( 
   select l.lossapprovalid from b_vehicle_loss l 
   where 1=1 and l.verifyflag in ('1','3') 
   and l.veripflag in ('N','1','3') 
   and l.objectid in ( 
      select lc.objectid from b_case_object lc 
      where lc.reportno ='07318000002024000014' )) 
	  and lkk.kindcode not in('BZ') 
);

--查看执行计划:

explain select * from b_vehicle_loss_component lcomp where lcomp.OperateStatus<>'D' 
and lcomp.id in ( select lkk.lossid from b_case_loss_coverage lkk 
 where lkk.lossapprovalid in ( 
   select l.lossapprovalid from b_vehicle_loss l 
   where 1=1 and l.verifyflag in ('1','3') 
   and l.veripflag in ('N','1','3') 
   and l.objectid in ( 
      select lc.objectid from b_case_object lc 
      where lc.reportno ='07318000002024000014' )) 
	  and lkk.kindcode not in('BZ') 
);

+----+--------------+-------------+------------+------+--------------------------------------------------------+-------------------------------+---------+--------------------------+---------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys                                          | key                           | key_len | ref                      | rows    | filtered | Extra                                              |
+----+--------------+-------------+------------+------+--------------------------------------------------------+-------------------------------+---------+--------------------------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL                                                   | NULL                          | NULL    | NULL                     |    NULL |     0.00 | NULL                                               |
|  1 | SIMPLE       | lcomp       | NULL       | ALL  | PRIMARY,Id                                             | NULL                          | NULL    | NULL                     | 2094983 |     0.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | lc          | NULL       | ref  | idx_lclaimlossitems_itemid,idx_lclmlositem_registno    | idx_lclmlositem_registno      | 69      | const                    |       2 |   100.00 | Using where                                        |
|  2 | MATERIALIZED | l           | NULL       | ref  | PRIMARY,idx_lcarlossapproval_itemid                    | idx_lcarlossapproval_itemid   | 93      | hxappdb.lc.ObjectId      |       1 |     6.00 | Using where                                        |
|  2 | MATERIALIZED | lkk         | NULL       | ref  | idx_llossapprovalkindcode_aid,idx_llosskindcode_lossid | idx_llossapprovalkindcode_aid | 123     | hxappdb.l.LossApprovalId |       3 |    90.00 | Using where                                        |
+----+--------------+-------------+------------+------+--------------------------------------------------------+-------------------------------+---------+--------------------------+---------+----------+----------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)
lcomp 表未使用到索引,且该表上ID字段上有索引。优化目前是让其使用索引。

--2.优化。

--优化后lcomp 能使用到了索引
explain select lcomp.* from b_vehicle_loss_component lcomp 
JOIN 
( select distinct lkk.lossid from b_case_loss_coverage lkk 
 where lkk.lossapprovalid in ( 
   select  l.lossapprovalid from b_vehicle_loss l 
   where 1=1 and l.verifyflag in ('1','3') 
   and l.veripflag in ('N','1','3') 
   and l.objectid in ( 
      select lc.objectid from b_case_object lc 
      where lc.reportno ='07318000002024000014' )) 
	  and lkk.kindcode not in('BZ') 
) W ON  lcomp.id= W.lossid
where lcomp.OperateStatus<>'D' ;
+----+-------------+------------+------------+--------+--------------------------------------------------------+-------------------------------+---------+--------------------------+------+----------+-----------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                                          | key                           | key_len | ref                      | rows | filtered | Extra                                         |
+----+-------------+------------+------------+--------+--------------------------------------------------------+-------------------------------+---------+--------------------------+------+----------+-----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                                   | NULL                          | NULL    | NULL                     |    2 |   100.00 | Using where                                   |
|  1 | PRIMARY     | lcomp      | NULL       | eq_ref | PRIMARY,Id                                             | PRIMARY                       | 122     | W.lossid                 |    1 |    90.00 | Using where                                   |
|  2 | DERIVED     | lc         | NULL       | ref    | idx_lclaimlossitems_itemid,idx_lclmlositem_registno    | idx_lclmlositem_registno      | 69      | const                    |    2 |   100.00 | Using where; Using temporary; Start temporary |
|  2 | DERIVED     | l          | NULL       | ref    | PRIMARY,idx_lcarlossapproval_itemid                    | idx_lcarlossapproval_itemid   | 93      | hxappdb.lc.ObjectId      |    1 |     6.00 | Using where                                   |
|  2 | DERIVED     | lkk        | NULL       | ref    | idx_llossapprovalkindcode_aid,idx_llosskindcode_lossid | idx_llossapprovalkindcode_aid | 123     | hxappdb.l.LossApprovalId |    3 |    90.00 | Using where; End temporary                    |
+----+-------------+------------+------------+--------+--------------------------------------------------------+-------------------------------+---------+--------------------------+------+----------+-----------------------------------------------+
5 rows in set, 1 warning (0.01 sec)

--优化后执行时间0s;

select lcomp.* from b_vehicle_loss_component lcomp 
JOIN 
( select distinct lkk.lossid from b_case_loss_coverage lkk 
 where lkk.lossapprovalid in ( 
   select  l.lossapprovalid from b_vehicle_loss l 
   where 1=1 and l.verifyflag in ('1','3') 
   and l.veripflag in ('N','1','3') 
   and l.objectid in ( 
      select lc.objectid from b_case_object lc 
      where lc.reportno ='07318000002024000014' )) 
	  and lkk.kindcode not in('BZ') 
) W ON  lcomp.id= W.lossid
where lcomp.OperateStatus<>'D' ;

167 rows in set (0.00 sec)

3.对比

原来执行时间:100.474167s,IN 导致未使用到索引。
现在执行时间:0.00s ,IN 改为JOIN 后使用到了索引。
嵌套循环连接:Using join buffer (Block Nested Loop),在当前SQL中性能差,目前应该是索引范围扫描。

4.优化建议 

今天9:00~16点,有2000多条慢SQL,大多数都是和表:b_vehicle_loss_component相关。
WHERE 条件中使用了 IN 导致表没有使用到索引,执行时间:20s~100s的都有。
建议将IN 等价改写为 JOIN,这样可以使用到索引。
我抽样检查了一部分慢SQL，看到的全部是这个表的 IN 操作,SQL语句雷同。
如上为优化样例,建议按如上方法修改。