前陣子在看資料庫寫入會卡住時找到的問題,主要是 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 之一。