老大，我就想加个索引，怎么就这么难？

小灰灰 2023-03-02 10:54 5阅读 0赞

> ❝
> 
> 领导让我`SQL`优化，我直接把服务干挂了...
> 
> ❞

## 前言 ##

`MySQL`大表加字段或者加索引，是有一定风险的。

大公司一般有`DBA`，会帮助开发解决这个痛点，可是`DBA`是怎么做的呢？

小公司没有`DBA`，作为开发我们的责任就更大了。那么我们怎么才能安全的加个索引呢？

今天，我们通过模拟案例以及原理分析，去弄清楚`MySQL`中`DDL`的风险，以及如何避免事故发生。

## 准备 ##

### 软件以及项目 ###

1.  安装本地版本MySQL。
2.  一个简单的增删改查项目。
3.  使用JMeter进行并发请求测试。

### 创建表 ###

# 如果存在user表则删除
    DROP TABLE  IF EXISTS user;
    
    # 创建user表
    CREATE TABLE `user` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      `name` varchar(10) DEFAULT NULL COMMENT '姓名',
      `age` int(2) DEFAULT NULL COMMENT '年龄',
      `address` varchar(30) DEFAULT NULL COMMENT '地址',
      `description` varchar(100) DEFAULT NULL COMMENT '描述',
      `test_id` bigint DEFAULT NULL COMMENT '测试 id',
      `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
      `modify_time` timestamp NULL DEFAULT NULL COMMENT '修改时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='mysql ddl测试表';

### 创建存储过程 ###

# 如果存在test存储过程则删除
    DROP PROCEDURE IF EXISTS `test`;
    
    # 创建无参存储过程，名称为test
    CREATE PROCEDURE test()
    
    BEGIN
        # 声明变量
        DECLARE i INT;
        # 变量赋值
        SET i = 0;
        # 结束循环的条件: 当i等于100万时跳出while循环
        WHILE i < 1000000 DO
        # 往t_test表添加数据
        INSERT INTO `test`.user (`name`, `age`, `address`, 
                                 `description`, `test_id`, `create_time`, `modify_time`)
        VALUES ('iisheng', 26, '北京', '如逆水行舟', LAST_INSERT_ID() + 1, 
                '2020-05-17 16:01:44', '2020-05-17 16:01:51');
    
        # 循环一次, i加1
        SET i = i + 1;
        # 结束while循环
        END WHILE;
    
    END

> ❝
> 
> 下面的创建存储过程语句，是在`IDE`内选择代码块执行的，如果在`Terminal`中执行，需要使用`DELIMITER`关键字，更改语句结束标志。
> 
> ❞

### 调用存储过程，生成百万数据 ###

CALL test();

### 开启慢SQL日志 ###

# 查看MySQL是否开启慢日志记录
    SHOW VARIABLES LIKE 'slow_query_log';
    
    # 开启慢SQL日志记录
    SET GLOBAL slow_query_log = 'ON';
    
    # 查看慢SQL日志位置
    SHOW VARIABLES LIKE 'slow_query_log_file';
    
    # 查看执行多久的SQL才算慢SQL
    SHOW VARIABLES LIKE 'long_query_time';
    
    # 设置慢SQL执行时间 只有新session才生效
    SET GLOBAL long_query_time = 1;

> ❝
> 
> 通常情况下这些会在MySQL的配置文件中配置，启动时生效。
> 
> ❞

### 几个有用的SQL语句 ###

# 展示哪些线程正在运行
    SHOW PROCESSLIST;
    
    # 查看正在执行的事务
    SELECT * FROM information_schema.INNODB_TRX;
    
    # 查看正在锁的事务
    SELECT * FROM information_schema.INNODB_LOCKS;
    
    # 查看正在等待锁的事务
    SELECT * FROM information_schema.INNODB_LOCK_WAITS;
    
    # 显示innodb存储引擎状态的大量信息，包含死锁日志
    SHOW ENGINE INNODB STATUS ;
    
    # 展示数据库最大连接数的配置
    SHOW VARIABLES LIKE 'max_connections';
    
    # 查看存在哪些触发器
    SELECT * FROM information_schema.TRIGGERS;
    
    # 查看MySQL版本
    SELECT VERSION();

> ❝
> 
> 后面我们会主要用前两条。
> 
> ❞

## 事故现场 ##

### 说明 ###

1.  我创建的`user`表除了主键是没有其他索引的。
2.  测试的`user`表数据量为一百万。
3.  测试`MySQL`版本为`5.7.28`。
4.  测试项目的逻辑：随机get()、list()、update()、create()，每个操作都开启事务，并且休眠500毫秒。

### 步骤 ###

> ❝
> 
> 运行测试项目
> 
> ❞

![20200729100753422.png][] 项目启动图

这里我们可以看到，项目已经正常启动了。

> ❝
> 
> `postman`调用一下接口
> 
> ❞

![2020072910082396.png][] 接口请求图

这里我们随便测试一个接口，请求时间2秒左右。

> ❝
> 
> 执行JMeter的Test Plan，观察项目日志
> 
> ❞

![20200729100851297.png][] JMeter配置图

这里我们创建了四个线程组，每个线程组调用一个我们的接口。模拟10个人循环1000次的访问。

![202007291009199.png][] 正常项目日志图

这里我们看到该请求频率下，日志无异常。

> ❝
> 
> 慢SQL日志
> 
> ❞

![20200729100951707.png][] 慢SQL日志图

这里我们看到，百万级的SQL，如果没加索引SQL执行时间还是比较长的，有的已经达到了2s。另外，关注公众号Java技术栈可以获取更多我整理的MySQL系列优化教程。

> ❝
> 
> 加个索引，再观察项目日志
> 
> ❞

![20200729101020822.png][] 加索引过程日志图

这里我们看到，项目已经开始报错了，大量的`Connection is not available, request timed out after 30001ms`。

> ❝
> 
> `SHOW PROCESSLIST`一下
> 
> ❞

![20200729101048179.png][] PROCESSLIST图

这里我们看到，有大量的`Waiting for table metadata lock`。

> ❝
> 
> `postman`再次调用一下接口
> 
> ❞

![20200729101116440.png][] 请求接口报错图

这个时候，调用接口已经报错了，响应时间也比较久。此时，服务对用户来说，已经基本不可用了。

## 为什么会这样？ ##

> ❝
> 
> 我就想加个索引，怎么就这么难？
> 
> ❞

看吧，就因为我加了个索引，服务就挂了，我没加之前还是好好的。遇到问题，我们要冷静，不是我们的锅坚决不能背，真的是我们的问题，下次一定要记得改正。那么，此刻的服务为什么就不可用了呢？

首先我们要知道，在`InnoDB`事务中，锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是**两阶段锁协议**。

然后，在`MySQL5.5`版本中引入了`MDL(Metadata Lock)`，当对一个表做增删改查操作的时候，加`MDL`读锁；当要对表做结构变更操作的时候，加`MDL`写锁。

我们可以简单的尝试一下下面的情况。

![20200729101149717.png][] DDL锁等待图

`Session A`开启一个事务，执行了一个简单的查询语句。此时，`Session B`，执行另一个查询语句，可以成功。接着，`Session C`执行了一个`DDL`操作，加了个字段，因为`Session A`的事务没有提交，而且`Session A`持有`MDL`读锁，`Session C`获取不到`MDL`写锁，所以`Session C`堵塞等待`MDL`写锁。又由于`MDL`写锁获取优先级高于`MDL`读锁，因此`Session D`这个时候也获取不到`MDL`读锁，等待`Session C`获取到`MDL`写锁之后它才能获取到`MDL`读锁。

我们发现，DDL操作之前如果存在长事务，一直不提交，DDL操作就会一直被堵塞，还会间接的影响后面其他的查询，导致所有的查询都被堵塞。

这也就是为什么我们把服务干挂的原因了。

## 目前主流解决方案 ##

针对上面出现的情况，我们怎么解决呢？

### MySQL5.6的`Online DDL` ###

`MySQL`从`5.6`开始，支持`Online DDL`。类似于这种的语句`ALTER TABLE user ADD INDEX idx_test_id (test_id), ALGORITHM=INPLACE, LOCK=NONE`在普通的`ALTER TABLE`或者`CREATE INDEX`语句后面添加`ALGORITHM`参数和`LOCK`参数。

> ❝
> 
> 实际上，`ALTERT TABLE`语句如果不加`ALGORITHM`参数，默认就会选择`ALGORITHM=INPLACE`的形式，如果执行的语句支持`INPLACE`，否则，会使用`ALGORITHM=COPY`。
> 
> ❞

以前写`SQL`只会`ALTER TABLE`不知道后面还可以加`ALGORITHM`参数，后来知道了`Online DDL`，知道了可以加`ALGORITHM=INPLACE`，结果两种写法有的时候是一样的...

![20200729101230830.png][] MySQL官网截图

这里顺便提一句，学习的途径有很多，但是官网，的确可以多看看。

### 使用`pt-online-schema-change` ###

> ❝
> 
> 简单说一下怎么安装这个东西
> 
> ❞

首先官网下载，然后校验以及安装，执行下面命令

perl Makefile.PL
    make
    make install

然后使用`CPAN`安装相关依赖（适用`Unix`），`CentOS`下直接`yum`更简单

perl -MCPAN -e shell
    cpan> install DBI
    cpan> install DBD::mysql

> ❝
> 
> 我自己Mac安装没啥问题，公司Mac安装失败了，然后升级了一下Perl版本就可以了。
> 
> ❞

### 语法 ###

pt-online-schema-change --charset=utf8 --no-check-replication-filters --no-version-check --user=user --password=pass --host=host_addr  P=3306,D=database,t=table --alter "ADD INDEX idx_name(field_name)" --execute

### 我的脚本添加索引 ###

pt-online-schema-change --charset=utf8 --no-check-replication-filters --no-version-check --user=root --password=mGy6GAzdawFPTJ7R --host=127.0.0.1  P=3306,D=test,t=user --alter "add INDEX idx_test_id(test_id)" --execute

## 使用`pt-osc`测试 ##

![2020072910130842.png][] pt-osc执行图

这里我们看到，`pt-osc`创建触发器的时候卡在那了。实际上这里也是在等待锁。

最终成功了，但是整个过程时间比较久。过程中我们也发现了一些死锁的日志。

![20200729101337172.png][] pt-osc死锁日志

其实，这个跟我的代码有一定的关系，我的测试代码随机数生成的范围是`[0, 20000]`，然后我根据生成的随机数，去查询数据库，锁的冲突会比较多。把范围修改为`[0, 1000000]`会好很多。

## 再看`Online DDL` ##

因为刚才我们发现了，自己代码写的有一些问题，所以我们刚才的结论也有一些影响。我们把随机数的范围改到100万，重新测试一遍。

![20200729101409825.png][] Online DDL 成功

这次`Online DDL`也成功了。但是也是有一些连接超时的日志。之前的测试如果一直执行下去，也会成功，只不过堵塞时间太长，对用户影响太大，我就停止算执行失败了。

> ❝
> 
> 实际效果跟机器性能也是有一些关系的，这里的关键点在于拿`MDL`写锁的等待时间，这个时间稍微久一些就会对用户造成很大的影响。
> 
> ❞

## `pt-osc`执行过程 ##

1.  创建一个和原表表结构一样的临时表(`_tablename_new`)，执行`alter`修改临时表表结构。
2.  在原表上创建3个与`insert` `delete` `update`对应的触发器，用于`copy`数据的过程中，在原表的更新操作，更新到新表。
3.  从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。
4.  `rename`原数据表为`old`表，把新表`rename`为原表名，并将`old`表删除。
5.  删除触发器。

这里面创建、删除触发器和`rename`表的时候都会尝试获取`DML`写锁，如果获取不到会等待。就是我们看到的`Waiting for table metadata lock`。

所以，这些时间段如果长时间获取不到锁，就会一直堵塞，还是会出现问题的。

## `Online DDL`执行过程 ##

1.  拿`MDL`写锁
2.  降级成`MDL`读锁
3.  真正做`DDL`
4.  升级成`MDL`写锁
5.  释放`MDL`锁

`1、4`如果没有锁冲突，执行时间非常短。第3步占用了`DDL`绝大部分时间，这期间这个表可以正常读写数据，因此称为**online**。

但是，如果拿锁的时候没拿到，或者升级`MDL`写锁不能成功，就会等待，我们又会看到`Waiting for table metadata lock`，然后就接着的一系列问题了。

## 总结 ##

加个索引，说难也难，说不难也不难。如果数据量大，又存在长事务，加索引的过程又有用户访问，`Online DDL`和`pt-osc`都不能保证对业务没有影响。但是如果我们`SQL`的执行时间比较短，或者我们加索引的时候，对应的业务没有多少请求。那么我们就可以很快的加完索引。

加字段也是类似的过程，但是如果我们能保证没有慢`SQL`，那么就不会存在长事务，那么执行时间就会很快，对用户就可以做到几乎没有影响。至于选择`Online DDL`还是`pt-osc`就要看他们的一些限制以及自己的场景需求了。感兴趣的同学，自己尝试一下。

## 最后想说 ##

当万丈高楼崩塌的时候，超人也不能将它复原。我们应该做的，是有一个好的规范，好的认知，好的监控，在问题没有出现的时候，就将问题扼杀在摇篮中。而不是让问题，日渐壮大，大到覆水难收...

参考文献：  
\[1\]:《MySQL实战45讲》  
\[2\]: https://dev.mysql.com/doc/refman/5.7/en/  
\[3\]: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

[20200729100753422.png]: /images/20230208/b62daac9091d43e29a0aa6cef1d527e2.png
[2020072910082396.png]: /images/20230208/e00a9f91d4024c77ba4fc7283a3046b3.png
[20200729100851297.png]: /images/20230208/efaaa79120d64f60a2691e4b0cce7214.png
[202007291009199.png]: /images/20230208/35c52e730b394d18b18bfd1386cb0ae5.png
[20200729100951707.png]: /images/20230208/a2e484beba7447cbaaf236b8a036fdc2.png
[20200729101020822.png]: /images/20230208/5b7e295e1da64db19e2a4c1b4d4e5c71.png
[20200729101048179.png]: /images/20230208/96e69d8379bc4c7b997884d847d50673.png
[20200729101116440.png]: /images/20230208/3be8dd6b88bf4a30a096f4b8a703da91.png
[20200729101149717.png]: /images/20230208/49a5dd2fad124df08134a08fac7d4af1.png
[20200729101230830.png]: /images/20230208/a08dfb1dda44415f8c5640c3e3ede951.png
[2020072910130842.png]: /images/20230208/4909fbc1a8fb42fc97aea3f27ab24b52.png
[20200729101337172.png]: /images/20230208/0b3542e5eda64e0eabcd6d7221fd75ba.png
[20200729101409825.png]: /images/20230208/1d2c3c11cf71434f91861e513c5e8601.png