Настройка производительности SQL

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

SQL performance tuning is speeding up queries against a relational database.

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

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

Способы поиска медленных SQL-запросов в SQL Server для измерения производительности SQL Server:

1. Создайте план выполнения. Очень важно иметь возможность создавать планы выполнения, которые вы можете выполнить с помощью SQL Server Management Studio, чтобы диагностировать отложенные запросы. После выполнения запросов генерируются фактические планы выполнения. Чтобы создать план выполнения:

• Начните с выбора «Запрос ядра базы данных» на панели инструментов SQL Server Management Studio.
• После этого введите запрос, а затем выберите «Включить фактический план выполнения» в параметре «Запрос».
• Пришло время выполнить ваш запрос на этом этапе. Вы можете сделать это, нажав F5 или кнопку «Выполнить» на панели инструментов.
• Затем план выполнения будет показан на панели результатов на вкладке «Панель выполнения» в SQL Server Management Studio.

2. Мониторинг использования ресурсов. На производительность базы данных SQL сильно влияет использование ресурсов. Мониторинг использования ресурсов важен, поскольку невозможно улучшить то, что не измеряется. Используйте средство системного монитора в Windows для оценки производительности SQL Server. С его помощью вы можете просматривать объекты SQL Server, счетчики производительности и другую активность объектов. Одновременно наблюдайте за счетчиками Windows и SQL Server с помощью системного монитора, чтобы увидеть, есть ли какая-либо корреляция между производительностью двух служб.

3. Используйте DMV SQL для поиска медленных запросов. Обилие представлений динамического управления (DMV), которые включает SQL Server, является одной из его лучших функций. Их много, и они могут дать много знаний по самым разным предметам.

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

Оптимизация запроса для SQL:

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

1. SELECT поля вместо использования SELECT *:

Многие разработчики SQL используют сочетание клавиш SELECT * для запроса всех данных в таблице при выполнении исследовательских запросов. Однако, если таблица содержит много полей и строк, база данных будет перегружена излишними запросами данных. Используя оператор SELECT, можно указать базе данных запрашивать только те данные, которые вам действительно нужны для удовлетворения потребностей вашего бизнеса. Например:

Inefficient: 
Select * from GeeksTable;

Efficient: 
SELECT FirstName, LastName, 
Address, City, State, Zip FROM GeeksTable;

2. Избегайте SELECT DISTINCT:

Практично избавиться от дубликатов в запросе с помощью SELECT DISTINCT. Чтобы получить отдельные результаты, ВЫБЕРИТЕ РАЗЛИЧНЫЕ ГРУППЫ для каждого поля в запросе. Однако для достижения этой цели требуется много вычислительной мощности. Кроме того, данные могут быть в определенной степени неточно классифицированы. Выберите больше полей, чтобы получить разные результаты, вместо использования SELECT DISTINCT.

Inefficient and inaccurate: 
SELECT DISTINCT FirstName, LastName,
State FROM GeeksTable;

Efficient and accurate: 
SELECT FirstName, LastName, Address, 
City, State, Zip FROM GeeksTable;

3. Создайте запросы с INNER JOIN (не WHERE или cross join):

Некоторые разработчики SQL предпочитают операторы WHERE, как в приведенных ниже примерах:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1, GFG2
WHERE GFG1.CustomerID = GFG2.CustomerID

Декартово соединение, также известное как декартово произведение или ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ, создается таким соединением. Декартово соединение создает все мыслимые комбинации переменных. Если бы в этом примере у нас было 1 000 клиентов и 1 000 общих продаж, запрос сначала выдал бы 1 000 000 результатов, а затем отфильтровал бы 1 000 записей, где CustomerID правильно подключен. База данных выполнила в 100 раз больше работы, чем было необходимо, поэтому это расточительное использование ее ресурсов. Из-за возможности получения миллиардов или триллионов результатов декартовы соединения представляют собой особую проблему для крупномасштабных баз данных.

Чтобы предотвратить создание декартова соединения, вместо этого используйте INNER JOIN:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID

База данных будет генерировать только ограниченное количество желаемых записей, где CustomerID равен.

4. Используйте WHERE вместо HAVING для определения фильтров:

Успешный запрос извлечет только необходимые записи из базы данных. Операторы HAVING вычисляются после операторов WHERE в соответствии с порядком операций SQL. Оператор WHERE более эффективен, если целью является фильтрация запроса на основе условий.

Например, если предположить, что в 2019 году было совершено 500 продаж, запросите, чтобы узнать, сколько продаж было совершено на одного клиента в этом году.

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
 FROM GFG1
  INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
GROUP BY GFG1.CustomerID, GFG1.Name
HAVING GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"

К таблице GFG2 будет отправлен запрос для получения 1000 записей о продажах, которые затем будут отфильтрованы, чтобы включить только 500 записей, созданных в 2019 году до подсчета набора данных.

Предложения WHERE, напротив, устанавливают ограничение на количество извлекаемых записей:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1 INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
WHERE GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"
GROUP BY GFG1.CustomerID, GFG1.Name

5. Используйте подстановочные знаки только в конце фразы:

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

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

SELECT City FROM GeekTable WHERE City LIKE ‘%No%’

Ожидаемые результаты — Noida и Noopur, которые будут возвращены этим запросом. Неожиданные результаты также будут получены.

Лучшим запросом было бы:

SELECT City FROM GeekTable WHERE City LIKE ‘No%’ 

6. Используйте LIMIT для выборки результатов запроса:

Используйте оператор LIMIT, чтобы проверить, будут ли результаты приятными и полезными, прежде чем выполнять запрос в первый раз. (В некоторых системах СУБД термины TOP и LIMIT используются как синонимы.) Оператор LIMIT возвращает только заданное количество записей. Используя оператор LIMIT, вы можете избежать нагрузки на производственную базу данных большим запросом только для того, чтобы обнаружить, что ее нужно отредактировать или улучшить.

Мы рассмотрим максимум 10 строк в запросе продаж 2019 года сверху:

SELECT GFG1.CustomerID, GFG1.Name, GFG1.LastSaleDate
FROM GFG1
INNER JOIN GFG2
ON GFG1.CustomerID = GFG2.CustomerID
WHERE GFG2.LastSaleDate BETWEEN "1/1/2019" AND "12/31/2019"
GROUP BY GFG1.CustomerID, GFG1.Name
LIMIT 10

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

7. Запустите запрос в нерабочее время:

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

Ваш запрос должен выполнять запрос чаще, используя следующее:

• Выбор из больших таблиц (> 1 000 000 записей)
• Декартовы соединения или ПЕРЕКРЕСТНЫЕ СОЕДИНЕНИЯ
• Операторы цикла
• ВЫБЕРИТЕ РАЗЛИЧНЫЕ операторы
• Вложенные подзапросы
• Поиск с подстановочными знаками в полях длинного текста или заметок
• Несколько запросов схемы
• Запрашивайте уверенно

Настройка индекса:

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

Indexes can be used to increase a database’s speed and query performance. Index tuning is the process of improving the index selection.

Преимущества настройки индекса:

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

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

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

• Последствия создания индекса: Когда приходит время редактировать записи, индексы снижают производительность. Индексы данных должны обновляться всякий раз, когда запрос изменяет данные в таблице. Прежде чем вы сможете создать нужное количество индексов, необходимо протестировать и контролировать вашу базу данных, чтобы определить идеальный баланс. Базы данных, часто используемые для создания отчетов в статических системах, могут поддерживать индексы большего размера, чтобы разрешить запросы только для чтения. Базе данных с большим объемом транзакций модификации данных потребуется меньше индексов, чтобы обеспечить более высокую пропускную способность. Дисковое пространство также необходимо индексам. Количество записей в таблице, количество и размер столбцов индекса и другие факторы будут влиять на точный размер.
• Создание наилучшего индекса. Существует несколько правил, которым необходимо следовать при создании наилучших индексов для вашего приложения. При выборе индексов для ваших таблиц учитывайте следующие факторы: от столбцов, которые выбирает пользователь, до значений данных внутри них:
• Короткие индексы: короткие индексы выгодны по двум причинам. Во-первых, операции с базой данных по своей природе требуют много места на диске. Большие ключи индекса потребуют от базы данных более частого считывания данных с диска, что снижает пропускную способность. Во-вторых, более мелкие элементы проще сравнивать, поскольку при сравнении часто используются элементы указателя. Поскольку целое число компактно и просто для сравнения в базе данных, один столбец целых чисел обеспечивает наилучший из возможных индексов. Строки символов, с другой стороны, требуют тщательного рассмотрения параметров сортировки и посимвольного сравнения.
• Отдельные индексы: индексы с наименьшим количеством повторяющихся значений являются наиболее эффективными. Представьте себе телефонную книгу для сообщества, где практически у каждого есть аналогия с фамилией Гупта. В любом городе телефонная книга, упорядоченная по фамилиям, не особенно полезна, потому что вы можете исключить лишь относительно ограниченное количество записей при поиске Гупты.
• Отдельный индекс — это тот, который имеет значительную часть уникальных значений. Уникальный индекс, очевидно, чрезвычайно избирательен, потому что в нем нет повторяющихся записей. Чтобы узнать, насколько избирательным является индекс, многие базы данных хранят статистику по каждому индексу в своих записях. При создании плана выполнения запроса база данных учитывает эту информацию.
• Кластерные индексы. Многие базы данных имеют уникальный специальный индекс для каждой таблицы, который содержит все данные из строки. Этот индекс известен как кластеризованный индекс SQL Server. Потому что в каждом элементе указателя содержится вся необходимая информация, и нет ссылок, по которым можно было бы получить дополнительные значения данных.

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

Обязательно выберите столбец со статическими данными при выборе столбцов для кластеризованного индекса. Базе данных может потребоваться сместить запись индекса, если вы изменяете запись и значение столбца в кластеризованном индексе (чтобы записи оставались в отсортированном порядке). Важно иметь в виду, что записи индекса для кластеризованных индексов содержат все данные столбца; таким образом, сдвиг элемента эквивалентен выполнению оператора DELETE, за которым следует INSERT, что может легко привести к проблемам с производительностью, если выполнять его часто. Из-за этого столбцы первичного ключа или столбцы внешнего ключа часто имеют кластеризованные индексы.

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