Проблема с тайм-аутом MySQL в производстве из-за левого соединения
Я работаю над приложением, в котором возникла проблема в производстве, когда один запрос выдает тайм-аут. Приложение использует MySQL.
SELECT
`tags`.*,
COUNT( ct.contact_id ) AS contacts_count
FROM
`tags`
LEFT JOIN `contact_tag` AS `ct` ON `tags`.`id` = `ct`.`tag_id`
WHERE
`tags`.`company_id` = 1068
AND `tags`.`category` = 1
AND `tags`.`deleted_at` IS NULL
GROUP BY
`tags`.`id`
ORDER BY
`tags`.`id` DESC
поэтому я разделил запрос на две части:
Этот первый запрос работает нормально:
SELECT
tags.id
FROM
tags
WHERE
tags.company_id = 1068
AND tags.category = 1
AND tags.deleted_at IS NULL
GROUP BY
tags.id
ORDER BY
tags.id DESC
LIMIT 20
с помощью этого запроса я получил следующие идентификаторы тегов:
294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287
Тогда этот второй запрос в первый раз всегда занимает 6 секунд, а затем 300 мс:
SELECT
tag_id,
COUNT(com_tag.contact_id) AS communications_count
FROM
contact_tag as com_tag
WHERE
tag_id IN (294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287)
GROUP BY
tag_id;
Результат этого запроса:
Последний запрос с объяснением:
Определение таблицы contact_tag:
CREATE TABLE `contact_tag` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`contact_id` int unsigned NOT NULL,
`tag_id` int unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `contact_tag_contact_id_tag_id_unique` (`contact_id`,`tag_id`),
KEY `contact_tag_tag_id_foreign` (`tag_id`),
CONSTRAINT `contact_tag_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `contact_tag_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2477082 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Сколько у вас tag_id в tag_id IN (1, 2, 3...)? MySQL необходимо подсчитать их для каждого из них, и это может занять некоторое время.
Вы хотите, чтобы объяснение было текстовым определением?
Да, вывод EXPLAIN <select statement> и SHOW CREATE TABLE contact_tag в формате ТЕКСТ, потому что изображения бесполезны...
Большой. Добавлено как изменения
измените порядок индексов contact_id и tag_id и включите contact_id is not null в свой запрос. Затем он может выполнить весь запрос, используя индекс и не разбивая по страницам, чтобы прочитать каждую соответствующую строку.
Ответы 2
ЭТО НЕ ОТВЕТ!
Статистика MySQL использует статистику для оптимизации запросов. Убедитесь, что ваша статистика до настоящего времени. Вы можете запустить
АНАЛИЗ ТАБЛИЦЫ contact_tag;
обновить статистику
Профилирование Покажите нам профилирование вашего запроса с помощью этих шагов и опубликуйте результаты.
mysql> SET профилирование = 1;
mysql> ВЫБРАТЬ 1; -- выполнить ваш запрос
mysql> показать профиль;
mysql> показать профиль ВСЕ;
mysql> SET профилирование = 0;
Вы не включили определения таблиц, но судя по названию этого индекса contact_tag_contact_id_tag_id_unique я предполагаю, что у вас есть такой индекс:
UNIQUE contact_tag_contact_id_tag_id_unique (contact_id, tag_id)
Этот индекс непригоден для использования, поскольку contact_id не является конкретным. Но у вас есть конкретный tag_id, поэтому измените порядок этого индекса на обратный:
UNIQUE contact_tag_tag_id_contact_id_unique (tag_id, contact_id)
позволит использовать этот индекс. Тогда можно будет отфильтровать любой NULL contact_id. Если у вас нет NULL в contact_id, вы, вероятно, можете вместо этого просто использовать COUNT(*), поэтому я предполагаю, что он у вас есть.
В любом случае вы можете написать запрос следующим образом:
SELECT
tag_id,
COUNT(*) AS communications_count
FROM
contact_tag as com_tag
WHERE
tag_id IN (294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287) AND
contact_id IS NOT NULL
GROUP BY
tag_id;
РЕДАКТИРОВАТЬ
С учетом вышесказанного, вы можете получить более быстрые результаты, используя подзапрос.
Я предполагаю, что tags.id уникален, поэтому вы можете исключить GROUP BY.
SELECT
`tags`.*,
(SELECT COUNT(*) FROM `contact_tag` WHERE `contact_tag`.`tag_id` = tags.id) AS contacts_count
FROM
`tags`
WHERE
`tags`.`company_id` = 1068
AND `tags`.`category` = 1
AND `tags`.`deleted_at` IS NULL
ORDER BY
`tags`.`id` DESC
«Запрос использует индекс» — какой из трех опубликованных запросов вы имеете в виду. также добавьте объяснение запроса и определений таблиц в виде текста