[Переклад] Поради щодо оптимізації індексів

Наша команда-партнер Artmisto

> SQL > [Переклад] Поради щодо оптимізації індексів

Протягом останнього тижня на роботі займався оптимізацією роботи MS SQL бази даних і наткнувся на список корисних порад щодо оптимізації індексів в базі, нижче наводжу переклад цієї статті. Багато з цих порад очевидні, але не завжди реально застосовуються на практиці.

оригінал: http://www.mssqlcity.com/Tips/tipInd.htm

1. Спробуйте створити індекс по стовпцях, які часто використовуються у виразах WHERE, ORDER BY, GROUP BY.
   Ці стовпці ідеальні кандидати для створення індексів. Вам слід дуже уважно проаналізувати ваші запити, щоб уникнути створення непотрібних індексів.
2. Ваші індекси повинні бути якомога менше.
   Через те, що кожен індекс займає дисковий простір, спробуйте мінімізувати розмір ключа індексу, щоб уникнути надмірного розміру бази. Таким чином зменшиться кількість операцій читання, необхідних для читання індексу і підвищить загальну продуктивність індексу.
3. Видаляйте індекси, які не використовуються.
   Оскільки кожен індекс займає дисковий простір і уповільнює вставку, видалення і оновлення рядків в таблицях, вам слід видаляти невикористовувані індекси. Ви можете використовувати Index Wizard для визначення індексів, які не використовуються вашими запитами.
4. Постарайтеся створити індекси за стовпцями, які мають цілі, а не символьні значення.
   Тому що цілі значення, як правило, меншого розміру, ніж символьні (розмір типу INT - 4 байта, розмір типу BIGINT - 8 байт), ви можете зменшити кількість сторінок індексу, які використовуються для зберігання ключів індеса. Це зменшить кількість читань, необхідних для читання індексу і збільшить загальну продуктивність.
5. Обмежте кількість індексів, якщо ваше додаток часто оновлює дані.
   Оскільки кожен індекс займає дисковий простір і уповільнює додавання, видалення і оновлення рядків, тому ви повинні створювати нові індекси тільки після аналізу використання даних, типу і частоти виконуваних запитів, і того, як ваші запити будуть використовувати нові індекси. У більшості випадків переваги від створення нових індексів переважують такі недоліки, як додаткове використовуваний простір і повільні модифікації даних. Однак, уникайте використання надлишкових індексів, створюйте їх, тільки коли це необхідно. Для таблиць, які використовуються виключно для читання, число індексів може бути збільшено.
6. Переконайтеся, що індекс, який ви намагаєтеся створити ще не існує.
   Майте на увазі, що при створенні первинного ключа або унікального ключа SQL Server автоматично створює індекс на стовпці, які беруть участь у вашому ключі. Якщо вказати інше ім'я індексу, то можна створювати індекси для того ж стовпця знову і знову.
7. Створюйте кластерний індекс замість некластерного для збільшення продуктивності запитів, які повертають діапазон значень, і для запитів, що містять GROUP BY або ORDER BY вираження і повертають відсортовані результати.
   Оскільки кожна таблиця може містити тільки один кластерний індекс, тому вам слід дуже ретельно вибирати стовпці для індексу. Постарайтеся проаналізувати всі ваші запити, виберіть найбільш часто використовувані і включіть в кластерний індекс тільки ті стовпці, які забезпечують найбільшу перевагу в продуктивності.
8. Створюйте некластерние індекси для збільшення продуктивності запитів, які повертають нелсколько рядків і там де індекси мають хорошу вибірковість (selectivity).
   Таблиці можуть містити до 249 некластерних індексів, на відміну від кластерного індексу, який може бути тільки один для кожної таблиці. Однак, вам слід створювати некластерние індекси дуже ретельно, як і в разі кластерними індексами, тому що кожен індекс займає дисковий простір і впливає на швидкість модифікації даних.
9. Створюйте кластерний індекс для стовпців, які не оновлюється дуже часто.
   Оскільки листя в дереві некластерізованний індексу містять ключ кластерного індексу, тому якщо у таблиці створено кластерний індекс, то кожен раз коли оновлюються стовпці що входять до нього - все некластерние індекси теж будуть оновлюватися.
10. Створюйте кластерний індекс на основі одного стовпчика або якомога меншої їх кількості.
    Оскільки некластерние індекси містять ключ кластерного індексу в листі дерева і некластерние індекси використовують кластерний для пошуку, то створення кластерного індексу по однім стовпці зменшить розмір не тільки кластерного індексу, а й усіх некластерних.
11. Слід уникати створення кластерного індексу на шпальтах з автоінкрементом.
    Наприклад, якщо у таблиці автоїнкрементальний первинний ключ і створений кластерний індекс по цьому стовпцю, то кожен раз коли відбувається вставка в цю таблицю, рядки будуть додаватися в кінець таблиці. А коли буде вставлятися багато рядків, то може статися « hot spot «. «Hot spot» відбувається коли багато запитів намагаються прочитати і записати дані в одну область в один час. «Hot spot» відбувається через те, що пристрої введення є вузьким місцем.
    Зауважте, що за замовчуванням SQL Server створює кластерний індекс по стовпцях, які входять в первинний ключ. В цьому випадку вам слід явно вказати, що ви хочете створити некластерние індекс для цих стовпців.
12. Створюйте кластерний індекс для кожної таблиці.
    Якщо ви створюєте таблицю без кластерного індексу, то дані будуть зберігатися неупорядоченно. Така струкрута називається купа. Кожен раз, коли дані будуть вставлятися в таку таблицю, рядки будуть додаватися в кінець таблиці. В цьому випадку знову може статися «hot spot». Для поліпшення роботи паралельних запитів, вам слід створювати кластерні індекси для кожної таблиці.
13. Не створюйте індекси за стовпцями з низькою вибірковістю (selectivity).
    Наприклад, не створюйте індекс по стовпцях, в яких міститься багато дублюються даних, наприклад для стовпець «Пол», який може містити тільки значення «Чоловічий» і «Жіночий», тому що в цьому випадку недоліки від додаткового зайнятого дискового простору і повільної модифікації переважують переваги в швидкості пошуку за таким індексом.
14. Якщо ви створюєте композитний індекс, то постарайтеся розташовувати ключі індексу в порядку зменшення вибірковості (selectivity), тобто ключ з найбільшою вибірковістю повинен бути самим лівим.
    Порядок стовпців в композитних індексах дуже важливий, він може збільшити шанс використання індексу в запитах.
15. Якщо ви створюєте композитний індекс, то розміщуйте на початку ключі індексу, які часто використовуються в WHERE виразах.
    Порядок стовпців в композитних індексах дуже важливий. Індекс буде використовуватися в запиті, тільки якщо стобци, які знаходяться в WHERE, є найбільш лівими ключами індексу. Наприклад, якщо ви створюєте композитний індекс «Name, Age», то при пошуку по Name індекс буде використаний, а при пошуку по Age, він використовуватися не буде.
16. Створюйте індекс для стовпців, які часто використовуються в JOIN'ах.
    Це може істотно збільшити продуктивність запитів з JOIN'амі за цими стовпцями.
17. Подумайте над створенням целочисленного первинного сурогатного ключа.
    Кожна таблиця повинна містити первинний ключ (унікальний ідентифікатор рядка в таблиці). Сурогатний первинний ключ це поле, яке має унікальне значення, але не відноситься ніяк до самого запису, і користувачі не повинні ніколи бачити або міняти такий первинний ключ. Деякі розробники використовують сурогатні первинні ключі, інші використовують дані самих полів, як первинний ключ. Якщо первинний ключ містить багато полів і має великий розмір - задумайтеся про створення сурогатного ключа. Це може збільшити продуктивність ваших запитів.
18. Розгляньте можливість створення індексів по всіх стовпцях, які використовуються найбільш часто в вираженні WHERE, яке містить оператори АБО.
    Якщо у виразі WHERE стримається запит, який містить оператор АБО і якщо з них ні один стовпець НЕ буде покритий індексом, то запитом доведеться повністю просканувати таблицю або кластерний індекс. Тому в таких випадках, створюйте індекси на всі такі стовпці, що істотно збільшить продуктивність ваших запитів.
19. Якщо ваш додаток виконує запити над таблицею дуже часто, то розгляньте питання про створення покриває індексу, що включає всі стовпці, які вимагаються запиті.
    Покриває індекс, це індекс, який включає всі стовпці з запиту. Створення таких індексів може збільшити продуктивність, тому що всі дані для замкну будуть вже міститися в індексі, і не доведеться отримувати з з таблиці. Покривають індекси можуть істотно збільшити продуктивність запитів, так як вони економлять на великій кількості операцій введення / виводу.
20. Використовуйте періодично інструкцію DBCC DBREINDEX для того, щоб перебудувати всі індекси у всіх таблицях для зменшення фрагментації.
    Фрагментовані дані можуть змусити SQL Server виробляти додаткові непотрібні читання, тому запити на сильно фрагментованих таблицях будуть виконуватися дуже погано. Так що варто періодично перебудовувати індекси для зменшення фрагментації. Постарайтеся запускати DBCC DBREINDEX в моменти, коли база сильно не навантажена.
21. Використовуйте інструкцію DBCC INDEXDERRAG для дефрагментації індексів таблиць і уявлень.
    Ця інструкція з'явилася в SQL Server 2000. На відміну від DBCC DBREINDEX ця інструкція не блокує дані надовго, а тому вони не блокуватиме виконання запитів. Постарайтеся використовувати DBCC INDEXDERRAG замість DBCC DBREINDEX, коли це можливо.
22. Спробуйте використовувати параметр SORT_IN_TEMPDB при створенні індексу, коли tempdb знаходиться не на тому ж диску, що і ваша база даних
    Параметр SORT_IN_TEMPDB був введений в SQL Server 2000. Коли ви створюєте індекс з цим параметром, то SQL Server використовує tempdb для сортування даних при створенні індексу, замість поточної бази даних. Цей параметр дозволяє швидше створити індекс, але збільшує кількість зайнятого дискового простору при створенні індексу.
23. Використовуйте Create Trace Wizard в SQL Server Profile з параметром «Identity Scans of Large Tables» для визначення таблиць в базі, яким необхідні індекси.
    Результати покажуть які таблиці не використовують індекси.

55.755786 37.617633