6.2. Оптимизация запросов

6.2. Оптимизация запросов

Основным способом повышения производительности запросов являются индексы. Определить, действительно ли созданные вами индексы используются запросом, позволяет команда

EXPLAIN <Текст запроса>;

Набор данных, выводимый командой EXPLAIN, содержит детальную информацию о ходе выполнения запроса. Каждая строка в этом наборе описывает одну из операций, составляющих запрос. Например, команда

EXPLAIN SELECT name,address,product_id,qty

FROM Customers, Orders

WHERE Customers.id=customer_id AND date='20007-12-12

выводит результат, представленный в табл. 6.1. Таблица 6.1. Результат выполнения команды EXPLAIN

Столбец table (таблица) содержит имя обрабатываемой таблицы, а столбец select_type (тип запроса) указывает место операции в структуре запроса:

• SIMPLE – простой запрос без вложенных запросов и UNION;

• PRIMARY – первый запрос в UNION или внешний запрос, имеющий вложенные запросы;

• UNION – второй и последующие запросы в объединении UNION;

• DEPENDENT UNION – второй и последующие вложенные запросы в объединении UNION, связанные с внешним запросом (о связанных подзапросах вы узнали в подразделе «Операторы сравнения с результатами вложенного запроса»);

• UNION RESULT – операция объединения результатов запросов;

• SUBQUERY – вложенный запрос;

• DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY – вложенный запрос, связанный с внешним запросом;

• DERIVED – запрос, генерирующий промежуточный результат (такой запрос следует после ключевого слова FROM).

В столбце rows (строки) содержится оценка количества строк таблицы, которое потребуется просмотреть для данной операции. Если ваш запрос имеет сложную структуру, значения в столбце rows помогут вам выявить его «узкие места».

Столбец possible_keys (возможные индексы) показывает, какие индексы из числа существующих в таблице программа MySQL могла бы использовать при выполнении запроса. Если этот столбец содержит значение NULL, а вы считаете необходимым ускорить операцию, добавьте в таблицу подходящие индексы (рекомендации по созданию индексов были изложены в предыдущем разделе).

Возможна ситуация, когда нужный индекс существует, но программа MySQL не применяет его, считая по каким-либо причинам, что просматривать таблицу полностью будет эффективнее. Индекс, фактически используемый при выполнении запроса, отображается в столбце key (индекс); если данный столбец содержит значение NULL, значит, программа MySQL не выбрала ни один из доступных индексов. Если вы, тем не менее, считаете, что индекс должен использоваться, выполните следующие действия:

1. Обновите статистику распределения индексов в таблице с помощью команды

ANALYZE TABLE <Имя таблицы>;

2. Повторно выполните команду EXPLAIN. Если и после обновления статистики индекс не начал использоваться, добавьте в текст запроса после имени таблицы параметр FORCE INDEX (<Имя индекса>). Сравните время выполнения запроса с параметром FORCE INDEX и без него и выберите оптимальный вариант.

Помимо введения индексов, существуют и другие способы ускорения запросов. Вот наиболее простые из них.

• Исключите получение лишней информации из базы данных. Результат запроса должен содержать только те данные, которые действительно необходимы. Например, если вы отображаете на веб-странице не более 20 товаров, не нужно получать из таблицы Products (Товары) все данные. Вместо этого используйте запросы вида

SELECT <Список столбцов> FROM <Список таблиц>

[WHERE <Условие отбора>]

…

LIMIT <Количество строк> OFFSET <Сдвиг>;

Так, для получения первой «порции» из 20 товаров выполните запрос

SELECT * FROM Products LIMIT 20 OFFSET 0;

Следующих 20 товаров —

SELECT * FROM Products LIMIT 20 OFFSET 20;

Затем

SELECT * FROM Products LIMIT 20 OFFSET 40;

и т. д.

• Максимально упростите систему привилегий доступа. Чем сложнее система привилегий, тем больше времени занимает проверка прав доступа при выполнении запросов (а также других SQL-команд). Хорошим решением является разграничение доступа на уровне баз данных и отказ от присвоения привилегий доступа к отдельным таблицам и столбцам. В этом случае контроль действий пользователей не требует обращения к таблицам tables_priv и columns_ priv (см. подраздел «Просмотр привилегий»).

• Если запрос содержит выражение, проверьте, не является ли вычисление этого выражения причиной замедления запроса. Для этого выполните команду

SELECT BENCHMARK(<Количество повторений>,<Выражение>);

Функция BENCHMARK() всегда возвращает значение 0, однако в сообщении о результате выполнения команды указывается время ее выполнения, благодаря чему вы можете оценить время вычисления выражения. Например, если ваш запрос включает вычисление синуса для каждой строки таблицы, выполните команду

SELECT BENCHMARK(10000000,SIN(1));

При использовании процессора с тактовой частотой 1,6 ГГц выполнение этой команды займет приблизительно 1 с. Таким образом, программа MySQL способна производить около 10 млн вычислений синуса в секунду, а значит, функция SIN() не оказывает существенного влияния на скорость запроса. Итак, мы рассмотрели способы оптимизации таблиц и запросов. В следующем разделе вы узнаете о том, как увеличить быстродействие сервера путем настройки системных переменных.

Данный текст является ознакомительным фрагментом.