MySQL index usage and LIMIT statement
Рубрика: Development, MySQL, Работа | 5 January 2007, 10:28 | 
Vadim Voituk
Хочу рассказать об одной достаточно важной особенности SQL-оптимизатора MySQL.
Предположим в таблице почти 390 миллионов (389239897) записей.
Обычный запрос, выбирающий 10 записей за октябрь 2006 г.
EXPLAIN
SELECT *
FROM my_table
WHERE
c_time>='2006-10-01 00:00:00' AND
c_time<'2006-11-01 00:00:00'
LIMIT 10;Обращаем внимаени на значения:
key: i_c_time
rows: 89221170
Такой же запрос, но без указания LIMIT:
EXPLAIN
SELECT *
FROM my_table
WHERE
c_time>='2006-10-01 00:00:00' AND
c_time<'2006-11-01 00:00:00';key: NULL
rows: 389239897
Видно что разница в том, что во втором запросе не используется индекс по полю c_time (key:NULL)
Следовательно происходит полное сканирование таблицы.
Насильно указываем использовать индекс по c_time:
EXPLAIN
SELECT *
FROM my_table FORCE INDEX (i_c_time)
WHERE
c_time>='2006-10-01 00:00:00' AND
c_time<'2006-11-01 00:00:00';Получаем заветные
key: i_c_time
rows: 89221170
Выясняем каким образом LIMIT влияет на использование индексов в запросах.
Указываем в LIMIT число, меньшее чем количество записей в таблице
... LIMIT 389239890Получаем заветные
key: i_c_time
rows: 89221170
Указываем в LIMIT число равное количеству записей в таблице:
[sql]… LIMIT 389239897[/sql]
Имеем
key: NULL
rows: 389239897
И аналогичный запрос с указанием LIMIT бОльшим чем записей в таблице
... LIMIT 389239899
И аналогичный результат
key: NULL
rows: 389239897
т.е. полное сканирование всей таблицы
Следовательно при указании значения LIMIT >= количества записей,
оптимизатор MySQL выполняет полное сканирования таблицы.
Подбирая параметры, я пришел к том, что такое поведение наблюдается
только в случае если ожидаемое число строк превышает 20% от общего числа строк.
Если же в запросе указать значение оператора LIMIT меньше чем записей в таблице,
то в выборке будет участвовать индекс.
За неименением свободного времени я не стал проводить тесты,
указывая в LIMIT диапазоны значений (например LIMIT 20000,30000),
но что-то мне подсказывает что ситуация будет аналогичная.
Ключевые слова – селективность индекса. Каково распределение данных в поле c_time ?
И почему ты не приводишь время выполнения реальных запросов ? Выборка с использованием индекса не всегда быстрее natural scan, а при принуждении движка к беготне туда-сюда по неудачному индексу – в разы медленнее, чем простой перебор всей таблицы.
Михаил,
Распределение c_time – равномерное
Реальные запросы выполнял, но всего несколько раз – время было пропорционально количеству просматриваемых записей.
Насчет “принуждения движка” – вы правы на 100%.
Когда-то у меня была заметка, которая это демонстрировала, но она не пережила переезд на wordpress.
Потому в каждом случае желательно кроме explain посмотреть и реальное время выполнения.
Вадим, если я правильно помню мануал, MySQL не использует индекс, если ему необходимо просмотреть как минимум треть таблицы. В этом случае считается, что использование индекса неэффективно. Если интересно, могу найти точную ссылку на мануал.
Если есть ссылка на мануал – было бы интересно.
Потому как я эту информацию брал из уст консультантов MySQL AB
Едва нашел…
http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html
Правда, в мануале, который я читал несколько лет назад, фразы “but a fixed percentage no longer determines the choice between using an index or a scan…” не было.
Vladimir,
Спасибо за цитату, её как раз не хватало для полноты картины.