mysql oldaltertable_MySQL5.6 ALTER TABLE 分析和测试

最新推荐文章于 2022-08-11 23:22:10 发布
最新推荐文章于 2022-08-11 23:22:10 发布
阅读量1k 收藏
点赞数
文章标签： mysql oldaltertable
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39876514/article/details/113517296
版权

在MySQL5.5和之前版本，在运行的生产环境对大表(超过数百万纪录)执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。因此知道Alter操作何时结束对我们是非常重要的.甚至当执行Create index的时候.如果启用了 fast_index_creation，则不会重建表，但是仍然会锁表。fast_index_creation特性引进在MySQL5.5和更高版本里。在MySQL5.1中如果使用了innodb plugin则也可以使用该特性。

自从MySQL5.6开始，Online DDL特性被引进。他增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。但是即使在MySQL5.6，仍然有一些Alter操作需要重建表，比如增加/删除列，增加/删除主键,改变数据类型等。

MySQL5.6 Online DDL在线状态概况如下(Yes*和No*表明结果依赖于其他一些附加条件)：

执行操作

允许ALGORITHM=INPLACE

是否拷贝表

允许并发DML

允许并发查询

备注和注意事项

create index

add index

Yes*

No*

Yes

Yes

对于全文索引，有一些限制，具体看下一行。目前，该操作不是在原地执行，需要拷贝表.

add fulltext index

Yes

No*

No

Yes

创建第一个全文索引涉及到拷贝表，除非有使用FTS_DOC_ID列。后面的全文索引则在原地执行。

drop index

Yes

No

Yes

Yes

optimize table

Yes

Yes

Yes

Yes

在MySQL5.6.17里使用 ALGORITHM=INPLACE.

如果设置old_alter_table=1或使用mysqld --skip-new选项，则使用 ALGORITHM=COPY.

如果表使用了全文索引，则 ALGORITHM=INPLACE不适用

set default value for  column

Yes

No

Yes

Yes

修改.frm文件，不涉及数据文件

change auto-increment value

Yes

No

Yes

Yes

修改存储到内存的一个值，不修改数据文件

add  foreign key constraint

Yes*

No*

Yes

Yes

禁用foreign_key_checks,则可以避免拷贝表

drop  forgien key constraing

Yes

No

Yes

Yes

foreign_key_checks可以禁用或开启

rename  column

Yes*

No*

Yes*

Yes

允许并发DML，保持相同的数据类型，仅改变字段名

add  column

Yes

Yes

Yes*

Yes

增加auto-increment字段时不允许DML操作.

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

drop column

Yes

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

reorder columns

Yes

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

change ROW_FORMAT

property

Yes

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

change KEY_BLOCK_SIZE

property

Yes

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

make column null

Yes

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

make cplumn not null

Yes*

Yes

Yes

Yes

当SQL_MODE为strict_all_tables,如果执行的列包含null，则会执行失败。

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

change data type

of column

No

Yes

Yes

Yes

add primary key

Yes*

Yes

Yes

Yes

虽然ALGORITHM=INPLACE可以允许,

但是数据要重组，代价比较昂贵.

如果列必须转换为非空的条件下，

ALGORITHM=INPLACE是不允许的。

drop primary key

and add other

Yes

Yes

Yes

Yes

当在同一个alter table新增主键时ALGORITHM=INPLACE是允许的.数据要重组，因此代价比较昂贵.

drop primary key

No

Yes

No

Yes

删除主键但是又不新增主键是被限制的

convert character set

No

Yes

No

Yes

如果新的字符编码不同将会重建表

specify character set

No

Yes

No

Yes

如果新的字符编码不同将会重建表

rebulid with

force option

Yes

Yes

Yes

Yes

在MySQL5.6.17里使用 ALGORITHM=INPLACE.

如果设置old_alter_table=1或使用mysqld --skip-new选项，则使用 ALGORITHM=COPY.

如果表使用了全文索引，则 ALGORITHM=INPLACE不适用

rebulid with

"null"

alter table ...

engine=innodb

Yes

Yes

Yes

Yes

在MySQL5.6.17里使用 ALGORITHM=INPLACE.

如果设置old_alter_table=1或使用mysqld --skip-new选项，则使用 ALGORITHM=COPY.

如果表使用了全文索引，则 ALGORITHM=INPLACE不适用

下面做测试，通过ALGORITHM=INPLACE和ALGORITHM=COPY两种不同算法下下分别删除和新增一个字段，ALGORITHM=INPLACE(online ddl)下删除和增加字段时可以进行DML操作，但是由于数据要重组，Alter时间比较长。而在ALGORITHM=COPY下因为要拷贝表和锁表,所以在执行过程中DML操作都会等待metadata lock，但是执行Alter时间要比ALGORITHM=INPLACE短很多。

1.ALGORITHM=INPLACE模式删除字段测试：

session 1:

13:55:11 pe> alter table product_2 ALGORITHM=INPLACE,drop column ext_1;

Query OK, 0 rows affected (42.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

13:56:20 pe>

session 2:

13:55:43 pe> update product_2 set category_id=5312 where id=621;

Query OK, 1 row affected (2.16 sec)

Rows matched: 1 Changed: 1 Warnings: 0

13:55:45 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(2000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.19 sec)

13:55:45 pe> delete from product_2 where id=2000001;

Query OK, 1 row affected (2.57 sec)

2.ALGORITHM=INPLACE模式添加字段测试：

session 1:

13:59:05 pe> alter table product_2 ALGORITHM=INPLACE,add column ext_1 char(10);

Query OK, 0 rows affected (42.98 sec)

Records: 0 Duplicates: 0 Warnings: 0

13:59:51 pe>

session 2:

13:59:28 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (2.37 sec)

Rows matched: 1 Changed: 0 Warnings: 0

13:59:30 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(2000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.00 sec)

13:59:30 pe> delete from product_2 where id=2000001;

Query OK, 1 row affected (2.50 sec)

13:59:32 pe>

3.ALGORITHM=COPY模式删除字段测试：

session1:

13:59:32 pe> alter table product_2 ALGORITHM=COPY,drop column ext_1;

Query OK, 1999999 rows affected (20.91 sec)

Records: 1999999 Duplicates: 0 Warnings: 0

session2:

14:03:15 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (16.80 sec)

Rows matched: 1 Changed: 0 Warnings: 0

14:03:32 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(2000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.00 sec)

14:03:32 pe> delete from product_2 where id=2000001;

Query OK, 1 row affected (1.61 sec)

14:03:33 pe>

4.ALGORITHM=COPY模式添加字段测试：

session1:

14:06:40 pe> alter table product_2 ALGORITHM=COPY,add column ext_1 char(10);

Query OK, 1999999 rows affected (21.77 sec)

Records: 1999999 Duplicates: 0 Warnings: 0

session2:

14:03:33 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (11.90 sec)

Rows matched: 1 Changed: 0 Warnings: 0

14:07:28 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(2000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.00 sec)

14:07:28 pe> delete from product_2 where id=2000001;

Query OK, 1 row affected (1.71 sec)

14:07:29 pe>

processlist:

987835 root localhost pe Query 7 Waiting for table metadata lock update product_2 set category_id=5312 where id=621

MySQL 5.6 Alter Table 常用场景测试：

MySQL 5.6的Alter Table有ALGORITHM=INPLACE和ALGORITHM=COPY两种不同算法。使用ALGORITHM=INPLACE可以允许在执行AlterDe时候并发执行DML语句。但是耗费的代价也比较大，在这种模式下Alter时间约是ALGORITHM=COPY算法的2倍左右。

如下测试：在5.6中，Alter Table首选使用ALGORITHM=INPLACE算法。其中加字段，加索引，加主键，字段设置默认值都允许在Alter的同时进行DML操作，而更改字段类型则要锁表。

1.加字段

session1:

17:03:40 pe> alter table product_2 add column ext_1 char(10);

Query OK, 0 rows affected (42.52 sec)

Records: 0 Duplicates: 0 Warnings: 0

17:04:31 pe>

session2:

17:03:50 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (2.09 sec)

Rows matched: 1 Changed: 0 Warnings: 0

17:03:54 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(2000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.15 sec)

17:03:54 pe> delete from product_2 where id=2000001;

Query OK, 1 row affected (1.69 sec)

17:03:56 pe>

2.加索引

session1:

17:12:34 pe> alter table product add index idx_1(category_id);

Query OK, 0 rows affected (49.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

17:13:38 pe>

session2:

17:12:51 pe> update product set category_id=5312 where id=621;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

17:12:52 pe> insert into product(id,product_code,product_cname,product_ename,category_id) values(20000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.01 sec)

17:12:52 pe> delete from product where id=2000001;

Query OK, 1 row affected (0.05 sec)

17:12:53 pe>

3.加主键

session1:

17:38:15 pe> alter table product_2 add primary key(id);

Query OK, 0 rows affected (47.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

17:39:48 pe>

session2:

17:39:03 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (2.07 sec)

Rows matched: 1 Changed: 0 Warnings: 0

17:39:07 pe> insert into product_2(id,product_code,product_cname,product_ename,category_id) values(20000001,'000222222','cname','ename',100);

Query OK, 1 row affected (0.00 sec)

17:39:07 pe> delete from product_2 where id=2000001;

Query OK, 0 rows affected (1.66 sec)

17:39:09 pe>

4.设置默认值，只改变.frm文件，很快

17:20:48 pe> alter table product_2 modify ext_1 char(10) default 'test';

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

5.更改字段类型，会锁表

session1:

17:22:22 pe> alter table product_2 modify ext_1 datetime;

Query OK, 1999999 rows affected (24.90 sec)

Records: 1999999 Duplicates: 0 Warnings: 0

17:28:34 pe>

session2:

17:28:12 pe> update product_2 set category_id=5312 where id=621;

Query OK, 0 rows affected (22.97 sec)

Rows matched: 1 Changed: 0 Warnings: 0

17:28:37 pe>

weixin_39876514
关注
mysql 在线DDL ＜old_alter_table参数＞
Omega29的博客
07-09 853
old_alter_table 命令行格式 --old-alter-table[={OFF|ON}] 系统变量 old_alter_table 范围 全局，会话 动态的 是的 类型 布尔值 默认值 OFF 启用此变量后，服务器不会使用优化的方法来处理ALTER TABLE操作。它恢复使用临时表，复制数据，然后将临时表重命名为原始表，如 MySQL 5.0 及更早版本所使用的那样。 5.7默认为OFF mysql中的ALTER .
mysql 5.6 binlog_format_ROW 格式binlog 在MySQL5.6上的数据恢复实验
weixin_29061997的博客
02-07 423
5.6和5.7版本的MySQL，有个参数binlog_row_image，默认值为FULL，表示记录的是全部的binlog操作日志(仅在binlog_format=ROW时候生效)。此外binlog_row_image还可以是minimal，表示binlog记录的就只是影响后的行。如此一来使用ROW格式就能节约很多的磁盘空间。因此，我们服务器上就可以直接设置binlog_format=ROW格式了...
mysql oldaltertable_MySQL简单操作之alter table改变表的结构
weixin_30415591的博客
01-30 840
我们知道，MySQL数据库常常与PHP结合来开发出功能强大的应用程序。那么对于我们初学PHP编程的人来说，就很有必要了解一些MySQL数据库的简单操作了。本文我们对MySQL数据库使用alter table语句来修改表结构的知识进行了介绍，接下来就让我们来一起了解一下这部分内容。1. 增加列alter table tbl_name add col_name type例如, 给pet的表增加一列 ...
mysql 8.0 alter table ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
一名数据库爱好者的专栏
09-10 1129
The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is u
新特性解读 | MySQL8.0 ALTER TABLE … ALGORITHM=INSTANT
杨建荣的学习笔记
08-11 408
作者：张娜爱可生南区DBA组成员，负责MySQL、TiDB日常维护、故障处理。本文来源：原创投稿* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。一、前言MySQL 8.0.29 之前，在线 DDL 操作中即时添加列只能添加在表的最后一列，对于在某个具体列后面快速添加列很不方便，MySQL 8.0.29 扩展了对 ALTER TABLE … ALGORITHM=INS...
mysql 释放内存_Mysql5.6启动内存占用过高解决方案
weixin_36125467的博客
01-18 1057
最近把MySQL升级到了5.6版本，无意中在任务管理器发现MySQL内存占用达到400+M，这是怎么回事呢？以前的版本可没有遇到过这种情况啊。开始的时候还以为是缓存了某个数据量较大的表的数据。于是重启MySQL服务，但结果出乎意料，MySQL服务一起启动，内存就又飙升到400+M。心想，这肯定不是缓存的那个表数据的问题了。接下来就是寻找解决MySQL 5.6 内存占用过高这个问题的解决方案了。 经...
MySQL中的alter table命令的基本使用方法及提速优化
09-10
使用`SHOW STATUS`或`EXPLAIN`来监控和分析`ALTER TABLE`操作的性能。 通过了解`ALTER TABLE`命令的基本用法和优化技巧，可以更高效地管理MySQL数据库的表结构，降低对系统性能的影响。在实际操作中，应根据具体...
mysql 碎片率_MySQL 5.6 整理表的碎片
weixin_36464343的博客
01-13 655
QQ截图20200702141000.png可以看到，当前表的碎片率超高了，50.6%。有三种办法整理碎片alter table table_name engine=innodboptimize tablept-online-schema-change这三种操作都是先创建一个临时表复制完成后再删除旧表，所以在执行操作的过程中磁盘会先增大。会锁表optimize no_write_to_binlog...
MySQL升级之路（5.6-8.0）
一只小鹰
07-08 4138
原文地址：https://www.jeremyjone.com/847/， 转载请注明。 这几天需要升级 MySQL，又折腾了半天，这个升级之路还是有一点曲折的。 升级之前 在升级之前需要做几件事情： 做好备份 备份 /var/lib/mysql 路径下的所有文件 cp -r /var/lib/mysql /var/lib/mysql_backup 备份 /etc/my.cnf 文件 cp /etc/my.cnf /etc/my_backup.cnf 备份整个数据库 mysqldum
ALTER TABLE操作（ALGORITHM = INPLACE）的中间退出，则可能会留下孤立的中间表，该表占用了系统空间
sinat_36355775的博客
01-28 1842
如果MySQL在就地ALTER TABLE操作（ALGORITHM = INPLACE）的中间退出，则可能会留下孤立的中间表，该表占用了系统空间。另外，否则为空的常规表空间中的孤立中间表会阻止您删除常规表空间。本节介绍如何识别和删除孤立的中间表。 中间表名称以＃sql-ib前缀开头（例如，＃sql-ib87-856498050）。随附的.frm文件具有＃sql- *前缀，并具有不同的名称（例如，＃sql-36ab_2.frm）。 要标识系统上的孤立中间表，可以查询INFORMATION_SCHEMA.I
MySQL5.6 ALTER TABLE 分析和测试
weixin_30498921的博客
12-14 364
在MySQL5.5和之前版本，在运行的生产环境对大表（超过数百万纪录）执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。因此知道Alter操作何时结束对我们是非常重要的.甚至当执行Create index的时候.如果启用了fast_index_creation，则不会重建表，但是仍然会锁表。fast_index_creation特性引进在MySQL5.5和更高版本里。在...
翻译：MariaDB ALTER TABLE语句
weixin_33690367的博客
09-26 235
本文是MariaDB官方ALTER TABLE手册的翻译，99%的内容是按照手册给的内容进行翻译的。 原文地址：https://mariadb.com/kb/en/library/alter-table/ 我提交到MariaDB官方手册的译文：https://mariadb.com/kb/zh-cn/library/alter-table/ Syntax ALTER [ONLINE] ...
mysql alter 判断_MySQL系统学习三：管理表语法之alter table
weixin_39977136的博客
01-27 676
学习MySQL管理表语句之前先来学习几个查看表相关信息的SQL语句：##表状态SHOW TABLE STATUS LIKE 'tableName'##like语法 like的是字符串##查看表结构的3种语法DESC tableNameDESCRIBE tableNameshow [full] columns from tableName##columns[ˈkɑ:ləm] 字段、列MySQL的管...
ALTER TABLE(修改表结构)
weixin_34265814的博客
04-09 438
2019独角兽企业重金招聘Python工程师标准>>> ...
SQL_alter_table已有表操作
jerrygaoling的博客
03-04 248
ALTER TABLE已有表操作 功能： ALTER TABLE 语句用于在已有的表中添加、删除或修改列。 如需在表中添加列，请使用下面的语法: ALTER TABLE table_name ADD column_name datatype column_name：新加列名 datatype：列数据属性 如需删除表中的列，请使用下面的语法 ： My SQL / Oracle： AL...
Mysql中慢SQL的分析与优化
独行侠梦的博客
11-21 1370
为何对慢SQL进行治理从数据库角度看：每个SQL执行都需要消耗一定I/O资源，SQL执行的快慢，决定资源被占用时间的长短。假设总资源是100，有一条慢SQL占用了30的资源共计1分钟。那么...
优化：数据碎片
eafun_888的博客
03-08 1307
一：数据碎片化原因 Mysql引擎不同，碎片化的原因也有所差别。 在InnoDB中，了，因而空间也没有真的被释放回收。 1：删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除，所以就没能释放回收。InnoDB的 Purge线程会异步的来清理这些没用的索引键和行。但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，...
mysql ddl 解析_MySQL 5.6 原生Online DDL解析
weixin_31814585的博客
01-27 574
做MySQL的都知道，数据库操作里面，DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。然后 mysql 5.6 开始，大家期待的Online DDL出现了，可以实现修改表结构的同时，依然允许DML操作(select,insert,update,delete)。在这个特性出现以前，用的比较多的工具是pt...
MySQL5.6基础教程：连接、查询与数据库操作
此外，`ALTER TABLE`用于修改已有表结构，`DROP DATABASE`和`DROP TABLE`用于删除数据库和表。 这些只是MySQL5.6基础知识的一部分，还有更多高级概念，如索引、视图、存储过程、触发器、事务处理等，都需要深入学习...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值