5分钟了解MySQL5.7的Online DDL雷区
来源:互联网 发布:atv444最新域名 编辑:程序博客网 时间:2024/04/30 20:25
原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://suifu.blog.51cto.com/9167728/1855872
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
68
69
70
71
72
73
74
75
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` $$
and
()*row_num),now(),repeat(
'su'
, floor(rand()*20)));
set
i = i+1;
END
while;
end
$$
delimiter ;
Query OK, 0
rows
affected, 1 warning (0.01 sec)
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
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)
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
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
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.
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.
0 0
- 5分钟了解MySQL5.7的Online DDL雷区
- MySQL5.6支持的online DDL操作及注意事项
- 5分钟了解MySQL5.7的undo log在线收缩新特性
- 5分钟了解MySQL5.7union all用法的黑科技
- 5分钟了解MySQL5.7的undo log在线收缩新特性
- Mysql5.6特性--- DDL online operate
- 10分钟了解MySQL5.7对原生JSON的支持与用法
- 5分钟了解MySQL5.7对in用法有什么黑科技
- Mysql5.6特性之细解--- DDL online operate
- MySQL5.6在线表结构变更(online ddl)总结
- MySQL Online DDL的改进与应用
- MySQL Online DDL的改进与应用
- Mysql5.6 借助工具pt-online-schema-change在线DDL(还有加索引)
- 5分钟了解Mockito
- 5分钟了解Mockito
- 5分钟了解Mockito
- 5分钟了解Mockito
- 5分钟了解mockito
- Ajax技术的优缺点分析
- 图解正向代理、反向代理、透明代理
- XML 学习(一)
- html文本域
- Pb使用POSTURL
- 5分钟了解MySQL5.7的Online DDL雷区
- FineUploader文件/图片和form表单参数一起上传,从入门到精通教程
- 第四周项目3-单链表的应用(3)
- poj 3686 The Windy's
- 安卓中的dialog的使用
- Unity_二维纹理 Texture 2D_1_Properties 属性
- linux下的wireshark抓包软件--Tcpdump 安装
- Unix网络编程之服务器回射客户端多线程编程01-server.c
- Gradle Java Project