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

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

20.08.2008 20:45:15
Вот некоторые приемы оптимизации запросов SQL Server
SQLMenace 22.09.2008 14:14:25
Я согласен, что это не конструктивно и его можно искать в Google, но почему у него 118 уф ?! :)
FLICKER 17.05.2016 20:36:03
29 ОТВЕТОВ

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

where SomeComplicatedFunctionOf(table.Column) = @param --silly
2
20.08.2008 20:48:24

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

5
20.08.2008 20:48:53
Это хорошо для MySQL, но вопрос был помечен как "sqlserver". Тем не менее, это хорошая вещь, чтобы сделать это. Аналогичная вещь, которую нужно сделать в SSMS, это использовать «Просмотр предполагаемого плана выполнения» и «Включить фактический план выполнения». Если вы можете исключить сканирование огромных таблиц и использовать поиск по кластерным индексам, то вы на пути к оптимальной производительности.
eksortso 15.05.2009 22:48:50

@ DavidM

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

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

1
23.05.2017 12:17:21

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

2
20.08.2008 20:52:02

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

1
20.08.2008 20:52:43

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

3
20.08.2008 20:56:36
Я видел это необъяснимое поведение.
Esen 19.03.2013 15:58:39

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

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

1
20.08.2008 20:56:56
  • Префикс всех таблиц с dbo. предотвратить перекомпиляции.
  • Просмотр планов запросов и поиск таблиц / индексов сканирования.
  • В 2005 году изучите представления управления на предмет отсутствия индексов.
0
20.08.2008 20:58:50

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

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeUniqueColumn varchar(25) not null,
    SomeNotUniqueColumn varchar(50) null,
    unique(SomeUniqueColumn)
)
2
20.08.2008 21:00:37

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

1
20.08.2008 21:01:10

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

isnull(SomeColThatMayBeNull, '')

Над

coalesce(SomeColThatMayBeNull, '')

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

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

0
20.08.2008 21:03:53

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

0
20.08.2008 21:16:41
Вы на самом деле отметили это? Если SQL Server делает то, что разумно (используя алгоритм хеширования для определения местоположения хранимого процесса), это не имеет значения. На самом деле, если бы SQL Server этого не делал, похоже, что производительность системы снизилась бы (поскольку он, вероятно, вызывает свои собственные процессы).
John Stauffer 22.09.2008 14:52:21
Я думаю, что это падает на ведро преждевременной оптимизации. Вероятно, это хорошая практика, чтобы избежать путаницы для людей, но как совет по оптимизации ... D-
JohnFx 8.01.2009 23:38:50

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

  • Для сред с высоким IO убедитесь, что ваши диски предназначены для RAID 10 или RAID 0 + 1 или для какой-либо вложенной реализации raid 1 и raid 0.
  • Не используйте диски менее 1500K.
  • Убедитесь, что ваши диски используются только для вашей базы данных. IE нет входа в систему, нет ОС.
  • Отключите автоматическое увеличение или подобную функцию. Позвольте базе данных использовать все ожидаемое хранилище. Не обязательно то, что в настоящее время используется.
  • спроектируйте свою схему и индексы для запросов типа.
  • если это таблица типа журнала (только для вставки) и должна быть в БД, не индексируйте ее.
  • если вы делаете много отчетов (сложные выборки с множеством объединений), то вам следует взглянуть на создание хранилища данных со схемой «звезда» или «снежинка».
  • Не бойтесь копировать данные в обмен на производительность!
18
20.08.2008 21:14:11
  1. Имейте довольно хорошее представление об оптимальном пути выполнения запроса в вашей голове.
  2. Проверяйте план запроса - всегда.
  3. Включите STATS, чтобы вы могли проверить производительность ввода-вывода и процессора. Сосредоточьтесь на уменьшении этих цифр, а не на времени запроса (так как это может зависеть от другой активности, кэша и т. Д.).
  4. Ищите большое количество строк, входящих в оператор, но небольшие числа, выходящие. Обычно индекс помогает, ограничивая количество входящих строк (что сохраняет чтение с диска).
  5. Сосредоточьтесь на самом большом стоимости поддерева в первую очередь. Изменение этого поддерева часто может изменить весь план запроса.
  6. Общие проблемы, которые я видел:
    • Если объединений много, иногда Sql Server выбирает расширение объединений, а затем применяет предложения WHERE. Обычно это можно исправить, переместив условия WHERE в предложение JOIN или в производную таблицу со встроенными условиями. Представления могут вызвать те же проблемы.
    • Субоптимальные объединения (LOOP против HASH против MERGE). Мое эмпирическое правило состоит в том, чтобы использовать соединение LOOP, когда верхний ряд имеет очень мало строк по сравнению с нижним, MERGE, когда наборы примерно равны и упорядочены, и HASH для всего остального. Добавление подсказки о соединении позволит вам проверить свою теорию.
    • Параметр нюхает. Если вы сначала запустили хранимый процесс с нереалистичными значениями (скажем, для тестирования), то план кэшированных запросов может оказаться неоптимальным для ваших производственных значений. Выполнение снова с RECOMPILE должно проверить это. Для некоторых хранимых процедур, особенно тех, которые имеют дело с диапазонами различного размера (скажем, все даты между сегодняшним и вчерашним днем ​​- что повлечет за собой ПОИСК ИНДЕКСА - или все даты между прошлым годом и этим годом - что было бы лучше при сканировании ИНДЕКСА ) вам, возможно, придется каждый раз запускать его с RECOMPILE.
    • Плохой отступ ... Хорошо, так что у Sql Server с этим нет проблем, но я уверен, что не смогу понять запрос, пока не исправлю форматирование.
19
20.08.2008 21:48:53
+1 за включение плохого отступа. Форматирование является ключевым! :)
mwigdahl 21.07.2011 19:13:44

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

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

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

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

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

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

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

2
20.08.2008 22:47:59

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

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

5
20.08.2008 23:33:41
SET NOCOUNT ON

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

1
21.08.2008 03:31:48
@@ ROWCOUNT устанавливается в любом случае. NOCOUNT отключает операторы «затронутые строки xx».
Sklivvz 1.10.2008 08:36:49
Имеет ли это когда-нибудь заметную разницу в производительности?
JohnFx 8.01.2009 23:37:30
Да, тогда счетчик не вычисляется автоматически каждый раз, когда выполняется оператор SQL. Достаточно легко сопоставить запрос с и без, чтобы увидеть, что он действительно имеет значение.
travis 16.01.2009 20:44:46
Счет в любом случае отслеживается в SQL Server. Любая разница в производительности, которую вы видите, заключается в том, что счетчики должны пройти через сеть к вашему интерфейсу. Если вы делаете один выбор, это не будет иметь заметного значения. Если у вас есть цикл с 100000 вставками, это намного больше по сети.
Tom H 21.01.2009 15:14:11

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

set transaction isolation level read uncommitted

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

0
5.01.2009 19:19:01
Да, но это может привести к странным ошибкам, которые ОЧЕНЬ трудно найти.
Grant Johnson 27.10.2008 18:12:57

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

1
5.03.2009 17:40:33
Да, курсоры - это проклятие! ;)
Sklivvz 1.10.2008 08:37:25
Тьфу. Не выбрасывайте это безоговорочно. Курсоры похожи на оружие. Они не плохие сами по себе, просто люди делают с ними действительно плохие вещи.
JohnFx 8.01.2009 23:36:51

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)

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

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

8
28.08.2008 03:34:35

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

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

2
16.09.2008 20:01:44

Вот удобный список вещей, которые я всегда даю тем, кто спрашивает меня об оптимизации.
В основном мы используем 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
  • Можете ли вы улучшить свое оборудование?
  • Во сколько ты бежишь? Есть ли более спокойное время?
  • Работает ли сервер репликации (или другой безостановочный процесс)? Вы можете приостановить это? Запустите это, например. ежечасно?
114
22.01.2015 15:39:26
на какой бит ты ссылаешься?
AJ. 9.10.2008 13:34:34
Это классная штука, но я бы хотел, чтобы у вас были ссылки на некоторые претензии. Например: я никогда не слышал, чтобы оптимизация учитывала только 4 таблицы за раз в соединении. Я не понимаю, как это может быть правильно. Не могли бы вы предоставить некоторые ссылки для этого, в частности? Я хотел бы видеть, где вы получаете это.
SheldonH 25.01.2016 15:09:53

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

SELECT * FROM Orders (nolock) where UserName = 'momma'
1
19.09.2008 16:01:20
NOLOCK предназначен только для запросов, для которых вы не заботитесь о правильных результатах
Mark Sowul 19.10.2012 13:15:21

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

2
22.09.2008 14:26:08
Это следует использовать разумно, а не привычно. Запирание не зло, просто неправильно понято.
user565869 12.12.2014 23:29:19

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

6
23.01.2009 17:42:07

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

0
30.01.2009 15:42:08

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

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

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

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

1
5.08.2009 10:55:52