MySQL5.6 線上DDL更改表測試

先在這裡普及下基礎知識：

在之前的版本，線上更改表，比如增加一個欄位，其內部原理是這樣的：

1、create table tmp like t1建立一個暫存資料表）

2、insert into tmp select * from t1一行行的把原表資料拷貝到暫存資料表裡，且更新索引）

3、drop table t1;rename table tmp to t1刪除原表並把暫存資料表改名為原表t1）

在這個過程中會對t1表加S鎖共用鎖定），所以這個代價是很高的。

而在MySQL5.6裡，對其進行了最佳化，當會話一增加欄位時，其他會話增，刪，改，查，均不受影響，不會鎖表。

比如這樣測試，用Sysbench產生一張1000萬行的資料，資料大一些，可以看出效果來。

 

  
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=100 --num-threads=16 
  
--mysql-host=192.168.110.140 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=test 
  
--mysql-socket=/tmp/mysql.sock prepare 
 

然後在會話一，執行：

 

  
alter table sbtest add name varchar(10) after pad; 
 

會話二，執行：

 

  
delete from sbtest where id=1000; 

 

 

  
insert into sbtest values(1000,1,'abc','abc','abc');  

 

 

  
update sbtest set k=11 where id=1000; 
 

這時你會發現並沒有鎖表，順利執行完畢。

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2255334Q1-0.jpg" alt="" />

但在MySQL5.5裡，這樣的操作是會鎖表的，：

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131228/22553321a-1.jpg" />

那麼MySQL5.6 線上DDL就一定不鎖表嗎？我們再這樣測試：

在會話一：

 

  
select * from sbtest; 
 

故意執行一條大結果的查詢，然後再執行刪除剛才增加的欄位name

 

  
alter table sbtest drop name; 
 

這時就會把表給鎖了，：

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2255334010-2.jpg" alt="" />

也就是說，在執行alter table表時，對該表的增、刪、改、查均不會鎖表。而在這之前，該表有被訪問時，需要等其執行完畢後，才可以執行alter table。

所以在淩晨上線時，一定要觀察下，此時此刻，是否有某個慢SQL對該表進行操作，以免改表時出現鎖等待現象。

參考手冊：

650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131228/2255332337-3.jpg" alt="" />

 

本文出自 “賀春暘的技術專欄” 部落格，請務必保留此出處http://hcymysql.blog.51cto.com/5223301/1185846