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

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

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

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

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

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

1. Имейте довольно хорошее представление об оптимальном пути выполнения запроса в вашей голове.
2. Проверьте план запроса-всегда.
3. Включите функцию STATS, чтобы проверить производительность как IO, так и CPU. Сосредоточьтесь на снижении этих чисел, не обязательно на времени запроса (так как на это может повлиять другая активность, кэш и т. д.).
4. Ищите большое количество строк, входящих в оператор, но маленькие числа, выходящие из него. Обычно индекс помогает, ограничивая количество входящих строк (что экономит чтение с диска).
5. Сначала сосредоточьтесь на самом большом поддереве затрат. Изменение этого поддерева часто может изменить весь план запроса.
6. Общие проблемы, которые я видел, таковы:
   • Если существует много соединений, иногда сервер Sql решит развернуть соединения, а затем применить предложения WHERE. Обычно это можно исправить, переместив условия WHERE в предложение JOIN или производную таблицу со встроенными условиями. Представления могут вызвать те же проблемы.
   • Субоптимальные соединения (LOOP vs HASH vs MERGE). Мое эмпирическое правило состоит в том, чтобы использовать соединение LOOP, когда верхняя строка имеет очень мало строк по сравнению с нижней, a MERGE, когда наборы примерно равны и упорядочены, и A HASH для всего остального. Добавление подсказки join позволит вам проверить свою теорию.
   • Параметр нюхает. Если вы сначала запустили сохраненный proc с нереалистичными значениями (скажем, для тестирования), то кэшированный план запроса может быть неоптимальным для ваших производственных значений. Повторный запуск с перекомпиляцией должен подтвердить это. Для некоторых хранимых процессов, особенно тех, которые имеют дело с диапазонами разного размера (скажем, все даты между сегодняшним и вчерашним днем - что повлечет за собой поиск индекса - или все даты между прошлым и этим годом - что было бы лучше с индексным сканированием), вам, возможно, придется запускать его с перекомпиляцией каждый раз.
   • Плохо indentation...Okay, так что Sql сервер не имеет проблемы с этим - но я уверен, что невозможно понять запрос, пока я не исправил форматирование.

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

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

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)

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

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

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

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

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

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

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

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

Преобразуйте не в запросах к левым внешним соединениям, если это возможно. Например если вы хотите найти все строки в Таблице 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

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

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

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

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

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

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

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

После этого я поговорю с нашим DBA, чтобы выяснить, что вызывает у RDBMS самую сильную боль. Обратите внимание, что вы не должны спрашивать "Why is this query slow?", это все равно что просить вашего врача удалить вам аппендикс. Конечно, ваш запрос может быть проблемой, но так же вероятно, что что-то еще идет не так. Как разработчики, мы склонны думать в терминах строк кода. Если линия медленная, исправьте эту линию. Но a RDBMS-это действительно сложная система, и ваш медленный запрос может быть симптомом гораздо большей проблемы.

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

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

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

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

Выполнение запроса с использованием WITH (NoLock) - довольно стандартная операция в моем месте. Любой, кто поймал выполнение запросов на таблицах tens-of-gigabytes без него, вынимается и расстреливается.

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

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

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

@ DavidM

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

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

Индекс таблица(ы), цлм(ы) фильтр

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

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

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

Я присматриваю за тобой:

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

SET NOCOUNT ON

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

Удалите курсоры везде, где они не являются необходимыми.

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

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

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