对一条慢SQL的优化记录 20220629

对一条慢SQL的优化记录 20220629

原SQL 注意原SQL之索引慢不仅是在左连接的时候在on中加入了其它判定比如on a.id=b.id and a.status=1这种 并且在判定in含有时在where子句使用and关联了两个id主键的where in子句

摘录:子查询的结果是未知的，不能作为外层的索引判断 。【高版本mysql优化器应该会优化成连接查询】 以上就是利用，在SQL 查询语言执行流程中，优化器执行计划生成已经索引选择阶段，子查询的结果无法提供任何的判断依据， 因此不能作为外层判断索引的依据，由此导致外层直接全表扫描了。

-- EXPLAIN SELECT tc.commodity_code commodity_code, ccs.cabinet_id cabinet_id, ccs.task_id , ccs.id id, ca.id as caid, tc.id as tcid, ccs.status as ccsstatus, ca.status as castatus, tc.status as tcstatus, tc.commodity_features commodity_features, tc.commodity_file_path commodity_file_path, tc.international_category_name international_category_name, tc.comment comment, tc.shelf_life shelf_life, tc.place_origin place_origin, tc.unit_value unit_value, tc.unit_id unit_id, tc.commodity_flag commodity_flag, tc.sell_time sell_time, tc.create_time create_time, tc.commodity_status commodity_status, tc.commodity_unit commodity_unit, tc.commodity_operate_mode commodity_operate_mode, cc.category_name category_name, tc.category_id category_id, tc.commodity_specification commodity_specification, tc.create_date create_date, tc.supplier_id supplier_id, tc.commodity_name commodity_name, s.supplier_name supplier_name, ca.cabinet_name cabinet_name, ca.cabinet_code cabinet_code, ccs.commodity_purchase_price purchase_price, ccs.commodity_guide_price commodity_guide_price, ccs.current_commodity_price current_price, ccs.commodity_sell_price plan_price, ccs.check_flag check_flag, ccs.check_user_id check_user_id, ccs.check_user_name check_user_name, ccs.user_id user_id, ccs.user_name user_name, ccs.commodity_id commodity_id, ca.external_code external_code FROM t_cabinet_commodity_sell ccs LEFT JOIN t_cabinet ca ON ccs.cabinet_id = ca.id AND ca.status = 1 LEFT JOIN t_commodity tc ON ccs.commodity_id = tc.id AND tc.status = 1 LEFT JOIN t_supplier s ON tc.supplier_id = s.id AND s.status = 1 LEFT JOIN t_commodity_category cc ON tc.category_id = cc.id AND cc.status = 1 WHERE ccs.status = 1 AND ccs.task_id = 7757 AND tc.id in (1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153) AND ca.id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) ORDER BY ccs.check_flag ASC

在改善时,通过最左主表的相关判定给定改入后,接下来判定的子表的where子句进行了关联,但此时数据是冗余的, 最后需要判定例如这个例子中tc.id和ca.id is not null

-- EXPLAINSELECT tc.commodity_code commodity_code, ccs.cabinet_id cabinet_id, ccs.task_id , ccs.id id, ca.id as caid, tc.id as tcid, ccs.status as ccsstatus, ca.status as castatus, tc.status as tcstatus, tc.commodity_features commodity_features, tc.commodity_file_path commodity_file_path, tc.international_category_name international_category_name, tc.comment comment, tc.shelf_life shelf_life, tc.place_origin place_origin, tc.unit_value unit_value, tc.unit_id unit_id, tc.commodity_flag commodity_flag, tc.sell_time sell_time, tc.create_time create_time, tc.commodity_status commodity_status, tc.commodity_unit commodity_unit, tc.commodity_operate_mode commodity_operate_mode, cc.category_name category_name, tc.category_id category_id, tc.commodity_specification commodity_specification, tc.create_date create_date, tc.supplier_id supplier_id, tc.commodity_name commodity_name, s.supplier_name supplier_name, ca.cabinet_name cabinet_name, ca.cabinet_code cabinet_code, ccs.commodity_purchase_price purchase_price, ccs.commodity_guide_price commodity_guide_price, ccs.current_commodity_price current_price, ccs.commodity_sell_price plan_price, ccs.check_flag check_flag, ccs.check_user_id check_user_id, ccs.check_user_name check_user_name, ccs.user_id user_id, ccs.user_name user_name, ccs.commodity_id commodity_id, ca.external_code external_code FROM (select ccs0.id,ccs0.commodity_purchase_price,ccs0.commodity_guide_price,ccs0.current_commodity_price,ccs0.task_id,ccs0.commodity_sell_price,ccs0.check_flag,ccs0.check_user_id,ccs0.check_user_name,ccs0.user_id,ccs0.user_name,ccs0.status, ccs0.commodity_id,ccs0.cabinet_id from t_cabinet_commodity_sell ccs0 where ccs0.status=1 and ccs0.task_id = 7757 )ccs LEFT JOIN (select ca0.cabinet_name,ca0.cabinet_code,ca0.external_code,ca0.id,ca0.status from t_cabinet ca0 where ca0.status=1 and ca0.id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30))ca ON ccs.cabinet_id = ca.id LEFT JOIN (select tc0.id,tc0.commodity_features,tc0.commodity_file_path,tc0.international_category_name,tc0.comment, tc0.shelf_life,tc0.place_origin,tc0.unit_value,tc0.unit_id,tc0.commodity_flag,tc0.sell_time,tc0.create_time,tc0.commodity_status,tc0.commodity_unit,tc0.commodity_operate_mode,tc0.category_id,tc0.commodity_specification,tc0.create_date,tc0.supplier_id,tc0.commodity_code,tc0.status, tc0.commodity_name from t_commodity tc0 where tc0.status = 1 and tc0.id in (1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153) )tc ON ccs.commodity_id = tc.id LEFT JOIN (select s0.supplier_name,s0.id from t_supplier s0 where s0.status=1)s ON tc.supplier_id = s.id LEFT JOIN (select cc0.category_name,cc0.id from t_commodity_category cc0 where cc0.status=1)cc ON tc.category_id = cc.id where ca.id is not null and tc.id is not null ORDER BY ccs.check_flag ASC

最后的where判定:

where tc.id is not null and ca.id is not null

这里只判定ca.id is not null是非常快的查询出来,但是那样的数据还是冗余的, 只有这两个id都不为null才是最后应该查询的数据. 之后我怀疑是子表(tc)表的查询是跟ac数据量不一样的,并且将这段代码改改少了id的判定

and tc0.id in(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153)

改为了:

and tc0.id in(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31)

where tc.id is not null

而看索引不起效中虽然null也是一个很关键的点

网上的索引不起效的七字口诀(感觉并不太准确)

模：代表模糊查询。型：代表数据类型。数：代表函数。空：是Null的意思。运：代表运算。最：代表最左原则。快：全表扫描更快的意思。

但是为了便于将判定不为空改为其他方式,这里使用了if (本来使用ifnull但是并不好用) 改好后的SQL,最后的where改为

where IF(ca.id is null,1,0) + IF(tc.id is null,1,0) = 0

总结:在使用多表联查关联时,并且每个子表(或非主表)有子查询时,一般做到子表的单独判定中 例如上面的ca0,tc0子表的单独子判定 并在最终where用相应if去去除一些冗余数据即可 而最终速度取决于索引是否奏效和是否是主键索引 需要调试并在执行计划查看 如果对你有帮助记得点赞~

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。