慢SQL优化案例

原始超时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）