В SQL Server существует два типа индексов; Кластерные и некластеризованные индексы. И кластерные, и некластеризованные индексы имеют одинаковую физическую структуру. Более того, оба они хранятся в SQL Server как структура B-Tree.
Кластерный индекс:
Кластеризованный список – это особый тип индекса, который перестраивает физическое хранение записей в таблице. В SQL Server индексы используются для ускорения операций с базой данных, что обеспечивает высокую производительность. Таким образом, таблица может иметь только один кластеризованный индекс, что обычно выполняется по первичному ключу. Концевые узлы кластерного индекса содержат «страницы данных». Таблица может иметь только один кластерный индекс.
Давайте создадим кластерный индекс, чтобы иметь лучший понимание. Прежде всего, нам нужно создать базу данных.
Создание базы данных
Для создания базы данных. Щелкните правой кнопкой мыши «Базы данных» в проводнике объектов и выберите вариант «Новая база данных» . Введите имя базы данных и нажмите ОК. База данных была создана, как показано на рисунке ниже.
Теперь мы создадим таблицу с именем «Сотрудник» с первичным ключом с помощью представления дизайна. Как видно на рисунке ниже, мы назначили в первую очередь поле с именем «ID», и мы не создали никакого индекса для таблицы.
Вы также можете создать таблицу, выполнив следующий код.
USE [test] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo]. [Employee] ([ID ] [int] IDENTITY (1,1) NOT NULL, [Dep_ID] [int] NULL, [Имя] [varchar] (200) NULL, [электронная почта] [varchar] (250) NULL, [city] [varchar] ( 250) NULL, [адрес] [varchar] (500) NULL, CONSTRAINT [Primary_Key_ID] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON_LOCKS = ON_LOCKS) ON [PRIMARY]) ON [PRIMARY] GO
Результат будет следующим.
Приведенный выше код создал таблицу с именем «Сотрудник» с полем идентификатора, уникальным идентификатором в качестве первичного ключа. Теперь в этой таблице кластеризованный индекс будет автоматически создан по идентификатору столбца из-за ограничений первичного ключа. Если вы хотите увидеть все индексы в таблице, запустите хранимую процедуру «sp_helpindex». Выполните следующий код, чтобы увидеть все индексы в таблице с именем «Employee». Эта процедура хранения принимает имя таблицы в качестве входного параметра.
USE testEXECUTE sp_helpindex Employee
Результат будет следующим.
Другой способ просмотреть индексы таблиц – перейти к «таблицам» в проводнике объектов . Выберите стол и расходуйте его. В папке indexes вы можете увидеть все индексы, относящиеся к этой конкретной таблице, как показано на рисунке ниже.
Поскольку это кластерный индекс, логический и физический порядок индекса будет таким же. Это означает, что если запись имеет идентификатор 3, то она будет сохранена в третьей строке таблицы. Точно так же, если пятая запись имеет идентификатор 6, она будет сохранена в 5 th месте таблицы. Чтобы понять порядок записей, вам необходимо выполнить следующий скрипт.
USE [test] GOSET IDENTITY_INSERT [dbo]. [Employee] ONINSERT [dbo]. [Employee] ([ID ], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (8, 6, N'Humbaerto Acevedo ', N'humbaerto.acevedo@gmail.com', N'SAINT PAUL ' , N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) ЗНАЧЕНИЯ (9, 6, N'Humbaerto Acevedo ', N'humbaerto.acevedo@gmail.com', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (10, 7, N'Pilar Ackaerman ', N'pilar.ackaerman@gmail.com', N'ATLANTA ', N' 5813 Eastern Ave Hyattsville Md 207822201 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address] ») ЗНАЧЕНИЯ (11, 1, N'Aaaronboy Gutierrez ', N'aronboy.gutierrez@gmail.com', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro или 97124') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя] , [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (12, 2, N'Aabdi Maghsoudi ', N'abdi_maghsoudi@gmail.com', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') ВСТАВЬТЕ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (13, 3, N'Aabharana, Sahni ', N'abharana. sahni@gmail.com ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [ город], [адрес]) ЦЕННОСТИ (14, 3, N'Aabharana, Sahni ', N'abharana.sahni@gmail.com', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') ВСТАВИТЬ [ dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (1, 1, N'Aaaronboy Gutierrez ', N'aronboy.gutierrez@gmail .com ', N'HILLSBORO', N'5840 Ne Cornell Rd Hillsboro или 97124 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [ адрес]) VALUES (2, 2, N'Aabdi Maghsoudi ', N'abdi_maghsoudi@gmail.com', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') ВСТАВИТЬ [dbo]. [Сотрудник ] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (3, 3, N'Aabharana, Sahni ', N'abharana.sahni@gmail.com', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (4, 3, N'Aabharana, Sahni ', N'abharana.sahni@gmail.com ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес ]) ЗНАЧЕНИЯ (5, 4, N'Aabish Mughal ', N'abish_mughal@gmail.com', N'OMAHA ', N'2975 Crouse Lane Burlington Nc 272150000') INSERT [dbo]. [Employee] ([ID] , [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (6, 5, N'Aabram Howell ', N'aronboy.gutierrez@gmail.com', N'DILLSBURG ', N '868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address] ») ЗНАЧЕНИЯ (7, 5, N ' Aabram Howell ', N'aronboy.gutierrez@gmail.com', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя] , [адрес электронной почты], [город], [адрес]) ЗНАЧЕНИЯ (15, 4, N'Aabish Mughal ', N'abish_mughal@gmail.com', N'OMAHA ', N'2975 Crouse Lane Burlington NC 272150000') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Nam e], [электронная почта], [город], [адрес]) VALUES (16, 5, N'Aabram Howell ', N'aronboy.gutierrez@gmail.com', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address] ») ЗНАЧЕНИЯ (17, 5, N'Aabram Howell', N ' aronboy.gutierrez@gmail.com ', N'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') ВСТАВИТЬ [dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [ город], [адрес]) VALUES (18, 6, N'Humbaerto Acevedo ', N'humbaerto.acevedo@gmail.com', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') ВСТАВИТЬ [ dbo]. [Сотрудник] ([ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес]) ЗНАЧЕНИЯ (19, 6, N'Humbaerto Acevedo ', N'humbaerto.acevedo@gmail .com ', N'SAINT PAUL', N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Сотрудник] ([ID], [Dep_ID], [Name], [email], [city] , [адрес]) VALUES (20, 7, N'Pilar Ackaerman ', N'pilar.ackaerman@gmail.com', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [dbo]. [ Сотрудник] ВЫКЛ
Althoug h записей хранятся в столбце «Id» в случайном порядке значений. Но из-за кластерного индекса в столбце id. Записи физически хранятся в порядке возрастания значений в столбце id. Чтобы проверить это, нам нужно выполнить следующий код.
Выбрать * из test.dbo.Employee
Результат будет следующим.
Как видно на приведенном выше рисунке, записи были получены в порядке возрастания значений в столбце id.
Настраиваемый кластерный индекс
Вы также можете создать настраиваемый кластерный индекс. Поскольку мы можем создать только один кластерный индекс, нам нужно удалить предыдущий. Чтобы удалить индекс, выполните следующий код.
USE [test] GOALTER TABLE [dbo]. [Employee] DROP CONSTRAINT [Primary_Key_ID] WITH (ONLINE = OFF) GO
Результат будет следующим.
Теперь, чтобы создать индекс, выполните следующий код в окне запроса. Этот индекс был создан для нескольких столбцов, поэтому он называется составным индексом.
USE [test] GOCREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] ON [dbo]. [Employee] ( [ID] ASC, [Dep_ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON GO # p> Результат будет следующим
Мы создали настраиваемый кластерный индекс по идентификатору и Dep_ID. Это отсортирует строки по Id, а затем по Dep_Id. Чтобы просмотреть это, выполните следующий код. Результатом будет идентификатор в возрастающем порядке, а затем - Идентификатор_положения.
SELECT [ID], [Dep_ID], [Имя], [электронная почта], [город], [адрес] ОТ [тест] . [dbo]. [Employee]Результат будет следующим.
Некластеризованный индекс:
Некластеризованный индекс - это особый тип индекса, в котором логический порядок индекса не соответствует физическому порядку строк, хранящихся на диске. Конечный узел некластеризованного индекса не содержит страниц данных, а содержит информацию о строках индекса. Таблица может содержать до 249 индексов. По умолчанию ограничение уникального ключа создает некластеризованный индекс. В операции чтения некластеризованные индексы работают медленнее, чем кластеризованные индексы. Некластеризованный индекс содержит копию данных из индексированных столбцов, хранящуюся в порядке вместе со ссылками на фактические строки данных; указатели на кластерный список, если таковые имеются. Поэтому рекомендуется выбирать только те столбцы, которые используются в индексе, вместо использования *. Таким образом, данные могут быть получены непосредственно из дублирующего индекса. В противном случае кластерный индекс также используется для выбора оставшихся столбцов, если он создается.
Синтаксис, используемый для создания некластеризованного индекса, аналогичен кластеризованному индексу. Однако ключевое слово «НЕКЛАСТЕРНЫЙ» используется вместо «CLUSTERED» в случае некластеризованного индекса. Выполните следующий сценарий, чтобы создать некластеризованный индекс.
USE [test] GOSET ANSI_PADDING ONGOCREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo]. [Employee] ([Name] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GOБудет следующим образом.
Записи таблицы сортируются по кластеризованному индексу, если он был создан. Этот новый некластеризованный индекс будет сортировать таблицу в соответствии с ее определением и будет храниться по отдельному физическому адресу. Приведенный выше сценарий будет создайте индекс в столбце «ИМЯ» таблицы сотрудников. Этот индекс будет отсортировать таблицу в порядке возрастания столбца «Имя». Данные таблицы и индекс будут храниться в разных местах, как мы говорили ранее. Теперь выполните следующее скрипт, чтобы просмотреть влияние нового некластеризованного индекса.
выберите Name from EmployeeРезультат будет следующим.
На рисунке выше видно, что столбец Name таблицы Employee показан в порядке возрастания столбца name, хотя мы не упомянули предложение «Порядок по ASC» с предложением select. Это связано с некластеризованным индексом в столбце «Имя», созданным в таблице сотрудников. Теперь, если запрос записывается в получить имя, адрес электронной почты, город и адрес конкретного человека. База данных сначала будет искать это конкретное имя внутри индекса, а затем извлекать соответствующие данные, что уменьшит время выборки запроса, особенно когда данные огромны.
выберите имя, адрес электронной почты, город, адрес от сотрудника where name = 'Aaaronboy Gutierrez'Заключение
Из приведенного выше обсуждения мы узнали, что кластеризованный индекс может быть только одним, тогда как некластеризованный индекс может быть многие. Кластеризованный индекс быстрее по сравнению с некластеризованным индексом. Кластеризованный индекс не требует дополнительного места для хранения, тогда как некластеризованный индекс требует дополнительной памяти для их хранения. Если мы применяем ограничение первичного ключа к таблице, на ней автоматически создается кластерный индекс. Более того, если мы применяем ограничение уникального ключа к любому столбцу, для него автоматически создается некластеризованный индекс. Некластеризованный индекс работает быстрее, чем кластеризованный, для операций вставки и обновления. Таблица может не иметь некластеризованного индекса.