背景

最近业务日志发现了几条慢sql，最长的执行时间超过2min,于是希望对这部分查询进行一个优化。

慢sql优化首先需要定位慢的原因

• 数据库性能差
• 数据量大，没有索引
• 数据量大，没用上索引
• 需要用到临时空间，排序等

原因可能有很多，需要具体问题具体分析。

本次业务中出现的问题主要就是查询的数据没有索引，导致查询走了全表扫描(kw级别的数据量)。

找到了问题，那么解决的方法就是加索引了。加索引属于DDL(Data Definition Language)操作，对于这类操作，我们需要评估影响，尽可能减小对业务的影响。

下面是几种DDL方式的介绍和比较

Offline DDL -- Copy

首先介绍一下最传统 offline DDL，在Mysql 5.6版本之前，mysql 的DDL都是采用的 Copy 的方式。

在高版本如果想使用这种方式，可以指定DDL算法，默认情况下，数据库应该就是选择的这种方式

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=COPY;

COPY 方式的执行流程如下

• 对原数据添加MDL 读锁(s)，读取表结构 -- 这个阶段很快
• 升级MDL成排他锁(x) -- 不允许进行其他DDL，DML
• 创建一张一样的新表:table_new
• 修改新表的表结构
• copy 原表的数据到新表
• 新表rename成旧表
• 删除旧表
• 释放所有锁

可以看到，COPY 操作执行的过程中是加锁的，阻塞DML，是会阻塞业务的；

同时，如果DDL执行的时间久，会造成比较大的主从延迟，从库再等待主库执行ddl，从库执行ddl时都不能更新信息。

Online DDL

从Mysql 5.6开始，官方支持了online DDL的操作

ALTER TABLE tbl_name ADD Index Index_name (column), ALGORITHM=INPLACE,LOCK=NONE;

Online DDL的全过程阻塞DDL，但不阻塞DML，执行期间不影响业务。

Online DDL需要区分两种情况:需要重建表和不需要重建表 对于添加索引的情况，不需要rebuild table，只需要重新建立一颗辅助索引的BTree，

像增加列这种操作则需要rebuild table 参考链接

ONline DDL的流程如下

prepare阶段

1、和COPY一样，会先获取MDL读锁(S)，读取表结构

2、新建新的临时frm文件(frm文件是一个包含表定义的文件。每当创建一个新表时，MySQL会在数据库目录下为该表创建一个.FRM文件。该文件包含了该表的结构定义，包括表的列名、数据类型、索引、主键等信息。)

3、升级成排他锁(X)不允许DML,根据alter判断是否需要rebuild table，做一些操作准备

4、如果需要rebuild table，还需要

• 分配row_log对象记录增量（no-rebuild不需要）
• 生成新的临时ibd文件（no-rebuild不需要）

执行阶段

1、降级成MDL读锁(S),允许DML,这里可以通过Lock=None手动设置表锁的形式。

• LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。
• EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）
• SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景
• DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。

2、扫描旧表的每一条数据，构造新索引

3、如果是rebuild类型,重放raw_log的日志，

commit 阶段

1、升级成MDL排他锁(X),不允许DML

2、重放完raw_log的最后一部分数据（rebuild 需要，以免漏数据）

3、完成DDL

4、如果是rebuild 类型，需要rename 新表成旧表(修改ibd文件名)

可以看到，Online DDL的过程中，prepare阶段和commit阶段是阻塞DML的，但是时间很短，这部分阻塞为了防止漏数据。

执行阶段是运行DML了，你的操作会保存在raw_log，完成DDL后进行重放。

Online DDL可以允许DDL，不阻塞业务运行。但也有缺点，这个操作是会带来主从延迟的，在DDL完成之前，从库是读不到后面这些DML的，如果DDL时间过长，会造成主从延迟，这是使用Online DDL必须考虑的点。

在本地创建了1kw和2kw的数据量的表，对其进行online DDL，耗时大概：

• 1kw : 25s
• 2kw : 52s

使用时需要考虑这个延时能否接受

pt-osc

pt-osc(pt-online-schema-change)是perconal 推出一个mysql管理小工具，他的特点同样是特点是在修改过程中不会造成读写阻塞。

但他使用的前提是必须带有主键或者唯一索引

它执行流程如下:

1. show create table tbosc
2. create table _tbosc_new -- 创建一个一样的新表
3. alter table _tbosc_new -- 对新表进行操作，速度很快(空表)
4. 在旧表创建删除触发器 pt_osc_dbddl_tbosc_del 
5. 在旧表创建创建更新触发器 pt_osc_dbddl_tbosc_upd
6. 在旧表创建创建插入触发器 pt_osc_dbddl_tbosc_ins
7. 按块拷贝旧数据到新表，拷贝过程对数据行持有S锁，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表。
8. analyze 新表
9. rename 表名，RENAME TABLE dbddl.tbosc TO dbddl._tbosc_old, dbddl._tbosc_new TO dbddl.tbosc
10. 删除旧表
11. 删除新表上的删除、更新、插入 触发器

对于有从库的表来说，实际上这个旧表的ddl操作是不写入bin log的，他写入的时工具做的这些建新表，建触发器，拷贝数据的操作，所以没有阻塞的语句，不会造成太大主从延迟。

Final

可以看到，对于业务量不大可以停服的服务来说，用offline ddl就够了，对主从延迟可以接受的服务，可以是使用online ddl。而对于时间很敏感的业务，就要考虑pt-osc了。

最后，对于慢sql的优化，还是得根据业务逻辑进行分析，尽可能使用能走索引的查询，尽可能减少索引，排序，临时空间。 然后才是添加索引，添加索引就需要考虑上述几种添加方式的优劣。