Блог

Таблица с миллиардами строк. Я использую 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 TABLE `user_log` ADD INDEX (`user_id`, `created_date`);
  

Зачем mysql нужна таблица блокировки
Во-первых, в mysql существует множество DDL операций, таких как alter table , drop table …Эти инструкции отличались от DML операций, таких как insert , update , select . DDL Операции изменят схему таблицы, в то время как DML операции влияют только на строки.
Во-вторых, в innodb есть несколько хорошо известных блокировок, таких как блокировка строки, блокировка следующего ключа.Но о блокировке метаданных мало кто знает. DDL Операции получат эксклюзивную блокировку метаданных, чтобы гарантировать согласованность данных, которая заблокирует DML операции.В какой ситуации DDL операции приведут к исключению данных без блокировки метаданных, вы можете сослаться на ошибку # 989.

Как использовать DDL без блокировки

В настоящее время mysql поддерживает онлайн ddl для выполнения DDL операций без блокировки. В онлайн ddl нет волшебства, которое просто создает новую таблицу с той же структурой и выполняет DDL инструкцию для новой таблицы, затем переименовывает новую таблицу в старую таблицу.

Чтобы добавить индекс в таблицу без блокировки, возникающей при ОБНОВЛЕНИИ / ВСТАВКЕ, можно использовать следующий формат инструкции:

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