UPSERT

維基百科對 UPSERT 的說明:(取自「Merge (SQL)」條目)

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches.

MySQL 裡的兩種語法其實就是在實做這個需求:

  • REPLACE INTO ...
  • INSERT INTO ... ON DUPLICATE KEY UPDATE ...

而前者其實是後者的一個特例 (當 INSERT 發現有 dupe key 時把現有的 record 改成與 INSERT 時相同的條件)。

而計數器是後者常見的 case 之一:當 record 不存在的時候塞一筆進去,並且將 counter 設為 1;當 record 存在的時候對 counter 加一更新。像是這樣的 SQL query:

INSERT INTO my_table SET id = ?, num = 1 ON DUPLICATE KEY UPDATE num = num + 1;

由於這是常見的需求,使得這個語法是目前少數 MySQL 比 PostgreSQL 好用的地方。

在「A Case for Upserts」這篇就看到抱怨 PostgreSQL 不實做這個功能...

不過我覺得作者寫得有點誇張,INSERT INTO ... ON DUPLICATE KEY UPDATE ... 應該是可以模擬出來的功能:當 INSERT 失敗後再跑 UPDATE。而 REPLACE INTO ... 是特例,也就當然可以模擬出來。

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