- Table of contents
- Теоретическая часть. Введение в язык SQL и основы реляционных баз данных
Теоретическая часть. Введение в язык SQL и основы реляционных баз данных¶
Реляционная база данных - это база данных, построенная на реляционной модели данных. Данные в базе данных представляют собой набор отношений, которые в свою очередь отвечают определенным условиям целостности.
Описание нормализации¶
Нормализация — это процесс организации данных в базе данных. Это включает создание таблиц и установление связей между этими таблицами в соответствии с правилами, предназначенными как для защиты данных, так и для того, чтобы сделать базу данных более гибкой за счет устранения избыточности и непоследовательной зависимости.
Избыточные данные пустая трата дискового пространства и создает проблемы с обслуживанием. Если данные, которые существуют в нескольких местах, должны быть изменены, данные должны быть изменены точно так же во всех расположениях.
Таким образом, изменение данных проще реализовать, если эти данные хранятся только в одной таблице и нигде больше в базе данных.
Непоследовательная зависимость может затруднить доступ к данным, так как путь к поиску данных может быть пропущен или нарушен.
Существует несколько правил нормализации базы данных. Каждое правило называется "нормальной формой". Если первое правило соблюдается, база данных, как сообщается, находится в "первой нормальной форме". Если соблюдаются первые три правила, база данных рассматривается как "третья нормальная форма". Хотя возможны другие уровни нормализации, третья нормальная форма считается наивысшим уровнем, необходимым для большинства приложений.
Типы ключей баз данных¶
Для понимания отношений и зависимостей в базе данных, необходимо знать, какие ключи бывают, какие к ним требования, для чего их используют.
Суперключ - это подмножество атрибутов отношения, удовлетворяющих требованию уникальности: не существует двух одинаковых значений, образованных этим подмножеством. Другими словами, это столбец или комбинация столбцов, которые дают уникальное значение.
Потенциальный ключ - это подмоножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности (его нельзя сократить). Другими словами, это все минимальные суперключи.
В базе данных может быть одновременно несколько потенциальных ключей. Один из них может быть выбран в качестве первичного ключа, тогда остальные потенциальные ключи будут называться альтренативными ключами.
Первичный ключ - один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию).
Внешний ключ - это подмножество атрибутов отношения R2, значения которых должны совпадать со значениями некоторого потенциального ключа некоторой переменной отношения R1.
Чтобы было проще понять, вот здесь уже покажу на таблицах.
таблица 1 (R1)
| ID | Атрибут |
| 1 | Исполнитель |
| 2 | Автор |
| 3 | Жанр |
таблица 2 (R2)
| ID | ID Атрибута | Значение |
| 100 | 1 | Филипп Киркоров |
| 101 | 1 | Алла Пугачева |
| 102 | 2 | Степанов В. |
| 103 | 2 | Снежина Т. |
| 104 | 3 | Pop |
| 105 | 3 | Rock |
Здесь "ID Атрибута" - это поле, которое является внешним ключом и ссылается на потенциальный ключ "ID" в таблице 1. Такая связь обеспечивает целостность данных, в таблице 2, в поле "ID Атрибута" мы не сможем добавить значение, которое отсутствует в таблице 1.
Классификация первичного ключа:
Простой ключ - это ключ, состоящий из единственного атрибута.
Составной ключ - это ключ, состоящий из двух и более атрибутов.
Естественный ключ - это ключ, состоящий из информационных атрибутов таблицы (полей, содержащих полезную информацию об описываемых объектах).
Суррогатный ключ - это ключ, состоящий из автоматически сгенерированного атрибута таблицы, никак не связанного с информационным содержанием записи.
Пример для упрощения понимания
| Исполнитель | Композиция | Длительность |
| Филлип Киркоров |
Роза чайная |
4:22 |
|
Филлип Киркоров |
Я за тебя умру |
3:27 |
|
Филлип Киркоров |
Огонь и вода |
3:14 |
|
Алла Пугачева |
Позови меня с собой |
4:19 |
Здесь в качестве первичного ключа выступает "Исполнитель + Композиция" - это составной, естественный ключ. Составной, т.к. состоит из двух атрибутов (полей) и естественный, т.к. состоит из информационных атрибутов (значение полей Исполнитель и Композиция очень даже полезны для нас).
| ID | Исполнитель | Композиция | Длительность | |
| 1 | Филлип Киркоров |
Роза чайная |
4:22 | |
| 2 |
Филлип Киркоров |
Я за тебя умру |
3:27 | |
| 3 |
Филлип Киркоров |
Огонь и вода |
3:14 | |
| 4 |
Алла Пугачева |
Позови меня с собой |
4:19 |
Здесь в качестве первичного ключа выступает поле "ID" - это простой, суррогатный ключ. Простой, т.к. состоит из одного атрибута и суррогатный, т.к. добавлен к записям искусственно, его значение генерируется автоматически и по смыслу он никак не относится к записям.
Нормальные формы¶
Нормальная форма - свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, потенциально приводящей к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяет совокупность требований, которым должно удовлетворять отношение.
Замечания, для упрощения понимания примеров:
- Первая строка - загаловок таблицы, название полей
- Подчеркнутые названия в первой строке - первичный ключ конкретно рассматриваемой таблицы
Первая нормальная форма (1НФ)¶
Переменная отношения находится в первой нормально форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.
Теперь, что это значит простым, человеческим языком. Одно поле - одно значение, никаких перечислений через запятую, точку с запятой и т.д.
Пример нарушения 1НФ:
| Исполнитель | Композиции |
| Филипп Киркоров | Роза чайная; Я за тебя умру; Огонь и вода |
| Алла Пугачева | Позови меня с собой |
Преобразование таблицы к 1НФ:
| Исполнитель | Композиция |
| Филлип Киркоров |
Роза чайная |
|
Филлип Киркоров |
Я за тебя умру |
|
Филлип Киркоров |
Огонь и вода |
|
Алла Пугачева |
Позови меня с собой |
Вторая нормальная форма (2НФ)¶
Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый неключевой атрибут неприводимо (функционально полно) зависит от её потенциального ключа. Функционально полная зависимость означает, что если потенциальный ключ является составным, то атрибут зависит от всего ключа и не зависит от его частей.
Теперь, что это значит простым, человеческим языком. Когда в таблице есть какой-то потенциальный ключ (идентификатор, от которого зависят остальные значения), связь должна быть полной: ключ - значение. Если среди значений можно найти такие, что зависят только от части ключа (одно из двух или трех полей), то это уже не полная зависимость и нарушение 2НФ.
Пример нарушения 2НФ:
| Исполнитель | Композиция | Длительность | Пол |
| Филлип Киркоров |
Роза чайная |
4:22 | Мужской |
|
Филлип Киркоров |
Я за тебя умру |
3:27 |
Мужской |
|
Филлип Киркоров |
Огонь и вода |
3:14 |
Мужской |
|
Алла Пугачева |
Позови меня с собой |
4:19 | Женский |
Здесь видно, что Длительность зависит от Исполнителя и Композиции (ключ "Исполнитель + Композиция"), а вот Пол зависит только от Исполнителя. Сколько бы Филипп Киркоров не пел, пол у него не изменится, а вот в зависимости от того, какую он песню поет или кто поет его песню, длительность может измениться.
Процесс нормализации здесь называется - декомпозиция, когда мы разбиваем одно отношение (в данном случае - таблицу) на два отношения, в которых не ключевые атрибуты полностью зависят от первичного ключа.
Пример декомпозиции к 2НФ:
таблица 1
| Исполнитель | Композиция | Длительность |
| Филлип Киркоров |
Роза чайная |
4:22 |
|
Филлип Киркоров |
Я за тебя умру |
3:27 |
|
Филлип Киркоров |
Огонь и вода |
3:14 |
|
Алла Пугачева |
Позови меня с собой |
4:19 |
таблица 2
| Исполнитель | Пол |
| Филлип Киркоров |
Мужской |
|
Алла Пугачева |
Женский |
Третья нормальная форма (3НФ)¶
Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во второй нормальной форме, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых.
Теперь, что это значит простым, человеческим языком. Это когда какой-то атрибут зависит от ключевого не напрямую, а через другой атрибут. Ключ -> Атрибут 1 -> Атрибут 2
Пример нарушения 3НФ:
| Альбом | Исполнитель | Пол |
|
ЧелоФилия |
Филипп Киркоров | Мужской |
| Я на Рафаэль | Филипп Киркоров | Мужской |
| Миллион роз | Алла Пугачева | Женский |
В этом примере, первичным ключом выступает поле Альбом. Пола у Альбома нет, он зависит только от Исполнителя.
Таким образом, мы получаем следующую модель отношений: Альбом -> Исполнитель, Исполнитель -> Пол, Альбом -> Пол.
Зависимость Альбом -> Пол является транзитивной и нарушает 3НФ.
Пример декомпозиции к 3НФ:
таблица 1
| Исполнитель | Пол |
| Филипп Киркоров | Мужской |
| Алла Пугачева | Женский |
таблица 2
| Альбом | Исполнитель |
|
ЧелоФилия |
Филипп Киркоров |
| Я на Рафаэль | Филипп Киркоров |
| Миллион роз | Алла Пугачева |
Нормальная форма Бойса-Кодда (НФБК)¶
Так же ее называют Частной формой 3НФ
Переменная отношения находится в нормальной форме Бойса — Кодда (иначе — в усиленной третьей нормальной форме) тогда и только тогда, когда каждая её нетривиальная и неприводимая слева функциональная зависимость имеет в качестве своего детерминанта некоторый потенциальный ключ.
Данаая форма образовалась из-за того, что 3НФ не совсем подходит для некоторых (частных случаев):
- Отношение имеет два или более потенциальных ключа
- Два и более потенциальных ключа являются составными (т.е. образуют один большой, потенциальный ключ)
- Отношения пересекаются (т.е. имеют хотя бы один общий атрибут)
Когда в отношении только один потенциальный ключ, НФБК является 3НФ.
В качестве примера рассмотрим гипотетическую ситуацию о времени размещения роликов в медиаплане по пакетам передач. Важное замечание: пример максимально гипотетический и только для объяснения НФБК, он не имеет ничего общего с архитектурой БД Digispot II.
| Расписание | Время начала блока | Время окончания блока | Пакет передач |
| Москва | 09:30 | 09:35 | Спонсорский |
| Москва | 11:00 | 11:05 | Спонсорский |
| Москва | 14:00 | 14:03 | Стандарт |
| Регион +3 | 11:02 | 11:05 | Регион-Стандарт |
|
Регион +3 |
12:02 | 12:05 | Регион-Стандарт |
|
Регион +3 |
14:00 | 14:05 | Регион-Спонсорский |
В чем смысл данной таблицы:
Существуют различные пакеты передач (тарифы), в которых стоимость проката рекламы для Москвы и для Региона отличаются (допустим, в Москве реклама дороже). Пакет передач функционально зависит от Расписания. Имея такую таблицу, можно случайно отправить в регион рекламный блок по тарифу Москвы, что будет несколько печально.
Небольшая разминка для лучшего усвоения материала, задача - проанализировать таблицу и выписать возможные потенциальные ключи.
Решение задачи:
Потенциальные ключи:
- Расписание + Время начала блока -> от этого ключа могут зависеть Время окончания блока и используемый Пакет передач (тариф)
внимательный читатель может заметить, что в столбце "Время окончания блока", есть два блока, которые заканчиваются в одно и то же время, поэтому для времени окончания требуется 2 параметра, чтобы однозначно его определить - Расписание + Время окончания блока -> от этого ключа могут зависеть Время начала блока и используемый Пакет передач (тариф)
опять же, внимательный читатель мог заметить, что в столбце "Время начала блока", есть два блока, которые начинаются в одно и то же время, поэтому для них требуется 2 параметра, чтобы однозначно определить время начала блока - Пакет передач + Время начала блока -> от этого ключа могут зависеть Расписание и Время окончания блока
- Пакет передач + Время окончания блока -> от этого ключа могут зависеть Расписание и Время начала блока
Если нам надо найти название расписания, в котором находится рекламный блок, одного поля "Время начала блока" нам не хватит: на 14:00 существуют два разных расписания
Аналогично не подходит "Время окончания блока", а вот по "Пакету передач" - можно, но оно не удовлетворяет требованиям уникальности и не может быть потенциальным ключом.
Мы можем найти название расписания по времени начала блока/времени окончания блока и пакета передач. Комбинация из двух полей даст нам однозначный результат.
Пример декомпозиции к НФБК:
таблица 1
| Пакет передач | Расписание | Наличие спонсорских прокатов |
| Спонсорский | Москва | Да |
| Стандарт | Москва | Нет |
| Регион-Спонсорский | Регион +3 | Да |
| Регион-Стандарт | Регион +3 | Нет |
таблица 2
| Пакет передач | Время начала блока | Время окончания блока |
| Спонсорский | 09:30 | 09:35 |
| Спонсорский | 11:00 | 11:05 |
| Стандарт | 14:00 | 14:03 |
| Регион-Стандарт | 11:02 | 11:05 |
| Регион-Стандарт | 12:02 | 12:05 |
| Регион-Спонсорский | 14:00 | 14:05 |
Четвертая нормальная форма (4НФ)¶
Переменная отношения находится в четвёртой нормальной форме, если она находится в нормальной форме Бойса — Кодда и не содержит нетривиальных многозначных зависимостей.
Теперь, что это значит простым, человеческим языком. Сложно.
Начнем с того, что такое многозначная зависимость:
В отношении R(A,B,C) существует многозначная зависимость R.A ->> R.B в том и только в том случае, если множество значений B, соответствующих паре значений A и C, зависит только от A и не зависит от C.
Сразу соберем пример и рассмотрим его - DDB соединение и отправляемые в него расписания и категории БД. Расписания и категории БД никак не связаны между собой, они являются полностью независимыми друг от друга сущностями. Выглядит примерно так:
| DDB соединение | Расписание | Категория БД |
| Новосибирск | Регион +4 | Музыка |
| Новосибирск | Регион +4 |
Передачи |
| Новосибирск | Реклама +4 | Музыка |
| Новосибирск | Реклама +4 | Передачи |
| Новосибирск | Регион +4 | Джинглы |
| Новосибирск | Реклама +4 | Джинглы |
| Томск | Реклама +4 | Музыка |
| Томск | Реклама +4 | Джинглы |
Что плохого в такой структуре? Абсолютно все.
Во-первых, это крайне избыточно и количество записей будет расти в геометрической последовательности.
Из полезного тут только следующее:
- В Новосибирск отправляется 2 расписания: "Регион +4" и "Реклама +4"
- В Томск отправляется 1 расписание: "Реклама +4"
- В Новосибирск отправляет 3 категории БД: "Музыка", "Передачи" и "Джинглы"
- В Томск отправляется 2 категории БД: "Музыка" и "Джинглы"
Во-вторых, возможность появления логической аномалии.
В чем она заключается? Если мы хотим добавить в DDB соединение еще одно расписание, нам необходимо будет добавить записи в связке с каждой категорией БД, которую мы передаем для этого DDB соединения. Требуется отдельный механизм проверки целостности отношений, иначе можно попасть в ситуацию, что определенному расписанию будут соответствовать не все категории БД, которые мы передаем в соединении.
Практическое задание (выполняется в excel):
- Скопируйте исходные танные в таблицу
- Добавьте еще одно DDB соединение: Красноярск и передавайте в него расписание "Реклама +4" и категорию "Музыка"
- Для соединения Томск добавьте еще одно расписание: "Новости +4"
- Для соединения Красноярск добавьте еще одну категорию БД "Новости"
- Для соединения Новосибирск добавьте расписание "Новости +4" и категорию "Новости"
Сколько у вас получилось всего записей в таблице? Насколько удобно было добавлять новую информацию? Насколько легко теперь искать в такой таблице информацию? Предположим, вам надо будет добавить еще с десяток соединений, под другие часовые пояса, со своими наборами расписаний и категориями БД, ваши ощущения от предстоящей задачи?
Пример декомпозиции:
таблица 1
| DDB соединение | Расписание |
| Новосибирск | Регион +4 |
| Новосибирск | Реклама +4 |
| Томск | Реклама +4 |
таблица 2
| DDB соединение | Категория БД |
| Новосибирск | Музыка |
| Новосибирск | Передачи |
| Новосибирск | Джинглы |
| Томск | Музыка |
| Томск | Джинглы |
Так проще работать с информацией?
Пятая нормальная форма (5НФ)¶
Переменная отношения находится в пятой нормальной форме (иначе — в проекционно-соединительной нормальной форме), если она находится в четвертой нормальной форме и каждая нетривиальная зависимость соединения в ней определяется потенциальным ключом (ключами) этого отношения.
Очень сложно, не могу пока объяснить и сделать пример.
Доменно-ключевая нормальная форма (ДКНФ)¶
Переменная отношения находится в ДКНФ тогда и только тогда, когда каждое наложенное на неё ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данную переменную отношения.
Очень сложно, не могу пока объяснить и сделать пример.
Шестая нормальная форма (6НФ)¶
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.
Очень сложно, не могу пока объяснить и сделать пример.
Типы данных в SQL¶
Числовые типы данных¶
- BIT: хранит значение 0 или 1. Фактически является аналогом булевого типа в языках программирования. Занимает 1 байт.
- TINYINT: хранит числа от 0 до 255. Занимает 1 байт. Хорошо подходит для хранения небольших чисел.
- SMALLINT: хранит числа от –32 768 до 32 767. Занимает 2 байта
- INT: хранит числа от –2 147 483 648 до 2 147 483 647. Занимает 4 байта. Наиболее используемый тип для хранения чисел.
- BIGINT: хранит очень большие числа от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, которые занимают в памяти 8 байт.
-
DECIMAL(p, s): хранит числа c фиксированной точностью. Занимает от 5 до 17 байт в зависимости от количества чисел после запятой.
Данный тип может принимать два параметра precision и scale: DECIMAL(precision, scale).
Параметр precision представляет максимальное количество цифр, которые может хранить число. Это значение должно находиться в диапазоне от 1 до 38. По умолчанию оно равно 18.
Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0. - NUMERIC: данный тип аналогичен типу DECIMAL.
- SMALLMONEY: хранит дробные значения от -214 748.3648 до 214 748.3647. Предназначено для хранения денежных величин. Занимает 4 байта. Эквивалентен типу DECIMAL(10,4).
- MONEY: хранит дробные значения от -922 337 203 685 477.5808 до 922 337 203 685 477.5807. Представляет денежные величины и занимает 8 байт. Эквивалентен типу DECIMAL(19,4).
-
FLOAT(n): хранит числа от –1.79E+308 до 1.79E+308. Занимает от 4 до 8 байт в зависимости от дробной части.
Может иметь форму опредеения в виде FLOAT(n), где n представляет число бит, которые используются для хранения десятичной части числа (мантиссы). По умолчанию n = 53. - REAL: хранит числа от –340E+38 to 3.40E+38. Занимает 4 байта. Эквивалентен типу FLOAT(24).
Дата и время¶
- DATE: хранит даты от 0001-01-01 (1 января 0001 года) до 9999-12-31 (31 декабря 9999 года). Занимает 3 байта.
-
TIME(n): хранит время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999. Занимает от 3 до 5 байт.
Может иметь форму TIME(n), где n представляет количество цифр от 0 до 7 в дробной части секунд. По умолчанию n = 7. -
DATETIME(n): хранит даты от 01/01/1753 до 31/12/9999 и время от 00:00:00 до 23:59:59.997. Занимает 8 байт.
Может иметь форму DATETIME(n), где n представляет количество цифр от 0 до 3 в дробной части секунд. По умолчанию n = 3.
Microsoft рекомендует не использовать этот тип данных, как устаревший. -
DATETIME2(n): хранит даты и время в диапазоне от 01/01/0001 00:00:00.0000000 до 31/12/9999 23:59:59.9999999. Занимает от 6 до 8 байт в зависимости от точности времени.
Может иметь форму DATETIME2(n), где n представляет количество цифр от 0 до 7 в дробной части секунд. По умолчанию n = 7. -
SMALLDATETIME: хранит даты и время в диапазоне от 01/01/1900 до 06/06/2079, то есть ближайшие даты. Занимает от 4 байта.
Microsoft рекомендует не использовать этот тип данных, как устаревший. -
DATETIMEOFFSET(n): хранит даты и время в диапазоне от 0001-01-01 до 9999-12-31 и добавляет сведения о часовом поясе на основе UTC. Сохраняет детальную информацию о времени с точностью до 100 наносекунд.Занимает 10 байт.
Может иметь форму DATETIMEOFFSET(n), где n представляет количество цифр от 0 до 7 в дробной части секунд. По умолчанию n = 7.
Символьные строки¶
-
CHAR(n): строковые данные фиксированного размера. n определяет размер строки в байтах и должно иметь значение от 1 до 8000. Для однобайтовых кодировок, количество символов будет равно размеру хранения (т.е. можно считать, что n - количество символов).
Для многобайтовых кодировок размер хранения остается указанным, но количество символов будет меньше (зависит от того, сколько байт выделяется для хранения одного символа). - VARCHAR(n|max): строковые данные переменного размера. Используйте значение n для определения размера строки в байтах (допускаются значения от 1 до 8000) или используйте max для указания предельного размера столбца вплоть до максимального размера хранилища, что составляет 2ГБ. Для однобайтовых кодировок, размер при хранении равен n байт + 2 байта, а количество хранимых символов - n. Для многобайтовых кодировок размер при хранении тоже равен n байт + 2 байта, но количество хранимых символов может быть меньше n.
-
NCHAR(n): строковые данные фиксированного размера. n определяет размер строки в парах байтов и должно иметь значение от 1 до 4000. Для хранения юникодных символов.
Размер хранилища 2n байт, количество хранимых символов для UCS-2 (без поддержки дополнительных символов) равно n, а для UTF-16 (с поддержкой дополнительных символов) это значение может быть меньше n. -
NVARCHAR(n|max): строковые данные переменного размера. n определяет размер строки в парах байтов и должно иметь значение от 1 до 4000. Для хранения юникодных символов.
Значение max указывает, что максимальный размер при хранении составляет 2ГБ. Размер хранилища 2n байт, количество хранимых символов для UCS-2 (без поддержки дополнительных символов) равно n, а для UTF-16 (с поддержкой дополнительных символов) это значение может быть меньше n.
text и ntext - устарели, поэтому не даю их описание.
Двоичные (бинарные) данные¶
- BINARY(n): двоичные данные фиксированной длины размером в n байт, где n - значение от 1 до 8000. Размер при хранении составляет n байт.
- VARBINARY(n|max): довичные данные с переменной длиной. n может иметь значение от 1 до 8000. Значение max указывает, что максимальный размер при хранении составляет 2ГБ. Размер хранения - это фактическая длина введенных данных плюс 2 байта.
Прочие типы данных¶
Если есть необходимость ознакомиться с прочими типами данных, это можно сделать на сайте Microsoft: ссылка
Индексы¶
Индекс — это объект базы данных, который представляет собой структуру данных, состоящую из ключей, построенных на основе одного или нескольких столбцов таблицы или представления, и указателей, которые сопоставляются с местом хранения заданных данных. Индексы предназначены для более быстрого получения строк из таблицы, другими словами, индексы обеспечивают быстрый поиск данных в таблице, что значительно повышает производительность запросов и приложений. Индексы также могут быть использованы и для обеспечения уникальности строк таблицы, гарантируя тем самым целостность данных.
Индексы могут повысить скорость выборки данных (SELECT), но индексы уменьшают скорость модификации данных таблицы, т.к. после каждой модификации системе будет необходимо перестроить все индексы для конкретной таблицы.
Желательно в каждом случае найти оптимальное решение, золотую середину, чтобы и производительность выборки, так и модификации данных была на должном уровне. Стратегия по созданию индексов и их количества может зависеть от многих факторов, например, насколько часто изменяются данные в таблице.
Типы индексов в MS SQL Server:
- Кластеризованный (Clustered) – это индекс, который хранит данные таблицы в отсортированном, по значению ключа индекса, виде. У таблицы может быть только один кластеризованный индекс, так как данные могут быть отсортированы только в одном порядке. По возможности каждая таблица должна иметь кластеризованный индекс, если у таблицы нет кластеризованного индекса, такая таблица называется «кучей». Кластеризованный индекс создается автоматически при создании ограничений PRIMARY KEY (первичный ключ) и UNIQUE, если до этого кластеризованный индекс для таблицы еще не был определен. В случае создания кластеризованного индекса для таблицы (кучи), в которой есть некластеризованные индексы, то после создания все их необходимо перестроить.
- Некластеризованный (Nonclustered) – это индекс, который содержит значение ключа и указатель на строку данных, содержащую значение этого ключа. У таблицы может быть несколько некластеризованных индексов. Создаваться некластеризованные индексы могут как на таблицах с кластеризованным индексом, так и без него. Именно этот тип индекса используется для повышения производительности часто используемых запросов, так как некластеризованные индексы обеспечивают быстрый поиск и доступ к данным по значениям ключа;
- Фильтруемый (Filtered) – это оптимизированный некластеризованный индекс, который использует предикат фильтра для индексирования части строк в таблице. Если хорошо спроектировать такой тип индекса, то он может повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами;
- Уникальный (Unique) – это индекс, который обеспечивает отсутствие повторяющихся (одинаковых) значений ключа индекса, гарантируя тем самым уникальность строк по данному ключу. Уникальными могут быть как кластеризованные, так и некластеризованные индексы. Если создавать уникальный индекс по нескольким столбцам, индекс гарантирует уникальность каждой комбинации значений в ключе. При создании ограничений PRIMARY KEY или UNIQUE SQL сервер автоматически создает уникальный индекс для ключевых столбцов. Уникальный индекс может быть создан только в том случае, если у таблицы на текущий момент отсутствуют дублирующие значения по ключевым столбцам;
- Колоночный (Columnstore) – это индекс, основанный на технологии хранения данных в виде столбцов. Данный тип индекса эффективно использовать для больших хранилищ данных, поскольку он может увеличить производительность запросов к хранилищу до 10 раз и также до 10 раз уменьшить размер данных, так как данные в Columnstore индексе сжимаются. Существуют как кластеризованные колоночные индексы, так и некластеризованные;
- Полнотекстовый (Full-text) – это специальный тип индекса, который обеспечивает эффективную поддержку сложных операций поиска слов в символьных строковых данных. Процесс создания и обслуживания полнотекстового индекса называется «заполнением». Существует такие типы заполнения как: полное заполнение и заполнение на основе отслеживания изменений. По умолчанию SQL сервер полностью заполняет новый полнотекстовый индекс сразу после его создания, но на это может потребоваться значительный объем ресурсов, в зависимости от размеров таблицы, поэтому есть возможность откладывать полное заполнение. Заполнение на основе отслеживания изменений используется для обслуживания полнотекстового индекса после его первоначального полного заполнения;
- Пространственный (Spatial) – это индекс, который обеспечивает возможность более эффективного использования конкретных операций на пространственных объектах в столбцах с типом данных geometry или geography. Данный тип индекса может быть создан только для пространственного столбца, также таблица, для которой определяется пространственный индекс, должна содержать первичный ключ (PRIMARY KEY);
- XML – это еще один специальный тип индекса, который предназначен для столбцов с типом данных XML. Благодаря XML-индексу повышается эффективность обработки запросов к XML столбцам. Существует два вида XML-индекса: первичные и вторичные. Первичный XML-индекс индексирует все теги, значения и пути, хранимые в XML столбце. Он может быть создан, только если у таблицы есть кластеризованный индекс по первичному ключу. Вторичный XML-индекс может быть создан, только если у таблицы есть первичный XML-индекс и используется он для повышения производительности запросов по определенному типу обращения к XML-столбцу, в связи с этим существует несколько типов вторичных индексов: PATH, VALUE и PROPERTY;
- Также существуют специальные индексы для таблиц, оптимизированных для памяти (In-Memory OLTP) такие как: Хэш (Hash) индексы и некластеризованные индексы, оптимизированные для памяти, которые создаются для сканирования диапазона и упорядоченного сканирования.
С полнотекстовыми индексами вы уже сталкивались: Настройка_полнотекстового_поиска
