Index 下的不好會對 MySQL 的 Index Merge 產生負面效果...

前陣子在看資料庫寫入會卡住時找到的問題，主要是 SELECT SQL query 跑太久造成 InnoDB lock issue。

這個問題可以朝幾個不同方向解決，其中一個方向是升級到 MySQL 5.6，對 read-only transaction 與 index merge 的判斷與效能都有改善。

另外一個方向是改善 SQL query 本身效能，讓他不要跑太久就不會有 InnoDB lock issue。這篇要討論的就是這個方法...

MySQL 在 5.0 開始就支援 Index Merge Optimization，也就是利用多個 index 的結果運算：「Index Merge Optimization」。

有三種 index merge optimization，可以在 EXPLAIN 時看到：

• Using intersect(...)
• Using union(...)
• Using sort_union(...)

其中 intersection (交集) 的部份，Percona 在兩年前有寫一篇關於 Index Merge Intersection 反而會使得效能變差的文章：「The Optimization That (Often) Isn't: Index Merge Intersection」。

以歌曲的例子來說，假設有一個表格叫做 song，裡面有千萬首歌曲的資料：

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| song_id    | int(10)    | NO   | PRI | NULL    | auto_increment |
| album_id   | int(10)    | NO   | MUL | 0       |                |
| artist_id  | int(10)    | NO   | MUL | 0       |                |
| song_valid | tinyint(1) | NO   | MUL | 0       |                |
+------------+------------+------+-----+---------+----------------+

其中 album_id 自己一個 index，artist_id 自己也一個 index，song_valid 自己也一個 index。當下這樣的 query 時就有可能會出現 index merge：

SELECT * FROM song WHERE artist_id = 10000 AND song_valid = 1;

其中 song_valid = 1 可能會佔 song 表格裡 90% 的資料，也就是 900 萬筆...

如果發生 index merge，MySQL 會先抓出 artist_id = 10000 的 song_id，以及 song_valid = 1 的 song_id 取交集，會造成大量的 seq i/o read。即使這些資料都在記憶體 cache 內，還是需要 CPU 大量運算。用 force index 的方式強迫 MySQL 直接用 artist_id 的 index 抓出來掃會快很多。

不過仔細去想，會發現有幾個解法：

• 應該對 (artist_id, song_valid) 建立 index，而非對 artist_id 單一欄位建立。這樣上面的 query 就會直接用到這個 index。
• 或是，由於 90% 的資料都是 song_valid = 1，而我們大多數也都是查 song_valid = 1，就直接把 song_valid 的 index 拔掉。

我是選擇後面這條，這是 index 愈多反而變得愈慢的 case 之一。