Настройка производительности SQL может быть сложной задачей. Но важно помнить о БД, чтобы поддерживать качественную производительность. Нам нужны инструменты в нашем наборе инструментов для настройки производительности SQL. В этом посте мы отметим шесть советов, которые помогут вам настроить SQL Server.n
Что такое настройка производительности SQL?
Настройка производительности SQL — это проверенная практика изменения способа взаимодействия нашего приложения с нашей реляционной базой данных, чтобы оно могло быть более производительным. nnПеред вами шесть советов по настройке производительности SQL. Каждый совет не стоит отдельно. Многие из них связаны.n
Совет 1: индивидуальные запросы, основанные на бизнес-использовании
Наш первый совет: приложение должно владеть базой данных. Это позволяет нам развивать нашу систему независимо от других, которые нуждаются в наших данных. Когда приложению принадлежит база данных, можно начать со сценариев использования приложения, а не с данных, которые хранятся.nnНиже я опишу многие преимущества этого подхода. Например, чем меньше полей мы извлекаем из базы данных за один раз, тем меньше используемой полосы пропускания и иногда быстрее выполняется запрос.nnЧтобы это произошло, требуется тесное сотрудничество между командой разработчиков и заинтересованными сторонами, но результат почти всегда стоит того. Если мы не понимаем, как мы используем бизнес, мы можем настроить базу данных так, чтобы она имела худшую производительность. Все настройки являются субъективными для поведения наших клиентов.n
Совет 2. Настройте мониторинг для быстрого выявления уязвимостей SQL
Все эти советы совершенно бесполезны, если у нас нет возможности увидеть, где находятся наши проблемы. Когда мы ведем машину, мы сканируем наши зеркала и проверяем наши «слепые зоны», чтобы убедиться, что никакие машины не могут поменять полосу движения. Точно так же мы хотим что-то, что «проверяет зеркала» приложения и может показать нам, где могут находиться потенциальные проблемы с базой данных. Ключевые места, где можно найти такие места БД — это скорость трафика и задержка.n
Совет 3: Индекс на основе полей запроса
Индексирование на основе полей запросов — один из самых распространенных советов, которые вы получите при настройке производительности SQL. Индексы сохранят список полей, чтобы вы могли искать их намного быстрее, а не сканировать всю таблицу. Какой минус? Каждый индекс в таблице замедляет время записи, так как мы должны обновлять индекс каждый раз, когда обновляем таблицу. Целесообразно выбирать только те поля, которые часто запрашиваются в вашем приложении. nnЕсли вы используете SQL Server, вы можете посмотреть на план выполнения вашего запроса, чтобы найти таблицы и заменить их индексами. Для других SQL провайдеров, вам, возможно, придется в значительной степени полагаться на свой мониторинг.n
Совет 4. Исключите запрос для каждого элемента коллекции
Запросы для каждой коллекции могут проявляться как страшная проблема SELECT N + 1. Если мы видим, что аналогичный или один и тот же запрос выполняется несколько раз в одном и том же запросе приложения, это сигнал для опасений. Обычно мы можем оптимизировать эти многочисленные запросы, чтобы получить все необходимое сразу. Например, я хотел получить список продуктов, которые заказал клиент недавно. У меня может быть часть запроса, которая загружает заказы для клиента:n
SELECT number, ID from Orders where customerID = @customerIDТогда я хочу получить все продукты по этим заказам. Для каждого заказа у меня может быть запрос, который выглядит так:n
SELECT name, part_number from Parts join OrderLines on part.Id = OrderLines.partId where orderId = @OrderIdСейчас я выполняю этот запрос для каждого заказа, который когда-либо делал клиент.nnС постоянными клиентами это может быстро стать проблемой. Вместо этого я могу захотеть объединить поиск продукта в один запрос:n
SELECT name, part_number from Parts join OrderLines on part.Id = OrderLines.partId where orderId IN (@OrderIds)Сейчас я отправляю весь список идентификаторов заказов для запроса сразу. Вы также можете присоединиться к таблице заказов. В любом случае это приведет к получению всех необходимых результатов со значительным приростом производительности.n
Совет 5: Уменьшите тупики
Блокировки базы данных — более тонкая, но более серьезная проблема производительности. Это также может повлиять на вашу функциональность. В SQL Server это может выглядеть так:n
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Многие разработчики боятся видеть это сообщение. Замять тупиковые ситуации сложно, и вы не можете их полностью устранить. Трудно отследить взаимные блокировки, потому что они являются результатом множественных запросов, сталкивающихся друг с другом. И поэтому ни одна трассировка запроса не станет для вас тупиком.n
Давайте ослабим давление на тупик
Чтобы настроиться на тупики, мы хотим уменьшить их возникновение. Лучший способ сделать это — изменить дизайн таблицы, которая получает удар. Частота возникновения взаимоблокировок соотносится со скоростью обновления одной и той же строки в одно и то же время. Как я уже говорил выше, ваш инструмент мониторинга должен иметь возможность легко выявлять их, показывая вам трафик на конечную точку. Чтобы уменьшить частоту взаимоблокировок, мы должны уменьшить скорость обновления таблицы одновременно. У нас есть несколько вариантов для этого.nnВо-первых, мы можем найти способ разделить таблицу из одной сущности на две сущности. Если мы настраиваем запросы на основе бизнес-использования, мы уже должны это делать. Но даже тогда наше приложение может достичь масштаба, в котором мы должны переосмыслить дизайн нашей сущности.nnСледующий вариант — перейти от обновления той же строки к добавлению новой строки. Например, если мы сделаем версию обновлений, у нас будет новая строка для каждого обновления, снимающая блокировку. Мы все еще хотим иметь своего рода оптимистическую блокировку в нашем приложении, чтобы мы не теряли обновления.nnПоследний вариант — сократить количество транзакций, попадающих в таблицу. Часто мы заходим в тупики, потому что в наших запросах мы слишком долго удерживаем транзакцию базы данных. Мы можем следовать здоровым образцам Unit of Work, чтобы настроить эту проблему.n
Совет 6: Используйте ограниченные наборы результатов
Подобно бомбе замедленного действия, неограниченные наборы результатов — это одна из тех вещей, которые почти всегда будут взрывать вас в будущем.nnОчень просто спроектировать программное обеспечение на короткий срок и упустить из виду среднесрочные или долгосрочные компромиссы в нашем дизайне. Обычно это нормально. Мы хотим спроектировать сейчас и развивать нашу систему на потом. Но неограниченные наборы результатов могут ударить, как грузовик, из ниоткуда, забрав с собой вашу систему. Любой запрос, который не гарантирует максимальное количество результатов, находится под угрозой.nnДавайте перейдем к нашему более раннему примеру получения заказов для клиента.nnЕсли мы получим все заказы, которые когда-либо заказывал клиент, у нас все будет хорошо в течение первых нескольких месяцев, возможно, даже лет. Но что, если клиент в итоге разместит у нас 1000 заказов по 100 товаров на заказ? Этого легко достичь в бизнес-транзакциях. Внезапно этот запрос начнет замедлять запрос или полностью останавливаться. В худшем случае эта медлительность начнет ставить в очередь другие запросы, в конечном итоге блокируя всю вашу систему.n
Нахождение и исправление неограниченных результатов
Ключ к исправлению неограниченных результатов восходит к настройке запросов для бизнес-использования. Поняв бизнес-процесс, вы сможете получить рекомендации по ограничению результатов. Возвращаясь к нашему примеру получения заказов для клиента. Если мы знали, что этот запрос должен был предоставить клиенту его самые последние заказы, мы можем определить «недавние» как последние 10 или стоимость заказов за последние три месяца и т.д. Теперь у нас есть ограниченный набор результатов.n
Понимать, контролировать и настраивать
Во всех этих советах есть основная тема. Во-первых, понять ваших пользователей и ваш бизнес-процесс. Если мы начнем там, все остальное может последовать за этим. Но если у нас нет этого фундаментального понимания, тогда мы рискуем сделать вещи хуже, а не лучше. Затем мы должны отслеживать наше приложение вплоть до базы данных. Мы можем исправить проблемы, только когда увидим их. И лучше всего увидеть проблему, прежде чем она сломает нашу систему или попадет в глаза наших клиентов.nnЯ надеюсь, что эти советы дадут вам уверенность и направление, необходимые для ускорения работы приложения при обращении к его базе данных.