Mysql在线添加索引

还是转转

已于 2023-04-24 14:52:35 修改

阅读量3.8k

点赞数 5

分类专栏：数据库与sql 文章标签： mysql 数据库 java

于 2021-01-13 23:15:28 首次发布

本文链接：https://blog.csdn.net/xiaoyi52/article/details/112596371

版权

数据库与sql 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

在实际工作中，经常遇到需要给数据库表添加索引的情况。虽然操作是由dba来执行，但开发还是应该了解在线添加索引会引起的性能问题。比如博主最近就遇到了线上添加索引导致业务报警的问题。

问题描述

出于业务需要，给一个表添加普通索引，但这个表有100个分表，因此需要给100个分表都加上。平均每张表大概有500万行的数据量。线上业务流量也比较高。mysql版本为5.7版本，在线添加索引问题不大。跟dba商量好之后，提交sql脚本由dba实施。经实践发现，平均一张表添加索引大概需要3~5分钟。在添加完20多张表之后，发现业务开始报警。查看业务日志，发现有不少接口超时。

根据经验判断，应该是服务端TCP连接满了，查看监控信息，果然如此，直接使用该数据库表的服务的tcp连接溢出。很显然，应该是数据库操作变慢导致接口响应时间增加，从而导致吞吐量降低，而业务流量保持不变的情况下，大量请求造成堆积，进而导致TCP连接被占满甚至溢出。

同时，即使没有使用该数据库表，使用其他库的服务也出现同样的问题。原因在于两者使用的数据库在同一个实例上，添加索引导致数据库服务器负载增加(实际增加不算多，正常来说应该不影响?)，影响到了其他库。

这时候只能让dba将索引添加操作暂停，等待晚上12点之后流量降低再执行。晚上执行时不再报警。

online ddl

通常情况下，对数据量大的表进行ddl操作时，一般都会选在流量低的时候进行。但是在mysql5.6之后，引入了一些新的特性，支持DDL执行期间DML语句的并行操作，提高了数据库的吞吐量。据mysql官方文档介绍，online ddl操作不会加锁，很快就能完成操作。正是基于这一点考虑，所以才直接在白天加。结果就出现了上面的问题。那么什么是online ddl呢？其结构图如下：
在这里插入图片描述

Online DDL原理

oneline ddl大致分为3个部分：

copy（ALGORITHM=COPY）这部分是offline的，ddl执行时会阻塞dml,中间需要临时表的中转。这也是5.6版本前的DDL执行方法。在innodb中不支持使用inplace的操作都会自动使用copy方式执行，而MyISAM表只能使用copy方式。
inplace（ALGORITHM=INPLACE）所有操作在innodb引擎层完成，不需要经过临时表的中转。除上图两种特殊索引创建外，其他以inplace方式执行的操作都是online的，执行期间其他DML操作可以并行，其中又以是否重建表又分为两个部分rebuild和no-rebuild。

rebuild部分涉及表的重建，在原表路径下创建新的.frm和.ibd文件，消耗的IO会较多。期间（原表可以修改）会申请row log空间记录DDL执行期间的DML操作，这部分操作会在DDL提交阶段应用新的表空间中。no-rebuild部分由于不涉及表的重建，除创建添加索引，会产生部分二级索引的写入操作外，其余操作均只修改元数据项，即只在原表路径下产生.frm文件，不会申请row log，不会消耗过多的IO，速度通常很快。

inplace but offline的几种特殊DDL操作，本身是按inplace方式执行，但是执行期间DML语句却不能并行。

如何区分DDL语句是使用了copy方式还是inplace方式，只需要查看语句执行完成输出结果中的 X rows
affected，如果X为0则是inplace（online）方式，如果不为0则是copy（offline）方式。

copy的整体执行过程如下：

1.锁表，期间DML不可并行执行
2.生成临时表以及临时表文件（.frm .ibd）
3.拷贝原表数据到临时表
4.重命名临时表及文件
5.删除原表及文件
6.提交事务，释放锁

inplace(rebuild)的整体执行过程如下：

准备阶段：

1.对表加元数据共享升级锁，并升级为排他锁（此时DML不能并行）
2.在原表所在的路径下创建.frm和.ibd临时中转文件（no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中）
3.申请row log空间，用于存放DDL执行阶段产生的DML操作（no-rebuild不需要）

执行阶段：

1.释放排他锁，保留元数据共享升级锁（此时DML可以并行）
2.扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中
3.将所有对原表的DML操作记录在日志文件row log中

如果只修改元数据部分（no-rebuild），该阶段只是修改.frm文件，不需要其他操作，也不需要申请row log

提交阶段：

1.升级元数据共享升级锁，产生排他锁锁表（此时DML不能并行）。
2.重做row log中的内容。（no-rebuild不需要）
3.重命名原表文件，将临时文件改名为原表文件名，删除原表文件
4.提交事务，变更完成。

显式online ddl参数

可以在执行online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在DDL语句的最后面，用逗号隔开。

ALGORITHM有如下选项：

INPLACE：直接在原表上面执行DDL的操作。
COPY：使用临时表。这期间需要多出一倍的磁盘空间来支撑这样的操作。执行期间，表不允许DML的操作。
DEFAULT：默认方式，由MySQL自己选择，优先使用INPLACE的方式。

LOCK有如下选项：

SHARE：共享锁，执行DDL的表可以读，但是不可以写。
NONE：没有任何限制，执行DDL的表可读可写。
EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值，由MySQL自动判断，优先使用NONE的方式。

例句如下，参数间使用逗号隔开：

alter table test add col int,ALGORITHM=INPLACE,LOCK=DEFAULT;

执行DDL操作时，显式参数可以不指定，mysql会自动选择合适的方式去执行，优先使用inplace，none的方式，效果与指定ALGORITHM=DEFAULT,LOCK=DEFAULT一样。但是如果显式指定了这两个参数，则必须按照指定的方式来执行，不支持的话则直接报错。

总结分析

从上面的内容来看，online ddl添加索引正常应该是很快的，但并不是完全不会加锁。在对表元数据加互斥锁的时候，会阻塞dml操作。但从整体来看，这个时间应该是极短的，所以官方才有online ddl操作不会加锁的说法。

上面说到在inplace模式的online ddl操作时，会申请一个缓存空间，用于存放在此期间的dml操作。这个缓存大小由参数innodb_online_alter_log_max_size控制，默认为128mb，支持动态修改。如果更新的表比较大，并且在ddl过程中有大量的写操作，就可能遇到空间不足的情况，会抛出相应的错误。

另外，如果ddl操作的目标表上有未结束的事务或者有锁没有释放，那么在加元数据独占锁(mdl)时就会等待前面的锁释放，这个时候的状态为：waiting for table metadata lock。又因为独占锁的优先权限，后面的DML操作都要排队等待。从而导致db操作阻塞。

最终的结论是：如果表数据量较小，或者加索引的表数量较少，online ddl操作是可以接受的，建议显式指定algorithm和lock参数。但如果数据量较大，或者加索引的表比较多，那么就需要充分考虑上面说到的问题，最好在业务流量低的时候执行。