Резюме

Должен ли IN переходить на индекс? Конечно, можно ли все сканы делать без индексации? Кажется, я видел дискуссию о том, могут ли Exist и IN перейти на index. Но, кажется, это было слишком давно, и я забыл. Ха-ха, если вы забыли, как запрашивается IN в MySQL, зайдите и просмотрите его.

проблема

Проблема в предыдущей статистике店铺数关注人数说起

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN
<foreach collection="shopIds" item="shopId" separator="," open="(" close=")">
    #{shopId}
</foreach>
GROUP BY shopId

В то время это было с точки зрения кэширования, чтобы проанализировать, как оптимизировать. интересно это читатьКак сделать кеш после микросервиса

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

В нашем бизнесе запрос SQL на 100 мс определяется как медленный запрос, который необходимо оптимизировать. Если оптимизация не может быть выполнена, необходимо контролировать частоту запросов. В то же время операции базы данных, которые превышают 5 с, будут остановлены, чтобы предотвратить перетаскивание всей базы данных, что приведет к вовлечению связанных приложений.

Время выполнения SQL составляет несколько сотен мс и должно быть оптимизировано. Когда Alibaba Cloud обнаруживает этот отчет SQL

1. Отношение количества отсканированных строк к количеству возвращенных строк превышает 100
2. Используйте функцию group_by, обратите внимание, чтобы проверить, используется ли индекс в group_by

анализировать

Первое, в чем нужно убедиться, это в том, что группаshop_idПоле должно быть проиндексировано, так почему же отношение количества просканированных строк к количеству возвращенных строк такое большое?

Сначала просмотрите три элемента оператора запроса анализа.

1. Время отклика, смысл очень ясен, не так много объяснений
2. Количество просканированных строк Сколько строк просканировано за весь запрос
3. Количество возвращенных строк Количество строк, попадающих в результат запроса. Как правило, лучше сканировать столько строк, сколько вы возвращаете, но это идеальный вариант, а это не так. Запрос ассоциативного запроса/сортировки по диапазону сделает количество отсканированных строк больше, чем количество возвращенных строк. Как правило, это соотношение должно быть ниже 10, иначе могут возникнуть проблемы с производительностью.

Не по теме, я всегда чувствую, что поля отображения объяснения mysql не такие интуитивно понятные, как у монго. Принцип работы mongo index такой же, как у mysql, если интересно, можете посмотреть.Анализ индекса Монго

Итак, теперь вопрос в том, почему соотношение количества строк, просканированных/возвращенных этим запросом, такое большое.

Тогда объясни это

Эксперимент 1

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN(1,2,3)
GROUP BY shopId

type	possible_keys	key	key_length	ref	rows	Extras
range	idx_shop	idx_shop	8	null	16000	Using index condition

Как я и ожидал, типrangeИндекс shopId исчез, нет проблем. Тогда почему соотношение количества отсканированных строк/количества возвращенных строк настолько велико.

Эксперимент 2

Попробуйте еще раз и увеличьте диапазон IN.

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN(1,2,3,4,5,6,7,8,9)
GROUP BY shopId

type	possible_keys	key	key_length	ref	rows	Extras
index	idx_shop	idx_shop	8	null	303000	Using where

Результат разный, типаindex, то есть сканирование не по диапазону, а по индексу.

Эксперимент 3

Вынужден перейти на индекс

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention force index(idx_shop)
WHERE shop_id IN(1,2,3,4,5,6,7,8,9)
GROUP BY shopId

type	possible_keys	key	key_length	ref	rows	Extras
range	idx_shop	idx_shop	8	null	29000	Using Index Condition

В настоящее время это сканирование диапазона, а не индекса. Но ты найдешь это времяВремя выполнения не меньше времени последнего выполнения.

MySQL оптимизирует этот запрос, чтобы он не проходил сканирование диапазона. Вместо этого это сканирование индекса. Тогда условий для ИН неизбежно будет все больше и больше, Чем больше просканировано строк, тем больше время выполнения.

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

Суммировать

В соответствии с реальной ситуацией необходимо контролировать область запроса IN. Причины следующие

1. Слишком много условий IN приведет к сбою индекса, и будет выполнено сканирование индекса.
2. Слишком много условий IN вернут много данных, что может привести к переполнению памяти в куче приложения.

Таким образом, вы должны контролировать количество запросов IN

Обратите внимание на публичный аккаунт [Abbot's Temple], как можно скорее получите обновление статьи и начните путь технической практики вместе с аббатом