Любимые трюки настройки производительности

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

Источник

29 ответов

Вот удобный список вещей, которые я всегда даю кому-то, кто спрашивает меня об оптимизации.
В основном мы используем Sybase, но большинство советов будут применяться по всем направлениям.

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

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

Контрольный список оптимизации запросов

  • Запустите UPDATE STATISTICS для базовых таблиц
    • Многие системы запускают это как запланированное еженедельное задание
  • Удалить записи из базовых таблиц (возможно архивировать удаленные записи)
    • Попробуйте сделать это автоматически один раз в день или раз в неделю.
  • Восстановить индексы
  • Перестроить таблицы (данные bcp из / в)
  • Сброс / перезагрузка базы данных (радикально, но может исправить повреждение)
  • Создайте новый, более подходящий индекс
  • Запустите DBCC, чтобы увидеть, возможно ли повреждение базы данных
  • Замки / тупики
    • Убедитесь, что в базе данных не запущены другие процессы
      • Особенно DBCC
    • Используете ли вы блокировку строки или уровня страницы?
    • Блокируйте таблицы исключительно перед началом запроса
    • Убедитесь, что все процессы обращаются к таблицам в одинаковом порядке.
  • Индексы используются должным образом?
    • Объединения будут использовать индекс только в том случае, если оба выражения имеют одинаковый тип данных
    • Индекс будет использоваться только в том случае, если в запросе сопоставляются первые поля индекса
    • Используются ли кластерные индексы в соответствующих случаях?
      • диапазон данных
      • ГДЕ поле между значением1 и значением2
  • Маленькие объединения - хорошие соединения
    • По умолчанию оптимизатор будет рассматривать только таблицы 4 одновременно.
    • Это означает, что в соединениях с более чем 4 таблицами у него есть хорошие шансы выбрать неоптимальный план запроса
  • Разбей Присоединиться
    • Вы можете разбить соединение?
    • Предварительно выбрать внешние ключи во временную таблицу
    • Сделайте половину соединения и поместите результаты во временную таблицу
  • Вы используете правильный вид временной таблицы?
    • #temp таблицы могут работать намного лучше, чем @table переменные с большими объемами (тысячи строк).
  • Поддерживать сводные таблицы
    • Сборка с триггерами на базовых таблицах
    • Сборка ежедневно / почасовая / и т. Д.
    • Построить ad-hoc
    • Построить пошагово или разорвать / перестроить
  • Посмотрите, что план запроса с SET SHOWPLAN ON
  • Посмотрите, что на самом деле происходит с SET STATS IO ON
  • Форсировать индекс с помощью прагмы: (index: myindex)
  • Принудительно настроить порядок таблиц, используя SET FORCEPLAN ON
  • Параметр Sniffing:
    • Разбить хранимую процедуру на 2
    • вызвать proc2 из proc1
    • позволяет оптимизатору выбирать индекс в proc2, если @parameter был изменен в proc1
  • Можете ли вы улучшить свое оборудование?
  • Во сколько ты бежишь? Есть ли более спокойное время?
  • Работает ли сервер репликации (или другой безостановочный процесс)? Вы можете приостановить это? Запустите это, например. ежечасно?
Источник
  1. Имейте довольно хорошее представление об оптимальном пути выполнения запроса в вашей голове.
  2. Проверяйте план запроса - всегда.
  3. Включите STATS, чтобы вы могли проверить производительность ввода-вывода и процессора. Сосредоточьтесь на уменьшении этих цифр, а не на времени запроса (поскольку это может зависеть от другой активности, кэша и т. Д.).
  4. Ищите большое количество строк, входящих в оператор, но небольшие числа, выходящие. Обычно индекс помогает, ограничивая количество входящих строк (что сохраняет чтение с диска).
  5. Сосредоточьтесь на самом большом стоимости поддерева в первую очередь. Изменение этого поддерева часто может изменить весь план запроса.
  6. Общие проблемы, которые я видел:
    • Если объединений много, иногда Sql Server решает расширить объединения, а затем применять предложения WHERE. Обычно это можно исправить, переместив условия WHERE в предложение JOIN или в производную таблицу со встроенными условиями. Представления могут вызвать те же проблемы.
    • Субоптимальные объединения (LOOP против HASH против MERGE). Мое эмпирическое правило состоит в том, чтобы использовать соединение LOOP, когда верхний ряд имеет очень мало строк по сравнению с нижним, MERGE, когда наборы примерно равны и упорядочены, и HASH для всего остального. Добавление подсказки о соединении позволит вам проверить свою теорию.
    • Параметр нюхает. Если вы сначала запустили хранимый процесс с нереалистичными значениями (скажем, для тестирования), то план кэшированных запросов может оказаться неоптимальным для ваших производственных значений. Повторный запуск с RECOMPILE должен проверить это. Для некоторых хранимых процедур, особенно тех, которые имеют дело с диапазонами различного размера (скажем, все даты между сегодняшним и вчерашним днем ​​- что повлечет за собой SEEX INDEX - или все даты между прошлым годом и этим годом - что было бы лучше при сканировании INDEX) вам, возможно, придется каждый раз запускать его с RECOMPILE.
    • Плохой отступ... Хорошо, так что Sql Server не имеет проблем с этим - но я уверен, что невозможно понять запрос, пока я не исправлю форматирование.
Источник

Немного не по теме, но если у вас есть контроль над этими проблемами...
Высокий уровень и высокая эффективность.

  • Для сред с высоким IO убедитесь, что ваши диски предназначены для RAID 10 или RAID 0+1 или для какой-либо вложенной реализации raid 1 и raid 0.
  • Не используйте диски менее 1500K.
  • Убедитесь, что ваши диски используются только для вашей базы данных. IE нет регистрации нет ОС.
  • Отключите автоматическое увеличение или подобную функцию. Позвольте базе данных использовать все ожидаемое хранилище. Не обязательно то, что в настоящее время используется.
  • спроектируйте свою схему и индексы для запросов типа.
  • если это таблица типа журнала (только для вставки) и должна быть в БД, не индексируйте ее.
  • если вы делаете много отчетов (сложные выборки с множеством объединений), то вам следует взглянуть на создание хранилища данных со схемой "звезда" или "снежинка".
  • Не бойтесь копировать данные в обмен на производительность!
Источник

CREATE INDEX

Убедитесь, что есть индексы, доступные для вашего WHERE а также JOIN статьи. Это значительно ускорит доступ к данным.

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

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

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

SELECT   i.make, i.model, i.price
FROM     dbo.inventory i
WHERE    i.color = 'red'
  AND    i.price BETWEEN 15000 AND 18000

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

Из этих вариантов индекса, idx01 обеспечивает более быстрый путь для удовлетворения запроса:

CREATE INDEX idx01 ON dbo.inventory (price, color)
CREATE INDEX idx02 ON dbo.inventory (color, price)

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

Мне известно, что есть два очень похожих индекса, отличающихся только порядком полей для ускорения запросов (имя, фамилия) в одном и (фамилия, имя) в другом.

Источник

Предполагая, что MySQL здесь, используйте EXPLAIN, чтобы узнать, что происходит с запросом, убедитесь, что индексы используются максимально эффективно, и попытайтесь устранить сортировку файлов. Высокопроизводительный MySQL: оптимизация, резервное копирование, репликация и многое другое - отличная книга на эту тему, как и MySQL Performance Blog.

Источник

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

UPDATE table
SET @variable = column = @variable + otherColumn

Или более читаемая версия:

UPDATE table
SET
    @variable = @variable + otherColumn,
    column = @variable

Я использовал это для замены сложных курсоров / объединений при реализации рекурсивных вычислений, а также значительно повысил производительность.

Вот подробности и пример кода, который сделал фантастические улучшения в производительности: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

Источник

@Terrapin Есть несколько других отличий между isnull и coalesce, о которых стоит упомянуть (кроме соответствия ANSI, которое для меня является большим).

Слияние против IsNull

Источник

Иногда в SQL Server, если вы используете ИЛИ в предложении where, это действительно ухудшит производительность. Вместо использования ИЛИ просто сделайте два выбора и объедините их вместе. Вы получаете те же результаты в 1000 раз быстрее.

Источник

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

Источник

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

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeUniqueColumn varchar(25) not null,
    SomeNotUniqueColumn varchar(50) null,
    unique(SomeUniqueColumn)
)
Источник

Посмотрите на предложение where - проверьте использование индексов / убедитесь, что ничего не делается глупо

where SomeComplicatedFunctionOf(table.Column) = @param --silly
Источник

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

Источник

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

Хорошей идеей было бы использовать memcached, где это возможно, так как это было бы намного быстрее, просто извлекая предварительно скомпилированные данные непосредственно из памяти, а не получая их из базы данных. Существует также разновидность MySQL, встроенная в memcached (сторонняя).

Источник

Я сделал привычкой всегда использовать переменные связывания. Возможно, переменные связывания не помогут, если СУБД не будет кэшировать операторы SQL. Но если вы не используете переменные связывания, СУБД не сможет повторно использовать планы выполнения запросов и проанализированные операторы SQL. Экономия может быть огромной: http://www.akadia.com/services/ora_bind_variables.html. Я работаю в основном с Oracle, но Microsoft SQL Server работает почти так же.

По моему опыту, если вы не знаете, используете ли вы переменные связывания, вы, вероятно, нет. Если язык вашего приложения не поддерживает их, найдите тот, который поддерживает. Иногда вы можете исправить запрос A, используя переменные связывания для запроса B.

После этого я разговариваю с нашим администратором баз данных, чтобы выяснить, что причиняет СУБД наибольшую боль. Обратите внимание, что вы не должны спрашивать "Почему этот запрос медленный?" Это все равно, что попросить своего врача удалить аппендикс. Конечно, ваш запрос может быть проблемой, но также вероятно, что что-то не так. Как разработчики, мы склонны мыслить в терминах строк кода. Если линия медленная, исправьте эту линию. Но СУБД - это действительно сложная система, и ваш медленный запрос может быть признаком гораздо более серьезной проблемы.

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

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

Источник

@ DavidM

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

В SQL Server план выполнения дает вам то же самое - он говорит вам, какие индексы поражены и т. Д.

Источник

Первый шаг: посмотрите на план выполнения запроса!
TableScan -> плохо
NestedLoop -> предупреждение
TableScan за NestedLoop -> DOOM!

НАСТРОЙКА СТАТИСТИКИ IO ON
УСТАНОВИТЬ ВРЕМЯ СТАТИСТИКИ

Источник

Выполнение запроса с использованием WITH (NoLock) в моем случае довольно стандартная операция. Любой, кто поймал запущенные запросы к таблицам в десятки гигабайт, без него вынимается и снимается.

Источник

Если возможно, конвертируйте запросы NOT IN в ЛЕВЫЕ НАРУЖНЫЕ СОЕДИНЕНИЯ. Например, если вы хотите найти все строки в Таблице 1, которые не используются внешним ключом в Таблице 2, вы можете сделать это:

SELECT *
FROM Table1
WHERE Table1.ID NOT IN (
    SELECT Table1ID
    FROM Table2)

Но вы получаете намного лучшую производительность с этим:

SELECT Table1.*
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.ID = Table2.Table1ID
WHERE Table2.ID is null
Источник 
SET NOCOUNT ON

Обычно первая строка в моих хранимых процедурах, если только мне не нужно использовать @@ROWCOUNT,

Источник
  • Префикс всех таблиц с dbo. предотвратить перекомпиляции.
  • Просмотр планов запросов и поиск таблиц / индексов сканирования.
  • В 2005 году изучите представления управления на предмет отсутствия индексов.
Источник

Удалите курсоры там, где они не нужны.

Источник

Удалите вызовы функций в Sprocs, где множество строк будет вызывать функцию.

Мой коллега использовал вызовы функций (в качестве примера получил lastlogindate из userid), чтобы вернуть очень широкие наборы записей.

С задачей оптимизации я заменил вызовы функции в sproc на код функции: время работы многих sprocs сократилось с> 20 секунд до < 1.

Источник

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

SELECT * FROM Orders (nolock) where UserName = 'momma'
Источник

Я люблю использовать

isnull(SomeColThatMayBeNull, '')

Над

coalesce(SomeColThatMayBeNull, '')

Когда мне не нужна поддержка нескольких аргументов, которую дает вам объединение.

http://blog.falafel.com/2006/04/05/SQLServerArcanaISNULLVsCOALESCE.aspx

Источник

Индексируйте таблицы с помощью clm, по которым вы фильтруете

Источник

Я высматриваю:

  • Разверните все циклы CURSOR и преобразуйте их в операторы UPDATE / INSERT.
  • Ищите любой код приложения, который:
    • Вызывает SP, который возвращает большой набор записей,
    • Затем в приложении проходит каждую запись и вызывает SP с параметрами для обновления записей.
    • Преобразуйте это в SP, который выполняет всю работу за одну транзакцию.
  • Любой SP, который делает много манипуляций со строками. Это свидетельствует о том, что данные не структурированы правильно / нормализованы.
  • Любые SP, которые заново изобретают колесо.
  • Любой ИП, которого я не могу понять, что он пытается сделать в течение минуты!
Источник

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

Источник

Грязные читает -

set transaction isolation level read uncommitted

Предотвращает мертвые блокировки, когда целостность транзакций не является абсолютно необходимой (что обычно верно)

Источник

Сначала я всегда обращаюсь к SQL Profiler (если это хранимая процедура с большим количеством уровней вложенности) или к планировщику выполнения запросов (если это несколько операторов SQL без вложенности). 90% времени вы можете сразу же найти проблему с помощью одного из этих двух инструментов.

Источник
Другие вопросы по тегам