1. Для оптимизации запроса следует избегать полного сканирования таблицы и сначала рассмотреть возможность создания индекса по столбцам, задействованным в где и порядке по.
2. Старайтесь избегать суждения по полям в оговорке where, иначе это приведёт к отказу движка от использования индексов и выполнения полного сканирования таблицы, например:
Выберите идентификатор из T, где num — null
Вы можете установить значение по умолчанию 0 на num, убедиться, что в столбце num в таблице нет null-значения, и затем отправить запрос следующим образом:
Выберите идентификатор из T, где num=0
3. Старайтесь избегать использования операторов != или <> в клаузе where, иначе движок откажется от использования индексов и выполнит полнотабличное сканирование.
4. Старайтесь избегать использования OR в оговорке where для объединения условия, иначе это приведёт к отказу от использования индексов и выполнению полного сканирования таблицы, например:
Выберите ID из T, где num=10 или num=20
Вы можете задавать запросы вот так:
Выберите ID из T, где num=10
Union All
Выберите ID из T, где num=20
5.in и не входящие также следует использовать с осторожностью, иначе это приведёт к полному сканированию стола, например:
Выберите ID из T, где num in(1,2,3)
Для непрерывных значений не используйте in, если можно использовать между:
Выберите ID из T, где число от 1 до 3
6. Следующий запрос также приведёт к полному сканированию таблицы:
Выберите ID из T, где имя вроде '%abc%'
Для повышения эффективности рассмотрите полнотекстовый поиск.
7. Если использовать параметр в оговорке «where», это также вызовет полное сканирование таблицы. Поскольку SQL разрешает локальные переменные только во время выполнения, а оптимизатор не может отложить выбор планов доступа на время выполнения; Он должен быть выбран во время компиляции. Однако если план доступа создан во время компиляции, значение переменной остаётся неизвестным и, следовательно, не может использоваться как входной элемент для выбора индекса. Следующие заявления будут отсканированы полностью:
Выберите ID из T, где num=@num
Вы можете заставить запрос использовать индекс:
Выберите идентификатор из T с (index(index(index(имя индекса)), где num=@num
8. Старайтесь избегать выражения полей в оговорке where, что приведёт к отказу от использования индексов в пользу полного сканирования таблицы. Например:
Выберите идентификатор из T, где num/2=100
Следует изменить на:
Выберите ID из T, где num=100*2
9. Старайтесь избегать выполнения операций функций на полях из клаузы where, что приведёт к отказу движка от использования индексов в пользу полного сканирования таблицы. Например:
Выберите идентификатор из T, где substring(name,1,3)='ABC' --Name id, начинающийся с abc
Выберите идентификатор из T, где Datediff(Day,createdate,'2005-11-30')=0--'2005-11-30' генерируется id
Следует изменить на:
Выберите идентификатор из T, где имя вроде 'abc%'
выберите идентификатор из t, где createdate>='2005-11-30' и createdate<'2005-12-1'
10. Не выполняйте функции, арифметические операции или другие операции выражения слева от "=" в клаузе where, иначе система может неправильно использовать индекс.
11. При использовании индексного поля в качестве условия, если индекс является составным индексом, то первое поле индекса должно использоваться как условие, чтобы система использовала индекс, иначе индекс не будет использован, и порядок полей должен максимально соответствовать порядку индекса.
12. Не пишите бессмысленные запросы, например, генерируйте пустую структуру таблицы:
Выберите COL1,COL2 в #t из T, где 1=0
Этот тип кода не возвращает набор результатов, но потребляет системные ресурсы, поэтому его следует заменить примерно так:
создать таблицу #t(...)
13. Часто это хороший выбор для замены на существует:
Выберите NUM из A, где num в (выберите num из b)
Замените следующим утверждением:
Выберите NUM из A, где существует (выберите 1 из b, где num=a.num)
14. Не все индексы допустимы для запросов, SQL основан на данных в таблице для оптимизации запроса; если столбец индекса сильно дублирует данные, SQL-запросы могут не использовать индекс, например, таблица имеет пол поля, мужской и женский — почти половина, и даже если индекс построен на поле, он не влияет на эффективность запросов.
15. Чем больше индексов, тем лучше, индекс, безусловно, может повысить эффективность соответствующего select, но также снижает эффективность вставки и обновления, поскольку индекс может быть восстановлен при вставке или обновлении, поэтому процесс построения индекса необходимо тщательно рассматривать в зависимости от конкретной ситуации. Лучше не иметь более 6 индексов в таблице, а если их слишком много, подумать, нужно ли строить индексы на редко используемых столбцах.
16. Избегайте обновления столбцов сгруппированных индексных данных по мере возможности, поскольку порядок сгруппированных индексных столбцов соответствует физическому порядку хранения записей таблицы, и после изменения значения столбца это приведёт к корректировке порядка всех записей таблицы, что потребляет значительные ресурсы. Если вашему приложению нужно часто обновлять кластерные столбцы индекса, стоит подумать, стоит ли строить индекс как кластерный индекс.
17. Старайтесь использовать числовые поля и не создавать поля, содержащие только числовую информацию в виде символов, что снизит производительность запросов и соединений, а также увеличит нагрузку на хранение. Это связано с тем, что движок сравнивает каждый символ строки по отдельности при обработке запросов и объединений, тогда как для числовых типов их нужно сравнивать только один раз.
18. Используйте varchar/nvarchar вместо char/nchar как можно больше, потому что, во-первых, более длинное поле может сэкономить место, а во-вторых, для запросов эффективность поиска в относительно небольшом поле явно выше.
19. Нигде не используйте select * из t, замените "*" на конкретный список полей и не возвращайте неиспользуемые поля.
20. Попробуйте использовать переменные таблицы вместо временных. Если переменная таблицы содержит большое количество данных, обратите внимание, что индекс очень ограничен (только индекс первичного ключа).
21. Избегайте частого создания и удаления временных таблиц для снижения потребления ресурсов системной таблицы.
22. Временные таблицы не являются непригодными, и правильное их использование может сделать некоторые процедуры более эффективными, например, когда приходится многократно ссылаться на набор данных в большой таблице или в часто используемой таблице. Однако для одноразовых событий лучше использовать таблицу экспорта.
23. При создании временной таблицы, если объём вставленных данных за один раз велик, то можно выбрать в вместо create таблицы, чтобы избежать увеличения скорости большого количества логов; Если объём данных небольшой, чтобы облегчить ресурсы системной таблицы, сначала создайте таблицу, а затем вставьте её.
24. Если используется временная таблица, обязательно явно удаляйте все временные таблицы в конце сохранённой процедуры, сначала обрезайте таблицу, а затем уберите таблицу, чтобы избежать длительной блокировки системной таблицы.
25. Старайтесь избегать использования курсора, потому что его эффективность низкая; если данные курсора превышают 10 000 строк, стоит рассмотреть возможность переписывания.
26. Решения на основе множеств следует искать для решения задач перед использованием методов на основе курсора или временных таблиц, которые часто бывают более эффективными.
27. Как и временные столы, курсоры не непригодны для использования. Использование FAST_FORWARD курсоров для небольших наборов данных часто лучше, чем другие методы обработки строк за строкой, особенно если нужно ссылаться на несколько таблиц для получения нужных данных. Процедуры, включающие «total» в набор результатов, обычно быстрее, чем те, что выполняются курсором. Если позволяет время на разработку, можно попробовать как курсорные, так и множество методы, чтобы понять, какой из них работает лучше.
28. Поставьте SET NOCOUNT ON в начале всех хранящихся процедур и триггеров, и SET NOCOUNT OFF в конце. Нет необходимости отправлять DONE_IN_PROC сообщения клиенту после выполнения каждого оператора сохранённой процедуры и триггера.
29. Старайтесь избегать крупных транзакций и повышать параллельную пропускную способность системы.
30. Старайтесь избегать возврата больших данных клиенту; если объём данных слишком велик, стоит оценить, насколько разумен соответствующий спрос. |
Опубликовано 27.01.2015 20:09:54
|
|
|
