加索引如何避免锁表？

2019-06-03 补充 此等锁表必须有两个条件： 首先历史原因，线上加索引是用了一个存储过程： 摒弃一切无关要素，我们只看核心语句： 线上用的就是这么一个语句添加索引的。 首先，添加索引为什么会锁表呢？查阅一些资料说，如果当前表正在执行一个慢查询，此时添加索引，就可能会锁表。 我尝试在测试环境重现了一下。 首先，我在b_regbroker_cst_proj这个表执行了一个慢查询事务。 这个查询正常完成要9s左右，够慢了，满足要求。 然后我要做的事情，就是在查询未完成的时间，给b_regbroker_cst_proj这个表添加索引。 好了，来试试，我先开启事务，先不提交。 然后创建索引： 哦耶，so funny。 此时无论这个表执行什么查询，都会卡住了： 查了一下下面这个lock的原因： 有没有更好的加索引解决方式呢？查文档文档mysql自5.6之后变提供了解决方式，运行线上无锁添加索引。 重点说两个参数： 好了，试下有没有用。 哈哈哈，卵用都没有。 我觉得我是理解错了，人家的无锁加索引，只是说加索引的语句不加锁，避免此时有update、insert、delete语句也持锁从而相互形成死锁。但是目前的问题是，有事务持了表锁，创建索引需要等待。等该事务提交，索引就能创建成功。但是在这个等待时间，其它sql都执行不了，这个很坑啊~~ 结论： 总之，alter table的语句是很危险的(其实他的危险其实是未提交事物或者长事务导致的)，在 *** 作之前最好确认对要 *** 作的表没有任何进行中的 *** 作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。 加索引要避免锁表，需要先确定此时没有慢查询事务未提交，如果这个查询卡了30分钟，那么整个表的所有业务都会卡30分钟，这是很变态的。 所以大表加索引还是在没人用的时间加比较安全，要不就是先创建副本，再将表名改掉。

select (*) from tb_name where create_time >xxx

最终得知是因为这个表数据行数已经超过 一千万了，然后create_time字段又没有索引 。

那解决办法肯定是加索引喽。

但是这个表是一直在线上运行，很重要和业务部分。如果给千万级的大表在线加索引 ，肯定会卡死。

然后就搜罗了一大筐解决方案，比如 在线无锁加索引使用

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

后来才发现，这个特性是 Mysql 5.6 以后才支持，然而我们的mysql用的是5.5版本

最后在 《高性能Mysql》一书中看到，可在通过 “影子拷贝”来解决，

就是 先创建一张和源表无关的新表，然后通过重命名和删表 *** 作交换两张表；

当给新表加完索引后，最上面那条查询直接就是0.0002s

场景：在给一张有几万条记录的表添加索引时，进度非常慢，导致其它查询无法进行

处理方式：

使用Navicat的命令行模式，执行以下命令：

show processlist

这时会看到有哪些线程正在执行，也可以查看锁表的线程。你会发现alter table * add key ****那个线程状态是Waiting for table metadata lock，后面有个这个表的所有 *** 作都是这个状态，很明显是这条加索引的语句把表给锁了。

查看线程ID，执行

kill 线程ID

这样被锁住的表就能立即被使用了。

由此得出一个结论，当一张表数据量很大时，不要轻易添加索引，会导致表被锁死！如果非要添加，那么应该先把数据表进行备份，然后进行空表添加索引。

只能通过ALTER TABLE不能create index

参数说明：

---