網(wǎng)易視頻云：不同執(zhí)行計(jì)劃下Mysql多表更新結(jié)果不一致的現(xiàn)象

1、網(wǎng)易視頻云：不同執(zhí)行計(jì)劃下Mysql多表更新結(jié)果不一致的現(xiàn)象網(wǎng)易視頻云是網(wǎng)易傾力打造的一款基于云計(jì)算的分布式多媒體處理集群和專業(yè)音視頻技術(shù)，為客戶提供穩(wěn)定流暢、低時(shí)延、高并發(fā)的視頻直播、錄制、存儲(chǔ)、轉(zhuǎn)碼及點(diǎn)播等音視頻的PASS服務(wù)。在線教育、遠(yuǎn)程醫(yī)療、娛樂秀場(chǎng)、在線金融等各行業(yè)及企業(yè)用戶只需經(jīng)過簡(jiǎn)單的開發(fā)即可打造在線音視頻平臺(tái)?，F(xiàn)在，網(wǎng)易視頻云與大家分享一下不同執(zhí)行計(jì)劃下Mysql多表更新結(jié)果不一致的現(xiàn)象。前陣子時(shí)間偶然的情況下公司里的前輩們?cè)诖a注釋中留下一條有趣的SQL引發(fā)了我的興趣， 在機(jī)緣巧合下又發(fā)現(xiàn)是否建立索引，建立什么樣的索引會(huì)導(dǎo)致更新語句的結(jié)果不一致。接下來重現(xiàn)這個(gè)問題：首先 在

2、mysql中來建立兩張表 t1, t2 兩張表都有兩個(gè)int 字段 a和b，為兩張表各自插入一條記錄(10, 20)然后有以下這樣的更新語句：      update t1, t2 set t1.b = 300, t2.b =  t1.b where t2.a = t1.a and t1.a = 10;一般認(rèn)為這條更新語句能夠?qū)1表和t2表中的記錄同時(shí)更改為(10, 300), 而事實(shí)結(jié)果是不是這樣的呢？我們選用了mysql-5.1.49版本進(jìn)行，并按照以下情形建表進(jìn)行測(cè)試：1.  create table t1

3、 (a int, b int);     create table t2 (a int, b int);2.  create table t1 (a int, b int, primary key(a);     create table t2 (a int, b int, primary key(a);3.  create table t1 (a int, b int);     create index idx on t1(a);     create

4、 table t2 (a int, b int);     create index idx on t2(a);結(jié)果1的結(jié)果： t2 表的數(shù)據(jù)沒變，只有t1被更新成了(10, 300)。這是怎么回事呢？首先來看查詢計(jì)劃， 由于5.1版本不支持update語句的explain，因此我們根據(jù) where后的條件，改寫成select 語句：     explain select * from t1, t2 where t2.a = t1.a and t1.a = 10;更新記錄存放在上層的table->record 中，其中recor

5、d1 為更新前項(xiàng)，record0 為更新后項(xiàng)， 于是乎問題就在于更新t2表時(shí)，record0 是怎么賦值的在存儲(chǔ)引擎的接口層打上斷點(diǎn)，可以初步分析以上三種情形下的執(zhí)行步驟：來分析case 1的執(zhí)行邏輯：1. 做t1 的全表掃描2. 做t2的全表掃描3. 將滿足條件的t1的rowId傳給上層4. 將滿足條件的t2的rowId傳給上層5. 全表掃描內(nèi)表t2，沒有符合條件的記錄6. 全表掃描外表t1， 沒有記錄了7. 根據(jù)t1的rowId 進(jìn)行postionScan8. 更新t1 將其更新為(10. 300)9. 根據(jù)t2的rowId進(jìn)行positionScan10. 更新t2，將其更新為（10，2

6、0）來分析case 2的執(zhí)行邏輯：1. 做t1的索引掃描，掃描獲取a,b 兩列2. 做t2的索引掃描，掃描a,b 兩列3. 更新t1表 將其更新為(10,300)4. 根據(jù)之前join返回的ref值（rowid），對(duì)t2表做position scan5. 更新t2表的記錄，將其更新為(10,300)來分析 case 3的執(zhí)行邏輯：join 選擇 t1 為外表 t2為內(nèi)表做nestloop查詢1. 做t1的索引掃描，掃描取a,b 兩列的值2. 做t2的索引掃描，掃描a，b兩列的值3. 更新 t1表的記錄，將其更新為（10， 300）4. 將滿足條件的t2 的rowId 傳給上層的ref5. 繼續(xù)

7、走索引掃描查詢內(nèi)表t2 ，看有沒有符合條件的記錄，發(fā)現(xiàn)沒有6. 再走索引掃描查詢外表t1，也沒有記錄7. 根據(jù)之前返回的ref值(符合join條件的rowId)，對(duì)t2表做position scan8. 跟新t2表的記錄，將其更新為（10，300）情況1的執(zhí)行計(jì)劃較情況2，3有較大的不同， 而情況2和情況3相比，兩張表各省略了一次索引掃描（因?yàn)橹麈I索引是唯一索引，不需要額外的去查看是否達(dá)到查詢邊界）。接下來看mysql上層是如何處理這樣的查詢語句的。sql語句經(jīng)過yacc解析層后， mysql會(huì)將更新后項(xiàng)加入一個(gè)values_for_table數(shù)組， 在本例中，數(shù)組的第一個(gè)元素，即t1表的更新

8、后項(xiàng)，為一個(gè)值為1的Item_int對(duì)象, 而第二個(gè)元素，t2的更新后項(xiàng)，為一個(gè)指向t1表record0 第二個(gè)屬性的指針。在multi_update:initialize_tables方法中判斷出我的主表t1是否能在join過程中直接更新掉。而此處就是導(dǎo)致結(jié)果不同的關(guān)鍵。相關(guān)代碼如下：    if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables)              table->mark_columns_n

9、eeded_for_update();          table_to_update= table;               / Update table on the fly          continue;    safe_updat

10、e_on_fly() 方法是判斷是否這張表中的一行需要讀兩次進(jìn)行更新，如果不需要的話，直接可以在join里就更新掉。根據(jù)代碼注釋的說明來看，可以直接在join里更新的條件如下：     1. 沒有列在set中又需要讀，又需要寫     2. 做tableScan，并且數(shù)據(jù)是單文件（MYISAM）或者我們不更新聚簇索引鍵     3. 做一個(gè)rangescan， 并且不更新查找鍵或者主鍵     4. table不是自相交針對(duì)我們遇到的情況來跟蹤代碼：case 1 因?yàn)閖oin

11、類型為 JT_ALL 所以判斷在set語句中 屬性b 又需要讀，又需要寫，因此，不能在join中直接更新主表case 2 因?yàn)閖oin 類型為 JT_CONST 所以認(rèn)為一定可以在join中直接更新主表case 3 因?yàn)閖oin類型為JT_REF 所以判斷屬性鍵a是否被更新，因?yàn)闆]有被更新，因此可以在join中直接更新主表（join 類型就是我們查詢計(jì)劃中的type）  對(duì)于不能直接在join中更新的表，mysl上層會(huì)為其建立一張對(duì)應(yīng)的臨時(shí)表來存儲(chǔ)更新后項(xiàng)，因此：case1 有兩張臨時(shí)表，而case2和case3 只有一張t2表對(duì)應(yīng)的臨時(shí)表。從大體的路徑上來說case 1是

12、一類， case 2、3是一類，所以以下就按照case 1和case 3進(jìn)行討論case 2：case2 在實(shí)際更新時(shí)，即第3步之前做了如下的事情：      store_record(table,record1);          / 將getNext獲取到的記錄record0 拷貝到更新前項(xiàng) record1中      if (fill_record_n_invoke_before_triggers(thd, *fields_for_tableoffset,

13、         / 將更新后項(xiàng)填充到 record0中                                         &

14、#160;     *values_for_tableoffset, 0,                                        

15、60;      table->triggers,                                         &

16、#160;     TRG_EVENT_UPDATE)可見在t2填充對(duì)應(yīng)的tmp_table1之前，t1表已經(jīng)完成了對(duì)table->record0 的設(shè)置。之后t2表根據(jù)新的t1表table->record0的值創(chuàng)建臨時(shí)表記錄插入到tmp_table1中。這條記錄已經(jīng)是300了行至第4步做rnd_pos時(shí)填充了record0（此時(shí)為舊項(xiàng)）然后通過store_record(table, record1) 將record0中的內(nèi)容拷貝到 record1 中， 而真正的更新后項(xiàng)record0是之后從臨時(shí)表中拷貝過來的。 開被 通過muti_upda

17、te 類中的一個(gè)copy_field 作為一個(gè)拷貝的橋梁，橋梁兩端分別指向臨時(shí)表tmp_table的字段和table->record0 字段。 然后對(duì)tmp_table 做一次全表掃描（tmp_table 是一張 heap引擎的數(shù)據(jù)表，臨時(shí)存儲(chǔ)一些數(shù)據(jù)）將取出來的值賦為tmp_table->record0.case 3 與case 2非常類似，不再做詳細(xì)討論case1：  case1 在第4步之后，會(huì)進(jìn)入上層的multi_update:send_data() 方法，此方法中首先將values_for_table0中的后項(xiàng)300，插入到臨時(shí)表tmp_table0中

18、,  將values_for_table1中記錄的t1表table->record0的內(nèi)容20 插入到臨時(shí)表tmp_table1中。至此，兩張表的更新后項(xiàng)已經(jīng)確定，之后無論t1表的table->record0如何改變，都不會(huì)再影響到t2表的更新后項(xiàng)。行至第8步之前，開始進(jìn)行實(shí)際更新時(shí)， t1將對(duì)應(yīng)的臨時(shí)表中記錄和record0交換。 第10步之前只是將t2對(duì)應(yīng)的臨時(shí)表中記錄和t2 的 table->record0交換，得到更新后項(xiàng)值為20。 結(jié)論： 個(gè)人認(rèn)為，MYSQL在處理多表更新時(shí)在更新前去獲取真正的更新后項(xiàng)才是一個(gè)靠譜的時(shí)機(jī)， 而不是在目前類似cas

19、e1 那樣早早的存一個(gè)過程值?，F(xiàn)在我們知道了結(jié)果不同的原因，那可以構(gòu)造更多的用例：更新語句為 update t1, t2 set t1.b = 300, t2.b =  t1.b where t2.a = t1.a and t1.a = 10 and t1.b = 20;4. create table t1 (a int, b int);     create index idx on t1(a, b);     create table t2 (a int, b int);     create index idx on t2(a, b);5. create table t1 (a int, b int, );    create unique index idx on t1(a, b);