Содержание
Рецепты оптимизации запросов
Общие принципы оптимизации
- выбираем только нужные колонки и строки, используем внешние ключи для ускорения JOIN
- ограничиваем обновление всей таблицы с помощью LIMIT или другими методами
- иногда вместо
JOIN
выгодна декомпозиция на несколькоSELECT
с использованиемIN
, вообще минимизируемJOIN
- в
GROUP BY
иORDER BY
желательно только колонки одной таблицы для использования индексов - замена вложенных запросов на
JOIN
может дать выигрыш, но нужно проверять TEXT/BLOB
поля вJOIN
снижают производительностьmysql_fetch_array()
не работает построчно, в отличие отmysql_unbuffered_array()
Подсказки оптимизатору запросов
Иногда можно оптимизировать запрос путем явного указания правила оптимизации, но лучше использовать эту возможность только если больше ничего не помогает.
HIGH_PRIORITY/LOW_PRIORITY
- приоритет для выраженияDELAYED
- отложенныйINSERT/REPLACE
STRAIGHT JOIN
- сортировкаJOIN
в указанном порядкеSQL_SMALL_RESULT/SQL_BIG_RESULT
- дляGROUP BY
иDISTINCT
, нужно ли помещать временную таблицу на дискSQL_BUFFER_RESULT
- помещает результата в буферSQL_CACHE/SQL_NO_CACHE
- управление кешированием запросаUSE INDEX
,FORCE INDEX
,IGNORE INDEX
- использование индексов
Оптимизация COUNT
COUNT(*)
считает строки, COUNT(expression)
считает количество не NULL
результатов:
COUNT(color = 'red' OR NULL)
Основные моменты:
COUNT(*)
быстрее чемCOUNT(id)
- для подсчета количества значений используем покрывающие индексы
- денормализация может существенно улучшить производительность: кеширование счетчиков в отдельном поле, summary tables
Оптимизация GROUP BY и DISTINCT
- используем индексы
- желательно не простые индексы, а покрывающие
SQL_BIG_RESULT
иSQL_SMALL_RESULT
- таблица в памяти или на диске в зависимости от размера результата- желательно группировать по целочисленным ключам
По умолчанию MySQL сортирует результат GROUP BY
по полям группировки, если нет ORDER BY
.
Если порядок не важен, используем ORDER BY NULL
. GROUP BY
может содержать ASC/DESC
.
Оптимизация LIMIT/OFFSET, UNION
Важно помнить, что чем больше смещение, тем ниже производительность. Иногда можно создать специальную колонку с принудительно заполняемым последовательным номером и индексом по ней. Могут помочь покрывающие индексы:
SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5 ) AS lim USING(film_id);
Всегда используем UNION ALL
, при простом UNION
выполняется неявный DISTINCT
- большие накладные расходы.
Кеширование запросов
- кешируется весь результат
SELECT
- проверяется по полному совпадению текста SQL, включая комментарии
- функции типа
NOW()
,CURRENT_DATE()
, пользовательские функции, хранимые процедуры и функции, пользовательские переменные, временные таблицы не кешируются - лучше всего подходит для сложных запросов, возвращающих мало данных, например
COUNT()
- кеширование большого объема данных часто снижает производительность, можно использовать
SQL_NO_CACHE
- лучше использовать кеш на клиенте