#mysql #sql #indexing #query-optimization #where-clause
#mysql #sql #индексирование #оптимизация запросов #where-предложение
Вопрос:
Я много искал в Google и не смог найти четкого ответа на свой вопрос
предположим, у нас есть этот запрос
SELECT * WHERE user_id = x ORDER BY date_created
Если у нас есть индекс одного столбца для user_id и еще один для date_created, использует ли оптимизатор оба индекса? или просто индекс user_id?
Комментарии:
1. Запустите объяснение и посмотрите.
2. большое спасибо… итак, вы говорите, что он не может использовать оба индекса. верно?
3. MySQL будет использовать один индекс за раз для конкретной таблицы, вы можете использовать составной индекс, то есть комбинацию обоих
(user_id, date_created)4. @RickJames Пожалуйста, удалите комментарии, а также ЛОЖНЫЙ комментарий на вашем веб-сайте о Firefox …. 😎
5. @Luuk — ложные комментарии удалены; устаревшая строка в блоге удалена. Спасибо, что указали на это.
Ответ №1:
Это ваш запрос:
SELECT *
FROM mytable
WHERE user_id = 123
ORDER BY date_created
Если у вас есть два разных индекса, то MySQL может использовать index on user_id для применения where предиката (если он считает, что это ускорит запрос, в зависимости от мощности ваших данных и других факторов). Он не будет использовать индекс on date_created , потому что у него нет способа связать промежуточный результирующий набор, который удовлетворяет where предикату, с этим индексом.
Для этого запроса вам нужен составной индекс (user_id, date_created) . База данных использует первый ключ в индексе для фильтрации набора данных: в B-дереве индекса соответствующие строки уже отсортированы по дате, поэтому order by операция становится неоперативной.
Я заметил, что вы используете select * ; это не очень хорошая практика в целом и не очень хорошо для производительности. Если в таблице есть другие столбцы, кроме user и date, это заставляет базу данных просматривать таблицу, чтобы вывести соответствующие строки после фильтрации и упорядочивания по индексу, что может быть дороже, чем вообще не использовать индекс. Если вам нужно всего несколько столбцов, перечислите их:
SELECT date_created, first_name, last_name
FROM mytable
WHERE user_id = 123
ORDER BY date_created
И иметь индекс (user_id, date_created, first_name, last_name) . Это покрывающий индекс: база данных может выполнить весь запрос, используя индекс, не просматривая саму таблицу.
Комментарии:
1. . . Эта часть документации, вероятно, не имеет значения при фильтрации запроса. Он пытается избежать ситуации, называемой thrashing , когда таблица не помещается в память и считывается не по порядку. В документации должно быть ясно по этому вопросу. И на самом деле речь идет не о
select *том, что в индексе есть какой- либо столбец, отличный от ключей. Черт возьми, эта часть документации действительно вводит в заблуждение.2. В общем, ответ GMB хорош. Но, как указывает Гордон, он ушел в потенциально нерелевантные направления. Некоторые пояснения: Столбцы должны быть проверены, следовательно
*, нет большой разницы в усилиях, чем список столбцов. Что имеет значение, так это наличие ненужногоTEXTBLOBстолбца or, который «не записан». Это, вероятно, потребует дополнительных операций чтения с дискаSELECT *вместо того, чтобы избегать этих столбцов.3. и выборки «вне записи» могут привести к «перебоям». Я вижу «хороший» индекс как тот, который обрабатывает
WHERE,GROUP BY, иORDER BY and LIMIT; «лучший» индекс — это тот, который делает это и «покрывает». Но часто «покрытие» невозможно (из-заTEXT) или непрактично («слишком много» столбцов).4. @GordonLinoff: Я удалил эту цитату из документа из своего ответа, поскольку она действительно вводит в заблуждение (и для меня тоже!).
5. @RickJames: Я хотел представить концепцию покрытия в этом
select *обсуждении. Я немного отредактировал свой ответ, чтобы (надеюсь) сделать его немного понятнее.