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

⇒ SQL\T-SQL ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

FreeBSD and Nix

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

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

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

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

USE test;
GO

--В SQL Server индексы организованы в виде сбалансированных деревьев. 
--Каждая страница в сбалансированном дереве индекса называется узлом индекса. 

--Теория индексов.

-- SQL Server хранит данные на страницах размером 8 килобайт – 8,060 байт

-- Страницы принадлежащие объекту(например таблице) связаны в двунаправленный список

-- Первые 8 страниц «объекта» хранятся в смешанных участках. После этого данные хранятся только в унифицированных участках.

-- 8 страниц группируются в «участки». Смешаные участки хранят данные из разных «объектов». 
--    Унифицированные участки хранят данные одного «объекта»

-- SQL Server использует страницы именуемыми - «карты размещения индексов» (Index Allocation Map) 
--    или кратко - IAM для определения страниц принадлежащих «объекту»

--Кучи – это данные, хранящиеся без какой-либо определенной сортировки, 
--    не имеющие индексов, доступ и поиск по таким данным происходит последовательно при сканировании страниц, 
--    и может занимать довольно долгое время влияя негативно на производительность.

--Кучи подходят для хранения небольшого количества данных.

--Существуют два типа индексов: кластеризованные и некластеризованные. 

--Кластеризованный индекс хранит в своих узлах-листьях реальные строки данных.

--Некластеризованный индекс является вспомогательной структурой, которая указывает данные в таблице

--Кластеризованный индекс
--Кластеризованные индексы сортируют и хранят строки данных в таблицах или представлениях на основе их ключевых значений. 
--Этими значениями являются столбцы, включенные в определение индекса. 
--Существует только один кластеризованный индекс для каждой таблицы, 
--потому что строки данных могут быть отсортированы только в единственном порядке.
--Строки данных в таблице хранятся в порядке сортировки только в том случае, 
--если таблица содержит кластеризованный индекс. 
--Если у таблицы есть кластеризованный индекс, то таблица называется кластеризованной. 
--Если у таблицы нет кластеризованного индекса, то строки данных хранятся в неупорядоченной структуре, которая называется кучей.
--Кластеризованный индекс реализуется в виде сбалансированного дерева, 
--которое поддерживает быстрое получение строк по значениям ключа кластеризованного индекса.

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

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

CREATE TABLE test.dbo.ind
(
    ID int NOT NULL,
    T char(8) NULL,
    U char(9) NOT NULL,
);
GO

--Добавим тестовые данные
DECLARE @i INT = (SELECT RAND() * 9999);
DECLARE @t CHAR(1) = 'T';

WHILE @i > 0
BEGIN
    INSERT INTO  test.dbo.ind VALUES(@i, @t + CAST(@i AS CHAR(6)), @t + CAST(@i AS CHAR(6)) + 'u');
    SET @i -= 1;
END

--Информация об индексах до создания
SELECT index_type_desc, index_depth, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats 
    (DB_ID(N'test'), OBJECT_ID(N'dbo.ind'), NULL, NULL , 'DETAILED');

--Создание кластеризованного индекса
CREATE CLUSTERED INDEX cluster_index
    ON test.dbo.ind (ID); 

--Создание некластеризованного индекса для таблицы
 CREATE NONCLUSTERED INDEX noncluster_index
    ON test.dbo.ind (T); 

--Создание уникального индекса
CREATE UNIQUE INDEX uniq_index 
   ON test.dbo.ind (U); 

--Информация об индексах после создания
SELECT index_type_desc, index_depth, index_level, page_count, record_count
FROM sys.dm_db_index_physical_stats 
    (DB_ID(N'test'), OBJECT_ID(N'dbo.ind'), NULL, NULL , 'DETAILED');

--STATISTICS IO - позволяет отображать в SQL Server сведения об активности диска, связанной с выполнением инструкций 
--Просмотр сведений во вкладке сообщения
SET STATISTICS IO ON;
SELECT * FROM test.dbo.ind WHERE ID < 50
SET STATISTICS IO OFF;

--Фрагментация
--Компонент Компонент SQL Server Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, 
--обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, 
--что данные в индексе окажутся разбросанными по базе данных (фрагментированными)
--Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, 
--основанный на значении ключа, не совпадает с физическим порядком в файле данных
--Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения
--Устранить фрагментацию возможно путем реорганизации или перестроения индекса

--Возвращает сведения о размере и фрагментации данных и индексов указанной таблицы или представления в SQL Server
--Уровень фрагментации индекса или кучи показан в столбце avg_fragmentation_in_percent. 
--Для куч это значение соответствует фрагментации экстентов. 
--Для индексов это значение соответствует логической фрагментации
--Для наибольшей производительности значение аргумента avg_fragmentation_in_percent должно быть как можно ближе к нулю
SELECT index_type_desc,avg_fragment_size_in_pages,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
    (DB_ID(N'test'), OBJECT_ID(N'dbo.ind'), NULL, NULL , 'DETAILED');

--Вариант просмотра с именами таблиц и именами индексов для текущей БД
--Условия для выборки:
--page_count > 8 - перестраивать индексы,имеющие небольшое количество страниц нет смысла
--avg_fragmentation_in_percent > 10 – нет смысла для индекса с показателем фрагментации 10 или менее процентов
--dm.index_id > 0 – 0, это - куча(неиндексированные данные)
SELECT 
    tbl.name AS table_name, 
    idx.name AS index_name, 
    avg_fragment_size_in_pages,
    dm.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
    INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id
    INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id
WHERE page_count > 8
    AND avg_fragmentation_in_percent > 10
    AND dm.index_id > 0

--Реорганизация и перестроение индексов производится с помощью ALTER INDEX REORGANIZE и ALTER INDEX REBUILD

--Реорганизация дефрагментированного индекса
ALTER INDEX uniq_index ON test.dbo.ind
REORGANIZE ; 

--Реорганизация всех индексов в таблице
ALTER INDEX ALL ON test.dbo.ind
REORGANIZE ; 

--Перестроение дефрагментированного индекса
ALTER INDEX uniq_index ON test.dbo.ind
REBUILD;

--Перестроение всех индексов в таблице
ALTER INDEX ALL ON test.dbo.ind
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

--Удаление индексов
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'cluster_index') 
    DROP INDEX cluster_index ON test.dbo.ind; 

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'noncluster_index') 
    DROP INDEX noncluster_index ON test.dbo.ind; 

IF EXISTS (SELECT name from sys.indexes
           WHERE name = N'uniq_index') 
   DROP INDEX uniq_index ON test.dbo.ind; 
GO

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