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

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

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

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

@ DavidM

Assuming MySQL here, use EXPLAIN to find out what is going on with the query, make sure that the indexes are being used as efficiently as possible...

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

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

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

Иногда в SQL Server, если вы используете OR в предложении 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, '')

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

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

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

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

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

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

Я ищу:

• Разверните все циклы CURSOR и преобразуйте их в операторы UPDATE / INSERT на основе набора.
• Обратите внимание на любой код приложения, который:
  • Вызывает SP, который возвращает большой набор записей,
  • Затем в приложении просматривает каждую запись и вызывает 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, что для меня очень важно).

Coalesce против 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 -> bad
NestedLoop -> предупреждение TableScan за вложенной петлей -> DOOM!

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

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

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

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

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

• Запустите ОБНОВЛЕНИЕ СТАТИСТИКИ в базовых таблицах
  • Многие системы запускают это как запланированное еженедельное задание.
• Удалить записи из базовых таблиц (возможно, заархивировать удаленные записи)
  • Попробуйте делать это автоматически один раз в день или раз в неделю.
• Восстановить индексы
• Перестроить таблицы (выход / вход данных bcp)
• Дамп / перезагрузка базы данных (радикально, но может исправить повреждение)
• Создайте новый, более подходящий индекс
• Запустите DBCC, чтобы увидеть, есть ли возможное повреждение в базе данных
• Замки / тупиковые ситуации
  • Убедитесь, что в базе данных не запущены другие процессы
    • Особенно DBCC
  • Вы используете блокировку на уровне строк или страниц?
  • Блокируйте таблицы исключительно перед запуском запроса
  • Убедитесь, что все процессы обращаются к таблицам в одном и том же порядке.
• Правильно ли используются индексы?
  • Объединения будут использовать индекс только в том случае, если оба выражения имеют один и тот же тип данных.
  • Индекс будет использоваться только в том случае, если в запросе совпадают первые поля индекса.
  • Используются ли кластерные индексы там, где это необходимо?
    • данные диапазона
    • Поле WHERE между значением1 и значением2
• Маленькие объединения - хорошие объединения
  • По умолчанию оптимизатор будет рассматривать только 4 таблицы за раз.
  • Это означает, что в соединениях с более чем 4 таблицами у него есть хорошие шансы выбрать неоптимальный план запроса.
• Разбейте соединение
  • Можете ли вы разбить соединение?
  • Предварительно выберите внешние ключи во временную таблицу
  • Сделайте половину соединения и поместите результаты во временную таблицу
• Вы используете правильную временную таблицу?
  • Таблицы #temp могут работать намного лучше, чем переменные @table с большими объемами (тысячи строк).
• Ведение сводных таблиц
  • Создавайте с помощью триггеров в базовых таблицах
  • Строить ежедневно / ежечасно / и т. д.
  • Сборка ad-hoc
  • Построить постепенно или разобрать / перестроить
• Посмотрите, какой план запроса, с помощью SET SHOWPLAN ON
• Посмотрите, что на самом деле происходит, с помощью SET STATS IO ON
• Форсировать индекс с помощью прагмы: (index: myindex)
• Установите порядок таблиц с помощью SET FORCEPLAN ON
• Обнюхивание параметров:
  • Разбить хранимую процедуру на 2
  • вызвать proc2 из proc1
  • позволяет оптимизатору выбрать индекс в proc2, если параметр @parameter был изменен процедурой proc1
• Можете ли вы улучшить свое оборудование?
• Во сколько ты бежишь? Есть время поспокойнее?
• Работает ли сервер репликации (или другой непрерывный процесс)? Вы можете приостановить это? Запустите его, например. ежечасно?

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

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 в LEFT OUTER JOINS, если это возможно. Например, если вы хотите найти все строки в Table1, которые не используются внешним ключом в Table2, вы можете сделать это:

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 из идентификатора пользователя) для возврата очень широких наборов записей.

Задавшись оптимизацией, я заменил вызовы функций в sproc на код функции: у меня время работы многих sproc уменьшилось с> 20 секунд до