【原】进行一个sql优化需要做哪些准备？-Metadata Lock和online DDL

优化的必要性和急迫性分析

真的有必要优化吗？

在《容易引起雪崩的两个处理》里，我提到一个慢查询问题。因为一个慢查询造成了联调环境的数据库挂掉，我们进行了点线面的梳理，排查了所有数据库相关的隐患，研究了优化方案。

在研究优化方案过程中，重点讨论的是哪些该做、哪些不做。因为在进行一个已经立项的新项目时，自然是在工期允许的范围内，尽量采用高效的方法；但是一旦已经上线到生产，再进行优化需要考虑风险的代价和收益是否成正比。

举个具体的例子，做优化方案时，我们整理了所有的数据库表、DML语句。密密麻麻的Excel啊，辛苦了认真细致的开发小哥哥。

但是最终在所有未加索引和索引不合理的相关表中，真正要优化的范围我们限定到了存在慢查询问题的表。而且，有的表可以通过数据定时清理解决的，也排除在了优化索引范围外。

所以，最后经过好几天的梳理和讨论，近百张(分表算1张)表中，我们定下来需要优化的表有两张。

那这两张表的加索引的最坏影响是什么？不加索引的最坏影响是什么？

首先一个经验：后加的索引没有十足的把握不要加唯一索引，否则数据会被合并，很多数据会消失！普通索引最坏的影响是锁表？锁表不可怕，可怕的是把表写坏了，不能用了。这时候就需要DBA操作前数据表备份，出现问题立即恢复。恢复时间段内的业务影响要评估清楚。

不加索引的最坏影响呢。慢查询会把数据库搞挂，一个数据库放多个团队的数据库还可能把多个团队的数据库搞挂！所以加索引要比不加索引更能确保生产的稳定。

真的有必要现在优化吗？

这两张当然最好合并进行一次加索引的变更。因为首先这两张表的读写不是很频繁，其实的风险不是非常高。这里要注意，敬畏生产。对每次生产都要谨慎谨慎再谨慎。

只是没有明确的等级，我才说不是非常高。意思是相比较多次变更可能引起的人工出错率增加，权衡之后合并投产风险更低。

方案定好了之后，领导直接给开发小哥哥说让他在一个关键期之前尽快投产。我知道之后跟开发小哥哥说你别着急，我找领导说一下，这件事关键期之后才做。

领导说要尽快做的依据我完全能理解：“联调环境都被打挂了，风险很高啊。”我找到领导，告诉他出现问题的数据表在生产环境，数据量只有联调环境的1/20。而且联调环境的数据库机器配置比生产环境低不止一个等级。注意，这些原因是要说的，但是最重要的是要有数据支撑，所以我还告诉领导这个慢查询在生产环境的时间耗时。领导听了我的汇报评估确实可以放到关键期之后做。

这里还想补充一句，作为一个小团队的直接负责人，在风险评估上起的作用非常关键。开发小哥哥本身由于经验等方面的原因，需要别人把关。上级领导没有时间把事情了解的那么透彻，不然自己的价值在哪里。领导依赖自己的汇报，所以自己的决策非常重要。这里方法论没有，就是要多想，翻来覆去的想。

验证

涉及的数据库知识

AC-NL-RO事务

先给大家普及一个认知。请大家想一个问题：咱们普通的select查询语句会用到事务吗？

事务包括两类，显式事务和AC-NL-RO（auto-commit non-locking read-only）事务。

• 显式事务包括两类

• 关闭AutoCommit下的操作

• 以begin或start transaction开始的操作

• AC-NL-RO可理解为AutoCommit开启下的select操作

这问题的答案就很清楚了：普通的select查询语句也使用了AC-NL-RO事务。

Metadata Lock

Metadata Lock源起于一个bug，MySQL官方文档链接：http://bugs./bug.php?id=989

这个bug大致意思是说：当一个会话在主库执行DML操作还没提交时，另一个会话对同一个对象执行了DDL操作如drop table，而由于MySQL的binlog是基于事务提交的先后顺序进行记录的，因此在从库上应用时，就出现了先drop table，然后再向table中insert的情况，导致从库应用出错。

因此，MySQL在5.5.3版本后引入了Metadata lock，只有在事务结束后才会释放Metadata lock，因此在事务提交或回滚前，是无法进行DDL操作的。

online DDL

online DDL是在mysql5.6版本后加入的特性，用于支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量。

常用的有三种类型：

• copy（ALGORITHM=COPY）这部分是offline的，在DDL执行期间其他DML不能并行，也是5.6版本前的DDL执行方法。其间生成临时表（server层的操作支持所有引擎），用于写入原表修改过的数据，同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中不支持使用inplace的操作都会自动使用copy方式执行，而MyISAM表只能使用copy方式。

• inplace（ALGORITHM=INPLACE）所有操作在innodb引擎层完成，不需要经过临时表的中转。除上图两种特殊索引创建外，其他以inplace方式执行的操作都是online的，执行期间其他DML操作可以并行，其中又以是否重建表又分为两个部分rebuild和no-rebuild。
  

• INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。这个新特性是8.0.12引入的。

现在主流的mysql版本是5.6或者5.7。这两个版本如何区分DDL语句是使用了copy方式还是inplace方式呢？

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

rebuild&no-rebuild

online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。

Prepare阶段：

1. 创建新的临时frm文件

2. 持有EXCLUSIVE-MDL锁，禁止读写

3. 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)

4. 更新数据字典的内存对象

5. 分配row_log对象记录增量

6. 生成新的临时ibd文件

ddl执行阶段：

1. 降级EXCLUSIVE-MDL锁，允许读写

2. 扫描old_table的聚集索引每一条记录rec

3. 遍历新表的聚集索引和二级索引，逐一处理

4. 根据rec构造对应的索引项

5. 将构造索引项插入sort_buffer块

6. 将sort_buffer块插入新的索引

7. 处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit阶段

1. 升级到EXCLUSIVE-MDL锁，禁止读写

2. 重做最后row_log中最后一部分增量

3. 更新innodb的数据字典表

4. 提交事务(刷事务的redo日志)

5. 修改统计信息

6. rename临时idb文件，frm文件

7. 变更完成

建索引属于哪种模式

验证内容

• 验证加索引前后数据表中的数据没有任何变化。

• 验证各种索引的组合效果验证：单独索引、联合索引、联合索引谁在前谁在后。

• 验证加索引过程的耗时和可能的影响。上面讲的数据库知识其实就是为了说明这个问题：创建索引操作需要rebuild，耗时长。并且select操作都可能会影响这个操作，增加耗时，验证时这个因素一定要考虑。

评估影响

操作本身的影响

• DML（数据操作语言）影响，在表上添加s索引会直接影响写操作性能（因为添加记录的同时还有创建相应记录的索引，这也是要耗资源的。）。

• DDL（数据定义语言）影响，随着表大小的不断增加，对性能的影响也会不断增加。比如：ALTER语句会耗费更多的时间。

• 磁盘空间的影响，往往在添完一个索引后表占用的空间大小会成倍的增加。

对相关方的影响

评估影响的业务，通知影响方，并确定需要避开的时间段。避开的原因请详细阅读上面提到的数据库知识。

总结

优化有风险，立项需谨慎。