Після тестування умовний запит виконувався до таблиці з понад 4 мільйонами записів, а час запиту сягав 40 секунд. Тому дуже важливо зрозуміти, як підвищити ефективність запитів до SQL-операторів. Нижче наведено кілька методів оптимізації операторів запитів, які широко поширені в Інтернеті:
По-перше, коли обсяг даних великий, слід уникати сканування всієї таблиці і розглянути побудову індексів на стовпцях, які беруть участь, і порядок по, що може значно прискорити пошук даних. Однак існують ситуації, коли індексування не працює:
1. Намагайтеся уникати використання операторів != або <> у клаузі where, інакше рушій відмовиться від використання індексів і виконає повне сканування таблиці.
2. Намагайтеся уникати нульового оцінювального судження щодо полів у пункті where, інакше рушій відмовиться від використання індексів і виконає повне сканування таблиці, наприклад:
Виберіть id з T, де NUM дорівнює нулю
Ви можете встановити значення за замовчуванням 0 на num, переконатися, що в стовпці num у таблиці немає null-значення, і потім зробити запит так:
Виберіть id з 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, де NUM від 1 до 3
6. Якщо використовувати параметр із клаузи where, це також спричинить сканування повної таблиці. Оскільки SQL розв'язує локальні змінні лише під час виконання, але оптимізатор не може відкласти вибір планів доступу на час виконання; Його потрібно вибрати під час компіляції. Однак, якщо план доступу встановлено під час компіляції, значення змінної залишається невідомим і тому не може використовуватися як вхідний елемент для вибору індексу. Наступні заяви будуть відскановані повністю:
Виберіть ID з T, де num=@num
Ви можете змусити запит використовувати індекс замість цього:
Виберіть id з T з (index(index(індексна назва)), де num=@num
7. Намагайтеся уникати вираження полів у клаузі where, що призведе до того, що рушій відмовиться від використання індексу і виконає повне сканування таблиці. Наприклад:
Виберіть ID з T, де NUM/2=100
слід змінити на:
Виберіть ID з T, де num=100*2
8. Намагайтеся уникати виконання функційних операцій над полями в клаузі where, що призведе до того, що рушій відмовиться від використання індексів і виконає повне сканування таблиці. Наприклад:
Виберіть ID з T, де substring(name,1,3)='abc' – ідентифікатор імені, що починається з abc
Виберіть id з T, де Datediff(Day,createdate,'2005-11-30′)=0–'2005-11-30′ згенерований id
слід змінити на:
Виберіть ID з T, де ім'я на кшталт 'abc%'
виберіть ID з T, де createdate>='2005-11-30′ та createdate<'2005-12-1′
9. Не виконуйте функції, арифметичні операції чи інші операції виразів ліворуч від "=" у клаузі where (where), інакше система може неправильно використовувати індекс.
10. При використанні індексного поля як умови, якщо індекс є складеним, перше поле в індексі має використовуватися як умова, щоб система використовувала індекс, інакше індекс не буде застосований, і порядок полів має максимально відповідати порядку індексу.
11. Не пишіть беззмістовні запити, наприклад, генеруйте порожну структуру таблиці:
Виберіть COL1,COL2 у #t з T, де 1=0
Цей тип коду не повертає жодного набору результатів, але споживає системні ресурси, тому його слід змінити на щось подібне:
Створити таблицю #t(...)
12. Часто це хороший вибір для використання існує замість у:
Виберіть NUM з A Where Num In (Виберіть NUM з B)
Замінимо наступним твердженням:
Виберіть NUM з A, де існує (виберіть 1 з b, де num=a.num)
На що слід звернути увагу при створенні індексу:
1. Не всі індекси дійсні для запитів, SQL базується на даних у таблиці для оптимізації запиту, коли індексний стовпець має велику кількість дублювання даних, SQL-запити можуть не використовувати індекс, наприклад, таблиця має поля стать, чоловічий, жіночий майже половину кожного, і навіть якщо індекс побудований на статі, він не відіграватиме ролі в ефективності запитів.
2. Чим більше індексів, тим краще, індекс безумовно може підвищити ефективність відповідного селекту, але також знижує ефективність вставки та оновлення, оскільки індекс може бути перебудований під час вставки або оновлення, тому побудова індексу потрібно ретельно розглядати залежно від конкретної ситуації. Краще не мати більше 6 індексів у таблиці, а якщо їх занадто багато, розглянуть, чи потрібно будувати індекси на деяких рідко використовуваних стовпцях.
3. Уникайте оновлення кластеризованих індексних стовпців наскільки це можливо, оскільки порядок кластеризованих індексованих стовпців даних — це фізичний порядок зберігання таблицьних записів, і після зміни значення стовпців це призведе до коригування порядку всіх записів таблиці, що споживатиме значні ресурси. Якщо прикладній системі потрібно часто оновлювати кластеризовані індексні стовпці, вона має розглянути, чи слід будувати індекс як кластеризований.
Інші моменти, на які варто звернути увагу:
1. Намагайтеся використовувати числові поля і не створюйте поля, які містять лише числову інформацію як символи, що знизить продуктивність запитів і з'єднань, а також збільшить витрати на зберігання. Це пов'язано з тим, що рушій порівнює кожен символ рядка по одному під час обробки запитів і об'єднань, тоді як для числових типів його потрібно порівнювати лише один раз.
2. Не використовуйте select * з t ніде, замініть "*" на конкретний список полів і не повертайте поля, які не використовуються.
3. Спробуйте використовувати змінні таблиці замість тимчасових. Якщо змінна таблиці містить велику кількість даних, зверніть увагу, що індекс дуже обмежений (лише первинний індекс ключа).
4. Уникайте частого створення та видалення тимчасових таблиць для зменшення споживання ресурсів системної таблиці.
5. Тимчасові таблиці не є непридатними, і їх правильне використання може зробити певні процедури ефективнішими, наприклад, коли потрібно неодноразово посилатися на велику таблицю або набір даних у поширеній таблиці. Однак для одноразових подій найкраще використовувати таблицю експорту.
6. При створенні тимчасової таблиці, якщо обсяг даних, вставлених одночасно, великий, можна використовувати select into замість create table, щоб уникнути збільшення швидкості великої кількості журналів; Якщо обсяг даних невеликий, щоб спростити ресурси системної таблиці, спочатку створіть таблицю, а потім вставте її.
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