在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...These语句与DML操作不同，比如insert、update、select.The DDL操作都会改变表模式，而DML操作只会影响行。

其次，innodb中有多个众所周知的锁，如行锁、下键lock.But MetaData锁等，很少有人knows.The DDL操作会获得独占的MetaData锁来保证数据的一致性，这将阻止DML operations.For在没有MetaData锁的情况下操作会导致数据异常，可以引用bug#989。

如何在不阻塞的情况下实现DDL

目前，mysql支持在线ddl不需要锁就可以执行DDL操作。在online中没有魔法，它只是创建一个具有相同结构的新表，并在新表上操作DDL语句，然后用旧表重命名新表。

若要将索引添加到没有锁的表中，则可以使用以下语句格式：

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