Best Practices for InnoDB Tables
- 用最常查詢的欄位當 primary key，沒有的話用 auto increment numbers
- 設定 Foreign key 會讓該欄位被 indexed，因此加快 join 效能。並且能傳播 updates 跟 deletes 到相關 table
- 關掉 autocommit，不然一次寫入就是一次交易，一秒可能幾百筆交易，效能會打折（受限於硬碟的寫入速度）
- 適當的使用
START TRANSACTION和COMMIT把 SQL 包起來執行，不要太小包，也不要大包到要跑好幾個小時 - 不要用
LOCK TABLES敘述 - 可以考慮 page compression 功能對你的 access pattern 有沒有（效能上的？）幫助
- 啟動時加上
--sql_mode=NO_ENGINE_SUBSTITUTION參數
InnoDB and the ACID Model
ACID 是用來確保即使軟體或硬體出錯，資料庫中的資料也不會因此亂掉。[1]藉著前人的研發基礎，我們就不用重新為了 consistency 造輪子。而且有兩種情況，你可以直接調整 MySQL 的設定以犧牲一些 reliability 來換取效能。比如你有超級可靠的硬體，或者你寫的應用程式可以接受一點掉資料的狀況。（實際案例：AWS 預設為 MySQL 8.0.30 above 以及 db.r6i / db.r5b 實例關閉 doublewrite buffer 以提供兩倍的寫入效能）
- Atomicity：交易中的操作全部完成，不然全部失敗
- Consistency：即使有 crash 資料也不會壞掉
- Isolation：多筆交易同時進行，同時發生的增刪改查不會彼此影響
- Durability：交易一旦完成，資料就會以更新過得狀態永久保留下來
InnoDB Multi-Versioning
InnoDB 為了支援交易（其實就是 ACID）會保留每個 row 的異動，並以 rollback segment 這個資料結構存在 system tablespace 或 undo tablespaces ，InnoDB 可以使用這些資訊來 rollback 交易，或者重建更新前的資料（以支援 consistent read）。
InnoDB 會在每一筆資料加入下列資訊：
- 6-byte
DB_TRX_ID：儲存最後一個 insert、update、delete 本筆資料的交易（會有一個額外的 bit 標示資料是否已被刪除） - 7-byte
DB_ROLL_PTR：aka. roll pointer，指向一個 undo log record（並以此還原出更新前的資料） - 6-byte
DB_ROW_ID：會遞增的 ID，通常等於 clustered index
Undo logs 分為 insert 以及 update 兩種：
Insert undo logs：交易結束後馬上可丟掉
Update undo logs：會參與 consistent reads，意即用來重建一筆資料更新前的狀態。因此只有在沒執行中的交易，且 InnoDB 已經設定新的 snapshot 之後才可以丟棄這資訊
InnoDB Architecture
上面這連結內有畫得很棒的架構圖
InnoDB In-Memory Structures
- Buffer pool：快取 table 及 index。Buffer pool 將資料儲存在多個 page 之中，page 之間以 linked-list 的形式連接，其中 3/8 的 page 因較少讀取所以被放在尾端，在 buffer pool 需要空間時會透過 LRU 演算法移除這些資料
- Change buffer：快取對 secondary index 的更新
- Adaptive hash index：InnoDB 用來加速用的，不過 like operator 可能無法受惠
- Log Buffer：暫存即將寫入到硬碟的資料
Clustered and Secondary Indexes
Clustered index 通常是 primary key，不然是 auto-increment 欄位，再來則是 UNIQUE NOT NULL 欄位，不然 InnoDB 會使用自己產生的 row ID。
Clustered index 以外的 index 都是 Secondary index。每個 secondary index record 都包含其對應的 primary key，所以要是 primary key 很長，secondary index 便會佔用更多空間
InnoDB Locking and Transaction Model
InnoDB Locking
- Shared and Exclusive Locks：Row-level locks，分成 Shared (S)、Exclusive (X)
- Intention Locks：在下 SQL 後、交易開始前，必須先取得欲操作範圍的 intention lock。意即要先取得 IS，才能取得 S（IX 一樣）。文件中有表格整理了 S、IS、X、IX 之間的衝突關係，簡單來說，兩個 [I]S 都沒問題、[I]S 與 [I]X 一定衝突、兩個 IX 倒是沒問題（原因是兩個 IX 鎖的可以是不同範圍，因此其下的 X 鎖不會衝突）。在取得 Intention Lock 時，若是已存在造成衝突的鎖，則會等到該鎖釋放為止。不過要是鎖下去會造成 deadlock 就會報錯。[2]
- Record Locks：對 index 的鎖。如果 table 沒有任何 index，則會鎖在隱藏的 clustered index 上。
- Gap Locks：在兩筆 index record 之間，或者一個 index record 之前或之後上鎖，防止寫入資料進這區間。同範圍可以鎖兩次，因為他們的作用一致。若是 SQL 有明確指定 index record（如
SELECT * FROM child WHERE id = 100;）則不會用到 gap lock。 - Next-Key Locks = 1x Record lock + 1x Gap lock before same index record，用來防止 Phantom rows
- Insert Intention Locks：表示寫入意圖的 gap lock
- AUTO-INC Locks：一種 table lock。預設模式下（
innodb_autoinc_lock_mode = 1）就能保障寫入資料是高效的。 - Predicate Locks for Spatial Indexes：Spatial data 版本的 next-key locks
InnoDB Transaction Model
Transaction Isolation Levels
InnoDB 實做了四種 Isolation Level：
SERIALIZABLE（最嚴，特殊場景用：XA 交易）REPEATABLE READ（預設等級，使用 snapshot isolation 及 undo log 達成 consistent read）READ COMMITTED（與上面一個等級相比，少了 gap lock，因此會出現 phantom read）READ UNCOMMITTED
關於四種 level 的差異很推薦看這篇《資料庫交易的 Isolation》[3]
不過 InnoDB 的 REPEATABLE READ 有 gap lock 及 next-key lock 保護，因此應該不會出現 phantom read？
每個對話（session）都可以指定要用哪個 Isolation level。
Consistent Nonlocking Reads
InnoDB 的 multi-versioning 機制可以呈現特定時間點（snapshot）的資料以供查詢，其原理是：查詢可以看到在其時間點前提交的交易更新，看不到在其時間點之後提交的交易更新，以及未提交的交易。（即使在你的時間點後有交易刪除了幾筆資料，但你還是查的到這幾筆資料。）不過查詢可以看到自己提交的更新。
上面的描述都是針對查詢（query），不適用於 DML，亦即你的 UPDATE 可能會更新到一些你現在看不到的資料。不過你更新這些資料之後，你就查的到他們了。
Locking Reads
承上一章，這樣子的隔離保護明顯不足，不過 InnoDB 提供額外兩項措施：SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE。他們兩者的相同之處是都允許 non-locking reads、都能防止其他對話更新我們指定的資料，相異之處是後者禁止其他對話同時 locking read 這些資料。[4]
從官方文件的範例可以歸納出兩者的使用場景如下：
- LOCK IN SHARE MODE：鎖住父，新增子，確保父資料存在，以其他們之間的關聯是完好的
- FOR UPDATE：鎖住資料，防止其他交易同時更新。能防止 DML 的更新彼此覆蓋（比如 increment）
跳過不讀的章節
- InnoDB Configuration
- InnoDB Table and Page Compression 跟效能優化有關，有空再讀
- InnoDB File-Format Management
- InnoDB Row Formats
- InnoDB Disk I/O and File Space Management 有朝一日再來讀
- InnoDB and Online DDL
- InnoDB Data-at-Rest Encryption 用 RDS 應該不用讀
- InnoDB Startup Options and System Variables 有興趣做微調時再來讀
- InnoDB INFORMATION_SCHEMA Tables
- InnoDB Integration with MySQL Performance Schema 遇到效能問題再來讀
- InnoDB Monitors
- InnoDB Backup and Recovery 用 RDS 應該不用讀
- InnoDB and MySQL Replication 用 RDS 應該不用讀
- InnoDB Troubleshooting
- InnoDB Limits
- InnoDB Restrictions and Limitations
心得
過往開發 API 都是 90% happy path 加上 10% 猜得到或用測試發現的 bad path。
以後開發，除了多做 deadlock 重試之外，還能保持幻想那隻 API 同時間執行兩次的習慣，這樣有助於設想是不是應該加 lock。
參考文件
- https://zh.wikipedia.org/zh-tw/ACID
- https://stackoverflow.com/questions/47047961/why-intention-exclusive-locks-are-compatible-with-intension-exclusive-locks-in-m
- https://blog.amis.com/database-transaction-isolation-a1e448a7736e
- https://stackoverflow.com/questions/32827650/mysql-innodb-difference-between-for-update-and-lock-in-share-mode
發佈留言