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

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

where SomeComplicatedFunctionOf(table.Column) = @param --silly

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

@ DavidM

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

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

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

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

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

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

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

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

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

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeUniqueColumn varchar(25) not null,
    SomeNotUniqueColumn varchar(50) null,
    unique(SomeUniqueColumn)
)

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

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

isnull(SomeColThatMayBeNull, '')

Над

coalesce(SomeColThatMayBeNull, '')

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

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

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

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

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

1. Имейте довольно хорошее представление об оптимальном пути выполнения запроса в вашей голове.
2. Проверяйте план запроса - всегда.
3. Включите STATS, чтобы вы могли проверить производительность ввода-вывода и процессора. Сосредоточьтесь на уменьшении этих цифр, а не на времени запроса (так как это может зависеть от другой активности, кэша и т. Д.).
4. Ищите большое количество строк, входящих в оператор, но небольшие числа, выходящие. Обычно индекс помогает, ограничивая количество входящих строк (что сохраняет чтение с диска).
5. Сосредоточьтесь на самом большом стоимости поддерева в первую очередь. Изменение этого поддерева часто может изменить весь план запроса.
6. Общие проблемы, которые я видел:
   • Если объединений много, иногда Sql Server выбирает расширение объединений, а затем применяет предложения WHERE. Обычно это можно исправить, переместив условия WHERE в предложение JOIN или в производную таблицу со встроенными условиями. Представления могут вызвать те же проблемы.
   • Субоптимальные объединения (LOOP против HASH против MERGE). Мое эмпирическое правило состоит в том, чтобы использовать соединение LOOP, когда верхний ряд имеет очень мало строк по сравнению с нижним, MERGE, когда наборы примерно равны и упорядочены, и HASH для всего остального. Добавление подсказки о соединении позволит вам проверить свою теорию.
   • Параметр нюхает. Если вы сначала запустили хранимый процесс с нереалистичными значениями (скажем, для тестирования), то план кэшированных запросов может оказаться неоптимальным для ваших производственных значений. Выполнение снова с RECOMPILE должно проверить это. Для некоторых хранимых процедур, особенно тех, которые имеют дело с диапазонами различного размера (скажем, все даты между сегодняшним и вчерашним днем ​​- что повлечет за собой ПОИСК ИНДЕКСА - или все даты между прошлым годом и этим годом - что было бы лучше при сканировании ИНДЕКСА ) вам, возможно, придется каждый раз запускать его с RECOMPILE.
   • Плохой отступ ... Хорошо, так что у Sql Server с этим нет проблем, но я уверен, что не смогу понять запрос, пока не исправлю форматирование.

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

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

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

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

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

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

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

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

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

SET NOCOUNT ON

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

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

set transaction isolation level read uncommitted

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

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

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)

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

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

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

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

Вот удобный список вещей, которые я всегда даю тем, кто спрашивает меня об оптимизации.
В основном мы используем 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
• Можете ли вы улучшить свое оборудование?
• Во сколько ты бежишь? Есть ли более спокойное время?
• Работает ли сервер репликации (или другой безостановочный процесс)? Вы можете приостановить это? Запустите это, например. ежечасно?

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

SELECT * FROM Orders (nolock) where UserName = 'momma'

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

Уловка, которую я недавно узнал, заключается в том, что 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

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

Если возможно, конвертируйте 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

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

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

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