摘要:本文主要向大家介绍了MySQL数据库之5分钟了解MySQL5.7的Online DDL雷区 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之5分钟了解MySQL5.7的Online DDL雷区 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
Part1:写在最前
Online DDL,当新手听到这个名字的时候,非常高兴,以为无论什么情况下,修改表结构都不会锁表,理想很丰满,现实很骨感!读完本文,教你如何避开这些雷区,安全的修改表结构。话不多说,我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。
Part2:5.6.25的表现
①首先我们构造数据并进行测试
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长度是否锁表
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字段无法更新,被锁
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:心得
经测试,增加、删除字段或索引不锁全表,变更字段长度,锁表。
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号