Part1:写在最前
Online DDL，当新手听到这个名字的时候，非常高兴，以为无论什么情况下，修改表结构都不会锁表，理想很丰满，现实很骨感！读完本文，教你如何避开这些雷区，安全的修改表结构。话不多说，我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。
Part2:5.6.25的表现
①首先我们构造数据并进行测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
mysql>
create
database
helei;
Query OK, 1 row affected (0.01 sec)
mysql> use helei;
Database
changed
mysql>
create
table
helei(
-> id
int
(10) unsigned
NOT
NULL
AUTO_INCREMENT,
-> c1
int
(10)
NOT
NULL
DEFAULT
'0'
,
-> c2
int
(10) unsigned
DEFAULT
NULL
,
-> c5
int
(10) unsigned
NOT
NULL
DEFAULT
'0'
,
-> c3
timestamp
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
,
-> c4
varchar
(200)
NOT
NULL
DEFAULT
''
,
->
PRIMARY
KEY
(id),
->
KEY
idx_c1(c1),
->
KEY
idx_c2(c2)
-> )ENGINE=InnoDB ;
Query OK, 0
rows
affected (0.02 sec)
mysql> delimiter $$
mysql>
drop
procedure
if exists `insert_helei` $$
mysql>
create
procedure
`insert_helei`(
in
row_num
int
)
->
begin
->
declare
i
int
default
0;
-> while i < row_num do
->
insert
into
helei(c1, c2, c5,c3, c4)
values
( floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat(
'su'
, floor(rand()*20)));
->
set
i = i+1;
->
END
while;
->
end
$$
Query OK, 0
rows
affected (0.00 sec)
mysql> delimiter ;
mysql> call insert_helei(1000000);
Query OK, 1 row affected (18
min
52.88 sec)
mysql>
desc
helei;
+
-------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
-------+------------------+------+-----+-------------------+-----------------------------+
| id |
int
(10) unsigned |
NO
| PRI |
NULL
| auto_increment |
| c1 |
int
(10) |
NO
| MUL | 0 | |
| c2 |
int
(10) unsigned | YES | MUL |
NULL
| |
| c5 |
int
(10) unsigned |
NO
| | 0 | |
| c3 |
timestamp
|
NO
| |
CURRENT_TIMESTAMP
|
on
update
CURRENT_TIMESTAMP
|
| c4 |
varchar
(200) |
NO
| | | |
+
-------+------------------+------+-----+-------------------+-----------------------------+
6
rows
in
set
(0.01 sec)
mysql>
alter
table
helei
add
c6
varchar
(60)
not
null
default
''
;
Query OK, 0
rows
affected (9.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
select
count
(*)
from
helei;
+
----------+
|
count
(*) |
+
----------+
| 1000000 |
+
----------+
1 row
in
set
(0.14 sec)
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(80)
not
null
default
''
;
ERROR 1846 (0A000): ALGORITHM=INPLACE
is
not
supported. Reason: Cannot change
column
type INPLACE. Try ALGORITHM=COPY.
mysql>
alter
table
helei
modify
c6
varchar
(80)
not
null
default
''
;
Query OK, 1000000
rows
affected (9.44 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
|
Tips:心得
可以看出5.6.25这里并不支持ALGORITHM=INPLACE，而直接对表进行修改的话用了9.44s。
②接下来我们测试下变更varchar长度是否锁表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql>
alter
table
helei
modify
c6
varchar
(40)
not
null
default
''
;
Query OK, 1000000
rows
affected (9.26 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql>
update
helei
set
c1=9999
where
id=1;
Query OK, 0
rows
affected (7.77 sec)
Rows
matched: 1 Changed: 0 Warnings: 0
mysql> show processlist;
+
----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id |
User
| Host | db | Command |
Time
| State | Info |
+
----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
| 6 | root | localhost | helei | Query | 2 | copy
to
tmp
table
|
alter
table
helei
modify
c6
varchar
(40)
not
null
default
''
|
| 7 | root | localhost | helei | Query | 1 | Waiting
for
table
metadata lock |
update
helei
set
c1=9999
where
id=1 |
| 8 | root | localhost |
NULL
| Query | 0 | init | show processlist |
+
----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
3
rows
in
set
(0.00 sec)
|
可以看到在对c6字段修改表结构时，对c1字段无法更新，被锁
|
1
2
3
4
5
6
7
|
mysql>
alter
table
helei
add
c7
varchar
(60)
not
null
default
''
;
Query OK, 0
rows
affected (8.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
update
helei
set
c1=9999
where
id=1;
Query OK, 0
rows
affected (0.00 sec)
Rows
matched: 1 Changed: 0 Warnings: 0
|
Tips:心得
经测试，增加、删除字段或索引不锁全表，变更字段长度，锁表。
Part3:5.7.15的表现
|
1
2
3
4
5
6
7
8
9
10
|
mysql>
select
count
(*)
from
helei;
+
----------+
|
count
(*) |
+
----------+
| 1000000 |
+
----------+
1 row
in
set
(0.11 sec)
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(80)
not
null
default
''
;
Query OK, 0
rows
affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
Tips:心得
可以看到，这里用时0.01sec，和5.6的9.44sec效率差了相当多吧~
Warning:警告1
不过，ALGORITHM用法只对varcahr类型有效哦，比如我们对c1列int型进行变更：
c1原为int(10) 变更为int(11) unsigned则无效
|
1
2
|
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c1
int
(11) unsigned
not
null
;
ERROR 1846 (0A000): ALGORITHM=INPLACE
is
not
supported. Reason: Cannot change
column
type INPLACE. Try ALGORITHM=COPY.
|
Tips:心得
只变更int的位数，是可以的，不过这没什么意义，因为无论你int多少，最多都只能存10位，这也就是为什么我们生产库开发规范要定义所有的int都用int(10)。
生产库开发规范详见：
http://suifu.blog.51cto.com/9167728/1792604
Warning:警告2
如果字段属性大于并等于varchar(256)（这里的256是指字节（UTF8占用3字节）或者把varchar(80)减少到varchar(70)或者更少)，则仍需要拷贝数据且锁全表。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(84)
not
null
default
''
;
Query OK, 0
rows
affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(85)
not
null
default
''
;
Query OK, 0
rows
affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(86)
not
null
default
''
;
ERROR 1846 (0A000): ALGORITHM=INPLACE
is
not
supported. Reason: Cannot change
column
type INPLACE. Try ALGORITHM=COPY.
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(40)
not
null
default
''
;
ERROR 1846 (0A000): ALGORITHM=INPLACE
is
not
supported. Reason: Cannot change
column
type INPLACE. Try ALGORITHM=COPY.
mysql>
alter
table
helei ALGORITHM=INPLACE,
modify
c6
varchar
(70)
not
null
default
''
;
ERROR 1846 (0A000): ALGORITHM=INPLACE
is
not
supported. Reason: Cannot change
column
type INPLACE. Try ALGORITHM=COPY.
|
Warning:警告3
添加字段alter table时，对该表的增删改查均不会锁表。而在这之前，当该表被访问时，需要等其执行完毕后才可以执行alter table。
——总结——
在varchar变更字段长度方面，5.7的新特性ALGORITHM参数可以快速调整varchar类型的字段长度。5.7同5.6一样，增加，删除字段或索引不锁全表，删除主键锁全表。因此，在上线时，一定要执行show processlist命令并观察，此刻是否有某个慢SQL对该表进行操作，以免alter table表时出现锁表现象。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1855872，如需转载请自行联系原作者
