作者：Yves Trudeau
前言
作為 Percona 的首席架構師，我的主要職責之一是對客戶的數據庫進行性能方面的優化，這使得工作複雜且非常有趣。在這篇文章中，我想討論一個最重要的問題：選擇最佳的 InnoDB 主鍵。
InnoDB 主鍵有什麼特別之處？
InnoDB 被稱為索引組織型的存儲引擎。主鍵使用的 B-Tree 來存儲數據，即錶行。這意味著 InnoDB 必須使用主鍵。如果表沒有主鍵，InnoDB 會向表中添加一個隱藏的自動遞增的 6 字節計數器，並使用該隱藏計數器作為主鍵。InnoDB 的隱藏主鍵存在一些問題。您應該始終在表上定義顯式主鍵，並通過主鍵值訪問所有 InnoDB 行。
InnoDB 的二級索引也是一個B-Tree。搜索關鍵字由索引列組成，存儲的值是匹配行的主鍵。通過二級索引進行搜索通常會導致主鍵的隱式搜索。
什麼是 B-Tree？
一個 B-Tree 是一種針對在塊設備上優化操作的數據結構。塊設備或磁盤有相當重要的數據訪問延遲，尤其是機械硬盤。在隨機位置檢索單個字節並不比檢索更大的數據花費的時間更少。這是 B-Tree 的基本原理，InnoDB 使用的數據頁為 16KB。
讓我們嘗試簡化 B-Tree 的描述。B-Tree 是圍繞這鍵來組織的數據結構。鍵用於搜索 B-Tree 內的數據。B-Tree 通常有多個級別。數據僅存儲在最底層，即葉子節點。其他級別的頁面（節點）僅包含下一級別的頁面的鍵和指針。
如果要訪問鍵值的數據，則從頂級節點-根節點開始，將其包含的鍵與搜索值進行比較，並找到要在下一級訪問的頁面。重複這個過程，直到你達到最後一個級別，即葉子節點。理論上，每個 B-Tree 級別的讀取都需要一次磁盤讀取操作。在實踐中，總是有內存緩存節點，因為它們數量較少且經常訪問，因此適合緩存。
作者：Yves Trudeau
前言
作為 Percona 的首席架構師，我的主要職責之一是對客戶的數據庫進行性能方面的優化，這使得工作複雜且非常有趣。在這篇文章中，我想討論一個最重要的問題：選擇最佳的 InnoDB 主鍵。
InnoDB 主鍵有什麼特別之處？
InnoDB 被稱為索引組織型的存儲引擎。主鍵使用的 B-Tree 來存儲數據，即錶行。這意味著 InnoDB 必須使用主鍵。如果表沒有主鍵，InnoDB 會向表中添加一個隱藏的自動遞增的 6 字節計數器，並使用該隱藏計數器作為主鍵。InnoDB 的隱藏主鍵存在一些問題。您應該始終在表上定義顯式主鍵，並通過主鍵值訪問所有 InnoDB 行。
InnoDB 的二級索引也是一個B-Tree。搜索關鍵字由索引列組成，存儲的值是匹配行的主鍵。通過二級索引進行搜索通常會導致主鍵的隱式搜索。
什麼是 B-Tree？
一個 B-Tree 是一種針對在塊設備上優化操作的數據結構。塊設備或磁盤有相當重要的數據訪問延遲，尤其是機械硬盤。在隨機位置檢索單個字節並不比檢索更大的數據花費的時間更少。這是 B-Tree 的基本原理，InnoDB 使用的數據頁為 16KB。
讓我們嘗試簡化 B-Tree 的描述。B-Tree 是圍繞這鍵來組織的數據結構。鍵用於搜索 B-Tree 內的數據。B-Tree 通常有多個級別。數據僅存儲在最底層，即葉子節點。其他級別的頁面（節點）僅包含下一級別的頁面的鍵和指針。
如果要訪問鍵值的數據，則從頂級節點-根節點開始，將其包含的鍵與搜索值進行比較，並找到要在下一級訪問的頁面。重複這個過程，直到你達到最後一個級別，即葉子節點。理論上，每個 B-Tree 級別的讀取都需要一次磁盤讀取操作。在實踐中，總是有內存緩存節點，因為它們數量較少且經常訪問，因此適合緩存。
一個簡單的三級 B-Tree 結構
有序的插入示例
讓我們考慮以下 sysbench 表：
mysql> show create table sbtest1\\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show table status like 'sbtest1'\\G
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2882954
Avg_row_length: 234
Data_length: 675282944
Max_data_length: 0
Index_length: 47775744
Data_free: 3145728
Auto_increment: 3000001
Create_time: 2018-07-13 18:27:09
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Data_length 值是 B-Tree 主鍵的大小。B-Tree 的二級索引，即 k_1 索引，Index_length 是其大小。因為 ID 主鍵自增，所以 sysbench 表數據是順序插入的。當按主鍵順序插入時，即使 innodb_fill_factor 設為 100，InnoDB 最多使用 15KB 的數據填充空間。這導致在初始插入數據之後，需要拆分頁面。頁面中還有一些頁眉和頁腳。如果頁面太滿且無法添加更多數據，則頁面將拆分為兩個。同樣，如果兩個相鄰頁面的填充率低於 50％，InnoDB 將合併它們。例如，這是以 ID 順序插入的 sysbench 表：
mysql> select count(*), TABLE_NAME,INDEX_NAME, avg(NUMBER_RECORDS), avg(DATA_SIZE) from information_schema.INNODB_BUFFER_PAGE
-> WHERE TABLE_NAME='`sbtest`.`sbtest1`' group by TABLE_NAME,INDEX_NAME order by count(*) desc;
+----------+--------------------+------------+---------------------+----------------+
| count(*) | TABLE_NAME | INDEX_NAME | avg(NUMBER_RECORDS) | avg(DATA_SIZE) |
+----------+--------------------+------------+---------------------+----------------+
| 13643 | `sbtest`.`sbtest1` | PRIMARY | 75.0709 | 15035.8929 |
| 44 | `sbtest`.`sbtest1` | k_1 | 1150.3864 | 15182.0227 |
+----------+--------------------+------------+---------------------+----------------+
2 rows in set (0.09 sec)
mysql> select PAGE_NUMBER,NUMBER_RECORDS,DATA_SIZE,INDEX_NAME,TABLE_NAME from information_schema.INNODB_BUFFER_PAGE
-> WHERE TABLE_NAME='`sbtest`.`sbtest1`' order by PAGE_NUMBER limit 1;
+-------------+----------------+-----------+------------+--------------------+
| PAGE_NUMBER | NUMBER_RECORDS | DATA_SIZE | INDEX_NAME | TABLE_NAME |
+-------------+----------------+-----------+------------+--------------------+
| 3 | 35 | 455 | PRIMARY | `sbtest`.`sbtest1` |
+-------------+----------------+-----------+------------+--------------------+
1 row in set (0.04 sec)
mysql> select PAGE_NUMBER,NUMBER_RECORDS,DATA_SIZE,INDEX_NAME,TABLE_NAME from information_schema.INNODB_BUFFER_PAGE
-> WHERE TABLE_NAME='`sbtest`.`sbtest1`' order by NUMBER_RECORDS desc limit 3;
+-------------+----------------+-----------+------------+--------------------+
| PAGE_NUMBER | NUMBER_RECORDS | DATA_SIZE | INDEX_NAME | TABLE_NAME |
+-------------+----------------+-----------+------------+--------------------+
| 39 | 1203 | 15639 | PRIMARY | `sbtest`.`sbtest1` |
| 61 | 1203 | 15639 | PRIMARY | `sbtest`.`sbtest1` |
| 37 | 1203 | 15639 | PRIMARY | `sbtest`.`sbtest1` |
+-------------+----------------+-----------+------------+--------------------+
3 rows in set (0.03 sec)
該表不適合緩衝池，但查詢為我們提供了很好的解釋。 B-Tree 主鍵的頁面平均有 75 條記錄，並存儲少於 15KB 的數據。sysbench 以隨機順序插入索引 k_1。sysbench 在插入行之後創建索引並且 InnoDB 使用排序文件來創建它。
您可以輕鬆估算 InnoDB B-Tree 中的級別數。上表需要大約 40K 頁（3M / 75）。當主鍵是四字節整數時，每個節點頁面保持大約 1200 個指針。因此葉子上層大約有 35 頁，然後在 B-Tree 上的根節點（PAGE_NUMBER = 3）我們總共有三個層級。
一個隨機插入的例子
如果你是一個敏銳的觀察者，你意識到以主鍵的隨機順序插入頁面通常是不連續的，平均填充係數僅為 65-75％ 左右。我修改了 sysbench 以隨機的 ID 順序插入並創建了一個表，也有 3M行。結果表格要大得多：
mysql> show table status like 'sbtest1'\\G
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3137367
Avg_row_length: 346
Data_length: 1088405504
Max_data_length: 0
Index_length: 47775744
Data_free: 15728640
Auto_increment: NULL
Create_time: 2018-07-19 19:10:36
Update_time: 2018-07-19 19:09:01
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
雖然以 ID 的順序插入 B-Tree 主鍵的大小是 644MB，但是以隨機順序插入的大小約為 1GB，多了 60％。顯然，我們的頁面填充係數較低：
mysql> select count(*), TABLE_NAME,INDEX_NAME, avg(NUMBER_RECORDS), avg(DATA_SIZE) from information_schema.INNODB_BUFFER_PAGE
-> WHERE TABLE_NAME='`sbtestrandom`.`sbtest1`'group by TABLE_NAME,INDEX_NAME order by count(*) desc;
+----------+--------------------------+------------+---------------------+----------------+
| count(*) | TABLE_NAME | INDEX_NAME | avg(NUMBER_RECORDS) | avg(DATA_SIZE) |
+----------+--------------------------+------------+---------------------+----------------+
| 4022 | `sbtestrandom`.`sbtest1` | PRIMARY | 66.4441 | 10901.5962 |
| 2499 | `sbtestrandom`.`sbtest1` | k_1 | 1201.5702 | 15624.4146 |
+----------+--------------------------+------------+---------------------+----------------+
2 rows in set (0.06 sec)
隨機順序插入時，主鍵頁現在只填充了大約 10KB 的數據（~66％）這是正常和預期的結果。對於某些工作負載情況而言，這很糟糕。
確定工作負載類型
第一步是確定工作負載類型。當您有一個插入密集型工作負載時，很可能頂級查詢是在一些大型表上插入的，並且數據庫會大量寫入磁盤。如果在 MySQL 客戶端中重複執行“show processlist;”，則會經常看到這些插入。這是典型的應用程序記錄大量數據。有許多數據收集器，他們都等待插入數據。如果等待時間過長，可能會丟失一些數據。如果您在插入時間上有嚴格的等級協議，而在讀取時間上有鬆弛的等級協議，那麼您顯然有一個面向插入的工作負載，您應該按主鍵的順序插入行。
也可以在大型表上具有不錯的插入速率，但這些插入是按批處理排隊並執行的。沒有人真的在等待這些插入完成，服務器可以輕鬆跟上插入的數量。對於您的應用程序而言，重要的是大量的讀取查詢將進入大型表，而不是插入。您已經完成了查詢調優，即使您有良好的索引，數據庫也會以非常高的速率從磁盤讀取數據。
當您查看 MySQL 進程列表時，您會在大表上看到多次相同的選擇查詢表單。唯一的選擇似乎是添加更多內存來降低磁盤讀取次數，但是這些表正在快速增長，並且您無法永久地添加內存。
如果您無法確定是否存在插入量大或讀取繁重的工作負載，那麼您可能只是沒有大的工作量。在這種情況下，默認是使用有序插入，而使用 MySQL 實現此目的的最佳方法是通過自動增量整數主鍵。這是許多 ORM 的默認行為。
讀密集型工作負載
我曾看到了很多讀密集型工作負載，主要是在線遊戲和社交網絡應用程序。最重要的是，一些遊戲具有社交網絡功能，例如：在遊戲進行過程中觀看朋友的分數。在我們進一步討論之前，我們首先需要確認讀取效率低下。當讀取效率低下時，頂部選擇查詢表單將訪問許多不同的 InnoDB 頁面，這些頁面接近於檢查的行數。用 pt-query-digest 工具對 MySQL 慢日誌進行分析，詳細級別包括 “InnoDB” 時，會暴露這兩個數量。這是一個示例輸出（我刪除了一些行）：
# Query 1: 2.62 QPS, 0.00x concurrency, ID 0x019AC6AF303E539E758259537C5258A2 at byte 19976
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-07-19T20:28:02 to 2018-07-19T20:28:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 48 55
# Exec time 76 93ms 637us 3ms 2ms 2ms 458us 2ms
# Lock time 100 10ms 72us 297us 182us 247us 47us 176us
# Rows sent 100 1.34k 16 36 25.04 31.70 4.22 24.84
# Rows examine 100 1.34k 16 36 25.04 31.70 4.22 24.84
# Rows affecte 0 0 0 0 0 0 0 0
# InnoDB:
# IO r bytes 0 0 0 0 0 0 0 0
# IO r ops 0 0 0 0 0 0 0 0
# IO r wait 0 0 0 0 0 0 0 0
# pages distin 100 1.36k 18 35 25.31 31.70 3.70 24.84
# EXPLAIN /*!50100 PARTITIONS*/
select * from friends where user_id = 1234\\G
該 friends 表的定義是：
CREATE TABLE `friends` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`friend_user_id` int(10) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id_friend` (`user_id`,`friend_user_id`),
KEY `idx_friend` (`friend_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=144002 DEFAULT CHARSET=latin1
我在測試服務器上構建了這個簡單的例子。該表很容易適合內存，因此沒有磁盤讀取。這裡重要的是“page distin”和“Rows Examine”之間的關係。如您所見，該比率接近 1 。這意味著 InnoDB 很少每頁訪問一行。對於給定的 user_id 值，匹配的行分散在 B-Tree 主鍵上。我們可以通過查看示例查詢的輸出來確認這一點：
mysql> select * from friends where user_id = 1234 order by id limit 10;
+-------+---------+----------------+---------------------+--------+
| id | user_id | friend_user_id | created | active |
+-------+---------+----------------+---------------------+--------+
| 257 | 1234 | 43 | 2018-07-19 20:14:47 | 1 |
| 7400 | 1234 | 1503 | 2018-07-19 20:14:49 | 1 |
| 13361 | 1234 | 814 | 2018-07-19 20:15:46 | 1 |
| 13793 | 1234 | 668 | 2018-07-19 20:15:47 | 1 |
| 14486 | 1234 | 1588 | 2018-07-19 20:15:47 | 1 |
| 30752 | 1234 | 1938 | 2018-07-19 20:16:27 | 1 |
| 31502 | 1234 | 733 | 2018-07-19 20:16:28 | 1 |
| 32987 | 1234 | 1907 | 2018-07-19 20:16:29 | 1 |
| 35867 | 1234 | 1068 | 2018-07-19 20:16:30 | 1 |
| 41471 | 1234 | 751 | 2018-07-19 20:16:32 | 1 |
+-------+---------+----------------+---------------------+--------+
10 rows in set (0.00 sec)
行通常由數千個 ID 值分開。雖然行很小，大約 30 個字節，但 InnoDB 頁面不包含超過 500行。隨著應用程序變得流行，用戶越來越多，表大小也越來越接近用戶數的平方。一旦表格超過 InnoDB 緩衝池限制，MySQL 就開始從磁盤讀取。更糟糕的情況是，沒有緩存，我們需要每個 friend 的 IOPS。如果這些要求的速率是平均 300條/秒而言，每個用戶有 100 個朋友，則 MySQL 需要每秒訪問多達 30000 個頁面。顯然，這不符合長期規劃。
我們需要確定訪問表的所有條件。為此，我使用 pt-query-digest 並且我提高了返回的查詢表單數量的限制。假設我發現：
- 93％ 訪問 userid
- 5％ 訪問 friendid
- 2％ 訪問 id
上述比例非常普遍。當存在顯性訪問模式時，我們可以做一些事情。朋友表關係是多對多的。使用 InnoDB，我們應該將這些表定義為：
CREATE TABLE `friends` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`friend_user_id` int(10) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`user_id`,`friend_user_id`),
KEY `idx_friend` (`friend_user_id`),
KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=144002 DEFAULT CHARSET=latin1
現在，行在 B-Tree 主鍵由 user_id 排序分組，但按隨機順序插入。換句話說，我們減慢了插入速度，使得表中的 select 語句受益。要插入一行，InnoDB 可能需要一個磁盤讀取來獲取新行所在的頁面和一個磁盤寫入以將其保存回磁盤。我們使表變得更大，InnoDB 頁數不夠多，二級索引更大，因為主鍵更大。我們還添加了二級索引。現在我們 InnoDB 的緩衝池中數據更少了。
我們會因為緩衝池中的數據較少而感到恐慌嗎？不，因為現在當 InnoDB 從磁盤讀取頁面時，它不會只獲得一個匹配的行，而是獲得數百個匹配的行。IOPS 的數量不再與朋友數量與 select 語句的速率相關聯。它現在只是 select 語句傳入速率的一個因素。沒有足夠的內存來緩存所有表的影響大大減少了。只要存儲可以執行比 select 語句的速率更多的 IOPS 次數。使用修改後的表，pt-query-digest 輸出的相關行：
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Rows examine 100 1.23k 16 34 23.72 30.19 4.19 22.53
# pages distin 100 111 2 5 2.09 1.96 0.44 1.96
使用新的主鍵，而不是讀 30k 的 IOPS，MySQL 只需要執行大約讀 588 次的 IOPS（~300 * 1.96）。這是一個更容易處理的工作量。插入的開銷更大，但如果它們的速率為100次/秒，則在最壞的情況下它意味著讀 100次 的 IOPS 和寫入 100次的 IOPS。
當存在明確的訪問模式時，上述策略很有效。最重要的是，這裡有一些其他例子，其中通常有顯著的訪問模式：
- 遊戲排行榜（按用戶）
- 用戶偏好（按用戶）
- 消息應用程序（來自或來自）
- 用戶對象存儲（按用戶）
- 喜歡物品（按項目）
- 項目評論（按項目）
當您沒有顯性訪問模式時，您可以做些什麼？一種選擇是使用覆蓋指數。覆蓋索引需要涵蓋所有必需的列。列的順序也很重要，因為第一個必須是分組值。另一種選擇是使用分區在數據集中創建易於緩存的熱點。
我們在本文中看到了用於解決讀密集型工作負載的常用策略。此策略不能始終有效 - 您必須通過通用模式訪問數據。但是當它工作時，你選擇了好的 InnoDB 主鍵，你就成了此刻的英雄！
原文鏈接：https://www.percona.com/blog/2018/07/26/tuning-innodb-primary-keys/