Индексы MySQL: почему индекс по нескольким столбцам работает хуже, чем индекс по одному столбцу?

0

Я изучаю индексы и решил попробовать эксперимент с базой данных MySQL Sakila. У меня есть следующий запрос:

SELECT title, release_year
FROM film
WHERE release_year < 2010
AND rating = 'R';

Это первый индекс, который я попробовал:

CREATE INDEX idx_releaseYear ON film(rating);

Когда я выполнил свой запрос, MySQL EXPLAIN вернул это:

Изображение 174551

РЕДАКТИРОВАТЬ: Моя интерпретация этих результатов: MySQL использует индекс (отсортированный по рейтингу), чтобы быстро найти все фильмы 'R' -rated, а затем проверяет год выпуска каждой строки, возвращаемой индексом.

Далее я создал индекс по рейтингу и release_year:

CREATE INDEX idx_releaseYear ON film(rating, release_year);

Когда я выполнил свой запрос, MySQL EXPLAIN вернул это:

Изображение 174551

РЕДАКТИРОВАТЬ: Мои ожидания: MySQL будет использовать индекс (отсортированный по рейтингу, затем release_year), чтобы быстро найти все фильмы 'R' -rated и release_years <2010, и использовать таблицу фильмов только для получения названий фильмов из строк, возвращаемых индексом, Несмотря на то, что все фильмы в базе данных имеют одинаковый release_year, я думал, что сортировка индекса по рейтингу, а затем год выпуска будет работать лучше (или, по крайней мере, с использованием только первого столбца индекса) в качестве первого запроса, поскольку в первом запрос должен был проверить годы выпуска в случайном порядке.

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

Есть одна вещь, о которой вы должны знать: все фильмы в базе данных имеют 2006 год выпуска.

  • 0
    если значение года всегда одинаково, это не поможет второму индексу. напротив, проверка для обоих столбцов менее эффективна, чем проверка для одного значения
  • 0
    Чрезмерная индексация небольших наборов данных (особенно больших) = проблемы с производительностью. Смотрите ответ ниже от Гордона. Предполагая, что мы имеем дело с приличным размером набора данных - один столбец в некластеризованном индексе, естественно, будет искать или сканировать страницы данных быстрее, чем 2 столбца. Меньше столбцов = меньше данных, копируемых в индекс. Поэтому меньше фильтровать. Смотрите эту ссылку для получения дополнительной информации об индексации MYSQL -> dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html
Теги:
indexing

3 ответа

5

Это слишком долго для комментария.

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

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

Мораль проста. Не судите о производительности по очень маленьким запросам. Зачастую индексы в этих случаях не нужны, поскольку они имеют дополнительные издержки.

Поделиться
  • 0
    Моя таблица имеет 1000 строк. Это считается слишком маленьким для индекса?
  • 0
    @ user2593669. , , Это зависит, но индекс не обязательно нужен. Другими словами, индекс может не помочь в таблице из 1000 строк, тогда как он может помочь в 1 000 000 строк.
Показать ещё 1 комментарий
0

Ваши "ожидания" верны. EXPLAIN неточно; не верь этому слишком далеко.

WHERE release_year < 2010
  AND rating = 'R'

(обычно) лучше всего оптимизируется с

INDEX(rating,   -- first, because it is tested with '='
      release_year)   -- last, because it is a range.

Если вы можете позволить себе запустить его в обоих направлениях, просмотр SESSION STATUS LIKE 'Handler%' дает вам точное представление о прочитанных строках (и, возможно, записанных во временные таблицы). Я обсуждаю эту технику здесь. Этот блог также объясняет, что составной индекс является лучшим.

Исключения на этот счет являются лучшими:

  • Возможно, статистика говорит, что использование индекса не стоит усилий; простое сканирование таблицы может быть лучше.
  • Возможно, было бы лучше расширить его, чтобы он стал "прикрытием". (Не для рассматриваемого запроса.)
  • Возможно, PRIMARY KEY должен быть этой парой столбцов или, по крайней мере, начинаться с них. Это позволяет избежать отказов между индексом BTree и Data BTree.

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

Примечание: если вы выберете ORDER BY release_year LIMIT 5, составной индекс действительно сияет. Это связано с тем, что индекс можно использовать для всех WHERE, для всех ORDER BY, и получить значение LIMIT, и коснуться только 5 строк. Почти любой другой сценарий должен собрать много строк во временной таблице, отсортировать их, а затем очистить 5 строк.

Поделиться
0

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

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню