技術分享 | MySQL:change buffer 何時生效

愛可生雲資料庫 發表於 2022-01-27
MySQL

作者:胡呈清

愛可生 DBA 團隊成員,擅長故障分析、效能優化,個人部落格:https://www.jianshu.com/u/a95...,歡迎討論。

本文來源:原創投稿

*愛可生開源社群出品,原創內容未經授權不得隨意使用,轉載請聯絡小編並註明來源。


已知 change buffer 的原理

參考資料:https://juejin.im/post/684490...

對於普通二級索引,當插入、修改、刪除二級索引記錄時,即使資料不在 innodb buffer pool 中,也不需要先把資料從磁碟讀取到記憶體。只需要在 change buffer 中完成 DML 操作,下次讀取時才會從磁碟讀取資料頁到記憶體,並與 change buffer 進行 merge,從而得到正確的資料。這減少了 DML 時的隨機 IO。

疑問

按照上述原理,使用 change buffer 二級索引不需要讀取磁碟,那 delete、update 是如何得到 affected rows 的?

不妨先作出假設:

  • 如果 delete、update 是以主鍵、唯一索引做為篩選條件,則讀取磁碟或者 innodb buffer pool 中的主鍵、唯一索引來確定 affected rows。對於普通索引頁上記錄的刪除或者修改,還是直接使用 change buffer,不需要單獨將普通索引頁從磁碟上讀取到記憶體。
  • 如果 delete、update 是以普通二級索引做為篩選條件,以 delete 為例(update 內部實現是先 delete 再 insert):delete from t where a=100; 如果索引頁不在記憶體中,則需要先從磁碟讀取 a 索引,找到 a = 100 的記錄對應的 id(主鍵值),再從磁碟掃描主鍵索引(回表)將 id 滿足條件的記錄讀取到記憶體。然後在 innodb buffer pool 中把對應的主鍵索引頁、二級索引頁中的記錄刪除。這裡不使用 change buffer。

驗證

接下來設計兩個實驗來驗證上述假設。

實驗1-以主鍵為篩選條件做 delete

用 sysbench 造一張 100 萬行的表,表中有一個主鍵和一個普通索引:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
);

重啟 mysqld ,清空 innodb buffer pool,注意引數:

innodb_buffer_pool_size = 64M
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_dump_pct = 0

執行 delete,並使用show engine innodb status\G觀察INSERT BUFFER AND ADAPTIVE HASH INDEX 部分資訊,判斷是否使用 change buffer:

mysql> delete from sbtest1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> delete from sbtest1 where id=2;
Query OK, 1 row affected (0.00 sec)
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 2 merges
merged operations:
 insert 0, delete mark 2, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> delete from sbtest1 where id=3;
Query OK, 1 row affected (0.00 sec) 
 -------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 3 merges
merged operations:
 insert 0, delete mark 3, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
mysql> select * from sbtest1 where id=4;
mysql> delete from sbtest1 where id=4;
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 4 merges
merged operations:
 insert 0, delete mark 4, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

上述實驗說明:如果 delete 是以主鍵做為篩選條件,對於普通索引k,如果索引頁不在記憶體中(select * from sbtest1 where id=4 讀取的只是主鍵索引頁,不會讀取k索引頁),會使用 change buffer(每次 delete 後,delete mark 都增加1)。

實驗2-以普通索引為篩選條件做 delete

重新造資料,重啟 mysqld 清空 buffer pool。下面實驗結果說明:如果 delete 以普通索引做為篩選條件,對於普通索引k,如果索引頁不在記憶體中,不會使用 change buffer。言外之意就是需要讀取磁碟了。

##delete where id=1,delete mark +1,說明使用了change buffer
mysql>  delete from sbtest1 where id=1;
Query OK, 1 row affected (0.01 sec)
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
 
##delete where k=367246,delete mark 不變,說明沒有使用change buffer
mysql> select * from sbtest1 where id=2;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  2 | 367246 | 42909700340-70078987867-62357124096-35495169193-85675377266-14643719347-30417020186-80900182681-50382374444-66260611196 | 74781290517-41121402981-50604677924-34464478849-89102349959 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> delete from sbtest1 where k=367246;
Query OK, 1 row affected (0.01 sec)

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 29, seg size 31, 1 merges
merged operations:
 insert 0, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0