MySQL DDL詳情揭示

2024-07-24 12:32:08

字體：大中小

來源：轉載

供稿：網友

MySQL中DDL語句，即數據定義語言，用于創(chuàng)建、刪除、修改、庫或表結構，對數據庫或表的結構操作。常見的有create，alter，drop等。這類語句通常會耗費很大代價，特別是對于大表做表結構變更。本篇文章會揭露各類DDL語句執(zhí)行的詳細情況。

1.Online DDL簡介
在MySQL的早期版本中，DDL操作因為鎖表會和DML操作發(fā)生鎖沖突，大大降低并發(fā)性。在早期版本中，大部分DDL操作的執(zhí)行原理就是通過重建表的方式，因為要復制原表數據，所以會長時間鎖表，只能讀不能寫，DDL操作和DML操作有很嚴重的沖突。從MySQL5.6開始，很多DDL操作過程都進行了改進，出現(xiàn)了Online DDL，用于支持DDL執(zhí)行期間DML語句的并行操作，提高數據庫的吞吐量。

MySQL 在線DDL分為 INPLACE 和 COPY 兩種方式，通過在ALTER語句的ALGORITHM參數指定。

ALGORITHM=INPLACE，可以避免重建表帶來的IO和CPU消耗，保證ddl期間依然有良好的性能和并發(fā)。
ALGORITHM=COPY，需要拷貝原始表，所以不允許并發(fā)DML寫操作，可讀。這種copy方式的效率還是不如 inplace ，因為前者需要記錄undo和redo log，而且因為臨時占用buffer pool引起短時間內性能受影響。
上面只是 Online DDL 內部的實現(xiàn)方式，此外還有 LOCK 選項控制是否鎖表，根據不同的DDL操作類型有不同的表現(xiàn)：默認MySQL盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表。

2.不同類DDL操作詳情
不同種類DDL語句具體的執(zhí)行情況是不同的，下表列舉出常見DDL語句具體的執(zhí)行詳情，包括是否允許讀寫及是否鎖表。這個表格希望大家可以詳細對比看下，特別要關注下需要copy table的DDL操作。

主從復制延遲，只有主庫上DDL執(zhí)行成功才會寫入到binlog中，而DDL操作在從庫上不能并發(fā)執(zhí)行，因此即使主庫執(zhí)行DDL時允許并發(fā)DML操作，對于大表操作，仍會引發(fā)嚴重的復制延遲。
主庫執(zhí)行Online DDL時，不能根據負載暫停DDL操作。
使用Inplace方式執(zhí)行的DDL，發(fā)生錯誤或被KILL時，需要一定時間的回滾期，執(zhí)行時間越長，回滾時間越長。
使用Copy方式執(zhí)行的DDL，需要記錄過程中的undo和redo日志，同時會消耗buffer pool的資源，效率較低，優(yōu)點是可以快速停止。
Online DDL并不是所有時間段的Online，在特定時間段需要加元數據鎖或其他鎖。
允許并發(fā)DML的DDL，可能會導致Duplicate entry問題。
針對DDL，下面整理下幾點干貨建議，之后執(zhí)行DDL語句時可以參考下：

執(zhí)行DDL前查看下該表有沒有被事務占用，防止出現(xiàn)MDL鎖。
執(zhí)行DDL前確保datadir，tmpdir磁盤空間足夠。
能業(yè)務低峰期操作的DDL，都盡量安排在業(yè)務低峰期進行。
對于大表和較大表，如果對復制延遲和主庫性能敏感，建議改為gh-ost或pt-osc工具。
對于并發(fā)操作較高的表，無論表數據量多少，不能在業(yè)務高峰期操作。
同個表的多個DDL語句可以合并在一起進行，避免多次table rebuild帶來的消耗。但是也要注意分組，比如需要copy table和只需inplace就能完成的，應該分兩個alter語句。

（編輯：武林網）

上一篇：mysql innodb cluster 創(chuàng)建

下一篇：linux下apache+mysql+php開發(fā)環(huán)境純源代碼編輯搭建