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

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

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

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

所以我們在mysql 的正根 alter 語句在高版本上還的拿出來用，而這里面就牽扯，什么時候 inplace 什么時候 copy 到底這都是什么鬼 ？MYSQL 的 DDL 好累心。

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

本次主要是針對字段的DDL 的 增刪改來進行的

從上面的8.0 提供的表來看

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

但實際上可以看出這個立即能做的事情不多，adding a column  ，setting a column default value , Dropping the column default value 這些才可以進行instant 但 最常用到的 adding a column也上面有一個* 號，這說明不可以都可以，是要有條件的。

條件：

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

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

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

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

5  臨時表不可以

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

添加字段還是蠻快的。下面我們在一個新表，并且一直插入數(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;

*/

#定義存儲過程

delimiter //

DROP PROCEDURE IF EXISTS insert_test_val;

##num_limit 要插入數(shù)據(jù)的數(shù)量,rand_limit 最大隨機的數(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)用存儲過程

call insert_test_val();

我們在test 庫建立一個表，并且往里面插入大量的數(shù)據(jù)，然后我們

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

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

SESSION 1

SESSION 2

字段瞬間添加上了，但是存儲過程在運行的途中直接報錯，通過上表的實驗證明 MYSQL 8 添加字段，不在是一個問題，PT 工具可以收手了。

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

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

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

所以這是要注意的。這個功能是由騰訊游戲的DBA 團隊提出的功能改進。

那這個更改對實際當(dāng)中的意義在哪里

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

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

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

session 1 

session 2

他大致的操作步驟

• 新建frm臨時文件

• 鎖原表，不許DML，可以查詢

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

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

• 進行rename操作，替換frm文件，完成DDL過程

  
  

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

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

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

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

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

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。