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

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

2、mysql中來建立兩張表 t1, t2 兩張表都有兩個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表中的記錄同時更改為(10, 300), 而事實結(jié)果是不是這樣的呢？我們選用了mysql-5.1.49版本進(jìn)行，并按照以下情形建表進(jìn)行測試：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)。這是怎么回事呢？首先來看查詢計劃， 由于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 為更新前項，record0 為更新后項， 于是乎問題就在于更新t2表時，record0 是怎么賦值的在存儲引擎的接口層打上斷點，可以初步分析以上三種情形下的執(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），對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)，對t2表做position scan8. 跟新t2表的記錄，將其更新為（10，300）情況1的執(zhí)行計劃較情況2，3有較大的不同， 而情況2和情況3相比，兩張表各省略了一次索引掃描（因為主鍵索引是唯一索引，不需要額外的去查看是否達(dá)到查詢邊界）。接下來看mysql上層是如何處理這樣的查詢語句的。sql語句經(jīng)過yacc解析層后， mysql會將更新后項加入一個values_for_table數(shù)組， 在本例中，數(shù)組的第一個元素，即t1表的更新

8、后項，為一個值為1的Item_int對象, 而第二個元素，t2的更新后項，為一個指向t1表record0 第二個屬性的指針。在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. 做一個rangescan， 并且不更新查找鍵或者主鍵     4. table不是自相交針對我們遇到的情況來跟蹤代碼：case 1 因為join

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

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

13、         / 將更新后項填充到 record0中                                         &

14、#160;     *values_for_tableoffset, 0,                                        

15、60;      table->triggers,                                         &

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

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

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

19、e1 那樣早早的存一個過程值?，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);