MySQL數(shù)據(jù)庫中DDL語句的介紹和使用

這篇文章的知識點(diǎn)包括：DDL語句的簡介、DDL語句的操作以及DDL語句的使用注意事項(xiàng)，閱讀完整文相信大家對MySQL數(shù)據(jù)庫中DDL語句有了一定的認(rèn)識。

1.Online DDL簡介

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

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

• ALGORITHM=INPLACE，可以避免重建表帶來的IO和CPU消耗，保證ddl期間依然有良好的性能和并發(fā)。
• ALGORITHM=COPY，需要拷貝原始表，所以不允許并發(fā)DML寫操作，可讀。這種copy方式的效率還是不如 inplace ，因?yàn)榍罢咝枰涗泆ndo和redo log，而且因?yàn)榕R時占用buffer pool引起短時間內(nèi)性能受影響。

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

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

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

2.不同類DDL操作詳情

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

3.DDL最佳實(shí)踐

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

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

針對DDL，下面整理下幾點(diǎn)干貨建議，之后執(zhí)行DDL語句時可以參考下：

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

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