2016/10/15

增加 MySQL insert 速度的撇步

一次要新增大批資料到 MySQL 時,就會 MySQL 吃 CPU 又卡 I/O wait,這個時候如果沒時間吃下午茶的話實在會有點不爽 (?)

假設今天有個 table 長這個樣子:
mysql> desc users;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| email        | varchar(200)        | YES  |     | NULL    |       |
| display_name | varchar(500)        | YES  |     | NULL    |       |
| reg_date     | int(10) unsigned    | YES  |     | NULL    |       |
| last_login   | int(10) unsigned    | YES  |     | NULL    |       |
| delete_at    | int(10) unsigned    | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

登入、使用者管理等動作,我們會為以下幾個欄位加上 index 以加快查詢速度:
  • email
  • reg_date
  • last_login
  • delete_at

這個時候我們新增一筆資料時,MySQL 大概會做幾個動作:
  1. 新增一個 row
  2. 更新 index
    1. 更新 email index
    2. 更新 reg_date index
    3. 更新 last_login index
    4. 更新 delete_at index

所以如果只是把新增一筆資料,事情最多的不是建立一個 record,而是更新所有的 index。假設要新增的資料有 10000 筆,新增資料的 I/O 就當作 10000 次好了,更新 index 就另外再增加 40000 次 I/O。

若這 10000 筆資料是一次處理完,且不打算中斷,是否有發法可以減少 index 處理的次數?答案是 transaction。

當 10000 個 insertion 放在同一個 transaction 時,MySQL 會在 insertion 確認成功以後再來一次 update index。也就是說原本 update index 跑 40000 次,現在只需要跑 4 次。

由於 transaction 會造成 lock,新增資料前可以先試試看一個 transaction 要新增幾個 record,lock 的時間才不會影響到其他服務的運作。

ref:

沒有留言:

張貼留言