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 之一。

Related Posts:

This entry was posted in Computer, Database, Murmuring, MySQL, Software and tagged , , , , . Bookmark the permalink.
  • dahan

    前陣子在看資料庫寫入會卡住時找到的問題,主要是 SELECT SQL query 跑太久造成 InnoDB lock issue。 是指Locking Reads? (SELECT … FOR UPDATE and SELECT … LOCK IN SHARE MODE )