MyISAM table full scan & key usage for LIMIT statement
Voituk Vadim
на какие недавно наступил работая с
MySQL.
В таблице почти 390 миллионов (389239897)
записей
Обычный запрос, выбирающий 10 записей
за октябрь 2006 г.
PLAIN TEXT
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:
PLAIN TEXT
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:
PLAIN TEXT
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 число, меньшее чем
количество записей в таблице
PLAIN TEXT
... LIMIT 389239890
Получаем заветные
key: i_c_time
rows: 89221170
Указываем в LIMIT число равное
количеству записей в таблице:
PLAIN TEXT
... LIMIT 389239897
Имеем
key: NULL
rows: 389239897
И аналогичный запрос с указанием LIMIT
большим чем записей в таблице
PLAIN TEXT
... LIMIT 389239899
И аналогичный результат
key: NULL
rows: 389239897
т.е. полное сканирование всей таблицы
Следовательно при указании значения
LIMIT >= количества записей,
оптимизатор MySQL выполняет полное
сканирования таблицы.
Подбирая параметры, я пришел к том, что
такое поведение наблюдается
только в случае если ожидаемое число
строк превышает 20% от общего числа
строк.
Если же в запросе указать значение
оператора LIMIT меньше чем записей в
таблице,
то в выборке будет участвовать индекс.
За неименением свободного времени я не
стал проводить тесты,
указывая в LIMIT диапазоны значений
(например LIMIT 20000,30000),
но что-то мне подсказывает что
ситуация будет аналогичная.
Оригинал тут
http://voituk.kiev.ua/2007/01/05/mysql-index-usage-and-limit-statement/
A4
почаще заглядывать в EXPLAIN.
А как ты обнаружил, что пора
оптимизировать - сильно тормозило?
Voituk Vadim
> А как ты обнаружил, что пора
> оптимизировать - сильно тормозило?
размывается, если в таблице 400 млн
записей.
При работе с данными такого обьема
перед тем как выполнить запрос раз 20
его перепишешь и прогонишь на explain.
Иногда, на построении отчетов, 5 минут
потраченные на подобные танцы с
бубном, позволяют эконимить по 2-3 суток
:)
--
Vadim Voituk
http://voituk.kiev.ua