参考：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 (''))
因为开发对这块的逻辑也不是很清楚，不分析逻辑上是否可以直接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` ='') 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 ('') 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` ='') 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优化器对比的文章：