Создание кластеризованных и некластеризованных индексов в SQL Server

Содержание:

• Кластеризованный индекс:
• Создание базы данных
• Настроенный кластеризованный индекс
• Некластеризованный индекс:
• Заключение

В SQL Server существует два типа индексов: кластерные и некластерные индексы. Как кластеризованные, так и некластеризованные индексы имеют одинаковую физическую структуру. Более того, оба индекса хранятся в SQL Server в виде структуры B-дерева.

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

Кластеризованный список — это особый тип индекса, который изменяет физическое расположение записей в таблице. В SQL Server индексы используются для ускорения операций с базой данных, что приводит к высокой производительности. Поэтому таблица может иметь только один кластеризованный индекс, который обычно создается на первичном ключе. Кластеризованный индекс ’ листовые узлы содержат «страницы данных». Таблица может иметь только один кластеризованный индекс.

Давайте создадим кластерный индекс для лучшего понимания. Прежде всего, нам необходимо создать базу данных.

Создание базы данных

Для того чтобы создать базу данных. Щелкните правой кнопкой мыши на «Базы данных» в проводнике объектов и выберите «Новая база данных» опция. Введите имя базы данных и нажмите OK. База данных была создана, как показано на рисунке ниже.

Теперь мы создадим таблицу с именем «Сотрудник» с первичным ключом с помощью представления дизайна. На рисунке ниже видно, что мы присвоили основное значение файлу с именем «ID» и не создали никакого индекса на таблице.

Вы также можете создать таблицу, выполнив следующий код.

USE [тест]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[сотрудник](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Dep_ID] [int] NULL,
[Name] [varchar](200) NULL,
[email] [varchar](250) NULL,
[город] [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, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Вывод будет выглядеть следующим образом.

Приведенный выше код создал таблицу с именем «Employee» с полем ID, уникальным идентификатором в качестве первичного ключа. Теперь в этой таблице будет автоматически создан кластеризованный индекс по столбцу ID из-за ограничений первичного ключа. Если вы хотите просмотреть все индексы таблицы, выполните хранимую процедуру “sp_helpindex”. Выполните следующий код, чтобы просмотреть все индексы таблицы с именем «Employee». Эта хранимая процедура принимает имя таблицы в качестве входного параметра.

ИСПОЛЬЗОВАТЬ тест
EXECUTE sp_helpindex Employee

Вывод будет выглядеть следующим образом.

Другой способ просмотреть индексы таблиц — перейти по адресу «таблицы» в проводнике объектов. Выберите таблицу и расходуйте ее. В папке indexes вы можете увидеть все индексы, относящиеся к данной конкретной таблице, как показано на рисунке ниже.

Поскольку это кластеризованный индекс, логический и физический порядок индекса будет одинаковым. Это означает, что если запись имеет Id равный 3, то она будет храниться в третьей строке таблицы. Аналогично, если пятая запись имеет идентификатор 6, она будет сохранена в файле 5^th расположение таблицы. Для того чтобы понять порядок записей, необходимо выполнить следующий скрипт.

USE [test]
GO
SET IDENTITY_INSERT [dbo].[Сотрудник] ON
INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (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]) VALUES (9, 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]) VALUES (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]) VALUES (11, 1, N'Aaaronboy Gutierrez', N'aronboy.gutierrez@gmail.com', N'HILLSBORO', N'5840 Ne Cornell Rd Hillsboro Or 97124')
INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (12, 2, N'Aabdi Maghsoudi', N'abdi_maghsoudi@gmail.com', N'BRENTWOOD', N'987400 Nebraska Medical Center Omaha Ne 681987400')
INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (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], [city], [address]) VALUES (14, 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], [city], [address]) VALUES (1, 1, N'Aaaronboy Gutierrez', N'aronboy.gutierrez@gmail.com', N'HILLSBORO', N'5840 Ne Cornell Rd Hillsboro Or 97124')
INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (2, 2, N'Aabdi Maghsoudi', N'abdi_maghsoudi@gmail.com', N'BRENTWOOD', N'987400 Nebraska Medical Center Omaha Ne 681987400')
INSERT [dbo].[Сотрудник] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (3, 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], [city], [address]) VALUES (4, 3, N'Aabharana, Sahni', N'abharana.sahni@gmail.com', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191')
INSERT [dbo] INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (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], [Name], [email], [city], [address]) VALUES (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]) VALUES (7, 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]) VALUES (15, 4, N'Aabish Mughal', N'abish_mughal@gmail.com', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000')
INSERT [dbo].[Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) 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]) VALUES (17, 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]) VALUES (18, 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]) VALUES (19, 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]) VALUES (20, 7, N'Pilar Ackaerman', N'pilar.ackaerman@gmail.com', N'ATLANTA', N'5813 Eastern Ave Hyattsville Md 207822201')
SET IDENTITY_INSERT [dbo].[Employee] OFF

Хотя записи хранятся в столбце «Id” в случайном порядке значений. Но из-за кластеризованного индекса на столбце id. Записи физически хранятся в порядке возрастания значений в столбце id. Чтобы проверить это, нам нужно выполнить следующий код.

Select * from test.dbo.Сотрудник

Выходные данные будут выглядеть следующим образом.

На рисунке выше видно, что записи были получены в порядке возрастания значений в столбце id.

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

Вы также можете создать пользовательский кластеризованный индекс. Так как мы можем создать только один кластеризованный индекс, нам нужно удалить предыдущий индекс. Для того чтобы удалить индекс, выполните следующий код.

USE [test]
GO
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [Primary_Key_ID] WITH ( ONLINE = OFF )
GO

Вывод будет выглядеть следующим образом.

Теперь для создания индекса выполните следующий код в окне запроса. Этот индекс был создан по нескольким столбцам, поэтому он называется составным индексом.

USE [test]
GO
CREATE 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 [PRIMARY]
GO

Выходные данные будут выглядеть следующим образом

Мы создали пользовательский кластеризованный индекс на ID и Dep_ID. Это позволит отсортировать строки по Id, а затем по Dep_Id. Для просмотра этого выполните следующий код. Результат будет в порядке возрастания ID, а затем Dep_id.

SELECT [ID] ,[Dep_ID],[Name],[email] ,[city] ,[address] FROM [test].[dbo].[Employee]

Результат будет следующим.

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

Некластеризованный индекс — это особый тип индекса, в котором логический порядок индекса не соответствует физическому порядку строк, хранящихся на диске. Листовой узел некластеризованного индекса не содержит страниц данных, а содержит информацию о строках индекса. Таблица может иметь до 249 индексов. По умолчанию ограничение на уникальный ключ создает некластеризованный индекс. При операции чтения некластеризованные индексы работают медленнее, чем кластеризованные. Некластеризованный индекс имеет копию данных из индексируемых столбцов, сохраненных в порядке, а также ссылки на фактические строки данных; указатели на кластеризованный список, если таковой имеется. Поэтому хорошей идеей будет выбрать только те столбцы, которые используются в индексе, вместо того, чтобы использовать *. Таким образом, данные могут быть получены непосредственно из дублирующего индекса. Некластеризованный индекс также используется для выбора оставшихся столбцов, если он был создан.

Синтаксис, используемый для создания некластеризованного индекса, аналогичен кластеризованному индексу. Однако ключевое слово “NONCLUSTERED” используется вместо “CLUSTERED” в случае некластеризованного индекса. Выполните следующий сценарий для создания некластеризованного индекса.

USE [test]
GO
SET ANSI_PADDING ON
GO
CREATE 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» таблицы Employee. Этот индекс отсортирует таблицу в порядке возрастания столбца «Имя». Данные таблицы и индекс будут храниться в разных местах, как мы говорили ранее. Теперь выполните следующий сценарий, чтобы увидеть влияние нового некластеризованного индекса.

select Name from Employee

Вывод будет выглядеть следующим образом.

На рисунке выше видно, что столбец Name таблицы Employee был показан в порядке возрастания столбца name, хотя мы не упомянули предложение «Order by ASC» в предложении select. Это происходит из-за некластеризованного индекса на столбце «Имя», созданного в таблице Employee. Теперь, если написать запрос, чтобы получить имя, электронную почту, город и адрес конкретного человека. База данных будет сначала искать это конкретное имя внутри индекса, а затем извлекать соответствующие данные, что уменьшит время выполнения запроса, особенно если данные огромны.

select Name, email, city, address from Employee where name='Aaaronboy Gutierrez'

Заключение

Из вышеприведенного обсуждения мы узнали, что кластеризованный индекс может быть только один, в то время как некластеризованных индексов может быть много. Кластеризованный индекс быстрее по сравнению с некластеризованным индексом. Кластеризованный индекс не занимает дополнительного места в памяти, в то время как некластеризованный индекс требует дополнительной памяти для их хранения. Если мы применяем ограничение первичного ключа к таблице, автоматически создается кластерный индекс. Более того, если мы применяем ограничение уникального ключа для любого столбца, то для него автоматически создается некластеризованный индекс. Некластеризованный индекс быстрее кластеризованного для операций вставки и обновления. Таблица может не иметь ни одного некластеризованного индекса.