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),
но что-то мне подсказывает что ситуация будет аналогичная.

Комментариев: 6

6 Responses to “MySQL index usage and LIMIT statement”

Комментарии:

  1. Михаил

    Ключевые слова – селективность индекса. Каково распределение данных в поле c_time ?
    И почему ты не приводишь время выполнения реальных запросов ? Выборка с использованием индекса не всегда быстрее natural scan, а при принуждении движка к беготне туда-сюда по неудачному индексу – в разы медленнее, чем простой перебор всей таблицы.

  2. vadim

    Михаил,
    Распределение c_time – равномерное

    Реальные запросы выполнял, но всего несколько раз – время было пропорционально количеству просматриваемых записей.

    Насчет “принуждения движка” – вы правы на 100%.
    Когда-то у меня была заметка, которая это демонстрировала, но она не пережила переезд на wordpress.
    Потому в каждом случае желательно кроме explain посмотреть и реальное время выполнения.

  3. Vladimir

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

  4. Vadim Voituk

    Если есть ссылка на мануал – было бы интересно.
    Потому как я эту информацию брал из уст консультантов MySQL AB

  5. Vladimir

    Едва нашел…

    http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

    Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

    Правда, в мануале, который я читал несколько лет назад, фразы “but a fixed percentage no longer determines the choice between using an index or a scan…” не было.

  6. Vadim Voituk

    Vladimir,
    Спасибо за цитату, её как раз не хватало для полноты картины.

Leave a Reply