Запрос с GROUP BY и ORDER BY не использует индекс

Question

Всем привет!

Помогите проставить корректные индексы.

Есть таблица:

CREATE TABLE `mail` (
  `id` int NOT NULL,
  `account_id` int DEFAULT NULL,
  `folder_path` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `mail_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL COMMENT 'текущий менеджер',
  `ticket_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `from_user_id` int DEFAULT NULL,
  `to_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `reply_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `bcc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `cc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `subject` varchar(350) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `read_status` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


ALTER TABLE `mail`
  ADD KEY `from_address` (`from_address`(255)),
  ADD KEY `to_address` (`to_address`(255)),
  ADD KEY `subject` (`subject`),
  ADD KEY `id` (`id`) USING BTREE,
  ADD KEY `ticket_number` (`ticket_number`,`folder_path`,`id`) USING BTREE,
  ADD KEY `id_2` (`id`,`created_at`),
  ADD KEY `folder_path` (`folder_path`,`id`) USING BTREE;


ALTER TABLE `mail`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

Следующим запросом я получаю список писем сгрупированных по тикету и отсортированных по дате получения, то есть чтобы в каждой группе я видел дату последнего письма:

SELECT
    `mail`.*,
    mail.mail_at AS receivedAt
FROM
    mail AS mail
LEFT JOIN `mail` AS `m2`
ON
    `mail`.`ticket_number` = `m2`.`ticket_number` AND `mail`.`folder_path` = `m2`.`folder_path` AND `mail`.`id` < `m2`.`id`
WHERE
    (`mail`.`folder_path` = 'INBOX') AND `m2`.`id` IS NULL
GROUP BY
    `mail`.`ticket_number`
ORDER BY
    `created_at`
DESC
LIMIT 20 OFFSET 0

В таблице около 200 тыс записей. Время выполнения 1.3 - 1.5 секунды и сильно грузит процессор.

+----+-------------+-------+------------+------+-----------------------------------+---------------+---------+---------------------------------------------------------------+-------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys                     | key           | key_len | ref                                                           | rows  | filtered | Extra                                |
+----+-------------+-------+------------+------+-----------------------------------+---------------+---------+---------------------------------------------------------------+-------+----------+--------------------------------------+
|  1 | SIMPLE      | mail  | NULL       | ref  | ticket_number,folder_path         | folder_path   | 602     | const                                                         | 46152 |   100.00 | Using temporary; Using filesort      |
|  1 | SIMPLE      | m2    | NULL       | ref  | id,ticket_number,id_2,folder_path | ticket_number | 684     | sandbox_busf.mail.ticket_number,sandbox_busf.mail.folder_path |     1 |    10.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+------+-----------------------------------+---------------+---------+---------------------------------------------------------------+-------+----------+--------------------------------------+

Если я убираю ORDER BY, то запрос выполняется за 0.01 сек.

+----+-------------+-------+------------+-------+-----------------------------------+---------------+---------+---------------------------------------------------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                     | key           | key_len | ref                                                           | rows | filtered | Extra                                |
+----+-------------+-------+------------+-------+-----------------------------------+---------------+---------+---------------------------------------------------------------+------+----------+--------------------------------------+
|  1 | SIMPLE      | mail  | NULL       | index | ticket_number,folder_path         | ticket_number | 688     | NULL                                                          |  935 |    22.45 | Using where                          |
|  1 | SIMPLE      | m2    | NULL       | ref   | id,ticket_number,id_2,folder_path | ticket_number | 684     | sandbox_busf.mail.ticket_number,sandbox_busf.mail.folder_path |    1 |    10.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+-------+-----------------------------------+---------------+---------+---------------------------------------------------------------+------+----------+--------------------------------------+

Очень прошу, помогите составить корректные индексы. Третий день пытаюсь, но не получается :(

Answer 1

Давайте разберёмся с тем что вам нужно и что вы делаете. Лучше всего двигаться от простого к сложному шаг за шагом.

Надо получить список писем при условии folder_path = 'INBOX':

select `id`
from `mail`
where `folder_path` = 'INBOX'
limit 20 offset 0;

Сгруппировать письма по тикету:

select
    `ticket_number`,
    json_arrayagg(`id`) as `mails`
from `mail`
where `folder_path` = 'INBOX'
group by `ticket_number`
limit 20 offset 0;

Важно понимать, что когда база группирует строки, то она должна принять решение, какое значение использовать для столбцов, которые не указаны в group_by. Если для одного тикета есть несколько писем, то группируя по номеру тикета, мы должны указать какой именно id письма поместить в результат. Например, можно собрать все id по одному тикету в массив с помошью json_arrayagg.

Выбрать дату создания для последнего письма в группе:

select
    `ticket_number`,
    json_arrayagg(`id`) as `mails`,
    max(`created_at`) as `max_created_at`
from `mail`
where `folder_path` = 'INBOX'
group by `ticket_number`
limit 20 offset 0;

Тут мы используем другую аггрегирующую функцию — max.

Осталось отсортировать по этой последней дате:

select
    `ticket_number`,
    json_arrayagg(`id`) as `mails`,
    max(`created_at`) as `max_created_at`
from `mail`
where `folder_path` = 'INBOX'
group by `ticket_number`
order by `max_created_at`
limit 20 offset 0;

Как видите, запрос получился совсем другой. Где-то вы свернули не туда.

Теперь разберёмся почему запросы с order by и без работают с разной скоростью. Чтобы сгруппировать все письма по тикетам, вам надо просканировать все строки с folder_path = 'INBOX'. Это может быть довольно много строк. Дальше вы просто получаете первые 20 результатов и больше не напрягаете базу. Но когда вы делаете order by, то база должна пересортировать все эти строки (Using filesort в вашем explain). Это может занимать много времени.

Одним из способов оптимизации такого запроса может быть денормализация. Добавим в таблицу тикетов поле last_mail_date и построим по нему индекс. При добавлении письма в таблицу mail будем обновлять last_mail_date в таблице тикетов. Теперь чтобы выбрать тикеты, по которым недавно отправлялись письма, достаточно выбрать из таблицы тикетов с сортировкой по last_mail_date и вторым запросом получить письма для всех выбранных ticket_number (where ticket_number in ...).

БЛОГ НА HUSL

Запрос с GROUP BY и ORDER BY не использует индекс

Ответы (1 шт):