将INDEX添加到MySQL表时的CPU利用率问题

Question

一个具有数十亿行的表。我使用的是MySQL 5.7当我试图向表添加索引时，它会花费很长时间，而且还会锁定表。结果，许多查询在队列中等待，服务器负载增加。

我不明白向表添加索引是创建表锁，也就是元数据锁。你能解释一下为什么会发生这种情况，并提供一个解决方案吗。

创建表

CREATE TABLE `user_log` (
`id` bigint(14) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`latitude` decimal(10,6) NOT NULL,
`longitude` decimal(10,6) NOT NULL,
`created_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=20000000000 DEFAULT CHARSET=latin1

ALTER命令

ALTER TABLE `user_log` ADD INDEX (`user_id`, `created_date`);

Answer 1

为什么mysql需要锁表
首先，mysql中有很多DDL操作，比如alter table、drop table。。。这些语句不同于DML操作，如insert、update、select。DDL操作将更改表模式，而DML操作仅影响行
其次，innodb中有多个众所周知的锁，例如行锁、下一个键锁。但MetaData锁很少有人知道。DDL操作将获取独占元数据锁以保证数据一致性，这将阻止DML操作。对于DDL操作在没有MetaData锁的情况下会导致数据异常的情况，您可以参考bug#989。

如何在没有块的情况下执行DDL

如今，mysql支持在线ddl进行无锁DDL操作。在线ddl中并没有魔术，它只是创建一个结构相同的新表，并在新表上操作DDL语句，然后用旧表重命名新表。

要在UPDATE/INSERT上不带锁的情况下向表添加索引，可以使用以下语句格式：

ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE;