布景

最近事务日志发现了几条慢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 tabletbosc
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的优化，仍是得依据事务逻辑进行剖析，尽可能运用能走索引的查询，尽可能减少索引，排序，暂时空间。 然后才是增加索引，增加索引就需求考虑上述几种增加方式的好坏。