【MySQL】select子查询优化

1.SQL查看 

--优化前:141.882718s
--优化后:0.02 sec

	# Time: 2024-02-24T15:58:59.173778+08:00
	# User@Host: claim[claim] @  [10.255.224.7]  Id: 1197878
	# Query_time: 141.882718  Lock_time: 0.000154 Rows_sent: 276  Rows_examined: 3334051
	SET timestamp=1708761539;
	select *
	  from b_vehicle_loss_component lcomp
	 where lcomp.OperateStatus <> 'D'
	   and lcomp.id in
		   (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 = '07104000002023002852'))
			   and lkk.kindcode in ('BZ')
			  );

2.分析 

	+----+--------------+-------------+------------+------+--------------------------------------------------------+-------------------------------+---------+--------------------------+---------+----------+----------------------------------------------------+
	| 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                     | 2094651 |     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                    |      16 |   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 |    10.00 | Using where                                        |
	+----+--------------+-------------+------------+------+--------------------------------------------------------+-------------------------------+---------+--------------------------+---------+----------+----------------------------------------------------+
	5 rows in set, 1 warning (0.00 sec)

	lcomp 表做了驱动表,使用了全表扫描。

3.优化方案如下:

	--改写SQL,子查询替换为JOIN;
	select *
	  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 = '07104000002023002852'))
			   and lkk.kindcode in ('BZ')
			  ) t on lcomp.id=t.lossid and 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     | t.lossid                 |    1 |    90.00 | Using where                                   |
	|  2 | DERIVED     | lc         | NULL       | ref    | idx_lclaimlossitems_itemid,idx_lclmlositem_registno    | idx_lclmlositem_registno      | 69      | const                    |   16 |   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 |    10.00 | Using where; End temporary                    |
	+----+-------------+------------+------------+--------+--------------------------------------------------------+-------------------------------+---------+--------------------------+------+----------+-----------------------------------------------+
	5 rows in set, 1 warning (0.00 sec)

	可以看到,所有的表都用到了索引。lc 表作为作为驱动表。

4.总结

针对select 查询 in 条件后是子查询的情况，如果主表筛选条件是索引或主键字段，则可以通过将IN子查询修改为JOIN操作。这样主表也可以使用索引，从而优化SQL;