Оптимизация составного индекса MySQL в InnoDB

Автор: Чжоу Мэнкан

В одном из моих бизнес-сценариев мне нужно было запросить последние пять записей данных аудита.

SELECT `id`, `title`
FROM `th_content`
WHERE `audit_time` < 1541984478
    AND `status` = 'ONLINE'
ORDER BY `audit_time` DESC, `id` DESC
LIMIT 5;

Данные мониторинга показали, что загрузка ЦП в то время превышала 100%, и я заметил, что многие похожие запросы имели статус create sort index после выполнения show processlist.

Посмотреть структуру таблицы

CREATE TABLE `th_content` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '内容标题',
  `content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT '正文内容',
  `audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '审核时间',
  `last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近编辑时间',
  `status` enum('CREATED','CHECKING','IGNORED','ONLINE','OFFLINE') CHARACTER SET utf8 NOT NULL DEFAULT 'CREATED' COMMENT '资讯状态',
  PRIMARY KEY (`id`),
  KEY `idx_at_let` (`audit_time`,`last_edit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Индекс имеет составной индекс с audit_time слева и не имеет индекса, связанного с status.

Проанализируйте логику выполнения предыдущего оператора SQL:

• Найдите все идентификаторы первичных ключей, которые меньше времени аудита, из составного индекса. (Если до этой отметки времени был проведен аудит 1 миллиона записей данных, идентификаторы первичных ключей этих 1 миллиона записей данных будут извлечены из составного индекса.)
• Отсортируйте 1 миллион идентификаторов. Эта операция сортировки предназначена для оптимизации операций ввода-вывода на следующем этапе извлечения данных в таблице, поскольку близкие первичные ключи могут быть извлечены с помощью одной операции ввода-вывода на диске.
• Получите соответствующий 1 миллион записей данных в таблице и просканируйте полученные записи, чтобы отфильтровать строки со статусом «ONLINE».
• Отсортируйте окончательные результаты запроса (если 500 000 строк имеют статус ONLINE, продолжайте сортировку 500 000 строк).

Хотя требуется получить только пять строк, в предыдущем примере, который я привел, запрос фактически сканирует 1 миллион строк и сортирует 500 000 строк в памяти базы данных из-за большого объема данных.

Следовательно, производительность запроса крайне низкая.

Я нарисовал следующую диаграмму, чтобы показать процесс запроса на шаге 1, где розовые части указывают строки данных, которые необходимо получить в таблице.

Я создал и ввел некоторые данные в соответствии с индексом и шаблоном хранения. Если вы обнаружите какие-либо ошибки, не стесняйтесь оставлять комментарии и дайте мне знать. Я надеюсь, что эта диаграмма поможет вам понять, как хранится составной индекс и как использовать индексы в запросах.

Идея улучшения 1

Не всегда просто использовать индексы в запросах диапазона. Если добавить составной индекс с audit_time и status, какие улучшения мы можем получить?

ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);
mysql> explain select `id`, `title` from `th_content` where `audit_time` < 1541984478 and `status` = 'ONLINE' order by `audit_time` desc, `id` desc limit 5;
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys                            | key              | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | th_content | range | idx_at_ft_pt_let,idx_audit_status        | idx_audit_status | 4       | NULL | 209754 | Using where |
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+

Подробности: поскольку audit_time является запросом диапазона, индекс во втором столбце не нужен, и будет использоваться только audit_time. Следовательно, key_len равно 4. В следующей идее улучшения 2, где включены те же два поля, key_len равно 5.

Давайте также проанализируем процесс выполнения после добавления этого индекса:

• Найдите строку с наибольшим значением audit_time, меньшим, чем это значение времени аудита, из составного индекса.
• Найдите другие строки с меньшими audit_time значениями, чем это значение времени аудита. Поскольку ‹audit_time - это запрос диапазона, а значения индекса во втором столбце разбросаны, нам нужно продолжить поиск совпадающих строк одну за другой и найти все строки индекса, соответствующие критерию (status = '_ 17_'), пока мы не получим пятый соответствующий ряд.
• Получите и запросите определенные записи данных в таблице.

На предыдущей диаграмме розовым цветом обозначены строки, отвечающие требованиям первого столбца индекса. Продолжите запрос, и на этом листовом узле будут найдены три записи. Переместитесь влево и выполните запрос на предыдущем листовом узле. Когда будут найдены пять совпадающих строк, извлеките соответствующие записи данных в таблице.

Реализованные улучшения

Поскольку индекс содержит status значений, не требуется извлекать соответствующие данные в процессе фильтрации строк со статусом «ONLINE». iops значительно уменьшается, потому что в таблице необходимо получить только пять строк данных.

Недостаток этого индекса

Если пять строк, просканированных в idx_audit_status, все имеют status ONLINE, нужно сканировать только пять строк.

Если только четыре строки из первых 1 миллиона строк, просканированных в idx_audit_status, имеют status ONLINE, необходимо просканировать 1 миллион и 1 строку, чтобы получить необходимые пять строк. Число строк, которые нужно просканировать индексом, неизвестно.

Идея улучшения 2

ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);

Это позволяет повысить эффективность как сортировки, так и извлечения данных в таблице.

Первоисточник