MySQL5.6 online DDL table change test

First, popularize the basic knowledge here:

In earlier versions, online table modification, for example, adding a field, works as follows:

1. create table tmp like t1 to create a temporary table)

2. insert into tmp select * from t1 copy the original table data to the temporary table and update the index)

3. drop table t1; rename table tmp to t1 Delete the original table and change the temporary table to the original table t1)

In this process, the S lock will be added to table t1), so this price is very high.

In MySQL5.6, It is optimized. When a field is added to session 1, other sessions add, delete, modify, and query fields. The table is not affected and will not be locked.

For example, in this test, you can use Sysbench to generate a data record of 10 million rows. If the data size is larger, you can see the effect.

 
 

  
  
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 
 
 

In session 1, run:

 
 

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

Session 2: Execute:

 
 

  
  
delete from sbtest where id=1000; 

 
 

 
 

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

 
 

 
 

  
  
update sbtest set k=11 where id=1000; 
 
 

At this time, you will find that the table is not locked and the execution is complete successfully.

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

However, in MySQL5.5, such an operation locks the table ,:

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

So does MySQL5.6 online DDL lock the table? Let's test again like this:

In Session 1:

 
 

  
  
select * from sbtest; 
 
 

Deliberately execute a query with large results, and then delete the added field name.

 
 

  
  
alter table sbtest drop name; 
 
 

The table is locked ,:

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

That is to say, when an alter table is executed, adding, deleting, modifying, and querying the table will not lock the table. Before that, when the table is accessed, you must wait until it is executed.

Therefore, when going online in the early morning, be sure to check whether a slow SQL statement operates the table at this moment to avoid lock wait during table modification.

Reference manual:

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

 

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1185846