如何避免在MySQL 5.6表中创建索引锁？

Question

我需要在一个大的InnoDB生产表上创建索引，而且希望在任何情况下都不会锁定表。我正在使用MySQL 5.6 (.38-83.90)。

我尝试过

create index my_index on my_table(col1, col2); 

两个列都不是主键。col1是一个外键。

这完全锁定了表格。其他查询被阻塞，出现了"等待表格元数据锁定"的情况，导致我的网站崩溃。我不得不终止create index查询。

从https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html上可以看到，我认为它不会锁定表格: "...不需要语法更改...在创建或删除索引时，表格仍然可用于读写操作。"

我知道我可以设置LOCK=NONE或LOCK=SHARED，但我不知道是否有必要，如果有必要，我应该使用哪一个。

"您可以指定LOCK=NONE来声明允许并发DML在DDL操作期间。当可能时，MySQL会自动允许并发DML。"

"您可以指定LOCK=SHARED来声明允许在DDL操作期间进行并发查询。当可能时，MySQL会自动允许并发查询。"

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html中的任何限制似乎都不适用于我的情况。

我错过了什么？

- user984003

---

对于那些投票要关闭此问题的人，问如何创建MySQL索引不属于编程范畴吗？在Stackoverflow上有大量关于MySQL的问题。 - user984003

从技术上讲，这是一个DBA问题https://dba.stackexchange.com/ - 不是一个“编程”问题（例如，它不是如何在代码中“编写”SQL查询）。 - barryhunter

Answer 1

我的猜测（仅仅是猜测）是您在CREATE INDEX语句中漏掉了ALGORITHM=INPLACE。

CREATE INDEX my_index ON my_table(col1, col2) ALGORITHM=INPLACE ;
                                              ^^^^^^^^^^^^^^^^^

请注意事务获取和持有元数据锁的情况。

任何引用my_table的事务都会继续在该表上持有元数据锁，直到事务提交或回滚。建议检查SHOW ENGINE INNODB STATUS输出中的TRANSACTIONS部分。 https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

- spencer7593