Образовательный проект «SnakeProject» Михаила Козлова

⇒ SQL\T-SQL ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

FreeBSD and Nix

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

T-SQL 2012: Индексы

--Индекс является структурой на диске, которая связана с таблицей или представлением и ускоряет получение строк из таблицы или представления. Индекс содержит ключи, построенные из одного или нескольких столбцов в таблице или представлении. Эти ключи хранятся в виде структуры сбалансированного дерева, которая поддерживает быстрый поиск строк по их ключевым значениям в 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 = 100000;
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; 

Комментарии пользователей