Тарифы 

Советы построения sql запросов oracle. Оптимизация SQL-запросов

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

1. Ни каких подзапросов, только JOIN
Как я уже писал ранее , если выборка 1 к 1 или надо что-то просуммировать, то ни каких подзапросов, только join.
Стоит заметить, что в большинстве случаев оптимизатор сможет развернуть подзапрос в join, но это может случиться не всегда.

2. Выбор IN или EXISTS ?
На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
Я дам только несколько советов:
* Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN . Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
* Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS . В этом случае сложный запрос выполнится не так часто.
* Если и там и там сложно, то это повод изменить логику на джойны.

3. Не забывайте про индексы
Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

4. По возможности не используйте OR.
Проведите тесты, возможно UNION выглядит не так элегантно, за то запрос может выполнится значительно быстрей. Причина в том, что в случае OR индексы почти не используются в join.

5. По возможности не используйте WITH в oracle.
Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз (с хинтом materialize) в основной выборке или если число строк в подзапросе не значительно.
Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

6. Не делайте километровых запросов
Часто в web обратная проблема - это много мелких запросов в цикле и их советуют объединить в один большой. Но тут есть свои ограничения, если у вас запрос множество раз обернутый в from, то внутреннюю(ие) части надо вынести в отдельную выборку, заполнить временную таблицу, навесить индексы, а потом использовать ее в основной выборке. Скорость работы будет значительно выше (в первую очередь из-за сложности построения оптимального плана на большом числе сочетаний таблиц)

7. Используйте KEEP взамен корреляционных подзапросов.
В ORACLE есть очень полезные аналитические функции , которые упростят ваши запросы. Один из них - это KEEP.
KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
SELECT n.ID, MIN(c.ID) KEEP (DENSE_RANK FIRST ORDER BY c.date ASC) as cnt_id FROM nmcl n, cnt c WHERE n.cnt_id = c.id GROUP BY n.ID При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка - это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр - это на сколько строк сместиться, третьи - параметр по-умолчанию, если данные соседа не нашлись. SELECT deptno, empno, sal, LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY date) NEXT_LOWER_SAL, LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY date) PREV_HIGHER_SAL FROM emp; ORDER BY deptno, date DESC; При обычном подходе бы пришлось это делать через логику приложения.

9. Direct Path Read
Установка этой настройки (настройкой или параллельным запросом) - чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

10. Direct IO
Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
* В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
* В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

Оптимизация неэффективного SQL, несомненно, важнейшее средство повышения производительности в арсенале аналитика по производи­тельности Oracle. Если вызов базы данных порождает более десятка вызовов LIO для каждой строки, возвращаемой из каждой таблицы фразы FROM соответствующей команды SQL, то, скорее всего, произво­дительность такой команды можно повысить. Например, операция со­единения трех таблиц, возвращающая две строки, вероятно, должна требовать не более 60 вызовов LIO.

Любая относительная оценка при определенных условиях мо­жет оказаться неверной. Одним из таких условий является фор­мирование результата запроса как итога агрегирования. Напри­мер, запрос, возвращающий сумму (одну строку) для таблицы из миллиона строк, вполне законно может требовать выполне­ния более десяти вызовов LIO.

Приложения, выполняющие код SQL, порождающий множество вызо­вов LIO, создает серьезные препятствия для масштабируемости систем с большим количеством пользователей. Лишние вызовы LIO не только отнимают мощность процессора, но и могут вызвать большое количе­ство событий latch free для защелок cache buffers chains . Получение и освобождение защелок само по себе может при­вести к избыточному потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для _SPIN_COUNT по умолчанию (обычно не стоит этого делать).

В настоящее время есть ряд полезных ресурсов по оптимизации SQL: .1 Участники разнообразных списков рассылки, например Oracle-L (http://www.cybcon.com/~jkstill), также делают замечательное дело, помогая друг другу писать эффективный код. Все эти ресурсы содер­жат хорошие советы о том, как писать эффективный SQL, применяя методы, часть из которых перечислена ниже:

Диагностика выполнения команд SQL посредством таких инстру­ментов, как tkprof, EXPLAIN PLAN, и отладочных событий 10032, 10033,10046,10079,10104 и 10241.

Диагностика поведения оптимизатора запросов Oracle при помощи отладочного события, подобного 10053.

Работа с текстом SQL, направленная на использование более эффек­тивных планов выполнения.

Выбор эффективной стратегии индексирования с тем, чтобы обес­печить сокращение объема данных для запросов без создания до­полнительной нагрузки в операциях INSERT, UPDATE, MERGE и DELETE.

Применение хранимых планов выполнения с целью заставить оп­тимизатор запросов Oracle использовать выбранный вами план.

Создание приемлемой статистики для таблиц, индексов и базы дан­ных с тем, чтобы наилучшим образом информировать оптимизатор запросов Oracle о ваших данных.

Разработка таких физических моделей данных, которые облегчают хранение и выборку в контексте приложения.

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

Оптимизация разбора

Избыточный разбор - это верный путь к невозможности обеспечить масштабируемость приложения для работы с большим количеством пользователей . Студенты обычно приходят к нам, считая, что полные разборы (hard parses) значительно замедля­ют обработку транзакций, а вот в частичном разборе (soft parse) нет ничего страшного. Более того, иногда люди считают, что полного раз­бора можно избежать, а частичного - нет. Оба мнения верны лишь на­половину. Полные разборы действительно так ужасны, как о них ду­мают, и их можно избежать, используя в коде SQL переменные связы­вания вместо литералов. Однако частичный разбор столь же ужасен, и часто без него тоже можно обойтись.

Многие авторы употребляют словосочетание «частичный разбор» (soft parse) как синоним для «вызова разбора» (parse call). Мне больше нра­вится термин «вызов разбора», т. к. он обращает наше внимание на приложение, внутри которого в действительности может быть пред­принято спасительное действие. Если же говорить о «частичном разбо­ре», то внимание акцентируется на базе данных, которая не является местом решения нашей проблемы. И вот почему. Каждый раз, когда серверный процесс Oracle получает вызов разбора от приложения, это­му процессу необходимо использовать процессор сервера базы данных. Если будет обнаружен подходящий для этого запроса разделяемый курсор в кэше курсоров сеанса или библиотечном кэше Oracle, то вызов разбора никогда не приведет к полному разбору, и окажется, что затра­ты на разбор совсем не так велики, как могли бы быть. Однако отсут­ствие разбора обходится еще дешевле, чем частичный разбор. Наилуч­шую масштабируемость для большого количества пользователей име­ют приложения, в которых разбор происходит как можно реже. Следу­ет по возможности избавиться от всех ненужных вызовов разбора.

Для обеспечения масштабируемости лучше всего, чтобы прило­жения осуществляли минимально возможное количество вызовов базы данных. Именно в этом направлении развивается Oracle Call Interface (OCI). Например, в версии 8 OCI предприняты меры для снижения количества пересылок между клиентом и сервером (http://otn.oracle.com/tech/oci/htdocs/Developing_apps.html). Версия 9.2 OCI идет еще дальше, делая так, что многие вызовы базы данных приложения вообще не достигают базы дан ных (http://otn.oracle.com/tech/oci/htdocs/oci9ir2_new_features).

В системах с высокой конкурентностью и неоправданно многочислен­ными вызовами разбора большое количество событий CPU service зачас­тую коррелирует с большим количеством событий ожидания latch free для библиотечного кэша, разделяемого пула и других защелок. Само по себе получение и освобождение защелок может привести к избыточно­му потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для SPINCOUNT по умолчанию (опять-таки, обычно не стоит этого делать). Более того, избыточные вызовы разбора могут привести к ненужным задержкам SQL*Net message from client, способным добавить до нескольких секунд лишнего време­ ни отклика на каждую секунду реальной работы, выполняемой в базе данных. Наконец, вызовы разбора для длинных фрагментов SQL созда­ют ненужные задержки SQL*Net more data from client, которые также мо­гут внести существенный вклад в увеличение времени отклика.

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

Старайтесь обходиться без строковых литералов в инструкциях WHERE. Заменяйте их переменными связывания (заполнителями), особенно когда строковый литерал имеет высокую кардинальность (т. е. строка может иметь множество значений). Использование строковых литералов вместо переменных связывания приводит к затратам процессорного времени (CPU service), а также вызывает в системах с высокой конкурентностью ненужные события ожида­ния освобождения защелок для библиотечного кэша, разделяемого пула и объектов кэша строк.

Пример 11.2. Разбор внутри цикла серьезно препятствует масштабируемости

Отключите те функции интерфейсного драйвера, которые приводят к увеличению количества вызовов разбора по отношению к их чис­лу в исходном коде приложения. Например, Perl DBI содержит ат­рибут уровня prepare под названием ora_check_sql. Его значение по умолчанию равно 1, что означает два вызова разбора для каждого вызова функции Perl prepare. Первый вызов разбора выполняется с тем, чтобы помочь SQL-разработчику приложения быстрее отла­дить свой исходный код за счет предоставления более подробной диагностической информации для неудачных вызовов разбора. Од­ нако в промышленных системах такую функцию следует отключить, т. к. она приводит к излишним вызовам разбора.

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

Нередки случаи, когда используются генераторы скриптов и программного кода для доступа к данным. Авторы программ надеются на то, что современные технологические новинки сами выполнят за вас всю работу. В результате нередки случаи, когда несколько месяцев спустя после внедрения программы, пользователи начинают жаловаться, что программа «еле шевелится». Начинается всеобщая паника с привлечением дорогостоящих специалистов, которые смогут найти “бутылочное горлышко” тормозящее программу и спасти проект.

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

Существует ряд программ позволяющих автоматизировать и упростить эту задачу. Данный материал ориентирован на работу с сервером Oracle , но и для других баз данных есть аналогичные средства анализа и оптимизации «тюнинга». Первым нашим помощником станет программа мониторинга работы сервера Oracle с названием « Spotlight on Oracle » фирмы Quest software (http://www.quest.com). Это очень мощный инструмент, предназначенный для контроля функционирования вашего сервера. Данная программа выполнена в необычной цветовой палитре, что резко выделяет ее от других продуктов. После запуска данной программы необходимо создать учетную запись пользователя для чего потребуется учетная запись SYS или запись с системными привилегиями DBA. Помощник создания новой учетной записи вызывается из меню “ File > User Wizard ”.

После создания учетной записи пользователя и соединением с сервером Oracle нам представляется визуальная картинка, которая отображает компоненты и процессы работы сервера. Если один, или несколько компонентов сервера работает не оптимально или с перегрузкой, то его цвет изменяется от зеленого до красного, в зависимости от степени перегрузки. Возможен мониторинг сразу нескольких серверов, список которых отображается в левой панели и так же меняет цвет. Иконка в панели задач также меняет цвет синхронно с программой, что позволяет оперативно реагировать при “свернутом” в приложении. Пример мониторинга показан на рисунке 1.

Очень полезной особенностью данной программы является система авто-рекомендаций решения проблем. Достаточно кликнуть мышкой по красному участку изображения, чтобы получить развернутое описание проблемы и возможные методы ее устранения. Если же все нормально, то благодаря данной программе можно подстроить параметры запуска сервера для уменьшения используемых им системных ресурсов. Например, по рисунку 1 можно сделать вывод, что размер табличного пространства файла базы данных можно смело уменьшить в два раза, и желательно выделить дополнительную память под “ Shared Pool ”.

Но это все проблемы администратора базы данных. Разработчиков же больше волнует, как работают их творения и сколько ресурсов «кушают» запросы к базе данных. Для этого вызываем пункт меню “ Navigator > Top Sessions ”. После заполнения параметров фильтра отбора данных нам будет показан список текущих запросов к серверу базы данных. Предварительно отсортировав запросы по требованиям к ресурсам, можно выделить самые “прожорливые”. В этом же окне можно посмотреть план выполнения запроса, пример которого показан на рисунке 2. Причем план запросов можно представить в виде графа, дерева или словесного описания. Здесь так же используется цветовая маркировка проблемных участков.

После выявления проблемных SQL запросов настал черед их оптимизации. Для автоматизации этого процесса воспользуемся программой SQL Expert фирмы LECCO (http://www.leccotech.com). Вызываем окно SQL редактора и добавляем в него скрипт запроса. Здесь так же можно посмотреть план выполнения запроса. Но нас больше всего интересует функция меню “SQL-> Optimize ”, которая генерирует список альтернативных вариантов построения заданного SQL скрипта. А функция “SQL-> Butch Run ” позволяет проанализировать время выполнения всех запросов на “живых” данных и вывести результирующую таблицу, которую можно отсортировать по требуемому параметру. Выбрав наиболее оптимальный запрос, его можно сравнить с оригиналом и принять решение о возможности дальнейшего его использования в своем приложении. Пример работы по оптимизации запроса показан на рисунке 3.

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

От автора: вы знаете, что быстрый сайт == счастливые пользователи, выше рейтинг в google и повышенная конверсия. Возможно, вы думаете, что ваш сайт на WordPress максимально быстрый: вы смотрите за производительностью по статьям «лучшие практики настройки сервера», «решение проблем с медленным кодом» и «перемещение изображений на CDN». Но все ли это?

В динамических сайтах типа WordPress с базой данных у вас будет одна и та же проблема – запросы к базе данных замедляют сайт. В этой статье я покажу вам, как делается оптимизация SQL запросов, вызывающих замедления, и как понять, в чем проблема в этих запросах. Я буду использовать реальный запрос, который мы недавно правили, так как он замедлял портал покупателя deliciousbrains.com.

Определение

Первый шаг в решении медленных SQL запросов – найти их. Ashley восхваляла плагин отладки Query Monitor в блоге. Бесценным этот инструмент делает функция плагина запросов к базе данных для определения медленных SQL запросов. Вы можете фильтровать запросы по коду или компоненту (плагин, тема или ядро WordPress), которые их совершают. А также плагин умеет выделять повторяющиеся и медленные запросы:

Если не хотите ставить плагин отладки на продакшн сайт (может, вас волнует производительность), можете запустить MySQL Slow Query Log , который логирует все запросы, занимающие определенное время на выполнение. Инструмент очень легко настроить и указать папку для логирования запросов. Так как это серверное решение, то удар на производительность будет меньше, чем плагин отладки. Однако когда он не нужен, его нужно отключить.

Понятие

MySQL

SELECT l.key_id, l.order_id, l.activation_email, l.licence_key, l.software_product_id, l.software_version, l.activations_limit, l.created, l.renewal_type, l.renewal_id, l.exempt_domain, s.next_payment_date, s.status, pm2.post_id AS "product_id", pm.meta_value AS "user_id" FROM oiz6q8a_woocommerce_software_licences l INNER JOIN oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id INNER JOIN oiz6q8a_posts p ON p.ID = l.order_id INNER JOIN oiz6q8a_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = "_customer_user" INNER JOIN oiz6q8a_postmeta pm2 ON pm2.meta_key = "_software_product_id" AND pm2.meta_value = l.software_product_id WHERE p.post_type = "shop_order" AND pm.meta_value = 279 ORDER BY s.next_payment_date

SELECT

l.activation_email,

l.software_product_id,

l.software_version,

l.activations_limit,

l.exempt_domain,

s.next_payment_date,

s.status ,

pm2.post_idAS "product_id" ,

pm.meta_valueAS "user_id"

FROM

oiz6q8a_woocommerce_software_licencesl

INNER JOIN

oiz6q8a_woocommerce_software_subscriptionssON s.key_id=l.key_id

INNER JOIN

oiz6q8a_postspON p.ID=l.order_id

INNER JOIN

oiz6q8a_postmetapmON pm.post_id=p.ID

AND pm.meta_key="_customer_user"

INNER JOIN

oiz6q8a_postmetapm2ON pm2.meta_key="_software_product_id"

AND pm2.meta_value=l.software_product_id

p.post_type="shop_order"

AND pm.meta_value=279

ORDER BY s.next_payment_date

Для запуска магазина плагинов мы использовали WooCommerce и плагин WooCommerce Software Subscriptions. Суть запроса – получение всех подписок покупателя по его номеру. В WooCommerce довольно сложная модель данных. Хотя заказ и хранится в виде кастомного типа поста, но id покупателя не хранится в post_author, а является частью post мета данных. Также есть несколько подключений к пользовательским таблицам, созданным плагином подписки на ПО. Давайте более подробно разберем запрос.

MySQL твой друг

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

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

Результат для медленного запроса:

На первый взгляд разобраться сложно. К счастью, ребята с SitePoint собрали полное руководство к пониманию выражения .

Самая главная колонка type, в ней описывается способ объединения таблиц. Если стоит ALL, значит MySQL читает всю таблицу с диска, увеличивает скорость чтения/записи и перекладывает загрузку на CPU. Процесс называется полное сканирование таблицы (более подробно позже).

Колонка rows также указывает на то, что 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 будет буквально сканировать все строки таблицы, пока не найдет необходимые. Добавим индекс и посмотрим, что он даст:

MySQL

CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id)

CREATE INDEX order_idON wp_woocommerce_software_licences(order_id)

Нам удалось сэкономить 5 секунд запроса простым добавлением индекса, круто!

Знайте свой запрос

Проанализируйте запрос – join за join, подзапрос за подзапросом. Запрос делает то, что не должен? Его можно оптимизировать?

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

Несильно, но запрос теперь выполняется меньше 3 секунд.

Кэшируйте все!

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

Query Monitor выяснил, что наш запрос запускается 4 раза за одну загрузку страницы. Хотя и хорошо кэшировать MySQL запросы, но дублировать считывания из базы данных в одном запросе точно не стоит. Статическое кэширование в коде PHP – простой и крайне эффективный способ решения проблемы. Вы получаете результат запроса из базы данных при первом запросе и храните результат в статическом свойстве класса. В последующих вызовах будет возвращаться результат из статического свойства:

class WC_Software_Subscription { protected static $subscriptions = array(); public static function get_user_subscriptions($user_id) { if (isset(static::$subscriptions[ $user_id ])) { return static::$subscriptions[ $user_id ]; } global $wpdb; $sql = "..."; $results = $wpdb->get_results($sql, ARRAY_A); static::$subscriptions[ $user_id ] = $results; return $results; } }

class WC_Software_Subscription {

protected static $ subscriptions = array () ;

public static function get_user_subscriptions ($ user _ id) {

$ user _ id] = $ results ;

return $ results ;

Кэш имеет время жизни запроса, более точно – время жизни объекта. Если вы ищите постоянные результаты запроса в других запросах, вам нужно реализовать постоянный Object Cache. Ваш код должен уметь включать кэш и устаревать кэш при изменении даты.

Мыслите нестандартно

Можно предпринять и другие способы для ускорения выполнения запросов, в которых нужно чуть больше, чем просто поправить запрос или добавить индекс. Одна из самых медленных частей нашего запроса – процесс объединения таблиц для перехода от id покупателя к id товара, и это необходимо делать для каждого покупателя. А что если сделать все объединения за раз, чтобы получать данные о покупателе тогда, когда это необходимо?

Вы можете денормализовать данные, создав таблицы, в которой хранятся данные лицензии, а также id пользователя и товара для всех лицензий, а также запрос к конкретному покупателю. Понадобится пересобрать таблицу с помощью MySQL triggers на INSERT/UPDATE/DELETE для таблицы лицензий (или других в зависимости от изменения данных), но это значительно повысит производительность запроса данных.

Если несколько join замедляют запрос, можно ускорить его, разбив на 2 и более выражения, после чего выполнять их отдельно в PHP, собирать и фильтровать результаты в коде. Laravel делает что-то похожее в жадной загрузке в Eloquent.

WordPress может замедлять запросы к таблице wp_posts, если объем данных большой, и присутствует множество кастомных типов постов. Если запросы типов постов замедляют сайт, попробуйте уйти от модели хранения кастомных типов постов в сторону кастомной таблицы.

Результаты

С помощью этих подходов по оптимизации запросов нам удалось ускорить наш запрос с 8 до 2 секунд, а также снизить количество вызовов с 4 до 1. Время запросов записывалось на версии для разработки, в продакшн оно было бы меньше.

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

Пять простых оптимизаций, которые можно реализовать на основе одних лишь метаданных (т. е. ограничений) и самого запроса Предлагаем вам адаптацию статьи Лукаса Эдера, рассчитанную на тех, кто имеет общее представление о базах данных и SQL, а также небольшой практический опыт работы с СУБД. Стоимостная оптимизация – фактически стандартный способ оптимизации SQL-запросов в современных базах данных. Именно поэтому настолько сложно написать вручную сложный алгоритм на 3GL (языках программирования третьего поколения) , производительность которого превышала бы динамически рассчитываемый план выполнения, сгенерированный современным оптимизатором. Сегодня мы не будем обсуждать стоимостную оптимизацию, то есть оптимизацию на основе стоимостной модели базы данных. Мы рассмотрим гораздо более простые оптимизации. Те,которые можно реализовать на основе одних лишь метаданных (т. е. ограничений) и самого запроса. Обычно их реализация для базы данных – не бином Ньютона, поскольку, в данном случае, любая оптимизация приведет к лучшему плану выполнения, независимо от наличия индексов, объемов данных и асимметрии распределения данных. "Не бином Ньютона" не в смысле легкости реализации оптимизации, а в том, следует ли это делать. Эти оптимизации устраняют [для базы данных] ненужную, дополнительную работу ().

Для чего эти оптимизации применяются?

Большинство из них применяется для:
  • исправления ошибок в запросах;
  • обеспечения повторного использования представлений без фактического выполнения логики представления базой данных.
В первом случае, можно было бы заявить: "Ну и что, просто возьми, и исправь этот дурацкий SQL-запрос". Но пусть первым бросит в меня камень тот, кому не доводилось ошибаться. Второй случай особенно интересен: это дает нам возможность создания сложных библиотек представлений и табличных функций, допускающих многократное использование в нескольких слоях.

Используемые базы данных

В этой статье мы будет сравнивать 10 SQL-оптимизаций в пяти наиболее широко используемых СУБД (согласно рейтингу баз данных):
  • Oracle 12.2;
  • MySQL 8.0.2;
  • SQL Server 2014;
  • PostgreSQL 9.6;
  • DB2 LUW 10.5.
Другой почти вторит ему. Как обычно, в этой статье я буду выполнять запросы к базе данных Sakila. Вот список этих десяти разновидностей оптимизаций:
  1. транзитивное замыкание;
  2. невозможные предикаты и ненужные обращения к таблицам;
  3. устранение JOIN;
  4. устранение "бессмысленных" предикатов;
  5. проекции в подзапросах EXISTS;
  6. cлияние предикатов;
  7. доказуемо пустые множества;
  8. oграничения CHECK;
  9. ненужные рефлексивные соединения;
  10. Pushdown предикатов
Сегодня мы обсудим пп. 1-3, во второй части - 4 и 5, а в части 3 – 6-10.

1. Транзитивное замыкание

Начнем с чего-нибудь попроще: транзитивного замыкания . Это тривиальное понятие, применимое ко множеству математических операций, например, оператору равенства. Его можно сформулировать в этом случае следующим образом: если A = B и B = C, то A = C.

Несложно, правда? Но это влечет некоторые интересные последствия для оптимизаторов SQL. Рассмотрим пример. Извлечем все фильмы с ACTOR_ID = 1: SELECT first_name, last_name, film_id FROM actor a JOIN film_actor fa ON a. actor_id = fa. actor_id WHERE a. actor_id = 1 ; Результат следующий: FIRST_NAME LAST_NAME FILM_ID PENELOPE GUINESS 1 PENELOPE GUINESS 23 PENELOPE GUINESS 25 PENELOPE GUINESS 106 PENELOPE GUINESS 140 PENELOPE GUINESS 166 . . . Взглянем теперь на план выполнения этого запроса в случае СУБД Oracle: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | Id | Operation | Name | Rows | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 19 | | 2 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | | * 3 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | | * 4 | INDEX RANGE SCAN | PK_FILM_ACTOR | 19 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Predicate Information (identified by operation id) : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 3 - access ("A" . "ACTOR_ID" = 1 ) 4 - access ("FA" . "ACTOR_ID" = 1 ) Особенно тут интересен раздел предикатов. Предикат ACTOR_ID = 1, вследствие транзитивного замыкания применяется как к таблице ACTOR, так и таблице FILM_ACTOR. Если: A. ACTOR_ID = 1 (из предиката WHERE) и… A. ACTOR_ID = FA. ACTOR_ID (из предиката ON) То: FA. ACTOR_ID = 1 В случае более сложных запросов это приводит к некоторым весьма приятным результатам. В частности, точность оценок кардинальности существенно повышается, так как появляется возможность подбора оценок на основе конкретного константного значения предиката, а не, например, среднего числа фильмов по актерам, как в следующем запросе (возвращающем такой же результат): SELECT first_name, last_name, film_id FROM actor a JOIN film_actor fa ON a. actor_id = fa. actor_id WHERE first_name = "PENELOPE" AND last_name = "GUINESS" Его план: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | Id | Operation | Name | Rows | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 2 | | * 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR | 1 | | * 3 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 3 | | * 4 | INDEX RANGE SCAN | PK_FILM_ACTOR | 27 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Predicate Information (identified by operation id) : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 2 - filter ("A" . "FIRST_NAME" = "PENELOPE" ) 3 - access ("A" . "LAST_NAME" = "GUINESS" ) 4 - access ("A" . "ACTOR_ID" = "FA" . "ACTOR_ID" ) Как вы можете видеть, оценка числа строк таблицы FILM_ACTOR завышена, а оценка для вложенных циклов (NESTED LOOP) занижена. Вот пару интересных значений: SELECT count (* ) FROM film_actor WHERE actor_id = 1 ; SELECT avg (c) FROM ( SELECT count (* ) c FROM film_actor GROUP BY actor_id ) ; Результат: 19 27.315 Отсюда и получаются оценки. Если база данных знает, что речь идет о ACTOR_ID = 1, то может собрать статистику по количеству фильмов для этого конкретного актёра . Если же не знает (поскольку стандартный механизм сбора статистики не соотносит FIRST_NAME/LAST_NAME с ACTOR_ID), то мы получим среднее число фильмов для всех актеров . Простая, несущественная ошибка в данном конкретном случае, но в сложном запросе она может распространяться дальше, накапливаться и приводить дальше в запросе (выше в плане) к неправильному выбору JOIN. Так что всегда, когда только можете, проектируйте свои соединения и простые предикаты так, что воспользоваться преимуществами транзитивного замыкания. Какие еще базы данных поддерживают эту возможность?

DB2

Да! Explain Plan -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | NLJOIN | 27 of 1 | 13 3 | FETCH ACTOR | 1 of 1 (100.00 % ) | 6 4 | IXSCAN PK_ACTOR | 1 of 200 ( .50 % ) | 0 5 | IXSCAN PK_FILM_ACTOR | 27 of 5462 ( .49 % ) | 6 Predicate Information 4 - START (Q2. ACTOR_ID = 1 ) STOP (Q2. ACTOR_ID = 1 ) 5 - START (1 = Q1. ACTOR_ID) STOP (1 = Q1. ACTOR_ID) Кстати, если вам нравятся крутые планы выполнения вроде этого, воспользуйтесь сценарием Маркуса Винанда (Markus Winand) .

MySQL

К сожалению, планы выполнения MySQL плохо подходят для подобного анализа. В выводимой информации отсутствует сам предикат: ID SELECT TYPE TABLE TYPE REF ROWS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1 SIMPLE a const const 1 1 SIMPLE fa ref const 19 Но тот факт, что в столбце REF два раза указано const показывает, что в обеих таблицах идет поиск по константному значению. В то же время, план запроса с FIRST_NAME / LAST_NAME выглядит следующим образом: ID SELECT TYPE TABLE TYPE REF ROWS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 1 SIMPLE a ref const 3 1 SIMPLE fa ref a. actor_id 27 И, как вы можете видеть, в REF теперь указана ссылка на столбец из предиката JOIN. Оценка кардинальности практически такая же, как в Oracle. Так что да, MySQL тоже поддерживает транзитивное замыкание.

PostgreSQL

Да! QUERY PLAN -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Nested Loop (cost= 4.49 . .40 .24 rows= 27 width= 15 ) - > Seq Scan on actor a (cost= 0.00 . .4 .50 rows= 1 width= 17 ) Filter: (actor_id = 1 ) - > Bitmap Heap Scan on film_actor fa (cost= 4.49 . .35 .47 rows= 27 width= 4 ) Recheck Cond: (actor_id = 1 ) - > Bitmap Index Scan on film_actor_pkey (cost= 0.00 . .4 .48 rows= 27 width= 0 ) Index Cond: (actor_id = 1 )

SQL Server

Да! | -- Nested Loops (Inner Join) | -- Nested Loops (Inner Join) | | -- Index Seek (SEEK: ([ a] . [ actor_id] = (1 ) ) ) | | -- RID Lookup | -- Index Seek (SEEK: ([ fa] . [ actor_id] = (1 ) ) )

Резюме

Все наши базы данных поддерживают транзитивное замыкание.
База данных Транзитивное замыкание
DB2 LUW 10.5 Да
MySQL 8.0.2 Да
Oracle 12.2.0.1 Да
PostgreSQL 9.6 Да
SQL Server 2014 Да
Однако дождитесь №6 в следующей части статьи. Существуют сложные случаи транзитивного замыкания, с которыми справляются не все базы данных.

2. Невозможные предикаты и ненужные обращения к таблицам

Эта совсем дурацкая оптимизация, но почему бы и нет? Если пользователи пишут невозможные предикаты, то зачем их вообще выполнять? Вот несколько примеров: -- "Очевидный" SELECT * FROM actor WHERE 1 = 0 -- "Хитрый" SELECT * FROM actor WHERE NULL = NULL Первый запрос, очевидно, никогда не вернет никаких результатов, но то же самое утверждение справедливо и относительно второго. Ведь хотя NULL IS NULL всегда TRUE, результат вычисления NULL = NULL равен NULL, что, согласно трёхзначной логике , эквивалентно FALSE. Это не требует особых пояснений, так что перейдем сразу к выяснению, какие из баз данных выполняют такую оптимизацию.

DB2

Да! Explain Plan -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - ID | Operation | Rows | Cost 1 | RETURN | | 0 2 | TBSCAN GENROW | 0 of 0 | 0 Как вы можете видеть, обращение к таблице ACTOR полностью исключено из плана. В нём присутствует только операция GENROW, генерирующая ноль строк. Идеально.

MySQL

Да! ID SELECT TYPE TABLE EXTRAS -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 1 SIMPLE Impossible WHERE На этот раз, MySQL был столь любезен, что сообщил нам о невозможном предложении WHERE. Спасибо! Это сильно облегчает анализ, особенно по сравнению с другими базами данных.

Oracle

Да! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | Id | Operation | Name | Starts | E- Rows | A- Rows | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | 0 | SELECT STATEMENT | | 1 | | 0 | | * 1 | FILTER | | 1 | | 0 | | 2 | TABLE ACCESS FULL| ACTOR | 0 | 200 | 0 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - Predicate Information (identified by operation id) : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 1 - filter (NULL IS NOT NULL) Видим, что в плане по-прежнему упоминается обращение к таблице ACTOR, причем ожидаемое число строк по-прежнему 200, но присутствует и операция фильтрации (FILTER) при Id=1, где никогда не будет TRUE. В силу нелюбви Oracle к стандартному булевому типу данных SQL , Oracle отображает в плане NULL IS NOT NULL, вместо простого FALSE. Ну что ж... Но если серьезно, следите за этим предикатом. Мне случалось отлаживать планы выполнения с поддеревьями в 1000 строк и крайне высокими значениями стоимости и лишь постфактум обнаруживать, что всё это поддерево "отсекалось" фильтром NULL IS NOT NULL. Немного обескураживающе, скажу я вам.

PostgreSQL

Да! QUERY PLAN -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - Result (cost= 0.00 . .0 .00 rows= 0 width= 228 ) One- Time Filter: false Уже лучше. Никакого надоедливого обращения к таблице ACTOR и маленький аккуратный предикат FALSE.

SQL Server?

Да! | -- Constant Scan SQL Server называет это "константным просмотром", то есть просмотром, при котором ничего не происходит – аналогично DB2. Все наши базы данных умеют исключать невозможные предикаты:

3. Устранение JOIN

В предыдущем разделе мы наблюдали ненужные обращения к таблицам в однотабличных запросах. Но что произойдет, если в JOIN не требуется одно из нескольких обращений к таблицам? Я уже писал про устранение JOIN в предыдущем посте из моего блога . SQL-движок способен определить, на основе вида запроса, а также наличия первичных и внешних ключей, действительно ли конкретный JOIN необходим в данном запросе, или его устранение не повлияет на семантику запроса. Во всех следующих трёх примерах, JOIN не нужен. Внутреннее соединение типа "...-к-одному" можно устранить при наличии не допускающего неопределенного значения (NOT NULL) внешнего ключа Вместо вот этого: SELECT first_name, last_name FROM customer c JOIN address a ON c. address_id = a. address_id База данных может выполнить следующее: SELECT first_name, last_name FROM customer c Внутреннее соединение (INNER JOIN) типа "...-к-одному" можно заменить при наличии допускающего неопределенного значения внешнего ключа. Вышеприведенный запрос работает, если на внешний ключ наложено ограничение NOT NULL. Если же нет, например, как в этом запросе: SELECT title FROM film f JOIN language l ON f. original_language_id = l. language_id то JOIN все равно можно устранить, но придется добавить предикат NOT NULL, вот так: SELECT title FROM film WHERE original_language_id IS NOT NULL Внешнее соединение (OUTER JOIN) типа "...-к-одному" можно убрать при наличии уникального ключа. Вместо вот этого: SELECT first_name, last_name FROM customer c LEFT JOIN address a ON c. address_id = a. address_id База данных, опять же, может выполнить следующее: SELECT first_name, last_name FROM customer c ... даже если внешнего ключа по CUSTOMER.ADDRESS_ID нет. Уникальное внешнее соединение (DISTINCT OUTER JOIN) типа "...-ко-многим" можно убрать. Вместо вот этого: SELECT DISTINCT first_name, last_name FROM actor a LEFT JOIN film_actor fa ON a. actor_id = fa. actor_id База данных может выполнить следующее: SELECT DISTINCT first_name, last_name FROM actor a Все эти примеры были подробно изучены в предыдущей статье, так что я не буду повторяться, а лишь подытожу всё, что могут устранять различные базы данных:
База данных INNER JOIN: ...-к-одному (может быть NULL): ...-к-одному OUTER JOIN: ...-к-одному OUTER JOIN DISTINCT: ...-ко-многим
DB2 LUW 10.5 Да Да Да Да
MySQL 8.0.2 Нет Нет Нет Нет
Oracle 12.2.0.1 Да Да Да Нет
PostgreSQL 9.6 Нет Нет Да Нет
SQL Server 2014 Да Нет Да Да
К сожалению, не все базы данных могут устранять все виды соединений. DB2 и SQL Server тут – безусловные лидеры!


Есть вопросы?

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Отправить