Об индексах и о том какие индексы нужны для запросов

добавлено: 08 авг 17
понравилось:0
просмотров: 1567
комментов: 1

теги:

Автор: Алексей Куренков

Для чего нужны индексы? У индексов таблиц в РСУБД два назначения – используются для эффективного поиска и для сортировки.

Сегодня хочу рассказать о том, каким образом устроены индексы в реляционных базах данных, в частности в Microsoft SQL Server. Сразу оговорюсь, что этот материал для начального уровня, здесь я не буду рассматривать структуру B-Tree и многие глубокие вещи. В этом обзоре предлагаю на начальном уровне разобраться с базовыми понятиями индексов. В обзоре будут примеры алгоритмов, поэтому у тебя, для понимания, ты должен знать, что такое сортировка, разница в поиске в сортированном и несортированном массивах. В этом обзоре я буду проводить ассоциации со структурами и массивами – как аналог таблиц в реляционных СУБД.

Итак – поехали.

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

create table employees
(
ID int not null identity, -- суррогатный первичный ключ
TabNo varchar(20) not null -- табельный номер может содержать только цифры и тире
check (TabNo not like '%[^0-9,^-]%'),
EmploymentDate date not null, -- дата трудоустройства
FirstName nvarchar(50) not null, -- Имя
LastName nvarchar(50) not null, -- Фамилия
MiddleName nvarchar(50) null, -- отчество
FullName as LastName+' '+FirstName+isnull(' '+MiddleName,''), -- ФИО
Gender char(1) not null -- пол
check (Gender in ('F','M')),
BirthDay date not null -- дата рождения
)
insert employees values
('000-001','2015-01-20','Андрей','Иванов','Сидорович','M','1980-05-05'),
('000-002','2015-01-20','Григорий','Петров','Сидорович','M','1981-06-05'),
('000-003','2015-02-12','Ольга','Иванова','Алексеевна','F','1985-01-25'),
('000-004','2015-03-02','Андрей','Иванов','Сидорович','M','1970-05-05'),
('000-005','2015-03-05','Сергей','Сидоров','Сидорович','M','1975-05-05'),
('000-006','2015-03-07','Сидр','Козлов','Сидорович','M','1983-05-05'),
('000-007','2015-03-21','Андрей','Баранов','Сидорович','M','1988-05-07'),
('000-008','2015-03-21','Василий','Медведев','Сидорович','M','1965-05-05'),
('000-009','2015-03-21','Петр','Баранов','Сидорович','M','1978-12-12'),
('000-010','2015-03-21','Абдула','Мамедов',null,'M','1969-11-11')


В процедурном языке программирования в паскаль или си – аналог таблицы — это массив элементом которого является структура с теми же полями что и наша таблица.
struct pk_employee
{
int ID;
char TabNo[20]; char EmploymentDate[10];
char FirstName[50];
char LastName[50];
char MiddleName[50];
char Gender;
char BirthDay[20];
};
pk_employee employee[Много_строк];

Этот массив не отсортирован, поэтому любая операция извлечения данных (select … from employees) с условием или без такового будет сопровождаться перебором всех элементов нашего массива, даже если нам нужно будет элемент по ID у нас нет индекса и SQL Server пока ничего не знает сколько раз этот элемент будет встречаться, поэтому всегда будет полный перебор. Предлагаю выполнить запрос с условием по ID и без условия и сравнить планы запросов
select * from employees
select * from employees where ID = 4

Картинка с другого сайта.
Видна абсолютная идентичность планов как по стоимости (50% верхний от общего плана и половина так же 2й запрос нижний с условием). В плане запроса мы видим, что 100% от всего запроса занимает Table Scan – сканирование таблицы – это и есть полный перебор элементов массива.
А что бы было если бы наш массив был бы отсортированным по уникальному полю ID? Ты же прекрасно понимаешь, что можно было бы не полным перебором найти нужный элемент, например, половинным делением или другим алгоритмом?
Ну и для понимания, аналог таблице объявим на
Вообще, когда таблица не отсортирована, как в нашем случае – данные хранятся в «куче (heap)». Но можно упорядочить данные по какому выбранному критерию, по любому набору полей в нашей таблице, например по – LastName,FirstName,MiddleName или по ID и дать команду SQL Server что бы он поддерживал нужную нам последовательность – порядок данных в таблице. Эта команда – создать кластерный индекс. Кластерный индекс – это альтернатива куче. В куче данные лежат хаотично – в таком порядке в каком их положили в БД (со временем, после дефрагментации, сжатия файлов БД – порядок данных в куче изменится), а более точно – имея кучу – SQL Server не следит за упорядочиванием данных в таблице. Но если мы создадим кластерный индекс на таблице – сервер будет всегда поддерживать данные в том порядке в каком мы указали в кластерном индексе. Ну и стоит упомянуть очевидное – в таблице может быть только один кластерный индекс, или его совсем может не быть, в таблице вместо кластерного индекса – куча.

Материал взят с моего сайта www.kurenkov.pro, и если Вам интересна эта статья, предлагаю ознакомиться с ней на моем сайте.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии