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

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

Методы оптимизации запросов к SQL Server — Советы для написания эффективных и быстрых запросов

Инновационный центр — Группа организаций Baba Farid

Автор перевода: Прищепа В.В.

Аннотация — SQL можно использовать для извлечения данных из любых баз данных. Чтобы получить один и тот же результат, можно написать различные SQL запросы. Для наилучшей производительности необходимо использовать лучшие, наиболее быстрее и эффективные запросы. Так что необходимо конфигурировать запросы на основании требований пользователей и решаемых задач. Эта статья раскрывает каким же образом SQL запросы могут быть оптимизированы для лучшей производительности. Оптимизация запросов тема очень глубокая, но мы будем стараться охватить наиболее важные моменты. В этой статье мы не будем сосредотачиваться на глубоком анализе базы данных, а обсудим простые советы по настройке запросов и приемы, которые могут быть применены, чтобы получить немедленный выигрыш в производительности.

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

II. ОБЗОР ВЫПОЛНЕНИЯ ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ СТАТИСТИКИ ЧТЕНИЯ/ЗАПИСИ

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

SET STATISTICS IO ON

Рассмотрим следующий запрос:

SELECT * FROM tablename

В окне результата SQL Server Management Studio вернулось следующее сообщение: «Table ‘tablename’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0«.

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

III. ОБЩИЕ РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ

Используйте конкретные имена столбцов вместо * в запросе SELECT

Запрос SQL, становится быстрее, если использовать имена столбцов в SELECT вместо ‘*’. Так что нам нужно ограничить запросы с выборкой всех столбцов, выбрав только определенные столбцы из таблицы. Это приводит к выигрышу общей производительности, уменьшению сетевого трафика.

SELECT col_1, col_2, col_3, col_4, subject FROM table_name;

SELECT * FROM table_name.

Используйте альтернативные методы для возврата общего количества строк таблицы вместо COUNT (*)

SELECT COUNT (*) делает полное сканирование таблицы, это может занять много времени для больших таблиц. Если нам нужно узнать количество строк таблицы, мы можем использовать альтернативный способ – системную таблицу sysindexes. В ней присутствует столбец ROWS, содержащий общее количество строк для каждой таблицы в системе. Таким образом, мы можем использовать следующий оператор выбора:

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID (‘table_name’) AND indid 10;

SELECT id, col1, col2 FROM table
WHERE col2 != 10.

Для сравнения строк:

SELECT id, col1, col2 FROM table
WHERE col1 LIKE ‘Nav%’;

SELECT id, col1, col2 FROM table
WHERE SUBSTR(col1,1,3) = ‘Nav’.

Для сравнения чисел в диапазоне:

SELECT Col1, Col2 FROM table
WHERE Col3 BETWEEN MAX (Col3) and MIN (Col3);

SELECT Col1, Col2 FROM table
WHERE Col3 >= MAX (Col3) and Col3 <= MIN (Col3).

Применяйте модифицированное значение колонки в инструкции WHERE только при необходимости.

SELECT id, Col1, Col2 FROM table
WHERE Col2 < 25000;

SELECT id, Col1, Col2 FROM table
WHERE Col2 + 10000 < 35000.

IV. ЕЩЕ НЕСКОЛЬКО СОВЕТОВ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ/ТАБЛИЦ/ХРАНИМЫХ ПРОЦЕДУР

• Таблица должна иметь минимум один кластеризованный индекс и соответствующее число не кластеризованных индексов;
• Избегайте использования триггеров, если это возможно. Включите логику триггера в хранимую процедуру;
• Таблица должна иметь ключевое поле;
• Старайтесь использовать переменные таблицы вместо временных. Переменные занимают меньше системных ресурсов и ресурсов логов;
• Избегайте использования VIEW, постарайтесь заменить их таблицами;
• Избегайте инструкции DISTINCT, используйте ее только если это действительно необходимо;
• Используйте TOP в иснтрукции SELECT, если необходимо выбрать некоторое количество строк в начале таблицы;
• Оформите повторяющийся код в пользовательскую процедуру. Это поможет улучшить производительность, ускорить вашу работу, уменьшить сетевой трафик;
• Использование TRUNCATE вместо DELETE позволит ускорить удаление строк из таблицы, потому что удаление происходит без записи информации в лог-файл;
• Избегайте использования курсоров, если это возможно, они сильно замедляют производительность;
• Когда разрабатывается запрос с подзапросами:
  • Используйте коррелированный подзапрос только тогда, когда возвращаемый результат будет относительно небольшим и/или другие критерии быстродействия подзапроса будут эффективными;
  • Используйте не коррелированные подзапросы при работе с большими таблицами, из которых ожидается большой результат и/или подзапрос имеет низкие показатели эффективности;
  • Убедитесь в том, что несколько подзапросов расположены в наиболее эффективном порядке;
  • Переписывание подзапроса с JOIN иногда может повысить эффективность;
• Для хранения символьных и строковых данных используйте char/varchar вместо nchar/nvarchar, если нет необходимости в использовании UNICODE. В первом случае для хранения символов используется один байт, во втором – два;
• Можно попытаться использовать инструкцию RETURN для возвращения целочисленного значения вместо того, чтобы это значение было частью результирующего набора данных;
• Очистите систему от неиспользуемых индексов, они занимают место на диске и замедляют операции DML;
• Создавайте индексы для целочисленных полей, это способствует меньшему объему индекса на диске, меньшему количеству операций чтения при использовании индекса;
• Если часто используется объединение одних и тех же таблиц, то стоит создать индекс для объединяемых столбцов.

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

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

Microsoft SQL Server. Работа с оптимизатором запросов (часть 1)

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

Забегая вперед, сразу сообщу об итогах исследования, для тех кому не важны технические подробности, а важны выводы. Оказывается, действительно можно сделать так, чтобы оптимизатор продолжал поиски «до упора», но вероятность, что он действительно найдет гораздо более удачный план невелика. Это логично, иначе, если бы оптимизатор очень часто «недооптимизировал» запросы, прекращая поиски раньше положенного, то следовало бы поменять механизм определения того самого момента, когда считается, что искать план дальше не имеет смысла. Между тем, оптимизатор довольно неплохо справляется со своей задачей, а когда не справляется, причина очень часто кроется не в самом оптимизаторе, а в том с чем ему приходится работать (неактуальная статистика, плохо написанный код и т.д.). Хотя, ради справедливости, стоит сказать, что бывают случаи, когда причина в самом оптимизаторе.

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

Теория

Основные понятия

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

Transformation Rule — правило преобразования. Это объект который содержит в себе методы по преобразованию одних логических операторов в другие логические (или физические) операторы.

Optimization Task — дословно, задача оптимизации, это операция предпринимаемая оптимизатором в процессе поиска плана. Это может быть например, применение правила преобразования к узлу дерева операторов.

Memo — структура в памяти сервера, которая используется для хранения и анализа получаемых в результате преобразований деревьев операторов.

Group — группа эквивалентности, часть структуры Memo, в которой хранятся эквивалентные выражения (операторы), например — Group 1: (A join B) , (B join A).

Group Expression — выражение в группе эквивалентности, например — Group 1: (A join B) , (B join A). (A join B) — одно из выражений группы Group 1.

Timeout — определенное количество задач оптимизации (Optimization Task), которое отводит себе оптимизатор перед тем, как начинает оптимизировать запрос («Я угадаю эту мелодию с 5 нот»!), т.е. некий бюджет на количество преобразований. По мере выполнения преобразований оптимизатор смотрит на этот счетчик, и как только потратил всё отведенное количество — прекращает оптимизацию и выдает тот план, который у него есть на данный момент. При этом, если в SSMS посмотреть на полученный план, выбрать корневой оператор SELECT и посмотреть свойства, то можно увидеть «Reason For Early Termination: Time Out».

Good Enough Plan — достаточно хороший план, это еще одно условие при котором оптимизация прекращается. Происходит это в том случае, если запас преобразований еще есть, но найденный на данном этапе план уже удовлетворяет внутреннему порогу оптимизатора. Это условие, также можно увидеть в свойствах плана в SSMS — «Reason For Early Termination: Good Enough Plan Found».

Алгоритм генерации альтернатив

Допустим есть запрос:

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

Дерево копируется в начальное Memo (Copy In):

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

Optimize Group

• На входе: группа, верхняя граница, требуемые свойства
• Сохранение лучшего плана в memo

Explore Group

• Итеративное исследование каждого выражения

Explore Expression

• Применение правил
• Генерирование альтернативных выражений
• Работа с memo, чтобы избежать повторов (e.g. JoinCommute)
• Битовая карта pattern memory определяет уже примененные правила

Apply Rule

• Предшественник – Потомок
• Привязка предшественников к правилам
• Применение правила
• Сохранение в memo (в том числе новых групп)
• Запуск следующего задания в зависимости от типа потомка
• Логический – Explore Expression
• Физический – Optimize Inputs

Optimize Inputs

• Подсчет наилучшего плана
• Форсирование физических свойств
• Отброс неэффективных ветвей

Все начинается с того, что на вход алгоритму, поступает корневая группа, на вход также поступают требуемые физические свойства, верхняя граница, выше которой (если стоимость превысит порог) не имеет смысла искать план. Поскольку план должен содержать физические операторы, то группа должна содержать физические операторы. Рекурсивно вызывается оптимизация дочерних групп.
В процессе оптимизации каждой из групп происходит исследование группы (Explore Group), если группа содержит несколько выражений, то исследование группы заключается в итеративном вызове (Explore Expression).
На этапе Explore Expression определяются правила, которые могут быть применены к этому выражению, ведется учет повторов, чтобы избежать одних и тех же преобразований, идет применение правил (Apply Rule). Важный момент: правила применяются не все подряд. А только те, что соответствуют некоторому шаблону для конкретного выражения группы (оператора). Правило применяется к выражению (предшественник) и генерирует новое выражение (потомок).
В зависимости от потомка, запускается либо задача Explore Expression, если потомок логический оператор. Либо Optimize Inputs, если потомок физический оператор. Либо Optimize Group, если применение правила породило потомка, который не входит ни в какую существующую группу, а образует новую.
Этап Optimize Inputs в свою очередь обеспечивает стратегию отброса (Discarding) неэффективных ветвей плана (Cost Based Pruning Factor), подсчет наилучшего плана и форсирование физических свойств (например, если у нас есть Merge join, который требует отсортированного входа, то будет форсирована операция сортировки).

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

После этого наиболее эффективный план копируется из Memo (Copy Out):

На протяжении всего этого процесса активно применяются две следующие концепции: Timeout, Cost Based Pruning Factor, Discarding.
Именно они влияют на то, как будет выбран план, и именно на них можно повлиять флагами трассировки.

Практика

Перейдем от теории к практике.

Отключаем Timeout

Первый флаг трассировки: 8780. Он позволяет «отключить» Timeout.

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

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

Примечание: Для просмотра информации я использую недокументированный флаг трассировки 8675, который выводит информацию по стадиям оптимизации. Я уже неоднократно использовал этот флаг в рассказах про оптимизатор. Например, тут Оптимизатор (ч.3): Optimization: Full Optimization: Search 0.

Дата поста: 01-10-2012

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

1. Оптимизация таблиц.

Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины — text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

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

2. Перестройка данных в таблице.

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

3. Тип данных.

Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.

4. NOT NULL и поле по умолчанию.

Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

5. Постоянное соединение с сервером БД.

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

6. Разделение данных.

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

Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть — не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример — счётчик посещений.

Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе — текстовое, а третье числовое — считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже — отсыл к ключевому полю id из первой таблицы.

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

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

Стоит помнить, что это не очень актуально для сайтов с малой посещаемостью и малым количеством информации.

7. Имена полей,

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

8. Требовать меньше данных.

При возможности избегать запросов типа:

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

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится «легче»; и производительнее.

Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.

Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.

Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.

Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

9. Ограничить использование DISTINCT.

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

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

10. Ограничить использование SELECT для постоянно изменяющихся таблиц.

11. Не забывайте про временные таблицы типа HEAP.

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

12. Поиск по шаблону.

Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%

13. Команда LOAD DATA INFILE

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

14. Хранение изображений в БД нежелательно.

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

15. Максимально число запросов при генерации страницы,

как мне думается, должно быть не более 20 (+- 5 запросов). При этом оно не должно зависеть от переменных параметров.

Скахин Алексей / pihel

Личный блог. Заметки о программировании и не только

Страницы

суббота, 1 декабря 2012 г.

Советы по оптимизации 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 позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка — это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр — это на сколько строк сместиться, третьи — параметр по-умолчанию, если данные соседа не нашлись. При обычном подходе бы пришлось это делать через логику приложения.

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

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

Оптимизация SQL запросов для ускорения сайтов

Дата публикации: 2017-12-06

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

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

Определение

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

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

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Понятие

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

Для запуска магазина плагинов мы использовали 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. Распространенная проблема в медленных запросах. Ее очень легко решить.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Индексы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результаты

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

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

Автор: Iain Poulson

Редакция: Команда webformyself.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока