加索引如何避免锁表？

- 日理万妓 2022-11-11 14:56 170阅读 0赞

1，加索引的时候有慢sql正在执行就会锁表--阻塞到mansql事物提交，或提前kill这个sql进程

2，alter tabe --表级锁

3，update ---行级锁

此等锁表必须有两个条件：

1.  未提交事务；  
    2.有需要表锁的操作，比如所有alter table操作。如果是update，insert这种行锁的操作，不会有问题  
    ====================================================================================  
    鉴于有经常有同事在生产环境添加索引导致死锁的情况，我们先来了解一下原因，然后再看看有没有解决方案。

首先历史原因，线上加索引是用了一个存储过程：

CREATE DEFINER=`myscrm_cn`@`%` PROCEDURE `CreateIndexIfNotExistsWithColumns`(IN tableName varchar(200), IN columnName VARCHAR(200))
    BEGIN
          DECLARE _tableCount   INT;
          DECLARE _indexCount   INT;
          SET _tableCount =
                 (SELECT COUNT(1)
                    FROM INFORMATION_SCHEMA.TABLES
                   WHERE TABLE_SCHEMA = (SELECT schema()) AND TABLE_NAME = tableName);
          SET _indexCount =
                 (SELECT count(1)
                    FROM information_schema.statistics
                    WHERE     TABLE_SCHEMA = (SELECT schema())
                         AND TABLE_NAME = tableName
                         AND INDEX_NAME = concat('IX_', columnName));
          IF _tableCount = 1 AND _indexCount = 0
          THEN
             SET @_sqlText =
                    CONCAT(' CREATE INDEX `IX_',columnName,'` ON `',tableName,'`(',columnName,');');
              PREPARE stmt1 FROM @_sqlText;
              EXECUTE stmt1;
              DEALLOCATE PREPARE stmt1;
          END IF;
    end

摒弃一切无关要素，我们只看核心语句：

CONCAT(' CREATE INDEX `IX_',columnName,'` ON `',tableName,'`(',columnName,');');

线上用的就是这么一个语句添加索引的。

首先，添加索引为什么会锁表呢？查阅一些资料说，如果当前表正在执行一个慢查询，此时添加索引，就可能会锁表。

我尝试在测试环境重现了一下。

首先，我在b\_regbroker\_cst\_proj这个表执行了一个慢查询事务。

BEGIN;
    SELECT
        *
    FROM
        `b_regbroker_cst_proj` a
    INNER JOIN b_regbroker_brokerage b ON a.b_regbroker_cst_projId = b.cst_proj_id
    LIMIT 5000;
    # COMMIT;

这个查询正常完成要9s左右，够慢了，满足要求。

然后我要做的事情，就是在查询未完成的时间，给b\_regbroker\_cst\_proj这个表添加索引。

CREATE INDEX `IX_test` ON `b_regbroker_cst_proj` (`protect_node`);

好了，来试试，我先开启事务，先不提交。

image.png

![0db8068c4eeef9c1862e74cda695793f.png][]

然后创建索引：

image.png

![fcf54681bb1d9ea90e459bd0e5b16872.png][]  
好了，果然卡了：

image.png

![bcb484ed630817fb9e79397a4096c9c2.png][]

哦耶，so funny。  
此时无论这个表执行什么查询，都会卡住了：

image.png

![3fbb560de064ad375e696a1060e2a045.png][]

image.png

![602eacac70ee273f901001450406b4c6.png][]  
此时我把事务提交，有没有用呢？

image.png

![ea34eb5f7a9691ee3b08cc91561b0139.png][]

有用的：

image.png

![44a6965e0d09a8e7b8d589f6872fc916.png][]

查了一下下面这个lock的原因：

Waiting for table metadata lock

*  1.  长事物运行，阻塞DDL，继而阻塞所有同表的后续操作  
        通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。
 *  1.  未提交事物，阻塞DDL，继而阻塞所有同表的后续操作  
        通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information\_schema.innodb\_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。
 *  1.  通过show processlist看不到TableA上有任何操作，在information\_schema.innodb\_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance\_schema.events\_statements\_current表中可以查到失败的语句。

有没有更好的加索引解决方式呢？查文档  
[文档][Link 1]

mysql自5.6之后变提供了解决方式，运行线上无锁添加索引。

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

重点说两个参数：

*  ALGORITHM

ALGORITHM=INPLACE
    更优秀的解决方案，在当前表加索引，步骤：
    1.创建索引(二级索引)数据字典
    2.加共享表锁，禁止DML，允许查询
    3.读取聚簇索引，构造新的索引项，排序并插
    入新索引
    4.等待打开当前表的所有只读事务提交
    5.创建索引结束
    
    ALGORITHM=COPY
    通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
    1.新建带索引（主键索引）的临时表
    2.锁原表，禁止DML，允许查询
    3.将原表数据拷贝到临时表
    4.禁止读写,进行rename，升级字典锁
    5.完成创建索引操作

*  LOCK

LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
    LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操
    作不支持对表的继续写入，则DDL操作失败，对表修改无效
    LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
    LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

好了，试下有没有用。

image.png

![24f206bc55648d4f4fca132521214ca9.png][]

哈哈哈，卵用都没有。

我觉得我是理解错了，人家的无锁加索引，只是说加索引的语句不加锁，避免此时有update、insert、delete语句也持锁从而相互形成死锁。但是目前的问题是，有事务持了表锁，创建索引需要等待。等该事务提交，索引就能创建成功。但是在这个等待时间，其它sql都执行不了，这个很坑啊~~

结论：  
总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock\_wait\_timeout设置好超时时间，避免长时间的metedata锁等待。

加索引要避免锁表，需要先确定此时没有慢查询事务未提交，如果这个查询卡了30分钟，那么整个表的所有业务都会卡30分钟，这是很变态的。

所以大表加索引还是在没人用的时间加比较安全，要不就是先创建副本，再将表名改掉。

作者：10xjzheng  
链接：https://www.jianshu.com/p/4abc30dfad01  
来源：简书  
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

[0db8068c4eeef9c1862e74cda695793f.png]: /images/20221022/53d30a2b5e644d7097b72adf4cca6b47.png
[fcf54681bb1d9ea90e459bd0e5b16872.png]: /images/20221022/46a38d3e3abd469194ee042c1b40e966.png
[bcb484ed630817fb9e79397a4096c9c2.png]: /images/20221022/9ddbf0d560c34194b8151f460fe07985.png
[3fbb560de064ad375e696a1060e2a045.png]: /images/20221022/05ae0026cb13447ea8f797297b5c142f.png
[602eacac70ee273f901001450406b4c6.png]: /images/20221022/0522dc4d8a6b42c3848975cb41ca3e9f.png
[ea34eb5f7a9691ee3b08cc91561b0139.png]: /images/20221022/aa1ea58586e8400ab711790d0568ccf0.png
[44a6965e0d09a8e7b8d589f6872fc916.png]: https://img-blog.csdnimg.cn/img_convert/44a6965e0d09a8e7b8d589f6872fc916.png
[Link 1]: https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Finnodb-online-ddl.html
[24f206bc55648d4f4fca132521214ca9.png]: https://img-blog.csdnimg.cn/img_convert/24f206bc55648d4f4fca132521214ca9.png