MySQL：每次update一定會修改數(shù)據(jù)嗎？

作者：高鵬 2023-12-30 10:59:03

實際上在MySQL或者innodb中，實際上只會對有數(shù)據(jù)修改的索引進行實際的更改。

一、問題描述

假設(shè)我們有這樣一張表，且包含一條記錄：

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
包含記錄：
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |   11 |   12 |   13 |

這個表實際上包含3個索引：

主鍵索引（且值包含一個block）
索引c1（且值包含一個block）
索引c2（且值包含一個block）

那么我們考慮如下的語句：

A: update mytest set c1=11,c2=12,c3=13 where id=1（c1\c2\c3字段都不更改）
B: update mytest set c1=11,c2=12,c3=14 where id=1（c1\c2字段不更改）
C: update mytest set c1=12,c2=12,c3=14 where id=1（c2字段不更改）

那么問題如下：

A 場景下各個索引的值是否更改，也就是實際的各個索引block是否更改。
B 場景下索引c1和索引c2的數(shù)據(jù)是否更改，也就是實際的索引c1和索引c2的block是否更改。
C 場景下索引c2的數(shù)據(jù)是否更改，也就是實際索引c2的block是否更改。

二、大概的半段方式和流程

對于update語句來講，函數(shù)mysql_update對修改流程大概如下：

掃描數(shù)據(jù)，獲取數(shù)據(jù)(rr_sequential)，存儲mysql格式的數(shù)據(jù)到record[0]中，其表示大概如下：

field1 | field2 | … | fieldN

每個field都包含一個指向?qū)嶋H數(shù)據(jù)的指針。

保存獲取的mysql格式的數(shù)據(jù)到record[1]中，然后使用語法解析后的信息填充獲取的record[0]中的數(shù)據(jù)（fill_record_n_invoke_before_triggers->fill_record），這里就是使用c1=,c2=,c3=*填充數(shù)據(jù)，需要填充的數(shù)據(jù)和字段實際上保存在兩個List中分別為Item_feild和Item_int類型的鏈表我們這里就叫做column_list和values_list，它們在bsion規(guī)則文件中使用如下表示：

                $$.column_list->push_back($1.column) ||
                $$.value_list->push_back($1.value))

下面使用語句update mytest set c1=11,c2=12,c3=13 where id=1來debug一下這個兩個list，我們斷點放到fill_record_n_invoke_before_triggers就可以了，

(gdb) p fields
$67 = (List<Item> &) @0x7fff30005da8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff300067f8, last = 0x7fff30006af8, elements = 3}, <No data fields>}
(gdb) p ((Item_field *)(fields->first->info)).field_name
$68 = 0x7fff309316d4 "c1"
(gdb) p ((Item_field *)(fields->first->next->info)).field_name
$69 = 0x7fff309316d7 "c2"
(gdb) p ((Item_field *)(fields->first->next->next->info)).field_name
$70 = 0x7fff309316da "c3"
(gdb) p values
$73 = (List<Item> &) @0x7fff30006e38: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff30006808, last = 0x7fff30006b08, elements = 3}, <No data fields>}
(gdb) p ((Item_int*)(values->first->info)).value
$74 = 11
(gdb) p ((Item_int*)(values->first->next->info)).value
$75 = 12
(gdb) p ((Item_int*)(values->first->next->next->info)).value
$76 = 13

這樣修改后record[0]中需要修改的字段的值就變?yōu)榱吮敬蝩pdate語句中的值。

過濾點1，比對record[0]和record[1] 中數(shù)據(jù)是否有差異，如果完全相同則不觸發(fā)update，這里也就對應(yīng)我們的場景A，因為前后記錄的值一模一樣，因此是不會做任何數(shù)據(jù)更改的，這里直接跳過了*。

到這里肯定是要修改數(shù)據(jù)的，因此對比record[0]和record[1]的記錄，將需要修改的字段的值和字段號放入到數(shù)組m_prebuilt->upd_node->update中（calc_row_difference），其中主要是需要修改的new值和需要修改的field_no比對方式為：

長度是否更改了(len)
實際值更改了(memcmp比對結(jié)果)

確認修改的字段是否包含了二級索引。因為前面已經(jīng)統(tǒng)計出來了需要更改的字段（row_upd的開頭），那么這里對比的方式如下：

如果為delete語句顯然肯定包含所有的二級索引
如果為update語句，根據(jù)前面數(shù)組中字段的號和字典中字段是否排序進行比對，因為二級索引的字段一定是排序的如果兩個條件都不滿足

如果兩個條件都不滿足，這說明沒有任何二級索引在本次修改中需要修改，設(shè)置本次update的標記為UPD_NODE_NO_ORD_CHANGE，UPD_NODE_NO_ORD_CHANGE則代表不需要修改任何二級索引字段。注意這里還會轉(zhuǎn)換為innodb的行格式（row_mysql_store_col_in_innobase_format）。

過濾點2，先修改主鍵，如果為UPD_NODE_NO_ORD_CHANGE update這不做二級索引更改，也就是不調(diào)用row_upd_sec_step函數(shù)，這是顯然的，因為沒有二級索引的字段需要更改（函數(shù)row_upd_clust_step中實現(xiàn)），這里對應(yīng)了場景B，雖然 c3字段修改了數(shù)據(jù)，但是c1\c2字段前后的值一樣，所以實際索引c1和索引c2不會更改，只修改主鍵索引。

如果需要更改二級索引，依次掃描字典中的每個二級索引循環(huán)開啟。

過濾點3首選需要確認修改的二級索引字段是否在本索引中，如果修改的字段根本就沒有在這個二級索引中，顯然不需要修改本次循環(huán)的索引了。而這個判斷在函數(shù)row_upd_changes_ord_field_binary中，方式為循環(huán)字典中本二級索引的每個字段判定，

如果本字段不在m_prebuilt->upd_node->update數(shù)組中，直接進行下一個字段，說明本字段不需要修改
如果本字段在m_prebuilt->upd_node->update數(shù)組中，這進行調(diào)用函數(shù)dfield_datas_are_binary_equal進行比較，也就是比較實際的值是否更改

這里實際上對應(yīng)了我們的場景3，因為c2字段的值沒有更改，因此索引c2不會做實際的更改，但是主鍵索引和索引c1需要更改值。

三、結(jié)論

從代碼中我們可以看到，實際上在MySQL或者innodb中，實際上只會對有數(shù)據(jù)修改的索引進行實際的更改。那么前面提到的幾個場景如下：

A: update mytest set c1=11,c2=12,c3=13 where id=1（c1\c2\c3字段都不更改）不做任何數(shù)據(jù)修改
B: update mytest set c1=11,c2=12,c3=14 where id=1（c1\c2字段不更改）只更改主鍵索引
C: update mytest set c1=12,c2=12,c3=14 where id=1（c2字段不更改）只更改主鍵索引和索引c1

四、驗證

對于驗證我們驗證場景3，這里主要通過block的last_modify_lsn進行驗證，因為一個block只要修改了數(shù)據(jù)，臟數(shù)據(jù)刷盤后其last_modify_lsn一定會修改，步驟如下：

初始化數(shù)據(jù) 這里mytest表為測試表，而mytest2表主要的作用是修改數(shù)據(jù)推進lsn：

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB;
 insert into mytest values(1,11,12,13);
 insert into mytest values(2,14,15,16);
 insert into mytest values(3,17,18,19);
 insert into mytest values(4,20,21,22);
 insert into mytest values(5,23,24,25);
 insert into mytest values(6,26,27,28);
 insert into mytest values(7,29,30,31);
 insert into mytest values(8,32,33,34);
 insert into mytest values(9,35,36,37);
 insert into mytest values(10,38,39,40);
CREATE TABLE `mytest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO mytest2(c1) values(1);

(1) 記錄當前l(fā)sn

由于是測試庫show engine的lsn是靜止的如下 Log sequence number 4806780238 Log flushed up to 4806780238 Pages flushed up to 4806780238 且 Modified db pages 0 沒有臟頁，都說明臟數(shù)據(jù)全部刷盤了。

(2) 查詢各個索引對應(yīng)block

mysql> select *from information_schema.INNODB_SYS_TABLES where NAME like 'testnew/mytest%';
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
|    19071 | testnew/mytest  |   33 |      7 | 10854 | Barracuda   | Dynamic    |             0 | Single     |
|    19072 | testnew/mytest2 |   33 |      5 | 10855 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.INNODB_SYS_INDEXES where space=10854;
+----------+---------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+---------+----------+------+----------+---------+-------+-----------------+
|    10957 | PRIMARY |    19071 |    3 |        1 |       3 | 10854 |              50 |
|    10958 | c1      |    19071 |    0 |        1 |       4 | 10854 |              50 |
|    10959 | c2      |    19071 |    0 |        1 |       5 | 10854 |              50 |
+----------+---------+----------+------+----------+---------+-------+-----------------+
3 rows in set (0.01 sec)

這里找到INDEX_ID 10957 主鍵，10958 c1 索引，10959 c2 索引。

./innblock mytest.ibd scan 16
===INDEX_ID:10957
level0 total block is (1)
block_no:         3,level:   0|*|
===INDEX_ID:10958
level0 total block is (1)
block_no:         4,level:   0|*|
===INDEX_ID:10959
level0 total block is (1)
block_no:         5,level:   0|*|

這里我們發(fā)現(xiàn) 10957的block為3 ,10958的block為4，10959的block為5，下面分別獲取他們的信息。

使用blockinfo工具查看當前mytest各個block的lsn：

10957 PRIMARY block 3：

./innblock mytest.ibd 3 16
----------------------------------------------------------------------------------------------------
==== Block base info ====
block_no:3          space_id:10854        index_id:10957       
...     
last_modify_lsn:4806771220 (注意這里)
page_type:B+_TREE level:0

10958 c1 block 4

./innblock mytest.ibd 4 16

----------------------------------------------------------------------------------------------------
==== Block base info ====
block_no:4          space_id:10854        index_id:10958       
...     
last_modify_lsn:4806771252(注意這里)

10959 c2 block 5

./innblock mytest.ibd 5 16

----------------------------------------------------------------------------------------------------
==== Block base info ====
block_no:5          space_id:10854        index_id:10959       
  
last_modify_lsn:4806771284(注意這里)

這里我們就將3個page的last_modify_lsn獲取到了大概在4806771200附近。

(3) mytest2表做一些數(shù)據(jù)修改推進lsn

INSERT INTO mytest2(c1) select c1 from mytest2;
INSERT INTO mytest2(c1) select c1 from mytest2;
...
INSERT INTO mytest2(c1) select c1 from mytest2;
Query OK, 32768 rows affected (13.27 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> select count(*) from mytest2;
+----------+
| count(*) |
+----------+
|    65536 |
+----------+
1 row in set (1.46 sec)

(4) 再次查看系統(tǒng)的lsn

Log sequence number 4867604378
Log flushed up to   4867604378
Pages flushed up to 4867604378
Modified db pages  0

這個時候lsn變化了，但是臟數(shù)據(jù)已經(jīng)刷臟。

(5) 對mytest表進行修改

修改這行記錄 id c1 c2 c3 2 14 15 16

update t1 set c1=14,c2=115,c3=116 where id=2;

我們保持c1不變化，預期如下： index：10957 PRIMARY block 3:last_modify_lsn 在4867604378附近 index：10958 c1 block 4:last_modify_lsn 保持4806771252不變，因為前面的理論表名不會做修改 index：10959 c2 block 5:last_modify_lsn 在4867604378附近.

(6) 最終結(jié)果符合預期截圖如下

五、代碼流程

mysql_update
  ->rr_sequential
返回數(shù)據(jù)到record0
保存record0數(shù)據(jù)到record1
  ->fill_record_n_invoke_before_triggers
   ->fill_record
  修改record0的數(shù)據(jù)，根據(jù)語法解析后得到修改的字段的信息更改recrod0
  做讀取操作，獲取需要更改行的位置，返回整行數(shù)據(jù)
  if (!records_are_comparable(table) || compare_records(table))
  ----過濾點一:比對整行數(shù)據(jù)和需要修改后的行數(shù)據(jù)是否相同，不相同則不需要進行以下調(diào)用
  ->handler::ha_update_row
    ->ha_innobase::update_row
     ->calc_row_difference
       將需要修改的字段的值和字段號放入到數(shù)組中(m_prebuilt->upd_node->update)
       方式:o_len != n_len || (o_len != UNIV_SQL_NULL &&0 != memcmp(o_ptr, n_ptr, o_len))
         A、長度是否更改了(len)
         B、實際值更改了(memcmp比對結(jié)果)
       因為前面過濾點一對比了是否需要更改，這里肯定是需要更改的，只是看哪些字段需要修改。
     ->row_update_for_mysql
       ->row_update_for_mysql_using_upd_graph
         ->row_upd_step
          ->row_upd
            首先確認修改的字段是否包含二級索引。
            方式:(node->is_delete|| row_upd_changes_some_index_ord_field_binary(node->table, node->update))
            A、如果為delete語句顯然肯定包含所有的二級索引
            B、如果為update語句，根據(jù)前面數(shù)組中字段的號和字典中字段是否排序進行比對，因為二級索引的字段一定是排序的
            如果兩個條件都不滿足，這說明沒有任何二級索引在本次修改中需要修改，設(shè)置本次update為UPD_NODE_NO_ORD_CHANGE
            UPD_NODE_NO_ORD_CHANGE則代表不需要修改任何二級索引字段。
            ->row_upd_clust_step
              先修改主鍵
            ----過濾點二:如果為UPD_NODE_NO_ORD_CHANGE update這不做二級索引更改，這是顯然的，因為沒有二級索引的字段
                需要更改
   
            如果需要更改二級索引，依次掃描字典中的每個二級索引循環(huán)開啟：
            while (node->index != NULL)
              ->row_upd_sec_step
                首選需要確認修改的二級索引字段是否在本索引中
                方式:if (node->state == UPD_NODE_UPDATE_ALL_SEC|| 
                     row_upd_changes_ord_field_binary(node->index, node->update,thr, node->row, node->ext))
                考慮函數(shù)row_upd_changes_ord_field_binary
                ->row_upd_changes_ord_field_binary
                  循環(huán)字典中本二級索引的每個字段判定
                  A、如果本字段不在m_prebuilt->upd_node->update數(shù)組中，直接進行下一個字段，說明本字段不需要修改
                  B、如果本字段在m_prebuilt->upd_node->update數(shù)組中，這進行實際使用dfield_datas_are_binary_equal
                     進行比較
                  如果不滿足上面的條件說明整個本二級索引沒有需要修改的字段，返回false
                ----過濾點三:如果需要本二級索引沒有需要更改的字段則不進行實際的修改了，如果需要更改則調(diào)用
                ->row_upd_sec_index_entry
                  做實際的修改.......

責任編輯：趙寧寧來源： MySQL學習