参考:http://codingstandards.iteye.com/blog/1344833
上面参考文章中《高性能MySQL》第四章第四节在第三版中我对应章节是第六章第五节
最近分析生产环境慢查询,发现上线很久但是效率不高的查询
MySQL版本5.5.18
SELECT loc.cell_no AS m_cellNo ...
FROM bs_loc loc LEFT JOIN st_stock_m m ON loc.cell_no = m.cell_no WHERE loc.zone_no = 'B12' AND loc.WMS_PICKING_FLAG = 'cp' AND m.cell_no in (SELECT cell_no FROM st_stock_m WHERE goods_no IN ('1230480'))
因为开发对这块的逻辑也不是很清楚,不分析逻辑上是否可以直接goods_no拿出来直接约束结果集,单纯从in子查询无法使用到索引来看MySQL优化器是如何去处理的
SELECT
`ma`.`loc`.`CELL_NO` AS `m_cellNo`
FROM `ma`.`bs_loc` `loc` JOIN `ma`.`st_stock_m` `m`
WHERE
((`ma`.`loc`.`ZONE_NO` ='B33') AND<in_optimizer>(`ma`.`m`.`CELL_NO`,
<EXISTS>
(SELECT1FROM `ma`.`st_stock_m` WHERE ((`ma`.`st_stock_m`.`GOODS_NO` ='1230480') AND (<CACHE>(`ma`.`m`.`CELL_NO`) = `ma`.`st_stock_m`.`CELL_NO`))))
AND (`ma`.`loc`.`CELL_NO` = `ma`.`m`.`CELL_NO`))
执行计划
其实子查询返回的结果集最多不会超过3个,通常我们认为内部会按照使用结果集逐一去查,效率会很快,但实际上不是
以为内部的操作会是
步骤1: SELECT group_concat(cell_no) FROM st_stock_m WHERE goods_no IN ('1230480') into @cell_no; 步骤2 SELECT loc.cell_no AS m_cellNo ... FROM bs_loc loc LEFT JOIN st_stock_m m ON loc.cell_no = m.cell_no WHERE loc.zone_no = 'B12' AND loc.WMS_PICKING_FLAG = 'cp' AND m.cell_no in (@cell_no);
按照《高性能MySQL》中所说:
把这个查询拿到MariaDB测试了一下,确实要比MySQL 5.5.18处理效果好很多。
SELECT
`ma`.`loc`.`CELL_NO` AS `m_cellNo`
FROM `ma`.`bs_loc` `loc` semi JOIN (`ma`.`st_stock_m`) JOIN `ma`.`st_stock_m` `m`
WHERE
(
(`ma`.`m`.`CELL_NO` = `ma`.`st_stock_m`.`CELL_NO`) AND
(`ma`.`loc`.`ZONE_NO` ='B33') AND
(`ma`.`st_stock_m`.`GOODS_NO` ='1230480') AND
(`ma`.`loc`.`CELL_NO` = `ma`.`st_stock_m`.`CELL_NO`)
)
执行计划
MariaDB优化器改写后使用的semi join,这块MariaDB官网有部分说明:
https://mariadb.com/kb/en/mariadb/semi-join-materialization-strategy/
《MySQL技术内幕:SQL编程》中对于MariaDB优化器对于子查询和join的优化部分有说明
其他博文对于MySQL5.5以及MariaDB5.3优化器对比的文章:
http://blog.sina.com.cn/s/blog_aa8dc60801012pzc.html
http://www.server110.com/mariadb/201310/2245.html