Оптимизация ms sql

Дата поста: 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 запросов). При этом оно не должно зависеть от переменных параметров.

Оптимизация 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.

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

Здесь приведены жестко заданные предположения, используемые оптимизатором в рамках метода «оптимизация для неизвестного». По крайней мере, в этом случае вы знаете, как оптимизатор угадывает неизвестные величины. Оптимальная настройка запросов в значительной мере начинается с умения объяснить оценки числа строк, особенно неточные. В моих примерах запросы будут направлены к таблице Sales. SalesOrderDetail в тестовой базе данных AdventureWorks2014. Если вы захотите выполнить примеры из этой статьи, но не располагаете установленной базой данных, ее можно загрузить. Кроме того, стоит убедиться, что база данных настроена на уровень совместимости 120, при котором SQL Server по умолчанию использует новое средство СЕ (2014). Сделать это можно с помощью следующего программного кода:

— Убедитесь, что уровень совместимости базы данных >= 120
для использования по умолчанию нового средства СЕ

Оценки оптимизации для неиз­вестных я разделяю на следующие группы операторов:

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

Оптимизация для неизвестного для операторов: >, >=, , >=, = . Число строк в таблице 121 317, поэтому СЕ фильтра будет 0,3 * 121317 = 36395,1. Насколько эта величина близка к действительному числу строк в типичном варианте использования, решать вам; однако оптимизатор делает именно такое предположение.
Это первый раздел, в котором демонстрируется метод оптимизации для неизвестного, поэтому начнем с перечисления различных случаев использования данного метода наряду с готовыми к применению примерами. Метод оптимизации для неизвестного используется в следующих случаях.

Оптимальная настройка запросов начинается с умения объяснить оценки числа строк, особенно неточные

1. При работе с локальными переменными
В отличие от значений параметров, которые можно прослушивать, значения переменных обычно прослушать нельзя. Исключение будет описано немного позже. Причина проста: начальная единица оптимизации — весь пакет, а не только инструкция запроса. Объявление и задание значений переменным выполняются в оптимизируемом пакете. Точка, в которой запрос оптимизируется, предшествует заданию любой переменной, поэтому значения переменных нельзя прослушивать. В результате оптимизатору приходится использовать метод оптимизации для неизвестного.

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

План выполнения для этого запроса показан на рисунке 1. Классический инструмент, используемый оптимизатором, чтобы получить СЕ для фильтра, — гистограмма. Если ее не существовало для столбца OrderQty перед выполнением этого запроса и вы не отключили автоматическое создание статистики в базе данных, то SQL Server создает ее при выполнении запроса. Вы можете использовать запрос, приведенный в листинге, чтобы получить автоматически созданное имя статистики.

Выполнив этот программный код после предшествующего запроса, я получил имя статистики _ WA_Sys_00000004_44 СА3770. Запомните полученное вами имя. Затем используйте следующий код для просмотра гистограммы после замены имени статистики на полученное вами:

Таблица Последние несколько шагов гистограммы

Последние несколько шагов в полученной гистограмме показаны в таблице 1.
Мы ясно видим, что СЕ, показанная на рисунке 1, основана на последних трех шагах гистограммы. Оценка довольно точная: 4,00639 при действительном значении 4. В отличие от приведенного выше примера, в следующем запросе используется локальная переменная, что вынуждает оптимизатор применить метод оптимизации для неизвестного:

План для этого запроса показан на рисунке 2.

Как было предсказано, это оценка 30% количества элементов ввода. Примечательно, что из-за неточности оценки оптимизатор выбрал неоптимальную стратегию статистической обработки. Здесь использован алгоритм статистической обработки Hash Match вместо сортировки и алгоритма Stream Aggregate. Это лишь одно из многих возможных последствий неточных оценок. Существует исключение, при котором оптимизатор может прослушивать переменные: событие перекомпиляции происходит на уровне инструкций. Дело в том, что по определению перекомпиляция на уровне инструкций происходит после того, как выполнено задание всех переменных. Автоматическая перекомпиляция всегда происходит на уровне инструкций. Так было все время после появления SQL Server 2005 и до написания данной статьи. Я тестирую программный код на SQL Server 2016. Для ручной перекомпиляции на уровне инструкций нужно добавить указание запроса RECOMPILE с использованием оператора OPTION:

Этот запрос формирует такой же план, как показанный на рисунке 1, где оценка является точной. Обратите внимание, что если указать параметр WITH RECOMPILE на уровне процедуры, то прослушивание не будет включено — это достигается только указанием в запросе OPTION (RECOMPILE). Перейдем к следующему случаю использования метода оптимизации для неизвестного.

2. При использовании параметров, но отключенном автоматическом прослушивании параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (©parameter UNKNOWN) или с флагом трассировки 4136
Обычно значения параметров доступны для прослушивания, так как они задаются при выполнении процедуры или функции, прежде чем пакет передается оптимизатору. Однако можно применить метод оптимизации для неизвестного с двумя указаниями запроса. Если нужно отменить прослушивание параметров для всех входов, используйте указание OPTIMIZE FOR UNKNOWN. Если требуется отменить прослушивание для определенного параметра, используйте указание OPTIMIZE FOR (©parameter UNKNOWN). Также можно использовать флаг трассировки 4136 для отключения прослушивания параметров при разных детализациях: запроса, сеанса или глобальной детализации. Обратите внимание, что при использовании хранимой процедуры, скомпилированной в собственном коде, оптимизация для неизвестного выбирается по умолчанию. В качестве примера следующий программный код создает хранимую процедуру и отключает прослушивание параметров в запросе с использованием указания

Используйте следующий программный код для тестирования хранимой процедуры: EXEC dbo.Prod @Qty = 40; Я получил такой же план запроса, как показанный на рисунке 2, с оценкой 30%.

Рассмотрим еще один сценарий, в котором используется метод оптимизации для неизвестного.
3. Статистика недоступна
Возьмем случай, когда гистограмма для фильтруемого столбца отсутствует и вы не позволяете SQL Server создать гистограмму, отключив автоматическое создание статистики на уровне базы данных и не формируя индекс для столбца. Используйте следующий программный код, чтобы организовать такую среду для нашей демонстрации, заменив имя статистики именем, полученным в результате выполнения запроса, приведенного в листинге:

Затем выполните код, в котором используется константа в фильтре:

Выполнив этот запрос ранее, вы получили план, показанный на рисунке 1, с точной оценкой. Но на этот раз у оптимизатора не было гистограммы, поэтому используется метод оптимизации для неизвестного и создается план, показанный на рисунке 2, с оценкой 30%.

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

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

Оценки оптимизации для неизвестных для операторов BETWEEN и LIKE
При использовании предиката BETWEEN жестко заданные предположения зависят от сценария и применяемой СЕ. В старых СЕ во всех случаях используется оценка 9%. Это демонстрирует следующий запрос. Флаг трассировки 9481 запроса используется, чтобы применить старую СЕ.

План для этого запроса показан на рисунке 3. Оценка 0,09 * 121317 = 10918,5.

В новой СЕ задействованы различные оценки при применении констант и отсутствующей гистограмме и при использовании переменных или параметров с отключенным прослушиванием. В первом случае используется оценка 9%; во втором — оценка 16,4317%. Ниже приводится пример использования констант. Обязательно удалите любую существующую статистику для столбца и отключите автоматическое создание статистики, как показано выше, перед выполнением теста и включите после его завершения.

Я получил такой же план, как на рисунке 3, с оценкой 9%. Ниже приводится пример, демонстрирующий применение переменных (то же поведение, что и при использовании параметров с отключенным прослушиванием):

Я получил план, приведенный на рисунке 4, показывающий оценку 16,4317%.
При использовании предиката LIKE во всех сценариях оптимизации для неизвестного как в старых, так и в новых СЕ применяется оценка 9%. Ниже приведен примере использованием локальных переменных:

Вы увидите ту же оценку 9%, как показано на рисунке 3, хотя в данном случае действительное число строк 12, а ранее было 3

Оценки оптимизации для неизвестных для оператора =
При использовании оператора = различают три основных случая:
• уникальный столбец;
• неуникальный столбец и доступная плотность;
• неуникальный столбец и недоступная плотность.
Если фильтруемый столбец уникален (для него определены уникальный индекс, ограничение PRIMARY KEY или UNIQUE), то оптимизатору известно, что совпадений не может быть более одного, поэтому оценка равна 1. Ниже приводится запрос, демонстрирующий этот случай:

На рисунке 5 показан план для этого запроса с оценкой 1. Если столбец не уникален и оптимизатору доступна информация о плотности (средний процент для отдельного значения), то оценка основывается на плотности. Если не отключено автоматическое создание статистики или для столбца сформирован индекс, то эта информация будет доступна оптимизатору. Чтобы продемонстрировать это, сначала убедитесь, что автоматическое создание статистики включено, выполнив следующий программный код:

Таблица 2 Оценки метода оптимизации для неизвестного для операторов

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

различные методы. В старой СЕ используется оценка С^0,75 (степень три четвертых), где С — входное число элементов, а в новой используется оценка С^0,5 (квадратный корень).
Чтобы продемонстрировать это, сначала удалите любую статистику для столбца OrderQty и отключите автоматическое создание статистики, как было показано ранее:

Используйте следующий программный код для тестирования старого метода СЕ:

План для этого запроса показан на рисунке 7.

Оценка 6500,42 — результат вычисления 121317^З/4.
Используйте следующий программный код для тестирования нового метода СЕ:

План для этого запроса показан на рисунке 8.

Оценка 348,306 получена в результате вычисления 121317^0,5.

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

Таким образом, метод оптимизации для неизвестного используется оптимизатором SQL Server, чтобы создать оценку СЕ при неизвестных входных данных или недостатке статистики.

Иногда у оптимизатора нет иного выбора, кроме использования этого метода просто из-за нехватки информации. Иногда данный метод применяется принудительно, если метод оптимизации для известного не подходит. Итак, метод оптимизации для неизвестного применяется в следующих случаях:
1. Использование переменных (кроме случаев использования RECOMPILE на уровне инструкций).
2. Использование параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (©parameter UNKNOWN) или флагом трассировки 4136 (всегда при использовании хранимой процедуры, скомпилированной в собственном коде).
3. Статистика недоступна.
В таблице 2 приведена сводка оценок оптимизации для неизвестного, используемых для различных групп операторов.

Настройка производительности SQL – Советы по оптимизации запросов MySQL

Главное меню » Базы данных » База данных MySQL » Настройка производительности SQL – Советы по оптимизации запросов MySQL

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

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

На самом деле не имеет значения, используете ли вы уровень абстракции базы данных (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django или другие) или пишете нативные SQL-запросы, вы в конечном итоге столкнетесь с проблемой настройки отправляемых запросов. в вашу базу данных.

Создавайте индексы, но делайте это с умом

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

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

Кроме того, основными узкими местами в поиске данных могут быть части GROUP BY и ORDER BY. Потенциальный сбой будет в том, что вы не сможете индексировать их в некоторых случаях, как мы объяснили здесь. Поэтому вам может потребоваться переосмыслить структуру вашего запроса перед созданием индексов, чтобы убедиться, что вы пишете отличные запросы, а также пишете запросы с индексированием.

Как только вы выяснили индексирование для одного запроса, не останавливайтесь на этом. Расширьте свой взгляд и посмотрите на другие важные вопросы в вашем приложении. Чем больше запросов вы просматриваете, тем больше вы будете думать о лучших индексах для создания. Убедитесь, что вы объединяете индексы, когда это возможно, и удаляете индексы, которые больше не нужны. Просмотр области всего приложения всегда будет лучше, чем просмотр области одного запроса.

Тем не менее, наличие большего количества индексов, чем вам нужно, также может иметь неприятные последствия, поскольку они могут замедлить операции записи (такие как операторы INSERT/UPDATE). Поэтому создайте индексы для оптимизации ваших запросов SQL, но делайте это с умом.

Не мешайте указателям

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

Пример № 1 – Избегайте переноса индексированных столбцов с функциями

Рассмотрим этот запрос, который подсчитывает количество хот-догов, купленных в России в 2019 году. На всякий случай, если вам интересно, в 2019 году в России было продано много хот-догов.

Как видите, мы используем функцию YEAR, чтобы получить часть года из столбца purchase_time. Этот вызов функции не позволит базе данных использовать индекс для поиска по столбцу purchase_time, потому что мы проиндексировали значение purchase_time, но не возвращаемое значение YEAR (purchase_time).

Чтобы преодолеть эту проблему и настроить этот запрос SQL, вы можете проиндексировать результат функции, используя Generated Columns, которые доступны начиная с MySQL 5.7.5.

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

Пример №2 – избегать условия OR

Рассмотрим этот запрос, который выбирает количество постов на Facebook, опубликованных после канун Нового года, или опубликованных пользователем по имени Alex.

Индекс для столбцов username и post_time может показаться полезным, но в большинстве случаев база данных не будет его использовать, по крайней мере, не полностью. Причиной будет связь между двумя условиями – оператор OR, который заставляет базу данных извлекать результаты каждой части условия в отдельности.

Альтернативный способ взглянуть на этот запрос может состоять в том, чтобы «разделить» условие OR и «объединить» его с помощью предложения UNION. Эта альтернатива позволит вам индексировать каждое из условий отдельно, поэтому база данных будет использовать индексы для поиска результатов, а затем объединять результаты с предложением UNION.

Обратите внимание, что если вы не возражаете против дублирования записей в наборе результатов, вы также можете использовать UNION ALL (который будет работать лучше, чем UNION DISTINCT по умолчанию).

Пример № 3 – Избегайте сортировки со смешанным порядком

Рассмотрим этот запрос, который выбирает все сообщения из Facebook и сортирует их по имени пользователя в порядке возрастания, а затем по дате публикации в порядке убывания.

MySQL (и многие другие реляционные базы данных) не могут использовать индексы при сортировке со смешанным порядком (как ASC, так и DESC в одном и том же предложении ORDER BY). Это изменилось с выпуском функциональности обращенных индексов и MySQL 8.x.

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

Итак, вы решили, что вам это нужно, или ваш менеджер по продукту сказал: «Мы никак не можем обойтись без него»? Другим вариантом будет использование сгенерированных столбцов (доступно в MySQL 5.7.5+) для создания обращенного столбца и сортировки по этому столбцу вместо исходного. Например, предположим, что вы сортируете по числовому столбцу, вы можете создать сгенерированный столбец с отрицательным числовым значением, соответствующим исходному номеру, и отсортировать по этому новому столбцу в обратном порядке. Таким образом, все столбцы будут иметь одинаковый порядок сортировки в предложении ORDER BY, но сортировка будет происходить так, как это было первоначально определено требованиями вашего продукта.

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

Пример № 4 – Избегайте условий с разными типами столбцов

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

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

Итак, как вы можете настроить этот запрос SQL? У вас есть два варианта для оптимизации этого запроса. Первый – сравнить столбец с константным значением, соответствующим типу столбца, поэтому, если это столбец VARCHAR, сравните его с «5» (с одинарными кавычками), а не с 5 (это числовое сравнение, которое приведет к в скрытом исполнении).

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

Избегайте поиск по LIKE с подстановочными знаками

Рассмотрим этот запрос, который ищет все сообщения в Facebook по имени пользователя, которое содержит строку «Mar», поэтому мы ищем все сообщения, написанные пользователями с именами Mark, Marcus, Almar и т. l.

Наличие подстановочного знака «%» в начале шаблона не позволит базе данных использовать индекс для поиска в этом столбце. Такие поиски могут занять некоторое время ..

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

Другим вариантом будет использование полнотекстовых индексов. Обратите внимание, что эти индексы и синтаксис MATCH… AGAINST не свободны от проблем и имеют некоторые различия по сравнению со знакомыми выражениями LIKE в MySQL.

Заключение

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.