Как оптимизировать sql запрос

Магия оптимизации SQL запросов

Думаю, каждый хоть раз использовал команду explain или хотя бы слышал про нее. Эта команда демонстрирует план выполнения запроса, но как именно СУБД приходит к нему остается загадкой. Да и как вообще СУБД понимает, что выбранный запрос оптимален? Неужели она проверяет все возможные варианты?
В этой статье я постараюсь дать небольшое представление о том, как работают оптимизаторы запросов с теоретической точки зрения.

Начнем с того, что можно выделить два основных подхода к поиску наиболее эффективного варианта выполнения: эвристический и стоимостной.

Эвристический подход

Для начала коротко рассмотрим эвристический подход. Здесь решение о выборе плана запроса принимается на основании некоторых заранее предложенных правил.
К примеру, таких:

• Выборка и проекции должны быть выполнены как можно раньше в дереве запроса
• При объединении таблиц необходимо в первую очередь выполнять наиболее строгие операции объединения

Важно, что эти правила основываются на общих идеях и теоретически не обязаны давать наиболее эффективные результаты. Но такой подход довольно прост в реализации и потому с завидной периодичностью используется даже в ведущих СУБД.

Стоимостной подход

Перейдем к более интересному, на мой взгляд, подходу — стоимостному. Его идея заключается в оценке эффективности плана выполнения на основании некоторого параметра. Этим параметром может быть время выполнения, количество физических обращений к диску, размеры промежуточных выборок из БД и т.д.

Первое, что делает при таком подходе оптимизатор — строит дерево запроса. Разберемся, что это такое.

Деревом запроса называется древовидная структура, в узлах которой расположены логические операторы, соответствующие отдельным операциям запроса.
Другими словами, нечто подобное:

К каждому такому дереву можно применить трансформацию — логическую или физическую. Логические трансформации порождают новые деревья посредством изменения структуры исходных, а физические заменяют логические операторы на их конкретные реализации, называемые физическими операторами, не меняя структуру дерева. Так, например, логический оператор JOIN можно заменить на физические LOOP JOIN или MERGE JOIN.

Трансформации производятся на основании некоторого набора правил, состоящих из шаблона и подстановки. Шаблон показывает, к какому выражению данное правило может быть применено, а подстановка — что будет получено в результате изменений.

Разобравшись с тем, что такое дерево поиска, введем ещё два определения: логическая эквивалентность и область поиска.

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

Областью поиска называется множество всевозможных логически эквивалентных деревьев запроса. Исходной областью поиска является логическое дерево, полученное в результате парсинга искомого запроса. При замене каждого из узлов дерева на его логические эквиваленты область поиска расширяется. Конечной областью поиска будет таким образом являться множество всех эквивалентных искомому запросу деревьев поиска. А точнее, одно дерево, каждый из узлов которого будет являться полной эквивалентной группой аналогичного узла в первоначальном дереве. Согласен, звучит сложно, но теория — она такая.

Итак, с определениями разобрались, теперь перейдём непосредственно к оценке стоимости плана. Она состоит из трех этапов: оценки кардинальности, применения стоимостной модели и перечисления планов. Рассмотрим каждый в отдельности.

Этап 1. Оценка кардинальности

Для тех кто не знал или забыл, напомню, что кардинальностью называется размер выборки, возвращаемой некоторым выражением. Чаще всего под размером понимается количество полученных в результате запроса кортежей, но иногда речь может идти и о количестве страниц. Очевидно, что при наличии готовой выборки посчитать кардинальность не составит труда, но можно ли как-то провести оценку, не выполняя сам запрос?

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

Первый вариант заключается в анализе выборки «в миниатюре». При таком подходе, к примеру, соединяют два небольших набора кортежей вместо соединения двух полных таблиц. Полученная в результате оценка затем экстраполируется на полноценное соединение.

Во втором случае используется некоторая дополнительная информация о таблицах БД, к примеру, размер, количество уникальных атрибутов, наиболее часто встречающиеся атрибуты и т.д. На основании этих данных можно «прикинуть» размер результата того или иного запроса. Подобные оценки грубы, и для их уточнения часто прибегают к использованию гистограмм — информации о распределении данных одного домена в таблице. При построении гистограмм рассматриваемый домен разбивается на интервалы, и затем для каждого из них вычисляется количество попавших в него значений. Определять размеры интервалов тоже можно по-разному: строить их равными по диапазону или по количеству значений.

Саму оценку кардинальности проводят в таком случае при помощи другого свойства запросов — селективности. Селективностью называется отношение количества строк, возвращаемых запросом, к общему количеству строк в таблице. То есть, если в таблице user содержатся записи о 100 людях, 25 из которых младше 50, а 75 старше, то селективность запроса SELECT * FROM user WHERE age > 50 будет равняться 75/100=0.75

Вернёмся к оценке кардинальности. Предположим, что таблица user имеет 1000 записей, и согласно гистограмме распределения количество пользователей с параметром age=20 равняется 100, а количество пользователей с параметром sex=“male“ равняется 500.

Тогда запрос SELECT * FROM user WHERE age=20 AND sex=’male’ будет оценен следующим образом: age=20 даст селективность 100/1000=0.1, а sex=’male’ соответственно 500/1000=0.5. Итоговая селективность составит 0.1*0.5=0.05. Кардинальность данного запроса в таком случае может быть оценена как 1000*0.05=50 записей.

Ну, а если данных о распределении значений атрибутов какого-то столбца нет, то можно прибегнуть к ряду допущений, например, предположить, что значения атрибутов распределены равномерно.

Этап 2. Применение стоимостной модели

Стоимостная модель задает отображение полученного дерева плана на некоторое число, которое будет обозначать его итоговую стоимость.
В свою очередь стоимостью плана называется сумма стоимостей всех входящих в него операторов. Стоимость оператора же может зависеть от множества факторов. Часть из них определена заранее и зависит, к примеру, от оборудования, на котором развернута СУБД. А часть может вычисляться динамически — например, оценка кардинальности оператора, наполненность буферов или наличие свободных потоков для параллельного выполнения.

Этап 3. Перечисление планов

Перечислением планов называется выбор наиболее оптимальной последовательности выполнения соединений. Важно помнить про время построения плана при использовании слишком большого числа соединений таблиц в одном запросе. Чаще всего эта проблема решается посредством применения ряда эвристик в ущерб оптимальности итогового плана.

Существует два наиболее часто применяемых алгоритма перечисления: прохождение по дереву плана снизу вверх при помощи динамического программирования и прохождение по дереву плана сверху вниз при помощи мемоизации.

Первый подход: снизу вверх

Первым алгоритмом, который мы рассмотрим, является прохождение по дереву снизу вверх при помощи динамического программирования. Здесь для вычисления оптимального плана на i-м уровне дерева вычисляются все планы на i-1-м уровне дерева, и из них выбирается наилучший. Очевидно, что при таком подходе выбор оптимального плана выродится в полный перебор всех вариантов, поэтому для использования на практике его нужно модифицировать. Так П.Г.Сэлинджер предложила рассматривать только левосторонние деревья, у которых при этом элементы на левой ветви имеют минимальную оценку кардинальности.

Чтобы понять, почему это работает, рассмотрим пример. Предположим, что у нас есть три таблицы B, C и D. Пусть A — таблица, полученная в результате соединения таблиц C и D.

Допустим, что некоторая операция contains возвращает true, если в таблице, переданной первым аргументом, есть кортеж, который можно естественным образом соединить с переданным во втором аргументе кортежем (совпадающие по именам атрибуты равны).

Попробуем сначала выполнить соединение A с B, а потом B с A. В первом случае процесс соединения можно описать следующим псевдокодом:

for a in A: if (contains(B, a)): return a x b

Если предположить, что метод contains для поиска искомого кортежа по очереди проверит каждую запись в таблице B, то приведенная операция очевидно будет иметь квадратичную сложность. Но поскольку B постоянно присутствует в базе данных, для нее можно построить индекс, что позволит быстрее выполнить поиск, и асимптотическая сложность задачи понизится.

Во втором случае процесс можно представить следующим (очень отличающимся) псевдокодом:

 for b in B: if (contains(A, b)): return b x a

Казалось бы, рассуждения здесь должны быть аналогичными, но это не так. Поскольку таблица A является промежуточным результатом вычисления, она не будет содержать заранее определенных индексов, а это в свою очередь означает, что сложность операции в таком случае будет всегда квадратичной вне зависимости от наличия индексов в таблице B. Значит в теории такие деревья вообще не стоит рассматривать — зачем, если вариант заведомо неэффективный?

Второй подход: сверху вниз

Другой подход, называющийся мемоизацией, заключается в построении дерева, узлами которого являются группы операций, и построение с их помощью итогового результата. Для этого каждому оператору в группе присваивается оценка стоимости и на каждом уровне выбирается один «победитель» — наиболее оптимальный оператор из своей группы. Набор групп, рассматриваемых в процессе отбора оптимального результата, называется МЕМО. Во многих современных оптимизаторах результаты выбора оптимального варианта для каждого узла запоминаются, чтобы уменьшить пространство поиска в дальнейшем.

На этом все, надеюсь, мне удалось хотя бы немного осветить базовые принципы работы оптимизаторов запросов, и спасибо за проявленный интерес.

P.S. Любая критика и исправления только приветствуются 🙂

• оптимизация запросов
• оптимизация запросов к базам данных
• оптимизация запросов postgresql
• оптимизатор запросов
• кардинальность
• запрос
• запросы к бд
• запросы sql

• MySQL
• PostgreSQL
• SQL
• Администрирование баз данных

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

У большинства MySQL серверов включено кэширование запросов. Один из наилучших способов улучшения производительности — просто предоставить кэширование самой базе данных. Когда какой-либо запрос повторяется много раз, его результат берется из кэша, что гораздо быстрее прямого обращения к базе данных. Основная проблема в том, что многие просто используют запросы, которые не могут быть закэшированы:

// запрос не будет кэширован $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // а так будет! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

Используйте EXPLAIN для ваших запросов SELECT

Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:

После добавления индекса для поля group_id:

Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.

LIMIT 1, когда нужна единственная строка

Иногда, обращаясь к таблице, вы точно знаете, что вам нужна только одна конкретная строка. Например, нужно получить одну уникальную строку или просто проверить существование записей, удовлетворяющих запросу WHERE.
В этом случае, добавление LIMIT 1 в ваш запрос будет оптимальней. Таким образом, база данных остановит выборку записей, после нахождения первой же, вместо того, чтобы выбрать всю таблицу или индекс.

// есть пользователи в Alabama? // можно так: $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows($r) > 0) < // . >// но так лучше: $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1"); if (mysql_num_rows($r) > 0) < // . >

Индексируйте поля, по которым ищите

Индекс это не только основной или уникальный ключ. Это так же любые столбцы в таблице, которые вы используете для поиска и их можно проиндексировать.

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE ‘a%’». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE ‘%apple%’»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

Индексируйте поля для объединения и используйте для них одинаковые типы столбцов

Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.

// выборки компаний в штате пользователя $r = mysql_query("SELECT company_name FROM users JOIN companies ON (users.state = companies.state) users.id = $user_id"); // обе колонки state должны быть проиндексированы // они обе должны иметь один тип данных и кодировку символов // а иначе MySQL сделает полную выборку из этих таблиц

Не используйте ORDER BY RAND() для больших таблиц

(Имеется в виду выборка единственной строки. Примечание переводчика)

Это одна из тех вещей, который выглядят очень хорошо на первый взгляд, но многие начинающие программисты попались на эту удочку. Вы даже не представляете, какое слабое место в производительности возникнет, если будете использовать это в запросах.
Если вам действительно нужен случайный порядок строк в запросе, то есть лучшие способы сделать это. Конечно, это приведет к дополнительному коду, но позволит избавиться от слабого места в производительности, которое будет сужаться экспоненциально при увеличении данных. Проблема в том, что MySQL будет выполнять RAND() (а это нагрузка на процессор) для каждой строки при сортировке, выдавая только одну строку.

Таким образом вы выберите случайный номер, который меньше количества строк и используете его для смещения в LIMIT.

$n_rand = mt_rand(1, $mysqli->query(«SELECT MAX(id) FROM `table`»));
$result_set = $mysqli->query(«SELECT * FROM `table` WHERE `id`=’$n_rand'»);
?>

SELECT f.id FROM files f JOIN ( SELECT RAND() * (SELECT MAX(id) FROM files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1;

Избегайте SELECT *

Чем больше данных считывается из таблицы, тем медленнее запрос. Это увеличивает время работы с хранилищем данных. Также, когда сервер базы данных установлен отдельно от web-сервера, будет большая задержка при передаче данных по сети.
Прописывайте, какие именно столбцы из запроса вам нужны.

// не очень хорошо: $r = mysql_query(«SELECT * FROM user WHERE user_id = 1»); $d = mysql_fetch_assoc($r); echo «Welcome »; // лучше: $r = mysql_query(«SELECT username FROM user WHERE user_id = 1»); $d = mysql_fetch_assoc($r); echo «Welcome »; // разница более значительна при большем наборе данных.

Старайтесь всегда создать поле ID

В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.

Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

Подготовленные выражения

Есть несколько фактов преимущества использования подготовленных выражений в области безопасности и производительности.

Подготовленные выражения по умолчанию фильтруют переменные, которые к ним привязаны, что является очень хорошей защитой ваших приложений против атак типа «инъекция SQL». Конечно, вы можете фильтровать переменные вручную тоже, но такие методы более подвержены ошибкам и забывчивости программистов.

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

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

Было время, когда программисты избегали использования подготовленных выражений только по причине того, что они не кэшировались MySQL. Но начиная с версии 5.1 кэширование также поддерживается для запросов подготовленных выражений.

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

// Создаем подготовленное выражение if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) < // Привязываем параметры $stmt->bind_param("s", $state); // Выполняем $stmt->execute(); // Привязываем переменные результата $stmt->bind_result($username); // Получаем значения $stmt->fetch(); printf("%s is from %s\n", $username, $state); $stmt->close(); >

Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

Хранение IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

$r = "UPDATE users SET ip = INET_ATON('') WHERE user_id = $user_id";

Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

Вертикальное разделение

Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.

Разделяйте большие запросы DELETE и INSERT

Если вам необходимо сделать большой запрос на удаление или вставку данных, надо быть осторожным, чтобы не нарушить работу приложения. Выполнение большого запроса может заблокировать таблицу и привести к неправильной работе всего приложения.
Apache может выполнять несколько параллельных процессов одновременно. Поэтому он работает более эффективно, если скрипты выполняются как можно быстрее.
Если вы блокируете таблицы на долгий срок (например, на 30 секунд или дольше), то при большой посещаемости сайта, может возникнуть большая очередь процессов и запросов, что может привести к медленной работе сайта или даже к падению сервера.
Если у вас есть такие запросы, используйте LIMIT, чтобы выполнять их небольшими сериями.

while (1) < mysql_query("DELETE FROM logs WHERE log_date // небольшая пауза usleep(50000); >

Выбирайте правильный тип таблицы

Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.

На нашем сайте Вы можете найти надежный платный хостинг.

Опыт и рекомендации по оптимизации SQL-запросов

Источник: Статья предоставлена автором для публикации в FORS Magazine. В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer. Нередко возникают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Назовем такие запросы неэффективными или ресурсоемкими.
Причины ресурсоемкости запроса могут быть следующие:

• плохая статистика по таблицам и индексам запроса;
• проблемы с индексами в запросе;
• проблемы с хинтами в запросе;
• неэффективно построенный запрос;
• неправильно настроены параметры инициализации базы данных, отвечающие за производительность запросов.

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

• средства, позволяющие получить предполагаемый план выполнения запроса;
• средства, позволяющие получить реальный план выполнения запроса;

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже приводиться пример плана выполнения запроса:

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup') AND SB.RANK >70 AND ROWNUM 

Текст выполняемого запроса; select * from table(dbms_xplan.display_cursor()); 

Select * from v$sql_plan where sql_id='SQL_ID'; 

Select * from v$sql_plan where sql_id='SQL_ID';

Select sql_id from v$sql where sql_fulltext like '%уникальный фрагмент текста запроса%'; 

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER,LEVEL 12' TRACEFILE_IDENTIFIER='M_2013' TIMED_STATISTICS=TRUE SQL_TRACE=TRUE; ---Исследуемый запрос, например, Select * from AGREEMENT where ISN=138518816; ALTER SESSION SET SQL_TRACE=FALSE;

Select value from v$parameter p where p.name= 'user_dump_dest';

DECLARE my_task_name varchar2(30);my_sqltext clob;rep_tuning clob; BEGIN Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task'); exception when others then NULL; end; MY_SQLTEXT:= ' --текст запроса (без точки с запятой в конце запроса) ' ; MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext, TIME_LIMIT => 60, --задается время выполнения в секундах TASK_NAME =>'my_sql_tuning_task', DESCRIPTION=> my_task_name , SCOPE => DBMS_SQLTUNE.scope_comprehensive); begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task'); exception when others then null;end; END;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup') AND SB.RANK >70 AND ROWNUM 

  
При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
  
Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
  
Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
  
Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
  
Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц. 
Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса
После анализа плана выполнения запроса осуществляется его оптимизация. 
Оптимизация запроса предполагает удаление причин неэффективности запроса, среди которых наиболее весомыми являются:

  
плохая статистика таблиц и индексов, участвующих в запросе (наиболее важный фактор, на который в первую очередь надо обратить внимание);
  
проблемы с индексами: отсутствие нужных индексов, неэффективно построенные индексы, неэффективно используемые индексы, большое значение фактора кластеризации;
  
проблемы с хинтами: отсутствие хинтов или они неэффективны;
  
неэффективная структура запроса (запрос построен не корректно).

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

   Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики. 
Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами: 
   для таблицы: 

execute DBMS_STATS.GATHER_TABLE_STATS ('HIST','AGREEMENT',NULL,10,NULL,'FOR ALL INDEXED COLUMNS SIZE AUTO',4);
execute DBMS_STATS.GATHER_INDEX_STATS('HIST', 'X_AGREEMENT',null,10,null,null,4);
где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.
Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:
SELECT owner, table_name, round(d.sample_size/decode(d.num_rows,0,100000000000,d.num_rows)*100,2) proch,d.last_analyzed FROM ALL_TABLES d WHERE owner='HIST' and table_name = 'AGREEMENT';
Процент сбора статистики по индексу находиться по запросу
SELECT owner,table_name, index_name, round(sample_size*100/nvl(decode(num_rows,0,100000,num_rows),1000000),2) proch,last_analyzed FROM ALL_IND_STATISTICS D Where owner='HIST' and table_name = 'AGREEMENT';
Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.
Блокировка статистики execute dbms_stats.lock_table_stats('имя схемы','имя таблицы'); Разблокировка execute dbms_stats.unlock_table_stats('имя схемы','имя таблицы'); Просмотр наличия блокировки статистики  SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED FROM ALL_tab_STATISTICS D WHERE OWNER='ИМЯ СХЕМЫ' AND TABLE_NAME='ИМЯ ТАБЛИЦЫ';
Замечание. При хорошем значении статистики по таблице может быть неблагополучная статистика по какому-то индексу таблицы, в силу чего целесообразно отслеживать статистику не только таблицы, но и индексов таблицы.
Проблемы с индексами
Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть: 
— Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс. 
— Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
Построен эффективный индекс, но он работает не эффективно в силу следующих причин: 
— Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д. 
Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс. 
— Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше. 
— Имеется хинт, блокирующий работу индекса, например NO_INDEX. 
— Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
Имеются сильные индексы, но они соперничают между собой. 
Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR. 
По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу: 


Select I.OWNER,T.TABLE_NAME, I.INDEX_NAME, T.BLOCKS, I.CLUSTERING_FACTOR, I.NUM_ROWS from ALL_INDEXES I, ALL_TABLES T where I.table_name=T.table_name and I.owner=T.owner and I.owner='имя схемы' and I.index_name='имя индекса';
Фактор кластеризации для индекса считает во время сбора статистики. Он используется оптимизатором при расчете стоимости индексного доступа к данным таблицы. Большой ФК (особенно близкий к числу строк в таблице) говорит о неэффективном индексе. Таким образом, ФК является характеристикой индекса, а не таблицы. Первое решение при большом ФК является убрать существующий индекс как не эффективный. Второе решение, если данный индекс наиболее часто применяется в запросах и он нужен, то перестроить структуру таблицы таким образом, чтобы строки в блоках таблицы были упорядочены в том же порядке, в котором расположена информация по данным строкам в индексе, т.е. сделать кластерными блоки таблицы, уменьшив таким образом число перемещений от одного блока к другому при работе индекса.
Проблемы с хинтами в запросе
Проблемы с хинтами могут быть следующие:

Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов: 
— хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе; 
— в хинте USE_NL содержится не полный перечень алиасов; 
— в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы. 
— хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
При создании хинта в запросе есть ряд рекомендаций: 
— В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса. 
— При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select). 
— Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы


Select /*+ parallel(32) Index_FFS (T имя_индекса) */ count(*) From имя_таблицы Partition (имя_партиции) T; 
Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.
Неэффективно написанный запрос.
Причин неэффективности запроса несколько:

неэффективное соединение таблиц;
использование NOT и NOT IN в условии where;
блокировка индекса в силу использования неправильных функций к столбцу, по которому построен индекс;
большая вложенность запроса или большая его длина;
большой объем выбираемых данных, требующих подключения в работу дисков, в том числе для выполнения агрегированных функций (order by, group by и т.д.);

неэффективные хранимые процедуры, используемые в запросе и др.


Среди причин неэффективности особое внимание следует уделить неэффективному соединению таблиц (наличие HASH или MERGE соединений там, где предпочтительнее NESTED LOOP — о чем сказано выше). Кроме того эффективность соединения может зависеть от порядка таблиц во фразе FROM. Чтобы оптимизатор работал с таблицами в том порядке, в каком они находятся во фразе From используется хинт Ordered.
Эффективность соединения зависит от полноты связи во фразе WHERE между таблицами. При недостаточной связке в плане выполнения появляется MERGE JOIN CAPTESIAN (о чем было сказано выше). Особое внимание при модификации запроса следует уделить фразе NOT IN в условии where. Как вариант освобождения от NOT IN можно использовать прием, при котором пишется первый запрос без NOT IN, а за ним после MINUS пишется тот же запрос с IN (вычитание из полного числа строк строки, получаемые после использования условия IN, который работает быстрее, чем NOT IN).
В целях ускорения работы запроса использовать (там, где это можно) вместо UNION фразу UNION ALL (UNION операция более медленная, т.к. осуществляется путем сортировки).
Рекомендуется уменьшать число таблиц во фразе FROM. Это позволит сделать план выполнения прозрачным для оптимизатора и его анализа. В первую очередь убрать из FROM таблицы, столбцы которых не используются после фразы Select. В этом случае можно использовать подзапросы с этими таблицами после Select или во фразе where. Задание диапазона дат, начиная с 01.01.0001, приводит к неэффективному плану выполнения. Надо сделать минимальную границу даты, т.е. как можно ближе к реальной дате.
В целях повышения производительности запроса не делать длинные запросы, т.к. длинный запрос увеличивает время разбора запроса оптимизатором, время передачи по каналам и занимает избыточную память.
В целях повышения производительности работы запроса шире использовать кэширование всех видов: последовательностей, таблиц, результатов выполнения запросов. Кэширование результатов выполнения запросов появилось в Oracle 11g и позволяет извлекать результат первого выполнения запроса из оперативной памяти. Это особенно эффективно при большом числе выполнения запроса и отсутствие в момент многократного выполнения запроса операций DML над таблицей.

Оптимизация SQL-запросов


12.12.2017

2142
Рейтинг: 5 . Проголосовало: 4

Вы проголосовали:

Для голосования нужно авторизироваться
  
Все мы знаем простую формулу: быстрый сайт = счастливые пользователи, высокая оценка Google-статистики и увеличение количества переходов. Возможно, Вам и кажется, что Ваш сайт работает настолько быстро, насколько ему позволяет быть быстрым WordPress – Вы же видели те списки лучших практик настройки сервера, улучшения производительности кода и так далее – но всё же, разве это предел?
Используя динамические базы данных сервисов типа WordPress, в конечном итоге перед Вами предстанет проблема: запросы к базе замедляют работу сайта.
В рамках этого поста я постараюсь научить Вас определять проблемные места, понимать их причину и предложу некоторые быстрые решения и другие программные подходы для ускорения работы. В качестве примера мы будем использовать действующие примеры работы с базой, замеченные на одном из сайтов. 
Идентификация
Первый шаг на пути повышения производительности - это обнаружение проблемных SQL-запросов. И на сцену выходит Query Monitor – по-настоящему незаменимый плагин для мониторинга работы запросов. Особенность его в том, что плагин показывает детальную информацию о каждом запросе, позволяя к тому же отфильтровать их, исходя из порождающего участка кода (включается подсветка дублированных запросов).
  

Если же по какой-то причине Вы не желаете устанавливать отладочный плагин, вы можете в качестве опции включить MySQL Slow Query Log, логирующий все запросы, требующее длительное время для выполнения. К тому же конфигурация и настройка места логирования относительно проста. Так как это серверное дополнение, падение производительности будет заметно в меньшей мере, чем если бы вместе с отладочным плагином непосредственно на сайте. Однако по мере отсутствия необходимости, MySQL Slow Query Log стоит отключать. 
Понимание
Как только вы находите затратный по времени запрос, который было бы неплохо улучшить, встает следующий вопрос: почему он работает медленно? К примеру, во время разработки одного сайта был обнаружен запрос, работающий целых 8 секунд!

Для поддержки магазина плагинов мы использовали WooCommerce и кастомизированную версию WooCommerce Software Subscriptions плагина. Задача приведенного выше запроса была получить все подписки пользователя, оперируя его идентификационным номером. WooCommerce обладает своеобразной комплексной моделью данных, при использовании которой наблюдалось следующее: хотя запрос и хранится как пользовательские пост-типы, идентификатор пользователя хранится в пост-мета данных, а не в post_author, как должно было бы быть. Также было найдено несколько связок с пользовательскими таблицами, созданными плагином. Итак, как же нам уберечь себя от подобных ошибок? 
Тема связана со специальностями:
MySQL – Ваш друг
MySQL обладает весьма полезным выражением DESCRIBE, которое используется для вывода информации о структуре таблицы (такой, как столбцы, типы данных, дефолты). К примеру, если Вы выполните команду DESCRIBE wp_postmeta, Вы увидите следующий результат:

Это, конечно, хорошо, но, возможно, Вы уже знали об этом. Но знали ли Вы, что DESCRIBE также может быть использован в качестве префикса для SELECT, INSERT, UPDATE, REPLACE и DELETE? Широкой общественности это так же известно, как синоним EXPLAIN, который дает нам детальную информацию, как именно та или иная команда будет выполнена.
Вот результат для нашего проблемного запроса:

На первый взгляд, понять это не слишком-то просто. Однако при более детальной расшифровке задача упрощается.
Наиболее важной колонкой считается type, так как она описывает связь между таблицами. Если вы замечаете слово ALL, это значит, что MySQL читает всю таблицу с диска, нагружая центральный процессор. Сие также известно под термином «полное сканирование таблицы» - позже мы вернемся к этому.
Колонка row также хороший индикатор того, что нужно, собственно говоря, MySQL сделать. Эта колонка наглядно демонстрирует, сколько рядов было прочитано, дабы найти результат.
Ключевое слово EXPLAIN также дает более полную информацию, полезную для дальнейшей оптимизации запросов. К примеру, таблица pm2 (wp_postmeta) говорит нам о том, что мы используем Using filesort, так как нам нужно отсортировать результаты по возрастанию (ORDER BY). 
Визуальное изучение
Здесь стоит упомянуть о таком полезном инструменте, как MySQL Workbench. Так как для баз данных, работающих на версии MySQL 5.6 и выше, результаты EXPLAIN формируются в виде JSON, MySQL Workbench конвертирует JSON в визуальное представление.
Утилита автоматически обращает Ваше внимание на проблемные запросы. На картинке мы ясно можем видеть, что wp_woocommerce_software_licences вызывает негодование. 
Решение
Упомянутая часть запроса проводит полное сканирование таблицы, чего Вам, собственно говоря, по мере возможности стоит избегать. К тому же тут используется неиндексированная колонка order_id в качестве ссылки между таблицами wp_woocommerce_software_licences и wp_posts. Подобная ситуация – общая причина большинства «медленных» запросов. Которая, однако, решается просто. 
Индексы
Order-id - достаточно важная часть идентификационной информации. Потому, если мы строим запрос подобным образом, мы должны иметь при себе индекс колонки. В противном случае MySQL в буквальном смысле просканирует ВСЕ рядки таблицы, пока не найдет нужный. Давайте добавим индекс и проверим, что из этого получится: 
Видео курсы по схожей тематике:


Платформа Managed Extensibility Framework (MEF)



Невероятно, но нам удалось урезать порядка 5 секунд выполнения запроса! 
Знайте свой запрос
Проверяйте запрос – команда за командой, подзапрос за подзапросом. Не делает ли он того, чего нам не нужно? Можем ли мы что-то оптимизировать?
В этом случае, ограничивая пост-типы shop_order, мы связываем таблицы лицензий с таблицами постов при помощи order_id. Повышая интеграцию данных, подобным образом мы убеждаемся, что используются только корректные порядки записей. Однако на самом деле это только внешняя часть запроса. Довольно опасно иметь строку software license в таблице, владеющей связанным с порядком WooCommerce order_id в пост-таблицах. Причина этому – связка с PHP-кодом плагина. Давайте удалим зависимость и посмотрим на результат:

Конечно, это не очень много, но теперь запрос работает до 3 секунд вместо 8! 
Кэшируйте все, что только можно!
Если на Вашем сервере не установлен MySQL query caching, тогда пора установить его. Это позволит MySQL вести запись всех исполняемых команд – совместно с их результатом. Кэш же, в свою очередь, не «устаревает», так как MySQL обновляет кэш при изменении таблиц.
Query Monitor обнаруживает, что наш запрос запускается 4 раза на одной странице. И, хотя query caching – полезная штука, дублирующие запросы должны быть удалены. Статическое кэширование в PHP – это достаточно простое и эффективное решение проблемы. По сути, Вы просто перехватываете результат чтения из базы и заносите его в статическое свойство класса. Когда будет вызван определенный подзапрос, вы просто используете уже загруженную информацию из статического свойства:

Кэш имеет такое понятие, как время жизни запроса. Если же Вам необходимо иметь постоянную информацию из кэша, тогда, возможно, Вам стоит воспользоваться постоянным Object Cache`ом. Однако в этом случае Ваш код должен сам описывать работу с кэшем. 
Отказывайтесь от шаблонного мышления!
Помимо этих, существует великое множество других техник по улучшению производительности запросов, которые потребуют операции посложнее, чем просто правка запроса или дописывание индекса. Одна из наиболее медленных операций нашего запроса – это необходимость связывать покупателя с таблицей товаров, причем для каждого покупателя. А что, если просто один раз связать все, что нам нужно, и не заморачиваться этим в дальнейшем, просто загружая из таблицы всю информацию, связанную с покупателем?
Мы можем создать таблицу, которая будет содержать в себе информацию о лицензии, вместе с идентификаторами пользователей и товаров для всех лицензий. При необходимости нам нужно будет только дать запрос для одного пользователя. Для этого Вам предстоит пересобрать таблицу при помощи MySQL triggers на базе INSERT/UPDATE/DELETE. Однако подобная операция заметно увеличит производительность системы в целом.
Подобным образом, в случае если количество связей замедляет выполнение запросов, возможно, Вам стоит разбить запрос в два или больше предложений и исполнять их раздельно при помощи PHP. После чего просто собрать и отфильтровать полученную информацию внутри кода. Что-то подобное реализовано в Laravel при помощи eager loading в Eloquent.
Бесплатные вебинары по схожей тематике:

10 ключевых ошибок во время собеседования

Как стать Full-Stack разработчиком

Интерактивный вебинар. Soft Skills на интервью и на испытательном сроке
WordPress иногда может замедлять запросы на wp_posts таблице, в случае если у Вас хранится много информации и разных пользовательских пост-типов. Если Вы считаете, что пост-типы замедляют запросы, возможно, Вам стоит задуматься об использовании кастомных таблиц. 
Подведем итоги
При помощи подобных нехитрых приемов оптимизации запросов нам удалось снизить время работы запроса с 8 до примерно 2 секунд. К тому же было снижено количество вызовов запрос с 4 до 1.
Искренне надеемся, что сей гайд будет полезным для работы с запросами. Внешне оптимизация может казаться чем-то устрашающим, но как только Вы попробуете это сделать, Ваши маленькие победы развеют подобные суждения.
Автор перевода: Евгений Лукашук


Похожие публикации:

Как зайти на сайт интел
Как работает push js
Как создать новый проект в фигма
Порт 137 для чего используется