作者介紹
雲雨雪， 一台無情的編碼機器，一個寫部落格的樂子人，夢想是讓這痛苦壓抑的世界綻放幸福快樂之花，向美好的世界獻上祝福。 。
前言
筆者負責的核心運算系統在運維的時候，被投訴運算不出數據，同事上手運維，一波索引最佳化給系統幹崩潰了。
本質就是索引操作不當，Navicat的編輯索引其實就是合並了刪除新增兩條命令，問題就出在索引刪除後，SQL過慢超過了閘道器的60s超時時間，頁面響應失敗。由於表數據量過大，SQL過慢，直接阻塞，連線池也滿了，資料庫建立新連線時直接報經典錯誤Communications link failure，一系列連鎖反應後，宣告系統中斷。
這種情況其實非常無奈，最好的法子就是等待索引建立完畢，此時即使Kill trx_id也無濟於事，還有新的SQL加入。但是筆者也不能束手無策，坐著幹等，於是臨時建了新表，跑了一版最新的數據，程式碼指向新表，來保證新加入運算的數據沒有問題，這個過程大概花了2小時，後續又花了2小時來處理歷史數據。
一、故障報告截選
xxxx-xx-xx 11:52 業務方反饋訂單交付沒有運算到數據，IT響應使用者並開始定位問題。
xxxx-xx-xx 12:00 IT定位問題發現是訂單交付即時運算變慢，選擇臨時對使用者需求的數據進行單獨處理。
xxxx-xx-xx 12:20 遠端呼叫線上介面，對該行數據進行處理，問題解決完畢。
xxxx-xx-xx 14:08 由於頻繁收到業務方的數據缺失問題，開始盤點訂單交付現存問題，問題如下：
1. 部份表數據由於數據膨脹，導致計算的速度已不能支持即時的計算架構，該問題直接導致業務方發現數據遺失（其實不是遺失，而是沒算到那裏，只是現狀是遺失）。
2. 部份SQL定位後，極慢，影響了運算速度。
3.交期查詢頁面速度慢，超過閘道器限時會導致頁面報錯。
xxxx-xx-xx 16:15 業務方再次提出數據遺失和慢的問題，IT選擇臨時對線上大表進行最佳化，但是處理索引不當，導致索引在變更的時候失效。因為大表查詢過慢，交期列表查詢過慢，頁面超時報錯。
xxxx-xx-xx 17:05 由於大表過大，無法進行操作，無論是改索引，還是處理數據均無法操作。選擇臨時新建一張表，跑一版數據後，程式碼指向新表，保證頁面執行。並重新最佳化交期列表查詢，提升列表查詢速度。
xxxx-xx-xx 18:02 IT驗證透過，並告知業務方服務已恢復，業務方使用後發現不影響操作，但是部份數據存在問題。
xxxx-xx-xx 20:37 IT定位後，將歷史數據進行處理後輸入新表，驗證無誤後告知業務方處理完畢。
二、MySQL數據表現狀
當時處理得比較匆忙，最近筆者把這張大表拖到測試庫，針對性地進行了一些測試。這是一張一億多行數據的表，表數據圖截取的Navicat界面。
後文中會用table_name替換真正的表名od_no_order_product_storage_plan_detail_copy2_copy1，雖然知道讀者肯定會吐槽，但是測試表忘了改名了，等想起來也嫌麻煩不想弄第二次了。
查詢索引SHOWINDEXFROM table_name;兩種建立索引的方式，唯一的區別是CREATEINDEX不可操作主鍵。CREATEINDEX index_name ON table_name (column_name);ALTERTABLE table_name ADDINDEX index_name (column_name);
當前表建立索引如下：
count語句用時：
用上索引覆蓋的語句效率：
不用索引的效率：
刪除索引時間幾乎等於無，因此後續實驗僅考慮建立索引。
三、MySQL操作實驗記錄
1.如何檢視鎖和事務
常規可以使用：
查詢正在執行 的行程 SELECT * FROM information_schema.PROCESSLIST where length(info) >0
查詢是否鎖表 show OPEN TABLES where In_use > 0;
檢視正在鎖的事務 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
檢視等待鎖的事務 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查詢正在執行的事務： SELECT * FROM information_schema.INNODB_TRX
刪除事務執行緒 kill (trx_mysql_thread_id)
如果要看很詳細的，可以獲取 InnoDB 引擎的狀態資訊：SHOW ENGINE INNODB STATUS，以下是截取返回資訊中的其中一段，是同一個SQL對應事務的兩次查詢結果。
第一次---TRANSACTION 93275186, ACTIVE 6 sec fetching rowsmysql tables in use1, locked124978lockstruct(s), heapsize2597072, 3308094rowlock(s), undolog entries 544721MySQL threadid4873714, OS thread handle 139964454049536, queryid23815668410.40.148.80 root updatingUPDATE od_no_order_product_storage_plan_detail_copy2_copy1 set is_delete=1WHEREtype='通訊'-------第二次---TRANSACTION 93275186, ACTIVE 155 sec fetching rowsmysql tablesinuse1, locked1772258lockstruct(s), heapsize80273616, 102147669rowlock(s), undolog entries 9524891MySQL threadid4873714, OS thread handle 139964454049536, queryid23815668410.40.148.80 root updatingUPDATE od_no_order_product_storage_plan_detail_copy2_copy1 set is_delete=1WHEREtype='通訊'--------
這段日誌表示正在進行的一個 MySQL 事務的狀態資訊，解釋如下：
TRANSACTION 93275186, ACTIVE 155 sec fetching rows： 這部份指示了當前事務的資訊。 TRANSACTION 表示這是一個事務。 93275186 是事務的識別元。 ACTIVE 155 sec 表示事務處於活動狀態，已經持續 了 155 秒。 fetching rows 表示正在獲取行。
mysql tables in use 1, locked 1： 表示正在使用的 MySQL 表數為 1，其中被釘選的表數為 1。
772258 lock struct(s), heap size 80273616, 102147669 row lock(s)： 這部份提供了有關鎖的資訊。 772258 lock struct(s) 表示該事務涉及 772258 個鎖結構。 heap size 80273616 表示鎖結構在記憶體中的大小。 102147669 row lock(s) 表示該事務涉及 102147669 個行級鎖。
undo log entries 9524891： 表示事務的撤銷日誌條目數。
MySQL thread id 4873714, OS thread handle 139964454049536, query id 238156684 0.40.148.80 root updating： 這部份提供了關於 MySQL 執行緒的資訊。 MySQL thread id 4873714 表示當前執行緒的 MySQL 執行緒識別元是 4873714。 OS thread handle 139964454049536 表示作業系統中對應的執行緒控制代碼是 139964454049536。 query id 238156684 表示當前查詢的識別元。 10.40.148.80 表示客戶端的 IP 地址。 root 表示當前查詢的使用者。 updating 表示正在進行的操作是一個更新操作。
綜上所述，這段日誌表示正在進行的一個活動狀態的事務，該事務正在更新名為 od_no_order_product_storage_plan_detail_copy2_copy1 的表，將 type 欄位為 '通訊' 的行的 is_delete 欄位設定為 1。
2.索引與更新
1）一個索引更新語句接著並列執行另一個非索引更新語句
索引更新先執行，接著執行非索引更新的，可以發現，後來全表掃描的非索引更新語句被鎖住，猜測原因是因為兩者掃描數據集有交集。
2）兩個非索引更新表鎖表
順序執行如下兩個沒有用到索引的SQL的Update語句：
UPDATE table_name set is_delete=1 and update_time='2023-08-22 20:17:23' WHERE type='通訊'
UPDATE table_name set is_delete=0 and update_time='2024-01-22 20:17:23' WHERE type='外協'
第二個SQL因為被等待鎖超時直接掛掉了。
在 MySQL 中，預設的鎖等待超時時間是50秒。這意味著當一個事務在等待鎖超過50秒時，MySQL 將自動終止該事務，並丟擲一個死結錯誤。這個超時時間可以透過參數 innodb_lock_wait_timeout 進行配置。
可以透過以下命令來檢視當前的鎖等待超時時間：SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
如果需要更改預設的鎖等待超時時間，可以使用以下命令：SET GLOBAL innodb_lock_wait_timeout = 60; -- 將鎖等待超時時間設定為60秒
但要註意的是修改這個值可能會影響系統的效能和並行處理能力，因此應謹慎調整。
3）兩個索引更新集不相交
以上數據來源於兩個都用到了索引的更新語句，如上顯示當更新數據集不相交的時候，更新不會阻塞。
4）兩個索引更新集相交
首先準備下數據，將UPDATE detail_test set master_id='9' WHERE market_code_name='白牌'，建立索引master_id，接著先後執行下面兩個語句：
U PDATE detail_test set storage_bom_code='bom1' WHERE market_code_name='白牌'
UPDATE detail_test set remark='666歐規' WHERE master_id='9'
測試結果如上圖，決定更新是否互相鎖住的根本原因就是，更新集是否相交，如果命中索引，那更新集局限於索引的數據範圍，相比全表會小不少。
3.隱式型別轉換索引失效的影響
以下是加索引後查詢的三種狀態，master_id欄位型別是varchar(200)，這裏圖一因為隱式型別轉換，導致索引失效走了全表。
圖二是查詢條件查出來即使沒有實際數據被檢索，也就是查詢結果數為0。MySQL仍然會執行查詢計劃，並且通常會估計檢索一行數據的成本，因此，row欄位仍然顯示為1。
圖三就是正常索引記錄，filtered表示透過查詢條件獲取的最終記錄行數占透過type欄位指明的搜尋方式搜尋出來的記錄行數的百分比。
4.DDL語句執行中對DML語句以及索引的影響
1）先Create Index再Update最後Select
結論是毫無影響，三個SQL並列執行。
2）修改表欄位結構再操作Update
修改欄位A的數據結構為varchar(1000)
這裏透過SHOW FULL PROCESSLIST得知兩個更新語句被鎖住。
透過查詢正在執行的事務：SELECT * FROM information_schema.INNODB_TRX，發現兩個Update語句連事務都沒有發起，當前事務的trx_tables_locked有值說明也鎖住了表。
在SHOW OPEN TABLES（https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html）結果中，也可驗證表已被鎖。
In_use欄位在表中表示表鎖或鎖請求的數量。 例如，如果一個客戶機使用鎖表t1寫的表獲得一個鎖，那麽In_use將是1。 如果另一個客戶端問題鎖表t1寫，而表仍然釘選，客戶端將阻塞等待鎖，但是鎖請求導致In_use為2。 如果計數為零，表是開啟的，但目前沒有使用。
Namelocked欄位表示表名是否被釘選。 如果Namelocked欄位的值為1，則表示表名已經被釘選，可能有其他會話正在執行對該表的DDL操作，比如重新命名、刪除等操作。 這意味著在表名被釘選期間，其他會話可能無法對該表執行相同型別的DDL操作，直到鎖被釋放。
查詢不受影響，MVCC的快照讀立大功，具體原理見八股文 。
這裏讓我比較意外的是，這個SQL沒有因為innodb_lock_wait_timeout超時，我猜是沒有開啟事務的原因。
5.DML語句執行中索引增刪是否會引起異常
1）先Update再Create Index最後Select
更新語句，沒有用到索引。
查詢語句同樣沒有用到索引。
更新語句之後增加索引，該DDL語句會被鎖住，同時鎖住後面的查詢語句。
更新語句鎖釋放後，增加索引和查詢並列執行，這裏可以看到查詢快於增加索引語句結束。
2）先Update再Drop Index最後Select
順序反過來測試一下，先執行UPDATE語句，此時用到了索引。
再刪除語句用到的索引，DROP INDEX master_idx ON table_name;
接著乘著DROP INDEX被阻塞的時候執行索引查詢SELECT * FROM table_name WHERE master_id='6'
此時會發生如下現象，當前執行事務只有Update一個：
SHOW FULL PROCESSLIST;中顯示Drop Index和Select語句都在等待鎖釋放。
註意這裏，很有趣的一點是明明Drop Index比Select早執行，但是還是被後來的Select鎖住了，這應該是個最佳化，希望有大佬解答原理。
3）先Delete再Drop Index最後Select
首先保證刪除用到了索引。
這裏能看到和Update不同的是，Delete不會鎖Select語句。
並且和Update有著相同的最佳化，明明Drop Index比Select早執行，但是還是被後來的Select鎖住了。
四、TiDB配置對比MySQL說明
TiDB的表數據是透過DataX直接同步過來的，還是很快，數據量是12.2G，比MySQL同樣數據的表大了600MB，我猜測是分布式資料庫做的一些用空間換時間的最佳化，以及數據儲存上的一些差異。
MySQL測試機器用的16G，配置buffer pool是8G，並行8，算是常規配置，之前有寫過如何配置 ，TiDB即使是測試，配置也遠超MySQL，所以效能是沒必要強行對比。
五、TiDB增刪索引
MySQL5.7：
TiDB的索引可以調，但是預設的也比5.7的快多了。
SHOW VARIABLES LIKE '%tidb_ddl_reorg%';
//當添加索引操作的目標列被頻繁更新（包含 UPDATE、INSERT 和 DELETE）時，調大上述配置會造成較為頻繁的寫沖突，使得線上負載較大；同時添加索引操作也可能由於不斷地重試，需要很長的時間才能完成。此時建議調小上述配置來避免和線上業務的寫沖突。
SET @@global.tidb_ddl_reorg_worker_cnt = 4;
SET @@global.tidb_ddl_reorg_batch_size = 256;
//當添加索引的目標列僅涉及查詢負載，或者與線上負載不直接相關時，可以適當調大上述變量來加速添加索引：
SET @@global.tidb_ddl_reorg_worker_cnt = 16;
SET @@global.tidb_ddl_reorg_batch_size = 4096;
將預設參數拉高後的效果並不是很明顯，我之前測試別的數據時效果明顯一些，這部份效能評估可能和數據量和TiDB本身的執行策略有關，暫時不深究。
六、TiDB操作實驗記錄
首先設定單條查詢的記憶體使用限制-預設1G，設定為3G，SET GLOBAL tidb_mem_quota_query = 3221225472;不然Update語句老報錯。
1.先Update再Drop Index最後Select
按照順序執行SQL後，得到的結果和MySQL居然是不一樣的，這是我沒想到的。在刪除索引的語句後，查詢語句理所當然地執行了全表掃描。
這裏開啟TiDB Dashboard的慢查詢頁面仔細看一下各個SQL的執行過程。
首先最晚開始的查詢語句，卻是最早執行完畢的，和MySQL不一樣的是完全沒有被鎖住，而是直接走了全表查詢。
Update語句是最先執行的語句，用到了SQL，並行起了事務。
Drop Index語句的執行詳情讓我蚌埠住了，完全沒有鎖，說刪就刪了。
關於TiDB的沒搞太明白，這裏也不深究了。主要也是因為線上TiDB出問題幾乎沒有，除了寫入並行量難以最佳化之外，別的也沒啥說的。
七、實驗結論
盡管實驗比較粗糙，但是還是可以得出一些結論，簡單總結一下。
DML語句盡量命中索引，縮小更新數據範圍，避免全表掃描或者更新數據集相交從而鎖住後續DML語句。
給Update或者Delete的欄位增加索引不會鎖，但是刪除索引會鎖表並且會阻塞命中索引的查詢語句。
修改表欄位結構的操作會鎖全表，但是只影響DML語句不影響查詢語句。
八、故障回顧
具體細節記不太清楚了，說實話我沒復現出來，當時明明只是刪除了索引，但是增加索引這一步遲遲沒有成功，資料庫像是被打崩了。按照我上面的實驗結論來講，增加索引是可能正常執行的，很奇怪。
因為當時我主要負責這個計畫，所以是我拉著同事去做的解決，我當時是雙管齊下，我去解決資料庫的響應問題，他去臨時建立表做備用方案。當時操作資料庫的細節我忘了，只記得非常卡，什麽操作都沒用，也有可能是我太著急了，反正這條路是斷了。也是著急恢復系統，所以直接啟用備用方案，讓PM去安撫使用者，我和同事開發測試上線，跟領導匯報情況，真是裂開了。
事後也是沒有能成功復現當時那種奇怪的現象，比較可惜。接下來用一段話來總結這次令人難忘的線上事故，註意嗷，這是重點。
令人難忘的線上事故，還是挺多的，因為是容器化部署，像是遇到JVM之類的OOM或者CPU滿了的問題，通常會觸發自動重新開機，一定程度上減少了中斷的時間。所以我自己覺得還是資料庫上面的問題更嚴重些，違規操作資料庫之類的。
去年底我負責的核心系統也就是訂單交付就出現了這麽一起長達兩小時中斷的事故。起因是使用者投訴數據過了十幾分鐘還沒有運算出來，經過排查後發現是運算太慢了，再定位是發現有一張MySQL表數據膨脹到億級，導致SQL查詢緩慢從而拖累了運算速度。
當時直接交給同事處理，同事在測試環境修改了索引發現速度變快，於是直接操作了正式庫索引。於是索引失效造成了一系列連鎖反應，首先是日誌瘋狂告警，提示運算失敗，然後使用者所在的網頁端因為呼叫閘道器超時而報錯，投訴蜂擁而來。我透過日誌定位到是資料庫連線失敗，發現Navicat連線異常慢，還有機率出現連線失敗，查詢行程發現查詢大表的索引失效。為了盡快恢復線上環境，臨時關閉了即時運算，kill掉所有事務，增加了一張臨時表，本地重跑了一版數據放到新表，並修改程式碼指向新表，測試無誤後上線。 上線後接著處理這段時間的歷史數據，並恢復即時計算，中斷時間大約是兩小時。
事後寫了故障報告，復盤了下這次的處理，做得不好的地方就是一開始在糾結資料庫的問題，總是想接著操作資料庫，浪費了很多時間。做得好的地方就是雙管齊下，同時啟用了備用方案，臨時建新表，並修改程式碼指向新表，也算是快速解決了問題。
最後我自己做了一些實驗去避免線上操作的問題：
索引一般不要輕易刪除，增加索引問題不大。
絕對不能修改表和欄位數據結構，會直接鎖住DML語句。
DML語句盡量命中索引，更新數據集相交會導致DML語句爭奪鎖造成等待。公司對這種情況也做了許可權管控，透過堡壘機做了許可權管控和監控，還有自研開發服務平台來控制資料庫的連結許可權。
dbaplus社群歡迎廣大技術人員投稿，投稿信箱： [email protected]
活動推薦
2024 XCOPS智慧運維管理人年會·廣州站將於5月24日舉辦 ，深究大模型、AI Agent等新興技術如何落地於運維領域，賦能企業智慧運維水平提升，構建全面運維自治能力！ 碼上報名，享早鳥優惠。
