MYSQL的DDL該怎么理解及應(yīng)用

MySQL的DDL該怎么理解及應(yīng)用，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

目前累計(jì)服務(wù)客戶(hù)千余家，積累了豐富的產(chǎn)品開(kāi)發(fā)及服務(wù)經(jīng)驗(yàn)。以網(wǎng)站設(shè)計(jì)水平和技術(shù)實(shí)力，樹(shù)立企業(yè)形象，為客戶(hù)提供成都網(wǎng)站建設(shè)、做網(wǎng)站、網(wǎng)站策劃、網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)絡(luò)營(yíng)銷(xiāo)、VI設(shè)計(jì)、網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。創(chuàng)新互聯(lián)公司始終以務(wù)實(shí)、誠(chéng)信為根本，不斷創(chuàng)新和提高建站品質(zhì)，通過(guò)對(duì)領(lǐng)先技術(shù)的掌握、對(duì)創(chuàng)意設(shè)計(jì)的研究、對(duì)客戶(hù)形象的視覺(jué)傳遞、對(duì)應(yīng)用系統(tǒng)的結(jié)合，為客戶(hù)提供更好的一站式互聯(lián)網(wǎng)解決方案，攜手廣大客戶(hù)，共同發(fā)展進(jìn)步。

DDL 操作一直是我們的 MYSQL 的一個(gè)軟肋，從MYSQL 5.6 其實(shí)相關(guān)的alter 語(yǔ)句已經(jīng)有了改變，也就是題目的的inplace 和 copy 。其實(shí)很多人都知道，但用的比較少，因?yàn)橛衟t-OSC 工具呀，還有另外一個(gè)工具gh-ost。

維護(hù)現(xiàn)在有提起這串豆腐的原因就是MYSQL 8 發(fā)展的太快, pt 工具有點(diǎn)跟不上，根據(jù)官方的文檔，8.013后的版本，PT的部分工具就開(kāi)始有的時(shí)候使用上會(huì)出現(xiàn)各種問(wèn)題。

所以我們?cè)趍ysql 的正根 alter 語(yǔ)句在高版本上還的拿出來(lái)用，而這里面就牽扯，什么時(shí)候 inplace 什么時(shí)候 copy 到底這都是什么鬼 ？MYSQL 的 DDL 好累心。

OK 下面就是一段官方+測(cè)試的東西 + MGR MYSQL 8.018

本次主要是針對(duì)字段的DDL 的 增刪改來(lái)進(jìn)行的

從上面的8.0 提供的表來(lái)看

下面是mysql 5.7 提供的，可以很清晰的看出，的確死不一樣了，多了一列叫Instant

但實(shí)際上可以看出這個(gè)立即能做的事情不多，adding a column  ，setting a column default value , Dropping the column default value 這些才可以進(jìn)行instant 但 最常用到的 adding a column也上面有一個(gè)* 號(hào)，這說(shuō)明不可以都可以，是要有條件的。

條件：

1 要不你就添加字段，你要是混合使用alter table語(yǔ)句，那恕不進(jìn)行instant的操作，例如一條語(yǔ)句又是加字段，又是刪字段

2 字段只能加到表最后一列，你要是想在之間加什么字段，恕不管用

3 表的row_format 不能是壓縮的 compressed 的格式

4  表里面有全文索引，no no no  不可以

5  臨時(shí)表不可以

6  數(shù)據(jù)字典表不可以

添加字段還是蠻快的。下面我們?cè)谝粋€(gè)新表，并且一直插入數(shù)據(jù)的狀態(tài)下，看看添加字段還這么愜意嗎？

我們看看結(jié)果如何

DROP TABLE IF EXISTS test.test;

CREATE TABLE test.test(

id int(10) not null auto_increment,

name varchar(20) not null,

age smallint not null,

work_years smallint not null,

PRIMARY key (`id`)

)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT 'test';

#清空數(shù)據(jù)

TRUNCATE table test.test;

*/

#定義存儲(chǔ)過(guò)程

delimiter //

DROP PROCEDURE IF EXISTS insert_test_val;

##num_limit 要插入數(shù)據(jù)的數(shù)量,rand_limit 最大隨機(jī)的數(shù)值

CREATE PROCEDURE insert_test_val()

BEGIN

DECLARE i int default 1;

DECLARE a varchar(20) ;

DECLARE b smallint ;

DECLARE c smallint ;

WHILE i<=1000000 do

set b = FLOOR(rand()*50);

set c = FLOOR(rand()*10);

if i mod 2 = 0 then

set a = 'peter';

elseif i mod 3 = 0 then

set a = 'jimmy';

elseif i mod 5 = 0 then

set a  = 'Tim';

elseif i mod 4 = 0 then

set a = 'semon';

else

set a = 'lisa';

end if;

if b < 20 then

set b = b + 15;

end if;

INSERT into test.test values (null,a,b,c);

set i = i + 1;

END WHILE;

END

//

#調(diào)用存儲(chǔ)過(guò)程

call insert_test_val();

我們?cè)趖est 庫(kù)建立一個(gè)表，并且往里面插入大量的數(shù)據(jù)，然后我們

alter table test add column column1 varchar(500) ,ALGORITHM=INSTANT;

在這個(gè)表上添加一個(gè)字段，結(jié)果如何

SESSION 1

SESSION 2

字段瞬間添加上了，但是存儲(chǔ)過(guò)程在運(yùn)行的途中直接報(bào)錯(cuò)，通過(guò)上表的實(shí)驗(yàn)證明 MYSQL 8 添加字段，不在是一個(gè)問(wèn)題，PT 工具可以收手了。

當(dāng)然這里添加的是一般的column如果你要添加自增的列，則就不可以這樣做，還是需要不能進(jìn)行 DML 操作，類(lèi)似鎖表的操作，好在MYSQL 里面添加自增序列的人不多，大多都是添加普通字段而已。

雖然可以瞬間將阻礙的DML 操作終止，并快速添加字段，但這在生產(chǎn)上來(lái)說(shuō)對(duì)應(yīng)用程序的某些事務(wù)性的操作時(shí)有害的，所以使用的時(shí)候，要小心，避免產(chǎn)生不愿意發(fā)生的“特殊情況”。instant 好處是只對(duì)數(shù)據(jù)字典中的元數(shù)據(jù)進(jìn)行更改。在SE更改期間不需要獲取元數(shù)據(jù)鎖，也不涉及表的數(shù)據(jù)。這個(gè)更改也影響了LOCK=…語(yǔ)義。沒(méi)有必要為INSTANT algorihtm指定鎖。

任何不能立即完成的操作設(shè)置ALGORITHM=INSTANT，您將得到一個(gè)錯(cuò)誤，如下所示。這里的思想是預(yù)先失敗并快速失敗，而不是進(jìn)行無(wú)聲的轉(zhuǎn)換并在幕后切換到另一個(gè)算法。

所以這是要注意的。這個(gè)功能是由騰訊游戲的DBA 團(tuán)隊(duì)提出的功能改進(jìn)。

那這個(gè)更改對(duì)實(shí)際當(dāng)中的意義在哪里

1 對(duì)于大型表，這可能需要很長(zhǎng)時(shí)間，特別是在復(fù)制設(shè)置中。
2 磁盤(pán)空間需求將增加一倍以上，大致與現(xiàn)有表的大小相同。
3 DDL操作需要大量資源，對(duì)CPU、內(nèi)存和IO的要求很高。這將從用戶(hù)事務(wù)中竊取資源。
4 如果涉及復(fù)制，用戶(hù)可能需要等待更長(zhǎng)的時(shí)間才能準(zhǔn)備好從服務(wù)器。DDL完成后將外部化

粗淺的說(shuō)完alter table 的 instant 的問(wèn)題， 下面的說(shuō)說(shuō)經(jīng)常要添加索引的問(wèn)題，在添加索引時(shí)是不能使用instant的功能的。目前在MYSQL 8 里面的最優(yōu)的還是inplace的方。(在你無(wú)法使用工具的時(shí)候)

我們繼續(xù)，一個(gè)測(cè)試，我們往test表里面插入數(shù)據(jù)，同時(shí)在另一個(gè)線(xiàn)程添加索引。

session 1 

session 2

他大致的操作步驟

• 新建frm臨時(shí)文件

• 鎖原表，不許DML，可以查詢(xún)

• 按聚集索引順序，查數(shù)據(jù)，找索引列數(shù)據(jù)，排序并插入到新的索引頁(yè)中

• 原表不能讀操作，也就是原表此時(shí)不提供讀寫(xiě)服務(wù)

• 進(jìn)行rename操作，替換frm文件，完成DDL過(guò)程

從上邊的圖可以看到，索引已經(jīng)添加并且表中國(guó)的數(shù)據(jù)也一直在插入，并沒(méi)有產(chǎn)生什么看似不良的影響。（以上操作在MGR 集群中操作）

當(dāng)然這不能說(shuō)明，就不會(huì)有問(wèn)題，生產(chǎn)系統(tǒng)的復(fù)雜性不是我們可以想象的，所以以上測(cè)試僅僅代表他能，但對(duì)非常繁忙的系統(tǒng)還是要小心。

而算法inplace——顧名思義，它修改表的模式，而不創(chuàng)建原始表的臨時(shí)表，而是修改原始表本身。在更改表模式(DDL)期間，它不會(huì)導(dǎo)致對(duì)原始表的讀寫(xiě)鎖(數(shù)據(jù)操作語(yǔ)言)

算法copy——顧名思義,它改變了模式的現(xiàn)有表創(chuàng)建一個(gè)新的臨時(shí)表改變模式(在我們的例子中,添加一個(gè)新的列),遷移到新的臨時(shí)表的數(shù)據(jù),改變了鏈接到新表,滴舊表,完成了。

使用ALGORITHM=COPY子句運(yùn)行的ALTER TABLE操作可以防止并發(fā)的DML操作。仍然允許并發(fā)查詢(xún)。也就是說(shuō)，表復(fù)制操作總是至少包含LOCK=SHARED(允許查詢(xún)，但不允許DML)的并發(fā)限制。您可以通過(guò)指定LOCK=EXCLUSIVE來(lái)進(jìn)一步限制此類(lèi)操作的并發(fā)性，這可以防止DML和查詢(xún)。

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。

當(dāng)前文章：MYSQL的DDL該怎么理解及應(yīng)用
轉(zhuǎn)載來(lái)于：http://muchs.cn/article18/ighidp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、網(wǎng)站策劃、網(wǎng)站收錄、商城網(wǎng)站、品牌網(wǎng)站制作、Google

廣告

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