不要用 in - 子查詢
你好，我是 yes。
前兩天我的 VIP 用戶向我拋出了一個 SQL 問題，他的 MySQL 是 8.x 版本：
大概意思如下 sql ：
select * from A where id in (
select max(id) as id from A
where task_id in(1,2,3)
group by task_id
);這個 A 表中是有 task_id 這個索引的。
一眼看去這個查詢沒啥問題，子查詢裏會利用 task_id 這個索引，然後外面的這個查詢會上 id 這個主鍵索引。
而事實是：
一看 explain ，果然走了全表掃描：
那麼問題來了：明明有主鍵索引不用，mysql 爲什麼要選擇全表？
我也不知道。
MySQL 有個優化器，它會決定最終以怎麼樣的形式、選擇那個索引來生成最終的執行計劃。
對將要執行的 SQL 而言，優化器會有成本模型，它會根據當前表的一些估算值結合當前的 SQL 語句進行打分，比如如果用了索引 A 需要多少 I/O 成本、CPU 成本，如果用索引 B 要多少 I/O、CPU 成本。
總而言之，它有自己的一套規則，會根據估計值預算成本，根據成本最終生成執行計劃。
篇幅有限具體不多介紹，有興趣的話看下官網：https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
既然是預算，那就有可能不準，所以有時候就會產生該走索引卻全表掃描的情況（因爲算出的成本走全表掃描反而更低）。
所以針對上面這種情況，我讓他加了強制用主鍵的操作 force index(PRIMARY)，但是並沒有生效，還是走了全表。
可以看到掃描了大約 996w 行數據（表數據有新增，所以從 800w 多變成了 900 多 w）。
我自己估計：大致就是因爲子查詢的結果不確定，MySQL 不知道 in 的值到底有多大，所以保守型的選擇了全表（我猜的，具體的邏輯估計得看源碼，不過最終表現的事實確實如此）。
問題就在 in 的值大小不確定。
通過子查詢，我們自己其實也無法保證 in 的值到底是多少。
所以最後的方案是拋棄 in 的方式，採用 inner join 的方式來改造 sql 實現。
改造 SQL 如下：
select * from A a inner join (
select max(id) as id from A
where task_id in(1,2,3) group by task_id
) b
on a.id = b.id;這樣改造以後，進行了一波 explain，結果如下：
查詢結果秒出，可以看下第二行，用上了主鍵索引，全部掃描的行數加起來也就 600 多。
所以我們換了一種方式來使得 MySQL 正常的用上索引且所要的結果是一致的。
這裏也建議，如果 sql 是包含子查詢的 in 查詢，那麼最好換成 join 的方式，因爲我們不能保證 in 的查詢一定會用上索引，萬一來個全表掃描，在表數據量比較大的場景，很容易產生阻塞，多來幾個這樣的阻塞，數據庫連接不可用，服務可能就掛了。
在測試環境看起來沒問題，一到生產就 GG。
所以能不用 in + 子查詢，就不要用，可以用 join 來替代實現。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/vUpFB6OTKfZOy6wxAdd79w