После тестирования был выполнен условный запрос к таблице, содержащей более 4 миллионов записей, и время запроса достигало 40 секунд. Поэтому очень важно понять, как повысить эффективность запросов к SQL-операторам. Ниже приведены несколько методов оптимизации запросов, которые широко распространены в Интернете:
Прежде всего, если объём данных большой, постарайтесь избегать сканирования всей таблицы и рассмотреть возможность построения индексов на столбцах, задействованных в where и order by, что может значительно ускорить поиск данных. Однако есть ситуации, когда индексирование не работает:
1. Старайтесь избегать использования операторов != или <> в клаузе where, иначе движок откажется от использования индексов и выполнит полное сканирование таблицы.
2. Старайтесь избегать суждения по полям в оговорке where, иначе движок откажется от использования индексов и выполнит полное сканирование таблицы, например:
Выберите идентификатор из T, где num — null
Вы можете установить значение по умолчанию 0 на num, убедиться, что в столбце num в таблице нет null-значения, и затем отправить запрос следующим образом:
Выберите идентификатор из T, где num=0
3. Старайтесь избегать использования OR в клаузе where для присоединения условий, иначе движок перестанет использовать индекс и выполнит полное сканирование таблицы, например:
Выберите ID из T, где num=10 или num=20
Вы можете задавать запросы вот так:
Выберите ID из T, где num=10
Union All
Выберите ID из T, где num=20
4. Следующий запрос также приведёт к полному сканированию таблицы:
Выберите ID из T, где имя вроде '%abc%'
Для повышения эффективности рассмотрите полнотекстовый поиск.
5. Также следует использовать пункт входа, а не вход, иначе это приведёт к полному сканированию таблицы, например:
Выберите ID из T, где num in(1,2,3)
Для непрерывных значений, если можно использовать промежуточное, не используйте в:
Выберите ID из T, где число от 1 до 3
6. Если использовать параметр из оговорки where, это также приведёт к сканированию полной таблицы. Поскольку SQL разрешает локальные переменные только во время выполнения, а оптимизатор не может отложить выбор планов доступа на время выполнения; Он должен быть выбран во время компиляции. Однако если план доступа создан во время компиляции, значение переменной остаётся неизвестным и, следовательно, не может использоваться как входной элемент для выбора индекса. Следующие заявления будут отсканированы полностью:
Выберите ID из T, где num=@num
Вы можете заставить запрос использовать индекс:
Выберите идентификатор из T с (index(index(index(имя индекса)), где num=@num
7. Старайтесь избегать выражения полей в клаузе where, что приведёт к тому, что движок прекратит использование индекса и выполнит полное сканирование таблицы. Например:
Выберите идентификатор из T, где num/2=100
Следует изменить на:
Выберите ID из T, где num=100*2
8. Старайтесь избегать выполнения операций функций над полями в клаузе where, что приведёт к отказу от использования индексов и выполнения полного сканирования таблицы. Например:
Выберите идентификатор из T, где substring(name,1,3)='abc' – идентификатор имени, начинающийся с abc
Выберите идентификатор из T, где datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ генерируется id
Следует изменить на:
Выберите идентификатор из T, где имя вроде 'abc%'
выберите идентификатор из t, где createdate>='2005-11-30′ и createdate<'2005-12-1′
9. Не выполняйте функции, арифметические операции или другие операции выражения слева от "=" в клаузе where (where), иначе система может неправильно использовать индекс.
10. При использовании индексного поля в качестве условия, если индекс является составным индексом, то первое поле индекса должно быть использовано как условие, чтобы система использовала индекс, иначе индекс не будет использован, и порядок полей должен максимально соответствовать порядку индекса.
11. Не пишите бессмысленные запросы, например, генерируйте пустую структуру таблицы:
Выберите COL1,COL2 в #t из T, где 1=0
Этот тип кода не возвращает набор результатов, но потребляет системные ресурсы, поэтому его следует заменить примерно так:
создать таблицу #t(...)
12. Часто это хороший выбор для использования существует вместо того, чтобы в:
Выберите NUM из A, где num в (выберите num из b)
Замените следующим утверждением:
Выберите NUM из A, где существует (выберите 1 из b, где num=a.num)
На что стоит обратить внимание при построении индекса:
1. Не все индексы допустимы для запросов, SQL основан на данных в таблице для оптимизации запроса; если столбец индекса сильно дублирует данные, SQL-запросы могут не использовать индекс, например, в таблице поля пол, мужской, женский почти половина, и даже если индекс построен на поле, он не будет играть роли в эффективности запросов.
2. Чем больше индексов, тем лучше, индекс может повысить эффективность соответствующего select, но также снижает эффективность вставки и обновления, поскольку индекс может быть восстановлен при вставке или обновлении, поэтому процесс построения индекса необходимо тщательно рассматривать в зависимости от конкретной ситуации. Лучше не иметь более 6 индексов в таблице, а если их слишком много, подумать, нужно ли строить индексы на редко используемых столбцах.
3. Избегайте обновления столбцов кластерированных индексных данных по мере необходимости, поскольку порядок кластерных индексированных столбцов соответствует порядку физического хранения записей таблицы, и после изменения значения столбцов это приведёт к корректировке порядка всех записей таблицы, что потребляет значительные ресурсы. Если прикладной системе необходимо часто обновлять кластерные столбцы индекса, она должна рассмотреть, стоит ли строить индекс как кластерный индекс.
Другие моменты, на которые стоит обратить внимание:
1. Старайтесь использовать числовые поля и не проектировать поля, содержащие только числовую информацию в виде символов, что снизит производительность запросов и соединений, а также увеличит нагрузку на хранение. Это связано с тем, что движок сравнивает каждый символ строки по отдельности при обработке запросов и объединений, тогда как для числовых типов их нужно сравнивать только один раз.
2. Не используйте select * из t нигде, замените "*" на конкретный список полей и не возвращайте неиспользуемые поля.
3. Попробуйте использовать переменные таблицы вместо временных. Если переменная таблицы содержит большое количество данных, обратите внимание, что индекс очень ограничен (только индекс первичного ключа).
4. Избегайте частого создания и удаления временных таблиц для снижения потребления ресурсов системных таблиц.
5. Временные таблицы не являются непригодными, и их правильное использование может сделать определённые процедуры более эффективными, например, когда нужно многократно ссылаться на большую таблицу или набор данных в часто используемой таблице. Однако для одноразовых событий лучше использовать таблицу экспорта.
6. При создании временной таблицы, если объём данных за один раз велик, вы можете выбрать в вместо create таблицы, чтобы избежать увеличения скорости избытка журналов; Если объём данных небольшой, чтобы облегчить ресурсы системной таблицы, сначала создайте таблицу, а затем вставьте её.
7. Если используется временная таблица, обязательно явно удаляйте все временные таблицы в конце сохранённой процедуры, сначала усекайте таблицу, а затем убрайте таблицу, чтобы избежать длительной блокировки системной таблицы.
8. Старайтесь избегать использования курсора, потому что его эффективность низкая; если данные превышают 10 000 строк, стоит рассмотреть возможность переписывания.
9. Перед использованием метода на основе курсора или временной таблицы следует сначала поискать решения на основе множеств для решения задачи, и метод на основе множеств обычно более эффективен.
10. Как и временные столы, курсор не непригоден для использования. Использование FAST_FORWARD курсоров для небольших наборов данных часто лучше, чем другие методы обработки строк за строкой, особенно если нужно ссылаться на несколько таблиц для получения нужных данных. Процедуры, включающие «total» в набор результатов, обычно быстрее, чем те, что выполняются курсором. Если позволяет время на разработку, можно попробовать как курсорные, так и множество методы, чтобы понять, какой из них работает лучше.
11. Поставьте SET NOCOUNT ON в начале всех хранящихся процедур и триггеров, а SET NOCOUNT OFF — в конце. Нет необходимости отправлять DONE_IN_PROC сообщения клиенту после выполнения каждого оператора сохранённой процедуры и триггера.
12. Старайтесь избегать возврата больших данных клиенту; если объём данных слишком велик, стоит оценить, насколько разумен соответствующий спрос.
13. Старайтесь избегать крупных транзакций и улучшать возможность параллелизма системы.
|
Опубликовано 11.05.2018 13:57:06
|
|
|
Опубликовано 17.05.2018 10:12:27