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

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

為玄武等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及玄武網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、玄武網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專(zhuān)業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

1.Online DDL簡(jiǎn)介

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

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

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

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

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

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

2.不同類(lèi)DDL操作詳情

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

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

3.DDL最佳實(shí)踐

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

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

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

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

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

網(wǎng)站欄目：MySQL數(shù)據(jù)庫(kù)中DDL語(yǔ)句的介紹和使用
URL網(wǎng)址：http://muchs.cn/article6/ppjhog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開(kāi)發(fā)、微信公眾號(hào)、商城網(wǎng)站、網(wǎng)站設(shè)計(jì)公司、定制網(wǎng)站、軟件開(kāi)發(fā)

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

猜你還喜歡下面的內(nèi)容