MySQLDDL詳情揭露

前言：?

站在用戶的角度思考問題，與客戶深入溝通，找到建寧網(wǎng)站設計與建寧網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術結合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站設計、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、國際域名空間、網(wǎng)絡空間、企業(yè)郵箱。業(yè)務覆蓋建寧地區(qū)。

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

1.Online DDL簡介

在MySQL的早期版本中，DDL操作因為鎖表會和DML操作發(fā)生鎖沖突，大大降低并發(fā)性。在早期版本中，大部分DDL操作的執(zhí)行原理就是通過重建表的方式，因為要復制原表數(shù)據(jù)，所以會長時間鎖表，只能讀不能寫，DDL操作和DML操作有很嚴重的沖突。從MySQL5.6開始，很多DDL操作過程都進行了改進，出現(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 ，因為前者需要記錄undo和redo log，而且因為臨時占用buffer pool引起短時間內性能受影響。

上面只是 Online DDL 內部的實現(xiàn)方式，此外還有 LOCK 選項控制是否鎖表，根據(jù)不同的DDL操作類型有不同的表現(xià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操作在最短的時間內完成，或者表短時間內不可用能接受，可以手動指定。

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

2.不同類DDL操作詳情

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

操作	支持方式	Allow R/W	說明
add/create index	online	允許讀寫	當表上有FULLTEXT索引除外，需要鎖表，阻塞寫
drop index	online	允許讀寫	操作元數(shù)據(jù)，不涉及表數(shù)據(jù)。所以很快，可以放心操作
optimize table	online	允許讀寫	當帶有fulltext index的表用copy table方式并且阻塞寫
alter table...engine=innodb	online	允許讀寫	當帶有fulltext index的表用copy table方式并且阻塞寫
add column	online	允許讀寫(增加自增列除外)	1、添加auto_increment列要鎖表，阻塞寫;2、雖采用online方式，但是表數(shù)據(jù)需要重新組織，所以增加列依然是昂貴的操作
drop column	online	允許讀寫(增加自增列除外)	同add column，重新組織表數(shù)據(jù)，，昂貴的操作
Rename a column	online	允許讀寫	操作元數(shù)據(jù);不能改列的類型，否則就鎖表
Reorder columns	online	允許讀寫	重新組織表數(shù)據(jù)，昂貴的操作
Make column NOT NULL	online	允許讀寫	重新組織表數(shù)據(jù)，昂貴的操作
Change data type of column?	copy table	僅支持讀，阻塞寫	?創(chuàng)建臨時表，復制表數(shù)據(jù)，昂貴的操作
Set default value for a column	online	允許讀寫	操作元數(shù)據(jù)，因為default value存儲在frm文件中，不涉及表數(shù)據(jù)。所以很快，可以放心操作
alter table xxx auto_increment=xx?	online	允許讀寫	操作元數(shù)據(jù)，不涉及表數(shù)據(jù)。所以很快，可以放心操作?
Add primary key	online	允許讀寫	昂貴的操作
Convert character set?	copy table	僅支持讀，阻塞寫	如果新字符集不同，需要重建表，昂貴的操作

3.DDL最佳實踐

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

主從復制延遲，只有主庫上DDL執(zhí)行成功才會寫入到binlog中，而DDL操作在從庫上不能并發(fā)執(zhí)行，因此即使主庫執(zhí)行DDL時允許并發(fā)DML操作，對于大表操作，仍會引發(fā)嚴重的復制延遲。
主庫執(zhí)行Online DDL時，不能根據(jù)負載暫停DDL操作。
使用Inplace方式執(zhí)行的DDL，發(fā)生錯誤或被KILL時，需要一定時間的回滾期，執(zhí)行時間越長，回滾時間越長。
使用Copy方式執(zhí)行的DDL，需要記錄過程中的undo和redo日志，同時會消耗buffer pool的資源，效率較低，優(yōu)點是可以快速停止。
Online DDL并不是所有時間段的Online，在特定時間段需要加元數(shù)據(jù)鎖或其他鎖。
允許并發(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ā)操作較高的表，無論表數(shù)據(jù)量多少，不能在業(yè)務高峰期操作。
同個表的多個DDL語句可以合并在一起進行，避免多次table rebuild帶來的消耗。但是也要注意分組，比如需要copy table和只需inplace就能完成的，應該分兩個alter語句。

參考：?

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
https://www.cnblogs.com/rayment/p/7762520.html

網(wǎng)頁題目：MySQLDDL詳情揭露
鏈接分享：http://bm7419.com/article30/pcggso.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設計公司、網(wǎng)站營銷、企業(yè)建站、面包屑導航、外貿(mào)建站、網(wǎng)站設計

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內容