阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL Online DDL 黄金准则

186次阅读
没有评论

共计 7630 个字符，预计需要花费 20 分钟才能阅读完成。

作为一名 DBA，对数据库进行 DDL 操作非常多，如添加索引，添加字段等等。对于 MySQL 数据库，DDL 支持的并不是很好，一不留心就导致了全表被锁，经常搞得刚入门小伙伴很郁闷又无辜，不是说 MySQL 支持 Online DDL 么，不是说不会锁表的么？是的，令人高兴的是从 MySQL5.6 开始就支持部分 DDL Online 操作了，但并不是全部喔，今天这里就对我们常用的 DDL 进行总结和说明，让操作 DDL 的小伙伴从此做到心中有数，得心应手，让老板们再也不用担心我们做 DDL 咯。

我自己遵守的一条黄金准则：DDL 永远不要在业务高峰期间执行。

环境说明：本次的测试服务器配置如下

CPU:32 cores
      MEM：128G
      DISK: SSD（固态硬盘）MySQL 版本：5.6.27 以上

一、MySQL 执行 DDL 原理

    MySQL 各版本，对于 DDL 的处理方式是不同的，主要有三种：

  • Copy Table 方式：这是 InnoDB 最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后 Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。
  • Inplace 方式：这是原生 MySQL 5.5，以及 innodb_plugin 中提供的方式。所谓 Inplace，也就是在原表上直接进行，不会拷贝临时表。相对于 Copy Table 方式，这比较高效率。原表同样可读的，但是不可写。
  • Online 方式：这是 MySQL 5.6 以上版本中提供的方式，也是今天我们重点说明的方式。无论是 Copy Table 方式，还是 Inplace 方式，原表只能允许读取，不可写。对应用有较大的限制，因此 MySQL 最新版本中，InnoDB 支持了所谓的 Online 方式 DDL。与以上两种方式相比，online 方式支持 DDL 时不仅可以读，还可以写，对于 dba 来说，这是一个非常棒的改进。

二、常用 DDL 执行方式总结

操作 支持方式 Allow R/W 说明
add/create index online 允许读写 FULLTEXT 索引除外，需要锁表，阻塞写
add fulltext index in-place 仅支持读，阻塞写

创建表上第一个 fulltext index 用 copy table 方式，除非表上 有FTS_DOC_ID列。

之后创建 fulltext index 用 in-place 方式，经过测试验证，第一次时 5.6 innodb

会隐含自动添加 FTS_DOC_ID 列，也就是 5.6 都是 in-place 方式
drop index online 允许读写 操作元数据，不涉及表数据。所以很快，可以放心操作
optimize table in-place or copy table(fulltext) 允许读写 or 阻塞写(fulltext)

5.6.17 版本以上才支持 inplace，之前版本是 copy-table；当带有 fulltext index 的表用

copy table 方式并且阻塞写
alter table…engine=innodb in-place or copy table(fulltext) 允许读写 or 阻塞写(fulltext)

5.6.17 版本以上才支持 inplace，之前是 copy-table，当带有 fulltext index 的表

用copy table 方式并且阻塞写
add column in-place
允许读写，(增加自增列除外)

1、添加 auto_increment 列要锁表，阻塞写;2、虽采用 in-place 方式，但是表数据需要

重新组织，所以增加列依然是昂贵的操作，小伙伴尤其注意啦（测试已验证）
drop column in-place
允许读写(增加自增列除外) 同 add column，重新组织表数据，，昂贵的操作
Rename a column online 允许读写 操作元数据; 不能改列的类型，否则就锁表（已验证）
Reorder columns in-place 允许读写 重新组织表数据，昂贵的操作
Make column NOT NULL in-place
允许读写 重新组织表数据，昂贵的操作
Change data type of column copy table 仅支持读，阻塞写 创建临时表，复制表数据，昂贵的操作（已验证）
Set default value for a column online 允许读写

操作元数据，因为 default value 存储在 frm 文件中，不涉及表数据。所以很快，

可以放心操作
alter table xxx auto_increment=xx online 允许读写 操作元数据，不涉及表数据。所以很快，可以放心操作
Add primary key in-place 允许读写 昂贵的操作（已验证）
Convert character set copy table 仅支持读，阻塞写 如果新字符集不同，需要重建表，昂贵的操作

 【注】：红色部分都需要注意的操作，会影响线上数据库性能

二、测试常用 DDL 执行方式

  • 测试用表：表大小 70M, 行数 13659
  • 初始表结构：
CREATE TABLE `t_mysql` (
  `checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL，
  `content` text，
  `content1` text，
  `content2` text，
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 测试机器开启 profiling：
root:test> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

1、add fulltext index

1) 用例 1：该语句执行期间是否锁表？

开两个 session。session 1：创建 fulltext index

dbadmin:test> alter table t_mysql add fulltext index idx_1(sample);
执行中.......

session 2: 进行 insert 数据，会一直等待中，阻塞写了

MySQL Online DDL 黄金准则

【结论 1】：创建全文索引时，仅支持读，阻塞写；dba 小伙伴加索引时要注意啦，而且执行时间超级超级长，在执行 ddl 时，尽量不要手动 kill，可能会导致异常，这里有个知识点。

2) 用例 2：创建表上第一个 fulltext index 用 copy table 方式，除非表上 有FTS_DOC_ID列。之后创建 fulltext index 用 in-place 方式？

  • 创建第一个全文索引：
root:test> alter table t_mysql add fulltext index idx_1(sample);
Query OK, 0 rows affected, 1 warning (15.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

这个时候发现0 rows affected，也就是说没有用 copy table 方式。这是为什么，官方文档上说第一个全文索引采用 copy table 方式的，难道官方文档上错误？再看下执行过程：

root:test> show profile for query 10;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000378 |
| checking permissions           |  0.000038 |
| checking permissions           |  0.000035 |
| init                           |  0.000032 |
| Opening tables                 |  0.000101 |
| setup                          |  0.000079 |
| creating table                 |  0.001043 |
| After create                   |  0.000217 |
| System lock                    |  0.000031 |
| preparing for alter table      |  0.023248 |
| altering table                 | 15.164399 |
| committing alter table to stor |  0.016108 |
| end                            |  0.000043 |
| query end                      |  0.000327 |
| closing tables                 |  0.000021 |
| freeing items                  |  0.000081 |
| logging slow query             |  0.000121 |
| cleaning up                    |  0.000060 |
+--------------------------------+-----------+
18 rows in set, 1 warning (0.00 sec)

在这上面也没有发现 copy tmp table 字样，说明确实没有进行表 copy。等等，在上面执行建全文索引时，有一个 warning，看下这个 warning：

root:test> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

到这里就明白了，原来当我们建第一个全文索引时，5.6 以上版本 innodb 会默认的为我们自动添加 FTS_DOC_ID，这样就避免了 copy table 了，所以相对会快些。

【结论 2】：5.6 以上版本 innodb 会默认的为我们自动添加 FTS_DOC_ID，所以第一次创建全文索引时避免了 copy table。我们可以自此认为 5.6 以上版本创建全文索引都是 in-place 方式。

2、optimize table & alter table...engine=innodb

注：测试前清除上面创建的全文索引，恢复表为初始

1) 用例：该语句执行期间是否锁表

1.1)不存在全文索引：

session1 执行：

root:test> alter table t_mysql engine=innodb;
Query OK, 0 rows affected (1.38 sec) # 没有数据受影响
Records: 0  Duplicates: 0  Warnings: 0

session2 同时执行：

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.14 sec)

当表上不存在全文索引时，optimize table 或者 alter table t_mysql engine=innodb 很快执行完成，并且不阻塞写；

1.2）存在全文索引时：

 步骤一：添加全文索引

CREATE TABLE `t_mysql` (`checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL,
  `content` text,
  FULLTEXT KEY `idx_1` (`sample`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

步骤二：session1：执行 optimize table 或者 alter table … engine=innodb

root:test> alter table t_mysql engine=innodb;
执行中.......

Query OK, 13661 rows affected (42.13 sec)   #说明进行 copy table 数据了
Records: 13661  Duplicates: 0  Warnings: 0
root:test> show profile for query 14;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000355 |
| checking permissions |  0.000071 |
| Opening tables       |  0.000151 |
| System lock          |  0.000188 |
| init                 |  0.000027 |
| Opening tables       |  0.000958 |
| setup                |  0.000062 |
| creating table       |  0.001235 |
| After create         |  0.000127 |
| System lock          |  0.045863 |
| copy to tmp table    | 43.937449 |
| rename result table  |  0.529001 |
| end                  |  0.000172 |
| Opening tables       |  0.000759 |
| System lock          |  0.002615 |
| query end            |  0.000402 |
| closing tables       |  0.000011 |
| freeing items        |  0.000022 |
| cleaning up          |  0.000033 |
+----------------------+-----------+

session 2：模拟插入数据：

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll'); 

等待中.......

当表上存在全文索引时，我们执行 optimize table 或者 alter table t_mysql engine=innodb 采用 copy table 方式，而且锁全表，阻塞写；

【结论 1】：当表上不存在全文索引时，optimize table 或者 alter table t_mysql engine=innodb 采用 in-place 方式，并且不阻塞写；

                当表上存在全文索引时，我们执行 optimize table 或者 alter table t_mysql engine=innodb 采用 copy table 方式，而且锁全表，阻塞写；

 3、add column

 1）用例 1：添加 auto_increment 列要锁表，阻塞写？

 session 1：

root:test> alter table t_mysql add column id int not null primary key auto_increment;
Query OK, 0 rows affected (1.41 sec)

session 2：

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');

waitting......
......
......
......
......
......
Query OK, 1 row affected (0.97 sec)

当添加自增列时，会阻塞写。

 2）用例 2：添加普通列，online？

session 1：

root:test> alter table t_mysql add column content1 text;
Query OK, 0 rows affected (1.36 sec)  #in-place 方式
Records: 0  Duplicates: 0  Warnings: 0

session 2：

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.01 sec)

当添加一个普通列时，是 online 的，不阻塞写入。

4、change column type

session 1：

root:test> alter table t_mysql change content1 content1 longtext;  
Query OK, 13674 rows affected (1.37 sec)  # copy table
Records: 13674  Duplicates: 0  Warnings: 0
 
root:test> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000302 |
| checking permissions | 0.000027 |
| checking permissions | 0.000045 |
| init                 | 0.000024 |
| Opening tables       | 0.000097 |
| setup                | 0.000067 |
| creating table       | 0.001379 |
| After create         | 0.000165 |
| System lock          | 0.004105 |
| copy to tmp table    | 1.327642 |  #copy table
| rename result table  | 0.034565 |
| end                  | 0.000473 |
| query end            | 0.001067 |
| closing tables       | 0.000263 |
| freeing items        | 0.000414 |
| logging slow query   | 0.000478 |
| cleaning up          | 0.001074 |
+----------------------+----------+

session 2：并发 DML

dbadmin:test> insert into t_mysql(checksum,sample,content1) values(0113,'测试全文索引','darrenllllllllllllll');

WAITTING.......
.......
.......
.......
.......
.......
Query OK, 1 row affected (0.95 sec)

【结论】：修改列类型 DDL 采用 copy table 方式并且阻塞写入，在线上操作必须谨慎再谨慎！

以上就是我经常进行线上的 DDL 操作了，如果还有其他 DDL 请查看下面的官方链接。从此，DBA 小伙伴进行 DDL 操作不再侥幸也不再盲目，做到心中有杆秤。

另外，我的一些建议：

 1、尽量不要在业务高峰期间进行 DDL，即使是 online DDL;

 2、对于大表（G 级别）DDL，最好在测试库上做一遍，预估下时间，不至于到线上执行时心慌手乱；（线上和测试环境数据量差不多）

 3、小心使得万年船，谨记上面博主为大家总结的”黄金准则”；

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/141126.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL Online DDL MySQL Online DDL 黄金准则 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计7630字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
ORA-00257: archiver error. Connect internal only, until freed 错误解决方案
CentOS6 更改MySQL数据存放位置
MySQL5.6.18解压包版在RHEL6.7上安装
EXPDP 时ORA-27054 问题解决方法
MySQL binlog中的事件类型
MySQL多源复制详解
Redis的几种应用实战
关于 Oracle redo与undo 的认识
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3459422
评论数
4
文章搜索
热门文章
基于开源MaxKB构建大语言模型的本地知识库系统

基于开源MaxKB构建大语言模型的本地知识库系统

基于开源 MaxKB 构建大语言模型的本地知识库系统 什么是 MaxKB MaxKB 是一款基于 LLM（La...
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
vmware下的网卡分配问题

vmware下的网卡分配问题

导读 vmware 虚拟机下 linux centos6.6 只有 lo，没有 eth0 网卡、随机分配 ip...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
一起探知Spring MVC原理

一起探知Spring MVC原理

导读 Spring MVC 属于 SpringFrameWork 的后续产品，已经融合在 Spring Web...
zabbix详解之zabbix的安装

zabbix详解之zabbix的安装

由于 ubuntu 源里自带有 zabbix，可以直接通过 apt-get 的方式进行安装，所以这里就以 ce...
使用 Diff 和 Meld 工具比较两个目录的不同处

使用 Diff 和 Meld 工具比较两个目录的不同处

一般情况下，要在 Linux 下比较两个文件，我们会使用 diff（一个简单的源自 Unix 的命令行工具）来...
Ansible普通用户su切换问题详解

Ansible普通用户su切换问题详解

在现网应用中，安全加固后的主机是不允许直接以 root 用户登陆的，而很多命令又需要 root 用户来执行，在...
ansible使用小结：ansible的安装

ansible使用小结：ansible的安装

一、简介 Ansible 是一个配置管理和应用部署工具，功能类似于目前业界的配置管理工具 Chef,Puppe...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中