代码上线一段时间后通过 APM 工具发现慢查询,有一些场景需要我们对 MySQL 表加索引来优化,利用索引本身会加速查询,或者利用索引覆盖、索引下推等方式来优化 SQL 的执行效率。

通常这个时候我们会小心翼翼,毕竟在生产环境上加索引,如果导致锁表,对业务的影响巨大,甚至造成多个服务直接 STOP THE WORLD 。
MySQL_logo

在查询一些资料后,发现 MySQL 在 5.6 版本已经优化了这个问题。

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

​ MySQL 5.6 innodb-online-ddl :https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation in place, as permitted, with as little locking as possible.

You can control aspects of a DDL operation using the ALGORITHM and LOCK clauses of the ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example:

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

针对这个问题,咨询了 DBA 同事,答复是 只要不改主键,就不会锁表