這篇是關于一個 MySQL query optimizer 問題的定位及解決。
公司系統(tǒng)中一個簡單的 SQL 查詢卻花費了近 3 秒的時間，語句大致為：
SELECT slug FROM products
WHERE id IN (id1, id2, ...)
AND deleted_at IS NULL;
其中，id 是 PRIMARY KEY，deleted_at 記錄刪除時間，用于實現(xiàn) soft delete，也加了索引。deleted_at 允許為空，為空代表是正常商品，不為空代表“已刪除”的商品，同時記錄下了刪除時間。
并且在 Rails 的 ActiveRecord 中設置了 default scope 默認 deleted_at 為空。
這條 SQL 語句當然期待一直使用 PRIMARY index 實現(xiàn)查詢效率最高，但是，結果卻出人意料的成了 slow query。
使用 MySQL EXPLAIN 來查看查詢的執(zhí)行流程。結果如下：
發(fā)現(xiàn)當給定的 ID 條件為 20 左右時，是正常地使用 PRIMARY index；但是，當給定的 ID 條件為 50 左右時，竟然使用了 deleted_at 的索引，要知道 deleted_at 為空的有幾千萬行！
EXPLAIN output 中 Extra 列出給了 Using index condition，也就是 MySQL 只使用了 deleted_at index 的數(shù)據(jù)，而不用讀取任何表數(shù)據(jù)；這是 MySQL 的 ICP 優(yōu)化。這個過程相當于：
- 讀取
deleted_at索引信息到內存； - 找出
deleted_at為空的行的 ID（幾千萬行）； - 在上面幾千萬行中找出 ID 在給定的 ID 列表中的行；
顯然，這樣效率是極其低下的，出現(xiàn)這種令人意外的行為，我猜測是由于 MySQL 最終獲取數(shù)據(jù)不只一種方式，這就需要評估多種方式執(zhí)行的復雜度。當指定 ID 比較少時，使用 PRIMARY index 這種方式的復雜度相較使用 deleted_at index 小；當指定 ID 比較多時，在 MySQL 評估算法中，前一種方式的復雜度竟然詭異地大于了后一種方式。而真實的執(zhí)行語句時的情況并非如此，讓我們先猜測一下該評估算法的一些行為。
- 當指定的 ID 比較多，而且比較離散時，MySQL 認為需要讀取更多的 BTree nodes；從 PRIMARY index 中獲取到 ID 后，還要離散地讀取表數(shù)據(jù)，從而過濾
deleted_at為空的行； - 回頭看看使用
deleted_atindex 這種方式呢，完全只需要使用 index 數(shù)據(jù)就可以完成；可以很好地利用 ICP 優(yōu)化，簡直完美。
等了解到更多 MySQL 查詢優(yōu)化后，再補充準確的原因吧。
解決方案
解決的辦法當然是讓 MySQL 盡量不要使用 deleted_at index 索引了，可以有如下幾種方法：
- 在每次使用 ID 查詢時，利用 Index Hints 明確告訴 MySQL 使用哪個索引，這里就是
Product.force_index(:PRIMARY)；其他條件查詢時，還要 case by case 的分析是否使用 index hints； - 直接刪除掉
deleted_at的索引；之前習慣性的給這類列加索引，其實仔細考慮下，幾乎沒什么用，也就能優(yōu)化下Product.count這樣的語句；其他查詢幾乎都不會用到該索引； - 不使用 soft delete；當然出現(xiàn)本文中的問題，怎么也怪不到 soft delete 的頭上，不過多少也有 default scope 的問題。使用 soft delete 并不一定合適，我一直認為 soft delete is evil，就像我認為 ActiveRecord callback is evil；這個話題回頭專門寫吧；
