技術文章 » 因為一條DDL，差點搞掛整個系統，這次真的長了教訓

因為一條DDL，差點搞掛整個系統，這次真的長了教訓

2023-07-17 18:03:36

有一次線上上提了一個sql變更，就是下面這條，

-- 修改欄位的資料型別由varchar(500)變更為text
ALTER TABLE t MODIFY COLUMN name text;

提完之後，上級審批人給我打來了電話，說不允許進行欄位型別的變更，要變更的話需要找大領導審批，一想還是算了，不要打擾領導了。最後把varchar的長度變更為1000，才把這個事情解決了。後來查閱資料才明白原來一條普通的DDL卻暗藏玄機。什麼玄機吶今天細細說來。

要了解DDL的執行原理，必須區分mysql的版本，不同的版本DDL執行原理是不一樣的。

一、DDL執行原理（5.6之前）

在mysql5.6版本之前，執行一條DDL語句，mysql內部會使用兩種方式執行，分別是copy和inplace。

1.1、copy

所謂copy就是在執行過程中需要copy table，看下其具體步驟，

  1. 新建跟原表格一致的臨時表，並在該臨時表上執行DDL語句；
  2. 鎖原表，不允許執行DML，僅允許查詢；
  3. 逐行把資料從原表拷貝到臨時表（無排序）；
  4. 拷貝結束後，原表禁止讀操作，也就是原表此時不提供讀寫服務；
  5. 進行rename操作，完成DDL過程；

可以看到在copy這種方式下，執行DDL語句的時候會鎖表，且無法執行DML語句；再看下inplace的方式，

1.2、inplace方式（僅針對索引建立、刪除）

這種方式僅對索引的建立、刪除有效，其他型別的DDL還是使用copy的方式，其步驟如下，

  1. 新建frm臨時檔案；
  2. 鎖住原表，不允許DDL，允許查詢；
  3. 按照聚集索引的順序查詢資料，找到需要的索引列資料，排序後插入到新的索引中；
  4. 原表禁止讀操作，也就是原表此時不提供讀寫服務；
  5. 進行rename操作，替換frm檔案，完成DDL；

可以看到inplace這種方式依然需要鎖表，且無法執行DML。

 

copy和inplace兩種都會阻止DML語句的執行，也就是insert/update/delete操作，只能執行select操作。相對於copy的需要拷貝全表的資料外，inplace只需要拷貝索引資料，就好很多，但inplace只支援索引新增、刪除。

在5.6版本之前的mysql在執行DDL的時候，一定要注意選擇業務低峰期，同時做好影響範圍的預測，以為在執行DDL的時候是無法執行DML的。

在5.6及之後，mysql推出了online DDL的方式。很好的解決了無法執行DML的問題。

二、online DDL

online DDL是mysql在5.6版本推出的執行DDL的方式，可以解決執行DDL時無法執行DDL的情況。online DDL有自己的語法，在傳統的DDL語句後加相應的引數，當然引數可以省略，省略的話mysql則會選擇一種適合的方式執行。

2.1、online DDL語法

標準的online DDL寫法如下，

-- 修改欄位的資料型別由varchar(500)變更為text
ALTER TABLE t MODIFY COLUMN name text,algorithm=default|copy|inplace|instant,lock=none|shared|default|exclusive;

在algorithm引數中有四個值，

default，預設的，由系統決定

copy，和早期的copy方式一致；

inplace，和早期的inplace方式一致；

instant，mysql8.0新增的。只會修改資料字典中的後設資料，會短暫的佔用後設資料上的排它鎖，操作是即時的，允許並行DML；

 

lock引數有四個值，其限制級別由少到多，

none，允許並行查詢和MDL語句，

shared，允許並行查詢，但阻止DML

default，允許儘可能多的並行查詢、DML。省略lock和default是一樣的。

exclusive，阻止並行查詢和DML，

 

2.2、online DDL執行過程

mysql將online DDL的執行過程分為三步，

初始化（initialization）

在這個階段，伺服器根據儲存引擎、語句中指定的選項等來確定允許的並行，使用共用的可升級後設資料鎖來保護當前表定義。

執行（execution）

語句被準備和執行，後設資料鎖是否升級為排它鎖取決於初始化階段的評估，如果需要獨佔後設資料鎖，只在語句準備期間短暫使用。

提交（commit table definition）

後設資料鎖升級為排它鎖，退出舊的表定義並提交新表定義，後設資料鎖持續時間很短。

 

2.3、常用的DDL

總結了常用的DDL的執行方式，

需要特別注意的是對於varchar的長度變化，其使用的演演算法是不一樣的。

有個很有趣的點，平時定義的varchar(50)，這裡的50是位元組數還是字元數嗎？

其實在mysql5.0之後varchar(50)，代表的是50個字元，在5.0之前是50個位元組；

按照UTF8編碼，一個字元3個位元組；按照GBK編碼一個字元2個位元組；

瞭解了上面的知識後，還需要了解字串的長度是怎麼儲存的，當小於256位元組時使用1個位元組儲存，當大於256位元組小於65535位元組時，使用2個位元組儲存；varchar的最大長度是65535位元組。

varchar型別字元長度的變化帶來的是位元組的變化，同時會引起儲存位元組長度的變化，也就是使用1個位元組還是2個位元組儲存其長度。

增加

以UTF8編碼為例，也就是一個字元3個位元組。

1、如果位元組的變化在256以內，也就是儲存長度使用1個位元組則使用inplace，如，

varchar(10)-->varchar(50)

varchar(50)-->varchar(80)

2、如果位元組的變化跨越了256，也就是儲存長度由1個位元組變成2個位元組則使用copy，如

varchar(80)-->varchar(90)

3、如果位元組的變化超過256，也就是儲存長度使用2個位元組則使用inplace，如

varchar(90)-->varchar(250)

varchar(250)-->varcahr(1000)

減少

對varchar的長度減少統一是copy方式。

下面總結了各種DDL語句使用的演演算法及是否允許並行DML，是否需要重建表等，可參考。

 

需要特別注意下面這些不允許並行DML的DDL，其均使用copy方式：
    1、改變列的資料型別；
    2、刪除主鍵；
    3、變更表字元集；
    4、varchar長度變短；
    5、varchar長度邊長，儲存位元組超過255；

三、總結

在mysql中執行DDL語句是很正常的，很多時候並不會想到會鎖表或者阻止DML的執行，因為DDL執行的太快了，相對於大表則要格外注意，尤其是線上業務高峰期，千萬不要執行DDL，在業務低峰期也要進行評估；

1、關注表的資料量；

2、確定mysql的版本；

3、關注CPU及記憶體使用情況；

4、做好應急措施；

 

參考：

https://www.cnblogs.com/hankyoon/p/15128334.html

https://www.cnblogs.com/xinysu/p/6732646.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-operations