原始超时SQL:
SELECT v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type,IFNULL(SUM(v.num),0) lock_num FROM
(
SELECT t.*,SUM(i.num)num FROM
(SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`) t
LEFT JOIN `tbl_inventory_locked_info` i ON t.`inventory_no`=i.inventory_no AND t.`type`=i.inventory_type
GROUP BY t.type,t.`inventory_no`
)v
GROUP BY v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type
优化后的SQL语句:
SELECT v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type,IFNULL(SUM(v.num),0) lock_num FROM
(
SELECT t.*,SUM(i.num)num FROM
(SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`) t
INNER JOIN `tbl_inventory_locked_info` i ON t.`inventory_no`=i.inventory_no AND t.`type`=i.inventory_type
GROUP BY t.`inventory_no`,t.type
)v WHERE v.num>0
GROUP BY v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type
这里可以看到只是把Left Join 改成了Inner Join 和加上一个Where条件(业务需要)优化前SQL的执行时间是13秒,优化后SQL的执行时间是200ms,显然执行效率是指数级的增长
原因分析:当使用Left Join的时候
左表是主表:
SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`
也就是上面这个子查询产生的临时表
而Left Join的特点是以左表作为驱动表,当左表比较大的时候,那么自然循环次数也会变多,效率会下降。
再结合上面这个具体的SQL语句:
当inventory_no和type的种类比较多的时候,左表就会相对比较大,所以效率相对也会比较低。
为什么改为Inner Join就能提高效率:inner join在连接的时候,mysql会自动选择较小的表来作为驱动表,从而达到减少循环次数的目的。
但是很多人应该都看出来了,优化前和优化后的查询结果是不同的,认为这样对比是没有意义的,确实结果是不同的,但是优化前的结果集是包括优化后SQL的结果集,而我们刚好又是只需要优化后的结果集,很多人又会说,那为什么一开始不写成这样呢,我想很可能写这条SQL的主人很可能是在代码层对数据进行了处理,所以当时SQL这样写了,也没想到后面会超时。
那为什么测试的时候为什么没有测试出来:数据量的问题,测试的时候inventory_no和type种类比较少,导致左表比较小,不会超时。所以这个案例同时也提醒我们,线上数据不可预测,我们在写完SQL后要更多地不同的数据量会不会导致超时。
总结和拓展:
1、left join选择小表作为驱动表
2、如果左表比较大,并且业务要求驱动表必须是左表,那么我们可以通过where条件语句,使得左表被过滤的小一些,主要原理和第一条类似
3、关联字段给索引,因为在mysql的嵌套循环算法中,是通过关联字段进行关联,并查询的,所以给关联字段索引很必要
4、如果sql里面有排序,请给排序字段加上索引,不然会造成排序使用全表扫描
5、如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。
6、根据文档,MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。所以把表与表之间的关联字段给上encoding和collation(决定字符比较的规则)全部改成统一的类型
7、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system)