Индексы SQL

SQL
Комментарии

Приветствую всех, сегодня разберем тему касаемо индексов на примерах.

Индексы используются для логической сортировки данных с целью повышения скорости поиска и выполнения в последующем операций сортировки.

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

Индексы баз данных работают весьма похожим образом. Данные первичного ключа всегда отсортированы — СУБД делает это для вас. Выборка указанных строк производиться по первичному ключу, таким образом, всегда гарантирует быстроту и эффективность этой
операции.

Поиск значений в других столбцах, однако, выполняется уже не столь эффективно. Вы можете определить в качестве индекса один или несколько столбцов так, чтобы СУБД хранила отсортированный список содержимого удобным для вас образом.

Однако прежде чем создавать множество индексов, примите во внимание следующее.

  • Индексы повышают производительность операций выборки, но ухудшают производительность при выполнении таких операций, как добавление данных, их модификация и удаление. Вызвано это тем, что при выполнении подобных операций СУБД должна еще и динамически обновлять индекс.
  • Для хранения данных индекса требуется много места на жестком диске.
  • Не все данные подходят для индексации. Данные, которые не являются по своей сути уникальными (как, например, названия штатов в столбце cust_state), не дадут такого выигрыша от индексации, как данные, которые имеют больше возможных значений (как, например, имя и фамилия).
  • Индексы используются для фильтрации и сортировки данных. Если вы часто сортируете данные определенным образом, эти данные могут быть кандидатом на индексацию.
  • В качестве индекса можно определить несколько столбцов (например, с названием штата и названием города). Такой индекс можно использовать, только если данные будут отсортированы в порядке «штат плюс город». (Если вы захотите отсортировать данные по названию города, такой индекс использован не будет).

Индекс – это список (такой как библиографическая информация или перечень цитат, взятых
из литературы), который обычно располагают в алфавитном порядке перечисления
определенных данных.
Индексы в таблице позволяют получить данные из определенной записи без перебора всех
записей в таблице.
Типы индексов:
1) Кластеризованный индекс
2) Некластеризованный индекс:

  • заданный на неупорядоченной таблице
  • заданный на кластеризированной таблице


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


Кластеризированной называется таблица, данные в которой отсортированы по определенному столбцу или группе столбцов на которых задан кластеризованный индекс.


Некластеризованный индекс содержит некластеризованное ключевое значение и указатель на строку.

Индексы хранятся в виде B-деревьев (B – tree). “B” означает балансированное.

Представления это способ вывода ограниченного набора столбцов из реальной таблицы в виде виртуальной таблицы.


CREATE TABLE BTree
(
numbers int primary key, -- Первичный ключ, добавляет кластеризованый индекс
word varchar(25)
)
--SET STATISTICS IO ON
INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree WHERE word = 'one';

SELECT * FROM sys.indexes
WHERE object_id = (SELECT object_id FROM sys.tables
				   WHERE name = 'BTree')

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int ,
word varchar(25)primary key -- кластеризированный индекс
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree;

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int unique, -- некластеризированный индекс на не упорядоченой таблице (данные хранятся не на листевом уровне дерева)
word varchar(25)
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree where word = 'one';

--------------------------------------------------

DROP TABLE BTree;
GO

CREATE TABLE BTree
(
numbers int primary key, -- кластеризированый индекс
word varchar(25)unique   -- некластеризированный индекс на клатеризованной таблице
)

INSERT BTree
VALUES
(1,'one'),
(2,'two'),
(3,'three'),
(4,'four'),
(6,'six'),
(7,'seven'),
(8,'eight'),
(9,'nine');

SELECT * FROM BTree;

INSERT BTree
VALUES (5,'five');

SELECT * FROM BTree where word in ('five', 'one'); -- поиск по некластеризованному индексу
SELECT * FROM BTree where numbers in (3,5);   -- поиск по кластеризованному индексу
--------------------------------------------------
DROP TABLE OrderDetails;
GO
DROP TABLE Orders;
GO
DROP TABLE Customers
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());
GO

SELECT * FROM Customers;

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL PRIMARY KEY,    -- кластеризованный индекс
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем кластеризованный индекс

-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) UNIQUE, -- некластеризованный индекс
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());
GO

SELECT * FROM Customers;

SELECT Phone FROM Customers			  
	WHERE Phone = '(093)2221212'; -- Для поиска используем некластеризованный
	
-----------------------------------------------------------------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int IDENTITY NOT NULL
		PRIMARY KEY,    -- кластеризованный индекс
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) UNIQUE, -- некластеризованный индекс
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

INSERT INTO Customers
( CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
('Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
('Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE());

SELECT * FROM Customers; -- для поиска используем только кластеризованный индекс

SELECT * FROM Customers			  -- Для поиска используем кластеризованный
	WHERE Phone = '(093)2221212'; -- и не кластеризированный индексы !!!
	
------------------------- Кластеризованный индекс -------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса
ON Customers(CustumerNo);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 как и в первой строке
GO

SELECT * FROM Customers;-- для поиска используем кластеризованный индекс

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

DROP TABLE Customers;
GO

CREATE TABLE Customers               
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12) Primary key,
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса 
ON Customers(CustumerNo);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(1,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- CustumerNo = 1 будет ошибка
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers; --для поиска используем кластеризованный индекс

------------------ Создание некластеризованного индекса на неупорядоченой таблице -------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного  индекса на неупорядоченной таблице
ON Customers(Phone);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1111111', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2222222', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers;

SELECT * FROM Customers			  
	WHERE Phone = '(093)2222222'; 
	

----------------Создание некластеризованного индекса на кластеризованной таблице--------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE CLUSTERED INDEX PK_IND1 -- создание собственного кластеризованного индекса
ON Customers(CustumerNo);
GO

CREATE UNIQUE NONCLUSTERED INDEX NC_IND1 -- создание собственного некластеризованного  индекса на кластеризованной таблице
ON Customers(Phone);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'Alex2', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); --CustumerNo должен быть уникальным для каждой строки если CREATE UNIQUE CLUSTERED INDEX
GO

SELECT * FROM Customers; -- Для поиска используем кластеризованный индекс

SELECT * FROM Customers			  -- Для поиска используем кластеризованный
	WHERE Phone = '(093)2221212'; --  и не кластеризированный индексы !!!
	


-----------------------------------------------------------
        /* Изменения индекса ALTER INDEX */
-----------------------------------------------------------

DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE INDEX PK_IND1 --если включена опция IGNORE_DUP_KEY и нету ограничения UNIQUE будет предупреждение (возможна вставка одинаковых индексов Alex и alex)
ON Customers(CustumerName)
WITH 
(
IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными
);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки
GO

SELECT * FROM Customers


ALTER INDEX PK_IND1 -- изменение индекса PK_IND1 
ON Customers		-- заданого на таблице Customers
REBUILD WITH
	(
		IGNORE_DUP_KEY = OFF
	);

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- Не будет ошибки
GO

SELECT * FROM Customers;

----------------------------УДАЛЕНИЕ ИНДЕКСОВ---------------------------------------------------------------------
DROP TABLE Customers;
GO

CREATE TABLE Customers                
(                                      
	CustumerNo int NOT NULL, 
	CustumerName varchar(25) NOT NULL,
	Address1 varchar(25) NOT NULL,
	Address2 varchar(25) NOT NULL,
	City      varchar(15) NOT NULL,
	State char(2) NOT NULL,
	Zip varchar(10) NOT NULL,
	Contact varchar(25) NOT NULL,
	Phone char(12),
	FedIDNo  varchar(10) NOT NULL,
	DateInSystem smalldatetime NOT NULL 
)
GO

CREATE UNIQUE INDEX PK_IND1 -- если включена опция IGNORE_DUP_KEY и нет ограничения UNIQUE будет предупреждения (возможна вставка одинаковых индексов Alex и alex)
ON Customers(CustumerName)
WITH 
(
IGNORE_DUP_KEY = ON -- включаем игнорирование различия между строчными и заглавными символами
);
GO

INSERT INTO Customers
( CustumerNo, CustumerName, Address1, Address2, City, State, Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
(1,'Alex', 'NewSTR', 'NewSTR2', 'City', 'NS', 'NewZip', 'dfgjs@mail.ru', '(093)1231212', 'qweq', GETDATE()),
(2,'alex', 'NewSTR2', 'NewSTR22', 'City2', 'SN', 'NewZip2', 'dfg2@mail.ru', '(093)2221212', 'qwq2', GETDATE()); -- если включена опция IGNORE_DUP_KEY и есть ограничения UNIQUE данная строка не запишется
GO

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска используем не кластеризованный индекс

DROP INDEX Customers.PK_IND1;

SELECT * FROM Customers WHERE CustumerName = 'alex'; -- для поиска не используем индекс
Обновлено: 16.01.2019 — 18:18

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.