Какие общие методы могут применяться для оптимизации SQL-запросов?

Какие методы могут быть эффективно применены для повышения производительности SQL-запросов? Существуют ли какие-либо общие правила?

Ответ 1

Использовать первичные ключи
Избегайте выбора *
Будьте максимально конкретны при построении условных операторов
Де-нормализация часто может быть более эффективной
Табличные переменные и временные таблицы (если они доступны) часто будут лучше, чем использование большой исходной таблицы
Разделенные представления
Использовать индексы и ограничения

Ответ 2

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

Индексы (не только то, что они есть, но и то, как они работают).
Кластерные индексы против таблиц, разделенных кучей.
Текстовые и двоичные запросы и когда они могут быть выстроены в линию.
Фактор заполнения.
Как записываются записи для обновления/удаления.
Когда происходит разделение страниц и почему.
Статистика, и как они влияют на различные скорости запросов.
Планировщик запросов и то, как он работает для вашей конкретной базы данных (например, в некоторых системах "select *" работает медленно, на современных MS-Sql DB, которые планировщик может обработать).

Ответ 3

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

В стороне, когда я начал учиться оптимизировать запросы, я запустил профилировщик запросов sql server против трассировки, посмотрел на сгенерированный SQL и попытался выяснить, почему это было улучшением. Профилировщик запросов далеко не оптимален, но это достойный старт.

Ответ 4

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

Убедитесь, что у вас есть минимум данных. Убедитесь, что вы выбрали только нужные столбцы. Сократите размеры полей до минимума.
Рассмотрим де-нормализацию вашей базы данных для сокращения объединений
Избегайте циклов (т.е. курсоров выборки), придерживайтесь заданий.
Реализовать запрос как хранимую процедуру, поскольку он предварительно скомпилирован и будет выполняться быстрее.
Убедитесь, что у вас установлены правильные индексы. Если ваша база данных используется в основном для поиска, то рассмотрите больше индексов.
Используйте план выполнения, чтобы увидеть, как выполняется обработка. То, что вы хотите избежать, - это сканирование таблицы, поскольку это дорого.
Убедитесь, что для автоматической статистики установлено значение "включено". SQL нуждается в этом, чтобы помочь решить оптимальное выполнение. См. Майк Гундерлой отличный пост для получения дополнительной информации. Основы статистики в SQL Server 2005
Убедитесь, что ваши индексы не фрагментированы. Уменьшение фрагментации индекса SQL Server
Убедитесь, что ваши таблицы не фрагментированы. Как определить фрагментацию таблицы в SQL Server 2000 и 2005

Ответ 5

Используйте элемент с для обработки фильтрации запросов. Ограничьте каждый подзапрос минимальным количеством строк. затем присоедините подзапросы.

WITH
master AS
(
    SELECT SSN, FIRST_NAME, LAST_NAME
    FROM MASTER_SSN
    WHERE STATE = 'PA' AND
          GENDER = 'M'
),
taxReturns AS
(
    SELECT SSN, RETURN_ID, GROSS_PAY
    FROM MASTER_RETURNS
    WHERE YEAR < 2003 AND
          YEAR > 2000
)
SELECT *
FROM master,
     taxReturns
WHERE master.ssn = taxReturns.ssn

Подзапросы в операторе with могут в конечном итоге совпадать с внутренними представлениями, или автоматически созданных временных таблиц. Я нахожу в работе, которую я делаю, розничные данные, что примерно в 70-80% случаев, есть преимущество в производительности.

100% времени, есть преимущество в обслуживании.

Ответ 6

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

Ответ 7

В Oracle вы можете посмотреть пояснить план, чтобы сравнить варианты по вашему запросу

Ответ 8

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

Ответ 9

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

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

Ответ 10

Индексы
Статистика
в стеке microsoft, советник по настройке ядра базы данных

Ответ 11

Некоторые другие точки (Mine основаны на SQL-сервере, поскольку каждый бэкенд имеет свои собственные реализации, которые могут быть или не соответствовать true для всех баз данных):

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

Создайте свои таблицы, чтобы использовать правильные типы данных, чтобы избежать необходимости применять к ним функции для получения данных. Гораздо сложнее делать математику дат, когда вы храните свои данные как varchar, например.

Если вы обнаружите, что часто выполняете объединения, в которых есть функции, вам нужно подумать о перепроектировании таблиц.

Если условия WHERE или JOIN включают операторы OR (которые медленнее), вы можете получить более высокую скорость, используя инструкцию UNION.

UNION ALL быстрее, чем UNION, если (и только если) две записи взаимоисключающие и возвращают одинаковые результаты в любом случае.

NOT EXISTS обычно быстрее, чем NOT IN, или с использованием левого соединения с предложением WHERE ID = null

В запросе UPDATE добавьте условие WHERE, чтобы убедиться, что вы не обновляете уже равные значения. Разница между обновлением 10 000 000 записей и 4 может быть весьма значимой!

Рассмотрим предварительные вычисления некоторых значений, если вы будете часто их запрашивать или для больших отчетов. Сумма значений в заказе должна выполняться только в том случае, если заказ сделан или скорректирован, а не когда вы суммируете результаты в 10 000 000 000 заказов в отчете. Предварительные вычисления должны выполняться в триггерах, чтобы они всегда были актуальными, это базовые изменения данных. И это тоже не должно быть просто число, у нас есть вычисленное поле, которое объединяет имена, которые мы используем в отчетах.

Будьте осторожны с скалярными UDF, они могут быть медленнее, чем ввод кода в строку.

Таблица Temp, как правило, быстрее для больших наборов данных и табличных переменных быстрее для небольших. Кроме того, вы можете индексировать временные таблицы.

Форматирование обычно быстрее в пользовательском интерфейсе, чем в SQL.

Не возвращайте больше данных, чем вам действительно нужно.

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

Очень плохая идея создавать представления, которые вызывают другие представления, которые вызывают другие представления. Вы можете обнаружить, что вы присоединяетесь к той же таблице 6 раз, когда вам нужно только один раз и создать 100 000,00 записей в базовом представлении, чтобы получить 6, которые находятся в конечном результате.

При проектировании базы данных подумайте о том, как вводить данные не только пользовательский интерфейс. Данные бесполезны, если они не используются, поэтому подумайте о том, как они будут использоваться после того, как они будут в базе данных, и как эти данные будут поддерживаться или проверяться. Это часто меняет дизайн. (Это одна из причин, по которой плохое представление о том, чтобы ORM проектировал ваши таблицы, он думает только об одном случае использования данных). Наиболее сложные запросы, влияющие на большинство данных, представлены в отчетах, поэтому при разработке изменений в предоставлении отчетов могут значительно ускорить выполнение запросов (и упростить их).

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

И поскольку это нельзя сказать слишком часто, используйте индексы правильно, не слишком много или слишком мало. И сделайте предложения WHERE доступными (можно использовать индексы).