Что умеет SQL:

• Определение данных.
  SQL позволяет пользователю определить структуру и организацию хранимых данных и взаимоотношения между элементами сохраненных данных.

• Выборка данных.
  SQL дает пользователю или приложению возможность извлекать из базы содержащиеся в ней данные и пользоваться ими.

• Обработка данных.
  SQL позволяет пользователю или приложению изменять базу данных, т.е. добавлять в нее новые данные, а также удалять или обновлять уже имеющиеся в ней данные.

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

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

• Целостность данных.
  SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы.

Сердцем СУБД является механизм базы данных (database engine, часто называемый просто движком). Oн отвечает за структурирование данных, сохранение и получение их из базы данных. Он принимает SQL-запросы от других компонентов СУБД, от пользовательских приложений и даже от других вычислительных систем.

Язык SQL относится к непроцедурным (nonprocedural) языкам — он лишь описывает нужные компоненты (например, таблицы) и желаемые результаты, не указывая, как именно эти результаты должны быть получены. Каждая реализация SQL является надстройкой над процессором базы данных (database engine), который интерпретирует операторы SQL и определяет порядок обращения к структурам БД для корректного и эффективного формирования желаемого результата.

(раскрыть все)

• CREATE
  Создает объект БД (базу, таблицу, представление, пользователя и т.д.).

  CREATE DATABASE Test

• ALTER
  Изменяет объект.

  ALTER TABLE ...

• DROP
  Удаляет объект.

  DROP TABLE ...

• TRUNCATE
  "Удаление" всех записей в таблице.

  TRUNCATE TABLE ...

  Особенности

  • Не срабатывают триггеры, в частности, триггер удаления
  • Удаляет все строки в таблице, не записывая при этом удаление отдельных строк данных в журнал транзакций
  • Сбрасывает счетчик идентификаторов до начального значения
  • Чтобы использовать, необходимы права на изменение таблицы
  • Не используется когда в таблице есть внешние ключи надо использовать DELETE

• GRANT
  Предоставляет пользователю (группе) разрешения на определенные операции с объектом.

  Предоставление права чтения таблицы students пользователю.
  GRANT SELECT ON table_name TO user_name;

• REVOKE
  Отзывает ранее выданные разрешения.

  Отменить запрет.
  REVOKE SELECT ON table_name FROM user_name;

• DENY
  Задает запрет, имеющий приоритет над разрешением

  Запрет права выборки из таблицы пользователя.
  DENY SELECT ON table_name TO user_name;

• COMMIT
  Применяется для завершения транзакции и сохранения изменений в базе данных.

• ROLLBACK
  Откатывает все изменения, сделанные в контексте текущей транзакции.

• SAVEPOINT
  Создаёт точку к которой группа транзакций может откатиться, разбивает транзакцию на более мелкие.

• SET TRANSACTION
  Применяется для установки параметров доступа к данным в текущей транзакции.

  Транзакция предназначена только для чтения:
  SET TRANSACTION READ ONLY

Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE

(раскрыть все)

• SELECT
  Выбирает данные, удовлетворяющие заданным условиям.

  Схема запроса

  SELECT [DISTINCT | ALL] поля_таблиц
  FROM список_таблиц
  [WHERE условия_на_ограничения_строк]
  [GROUP BY условия_группировки]
  [HAVING условия_на_ограничения_строк_после_группировки по агрегатным функциям]
  [ORDER BY порядок_сортировки [ASC | DESC]]
  [LIMIT ограничение_количества_записей]

• INSERT
  Добавляет новые данные.

  Схема запроса

  INSERT INTO имя_таблицы [(поле_таблицы, ...)]
  VALUES (значение_поля_таблицы, ...)
  | SELECT поле_таблицы, ... FROM имя_таблицы ...

• UPDATE
  Изменяет существующие данные.

• DELETE
  Удаляет данные.

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

NULL означает отсутствие, неизвестность информации.

Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных.

Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни 0.

При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0.

Более того, NULL не равно NULL!

Команды для проверки: IS NULL, IS NOT NULL

Хранимая процедура - объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере.

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

В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.

Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину.

Схема запроса

View (Представление) - виртуальная таблица, представляющая данные одной или более таблиц альтернативным образом.

В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных.

Представления значительно расширяют возможности управления данными.

Пример дать публичный доступ к некоторой (но не всей) информации в таблице:

CREATE VIEW Londonstaff
        AS SELECT *
        FROM Salespeople
        WHERE city = 'London';

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

Они могут быть локальными (только я могу работать) или глобальными (все).

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

Срок жизни временной таблицы – сеанс с БД

Транзакция - это воздействие на базу данных, переводящее её из одного целостного (консистентного) состояния в другое и выражаемое в изменении данных, хранящихся в базе данных.
Это N (N≥1) запросов к БД, которые выполнятся успешно все вместе или не выполнятся вовсе.

Основные свойства транзакции

• Атомарность (atomicity)
  Гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.

• Согласованность (consistency)
  Транзакция, достигающая своего нормального завершения (EOT — end of transaction, завершение транзакции) и, тем самым, фиксирующая свои результаты, сохраняет согласованность базы данных.

  Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты.

• Изолированность (isolation)
  Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на ее результат.

• Долговечность (durability)
  Независимо от проблем на нижних уровнях (к примеру, обесточивание системы или сбои в оборудовании) изменения, сделанные успешно завершённой транзакцией, должны остаться сохраненными после возвращения системы в работу. Если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя.

При параллельном выполнении транзакций возможны следующие проблемы (аномалии):

1. Потерянное обновление (англ. lost update)
   При одновременном изменении одного блока данных разными транзакциями теряются все изменения, кроме последнего.

2. Грязное чтение (англ. dirty read)
   Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);

3. Неповторяющееся чтение (англ. non-repeatable read)
   При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.

4. Фантомное чтение (англ. phantom reads)
   Одна транзакция в ходе своего выполнения несколько раз выбирает множество строк по одним и тем же критериям. Другая транзакция в интервалах между этими выборками добавляет строки или изменяет столбцы некоторых строк, используемых в критериях выборки первой транзакции, и успешно заканчивается. В результате получится, что одни и те же выборки в первой транзакции дают разные множества строк.

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

В порядке увеличения изолированности транзакций и, соответственно, надежности работы с данными:

• Чтение неподтверждённых данных (грязное чтение) (read uncommitted, dirty read) - чтение незафиксированных изменений как своей транзакции, так и параллельных транзакций.
  Нет гарантии, что данные, измененные другими транзакциями, не будут в любой момент изменены в результате их отката, поэтому такое чтение является потенциальным источником ошибок.
  Невозможны потерянные изменения, возможны неповторяемое чтение и фантомы.

• Чтение подтвержденных данных (read committed) - чтение всех изменений своей транзакции и зафиксированных изменений параллельных транзакций.
  Потерянные изменения и грязное чтение не допускается, возможны неповторяемое чтение (когда мы видим обновленные и удаленные строки (UPDATE, DELETE)) и фантомы (когда мы видим добавленные записи (INSERT)).

• Повторяемость чтения (repeatable read, snapshot) - чтение всех изменений своей транзакции, любые изменения, внесенные параллельными транзакциями после начала своей, недоступны.
  Потерянные изменения, грязное и неповторяемое чтение невозможны, возможны фантомы.

• Упорядочиваемость (serializable) - результат параллельного выполнения сериализуемой транзакции с другими транзакциями должен быть логически эквивалентен результату их какого-либо последовательного выполнения.
  Проблемы синхронизации не возникают.

non-repeatable read

non-repeatable read

non-repeatable read

non-repeatable read

Распространение транзакций (Propagation) в @Transactional

• REQUIRED(0)
  Если активная транзакция уже существует, метод будет выполняться в её контексте. Если транзакции нет, она будет создана. Режим по-умолчанию.

• SUPPORTS(1)
  Если существует активная транзакция, метод будет выполнен в её контексте. Если транзакции нет, метод будет выполнен вне контекста транзакции.

• MANDATORY(2)
  Метод требует наличия активной транзакции. Если ее нет, будет выброшено исключение.

• REQUIRES_NEW(3)
  Метод всегда будет выполняться в новой транзакции. Если уже есть активная транзакция, она будет приостановлена до завершения новой транзакции.

• NOT_SUPPORTED(4)
  Метод будет выполняться вне контекста транзакции. Если имеется активная транзакция, она будет приостановлена на время его выполнения.

• NEVER(5)
  Метод никогда не должен быть выполнен в контексте транзакции. Если транзакция активна, будет выброшено исключение.

• NESTED(6)
  Метод будет выполнен в рамках вложенной транзакции, если уже существует активная транзакция. В противном случае, будет создана новая транзакция.

  Вложенные транзакции позволяют откатывать только часть операции без влияния на внешнюю транзакцию. Однако поддержка этой модели зависимости от конкретных систем управления транзакциями может отличаться.

интересный видосик про транзакции

Грабли при использовании аннотации @Transactional:

• Не вызывать метод, помеченный @Transactional из одного класса.

• @Transactional не откатит транзакцию если выброшено Exception, работает с RuntimeException.

  Но можно настроить

  @Transactional(rollbackFor=Exception.class)
• @Transactional работает только с public методами.
• @Transactional занимает соединение с БД.

очень кратенько, но полезненько

JOIN - оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Предназначен для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор.

Особенностями операции соединения являются следующее:
- В схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является "сцеплением" схем операндов;
- Каждая строка таблицы-результата является "сцеплением" строки из одной таблицы-операнда со строкой второй таблицы-операнда;
- При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).

SELECT <поля_для_выбора>
FROM table_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN table_2
    ON <условие_соединения>
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN table_n
    ON <условие_соединения>]

Внутреннее объединение

Оператору передаются две таблицы, и он возвращает их внутреннее пересечение по какому-либо критерию. Результатом будут записи, которые соответствуют обеим таблицам.

SELECT *
FROM table_left
INNER JOIN table_right
    ON table_left.key = table_right.key;

Внешнее соединение

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

Если внутреннее объединение имеет сходство с бинарным "и", то внешнее — несколько вариаций бинарного "или".

Перекрестное соединение

Он возвращает декартово произведение — собираются все возможные пары из обеих таблиц.
В отличие от остальных режимов, CROSS JOIN не требует указания дополнительной информации.

В разработке Cross Join может использоваться при создании тех же фильтров в интернет-магазине. Например, человек ищет обувь по характеристикам "тип" и "размер" — должны быть выведены все возможные комбинации типа с размером.

SELECT *
FROM table_left
CROSS JOIN table_right;

Самосоединение

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

Чтобы Self Join работал правильно, могут потребоваться псевдонимы таблиц: они помогают называть одну и ту же таблицу разными именами. В результате оператор "воспринимает" переданные сущности как разные.

Чаще всего выделяют четыре режима SQL JOIN: INNER, OUTER, SELF и CROSS.

Возвращает пересечение множеств и все элементы из левой таблицы.

SELECT *
FROM table_left
LEFT JOIN table_right
    ON table_left.key = table_right.key;

Если в table_right нет соответствующей строки, то возвращаются NULL для столбцов из table_right.

Возвращает пересечение множеств и все элементы из правой таблицы.

SELECT *
FROM table_left
RIGHT JOIN table_right
    ON table_left.key = table_right.key;

Если в table_left нет соответствующей строки, то возвращаются NULL для столбцов из table_left.

Возвращает обе таблицы, объединенные в одну.

SELECT *
FROM table_left
FULL JOIN table_right ON table_left.key = table_right.key;

Если в table_left нет соответствующей строки, то возвращаются NULL для столбцов из table_left, и наоборот, если в table_right нет соответствующей строки.

Возвращает данные из левой таблицы, но без пересечений с правой.

SELECT *
FROM table_left
LEFT JOIN table_right
    ON table_left.key = table_right.key
WHERE table_right.key IS NULL;

Этот прием полезен для поиска "несвязанных" строк в левой таблице.

Возвращает данные из правой таблицы, но без пересечений с левой.

SELECT *
FROM table_left
RIGHT JOIN table_right
    ON table_left.key = table_right.key
WHERE table_left.key IS NULL;

Это полезно для поиска "несвязанных" строк в правой таблице.

Возвращает результат из обеих таблиц, кроме пересечений.

Работает как исключающее "или".

SELECT *
FROM table_left
FULL JOIN table_right
    ON table_left.key = table_right.key
WHERE table_left.key IS NULL
    OR table_right.key IS NULL;

Запрос выбирает все "несвязанные" строки из обеих таблиц.

Обычно лучше использовать JOIN, поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя). Так же JOIN имеет заметное преимущество над подзапросами в случае, когда список выбора SELECT содержит столбцы более чем из одной таблицы.

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

Объединяет запросы в одну таблицу.

SELECT поля_таблиц FROM список_таблиц ...
UNION [ALL]
SELECT поля_таблиц FROM список_таблиц ... ;

UNION по умолчанию убирает повторения в результирующей таблице. Для отображения с повторением есть необязательный параметр ALL.

Не путайте операции объединения запросов с операциями объединения таблиц.

Для этого служит оператор JOIN.

Не путайте операции объединения запросов с подзапросами.

Подзапросы выполняются для связанных таблиц.

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

Отличие HAVING от WHERE:

WHERE
Сначала выбираются записи по условию, а затем могут быть сгруппированы, отсортированы и т.д. Это ограничивающее выражение.
Оно выполняется до того, как будет получен результат операции.

HAVING
Сначала группируются записи, а затем выбираются по условию, при этом, в отличие от WHERE, в нём можно использовать значения агрегатных функций

Выражения WHERE используются вместе с операциями SELECT, UPDATE, DELETE, в то время как HAVING только с SELECT и предложением GROUP BY, т.е. использовать WHERE в запросах с агрегатными функциями нельзя, для этого и был введен HAVING.

SELECT поля_таблиц FROM список_таблиц
ORDER BY столбец_1 [ASC | DESC][, столбец_n [ASC | DESC]];

Правило сортировки применяется только к указанным столбцам.
Можно указать направление сортировки.

Иногда требуется узнать информацию не о самих объектах, а об определенных группах, которые они образуют. Для этого используется оператор GROUP BY и агрегатные функции.

SELECT family_member, SUM(unit_price * amount) FROM Payments
GROUP BY family_member;

При использовании GROUP BY все значения NULL считаются равными.
Агрегатные функции применяются для значений, не равных NULL.
Исключением является функция COUNT().

SUM(поле_таблицы) Возвращает сумму значений
AVG(поле_таблицы) Возвращает среднее значение
COUNT(поле_таблицы) Возвращает количество записей
MIN(поле_таблицы) Возвращает минимальное значение
MAX(поле_таблицы) Возвращает максимальное значение

Оператор SQL DISTINCT используется для указания на то, что следует работать только с уникальными значениями столбца.
Может использоваться с агрегатными функциями.

SELECT COUNT(DISTINCT Singer)
AS CountOfSingers
FROM Artists

SELECT поля_выборки
FROM список_таблиц
LIMIT [количество_пропущенных_записей,] количество_записей_для_вывода;

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

Для того, чтобы вывести строки с 3 по 5, нужно использовать такой запрос:

SELECT * FROM Company LIMIT 2, 3;

EXISTS берет подзапрос, как аргумент, и оценивает его как TRUE, если подзапрос возвращает какие-либо записи и FALSE, если нет.

CREATE DATABASE IF NOT EXIST имя_базы_данных;
DROP DATABASE IF EXIST имя_базы_данных;

Обычно предикат EXISTS используется в зависимых (коррелирующих) подзапросах. Этот вид подзапроса имеет внешнюю ссылку, связанную со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оцениваться отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для разных строк основного запроса.

Найти тех производителей портативных компьютеров, которые также производят принтеры:

SELECT DISTINCT maker
FROM Product AS l
WHERE l.type = 'laptop' AND
    NOT EXISTS (SELECT р.maker
                FROM Product r
                WHERE r.type = 'printer' AND r.maker = l.maker);

COUNT(*)
подсчитывает количество записей в таблице, не игнорируя значение NULL, поскольку эта функция оперирует записями, а не столбцами.

COUNT (column)
подсчитывает количество значений в column. При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значение NULL.

При создании таблицы можно установить ограничение на значения которые могут быть в нее внесены.

• PRIMARY KEY
  набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице.
  Для таблицы может быть создано только одно такое ограничение.
  Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей.

  Первичные ключи не могут позволить значений NULL.

• CHECK
  Позволяет установить свое условие, которому должно удовлетворять значение вводимое в таблицу, прежде чем оно будет принято.

• UNIQUE
  Обеспечивает отсутствие дубликатов в столбце или наборе столбцов.

• FOREIGN KEY
  Защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY.

CREATE TABLE < table name >
        (< column name > < column constraint >,
        < column name > < data type > < column constraint > ...
        < table constraint > ( < column name >
        [, < column name > ])... );

[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец1, столбец2, ... столбецN)
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)
[ON DELETE действие]
[ON UPDATE действие]

Простой ключ состоит из одного атрибута (поля).

Составной ключ (composite) - из двух и более.

По умолчанию ограничение PRIMARY создает кластерный индекс на столбце, а UNIQUE - некластерный*.
Другим отличием является то, что PRIMARY не разрешает NULL записей, в то время как UNIQUE разрешает одну (а в некоторых СУБД несколько) NULL запись.

* Кластерный индекс - это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.

Может, если на данный столбец не наложено ограничение NOT NULL.

Суррогатный ключ - понятие теории реляционных баз данных. Это дополнительное служебное поле, добавленное к уже имеющимся информационным полям таблицы, единственное предназначение которого — служить первичным ключом.

Индекс (index) - объект базы данных, создаваемый с целью повышения производительности выборки данных.

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

Преимущества

• ускорение поиска и сортировки по определенному полю или набору полей.
• обеспечение уникальности данных.

Недостатки

• требование дополнительного места на диске и в оперативной памяти и чем больше/длиннее ключ, тем больше размер индекса.
• замедление операций вставки, обновления и удаления записей, поскольку при этом приходится обновлять сами индексы.

Индексы предпочтительней для:

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

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

• Поля, которое объявлено первичным ключом (primary key).
• Поля, в котором данные выбираются из некоторого диапазона.

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

Использование индексов нецелесообразно для:

• Полей, которые редко используются в запросах;
• Полей, которые содержат всего два или три значения, например: мужской, женский пол или значения "да", "нет".

  Так называемые низкоселективные поля.

(раскрыть все)

• По порядку сортировки:

  • упорядоченные - индексы, в которых элементы упорядочены

• По источнику данных:

  • индексы по представлению (view)
  • индексы по выражениям

• По воздействию на источник данных:

  • кластерный индекс - при определении в наборе данных физическое расположение данных перестраивается в соответствии со структурой индекса.

    Логическая структура набора данных в этом случае представляет собой скорее словарь, чем индекс. Данные в словаре физически упорядочены, например по алфавиту.
    Кластерные индексы могут дать существенное увеличение производительности поиска данных даже по сравнению с обычными индексами. Увеличение производительности особенно заметно при работе с последовательными данными.

  • некластерный индекс - наиболее типичные представители семейства индексов. В отличие от кластерных, они не перестраивают физическую структуру набора данных, а лишь организуют ссылки на соответствующие записи.

    Для идентификации нужной записи в наборе данных некластерный индекс организует специальные указатели, включающие в себя:
    - информацию об идентификационном номере файла, в котором хранится запись;
    - идентификационный номер страницы соответствующих данных;
    - номер искомой записи на соответствующей странице;
    - содержимое столбца.

• По структуре:

  • B-деревья

    - Стандартная структура данных для индексов, которая поддерживает отсортированное хранение данных и обеспечивает быструю вставку, удаление и поиск.
    - B-дерево сбалансировано, что означает, что все листья находятся на одном уровне, и операции поддерживаются за логарифмическое время.

  • B+-деревья

    - Усовершенствованная версия B-дерева.
    - Все значения данных хранятся только в листьях, тогда как внутренние узлы содержат только индексы и используются для навигации.
    - Это позволяет быстрее выполнять последовательное считывание данных.

  • B*-деревья

    - Дальнейшее развитие концепции B+-деревьев, которые используют более плотное заполнение узлов.
    - Более высокая плотность заполнения узлов снижает высоту дерева, что может привести к увеличению эффективности операций поиска за счет уменьшения количества операций доступа к диску.

  • Хэш-индексы

    - Используют хеш-функции для организации данных. Хеширование позволяет быстрее находить записи по точному совпадению ключа.
    - Подходят для операций точного поиска, но неэффективны для последовательного доступа или поиска диапазонов, поскольку данные в хеш-таблице не упорядочены.

• По количественному составу:

  • простой индекс (индекс с одним ключом) - строится по одному полю.

  • составной (многоключевой, композитный) индекс - строится по нескольким полям при этом важен порядок их следования.

  • индекс с включенными столбцами - некластеризованный индекс, дополнительно содержащий кроме ключевых столбцов еще и неключевые.

  • главный индекс (индекс по первичному ключу) - это тот индексный ключ, под управлением которого в данный момент находится набор данных.

    Набор данных не может быть отсортирован по нескольким индексным ключам одновременно.

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

• По характеристике содержимого:

  • уникальный индекс состоит из множества уникальных значений поля.

  • плотный индекс (NoSQL) - индекс, при котором, каждом документе в индексируемой коллекции соответствует запись в индексе, даже если в документе нет индексируемого поля.

  • разреженный индекс (NoSQL) - тот, в котором представлены только те документы, для которых индексируемый ключ имеет какое-то определённое значение (существует).

  • пространственный индекс - оптимизирован для описания географического местоположения. Представляет из себя многоключевой индекс состоящий из широты и долготы.

  • составной пространственный индекс - индекс, включающий в себя кроме широты и долготы ещё какие-либо мета-данные (например теги). Но географические координаты должны стоять на первом месте.

  • полнотекстовый (инвертированный) индекс - словарь, в котором перечислены все слова и указано, в каких местах они встречаются. При наличии такого индекса достаточно осуществить поиск нужных слов в нём и тогда сразу же будет получен список документов, в которых они встречаются.

  • хэш-индекс - предполагает хранение не самих значений, а их хэшей, благодаря чему уменьшается размер (а, соответственно, и увеличивается скорость их обработки) индексов из больших полей.

    Т.о., при запросах с использованием хэш-индексов, сравниваться будут не искомое со значения поля, а хэш от искомого значения с хэшами полей. Из-за нелинейности хэш-функций данный индекс нельзя сортировать по значению, что приводит к невозможности использования в сравнениях больше/меньше и "is null".
    Кроме того, так как хэши не уникальны, то для совпадающих хэшей применяются методы разрешения коллизий.

  • битовый индекс (bitmap index) - метод битовых индексов заключается в создании отдельных битовых карт (последовательностей 0 и 1) для каждого возможного значения столбца, где каждому биту соответствует запись с индексируемым значением, а его значение равное 1 означает, что запись, соответствующая позиции бита содержит индексируемое значение для данного столбца или свойства.

  • обратный индекс (reverse index) - B-tree индекс, но с реверсированным ключом, используемый в основном для монотонно возрастающих значений (например, автоинкрементный идентификатор) в OLTP системах с целью снятия конкуренции за последний листовой блок индекса, т.к. благодаря переворачиванию значения две соседние записи индекса попадают в разные блоки индекса.
    Он не может использоваться для диапазонного поиска.

  • функциональный индекс, индекс по вычисляемому полю (function-based index) - индекс, ключи которого хранят результат пользовательских функций. Функциональные индексы часто строятся для полей, значения которых проходят предварительную обработку перед сравнением в команде SQL.

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

  • первичный индекс - уникальный индекс по полю первичного ключа.

  • вторичный индекс - индекс по другим полям (кроме поля первичного ключа).

  • XML-индекс - вырезанное материализованное представление больших двоичных XML-объектов (BLOB) в столбце с типом данных xml.

• По механизму обновления:

  • полностью перестраиваемый - при добавлении элемента заново перестраивается весь индекс.

  • пополняемый (балансируемый) - при добавлении элементов индекс перестраивается частично (например, одна из ветви) и периодически балансируется.

• По покрытию индексируемого содержимого:

  • полностью покрывающий (полный) индекс - покрывает всё содержимое индексируемого объекта.

  • частичный индекс (partial index) - это индекс, построенный на части набора данных, удовлетворяющей определенному условию самого индекса.
    Данный индекс создан для уменьшения размера индекса.

  • инкрементный (delta) индекс - индексируется малая часть данных(дельта), как правило, по истечении определенного времени.
    Используется при интенсивной записи.

    Например, полный индекс перестраивается раз в сутки, а дельта-индекс строится каждый час. По сути это частичный индекс по временной метке.

  • индекс реального времени (real-time index) - особый вид инкрементного индекса, характеризующийся высокой скоростью построения.
    Предназначен для часто меняющихся данных.

• Индексы в кластерных системах:

  • глобальный индекс - индекс по всему содержимому всех сегментов БД (shard).

  • сегментный индекс глобальный индекс по полю-сегментируемому ключу (shard key).
    Используется для быстрого определения сегмента, на котором хранятся данные в процессе маршрутизации запроса в кластере БД.

  • локальный индекс - индекс по содержимому только одного сегмента БД.

Некластерные индексы - данные физически расположены в произвольном порядке, но логически упорядочены согласно индексу. Такой тип индексов подходит для часто изменяемого набора данных.

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

Для одного набора данных может быть создан только один кластерный индекс.

Примерное правило, которым можно руководствоваться при создании индекса - если объем информации (в байтах) НЕ удовлетворяющей условию выборки меньше, чем размер индекса (в байтах) по данному условию выборки, то в общем случае оптимизация приведет к замедлению выборки.

Полное сканирование производится многоблочным чтением. Сканирование по индексу - одноблочным. Также (и очень важно), при доступе по индексу сначала идет сканирование самого индекса, а затем чтение блоков из набора данных.
Число блоков, которые надо при этом прочитать из набора зависит от фактора кластеризации. Если суммарная стоимость всех необходимых одноблочных чтений больше стоимости полного сканирования многоблочным чтением, то полное сканирование выгоднее и оно выбирается оптимизатором.
Т.о., полное сканирование выбирается при слабой селективности предикатов запроса и/или слабой кластеризации данных, либо в случае очень маленьких наборов данных.

Индекс можно создать либо с помощью выражения CREATE INDEX:

Либо указав ограничение целостности в виде уникального UNIQUE или первичного PRIMARY ключа в операторе создания таблицы CREATE TABLE.

Реплицирование - это процесс создания и поддержания копий данных на нескольких серверах или узлах в системе.

Основная цель репликации - обеспечить отказоустойчивость, повысить доступность данных и улучшить производительность системы.

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

Существует несколько моделей репликации:

• Синхронная репликация:
  при внесении изменений в данные на одном узле эти изменения сразу же применяются ко всем копиям данных на других узлах.
  Это гарантирует, что все копии данных остаются консистентными, но может увеличить задержки при операциях записи из-за необходимости дожидаться подтверждения от всех узлов.

• Асинхронная репликация:
  изменения на основном узле передаются на реплики с задержкой.
  Это может улучшить производительность, так как узел не должен ждать подтверждения от реплик при записи, но есть риск того, что данные на репликах временно могут быть неактуальными.

Репликация помогает обеспечить:

• Отказоустойчивость:
  если один сервер выходит из строя, данные могут быть доступны с другой реплики.

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

• Географическое распределение данных:
  хранение реплик данных в разных географических локациях позволяет пользователям получать доступ к данным с меньшими задержками.

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

Нормализация - это процесс преобразования отношений базы данных к виду, отвечающему нормальным формам (пошаговый, обратимый процесс замены исходной схемы другой схемой, в которой наборы данных имеют более простую и логичную структуру).

Нормализация – это и есть здравый смысл в проектировании БД.

Цель нормализации: исключить избыточное дублирование данных, которое является причиной аномалий, возникших при добавлении, редактировании и удалении кортежей (строк таблицы).

Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения (может быть выражено одним, как правило, действительным числом). Не должно быть повторений строк в таблице.

Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа(ПК).
Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость.

Короче, прослеживается 2 зависимости из которых можно выделить 2-ю таблицу.

Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа.

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

Частная форма третьей нормальной формы

Определение 3НФ не совсем подходит для следующих отношений:

1. Отношение имеет два или более потенциальных ключа;
2. Два и более потенциальных ключа являются составными;
3. Они пересекаются, т.е. имеют хотя бы один общий атрибут.

Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ. Отношение находится в НФБК, когда каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта.

Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день:

Тариф имеет уникальное название и зависит от выбранной стоянки и наличии льгот, в частности:

• Бережливый - стоянка 1 для льготников.

• Стандарт - стоянка 1 для не льготников.

• Премиум-А стоянка 2 для льготников.

• Премиум-B стоянка 2 для не льготников.

Таким образом, возможны следующие составные первичные ключи:

[Номер стоянки, Время начала],
[Номер стоянки, Время окончания],
[Тариф, Время начала],
[Тариф, Время окончания].

Отношение находится в 3НФ.
Требования второй нормальной формы выполняются, так как все атрибуты входят в какой-то из потенциальных ключей, а неключевых атрибутов в отношении нет.
Также нет и транзитивных зависимостей, что соответствует требованиям третьей нормальной формы.
Тем не менее, существует функциональная зависимость Тариф->Номер стоянки, в которой левая часть (детерминант) не является потенциальным ключом отношения, то есть отношение не находится в нормальной форме Бойса — Кодда.

Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф "Бережливый" к бронированию второй стоянки, хотя он может относиться только к первой стоянки.

Можно улучшить структуру с помощью декомпозиции отношения на два и добавления атрибута "Имеет льготы", получив отношения, удовлетворяющие НФБК:

Тарифы

Бронирование

Отношение находится в 4НФ, если оно находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей.

В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С.

Предположим, что рестораны производят разные виды пиццы, а службы доставки ресторанов работают только в определенных районах города. Составной первичный ключ соответствующей переменной отношения включает три атрибута: [Ресторан, Вид пиццы, Район доставки].

Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость:
Ресторан->Вид пиццы
Ресторан->Район доставки

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

Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях.
В данном примере следует выполнить декомпозицию на [Ресторан, Вид пиццы] и [Ресторан, Район доставки].

Однако, если к исходной переменной отношения добавить атрибут, функционально зависящий от потенциального ключа, например цену с учётом стоимости доставки ([Ресторан, Вид пиццы, Район доставки]->Цена), то полученное отношение будет находиться в 4НФ и его уже нельзя подвергнуть декомпозиции без потерь.

Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами.
Если "Атрибут_1" зависит от "Атрибута_2", а "Атрибут_2" в свою очередь зависит от "Атрибута_3", а "Атрибут_3" зависит от "Атрибута_1", то все три атрибута обязательно входят в один кортеж.

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

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

Ограничение домена - ограничение, предписывающее использовать для определённого атрибута значения только из некоторого заданного домена.
Ограничение по своей сути является заданием перечня (или логического эквивалента перечня) допустимых значений типа и объявлением о том, что указанный атрибут имеет данный тип.

Ограничение ключа - ограничение, утверждающее, что некоторый атрибут или комбинация атрибутов является потенциальным ключом.

Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ.

Переменная отношения находится в 6НФ тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения.

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

Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.

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