Индексы и SQL-запросы — продолжение (§5, §6)

1.

Индексы
и SQL-запросы
Ускорение поиска и декларативный доступ к данным на примере базы
данных колледжа.

2.

Что такое индекс?
Физическая структура данных в БД для ускоренного доступа к информации
«Индекс — это физическая структура данных в БД, при помощи которой осуществляется ускоренный доступ к
“ необходимой информации. Использование подходящего индекса значительно улучшает производительность
запроса.»
▤ Б Е З И Н Д Е К СА — П О СЛ Е Д О В АТ Е Л Ь Н ЫЙ
П Е РЕ Б О Р
❏ С И Н Д Е К СО М — П РЯМО Й Д О СТ УП
Чтобы найти главу в книге без оглавления, придется
перебрать все страницы по очереди. В БД это table scan
— СУБД просматривает каждую запись таблицы.
Медленно для больших таблиц.
Оглавление книги даёт номер нужной страницы — вы
сразу переходите к ней. Индекс в БД работает так же:
СУБД находит запись в индексе, затем прямо обращается
к данным.
-- Поиск по Students.LastName без индекса
-- После CREATE INDEX IX_Students_LastName
ON Students (LastName);
N'Козлов'
SELECT * FROM Students WHERE LastName = N'Козлов';
-- СУБД сканирует всю таблицу
SELECT * FROM Students WHERE LastName = N'Козлов';
-- СУБД использует индекс — мгновенный поиск
Индексы в CollegeDB: Students.LastName — частый поиск по фамилии; Grades.StudentID — соединение таблиц;
Schedule.LessonDate — расписание по дате.

3.

Цели и задачи индекса
Почему нельзя проиндексировать все поля таблицы
+ ПЛЮСЫ
● Ускорение SELECT-запросов в десятки раз
● Быстрый поиск по часто фильтруемым полям
● Эффективные JOIN-соединения таблиц
− МИНУСЫ
● Рост размера БД — индекс = копия данных поля
● Замедление INSERT, UPDATE, DELETE — при
изменении данных перестраивается индекс
● Фрагментация индекса при частых модификациях
4 ПРАВИЛА ИНДЕКСАЦИИ
1 АВТОМАТИЧЕСКИЕ ИНДЕКСЫ
Индексы автоматически создаются для уникальных полей
таблицы (UNIQUE) и полей, указанных в качестве
первичных ключей (PRIMARY KEY).
2 ПОЛЯ ЧАСТОГО ПОИСКА
Индексы следует создавать для полей таблицы, по
которым часто производится поиск. Можно
создавать один индекс для набора столбцов
(составной индекс).
— В CollegeDB StudentID, GroupID, TeacherID уже
проиндексированы автоматически
— Students.LastName, Grades.StudentID, Schedule.LessonDate
3 МИНИМУМ ПОВТОРОВ
4 ИНДЕКСЫ ДЛЯ ПРЕДСТАВЛЕНИЙ
Для создания индекса наиболее подходят те поля
таблицы, у которых количество повторяющихся
значений минимально. Поле с высокой селективностью.
Индексы также можно создавать и для
представлений (VIEWS).
— Email лучше, чем Course (1–4) или Semester (1–8)

4.

Внутреннее устройство индекса
Страницы, экстенты и B-Tree — как SQL Server физически хранит индексы
8 КБ РАЗМЕР СТРАНИЦЫ
8 шт СТРАНИЦ В ЭКСТЕНТЕ
2 типа СТРАНИЦ
Минимальная единица распределения
памяти в БД
Экстент = 64 КБ — эффективная единица
управления
Страницы данных и страницы индексов
B-TREE · СБАЛАНСИРОВАННОЕ ДЕРЕВО
Для хранения индексов используется структура данных в виде
сбалансированного дерева B-Tree (Balanced Tree). Дерево
автоматически балансируется — количество ветвей справа от
корневого узла приблизительно равно количеству ветвей слева.
Это обеспечивает простой и быстрый способ выборки
необходимой информации.
Корень (Root)
СХЕМА B-ДЕРЕВА
ROOT
INT
INT
INT
Указатели на промежуточные узлы
Промежуточный Указатели на листовые узлы
Листовой (Leaf) Ключи индекса + данные / RID
Иерархия: корень → промежуточные узлы → листовые узлы.
LEAF
LEAF
LEAF
LEAF
LEAF
LEAF
LEAF

5.

Кластерный vs некластерный индекс
Что хранится на листовом уровне — главное отличие
На листовом уровне кластерного индекса содержатся действительные данные. На листовом уровне некластерного —
идентификатор строки (Row ID, RID) или кластеризованный ключ для дальнейшего поиска.
✦
КЛАСТЕРНЫЙ ИНДЕКС
Лист = реальные данные
▤
НЕКЛАСТЕРНЫЙ ИНДЕКС
Лист = RID или ключ кластера
С ОД Е РЖИМОЕ ЛИС ТА
Действительные данные таблицы
С ОД Е РЖИМОЕ ЛИС ТА
Row ID (RID) или кластеризованный ключ
К ОЛИЧ ЕС ТВО
Только 1 на таблицу
К ОЛИЧ ЕС ТВО
Неограниченно на таблицу
Автоматически на PRIMARY KEY
С ОЗ Д АНИЕ
Вручную через CREATE INDEX
Students.StudentID (PK)
ПРИМ Е Р В C OLLE GE DB
Students.LastName, Grades.StudentID
С ОЗ Д АНИЕ
ПР ИМ Е Р В C OLLE GE DB
-- Создаётся автоматически с PK
ALTER TABLE Students
ADD CONSTRAINT PK_Students
PRIMARY KEY (StudentID);
-- → кластерный индекс создан
-- Создаётся вручную
CREATE NONCLUSTERED INDEX
IX_Students_LastName
ON Students(LastName);
ДВА ШАГА: НАЙТИ RID В ИНДЕКСЕ, ЗАТЕМ НАЙТИ ДАННЫЕ
ДАННЫЕ ФИЗИЧЕСКИ УПОРЯДОЧЕНЫ ПО КЛЮЧУ
RID = (экстент, страница, смещение строки) — указывает на физическое расположение записи. Если есть кластерный
индекс, вместо RID хранится кластеризованный ключ.

6.

CREATE INDEX на CollegeDB
-- 1) Некластерный индекс для поиска по фамилии студента
-- Students.LastName уже использовался в SELECT-WHERE
CREATE NONCLUSTERED INDEX IX_Students_LastName
ON Students(LastName);
-- 2) Составной индекс для расписания по дате и группе
-- Частый запрос: «расписание группы ИС-301 на 10 февраля»
CREATE NONCLUSTERED INDEX IX_Schedule_Date_Group
ON Schedule(LessonDate, GroupID);
СИ Н Т АК СИ С
CREATE INDEX
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <имя_индекса>
ON <таблица>(столбец1, столбец2, ...)
[WHERE <условие>];
Имя индекса: IX_<Таблица>_<Поле> — стандартное соглашение об
именовании.
П О Ч Е МУ И МЕ Н Н О ЭТ И П О Л Я
-- 3) Индекс на внешнем ключе Grades.StudentID
-- Ускоряет JOIN Grades Students
CREATE NONCLUSTERED INDEX IX_Grades_StudentID
ON Grades(StudentID);
● LastName — поле частого поиска в Students
● (LessonDate, GroupID) — типовой запрос расписания
● Grades.StudentID — FK на Students; JOIN без индекса =
-- 4) Уникальный индекс на email студента
-- Email должен быть уникален — индекс + ограничение
CREATE UNIQUE NONCLUSTERED INDEX IX_Students_Email
ON Students(Email)
WHERE Email IS NOT NULL;
С В Я З Ь С П Р Е Д Ы Д У ЩЕ Й П Р Е З Е Н Т А Ц И Е Й
-- 5) Удаление индекса (если создан по ошибке)
DROP INDEX IX_Students_LastName
ON Students;
медленно
● Email — уникальность + частая авторизация
В предыдущей презентации мы создали таблицы Students,
Grades, Schedule с первичными и внешними ключами.
PRIMARY KEY уже создал кластерные индексы автоматически.
Теперь мы добавляем ручные некластерные индексы для
ускорения конкретных запросов.

7.

Введение в язык SQL
Structured Query Language — как появился и зачем
Основное предназначение любой БД — накопление информации и предоставление её при необходимости.
Необходимые данные мы получаем, запросив их у БД, то есть написав код-запрос. Для этого нужен
специализированный язык программирования. Традиционные языки (COBOL, Fortran) на эту роль не подошли — был
разработан SQL (Structured Query Language).
ИСТОРИЯ ЯЗЫКА
ПОЧЕМУ НЕ COBOL И FORTRAN?
1970-е · SEQUEL
Традиционные языки существовали на момент появления
реляционной модели (1970, Эдгар Кодд). Но они были
процедурными — программист описывал КАК получить результат.
Реляционная модель требовала декларативного подхода — ЧТО
получить, а не КАК.
Structured English Query Language
Разработан компанией IBM как часть проекта System/R
для реализации реляционной СУБД.
~1976 · SEQUEL/2
Вторая версия языка
Расширены возможности, добавлены новые операторы.
Конец 1970-х · Переименование
SEQUEL → SQL
Язык переименован из SEQUEL в SQL из-за конфликтов
торговой марки.
1986 · SQL-86 (ANSI)
Первый официальный стандарт
Утверждён ANSI, в 1987 — ISO. Начало стандартизации.
PROCEDURAL ·
COBOL/FORTRAN
DECLARATIVE · SQL
Описываем алгоритм шаг за
шагом
Описываем желаемый результат
«КАК получить»
«ЧТО получить»
СУБД не оптимизирует
СУБД сама строит план
выполнения

8.

SQL — декларативный язык
Вы указываете ЧТО нужно, а задача КАК получить — решает СУБД
мне фамилии только тех студентов, которые родились в
“ Покажите
ноябре.
1
SELECT
2
FROM
3
WHERE
ЧТО ПОЛУЧИТЬ
ОТКУДА ВЗЯТЬ
КАКОЕ УСЛОВИЕ
«Покажите мне ... фамилии»
«... студентов ...»
«... которые родились в ноябре»
SELECT LastName
FROM Students
WHERE MONTH(BirthDate) = 11;
Перечисляем столбцы, которые хотим
увидеть в результате. Можно * (все
столбцы), можно конкретные — лучше
конкретные.
Указываем таблицу (или таблицы через
JOIN) — источник данных. У нас это
Students из CollegeDB.
Фильтруем строки результата. MONTH()
— встроенная функция T-SQL, возвращает
номер месяца из даты.
-- Полный запрос на таблице Students из CollegeDB
SELECT LastName
FROM Students
WHERE MONTH(BirthDate) = 11;

9.

Диалект Transact-SQL
Реализация SQL корпорацией Microsoft для SQL Server
Transact-SQL — реализация SQL, разработанная корпорацией Microsoft для СУБД Microsoft SQL Server. T-SQL расширяет
стандартный SQL процедурными конструкциями, переменными, управляющими операторами и встроенными функциями.
4 ГРУППЫ ОПЕРАТОРОВ
ОПЕРАТОРЫ
АРИФМЕТИЧЕСКИЕ
+ - * / %
ЛОГИЧЕСКИЕ
AND OR NOT
СРАВНЕНИЯ
= > < >= <= <>
МНОЖЕСТВА
IN (подзапросы)
УПРАВЛЕНИЕ ПОТОКОМ
Условный оператор IF и цикл WHILE — процедурные
расширения поверх стандартного SQL.
ПЕРЕМЕННЫЕ
Переменные создаются командой DECLARE с префиксом @.
Присваивание через SET или SELECT.
DECLARE @CourseNum INT = 3;
SET @CourseNum = @CourseNum + 1;
SELECT @CourseNum AS Result;
IF + WHILE
ФУНКЦИИ И КОММЕНТАРИИ
ФУНКЦИИ
4
IF @CourseNum > 4
PRINT N'Выпускной курс';
WHILE @CourseNum < 5
BEGIN
SET @CourseNum += 1;
END;
5
1
DECLARE + ИДЕНТИФИКАТОР @
COUNT, SUM, MIN, MAX, AVG, DATEDIFF,
ABS, GETDATE, MONTH, LEN, SUBSTRING
ВСТРОЕННЫЕ ФУНКЦИИ + 2 ТИПА
КОММЕНТАРИЕВ
КОММЕНТАРИИ
-- строчный комментарий
/* блочный
комментарий */

10.

Понятия DDL, DML, DCL
Три категории SQL-операторов и их применение в CollegeDB
SQL-операторы делятся на три категории: DDL (Data Definition Language — язык описания данных), DML (Data
Manipulation Language — язык управления данными) и DCL (Data Control Language — язык управления доступом к
данным).
СТРУКТУРА
DDL
ДАННЫЕ
DML
ДОСТУП
DCL
DATA DEFINITION LANGUAGE
DATA MANIPULATION LANGUAGE
DATA CONTROL LANGUAGE
CREATE создание объекта
SELECT
GRANT
CREATE DATABASE CollegeDB;
SELECT LastName FROM Students WHERE
MONTH(BirthDate) = 11;
CREATE TABLE Students (…);
ALTER
изменение объекта
ALTER TABLE Students ADD CONSTRAINT
FK_Students_Groups …
DROP
удаление объекта
DROP INDEX IX_Students_LastName ON Students;
✓ Использовались в предыдущей презентации: CREATE
DATABASE, CREATE TABLE, ALTER TABLE
INSERT
запрос информации
вставка данных
INSERT INTO Students VALUES (N'Михаил', N'Козлов', );
предоставление доступа
GRANT SELECT ON Students TO college_student_role;
DENY
запрет доступа
DENY DELETE ON Grades TO college_student_role;
REVOKE отмена привилегий
UPDATE обновление данных
UPDATE Students SET Email = NULL WHERE
StudentID = 5;
DELETE
удаление данных
DELETE FROM Grades WHERE GradeDate < '2024-01-01';
✓ INSERT INTO — основная команда раздела 03 предыдущей презентации
REVOKE UPDATE ON Teachers FROM college_admin_role;
DCL — администрирование. В курсе разбирать не будем
подробно, но знать нужно.

11.

16 / 17

12.

Практические задания
1
ИНДЕКС
Создать некластерный индекс на поле Email таблицы Students. Объяснить, почему индекс на поле
Course (значения 1–4) — плохая идея.
2
ЗАПРОС
Написать SELECT-запрос, который выводит фамилии и даты рождения всех студентов группы ИС-301
(GroupID = 1), родившихся в ноябре. Использовать SELECT, FROM, WHERE.
3
АНАЛИЗ
Дана таблица Grades с индексом IX_Grades_StudentID. Объяснить: (а) почему этот индекс ускорит
JOIN с таблицей Students; (б) какие DML-операции замедлит этот индекс; (в) к какой категории
SQL-операторов относится CREATE INDEX — DDL, DML или DCL?