MySQL數據庫中DDL語句的介紹和使用

這篇文章的知識點包括：DDL語句的簡介、DDL語句的操作以及DDL語句的使用注意事項，閱讀完整文相信大家對MySQL數據庫中DDL語句有了一定的認識。

1.Online DDL簡介

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

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

• ALGORITHM=INPLACE，可以避免重建表帶來的IO和CPU消耗，保證ddl期間依然有良好的性能和并發。
• ALGORITHM=COPY，需要拷貝原始表，所以不允許并發DML寫操作，可讀。這種copy方式的效率還是不如 inplace ，因為前者需要記錄undo和redo log，而且因為臨時占用buffer pool引起短時間內性能受影響。

上面只是 Online DDL 內部的實現方式，此外還有 LOCK 選項控制是否鎖表，根據不同的DDL操作類型有不同的表現：默認MySQL盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表。

• LOCK=NONE，即DDL期間允許并發讀寫涉及的表，比如為了保證 ALTER TABLE 時不影響用戶注冊或支付，可以明確指定，好處是如果不幸該 alter語句不支持對該表的繼續寫入，則會提示失敗，而不會直接發到庫上執行。
• LOCK=SHARED，即DDL期間表上的寫操作會被阻塞，但不影響讀取。
• LOCK=DEFAULT，讓mysql自己去判斷lock的模式，原則是mysql盡可能不去鎖表
• LOCK=EXCLUSIVE，即DDL期間該表不可用，堵塞任何讀寫請求。如果你想alter操作在最短的時間內完成，或者表短時間內不可用能接受，可以手動指定。

但是有一點需要說明，無論任何模式下，Online DDL開始之前都需要一個短時間排它鎖(exclusive)來準備環境，所以alter命令發出后，會首先等待該表上的其它操作完成，在alter命令之后的請求會出現等待waiting meta data lock。同樣在DDL結束之前，也要等待alter期間所有的事務完成，也會堵塞一小段時間。所以盡量在ALTER TABLE之前確保沒有大事務在執行，否則一樣出現連環鎖表。

2.不同類DDL操作詳情

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

3.DDL最佳實踐

雖然MySQL 5.6和5.7版本提供了Online DDL操作，但Online DDL仍存在以下問題：

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

針對DDL，下面整理下幾點干貨建議，之后執行DDL語句時可以參考下：

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

以上就是MySQL數據庫中DDL語句的介紹和使用，看完之后是否有所收獲呢？如果想了解更多相關內容，歡迎關注億速云行業資訊！