--Індекс є структурою на диску, яка пов'язана з таблицею або поданням та прискорює отримання рядків з таблиці чи подання. Індекс містить ключі, побудовані з одного або кількох стовпців у таблиці чи поданні. Ці ключі зберігаються у вигляді структури збалансованого дерева, яка підтримує швидкий пошук рядків за їхніми ключовими значеннями в SQL Server.

--Кластеризовані індекси сортують та зберігають рядки даних у таблицях чи уявленнях на основі їх ключових значень. Цими значеннями є стовпці, включені до визначення індексу. Існує лише один кластеризований індекс для кожної таблиці, тому що рядки даних можуть бути відсортовані лише в єдиному порядку.
--Рядки даних у таблиці зберігаються у порядку сортування тільки в тому випадку, якщо таблиця містить кластеризований індекс. Якщо таблиця має кластеризований індекс, то таблиця називається кластеризованою. Якщо таблиця не має кластеризованого індексу, то рядки даних зберігаються в неупорядкованій структурі, яка називається купою.

--Некластеризований індекс має таку саму структуру, що і кластеризований індекс, але з двома важливими відмінностями:
--Некластеризований індекс не змінює фізичний порядок рядків у таблиці та сторінки листя в некластеризованому індексі складаються з індексних ключів та закладок.

--Кластеризовані індекси забезпечують швидше отримання даних, ніж некластеризовані. Зазвичай вони виявляються швидше і при оновленні, але не в тому випадку, коли багато оновлень відбувається в тому самому місці в середині відносини.

--З деяких причин кластеризований індекс зазвичай виконується швидше, ніж некластеризований індекс. Коли система сканує кластеризований індекс, немає необхідності залишати структуру В-дерева (В-дерево є одним з конкретних типів деревоподібного індексу) для сканування сторінок даних, тому що такі сторінки вже присутні на рівні листя цього дерева.

--Некластеризований індекс також потребує більше операцій введення/виводу, ніж відповідний кластеризований індекс.

--Некластеризований індекс потрібно читати сторінки даних після перегляду В-дерева або, якщо існує кластеризований індекс для іншого стовпця (стовпців) таблиці, некластеризований індекс потрібно читати структуру В-дерева кластеризованого індексу.

--Тому кластеризований індекс буде виконуватися значно швидше, ніж сканування таблиці, навіть якщо його селективність досить погана (запит повертає багато рядків)

CREATE TABLE tsql.dbo.NI
ID int NOT NULL,
T char(8) NULL
);

CREATE TABLE tsql.dbo.NCI
ID int NOT NULL,
T char(8) NULL
);

--Створення кластеризованого індексу

CREATE CLUSTERED INDEX IX_1
ON tsql.dbo.NCI (ID);

--Створення некластеризованого індексу для таблиці

CREATE NONCLUSTERED INDEX IX_2
ON tsql.dbo.NCI (T);

--Додамо тестові дані
DECLARE @i INT = 100 000;
DECLARE @t CHAR(1) = "T";

WHILE @i > 0
BEGIN
insert into tsql.dbo.NI values(@i, @t + CAST(@i AS char(6)));
insert into tsql.dbo.NCI values(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
END

--Запити до таблиці з індексами
SELECT ID, T FROM tsql.dbo.NCI
ORDER BY ID, T

SELECT ID, COUNT(*) AS C FROM tsql.dbo.NCI
GROUP BY ID, T

SELECT ID, T FROM tsql.dbo.NCI
WHERE ID > 4000 AND ID< 55000 AND T LIKE "T%"

--Запит з використанням обох індексів
USE tsql;
SELECT CAST(dbo.NCI.ID AS VARCHAR)
FROM dbo.NCI
GROUP BY dbo.NCI.ID
UNION ALL
SELECT dbo.NCI.T
FROM dbo.NCI
GROUP BY dbo.NCI.T

--Інформація про індекси
SELECT index_type_desc, index_depth, index_level,
page_count, record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL, "DETAILED");

--Видалення індексів
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N"IX_1")
DROP INDEX IX_1 ON tsql.dbo.NCI;

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N"IX_2")
DROP INDEX IX_2 ON tsql.dbo.NCI;

У попередній статті ми познайомилися зі способами оптимізації реляційних баз даних та обговорили принцип дії кластеризованих та некластеризованих індексів у контексті оптимізації часу виконання запитів до бази даних. Тепер настав час застосувати отримані знання практично, навчившись створювати оптимізаційні індекси для бази даних MS SQL.

Нагадаю вам визначення схеми таблиці Staffs, з якою ми працюватимемо:

Таблиця Staffs

Допустимо нам необхідно для таблиці Staffs створити некластеризований індекс, який оптимізуватиме наступний запит:

SELECT Id, Name, Job FROM Stuffs WHERE SALARY > 1000 AND Photo IS NOT NULL

Ключем індексу будуть стовпці SALARY і Photo, оскільки за цими полями проводиться фільтрація вибірки. А стовпи Id, Name та Job будуть включеними до індексу стовпцями.

Загальний синтаксис команди наступний:

USE GO

CREATE NONCLUSTERED INDEX ON (ASC - стовпці ключа індексу)

INCLUDE ( -- включені стовпці) GO

У нашому випадку запит буде виглядати так:

(Salary, Photo) INCLUDE (Id, Name, Job) GO

Ми створили некластеризований індекс. А точніше некластеризований індекс, що покриває. Це означає, що в індексі є всі необхідні для виконання запиту поля, і SQL Server під час виконання запиту не буде звертатися до базової таблиці.

Якби наш код був таким:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

(Salary, Photo) INCLUDE (Id) GO

То в цьому випадку індекс перестає бути покриваючим, оскільки не включає всі стовпці, що використовуються в запиті. Оптимізатор все одно використовуватиме цей індекс при виконанні запиту, проте його ефективність на порядок знизиться, оскільки потрібні звернення до базової таблиці.

Кластеризований індекс створюється за допомогою наступної команди:

CREATE CLUSTERED INDEX IDX_Stsffsid ON Stuffs (Id)

Тут було створено унікальний кластерний індекс, побудований з урахуванням первинного ключа таблиці (стовпця Id).

Реальний приклад

Давайте розробимо сценарій, на якому зможемо реально оцінити ступінь приросту продуктивності у разі використання індексів.

Створимо нову базу даних:

CREATE DATABASE TestDB;

І єдину таблицю Customers, яка складатиметься із чотирьох стовпців:

CREATE TABLE. (

NOT NULL, NULL, NULL, NULL) GO

Тепер заповнимо таблицю випадковими даними. Стовпець Id нарощуватиметься в циклі, а решта трьох колонок таблиці заповнюватиметься випадковими числами за допомогою своєрідного варіанту функції random:

DECLARE @i int = 0;

WHILE (@i< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid()))) SET @i = @i + 1; END

Цей скрипт вносить до таблиці півмільйона записів, тому будьте терплячі, скрипт працюватиме не менше 3 хвилин.

Все готове для випробування. Ми оцінюватимемо характеристики продуктивності виконання запиту. Оскільки час виконання запиту може залежати від конкретної машини, ми аналізуватимемо більш незалежний показник – кількість логічних зчитувань.

Щоб увімкнути режим збору статистики, необхідно виконати наступну команду:

Тепер після кожного запиту на вкладці Messages нам буде доступна статистика виконання цього запиту, як показано нижче:

Нас цікавить лише значення параметра logical reads.

Отже, поки що в нашій таблиці немає жодного індексу. Виконаємо наступні три запити та запишемо кількість логічних читань для кожного запиту до таблиці результатів, яка наведена нижче:

1) SELECT Id, Num1, Num2 FROM Customers WHERE Id = 2000

2) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id< 1000

3) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id< 5000

Дані запити повернуть відповідно 1 рядок, 1000 рядків та 5000 рядків. Без індексів показник продуктивності (кількість логічних зчитувань) для всіх запитів однаковий і дорівнює 1621. Внесемо дані до таблиці результатів:

Ми бачимо, що для другого і третього запиту, коли повертається досить багато рядків, створений нами індекс не збільшив продуктивність. Однак для запиту, який повертає один рядок, прискорення вийшло величезним. Таким чином, ми можемо зробити висновок, що індекси, що не покривають, має сенс створювати при оптимізації запитів, які повертають один результат.

Тепер створимо індекс, що покриває, тим самим досягнувши максимальної продуктивності.

Для початку видалимо попередній індекс:

USE TestDB GO DROP INDEX Customers.TestIndex1

І створимо новий індекс:

CREATE NONCLUSTERED INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

Тепер виконаємо наші запити втретє та запишемо результати до таблиці:

Без індексів
Непокриваючий індекс
Покриваючий індекс

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

У статті ми розглянули приклад оптимізації бази даних шляхом створення індексів. Слід зазначити, що створення індексів є суто індивідуальним процесом для кожного запиту. Щоб побудувати індекс, який дійсно оптимізувати роботу запиту, необхідно ретельно проаналізувати сам запит і його план виконання.

Ефективна побудова індексів - один із кращих способів підвищення продуктивності програми, що працює з базою даних. Без використання індексів, SQL сервер подібний до читача, який намагається знайти слово в книзі, переглядаючи кожну сторінку. Якщо у книзі є предметний покажчик (індекс), читач може виконати пошук необхідної інформації набагато швидше.

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

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

Корисні індекси

Отже, індексування таблиць буде корисним при пошуку певного запису в таблиці з використанням інструкції Where. До таких запитів відносяться, наприклад, запити пошуку діапазону значень, запити точного зіставлення певного значення, запити, що здійснюють злиття двох таблиць.

Наприклад, наведені нижче запити до бази даних Northwind будуть виконуватися більш ефективно при побудові індексу по стовпцю UnitPrice.

Delete from Products Where UnitPrice=1
Select * from products Where UnitPrice між 14 AND 16

Оскільки елементи індексу зберігаються відсортованими, індексування також виявляється корисним під час побудови запиту з використанням інструкції Order by. Без індексу записи завантажуються та сортуються під час виконання запиту. Індекс UnitPrice дозволить при обробці наступного запиту просто просканувати індекс і витягти рядки за посиланням. Якщо потрібно впорядкувати рядки зі спадання, достатньо буде просто просканувати індекс у зворотному порядку.

Select * From Products order by UnitPrice ASC

Угруповання запису з використанням інструкції Group by також часто вимагає сортування, таким чином, побудова індексу по колонці UnitPrice буде корисною і при наступному запиті, що підраховує кількість одиниць продукту за кожною певною ціною

Select count(*), UnitPrice З Products Group by UnitPrice

Індекси виявляються корисними для підтримки унікального значення стовпця, тому що СУБД може легко за індексом переглянути, чи міститься вже таке значення. Тому первинні ключі завжди проіндексовані.

Недоліки індексування

Індекси погіршують продуктивність системи під час зміни запису. У будь-який час при виконанні запиту на зміну даних у таблиці індекс також повинен змінюватися. Для вибору оптимальної кількості індексів необхідне тестування бази даних та спостереження за її продуктивністю. Статичні системи, де бази даних використовуються в основному для отримання даних, наприклад для побудови звітів, дозволяють містити більшу кількість індексів для підтримки запитів тільки на читання. Бази даних з великою кількістю транзакцій для зміни даних потребуватимуть невеликої кількості індексів для забезпечення більш високої пропускної спроможності.

Індекси займають додаткове місце на диску та в оперативній пам'яті. Точний розмір залежатиме від кількості записів у таблиці, як і від кількості та розміру стовпців в індексі. У більшості випадків це не є основною проблемою, оскільки дисковий простір зараз легко пожертвувати для кращої продуктивності.

Побудова оптимального індексу

Простий індекс

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

По-друге, оскільки елементи індексу часто залучені до порівняння, менші індекси легше порівнювати. З цих двох причин єдина колонка цілого типу є кращим індексом, так як він малий і легкий для порівняння. Рядки символів, з іншого боку, вимагають посимвольного порівняння та уваги до обробки параметрів.

Селективний індекс

Найбільш ефективні індекси - це індекси з малим відсотком значень, що дублюються. Наприклад, телефонний довідник міста, в якому практично кожен має прізвище Сміт, буде не настільки корисним, якщо записи в ньому відсортувати на прізвище.

Індекс з високим відсотком унікальних значень також називають селективним індексом. Очевидно, унікальний індекс має найбільшу селективність, тому що не містить значень, що дублюються. Багато СУБД можуть відстежувати статистику про кожен індекс і можуть розпізнавати, як багато значень, що неповторюються, містить кожен індекс. Ця статистика використовується при генерації плану виконання запиту.

Покриваючі індекси

Індекси складаються зі стовпця даних, за яким власне побудовано індекс та покажчика на відповідний рядок. Це схоже на предметний покажчик книги: він містить тільки ключові слова та посилання на сторінку, на яку ви можете звернутися за додатковою інформацією. Зазвичай СУБД слідуватиме вказівникам до рядка з індексу, щоб зібрати всю необхідну інформацію для запиту. Проте, якщо індекс містить усі стовпці, необхідні в запиті, інформація може бути вилучена без звернення до самої таблиці.

Розглянемо індекс по стовпцю UnitPrice, який згадувався вище. СУБД може використовувати лише елементи індексу для виконання наступного запиту.

Select Count(*), UnitPrice Від Products Group by UnitPrice

Такий тип запиту називають покриваючим запитом, тому що всі стовпці, що запитуються, можуть бути вилучені з одного індексу. Для найбільш важливих запитів ви можете розглянути можливість створення індексу, що покриває, для можливо кращої продуктивності. Такі індекси з великою ймовірністю будуть складовими (використано більше одного стовпець), що протиставляється першому принципу: створювати прості індекси. Очевидно, вибір оптимальної кількості стовпців в індексі можна оцінити лише за допомогою тестування та спостереження за продуктивністю бази даних у різних ситуаціях.

Кластерний індекс

Багато баз даних мають один спеціальний індекс до таблиці, де всі дані з рядка містяться в індексі. У SQL сервері такий індекс називається кластерним (кластеризованим). Кластерний індекс можна порівняти з телефонним довідником, тому що кожен елемент індексу містить всю інформацію, яка вам потрібна та не містить посилань для отримання додаткових даних.

Є загальне правило – кожна нетривіальна таблиця повинна мати кластерний індекс. Якщо можна створити лише один індекс до таблиці, зробіть кластерним. У SQL сервері при створенні первинного ключа буде автоматично створено кластерний індекс (якщо він ще не міститься), використовуючи стовпець з первинним ключем як ключ для індексування. Кластерний індекс найбільш ефективний індекс (якщо він використовується, то покриває весь запит) і в багатьох СУБД такий індекс сприяє ефективному управлінню простором, що запитується для зберігання таблиць, тому що в іншому випадку (без побудови кластерного індексу) рядки таблиць зберігаються в неупорядкованій структурі, яку називають купою.

При виборі стовпців для кластерного індексу будьте обережні. Якщо змінити запис і змінити значення стовпця в кластерному індексі, база даних буде змушена перебудувати елементи індексу (щоб тримати їх у відсортованому порядку). Пам'ятайте, елементи індексу для кластерного індексу містити всі значення стовпців, таким чином, зміна значення стовпця можна порівняти з виконанням інструкції Delete і подальшою інструкцією Insert, що очевидно викличе проблеми з продуктивністю, якщо робити це часто. З цієї причини кластерні індекси часто складаються зі стовпців первинного ключа та зовнішнього ключа. Значення ключів якщо змінюються, дуже рідко.

Висновок

Визначення правильних індексів, що використовуються у базі даних, потребує ретельного аналізу та тестування системи. Практичні методи, представлені у цій статті, є добрими правилами для побудови індексів. Після застосування цих методів вам необхідно буде заново протестувати конкретну програму за ваших конкретних апаратних умов, пам'яті та операціях.

Одним із найважливіших шляхів досягнення високої продуктивності SQL Serverє використання індексів. Індекс прискорює процес запиту, надаючи швидкий доступ до рядків даних у таблиці, аналогічно до того, як покажчик у книзі допомагає вам швидко знайти необхідну інформацію. У цій статті я наведу короткий огляд індексів у SQL Serverі поясню як вони організовані у базі даних і як допомагають прискоренню виконання запитів до бази даних.

Індекси створюються для стовпців таблиць та уявлень. Індекси надають шлях для швидкого пошуку даних на основі значень цих стовпців. Наприклад, якщо ви створите індекс первинного ключа, а потім будете шукати рядок з даними, використовуючи значення первинного ключа, то SQL Serverспочатку знайде значення індексу, а потім використовує індекс для швидкого знаходження всього рядка з даними. Без індексу буде виконано повний перегляд (сканування) всіх рядків таблиці, що може вплинути на продуктивність.
Ви можете створити індекс на більшості стовпців таблиці або уявлення. Винятком, переважно, є стовпці з типами даних зберігання великих об'єктів ( LOB), таких як image, textабо varchar(max). Ви також можете створити індекси на стовпцях, призначених для зберігання даних у форматі XMLАле ці індекси влаштовані трохи інакше, ніж стандартні, і їх розгляд виходить за рамки цієї статті. Також у статті не розглядаються columnstoreіндекси. Натомість я фокусуюся на тих індексах, які найчастіше застосовуються в базах даних SQL Server.
Індекс складається з набору сторінок, вузлів індексу, які організовані у вигляді деревоподібної структури. збалансованого дерева. Ця структура є ієрархічною за своєю природою і починається з кореневого вузла на вершині ієрархії та кінцевих вузлів, листя, в нижній частині, як показано на малюнку:


Коли ви формуєте запит на індексований стовпець, підсистема запитів починає йти зверху від кореневого вузла і поступово рухається через проміжні вузли, при цьому кожен шар проміжного рівня містить більш детальну інформацію про дані. Підсистема запитів продовжує рухатися вузлами індексу до тих пір, поки досягне нижнього рівня з листям індексу. Наприклад, якщо ви шукаєте значення 123 в індексованому стовпі, підсистема запитів спочатку на кореневому рівні визначить сторінку на першому проміжному (intermediate) рівні. У цьому випадку перша сторінка вказує на значення від 1 до 100, а друга від 101 до 200, таким чином, підсистема запитів звернеться до другої сторінки цього проміжного рівня. Далі буде з'ясовано, що слід звернутися до третьої сторінки наступного проміжного рівня. Звідси підсистема запитів прочитає на нижньому рівні значення індексу. Листя індексу може містити як самі дані таблиці, і просто покажчик на рядки з даними у таблиці, залежно від типу індексу: кластеризований індекс чи некластеризованный.

Кластеризований індекс

Кластеризований індекс зберігає реальні рядки даних у листі індексу. Повертаючись до попереднього прикладу, це означає, що рядок даних, пов'язана зі значенням ключа, рівного 123 буде зберігатися в самому індексі. Важливою характеристикою кластеризованого індексу і те, що це значення відсортовані у порядку чи зростання, чи спадання. Таким чином, таблиця або уявлення може мати лише один кластеризований індекс. На додаток слід зазначити, що дані в таблиці зберігаються у відсортованому вигляді лише у випадку, якщо створено кластеризований індекс цієї таблиці.
Таблиця, що не має кластеризованого індексу, називається купою.

Некластеризований індекс

На відміну від кластеризованого індексу, листя некластеризованого індексу містить лише ті стовпці ( ключові), якими визначено цей індекс, і навіть містить покажчик на рядки з реальними даними у таблиці. Це означає, що система підзапитів потребує додаткової операції для виявлення та отримання необхідних даних. Зміст покажчика даних залежить від способу зберігання даних: кластеризована таблиця чи купа. Якщо покажчик посилається на кластеризовану таблицю, він веде до кластеризованому індексу, використовуючи який можна знайти реальні дані. Якщо покажчик посилається на купу, він веде до конкретного ідентифікатору рядки з даними. Некластеризовані індекси не можуть бути відсортовані на відміну від кластеризованих, однак ви можете створити більше одного некластеризованого індексу на таблиці або поданні, аж до 999. Це не означає, що ви повинні створювати якнайбільше індексів. Індекси можуть як покращити, так і погіршити продуктивність системи. На додаток до можливості створити кілька некластеризованих індексів, ви можете також увімкнути додаткові стовпці ( included column) у свій індекс: на листі індексу зберігатиметься не лише значення самих індексованих стовпців, а й значення цих не індексованих додаткових стовпців. Цей підхід дозволить вам обійти деякі обмеження, накладені на індекс. Наприклад, ви можете включити стовпець, що не ідексується, або обійти обмеження на довжину індексу (900 байт в більшості випадків).

Типи індексів

На додаток до того, що індекс може бути кластеризованим, або некластеризованим, можливо його додатково сконфігурувати як складовий індекс, унікальний індекс або покриває індекс.

Складовий індекс

Такий індекс може містити більше одного стовпця. Ви можете включити до 16 стовпців в індекс, але їхня загальна довжина обмежена 900 байтами. Як кластеризований, і некластеризований індекси можуть бути складовими.

Унікальний індекс

Такий індекс забезпечує унікальність кожного значення в стовпці, що індексується. Якщо індекс складової, то унікальність поширюється попри всі стовпці індексу, але з кожен окремий стовпець. Наприклад, якщо ви створите унікальний індекс на стовпцях Ім'яі ПРІЗВИЩЕ, то повне ім'я має бути унікальним, але окремо можливі дублі в імені або прізвища.
Унікальний індекс автоматично створюється, коли ви визначаєте обмеження стовпця: первинний ключ або обмеження на унікальність значень:

• Первинний ключ
  Коли ви визначаєте обмеження первинного ключа на один або кілька шпальт, тоді SQL Serverавтоматично створює унікальний кластеризований індекс, якщо кластеризований індекс не був створений раніше (у цьому випадку створюється унікальний некластеризований індекс за первинним ключем)
• Унікальність значень
  Коли ви визначаєте обмеження на унікальність значень, тоді SQL Serverавтоматично створює унікальний некластеризований індекс. Ви можете вказати, щоб був створений унікальний кластеризований індекс, якщо кластеризований індекс досі не був створений на таблиці

Покриваючий індекс

Такий індекс дозволяє конкретному запиту одразу отримати всі необхідні дані з листя індексу без додаткових звернень до записів таблиці.

Проектування індексів

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

База даних

Як було зазначено раніше індекси можуть збільшити продуктивність системи, т.к. вони забезпечують підсистему запитів швидким шляхом знаходження даних. Однак, ви також повинні взяти до уваги те, як часто ви збираєтеся вставляти, оновлювати або видаляти дані. Коли ви змінюєте дані, індекси повинні також бути змінені, щоб відобразити відповідні дії над даними, що може значно знизити продуктивність системи. Розглянемо такі рекомендації під час планування стратегії індексування:

• Для таблиць, які часто оновлюються, використовуйте якнайменше індексів.
• Якщо таблиця містить велику кількість даних, але їх зміни незначні, тоді використовуйте стільки індексів, скільки необхідно для покращення продуктивності ваших запитів. Однак, добре подумайте перед використанням індексів на невеликих таблицях, т.к. Можливо, використання пошуку по індексу може зайняти більше часу, ніж просте сканування всіх рядків.
• Для кластеризованих індексів намагайтеся використовувати такі короткі поля наскільки це можливо. Найкращим буде застосування кластеризованого індексу на стовпцях з унікальними значеннями і які не дозволяють використовувати NULL. Саме тому первинний ключ часто використовується як кластеризований індекс.
• Унікальність значень у стовпці впливає на продуктивність індексу. Загалом, чим більше у вас дублікатів у стовпці, тим гірше працює індекс. З іншого боку, що більше унікальних значення, то вище працездатність індексу. Коли можливо, використовуйте унікальний індекс.
• Для складеного індексу візьміть до уваги порядок шпальт в індексі. Стовпці, які використовуються у виразах WHERE(наприклад, WHERE FirstName = "Charlie") мають бути в індексі першими. Наступні стовпці мають бути перераховані з урахуванням унікальності їх значень (стовпці з найвищою кількістю унікальних значень йдуть першими).
• Також можна вказати індекс на стовпцях, що обчислюються, якщо вони відповідають деяким вимогам. Наприклад, вираз, які використовуються для отримання значення стовпця, повинні бути детерміністичними (завжди повертати один і той же результат для заданого набору вхідних параметрів).

Запити до бази даних

Інше міркування, яке слід враховувати при проектуванні індексів, це які запити виконуються до бази даних. Як було зазначено раніше, ви повинні враховувати, як часто змінюються дані. Додатково слід використовувати такі принципи:

• Намагайтеся вставляти або модифікувати в одному запиті якнайбільше рядків, а не робити це в кілька одиночних запитів.
• Створіть некластеризований індекс на стовпцях, які часто використовуються у ваших запитах як умови пошуку в WHEREі з'єднання в JOIN.
• Розгляньте можливість індексування стовпців, які використовуються у запитах пошуку рядків на точну відповідність значень.

А тепер, власне:

14 питань про індекси в SQL Server, які ви соромилися задати

Чому таблиця не може мати два кластеризованих індекси?

Хочете коротку відповідь? Кластеризований індекс – це таблиця. Коли ви створюєте кластеризований індекс у таблиці, підсистема зберігання даних сортує всі рядки в таблиці в порядку зростання або зменшення, згідно з визначенням індексу. Кластеризований індекс це не окрема сутність як інші індекси, а механізм сортування даних у таблиці та полегшення швидкого доступу до рядків із даними.
Уявимо, що у вас є таблиця, що містить історію операцій із продажу. Таблиця Sales включає таку інформацію як ідентифікатор замовлення, позицію товару в замовленні, номер товару, кількість товару, номер і дату замовлення і т.д. Ви створюєте кластеризований індекс по стовпцях OrderIDі LineID, із сортуванням у порядку зростання, як показано в наступному T-SQLкоді:
CREATE UNIQUE CLUSTERED INDEX ix_oriderid_lineid ON dbo.Sales(OrderID, LineID);
Коли ви запустите цей скрипт, всі рядки в таблиці будуть фізично відсортовані спочатку по стовпцю OrderID, а потім по LineID, але самі дані залишаться в єдиному логічному блоці, в таблиці. З цієї причини ви не можете створити два кластеризовані індекси. Можливо лише одна таблиця з одними даними і ця таблиця може бути відсортована лише один раз у певному порядку.

Якщо кластеризована таблиця дає безліч переваг, то навіщо використати купу?

Ви маєте рацію. Кластеризовані таблиці відмінні і більшість ваших запитів будуть краще виконуватися до таблиць, що мають кластеризований індекс. Але в деяких випадках ви, можливо, захочете залишити таблиці в їхньому природному первозданному стані, тобто. у вигляді купи і створити лише некластеризовані індекси для підтримки працездатності ваших запитів.
Купа, як ви пам'ятаєте, зберігає дані у випадковому порядку. Зазвичай підсистема зберігання даних додає таблицю дані в тій послідовності в якій вони вставляються, проте підсистема також любить переміщати рядки з метою більш ефективного зберігання. В результаті у вас немає жодного шансу передбачити, в якому порядку зберігатимуться дані.
Якщо підсистема запитів має знайти дані без переваг некластеризованого індексу, вона зробить повне сканування таблиці знаходження потрібних їй рядків. На дуже маленьких таблицях це зазвичай не проблема, але як тільки купа росте у своїх розмірах, продуктивність швидко падає. Звичайно, некластеризований індекс може допомогти, використовуючи покажчик на файл, сторінку та рядок де зберігаються необхідні дані – зазвичай це набагато найкраща альтернатива скануванню таблиці. Але навіть у разі важко порівнювати з перевагами кластеризованого індексу під час розгляду продуктивності запитів.
Однак купа може допомогти покращити продуктивність у певних ситуаціях. Розглянемо таблицю з великою кількістю вставок, але рідкісні оновлення або видалення даних. Наприклад, таблиця, що зберігає балку, переважно використовується для вставки значень до тих пір, поки не буде архівована. У купі ви не побачите розбиття сторінок і фрагментацію даних, як це трапляється з кластеризованим індексом, тому що рядки просто додаються в кінець купи. Занадто великий поділ сторінок може мати значний вплив на продуктивність і в найкращому сенсі. Загалом, купа дозволяє вставляти дані відносно безболісно і вам не треба буде боротися з накладними витратами на зберігання та обслуговування, як це буває у разі кластеризованого індексу.
Але відсутність оновлення та видалення даних не повинні розглядатися як єдина причина. Спосіб вибірки даних також є важливим фактором. Наприклад, ви не повинні використовувати купу, якщо часто виконуєте запити діапазонів даних або дані, що запитуються, часто повинні бути сортовані або згруповані.
Все це означає, що ви повинні розглядати можливість використання купи тільки коли працюєте з особливо маленькими таблицями або вся ваша взаємодія з таблицею обмежена вставкою даних і ваші запити надзвичайно прості (і ви все одно використовуєте некластеризовані індекси). Інакше тримайтеся добре спроектованого кластеризованого індексу, наприклад визначеного на простому зростаючому ключовому полі, як стовпець, що широко застосовується з IDENTITY.

Як змінити значення за замовчуванням значення коефіцієнта заповнення індексу?

Зміна встановленого за умовчанням коефіцієнта заповнення індексу це одна справа. Розуміння того, як встановлений за замовчуванням коефіцієнт працює це інше. Але спочатку кілька кроків тому. p align="justify"> Коефіцієнт заповнення індексу визначає кількість простору на сторінці для зберігання індексу на нижньому рівні (рівень листя) перед тим як почати заповнювати нову сторінку. Наприклад, якщо коефіцієнт виставлено значення 90, то при зростанні індекс займе на сторінці 90%, а потім перейде на наступну сторінку.
За замовчуванням значення коефіцієнта заповнення індексу в SQL Serverдорівнює 0, що рівнозначно значенню 100. В результаті всі нові індекси автоматично успадковують цю настройки, якщо ви спеціально в коді не вкажете відмінне від стандартного для системи значення або змінити стандартну поведінку. Ви можете скористатися SQL Server Management Studioдля коригування встановленого за замовчуванням значення або запустити збережену системну процедуру sp_configure. Наприклад, наступний набір T-SQLкоманд встановлює значення коефіцієнта рівне 90 (попередньо необхідно переключиться в режим просунутих налаштувань):
EXEC sp_configure "show advanced options", 1; GO RECONFIGURE; GO EXEC sp_configure "fill factor", 90; GO RECONFIGURE; GO
Після зміни значення коефіцієнта заповнення індексу необхідно перезавантажити сервіс SQL Server. Тепер можна перевірити встановлене значення, запустивши процедуру sp_configure без зазначеного другого аргументу:
EXEC sp_configure "fill factor" GO
Ця команда повинна повернути значення, що дорівнює 90. В результаті всі новостворені індекси будуть використовувати це значення. Ви можете перевірити це, створивши індекс та запитати значення коефіцієнта заповнення:
USE AdventureWorks2012; -- ваша база даних GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
У цьому прикладі ми створили некластеризований індекс у таблиці Personу базі даних AdventureWorks2012. Після створення індексу ми можемо отримати значення коефіцієнта заповнення із системної таблиць sys.indexes. Запит повинен відновити 90.
Однак уявімо, що ми видалили індекс і знову створили його, але тепер вказали конкретне значення коефіцієнта заповнення:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
На цей раз ми додали інструкцію WITHта опцію fillfactorдля нашої операції створення індексу CREATE INDEXта вказали значення 80. Оператор SELECTтепер повертає відповідне значення.
Досі все було досить прямолінійно. Де ви реально можете погоріти у всьому цьому процесі, так це коли ви створюєте індекс, який використовує значення коефіцієнта за умовчанням, маючи на увазі, що ви знаєте це значення. Наприклад, хтось невміло колупається в налаштуваннях сервера і він настільки впертий, що ставить значення коефіцієнта заповнення індексу рівне 20. Тим часом ви продовжуєте створювати індекси, припускаючи значення за умовчанням 0. На жаль, у вас немає способу дізнатися значення коефіцієнта до поки ви не створите індекс, а потім перевірте значення, як ми робили в наших прикладах. В іншому випадку, вам доведеться чекати моменту, коли продуктивність запитів настільки впаде, що ви почнете щось підозрювати.
Інша проблема про яку вам варто пам'ятати це перебудова індексів. Як і під час створення індексу ви можете конкретизувати значення коефіцієнта заповнення індексу, коли його перебудовуєте. Однак, на відміну від команди створення індексу, перебудова не використовує серверні налаштування за умовчанням, незважаючи на те, що так може здатися. Навіть більше, якщо ви не вкажете значення коефіцієнта заповнення індексу, то SQL Serverбуде використовувати значення коефіцієнта, з яким цей індекс існував до його перебудови. Наприклад, наступна операція ALTER INDEXперебудовує щойно створений нами індекс:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Коли ми перевіримо значення коефіцієнта заповнення, ми отримаємо значення, що дорівнює 80, тому що саме його ми вказали при останньому створенні індексу. Значення за промовчанням не враховується.
Як ви бачите змінити значення коефіцієнта заповнення індексу не така вже складна справа. Набагато складніше знати поточне значення і розуміти, коли воно застосовується. Якщо ви завжди конкретно вказується коефіцієнт під час створення та перебудови індексів, то ви завжди знаєте конкретний результат. Хіба що вам доводиться дбати про те, щоб хтось інший знову не напортачив в налаштуваннях сервера, викликавши перебудову всіх індексів зі смішно низьким значенням коефіцієнта заповнення індексу.

Чи можна створити кластеризований індекс на стовпці, що містить дублікати?

І так і ні. Так ви можете створити кластеризований індекс на ключовому стовпці, що містить дублікати значень. Ні, значення ключового стовпця не зможуть залишитись у стані не унікальності. Дозвольте пояснити. Якщо ви створюєте неунікальний кластерний індекс (non-unique clustered index) на стовпці, то підсистема зберігання даних додає до дублюючого значення цілочисельне значення (uniquifier), щоб переконатися в унікальності і, відповідно, забезпечити ідентифікувати кожний рядок кластеризованої таблиці.
Наприклад, ви можете вирішити створити в таблиці з даними про клієнтів кластеризований індекс по стовпцю LastName, що зберігає прізвище. Стовпець містить такі значення як Franklin, Hancock, Washington та Smith. Потім ви вставляєте значення Adams, Hancock, Smith і знову Smith. Але значення ключового стовпця обов'язково повинні бути унікальними, тому підсистема зберігання даних змінить значення дублікатів таким чином, що вони виглядатимуть приблизно так: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 та Smith5678.
На перший погляд такий підхід здається нормальним, але ціле значення збільшує розмір ключа, що може стати проблемою при великій кількості дублікатів, а ці значення стануть основою некластеризованого індексу або посиланням зовнішнього ключа. З цих причин ви завжди повинні намагатися створювати унікальний кластеризований (unique clustered indexes) за будь-якої можливості. Якщо це неможливо, то принаймні постарайтеся використовувати стовпці з дуже високим вмістом унікальних значень.

Як зберігається таблиця, якщо не було створено кластеризованого індексу?

SQL Serverпідтримує два типи таблиць: кластеризовані таблиці, що мають кластеризований індекс та таблиці-купи або просто купи. На відміну від кластеризованих таблиць дані купі не сортовані жодним чином. По суті, це і є нагромадження (купа) даних. Якщо ви додасте рядок до такої таблиці, підсистема зберігання даних просто додасть її до кінця сторінки. Коли сторінка заповниться даними, вони будуть додані на нову сторінку. У більшості випадків, ви захочете створити кластеризований індекс на таблиці, щоб отримати переваги від можливості сортування та прискорення запитів (спробуйте уявити собі знайти телефонний номер в адресній книзі, яка не відсортована за будь-яким принципом). Однак, якщо ви вирішите не створювати кластеризований індекс, то ви, як і раніше, можете створити у купи некластеризований індекс. У цьому випадку кожен рядок індексу матиме вказівник на рядок купи. Вказівник включає ідентифікатор файлу, номер сторінки та номер рядка з даними.

Який взаємозв'язок між обмеженнями на унікальність значення та первинним ключем з індексами таблиці?

Первинний ключ та обмеження унікальності забезпечують, що значення в стовпці будуть унікальні. Ви можете створити лише один первинний ключ у таблиці і він не може містити значення NULL. Ви можете створити у таблиці кілька обмежень на унікальність значення і кожен з них може мати єдиний запис з NULL.
Коли ви створюєте первинний ключ, підсистема зберігання даних також створює унікальний кластеризований індекс, якщо вже кластеризований індекс не був створений. Однак, ви можете перевизначити встановлену за умовчанням поведінку, і тоді буде створено некластеризований індекс. Якщо кластеризований індекс існує, коли ви створюєте первинний ключ, то буде створено унікальний некластеризований індекс.
Коли ви створюєте обмеження на унікальність, система зберігання даних створює унікальний некластеризований індекс. Але ви можете вказати створення унікального кластеризованого індексу, якщо його не було створено раніше.
У загальному випадку, обмеження на унікальність значення та унікальний індекс це одне й те саме.

Чому в SQL Server кластеризовані та некластеризовані індекси називаються збалансованим деревом?

Базові індекси в SQL Server, кластеризовані чи некластеризовані, поширюються наборами сторінок – вузлам індексу. Ці сторінки організовані як певної ієрархії з деревоподібної структурою, званої збалансованим деревом. На верхньому рівні знаходиться кореневий вузол, на нижньому, кінцеві вузли листя з проміжними вузлами між верхнім і нижнім рівнями, як показано на малюнку:


Кореневий вузол надає головну точку входу для запитів, які намагаються отримати дані через індекс. Починаючи з цього вузла, підсистема запитів ініціює перехід по ієрархічній структурі вниз до відповідного кінцевого вузла, що містить дані.
Наприклад, уявімо, що надійшов запит на вибірку рядків, що містять значення ключа, що дорівнює 82. Підсистема запитів починає роботу з кореневого вузла, який відсилає до відповідного проміжного вузла, в нашому випадку 1-100. Від проміжного вузла 1-100 відбувається перехід до вузла 51-100 а звідти до кінцевого вузла 76-100. Якщо це кластеризований індекс, то на аркуші вузла міститься дані рядка, асоційованого з ключем рівним 82. Якщо ж це некластеризований індекс, то лист індексу містить покажчик на кластеризовану таблицю або конкретний рядок у купі.

Як взагалі індекс може покращити продуктивність запитів, якщо доводиться переходити всіма цими індексними вузлами?

По-перше, індекси не завжди покращують продуктивність. Занадто багато невірно створених індексів перетворюють систему на болото і знижують продуктивність запитів. Правильніше сказати, що якщо індекси були акуратно застосовані, то вони можуть забезпечити значний приріст у продуктивності.
Подумайте про величезну книгу, присвячену настроюванню продуктивності SQL Server(паперовий, не про електронний варіант). Уявіть, що ви хочете знайти інформацію про конфігурування Регулятора ресурсів . Ви можете водити пальцем посторінково через всю книгу або відкрити зміст і дізнатися точний номер сторінки з інформацією, що шукається (за умови, що книга правильно проіндексована і у змісті вірні покажчики). Безумовно, це заощадить вам значний час, незважаючи на те, що вам треба спочатку звернутися до зовсім іншої структури (індексу), щоб отримати необхідну інформацію з первинної структури (книги).
Як і книжковий покажчик, покажчик у SQL Serverдозволяє виконувати точні запити до потрібних даних замість повного сканування всіх даних, що містяться в таблиці. Для маленьких таблиць повне сканування зазвичай не проблема, але великі таблиці займають багато сторінок з даними, що в результаті може привезти значного часу виконання запиту, якщо не існує індексу, що дозволяє підсистемі запитів відразу отримати правильне розташування даних. Уявіть, що ви заблукали на багаторівневій дорожній розв'язці перед великим мегаполісом без карти і ви зрозумієте ідею.

Якщо індекси настільки чудові, то чому просто не створити їх на кожен стовпець?

Жодна добра справа не повинна залишатися безкарною. Принаймні, саме так і справа з індексами. Зрозуміло, індекси відмінно себе показують, поки ви виконуєте запити на вибір даних оператором SELECT, але щойно починається частий виклик операторів INSERT, UPDATEі DELETEтак пейзаж дуже швидко змінюється.
Коли ви ініціюєте запит даних оператором SELECT, підсистема запитів знаходить індекс, просувається за його деревоподібною структурою і виявляє дані, що шукаються. Що може бути простішим? Але все змінюється, якщо ви ініціюєте оператор зміни, такої як UPDATE. Так, для першої частини оператора підсистема запитів може знову використовувати індекс для виявлення рядка, що модифікується - це хороші новини. І якщо відбувається проста зміна даних у рядку, що не торкається зміни ключових стовпців, то процес зміни пройде цілком безболісно. Але що, якщо зміна призведе до поділу сторінок, що містять дані, або буде змінено значення ключового стовпця, що призводить до перенесення його в інший індексний вузол - це призведе до того, що індексу може знадобитися реорганізація, що стосується всіх зв'язаних індексів і операцій, в результаті буде повсюдне падіння продуктивності.
Аналогічні процеси відбуваються під час виклику оператора DELETE. Індекс може допомогти знайти місцезнаходження видалених даних, але саме собою видалення даних може призвести до перестановки сторінок. Щодо оператора INSERT, головного ворога всіх індексів: ви починаєте додавати велику кількість даних, що призводить до зміни індексів та їх реорганізації і всі страждають.
Так що враховуйте види запитів до вашої бази даних при роздумах, який тип індексів і в якій кількості варто створювати. Більше не означає краще. Перед тим як додати новий індекс на таблицю прорахуйте вартість не тільки базових запитів, але й обсяг дискового простору, вартість підтримки працездатності та індексів, що може призвести до ефекту доміно для інших операцій. Ваша стратегія проектування індексів є одним з найважливіших аспектів впровадження і повинна включати до розгляду безліч міркувань: від розміру індексу, кількості унікальних значень до типу запитів, що підтримуються індексом.

Чи обов'язково створювати кластеризований індекс на стовпчику з первинним ключем?

Ви можете створити кластеризований індекс на будь-якому стовпчику, який відповідає необхідним умовам. Це вірно, що кластеризований індекс і обмеження первинного ключа створені один для одного і їх шлюб укладений на небесах, так що засвоїть факт, що коли ви створюєте первинний ключ, тоді буде автоматично створений кластеризований індекс, якщо він не був створений раніше. Тим не менш, ви можете вирішити, що кластеризований індекс краще працюватиме в іншому місці, і часто ваше рішення буде цілком виправданим.
Головна мета кластеризованого індексу - сортування всіх рядків до вашої таблиці на основі ключового стовпця, вказаного при визначенні індексу. Це забезпечує швидкий пошук та легкий доступ до даних таблиці.
Первинний ключ таблиці може бути хорошим вибором, тому що він однозначно ідентифікує кожен рядок таблиці без необхідності додавати додаткові дані. У деяких випадках кращим вибором буде сурогатний первинний ключ, що має не тільки ознаку унікальності, але і малий розмір, а значення якого збільшуються послідовно, що робить некластеризовані індекси, засновані на цьому значенні більш ефективними. Оптимізатор запитів також любить таке поєднання кластеризованого індексу та первинного ключа, тому що з'єднання таблиць відбувається швидше, ніж при з'єднанні іншим способом, що не використовує первинний ключ та асоційований з ним кластеризований індекс. Як я й казав це шлюб, укладений на небесах.
В кінці варто, однак, відзначити, що при створенні кластеризованого індексу необхідно взяти до уваги кілька аспектів: як багато некластеризованих індексів буде ґрунтуватися на ньому, як часто змінюватимуться значення ключового стовпця індексу і на скільки не більші. Коли значення у стовпцях кластеризованого індексу зміняться або індекс не забезпечуватиме належної продуктивності, тоді всі інші індекси таблиці можуть бути зачеплені. Кластеризований індекс повинен бути заснований на найбільш стійкому стовпці, значення якого збільшуються у порядку, але не змінюються у випадковому. Індекс повинен підтримувати запити до даних таблиці, що найчастіше використовуються, таким чином запити отримують всі переваги того, що дані сортовані і доступні на кореневих вузлах, листі індексу. Якщо первинний ключ відповідає цьому сценарію, використовуйте його. Якщо ж ні, виберіть інший набір стовпців.

А що якщо проіндексувати виставу, то це, як і раніше, буде вистава?

Подання – це віртуальна таблиця, яка формує дані з однієї чи кількох таблиць. По суті, це іменований запит, який отримує дані з таблиць нижче, коли ви викликаєте запит до цього подання. Ви можете покращити продуктивність запитів, створивши кластеризований індекс і некластеризовані індекси у цього уявлення, аналогічно як ви створюєте індекси у таблиці, але основний нюанс полягає в тому, що спочатку створюється кластеризований індекс, а потім ви можете створити некластеризований.
Коли створюється індексоване уявлення (матеріалізоване уявлення), тоді саме визначення уявлення залишається окремою сутністю. Це, зрештою, лише жорстко прописаний оператор SELECT, що зберігається у базі даних. А ось індекс — зовсім інша історія. Коли ви створюєте кластеризований або некластеризований індекс у запобігання, дані фізично зберігаються на диск, аналогічно звичайному індексу. На додаток, коли в таблицях нижче змінюються дані, то індекс подання автоматично змінюється (це означає, що ви можете захотіти уникнути індексування уявлень тих таблиць, в яких відбуваються часті зміни). У будь-якому випадку, уявлення залишається поданням - поглядом на таблиці, але саме виконаним на даний момент, з індексами, що йому відповідають.
Перед тим, як ви зможете створити індекс у подання, воно має відповідати декільком обмеженням. Наприклад, уявлення може посилатися лише з базові таблиці, але з інші уявлення і ці таблиці повинні бути у тій самій базі даних. Насправді там багато інших обмежень, так що не забудьте звернутися до документації щодо SQL Serverза всіма брудними подробицями.

Навіщо використовувати покриваючий індекс замість складеного індексу?

По-перше, давайте переконаємося, що ми розуміємо різницю між ними. Складовий індекс це просто звичайний індекс, до якого включено більше одного стовпця. Кілька ключових стовпців може використовуватися для забезпечення унікальності кожного рядка таблиці, також можливий варіант, коли первинний ключ складається з декількох стовпців, що забезпечують його унікальність, або ви намагаєтеся оптимізувати виконання запитів, що часто викликаються, до кількох стовпців. Загалом, проте, що більше ключових стовпців містить індекс, то менш ефективна робота цього індексу, отже складові індекси варто використовувати розумно.
Як було сказано, запит може отримати величезний зиск, якщо всі необхідні дані відразу розташовані на листі індексу, як і сам індекс. Не проблема кластеризованого індексу, т.к. всі дані вже там (ось чому так важливо добре подумати коли ви створюєте кластеризований індекс). Але некластеризований індекс на листі містить лише ключові стовпці. Для доступу до інших даних оптимізатору запитів необхідні додаткові кроки, що може спричинити значні додаткові накладні витрати для виконання ваших запитів.
Ось де індекс, що покриває, поспішає на допомогу. Коли ви визначаєте некластеризований індекс, можете вказати додаткові стовпці до ваших ключових. Наприклад, уявимо, що ваш додаток часто запитує дані стовпців OrderIDі OrderDateв таблиці Sales:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Ви можете створити складовий некластеризований індекс на обох стовпцях, але стовпець OrderDate тільки додасть накладних витрат на обслуговування індексу, але так і не зможе служити особливо корисним ключовим стовпцем. Найкраще рішення буде це створення індексу, що покриває, з ключовим стовпцем OrderIDта додатково включеним стовпцем OrderDate:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
При цьому ви уникаєте недоліків, що виникають при індексації зайвих стовпців, водночас зберігаєте переваги зберігання даних на листі під час виконання запитів. Включений стовпець не є частиною ключа, але дані зберігаються саме на кінцевому вузлі, аркуші індексу. Це може покращити продуктивність виконання запиту без будь-яких додаткових витрат. До того ж, на стовпці, включені в індекс, що покриває, накладається менше обмежень, ніж на ключові стовпці індексу.

Чи має значення кількість дублікатів у ключовому стовпці?

Коли ви створюєте індекс, ви повинні постаратися зменшити кількість дублікатів у ключових стовпцях. Або точніше: намагатися тримати коефіцієнт повторюваних значень настільки низьким, наскільки це можливо.
Якщо ви працюєте зі складовим індексом, то дублювання відноситься до всіх ключових стовпців загалом. Окремий стовпець може містити безліч значень, що повторюються, але повторення серед усіх стовпців індексу має бути мінімальним. Наприклад, ви створюєте складовий некластеризований індекс на стовпцях FirstNameі LastName, ви можете мати безліч значень рівних John і безліч Doe, але ви хочете мати якнайменше значень John Doe, або краще тільки одне значення John Doe.
Коефіцієнт унікальності значень ключового стовпця називається вибірковістю індексу. Чим більше унікальних значень, тим вища вибірковість: унікальний індекс має найбільшу можливу вибірковість. Підсистема запитів дуже любить стовпці з високим значенням вибірковості, особливо якщо ці стовпці беруть участь в умовах вибірки WHERE ваших найбільш часто виконуваних запитів. Що вище вибірковість індексу, то швидше підсистема запитів може зменшити розмір результуючого набору даних. Зворотною стороною, зрозуміло, є те, що стовпці з відносно невеликою кількістю унікальних значень рідко будуть хорошими кандидатами на індексування.

Чи можна створити некластеризований індекс лише для певного підмножини даних ключового стовпця?

За умовчанням, некластеризований індекс містить один рядок для кожного рядка таблиці. Звичайно, ви можете сказати те саме щодо кластеризованого індексу, враховуючи, що такий індекс це і є таблиця. Але щодо некластеризованого індексу, то ставлення «один до одного» важливий концепт, тому що, починаючи з версії SQL Server 2008, у вас є можливість створити індекс, що фільтрується, який обмежує включені в нього рядки. Індекс, що фільтрується, може поліпшити продуктивність виконання запитів, т.к. він менший за розміром і містить відфільтровану, акуратнішу, статистику, ніж вся таблична - це призводить до створення покращених планів виконання. Фільтрований індекс також вимагає менше місця для зберігання та менших витрат на обслуговування. Індекс оновлюється лише коли змінюються підходящі під фільтр дані.
На додаток, індекс, що фільтрується, легко створити. В операторі CREATE INDEXпросто необхідно вказати в WHEREумова фільтрації. Наприклад, ви можете відфільтрувати з індексу всі рядки, що містять NULL, як показано в коді:
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IS NOT NULL;
Ми можемо фактично відфільтрувати будь-які дані, які не важливі в критичних запитах. Але будьте уважні, т.к. SQL Serverнакладає кілька обмежень на індекси, що фільтруються, такі, як неможливість створити індекс, що фільтрується, у подання, так що уважно читайте документацію.
Також може статися, що ви можете досягти подібних результатів створенням індексованого уявлення. Однак, фільтрований індекс має кілька переваг, таких як можливість зменшити вартість обслуговування та покращити якість ваших планів виконання. Індекси, що фільтруються, також допускають перебудову в онлайн-режимі. Спробуйте це зробити з поданням, що індексується.

І знову трохи від перекладача

Метою появи цього перекладу на сторінках Хабрахабра було розповісти чи нагадати вам про блог SimpleTalk від RedGate.
У ньому публікується безліч цікавих та цікавих записів.
Я не пов'язаний з продуктами фірми RedGate, ні з їх продажем.

Як і обіцяв, книги для тих, хто хоче знати більше
Порекомендую від себе три дуже хороші книги (посилання ведуть на kindleверсії у магазині Amazon):

В принципі, можна відкрити просто індекси

новачкам

індекс

Додати теги

Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference) Author Itzik Ben-Gan Publication Date: Липень 15, 2012 Автор, майстер своєї справи, дає базові знання роботи з базами даних. Якщо ви все забули або ніколи не знали, то безперечно варто її прочитати

Індекси ROWID- це об'єкти бази даних, що забезпечують відображення всіх значень стовпця таблиці, а також ідентифікаторів ROWID всіх рядків таблиці, які містять значення стовпця.

ROWID- це псевдостовпець, який є унікальним ідентифікатором рядка в таблиці та фактично описує точне фізичне розташування даного конкретного рядка. На основі цієї інформації Oracleзгодом може знайти дані, пов'язані з рядком таблиці. При кожному переміщенні, експорті, імпорті рядка, а також під час виконання будь-яких інших операцій, що призводять до зміни її місцезнаходження, змінюється ROWIDрядки, оскільки вона займає інше фізичне становище. Для зберігання даних ROWIDпотрібно 80 біт (10 байт). Ідентифікатори ROWIDскладаються із чотирьох компонентів: номери об'єкта (32 біти), відносного номера файлу (10 біт), номери блоку (22 біти) та номери рядка (16 біт). Ці ідентифікатори відображаються як 18-символьні послідовності, що вказують місцезнаходження даних БД, причому кожен символ представлений у форматі base-64, що складається з символів A-Z, a-z, 0-9, + і /. Перші шість символів – це номер об'єкта даних, наступні три – відносний номер файлу, наступні шість – номер блоку, останні три – номер рядка.

Приклад:

SELECT fam, ROWID FROM student;

FAM ROWID

——————————————

ІВАНІВ AAAA3kAAGAAAAGsAAA

ПЕТРІВ AAAA3kAAGAAAAGsAAB

У базі даних Oracleіндекси використовуються для різних цілей: для забезпечення унікальності значень в базі даних, для підвищення продуктивності пошуку записів у таблиці та ін. У Oracleіндекси можна створювати за будь-яким стовпцем таблиці, крім стовпців типу LONG. Індекси проводять різницю між додатками, котрим швидкість не важлива, і інтенсивно функціонуючими додатками, що стосується роботи з великими таблицями. Однак, перш ніж ухвалити рішення про створення індексу, необхідно зважити всі «за» та «проти» щодо продуктивності системи. Продуктивність не підвищиться, якщо просто ввести індекс та забути про нього.

Хоча найбільше підвищення продуктивності досягається створенням індексу стовпця, всі значення якого унікальні, схожий результат можна отримати і для стовпців, що містять однакові значення або NULL-значення. Для створення індексу зовсім не обов'язково, щоб значення стовпця були унікальними. Наведемо ряд рекомендацій, які забезпечують необхідне підвищення продуктивності під час використання стандартного індексу, і навіть розглянемо питання, пов'язані з балансом між продуктивністю і витратою дискового простору під час створення індексу.

Використання індексів для пошуку інформації в таблицях може дати значне підвищення продуктивності, порівняно з переглядом таблиць, стовпці яких неіндексовані. Проте вибрати правильний індекс зовсім непросто. Звичайно, для індексування за допомогою індексу В-дерева переважний стовпець, всі значення якого унікальні, але і стовпець, що не відповідає цим вимогам, - непоганий кандидат, якщо тільки однакові значення містяться приблизно в 10% його рядків і не більше. Стовпці-«перемикачі», або «прапори», наприклад ті в яких зберігаються відомості про поле людини, для індексів В-дерева не годяться Не підходять і ті стовпці, які використовуються для зберігання невеликої кількості «достовірних значень», а також які- то ознаки, наприклад «достовірність» або «недостовірність», «активність» або «неактивність», «так» або «ні» і т. д., і т. д. де встановлений та функціонує Oracle Parallel Server і потрібно до максимуму підвищити рівень паралельності у базі даних.