【MySQL】MySQL/MariaDB的最佳化器對in子查詢的處理

標籤：

參考：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_noWHERE　　　　　　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;步驟2SELECT        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

 

【MySQL】MySQL/MariaDB的最佳化器對in子查詢的處理