Спочатку це не планувалося як стаття. Я просто експериментував з SQL і документував деякі моменти, а потім подумав, що це може вийти в хорошу статтю. Я написав багато статей і створив безліч відео про C#, .NET Core та Angular. Але це моя перша стаття про SQL.
У цьому блозі ми розглянемо кілька термінів виконуваного плану, таких як сканування таблиці, сканування кластеризованого індексу, пошук індексу, пошук RID і пошук по ключу.
⚠️ Якщо ви будете виконувати запити під час читання, це буде більш корисно.
Інструменти та технології, які використовуються
- SQL Server 2022
- SQL Server Management Studio (GUI Tool)
Отже, давайте почнемо.
Щоб увімкнути план виконання, виберіть вкладку включити фактичний план виконання(ctrl+m) як показано на малюнку. Потім виконайте запит як зазвичай, і ви побачите вкладку execution plan, де можна побачити план виконання.
план виконання
Купи і сканування таблиці
Використовуйте наступний скрипт для створення таблиці.
use master;
go
drop table if exists users;
create table users(
id int identity,
username nvarchar(100)
);
go
insert into users(username)
values
('ramesh'),('Abhishek'),('arun');
go
Таблиця users не має жодного первинного ключа. Насправді, в цій таблиці немає жодного ключа. Давайте виконаємо наступний запит.
select id,username from users;
План виконання:
Як ви помітили, всього 3 рядки були прочитані, і SQL Server виконав сканування таблиці, що означає, що йому потрібно було сканувати всі рядки. Це очікувано, оскільки ми отримуємо всі дані.
Тепер виконаємо той самий запит з умовою WHERE.
select id,username from users where id=1;
план виконання:
сканування таблиці за умови WHERE
Є лише один запис з id=1, і SQL Server читає 3 рядки (всього 3 рядки). Щоб отримати лише один запис, SQL Server має сканувати всю таблицю. Це називається скануванням таблиці.
Тепер виконаємо інший запит.
select id,username from users where username='abhishek';
Як і в попередньому запиті, буде виконано сканування таблиці, і SQL Server прочитає всі рядки, щоб отримати запис з username = ‘abhishek’. Якщо б у нас було тисячі рядків, він би прочитав усі тисячі рядків. Щоб знайти один запис, ми повинні сканувати всю таблицю. Це не дуже добре, якщо ми маємо великий набір даних.
Зараз наша таблиця не має жодного кластеризованого індексу. У SQL Server таблиця без кластеризованого індексу називається heap. Це означає, що наші дані не зберігаються фізично в відсортованому порядку.
Кластеризований індекс і сканування кластеризованого індексу
Додамо первинний ключ до id.
alter table users
add constraint pk_users_id primary key (id);
Створивши первинний ключ, автоматично створюється кластеризований індекс. Кластеризований індекс додається до стовпця id. Тепер таблиця більше не є heap. Коли таблиця має кластеризований індекс, таблиця називається кластеризованою таблицею. Таблиця зберігається фізично в відсортованому порядку (відсортованому за id).
Тепер виконаємо цей запит.
select id,username from users where username='abhishek';
сканування кластеризованого індексу
Як ви помітили в плані виконання, відображається Clustered Index Scan. Щоб знайти запис з username = ‘abhishek’, SQL Server має сканувати всі рядки з кластеризованого індексу.
Це називається скануванням кластеризованого індексу.
Пошук кластеризованого індексу
Давайте виконаємо цей запит.
select id,username from users where id=1;
пошук кластеризованого індексу
Тепер ви помітили Пошук кластеризованого індексу в плані виконання, і SQL Server читає лише 1 рядок, щоб отримати запис з id=1.
- Ми шукаємо запис з id=1. База даних точно знає, де шукати. Це як книга, де всі записи організовані за номерами сторінок. Нам просто потрібно відкрити сторінку 1 (або будь-яку іншу), і ми можемо отримати інформацію на цій сторінці.
- Подібно до книг, SQL Server безпосередньо переходить до запису з id=1 і читає відповідні стовпці. Це називається
пошук індексу, що є найбільш ефективним.
Не кластеризований індекс на таблиці heap
Давайте видалимо таблицю і створимо її знову. Тепер я видаляю первинний ключ і створю індекс на стовпці username.
use master;
go
drop table if exists users;
create table users(
id int identity,
username nvarchar(100)
);
go
create index idx_user_username on users(username);
go
insert into users(username)
values
('ramesh'),('Abhishek'),('arun');
go
Тепер давайте виконаємо запит.
select id,username from users where id=1;
сканування таблиці 2
План виконання показує, що це сканування таблиці. Це очевидно, оскільки в нас немає індексу на стовпці id, і ми фільтруємо записи за id.
Тепер давайте спробуємо фільтрувати записи за username.
сканування таблиці через невеликий набір даних
План виконання говорить, що це сканування таблиці. Але це повинно бути пошук індексу, оскільки у нас є індекс на стовпці username. Чому це сканування таблиці? Зараз у таблиці всього 3 записи. SQL Server вважає, що сканування таблиці буде значно швидшим, ніж пошук індексу. Давайте додамо ще кілька записів. Я збираюся вставити 999997 записів у таблицю користувачів.
;with numbers
as
(
select 4 as n
union all
select n+1 from numbers
where n<999997
)
insert into users(username)
select concat('name',n) from numbers
option (maxrecursion 0);
Я використав рекурсивний CTE для швидшого вставлення.
⚠️ Я помилково порахував і не зміг додати 1 млн записів.🤭
Тепер давайте виконаємо той самий запит.
select id,username from users where username='abhishek';
не кластеризований пошук і RID пошук
Тепер SQL Server виконав пошук індексу (не кластеризований).
Оскільки наша таблиця не має первинного ключа, це означає, що в неї немає кластеризованого індексу, отже, наша таблиця є heap. І записи не зберігаються фізично в відсортованому порядку.
Ось як зберігається heap з не кластеризованим індексом (не зовсім так, але щось подібне).
Таблиця heap (користувачі) 👇
| RID | id | username |
| --- | --- | -------- |
| xx1 | 1 | ramesh |
| xx2 | 2 | abhishek |
| xx3 | 3 | arun |
Коли таблиця зберігається як heap, кожен рядок має 8-байтний ідентифікатор рядка (RID). Це складається з номера файлу, номера сторінки даних і слота на сторінці (FileID:PageID:SlotID). Heap таблиця є неструктурованою, записи зберігаються без будь-якого порядку.
Тепер давайте зрозуміємо, як зберігаються не кластеризовані індекси.
Не кластеризований індекс на username 👇
| username | RID |
| -------- | --- |
| Abhishek | xx2 |
| arun | xx3 |
| ramesh | xx1 |
Не кластеризований індекс username зберігається в відсортованому порядку. Кожен username пов'язаний з RID (ідентифікатор рядка), який є вказівником на heap таблицю.
Використовуючи row id, SQL Server шукає залишкові стовпці таблиці.
Коли ми виконуємо запит SELECT id, username FROM users WHERE username = 'abhishek', SQL Server виконує наступні дії:
- Використовує не кластеризований індекс, щоб знайти ‘abhishek’ і отримати його RID (xx2)
- Використовує цей RID, щоб безпосередньо знайти рядок у таблиці heap
- Отримує потрібні стовпці з цього рядка
Пошук індексу (не кластеризований)
Тепер давайте додамо первинний ключ на стовпець id.
alter table users
add constraint pk_user_id primary key (id);
⚠️ Додавши первинний ключ, таблиця users більше не є heap, вона стає кластеризованим індексом.
Тепер давайте виконаємо цей запит.
select id,username from users where username='abhishek';
пошук індексу (не кластеризований)
План виконання досить простий. База даних виконує пошук індексу (не кластеризований). SQL Server прочитає лише один рядок, щоб отримати потрібний запис.
Тепер давайте додамо новий стовпець з назвою name в таблицю users та додамо за замовчуванням значення no-name.
alter table users
add [name] nvarchar(30) not null default 'no-name';
Тепер давайте виконаємо цей запит.
select id,username,[name] from users where username='abhishek';
план виконання:
пошук ключа
пошук ключа
Як вказано в плані виконання, SQL Server вибрав пошук індексу та пошук ключа для цього запиту.
Наша таблиця має кластеризований індекс і не кластеризований індекс. Зараз ми отримуємо id, username та name. Стовпець id має кластеризований індекс, username має не кластеризований індекс, але name не має індексу. Оскільки ми отримуємо стовпець (name), який не є частиною не кластеризованого індексу, то база даних виконує пошук ключа. Це не відбувалося в випадку запиту select id,username from users where username='abhishek'. Оскільки всі стовпці, які потрібно отримати, є частиною деяких індексів.
Пошук ключа — це процес отримання додаткових даних з таблиці (з кластеризованим індексом), які не є частиною не кластеризованого індексу, що використовується для запиту.
Коли SQL Server виконує запит, використовуючи не кластеризований індекс і потрібно отримати стовпці, які не є частиною цього індексу, він виконує наступні кроки:
- Знаходить рядки в не кластеризованому індексі
- Потім використовує ключ кластеризованого індексу (або RID для heap) збережений у не кластеризованому індексі
- Нарешті, виконує пошук додаткових стовпців у кластеризованому індексі (або heap).
Що відбувається, коли ми виконуємо наш запит select select id,username,[name] from users where username='abhishek'?
У нашому випадку не кластеризований індекс (на username) виглядає так:
| username | id |
| -------- | --- |
| Abhishek | 2 |
| arun | 3 |
| ramesh | 1 |
Оскільки username використовується в умові WHERE і username має не кластеризований індекс. SQL Server виконує наступні дії:
- Він сканує не кластеризований індекс на
username, щоб знайти записи деusername='abhishek'. - Цей не кластеризований індекс містить значення
usernameта вказівник (кластеризований індекс, який єidв нашому випадку).
Оскільки запит також потребує стовпець name, який не є частиною не кластеризованого індексу, він не може знайти name в не кластеризованому індексі, тому він має шукати його. Як він буде шукати це?
- Він використовує
id, знайдений у не кластеризованому індексі, який пов'язаний з кластеризованим індексом (нашою таблицею користувачів).abhishekасоціюється зid=2в не кластеризованому індексі. - SQL Server шукатиме
id=2у кластеризованій таблиці (таблиці користувачів) і може легко отримати рядок.
Підсумок основних концепцій
Heap
Таблиця без кластеризованого індексу називається heap.
У таблиці heap кожен рядок має ідентифікатор рядка 8 байтів (RID). Це складається з номера файлу, номера сторінки даних та слота на сторінці (FileID:PageID:SlotID). Таблиця heap є неструктурованою, записи зберігаються без будь-якого порядку. Heap мають свої особливі випадки використання, про які ви можете дізнатися більше за посиланнями, наданими в кінці.
Пошук RID
Пошук у таблиці heap за допомогою ідентифікатора рядка (RID). Не кластеризовані індекси включають row id для знаходження даних таблиці.
Пошук ключа
Пошук ключа — це процес отримання додаткових даних, які не є частиною не кластеризованого індексу, з кластеризованої таблиці (таблиці з кластеризованим індексом). Пошук ключа використовує кластеризований ключ (зазвичай первинний ключ) як вказівник, на відміну від RID, як у heap.
Сканування таблиці
Якщо таблиця не має індексу, механізм бази даних повинен сканувати всю таблицю, щоб знайти записи, що називається скануванням таблиці. Сканування таблиці зазвичай вважається поганим, але не завжди, якщо у вас є дуже маленький набір даних, SQL Server може вибрати сканування таблиці замість пошуку індексу.
Сканування кластеризованого індексу
Механізм бази даних переглядає всі записи в кластеризованій таблиці (таблиці з кластеризованим індексом, яка фізично відсортована).
Сканування кластеризованого індексу vs сканування таблиці
З скануванням таблиці, механізм бази даних сканує дані з heap. З скануванням кластеризованого індексу, механізм бази даних сканує дані з кластеризованої таблиці.
Пошук індексу
Безпосередньо перейдіть до запису.
Посилання
- Плани виконання запитів SQL Server для початківців — Оператори кластеризованих індексів
- Яка різниця між скануванням таблиці та скануванням кластеризованого індексу?
- Пошук ключа і RID в планах виконання SQL Server
- Heap (таблиці без кластеризованих індексів)
- SQL SERVER Heap: розуміння їх переваг та обмежень
- Що таке таблиця heap | Повне сканування таблиці
- SQL SERVER — Heap, Сканування та RID Lookup
Будь ласка, поставте лайк і поділіться, якщо цей пост допоміг вам.
Зв'язатися зі мною
Перекладено з: SQL SERVER: Table scan, clustered index scan, index seek, RID lookup, Key lookup