原SQL 注意原SQL之索引慢不仅是在左连接的时候在on中加入了其它判定比如on a.id=b.id and a.status=1这种 并且在判定in含有时在where子句使用and关联了两个id主键的where in子句 摘录:子查询的结
原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
上面这个原SQL执行计划看下来是这样的
可以看到tc表直接走了全表查询,没有走索引(包括主键也没走)
在改善时,通过最左主表的相关判定给定改入后,接下来判定的子表的where子句进行了关联,但此时数据是冗余的,
最后需要判定例如这个例子中tc.id和ca.id is not null
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
(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但此时tc主键依旧没有走索引
这里ALL并且tc表展示了rows 431条,也就说明了tc表含有数量为431条
这里只判定ca.id is not null是非常快的查询出来,但是那样的数据还是冗余的,
只有这两个id都不为null才是最后应该查询的数据.
之后我怀疑是子表(tc)表的查询是跟ac数据量不一样的,并且将这段代码改改少了id的判定
(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)
这是看执行计划中type改为了range区间,走了主键索引,但是依旧非常慢
此时看到已经通过主键的range判定rows变为了26条(传入了26个tc的id,并且也能对应到该表)
而这里tc的id保留至92时,相关tc表走了status的索引,而也没有走PRIMARY,并且时间依旧非常久(33s)
因为这里关键的是要id主键in子句.
在调整tc的id保留至91时,依旧是range,在保留的id到91时的数据量在tc表中为78条
而78占原有tc表数据量比例为
将近有五分之一的数据量时开始走的range
而不管range还是ref(走的tc的status索引)
速度都是要很慢的
而最终回归到使用到至153的tc的id,
并且从根源上查看改掉为什么tc的id没有走主键索引
而看索引不起效中虽然null也是一个很关键的点
网上的索引不起效的七字口诀(感觉并不太准确)
但是为了便于将判定不为空改为其他方式,这里使用了if
(本来使用ifnull但是并不好用)
改好后的SQL,最后的where改为
作为一个判定也作为两个函数返回结果的计算,这里其实最终是走了索引
而整个查询也从最开始的那条SQL将近1分钟,变为现在最后用整个where if计算=0的半秒
并且查询数据量都一致
总结:在使用多表联查关联时,并且每个子表(或非主表)有子查询时,一般做到子表的单独判定中
例如上面的ca0,tc0子表的单独子判定
并在最终where用相应if去去除一些冗余数据即可
而最终速度取决于索引是否奏效和是否是主键索引
需要调试并在执行计划查看
如果对你有帮助记得点赞~