Настройка производительности базы данных SQL для разработчиков

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

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

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

В этой статье я хотел бы сделать две вещи:

1. Предоставьте разработчикам некоторые методы настройки производительности SQL на стороне разработчиков.
2. Объясните, как разработчики и администраторы баз данных могут эффективно работать вместе.

Настройка производительности SQL (в кодовой базе): индексы

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

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

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

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

Обратите внимание, что если ваши таблицы постоянно забиты INSERT , UPDATE и DELETE , вам следует быть осторожным при индексировании — вы можете в конечном итоге снизить производительность, поскольку после этих операций необходимо изменить все индексы.

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

Настройка SQL: планы выполнения в SQL Server

Кстати: инструмент Execution Plan в SQL Server может быть полезен для создания индексов.

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

Чтобы получить план выполнения (в SQL Server Management Studio), просто нажмите «Включить фактический план выполнения» (CTRL + M) перед выполнением запроса.

После этого появится третья вкладка под названием «План выполнения». Вы можете увидеть обнаруженный отсутствующий индекс. Чтобы создать его, просто щелкните правой кнопкой мыши план выполнения и выберите «Отсутствующие сведения об индексе…». Это так просто!

( Нажмите, чтобы увеличить )

Настройка SQL: избегайте циклов кодирования

Представьте себе сценарий, в котором 1000 запросов последовательно забивают вашу базу данных. Что-то типа:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

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

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

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

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Настройка SQL: избегайте коррелированных подзапросов SQL

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

Вот пример коррелированного подзапроса:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

В частности, проблема заключается в том, что внутренний запрос ( SELECT CompanyName… ) выполняется для каждой строки, возвращаемой внешним запросом ( SELECT c.Name… ). Но зачем снова и снова перебирать Company для каждой строки, обрабатываемой внешним запросом?

Более эффективным методом настройки производительности SQL был бы рефакторинг коррелированного подзапроса как соединения:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

В этом случае мы просматриваем таблицу Company только один раз, в начале, и JOIN ее с таблицей Customer . С этого момента мы можем более эффективно выбирать нужные нам значения ( co.CompanyName ).

Настройка SQL: выбирайте экономно

Один из моих любимых советов по оптимизации SQL — избегать SELECT * ! Вместо этого вы должны индивидуально включать определенные столбцы, которые вам нужны. Опять же, это звучит просто, но я вижу эту ошибку повсюду. Рассмотрим таблицу с сотнями столбцов и миллионами строк — если вашему приложению действительно нужно всего несколько столбцов, нет смысла запрашивать все данные. Это массовая трата ресурсов. ( Дополнительные вопросы см. здесь. )

Например:

 SELECT * FROM Employees

против.

 SELECT FirstName, City, Country FROM Employees

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

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Чтобы избежать такой ошибки SQL Server, вы должны объявить каждый столбец отдельно:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

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

Настройка SQL: разумное использование временных таблиц (#Temp)

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

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

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

Решение не всегда простое, но этот пример даст вам представление о ситуациях, в которых вы можете захотеть использовать временные таблицы:

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

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Примечание: некоторые разработчики SQL также избегают использования SELECT INTO для создания временных таблиц, говоря, что эта команда блокирует базу данных tempdb, запрещая другим пользователям создавать временные таблицы. К счастью, это исправлено в версии 7.0 и более поздних .)

В качестве альтернативы временным таблицам вы можете рассмотреть возможность использования подзапроса в качестве таблицы:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Но ждать! Возникла проблема со вторым запросом. Как описано выше, мы должны включать в наш подзапрос только те столбцы, которые нам нужны (т. е. не использовать SELECT * ). Принимая это во внимание:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

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

Наконец, когда вы закончите работу с временной таблицей, удалите ее, чтобы очистить ресурсы tempdb, а не просто ждите, пока она будет удалена автоматически (как это произойдет, когда ваше соединение с базой данных будет разорвано):

 DROP TABLE #temp

Настройка SQL: «Существует ли моя запись?»

Этот метод оптимизации SQL касается использования EXISTS() . Если вы хотите проверить, существует ли запись, используйте EXISTS() вместо COUNT() . В то время как COUNT() сканирует всю таблицу, подсчитывая все записи, соответствующие вашему условию, EXISTS() завершит работу, как только увидит нужный результат. Это даст вам лучшую производительность и более четкий код.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

против.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Настройка производительности SQL с помощью SQL Server 2016

Как, вероятно, знают администраторы баз данных, работающие с SQL Server 2016, в этой версии произошли важные изменения в настройках по умолчанию и управлении совместимостью. Как основная версия, она, конечно, поставляется с новыми оптимизациями запросов, но контроль над их использованием теперь упрощается через sys.databases.compatibility_level .

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

Администраторы баз данных SQL (DBA) и разработчики часто конфликтуют по вопросам, связанным с данными и не связанными с данными. Вот несколько советов (для обеих сторон), основанных на моем опыте, о том, как ладить и эффективно работать вместе.

Твитнуть

Оптимизация базы данных для разработчиков:

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

2. Даже если вы ниндзя в моделировании данных SQL, попросите администратора баз данных помочь вам с вашей реляционной диаграммой. Им есть чем поделиться и что предложить.

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

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

Оптимизация базы данных для администраторов баз данных SQL Server:

1. Если вам не нравятся люди, которые спрашивают вас о базе данных, предоставьте им панель состояния в реальном времени. Разработчики всегда с подозрением относятся к состоянию базы данных, и такая панель могла бы сэкономить всем время и силы.

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

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

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