databases-101

Добро пожаловать в «Databases 101» — это заметки, охватывающие ключевые концепции баз данных от начального до среднего уровня. Материалы подойдут как новичкам, стремящимся получить прочную базу, так и более опытным пользователям, желающим освежить свои знания. Заметки охватывают такие темы, как SQL-запросы, оптимизация и производительность, транзакции и согласованность данных, и многое другое. Некоторые темы сгруппированы логически для лучшего понимания, а план, представленный ниже, поможет легко ориентироваться в представленных материалах.

План

Введение в базы данных

Базы данных и СУБД

Подробнее

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

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

Основные функции СУБД:

Хранение данных — организация данных на диске и в оперативной памяти.
Управление доступом — разграничение прав пользователей (см. Управление доступом).
Обработка запросов — приём, оптимизация и выполнение запросов (как правило, на языке SQL).
Обеспечение целостности — соблюдение ограничений (ключи, типы данных, проверки).
Управление транзакциями — гарантия свойств ACID при одновременной работе многих пользователей.
Резервное копирование и восстановление — защита от потери данных при сбоях.

Типы СУБД

По модели данных СУБД принято делить на две большие группы:

Реляционные (SQL) — данные хранятся в виде таблиц (отношений), связанных между собой ключами, а доступ осуществляется через язык SQL. Примеры: PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, SQLite.
Нереляционные (NoSQL) — данные хранятся в моделях, отличных от табличной. Выделяют несколько подтипов:
- документоориентированные (MongoDB, CouchDB) — данные в виде документов (JSON/BSON);
- ключ-значение (Redis, DynamoDB) — простое соответствие «ключ → значение»;
- колоночные (Cassandra, HBase) — данные хранятся по столбцам, что удобно для аналитики;
- графовые (Neo4j) — данные в виде узлов и связей, что удобно для сильно связанных данных.

Критерий	Реляционные (SQL)	Нереляционные (NoSQL)
Структура данных	Таблицы со строгой схемой	Гибкая схема (документы, пары, графы и т.д.)
Масштабирование	Преимущественно вертикальное	Преимущественно горизонтальное
Согласованность	Строгая (ACID)	Часто согласованность «в конечном счёте» (BASE)
Язык запросов	SQL (стандартизирован)	У каждой СУБД свой API или язык
Типичные сценарии	Финансы, учёт, транзакционные системы	Большие объёмы, кэш, аналитика, гибкие данные

Выбор между SQL и NoSQL зависит от задачи: если важны строгая согласованность и сложные связи между данными — выбирают реляционные СУБД; если важны гибкость схемы и горизонтальное масштабирование под большие объёмы — нереляционные.

NoSQL

Подробнее

NoSQL (Not Only SQL) — это общее название для нереляционных баз данных, которые отказываются от строгой табличной модели и языка SQL ради гибкости схемы и горизонтального масштабирования. NoSQL-решения появились как ответ на потребности веб-сервисов с огромными объёмами данных и высокой нагрузкой, где классические реляционные СУБД масштабировать сложно и дорого.

Основные типы NoSQL-баз:

Документоориентированные (Document) — хранят данные в виде документов (JSON, BSON), где каждый документ может иметь свою структуру. Удобны для гибких, часто меняющихся данных. Примеры: MongoDB, CouchDB.
Ключ-значение (Key-Value) — простейшая модель: уникальный ключ → произвольное значение. Очень быстрые, часто используются для кэша и сессий. Примеры: Redis, DynamoDB, Memcached.
Колоночные (Wide-Column) — данные хранятся семействами столбцов, что эффективно для разреженных данных и аналитики на больших объёмах. Примеры: Cassandra, HBase.
Графовые (Graph) — данные представлены узлами и связями между ними; идеальны для сильно связанных данных (соцсети, рекомендации, графы знаний). Примеры: Neo4j, ArangoDB.

Сильные стороны NoSQL:

Гибкая схема — структуру данных можно менять без миграций, разные записи могут иметь разный набор полей.
Горизонтальное масштабирование — многие NoSQL-СУБД изначально спроектированы для работы на кластере серверов (см. Шардинг).
Высокая производительность на простых операциях чтения и записи при больших объёмах.

Слабые стороны:

Слабая согласованность — многие NoSQL-системы относятся к классу AP по теореме CAP и обеспечивают лишь «согласованность в конечном счёте» (BASE), а не строгие гарантии ACID.
Нет стандартного языка — у каждой СУБД свой API и модель запросов.
Ограниченная поддержка сложных связей — то, что в реляционной модели делается через JOIN, часто приходится решать на стороне приложения или дублированием данных.

Когда выбирать NoSQL? Когда данные плохо ложатся в строгую табличную схему, нужны гибкость и горизонтальное масштабирование под большие объёмы, а строгая согласованность не критична. Для систем с важной целостностью и сложными связями (финансы, учёт) по-прежнему предпочтительны реляционные СУБД. Нередко их используют вместе — этот подход называют полиглот-персистентностью.

ETL-процессы

Подробнее

ETL (Extract, Transform, Load — извлечение, преобразование, загрузка) — это процесс переноса данных из одного или нескольких источников в целевое хранилище (чаще всего в хранилище данных, Data Warehouse). ETL используется, чтобы собрать разрозненные данные, привести их к единому виду и подготовить для анализа и построения отчётности.

Процесс состоит из трёх последовательных этапов:

Extract (Извлечение) — данные считываются из источников: реляционных БД, файлов (CSV, JSON, XML), API, очередей сообщений, логов и т.д. Источники могут быть разнородными и иметь разную структуру.
Transform (Преобразование) — извлечённые данные очищаются и приводятся к нужному формату. Здесь выполняются: очистка от ошибок и дубликатов, приведение типов и единиц измерения, объединение данных из разных источников, агрегация, расчёт производных показателей, фильтрация. Именно на этом этапе обеспечивается качество данных.
Load (Загрузка) — подготовленные данные записываются в целевое хранилище. Загрузка может быть полной (перезапись всех данных) или инкрементальной (догрузка только новых и изменённых записей).

ETL и ELT

В классическом ETL данные преобразуются до загрузки — на отдельном сервере или в специальном движке. В подходе ELT (Extract, Load, Transform) сырые данные сначала загружаются в мощное хранилище, а преобразования выполняются уже внутри него средствами самой СУБД. ELT стал популярен с развитием облачных аналитических хранилищ (Snowflake, BigQuery, ClickHouse), способных эффективно обрабатывать большие объёмы данных.

Критерий	ETL	ELT
Где идёт преобразование	На промежуточном ETL-сервере	Внутри целевого хранилища
Скорость загрузки	Ниже (сначала преобразование)	Выше (сначала загрузка сырых данных)
Гибкость	Схема задаётся заранее	Можно преобразовывать данные по мере надобности
Типичное применение	Классические DWH	Облачные хранилища, большие объёмы данных

Результат ETL/ELT-процессов обычно укладывается в специализированные модели хранилища — например, в схемы «звезда» и «снежинка». Для автоматизации ETL применяют инструменты-оркестраторы: Apache Airflow, Apache NiFi, dbt и др.

ER-диаграмма

Подробнее

Что такое ER-диаграмма?

Схема «сущность-связь» (также ERD или ER-диаграмма) — это разновидность блок-схемы, где показано, как разные «сущности» (люди, объекты, концепции и так далее) связаны между собой внутри системы. ER-диаграммы чаще всего применяются для проектирования и отладки реляционных баз данных в сфере образования, исследования и разработки программного обеспечения и информационных систем для бизнеса. ER-диаграммы (или ER-модели) полагаются на стандартный набор символов, включая прямоугольники, ромбы, овалы и соединительные линии, для отображения сущностей, их атрибутов и связей. Эти диаграммы устроены по тому же принципу, что и грамматические структуры: сущности выполняют роль существительных, а связи — глаголов.

ER-диаграммы — «родственники» схем структуры данных (DSD), где вместо связей между самими сущностями отображаются отношения между элементами внутри них.

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

В ER-моделях и моделях данных обычно выделяют до трех уровней детализации:

Концептуальная модель данных — схема наивысшего уровня с минимальным количеством подробностей. Достоинство этого подхода заключается в возможности отобразить общую структуру модели и всю архитектуру системы. Менее масштабные системы могут обойтись и без этой модели. В этом случае можно сразу переходить к логической модели.
Логическая модель данных содержит более подробную информацию, нежели концептуальная модель. На этом уровне определяются более подробные операционные и транзакционные сущности. Логическая модель не зависит от технологии, в которой она будет применяться.
Физическая модель данных: на основе каждой логической модели данных можно составить одну или две физических модели. В последних должно присутствовать достаточно технических подробностей для составления и внедрения самой базы данных.

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

Только реляционные данные. Следует четко понимать, что цель ER-диаграмм — показать связи и отношения между элементами, поэтому они отображают только реляционную структуру.

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

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

Символы и способы нотации ERD

Концептуальные модели данных дают общее представление о том, что должно входить в состав модели. Концептуальные ER-диаграммы можно брать за основу логических моделей данных. Их также можно использовать для создания отношений общности между разными ER-моделями, положив их в основу интеграции. Все приведенные ниже символы можно найти в библиотеках «Сущность-связь» для UML» и «Фигуры по модели «сущность-связь» на Lucidchart.

Символы ERD-сущностей

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

Символы ERD-связей

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

Символы ERD-атрибутов

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

Символы физических ER-диаграмм

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

Ключевые составляющие таблиц «сущность-связь»:

Поля

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

Ключи

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

Первичные ключи

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

Внешние ключи

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

К примеру, займ на каждый отдельный автомобиль может быть выдан только одним банком, поэтому в качестве внешнего ключа FinancedBy («кем выдан займ») в таблице Car (автомобиль») использован основной ключ BankId («идентификатор банка»).При этом идентификатор BankId может служить внешним ключом сразу для нескольких автомобилей.

Типы

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

Нотация ER-диаграмм

Хотя нотация Crow's Foot («вороньи лапки») часто признается наиболее интуитивной, некоторые пользователи отдают предпочтение нотации Бахмана, OMT, IDEF или UML. Тем не менее, «вороньи лапки» действительно предлагают наглядный интуитивный формат — вот почему мы выбрали их для нотации ER-диаграмм в Lucidchart.

Кардинальность и ординальность

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

Кардинальность и ординальность отображаются на соединительных линиях согласно выбранному формату нотации.

(Бывают следующие ситуации — один к одному, один ко многим, многие ко многим и сам по себе. С «один к одному» и «сам по себе» всё просто — одна запись в одной таблице соотносится с одной записью в другой или таблица сама по себе и ни к чему не относится. «Один ко многим» — это случай, когда запись одной таблицы соотносится с более чем одной записью другой таблицы; реализуется внешним ключом на стороне «многие». «Многие ко многим» — когда записи обеих таблиц могут соотноситься с множеством записей друг друга; это (если по правильному) решается промежуточной таблицей)

Больше о том, как создать ER-диаграмму можно найти здесь: Что такое ER-диаграмма и как ее создать?

Ключи

Подробнее

Первичный ключ против внешнего ключа

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

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

Основной ключ (Primary Key)

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

Наличие первичного ключа:

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

Внешний ключ (Foreign Key)

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

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

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

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

Сравнение первичных ключей и внешних ключей

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

Цель:
Единственная цель первичного ключа — уникальная идентификация каждой записи таблицы. Напротив, внешний ключ ссылается на первичный ключ другой таблицы, устанавливая связь и позволяя извлекать данные из разных таблиц. Это позволяет вам подключить сопутствующую информацию и увидеть единый обзор в вашей базе данных.
Уникальность:
Первичный ключ должен содержать уникальное значение для каждой записи в таблице. Дубликатов быть не может – каждой записи нужен свой отдельный идентификатор.
Уникальность внутри таблицы не является обязательной для внешнего ключа. Но он должен ссылаться на уникальное значение в первичном ключе таблицы, на которую он указывает. Он может подключаться только к одной четко определенной точке на другой стороне.
Обнуляемость:
Нулевые значения обычно не допускаются в первичном ключе. Каждой записи необходимо определенное значение первичного ключа, чтобы гарантировать отсутствие пропущенных идентификаторов и предотвратить путаницу при ссылке на определенные точки данных.
В зависимости от связи между таблицами внешний ключ допускает нулевые значения. Например, заказ клиента может иметь внешний ключ, ссылающийся на «адрес доставки», но поле адреса будет пустым, если заказ еще не отправлен.
Обеспечение целостности данных:
По своей природе первичный ключ обеспечивает целостность данных в пределах своей таблицы. Уникальность гарантирует отсутствие повторяющихся записей, а отсутствие нулевых значений предотвращает отсутствие идентификаторов.
Внешние ключи жизненно важны для поддержания целостности данных между таблицами. Ссылка на действительный первичный ключ в другой таблице помогает предотвратить появление потерянных записей (записей со значениями внешнего ключа, которые не соответствуют никаким существующим данным в ссылочной таблице). Это создает согласованность и предотвращает нарушение связей внутри вашей базы данных.
Возможность обновления и удаления:
Из-за своей роли уникального идентификатора первичный ключ обычно предназначен для умеренного обновления. Изменение значения первичного ключа может нарушить связи с другими таблицами.
Пользователи могут обновлять значения внешнего ключа, если новое значение остается действительным первичным ключом в указанной таблице. Однако удаление записи в ссылочной таблице может повлиять на соответствующие значения внешнего ключа других таблиц, в зависимости от выбранных ограничений ссылочной целостности.

Первичный ключ и внешний ключ: 9 важных отличий

Основной ключ	Внешний ключ
Столбец или набор столбцов, идентифицирующий каждую строку таблицы.	Столбец или несколько столбцов в одной таблице, который ссылается на первичный ключ в другой таблице.
Должен содержать уникальные значения; дубликаты не допускаются.	Может содержать повторяющиеся значения; обычно относится к значениям первичного ключа в другой таблице.
В каждой таблице имеется только один первичный ключ.	В зависимости от отношений в таблице может существовать несколько внешних ключей.
Обеспечивает целостность данных и целостность объекта (каждая строка однозначно идентифицируется).	Устанавливает и поддерживает ссылочную целостность между связанными таблицами.
По умолчанию автоматически индексируется (в большинстве СУБД).	Может индексироваться автоматически, но это не обязательно; индекс рекомендуется для производительности.
Обычно это числовой или уникальный идентификатор.	Соответствует типу данных первичного ключа, на который он ссылается.
Ограничение первичного ключа обеспечивает уникальность и не является нулевым.	Ограничение внешнего ключа обеспечивает ссылочную целостность (значения должны существовать в указанной таблице).
Используется для однозначной идентификации строк при объединении таблиц.	Используется для установления связей и обеспечения соблюдения ограничений во время соединений.
Изменения ограничены, если первичный ключ где-то упоминается как внешний ключ (в зависимости от параметров каскада).	Значения можно обновлять или удалять, обычно с помощью каскадных опций для поддержания ссылочной целостности.

Типы ключей в модели реляционной базы данных (СУБД)

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

Первичный ключ
Первичный ключ в СУБД — это один столбец или комбинация столбцов в таблице, которые однозначно идентифицируют каждую запись в этой таблице. Таблица может иметь только один первичный ключ, который должен иметь уникальные значения без повторений во всех строках.
Суперключ
Суперключ — это один ключ или группа ключей, которые могут однозначно идентифицировать каждую строку в таблице. Это означает, что любая комбинация столбцов, которая однозначно определяет все остальные столбцы в таблице, квалифицируется как суперключ. Супер ключ включает все возможные ключи, которые могут однозначно идентифицировать строки. Из этих суперключей выбирается первичный ключ, позволяющий однозначно идентифицировать каждую строку таблицы.
Ключ кандидата
Ключи-кандидаты однозначно идентифицируют строки таблицы, действуя во многом как первичные ключи с теми же свойствами. Таблица выбирает свой первичный ключ из числа возможных ключей. Хотя потенциальных ключей может быть несколько, ни один из них не может быть пустым, что гарантирует, что каждый из них несет уникальную информацию и ценность. Группа атрибутов также может вместе выступать в качестве потенциальных ключей.
Альтернативный ключ
Таблица может иметь несколько кандидатов на первичный ключ, но выбирает только одного. Ключи, не выбранные в качестве первичного ключа, называются альтернативными ключами.
Внешний ключ
Внешние ключи связывают две таблицы, требуя, чтобы каждое значение в одном столбце или столбце соответствовало первичному ключу в другой ссылочной таблице. Они обеспечивают связи между связанной, но не идентичной информацией.
Составной ключ
Составной ключ объединяет два или более атрибута для уникальной идентификации каждой строки таблицы. Хотя эти атрибуты могут и не быть уникальными по отдельности, их сочетание гарантирует уникальность. Иногда его также называют сложным или композитным ключом.
Уникальный ключ
Уникальный ключ, состоящий из одного или нескольких столбцов, уникально идентифицирует каждую строку в таблице, поэтому все значения ключа должны быть уникальными. В отличие от первичного ключа, уникальный ключ может включать одно нулевое значение, тогда как первичный ключ не допускает нулевых значений.

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

Заключение

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

Больше о ключах на практических примерах PostgreSQL можно найти здесь: https://habr.com/ru/articles/787374/

Нормальные формы

Подробнее

Нормализация баз данных

В реляционных базах данных есть такое понятия, как «Нормализация».

Нормализация – это процесс удаления избыточных данных.

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

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

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

Зачем нормализовать базу данных?

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

Устранения аномалий
Повышения производительности
Повышения удобства управления данными

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

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

Пример. Следующая таблица хранит информацию о предметах мебели (наименование предмета и материал, из которого он изготовлен).

Идентификатор предмета	Наименование предмета	Материал
1	Стул	Металл
2	Стол	Массив дерева
3	Кровать	ЛДСП
4	Шкаф	Массив дерева
5	Комод	ЛДСП

Допустим, у нас возникла необходимость изменить название материала, вместо «Массив дерева» нужно написать «Натуральное дерево». Чтобы это сделать нам необходимо внести изменения сразу в несколько строк, так как предметов, изготовленных из массива дерева, несколько.

По каким-то причинам мы внесли изменения только в одну строку. В итоге в нашей таблице будет и «Массив дерева», и «Натуральное дерево».

Идентификатор предмета	Наименование предмета	Материал
1	Стул	Металл
2	Стол	Натуральное дерево
3	Кровать	ЛДСП
4	Шкаф	Массив дерева
5	Комод	ЛДСП

Также мы можем внести еще какое-то новое значение при добавлении новых записей, например, просто «Дерево».

В этом случае в нашей таблице в скором времени будет и «Массив дерева», и «Натуральное дерево», и просто «Дерево».

Идентификатор предмета	Наименование предмета	Материал
1	Стул	Металл
2	Стол	Натуральное дерево
3	Кровать	ЛДСП
4	Шкаф	Массив дерева
5	Комод	ЛДСП
6	Тумба	Дерево

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

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

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

Предметы мебели.

Идентификатор предмета	Наименование предмета	Идентификатор материала
1	Стул	2
2	Стол	1
3	Кровать	3
4	Шкаф	1
5	Комод	3

Материалы, из которых изготовлены предметы мебели.

Идентификатор материала	Материал
1	Массив дерева
2	Металл
3	ЛДСП

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

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

Нормальные формы базы данных

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

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

Нормальная форма базы данных – это набор правил и критериев, которым должна отвечать база данных.

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

Отсюда можно сделать вывод, что чем выше нормальная форма, тем меньше аномалий в базе будет.

Процесс нормализации – это последовательный процесс приведения базы данных к эталонному виду, т.е. переход от одной нормальной формы к следующей.

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

Существует 5 основных нормальных форм базы данных:

Первая нормальная форма (1NF)
Вторая нормальная форма (2NF)
Третья нормальная форма (3NF)
Четвертая нормальная форма (4NF)
Пятая нормальная форма (5NF)

Однако выделяют еще дополнительные нормальные формы:

Ненормализованная форма или нулевая нормальная форма (UNF)
Нормальная форма Бойса-Кодда (BCNF)
Доменно-ключевая нормальная форма (DKNF)
Шестая нормальная форма (6NF)

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

Ненормализованная форма или нулевая нормальная форма (UNF)
Первая нормальная форма (1NF)
Вторая нормальная форма (2NF)
Третья нормальная форма (3NF)
Нормальная форма Бойса-Кодда (BCNF)
Четвертая нормальная форма (4NF)
Пятая нормальная форма (5NF)
Доменно-ключевая нормальная форма (DKNF)
Шестая нормальная форма (6NF)

База данных считается нормализованной, если она находится как минимум в третьей нормальной форме (3NF).

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

Ситуации, при которых требуется нормализовать базу данных до четвертой нормальной формы (4NF), в реальном мире встречаются достаточно редко.

Если говорить о всех последующих нормальных формах (5NF, DKNF, 6NF), то в реальной жизни трудно даже представить ситуации, при которых потребуется нормализовать базу данных до этих форм.

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

1. Ненормализованная форма или нулевая нормальная форма (UNF) базы данных

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

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

Пример: До:

№	A	B
1	Иван	Иванов
2	Сергей	Сергеев

После:

first_name	last_name
Иван	Иванов
Сергей	Сергеев

2. Первая нормальная форма (1NF) базы данных

Первая нормальная форма (1NF) требует, чтобы таблица соответствовала реляционной модели данных:

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

Пример: До (не в 1NF):

Сотрудник	Контакт
Иванов И.И.	123-456-789, 987-654-321
Сергеев С.С.	Раб. 555-666-777, Дом. 777-888-999

После (в 1NF):

Сотрудник	Телефон	Тип телефона
Иванов И.И.	123-456-789
Иванов И.И.	987-654-321
Сергеев С.С.	555-666-777	Рабочий
Сергеев С.С.	777-888-999	Домашний

Главное правило 1NF:

Строки хранят данные.
Столбцы содержат структурную информацию.
Ячейки содержат одно атомарное значение.

Таблицы, созданные по реляционной теории, уже соответствуют 1NF.

3. Вторая нормальная форма (2NF) базы данных

После приведения таблиц к первой нормальной форме (1NF), следующий этап нормализации – достижение второй нормальной формы (2NF). Таблицы во второй нормальной форме должны соответствовать следующим требованиям:

Требования 2NF:

Первая нормальная форма (1NF): Таблицы уже находятся в первой нормальной форме.
Ключ: Таблицы имеют первичный ключ (простой или составной), который уникально идентифицирует каждую строку.
Полная зависимость от ключа: Все неключевые столбцы зависят от полного ключа (если он составной).

Если неключевые столбцы зависят только от части составного ключа, то таблица не соответствует второй нормальной форме.

Правило 2NF:

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

Пример приведения таблицы к 2NF с простым ключом

Рассмотрим таблицу сотрудников:

ФИО	Должность	Подразделение	Описание подразделения
Иванов И.И.	Программист	Отдел разработки	Разработка и сопровождение приложений
Сергеев С.С.	Бухгалтер	Бухгалтерия	Ведение бухгалтерского учета
John Smith	Продавец	Отдел реализации	Организация сбыта продукции

Эта таблица находится в 1NF, но у нее нет уникального идентификатора. Чтобы привести ее ко 2NF, добавляем столбец с уникальным табельным номером:

Табельный номер	ФИО	Должность	Подразделение	Описание подразделения
1	Иванов И.И.	Программист	Отдел разработки	Разработка и сопровождение приложений
2	Сергеев С.С.	Бухгалтер	Бухгалтерия	Ведение бухгалтерского учета
3	John Smith	Продавец	Отдел реализации	Организация сбыта продукции

Поскольку ключ здесь простой, таблица автоматически соответствует требованиям 2NF.

Пример приведения таблицы к 2NF с составным ключом

Допустим, нам нужно хранить информацию о проектах и участниках.

Исходная таблица (1NF):

Название проекта	Участник	Должность	Срок проекта (мес.)
Внедрение приложения	Иванов И.И.	Программист	8
Внедрение приложения	Сергеев С.С.	Бухгалтер	8
Внедрение приложения	John Smith	Менеджер	8
Открытие нового магазина	Сергеев С.С.	Бухгалтер	12
Открытие нового магазина	John Smith	Менеджер	12

Проблема:

Первичный ключ состоит из двух столбцов: Название проекта + Участник.
Столбец Должность зависит только от Участника, а столбец Срок проекта — только от Названия проекта, то есть оба зависят лишь от части составного ключа, а не от полного ключа.

Решение:

Выполняем декомпозицию таблицы:

Декомпозиция – это процесс разбиения одного отношения (таблицы) на несколько.

Таблица "Проекты":

Идентификатор проекта	Название проекта	Срок проекта (мес.)
1	Внедрение приложения	8
2	Открытие нового магазина	12

Таблица "Участники":

Идентификатор участника	Участник	Должность
1	Иванов И.И.	Программист
2	Сергеев С.С.	Бухгалтер
3	John Smith	Менеджер

Связь между проектами и участниками:

Идентификатор проекта Идентификатор участника

1 1

1 2

1 3

2 2

2 3

Мы создали 3 таблицы:

Проекты, в нее мы добавили искусственный первичный ключ
Участники, в нее мы также добавили искусственный первичный ключ
Связь между проектами и участниками, она нужна для реализации связи «Многие ко многим», так как между этими таблицами связь именно такая

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

У каждого атрибута (столбца) есть полная зависимость от ключа.
Исключены частичные зависимости.

--

Вывод

Вторая нормальная форма устраняет частичные зависимости, чтобы структура таблиц стала логичной и устойчивой к аномалиям. Для достижения 2NF ключевые шаги:

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

4. Третья нормальная форма (3NF)

Цель: Устранение транзитивных зависимостей, чтобы неключевые столбцы зависели только от первичного ключа.

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

Основные требования 3NF:

Таблица должна находиться во второй нормальной форме (2NF).
Все неключевые столбцы должны зависеть только от первичного ключа (никаких зависимостей между неключевыми столбцами).

Пример:

Исходная таблица (2NF):

Табельный номер	ФИО	Должность	Подразделение	Описание подразделения
1	Иванов И.И.	Программист	Отдел разработки	Разработка приложений и сайтов
2	Сергеев С.С.	Бухгалтер	Бухгалтерия	Учет финансово-хозяйственной деятельности
3	John Smith	Продавец	Отдел реализации	Организация сбыта продукции

Проблема: "Описание подразделения" зависит не от первичного ключа, а от "Подразделения" — это транзитивная зависимость.

Решение:

Разделяем таблицу на две:

Сотрудники:

Табельный номер	ФИО	Должность	Подразделение
1	Иванов И.И.	Программист	1
2	Сергеев С.С.	Бухгалтер	2
3	John Smith	Продавец	3

Подразделения:

Идентификатор подразделения	Подразделение	Описание подразделения
1	Отдел разработки	Разработка приложений и сайтов
2	Бухгалтерия	Учет финансово-хозяйственной деятельности
3	Отдел реализации	Организация сбыта продукции

Теперь транзитивные зависимости устранены, и таблицы соответствуют требованиям 3NF.

5. Нормальная форма Бойса-Кодда (BCNF)

Цель: Устранение зависимостей, где часть составного ключа зависит от неключевого столбца.

Основные требования BCNF:

Таблица должна быть в третьей нормальной форме (3NF).
Никакая часть составного первичного ключа не должна зависеть от неключевых столбцов.

Ключевая проблема:

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

Пример: Исходная таблица:

Проект	Направление	Куратор
1	Разработка	Иванов И.И.
1	Бухгалтерия	Сергеев С.С.
2	Разработка	Иванов И.И.
2	Бухгалтерия	Петров П.П.
2	Реализация	John Smith
3	Разработка	Андреев А.А.

Проблема: Направление зависит от "Куратора" — нарушение BCNF.

Решение:

Разделяем таблицу на две:

Кураторы и их направления:

Идентификатор куратора	ФИО	Направление
1	Иванов И.И.	Разработка
2	Сергеев С.С.	Бухгалтерия
3	Петров П.П.	Бухгалтерия
4	John Smith	Реализация
5	Андреев А.А.	Разработка

Проекты и их кураторы:

Проект Идентификатор куратора

1 1

1 2

2 1

2 3

2 4

3 5

Теперь зависимость устранена, и таблицы соответствуют требованиям BCNF.

N.B

Главная задача нормализации — устранить аномалии и избыточность, а не ускорить запросы. На производительность она влияет неоднозначно: уменьшение дублирования ускоряет запись и экономит место, но рост числа таблиц добавляет соединения и может замедлять чтение. Положительный баланс нормализация сохраняет примерно до 3 нормальной формы. Начиная с 4 нормальной формы выигрыша по производительности уже нет, более того, с каждой новой формой производительность чтения, как правило, снижается, не говоря уже о том, что с нормализованной базой данных до 5 или 6 нормальной формы будет крайне сложно и неудобно работать и сопровождать ее, ведь с каждой новой формой мы значительно увеличиваем количество таблиц в базе данных.

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

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

Полностью нормализованная база данных – это плохая база данных.

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

Больше о нормальных формах:

Денормализация форм

Подробнее

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

Зачем нужна денормализация?

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

Денормализацию применяют, когда:

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

Основные приёмы денормализации:

Добавление избыточных столбцов — копирование часто запрашиваемого поля из связанной таблицы, чтобы избежать соединения (например, хранить customer_name прямо в таблице заказов).
Предрасчёт агрегатов — хранение вычисленных значений (сумма заказа, количество товаров), чтобы не считать их при каждом запросе.
Объединение таблиц — слияние нескольких связанных таблиц в одну.
Материализованные представления — сохранение результата сложного запроса в виде физической таблицы, которая периодически обновляется (см. Представления).

Недостатки и риски:

Избыточность данных — одни и те же данные хранятся в нескольких местах, что увеличивает объём хранилища.
Аномалии обновления — при изменении продублированных данных их нужно синхронно обновлять во всех копиях, иначе возникнет рассогласование (именно те аномалии, которые устраняет нормализация).
Усложнение логики записи — приложение или триггеры должны поддерживать согласованность дубликатов.

Вывод: денормализация — это компромисс между скоростью чтения и целостностью/простотой записи. Типичный подход на практике: спроектировать базу в нормализованном виде (как правило, до 3NF), а затем точечно денормализовать только те участки, где это действительно необходимо для производительности. Яркий пример денормализации — таблицы измерений в схеме «звезда».

Схемы "звезда" и "снежинка"

Подробнее

Важным решением при настройке хранилища данных является выбор между схемой «звезда» и схемой «снежинка».

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

Что такое звездообразная схема (Star Schema)?

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

Таблица фактов содержит количественные данные, часто называемые мерами или метриками. Меры, как правило, числовые, такие как скорость, стоимость, количество и вес, и их можно агрегировать. Таблица фактов содержит ссылки на внешние ключи на таблицы измерений, которые содержат нечисловые элементы. Это описательные атрибуты, такие как сведения о продукте (название, категория, бренд), информация о клиенте (имя, адрес, сегмент), показатели времени (дата, месяц, год) и т. д. Каждая таблица измерений представляет определенный аспект или измерение данных. Измерение обычно имеет столбец первичного ключа, и на него ссылается таблица фактов через отношения внешнего ключа.

В звездной схеме:

Таблица фактов, содержащая основные метрики, расположена в центре.
Каждая таблица измерений напрямую связана с таблицей фактов, но не с другими таблицами измерений, поэтому имеет звездчатую структуру. Простота схемы «звезда» облегчает составление агрегированных отчетов и анализ, а также оптимизирует операции по извлечению данных. Это связано с тем, что запросы обычно включают меньше соединений по сравнению с более нормализованными схемами. Уменьшенная сложность и простая структура оптимизируют доступ к данным и их обработку, что хорошо подходит для облачных решений для хранения данных.

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

Характеристики звездообразной схемы:

Центральная таблица фактов: В центре находится таблица основных фактов, содержащая показатели. Он представляет деятельность, события и бизнес-операции.
Таблицы размеров: Они окружают таблицу фактов и представляют конкретный аспект бизнес-контекста. В таблицах измерений показаны описательные атрибуты.
Отношения первичный-внешний ключ: Связь между таблицей фактов и таблицей измерений устанавливается через отношения первичный-внешний ключ, позволяя агрегировать данные по разным измерениям.
Связь с размерными таблицами: Между таблицами измерений нет никаких связей. Все таблицы измерений подключены только к центральной таблице фактов.
Денормализованная структура: таблицы измерений часто денормализованы, что полезно для уменьшения необходимости в соединениях во время запросов, поскольку необходимые атрибуты включаются в одно измерение, а не разбивают их на несколько таблиц.
Оптимизированная производительность запросов: Такие функции, как прямые связи между таблицами фактов и измерений и денормализованная структура, способствуют оптимизации производительности запросов. Это позволяет звездообразным схемам решать сложные аналитические задачи и, таким образом, хорошо подходит для анализа данных и составления отчетов.

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

Что такое схема снежинки (Snowflake Schema)?

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

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

Характеристики схемы «снежинка»:

Нормализация: В схеме «снежинка» таблицы измерений нормализованы, в отличие от схемы «звезда», где таблицы денормализованы. Это означает, что атрибуты в таблицах измерений разбиваются на несколько связанных таблиц.
Иерархическая структура: Нормализация таблиц измерений создает иерархическую структуру, напоминающую снежинку.
Связь между таблицами: Нормализация приводит к дополнительным связям соединения между нормализованными таблицами, что увеличивает сложность запросов.
Производительность: Объединение нескольких нормализованных таблиц в схему «снежинка» требует большей вычислительной мощности из-за повышенной сложности запросов, что потенциально влияет на производительность.
Целостность данных: Схемы «снежинка» уменьшают избыточность и устраняют аномалии обновления. Это гарантирует, что данные будут храниться согласованным и нормализованным образом.
Гибкость: Схемы «снежинка» обеспечивают гибкость в организации и управлении сложными связями данных, что обеспечивает более структурированный подход к анализу данных.

Ключевые различия между схемой «Звезда» и «Снежинка»

Key Differences Between Star and Snowflake Schemas

Архитектура
Таблицы измерений денормализованы в звездообразной схеме. Это означает, что они представлены в виде отдельных таблиц, в которых содержатся все атрибуты. Структура этой схемы напоминает звезду, демонстрируя таблицу фактов в центре и исходящие от нее таблицы измерений.
С другой стороны, схема «снежинка» имеет нормализованные таблицы измерений. Это означает, что они разбиты на несколько связанных таблиц. Такая нормализация создает иерархическую структуру, напоминающую снежинку, с дополнительными уровнями таблиц, ответвляющимися от основных таблиц измерений.
Нормализация
Звездообразные схемы денормализованы, где все атрибуты находятся в одной таблице для каждого измерения. Эта денормализация сделана намеренно для повышения производительности. Однако его недостатком является то, что может возникнуть избыточность данных, т. е. одни и те же данные появляются в нескольких таблицах измерений, что требует большего объема памяти.
Схема «снежинка» представляет собой нормализованную таблицу измерений с атрибутами, разбитыми на несколько связанных таблиц. Схема «снежинка» позволяет избежать избыточности данных, повышает качество данных и использует меньше места для хранения, чем схема «звезда».
Производительность запросов
Учитывая меньшее количество операций соединения и более простую структуру таблицы в схеме «звезда», производительность запросов обычно выше по сравнению со схемой «снежинка».
С другой стороны, схема «снежинка» содержит сложные операции соединения, которые требуют доступа к данным из нескольких нормализованных таблиц. В результате схема «снежинка» обычно приводит к снижению производительности запросов.
Техническое обслуживание
В зависимости от нескольких факторов, таких как сложность данных, обновления и объем памяти, поддержка схем «звезда» и «снежинка» может оказаться сложной задачей.
Однако схемы «звезда» обычно легче поддерживать по сравнению со схемами «снежинка» из-за меньшего количества операций соединения, которые упрощают оптимизацию запросов. Однако денормализованная структура способствует некоторому уровню избыточности, что требует тщательного управления для повышения точности анализа данных и понимания.
Процесс нормализации в схемах-снежинках увеличивает сложность и затрудняет поддержку. Соединения требуют дополнительного внимания для поддержания приемлемого уровня производительности. Более того, управление обновлениями и вставками в схеме «снежинка» более сложное, поскольку необходимо распространять изменения по нескольким связанным таблицам. Это можно сравнить со звездообразной схемой, где данные сконцентрированы в меньшем количестве таблиц. Обновления обычно затрагивают только одну или несколько таблиц, что упрощает управление ими.

Схема «Звезда»	Схема «Снежинка»
Иерархии размеров хранятся в таблице размеров.	Иерархии разделены на отдельные таблицы.
Содержит таблицу фактов, окруженную таблицами измерений.	Одна таблица фактов, окруженная таблицей измерений, которая, в свою очередь, окружена таблицей измерений.
Только одно соединение создает связь между таблицей фактов и любыми таблицами измерений.	Требует множества соединений для получения данных.
Простой дизайн БД.	Очень сложная конструкция БД.
Денормализованная структура данных, запросы выполняются быстрее.	Нормализованная структура данных.
Высокий уровень избыточности данных.	Очень низкий уровень избыточности данных.
Таблица одного измерения содержит агрегированные данные.	Данные разделены на разные таблицы измерений.
Обработка куба происходит быстрее.	Обработка куба может быть медленной из-за сложного соединения.
Таблицы могут быть связаны с несколькими измерениями.	Представлена централизованной таблицей фактов, измерения которой нормализованы и разбиты на под-измерения, образуя иерархию.
Предлагает более эффективные запросы с использованием оптимизации запросов Star Join.

Основы SQL

SQL

Подробнее

SQL — язык структурированных запросов (SQL, Structured Query Language), который используется в качестве эффективного способа сохранения данных, поиска их частей, обновления, извлечения и удаления из базы данных.

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

Извлекать данные из базы данных
Вставлять записи в базу данных
Обновлять записи в базе данных
Удалять записи из базы данных
Создавать новые базы данных
Создавать новые таблицы в базе данных
Создавать хранимые процедуры в базе данных
Создавать представления в базе данных
Устанавливать разрешения для таблиц, процедур и представлений

Диалекты SQL (расширения SQL)

Язык SQL – универсальный язык для всех реляционных систем управления базами данных, но многие СУБД вносят свои изменения в язык, применяемый в них, тем самым отступая от стандарта. Такие языки называют диалектами или расширениями языка.

Вот некоторые из них:

T-SQL – диалект Microsoft SQL Server
PL/SQL – диалект Oracle Database
PL/pgSQL – диалект PostgreSQL

С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.

Группы операторов языка SQL

DDL – Data Definition Language

– это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.

В эту группу входят следующие операторы:

CREATE – используется для создания объектов базы данных;
ALTER – используется для изменения объектов базы данных;
DROP – используется для удаления объектов базы данных.

DML – Data Manipulation Language

– это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.

В эту группу входят самые распространённые операторы языка SQL:

SELECT – осуществляет выборку данных;
INSERT – добавляет новые данные;
UPDATE – изменяет существующие данные;
DELETE – удаляет данные.

DCL – Data Control Language

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

Сюда входят:

GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом;
REVOKE – отзывает выданные разрешения;
DENY – задаёт запрет, имеющий приоритет над разрешением.

TCL – Transaction Control Language

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

Сюда можно отнести:

BEGIN TRANSACTION – служит для определения начала транзакции;
COMMIT TRANSACTION – применяет транзакцию;
ROLLBACK TRANSACTION – откатывает все изменения, сделанные в контексте текущей транзакции;
SAVE TRANSACTION – устанавливает промежуточную точку сохранения внутри транзакции.

Больше информации на примере MS SQL можно найти здесь: Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть первая, Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть вторая

Общая структура запроса выглядит следующим образом:

SELECT (столбцы или * для выбора всех столбцов; обязательно)

FROM (таблица; обязательно при выборке из таблицы, но может опускаться при выборе выражений, констант или функций, например SELECT NOW())

WHERE (условие/фильтрация, например, city = Moscow; необязательно)

GROUP BY (столбец, по которому хотим сгруппировать данные; необязательно)

HAVING (условие/фильтрация на уровне сгруппированных данных; необязательно)

ORDER BY (столбец, по которому хотим отсортировать вывод; необязательно)

Создание таблиц, типы данных и ограничения

Подробнее

Прежде чем работать с данными, таблицу нужно создать и описать её структуру. За это отвечают операторы группы DDL (см. SQL): CREATE, ALTER, DROP.

Создание таблицы (CREATE TABLE):

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE,
    age           INT CHECK (age >= 18),
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT now()
);

Изменение и удаление структуры:

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);   -- добавить столбец
ALTER TABLE customers DROP COLUMN phone;              -- удалить столбец
DROP TABLE customers;                                 -- удалить таблицу целиком

Основные типы данных

Типы немного различаются в разных СУБД, но есть общие группы:

Категория	Примеры типов	Назначение
Числовые	`INT`, `BIGINT`, `NUMERIC`/`DECIMAL`, `REAL`	Целые и дробные числа
Строковые	`CHAR(n)`, `VARCHAR(n)`, `TEXT`	Текст фиксированной/переменной длины
Дата и время	`DATE`, `TIME`, `TIMESTAMP`	Даты, время, отметки времени
Логический	`BOOLEAN`	`TRUE`/`FALSE`
Прочие	`UUID`, `JSON`/`JSONB`, `BYTEA`/`BLOB`	Идентификаторы, документы, бинарные данные

Для денежных значений следует использовать NUMERIC/DECIMAL, а не REAL/FLOAT: числа с плавающей точкой хранят значения приближённо и приводят к ошибкам округления.

Ограничения (Constraints)

Ограничения — это правила, которые СУБД автоматически проверяет, обеспечивая целостность данных:

PRIMARY KEY — первичный ключ: уникальность плюс запрет NULL (см. Ключи).
FOREIGN KEY — внешний ключ: значение должно существовать в связанной таблице.
NOT NULL — столбец не может быть пустым.
UNIQUE — значения в столбце не повторяются.
CHECK — значение должно удовлетворять условию (например, age >= 18).
DEFAULT — значение по умолчанию, если оно не указано при вставке.

Ссылочные действия для внешнего ключа

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

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE      -- удалить вслед за родителем и зависимые строки
    ON UPDATE CASCADE      -- обновить ссылку вслед за изменением ключа

Кроме CASCADE доступны RESTRICT/NO ACTION (запретить операцию) и SET NULL/SET DEFAULT (обнулить ссылку или подставить значение по умолчанию).

Изменение данных (INSERT, UPDATE, DELETE)

Подробнее

Если SELECT отвечает за чтение данных, то операторы группы DML INSERT, UPDATE и DELETE отвечают за их изменение.

INSERT — добавление строк

-- Вставка одной строки
INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Иван Иванов', 'ivan@example.com');

-- Вставка нескольких строк сразу
INSERT INTO customers (customer_id, name, email)
VALUES
    (2, 'Пётр Петров', 'petr@example.com'),
    (3, 'Анна Сидорова', 'anna@example.com');

UPDATE — изменение существующих строк

UPDATE customers
SET email = 'new@example.com'
WHERE customer_id = 1;

DELETE — удаление строк

DELETE FROM customers
WHERE customer_id = 3;

Важно про WHERE

В UPDATE и DELETE условие WHERE определяет, какие именно строки будут затронуты. Если забыть WHERE, операция применится ко всем строкам таблицы:

UPDATE customers SET is_active = FALSE;  -- деактивирует ВСЕХ клиентов
DELETE FROM customers;                   -- удалит ВСЕ строки

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

DELETE и TRUNCATE

Для полной очистки таблицы есть более быстрый оператор TRUNCATE TABLE: он удаляет все строки разом, не обрабатывая их построчно и не вызывая строчные триггеры. DELETE без WHERE тоже удаляет все строки, но делает это построчно (медленнее), зато его можно фильтровать условием и откатывать в транзакции стандартным образом.

SELECT

Подробнее

SELECT — обязательный элемент запроса; FROM задаёт источник данных и обязателен при обращении к таблице (но может опускаться при выборке выражений или функций). Вместе они определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers;

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers;

Агрегатные функции

Подробнее

GROUP BY

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

все неагрегированные столбцы из SELECT должны присутствовать в GROUP BY (при этом в GROUP BY можно указать и дополнительные столбцы, которых нет в SELECT),
агрегатные функции (SUM, AVG, COUNT, MAX, MIN) указываются внутри SELECT с указанием столбца, к которому такая функция применяется.

Описание агрегатных функций

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

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

Группировка количества клиентов по городу:

SELECT City, count(CustomerID) FROM Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

SELECT Country, City, count(CustomerID) FROM Customers
GROUP BY Country, City

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

SELECT ProductID, COUNT(OrderID), SUM(Quantity) FROM OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

SELECT City, count(CustomerID) FROM Customers
WHERE Country = 'Germany'
GROUP BY City

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

SELECT City, count(CustomerID) AS Number_of_clients FROM Customers
GROUP BY City

WHERE и HAVING

Подробнее

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента WHERE используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

SELECT * FROM Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

SELECT * FROM Customers
WHERE City IN ('London', 'Berlin')

SELECT * FROM Customers
WHERE City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

SELECT * FROM Customers
WHERE Country = 'Germany' AND City NOT IN ('Berlin', 'Aachen') AND CustomerID > 15

SELECT * FROM Customers
WHERE City IN ('London', 'Berlin') OR CustomerID > 4

HAVING

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

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

SELECT City, count(CustomerID) FROM Customers
GROUP BY City
HAVING count(CustomerID) >= 5

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

SELECT City, count(CustomerID) AS number_of_clients FROM Customers
GROUP BY City
HAVING number_of_clients >= 5

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

SELECT City, count(CustomerID) AS number_of_clients FROM Customers
WHERE CustomerName NOT IN ('Around the Horn','Drachenblut Delikatessend')
GROUP BY City
HAVING number_of_clients >= 5

NULL и трёхзначная логика

Подробнее

NULL в SQL — это не ноль и не пустая строка, а специальный маркер, означающий «значение отсутствует или неизвестно». Из-за этого SQL использует не двузначную (TRUE/FALSE), а трёхзначную логику (3VL, three-valued logic): результатом сравнения может быть TRUE, FALSE или UNKNOWN.

Любое сравнение с NULL даёт UNKNOWN

SELECT 1 = NULL;      -- UNKNOWN (а не TRUE и не FALSE)
SELECT NULL = NULL;   -- тоже UNKNOWN!

Поэтому для проверки на NULL нельзя использовать =; есть специальные операторы IS NULL и IS NOT NULL:

SELECT * FROM customers WHERE email IS NULL;
SELECT * FROM customers WHERE email IS NOT NULL;

Как NULL влияет на запросы:

WHERE оставляет строку, только если условие истинно (TRUE); строки со значением UNKNOWN в результат не попадают.
Агрегатные функции игнорируют NULL (кроме COUNT(*)), см. Агрегатные функции.
GROUP BY считает все значения NULL одной группой.
ORDER BY собирает NULL вместе — в начале или в конце сортировки в зависимости от СУБД (в PostgreSQL по умолчанию NULL в конце при ASC).
UNIQUE, как правило, допускает несколько NULL, так как они не считаются равными друг другу.

Классическая ловушка: NOT IN с NULL

Если подзапрос в NOT IN вернёт хотя бы одно значение NULL, весь результат NOT IN станет UNKNOWN, и запрос не вернёт ни одной строки:

-- Если в orders есть customer_id = NULL, этот запрос вернёт пусто:
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Поэтому вместо NOT IN с потенциальными NULL безопаснее использовать NOT EXISTS.

Работа с NULL: заменить NULL на значение по умолчанию помогает функция COALESCE, а NULLIF — наоборот, превращает значение в NULL (см. Функции).

ORDER BY

Подробнее

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

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

SELECT * FROM Customers
ORDER BY City

Осуществлять сортировку можно и по нескольким столбцам, в этом случае сортировка происходит по порядку указанных столбцов:

SELECT * FROM Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:

SELECT * FROM Customers
ORDER BY CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

SELECT * FROM Customers
ORDER BY Country DESC, City

DISTINCT, LIMIT и OFFSET

Подробнее

DISTINCT — удаление дубликатов

DISTINCT оставляет в результате только уникальные строки, убирая повторы.

-- Уникальные города, в которых есть клиенты
SELECT DISTINCT City FROM Customers;

Если указать несколько столбцов, уникальной считается их комбинация:

SELECT DISTINCT Country, City FROM Customers;

LIMIT и OFFSET — ограничение количества строк

LIMIT ограничивает число возвращаемых строк, а OFFSET пропускает заданное количество строк от начала. Это основной механизм постраничной выдачи (пагинации).

-- Первые 10 клиентов
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10;

-- Следующие 10 (вторая «страница»)
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10 OFFSET 10;

Синтаксис зависит от СУБД: в PostgreSQL и MySQL — LIMIT ... OFFSET ..., в SQL Server и Oracle — OFFSET ... FETCH NEXT ... ROWS ONLY.

Важно: при использовании LIMIT/OFFSET почти всегда нужен ORDER BY — без явной сортировки порядок строк не гарантирован, и «страницы» могут перемешаться.

Проблема OFFSET на больших данных

Чтобы пропустить OFFSET строк, СУБД всё равно должна их прочитать и отбросить. На больших смещениях (например, OFFSET 1000000) это становится медленным. Более эффективный подход — keyset-пагинация (seek method): вместо смещения фильтровать по значению последнего показанного ключа:

-- Следующая страница после последнего показанного CustomerID = 1050
SELECT * FROM Customers
WHERE CustomerID > 1050
ORDER BY CustomerID
LIMIT 10;

CASE

Подробнее

CASE выражение в SQL используется для выполнения условной логики в запросах. Оно позволяет проверять условия и возвращать значение, как только первое условие выполнено. Поэтому как только встречается правдивое условие, чтение останавливается, и возвращается результат.

Существует две формы выражения CASE: простое (simple) CASE и поиск с условиями (searched) CASE.

Простое выражение CASE В этой форме SQL сравнивает выражение с набором возможных значений и возвращает результат первого совпадения.

Синтаксис:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Пример:

SELECT
    product_name,
    CASE category_id
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Books'
        ELSE 'Other'
    END AS category
FROM products;

Здесь, в зависимости от category_id, продукту присваивается соответствующая категория. Если нет совпадений, выполняется условие ELSE, возвращающее 'Other'.

Поиск с условиями CASE Эта форма позволяет использовать логические условия (например, >, <, =, !=), а не просто сопоставлять выражение с конкретными значениями.

Синтаксис:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Пример:

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary > 80000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_range
FROM employees;

Это выражение CASE проверяет различные условия для зарплаты и возвращает соответствующий диапазон зарплаты.

Основные моменты:

Условия в блоках WHEN проверяются по порядку, и первое, которое возвращает TRUE, используется.
Блок ELSE необязателен и задаёт значение по умолчанию, если ни одно условие не выполнено.
CASE часто используется в операторе SELECT, но также может применяться в UPDATE, DELETE и других частях запроса.

Объединения таблиц

Подробнее

Объединения таблиц основаны на принципе первичных и внешних ключей таблиц и являются одной из основ работы реляционных СУБД.

INNER JOIN

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

LEFT JOIN

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

RIGHT JOIN

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

FULL JOIN

Соединение, которое выполняет внутреннее соединение записей и дополняет их левым внешним соединением и правым внешним соединением.

Алгоритм работы полного соединения:

Формируется таблица на основе внутреннего соединения (INNER JOIN)
В таблицу добавляются значения не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN)
В таблицу добавляются значения не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN)

Базовые запросы для разных вариантов объединения таблиц

Операции над множествами

Подробнее

В отличие от соединений, которые объединяют таблицы «по горизонтали» (добавляют столбцы), операции над множествами объединяют результаты двух запросов «по вертикали» (добавляют строки). Для этого оба запроса должны возвращать одинаковое число столбцов совместимых типов.

UNION — объединяет результаты двух запросов и удаляет дубликаты.
UNION ALL — объединяет результаты, сохраняя дубликаты (быстрее, так как не нужно искать и убирать повторы).
INTERSECT — возвращает только строки, присутствующие в обоих запросах.
EXCEPT (в Oracle — MINUS) — возвращает строки из первого запроса, которых нет во втором.

-- Все города, где есть клиенты ИЛИ поставщики (без повторов)
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

-- Города, где есть и клиенты, И поставщики
SELECT City FROM Customers
INTERSECT
SELECT City FROM Suppliers;

-- Города с клиентами, но без поставщиков
SELECT City FROM Customers
EXCEPT
SELECT City FROM Suppliers;

UNION или UNION ALL? Если вы точно знаете, что дубликатов нет или они допустимы, используйте UNION ALL — он работает быстрее, потому что не выполняет дополнительную операцию удаления дубликатов.

Сортировка (ORDER BY) указывается один раз — в конце всего объединённого запроса.

Вложенные запросы

Подробнее

Вложенный запрос – это запрос, который находится внутри другого SQL запроса. Чаще всего он встречается в условии WHERE, но также может использоваться в SELECT, FROM (производные таблицы), HAVING и других частях запроса.

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

Правила вложенных запросов

На вложенный запрос распространяются следующие ограничения:

Список выбора вложенного запроса, начинающийся с оператора сравнения, может включать только одно выражение или имя столбца (за исключением операторов EXISTS и IN в инструкции SELECT * или в списке соответственно).
Если предложение WHERE внешнего запроса включает имя столбца, оно должно быть совместимо для соединения со столбцом в списке выбора вложенного запроса.
Типы данных ntext, текста и изображения нельзя использовать в списке вложенных запросов.
Так как они должны возвращать одно значение, вложенные запросы, представленные оператором немодифицированного сравнения (один не следует ключевому слову ANY или ALL) не могут включать GROUP BY и HAVING предложения.
Ключевое DISTINCT слово нельзя использовать с вложенными запросами, включающими GROUP BY.
Нельзя использовать предложения COMPUTE и INTO.
Предложение ORDER BY может быть указано только вместе с предложением TOP.
Представление, созданное с помощью вложенных запросов, не может быть обновлено.
Список выбора вложенного запроса, начинающегося с предложения EXISTS, по соглашению содержит звездочку * вместо отдельного имени столбца. Правила для вложенного запроса, начинающегося с предложения EXISTS, являются такими же, как для стандартного списка выбора, поскольку вложенный запрос, начинающийся с предложения EXISTS, проводит проверку на существование и возвращает TRUE или FALSE вместо данных.

Больше информации с примерами можно найти здесь: Вложенные запросы (SQL Server)

Расширенные возможности SQL

Функции

Подробнее

Применение функций

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

Функции в SQL можно разделить на несколько категорий:

Агрегатные функции

Эти функции работают с множеством строк и возвращают одно агрегированное значение. Их часто используют вместе с операцией GROUP BY.

COUNT(): Возвращает количество строк.
```
SELECT COUNT(*) FROM users;
```
SUM(): Возвращает сумму значений.
```
SELECT SUM(salary) FROM employees;
```
AVG(): Возвращает среднее значение.
```
SELECT AVG(age) FROM users;
```
MIN(): Возвращает минимальное значение.
```
SELECT MIN(salary) FROM employees;
```
MAX(): Возвращает максимальное значение.
```
SELECT MAX(salary) FROM employees;
```

Строковые функции

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

LENGTH(): Возвращает длину строки.
```
SELECT LENGTH('Hello World');
```
LOWER() / UPPER(): Преобразуют строку к нижнему или верхнему регистру.
```
SELECT LOWER('HELLO'), UPPER('hello');
```

SUBSTRING(): Возвращает подстроку.

SELECT SUBSTRING('Hello World', 1, 5);  -- Вернет 'Hello'

CONCAT(): Объединяет строки.

SELECT CONCAT(first_name, ' ', last_name) FROM users;

TRIM(): Удаляет пробелы в начале и конце строки.
```
SELECT TRIM('   Hello World   ');
```

Числовые функции

Работают с числовыми данными для выполнения математических операций.

ROUND(): Округляет число до указанного количества знаков после запятой.
```
SELECT ROUND(123.456, 2);  -- Вернет 123.46
```
CEIL() / FLOOR(): Округляют число вверх или вниз до ближайшего целого.
```
SELECT CEIL(1.2), FLOOR(1.8);  -- Вернет 2 и 1 соответственно
```
ABS(): Возвращает абсолютное значение числа.
```
SELECT ABS(-10);  -- Вернет 10
```
MOD(): Возвращает остаток от деления.
```
SELECT MOD(10, 3);  -- Вернет 1
```

Дата и время функции

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

NOW(): Возвращает текущую дату и время.
```
SELECT NOW();
```
CURDATE(): Возвращает текущую дату.
```
SELECT CURDATE();
```

DATE_ADD(): Добавляет к дате указанный интервал.

SELECT DATE_ADD('2024-01-01', INTERVAL 7 DAY);  -- Вернет '2024-01-08'

DATEDIFF(): Возвращает разницу между двумя датами в днях.

SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- Вернет 365 (2024 — високосный год)

YEAR(), MONTH(), DAY(): Извлекают соответствующую часть даты.
```
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
```

Логические функции

Эти функции применяются для работы с булевыми значениями и выражениями.

IF(): Возвращает одно значение, если условие истинно, и другое — если ложно.
```
SELECT IF(salary > 5000, 'High', 'Low') FROM employees;
```
COALESCE(): Возвращает первое ненулевое (не NULL) значение.
```
SELECT COALESCE(NULL, NULL, 'Value');  -- Вернет 'Value'
```
NULLIF(): Возвращает NULL, если два выражения равны, иначе возвращает первое выражение.
```
SELECT NULLIF(10, 10);  -- Вернет NULL
```

Пример использования функций:

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       YEAR(NOW()) - YEAR(birthdate) AS age,
       IF(salary > 5000, 'High Salary', 'Low Salary') AS salary_status
FROM employees;

Этот запрос возвращает полное имя сотрудника, возраст и статус зарплаты на основе условия.

Функции в SQL позволяют эффективно работать с данными, улучшая обработку запросов и предоставляя различные способы манипуляции числами, строками и датами.

Справочник по функциям SQL

Аналитические функции

Подробнее

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

Синтаксис аналитических функций

Обычно аналитические функции пишутся следующим образом:

<функция>([аргументы]) OVER ([PARTITION BY] [ORDER BY])

Функция — это аналитическая функция, например, ROW_NUMBER(), RANK(), SUM(), AVG() и другие.
OVER — ключевое слово, которое указывает на использование оконной функции.
PARTITION BY — позволяет разделить набор данных на группы, в пределах которых будет происходить расчет функции (аналогично GROUP BY).
ORDER BY — определяет порядок, в котором строки должны быть упорядочены для расчетов.

Часто используемые аналитические функции

ROW_NUMBER(): возвращает номер строки в пределах каждой группы в указанном порядке.
```
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, employee_name, salary
FROM employees;
```
RANK(): присваивает ранг каждой строке на основе указанного порядка, при этом строки с одинаковыми значениями получают одинаковый ранг, а следующие за ними ранги пропускаются.
```
SELECT RANK() OVER (ORDER BY salary DESC) AS rank, employee_name, salary
FROM employees;
```
DENSE_RANK(): отличается от RANK(), так как при одинаковых значениях строки пропуски между рангами не образуются.
```
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank, employee_name, salary
FROM employees;
```

NTILE(n): делит строки на n равных частей (кусков).

SELECT NTILE(4) OVER (ORDER BY salary DESC) AS quartile, employee_name, salary
FROM employees;

SUM(), AVG(), MIN(), MAX(): могут использоваться как оконные функции, например, для расчета кумулятивной суммы.
```
SELECT employee_name, salary,
       SUM(salary) OVER (ORDER BY salary DESC) AS running_total
FROM employees;
```
FIRST_VALUE(): возвращает первое значение в окне, определенном OVER().
```
SELECT name, salary, FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS first_salary
FROM employees;
```

Для каждой строки этот запрос возвращает самое высокое (первое) значение зарплаты в окне.

LAST_VALUE(): возвращает последнее значение в окне.

SELECT name, salary, LAST_VALUE(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM employees;

В этом примере LAST_VALUE() возвращает последнее значение зарплаты в окне. Замечание: Для корректной работы функции нужно явно указать границы окна.

LAG(): позволяет получить значение из предыдущей строки относительно текущей.
```
SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;
```

Этот запрос возвращает зарплату предыдущей строки относительно текущей строки. Если это первая строка, вернется NULL.

LEAD(): позволяет получить значение из следующей строки относительно текущей.
```
SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;
```

Этот запрос возвращает зарплату следующей строки относительно текущей строки. Если это последняя строка, вернется NULL.

Пример использования аналитических функций:

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

SELECT name,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary,
       FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS highest_salary
FROM employees;

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

PARTITION BY

PARTITION BY разделяет набор данных на группы, внутри которых будет производиться вычисление функции. Это похоже на GROUP BY, но без агрегации данных в одну строку.

Пример: подсчет ранга в каждой группе департаментов.

SELECT department, employee_name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Практическое применение

Кумулятивные суммы и скользящие средние: Аналитические функции используются для расчета кумулятивных значений или для анализа временных рядов, например, для нахождения скользящего среднего.
Сравнение строк: легко можно сравнивать текущую строку с предыдущей или следующей с помощью таких функций, как LAG() и LEAD().
```
SELECT employee_name, salary,
       LAG(salary) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;
```

Оконные рамки (Window Frames)

Можно задавать рамки для окна, определяя диапазон строк для анализа:

ROWS BETWEEN: определяет рамку относительно текущей строки.
```
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
```

Это полезно для расчета значений на основе соседних строк.

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

Справочник по функциям SQL

Больше информации можно найти здесь: Построение OLAP-запросов с использованием аналитических функций, Учимся применять оконные функции

Общие табличные выражения (CTE)

Подробнее

Общее табличное выражение (CTE, Common Table Expression) — это временный именованный результат запроса, который существует только в рамках одного оператора. CTE объявляется через ключевое слово WITH и делает сложные запросы более читаемыми, разбивая их на логические шаги.

Синтаксис:

WITH active_customers AS (
    SELECT customer_id, name
    FROM customers
    WHERE is_active = TRUE
)
SELECT * FROM active_customers WHERE name LIKE 'А%';

CTE и подзапросы

По возможностям CTE похож на вложенный запрос в FROM, но имеет преимущества:

Читаемость — логика выносится «наверх» и получает понятное имя.
Переиспользование — на один CTE можно сослаться несколько раз в пределах запроса.
Цепочки шагов — несколько CTE можно перечислить через запятую, где следующий использует предыдущий.

WITH orders_per_customer AS (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, o.cnt
FROM customers c
JOIN orders_per_customer o ON o.customer_id = c.customer_id
WHERE o.cnt > 5;

Рекурсивные CTE

WITH RECURSIVE позволяет запросу ссылаться на самого себя — это используется для обхода иерархических и древовидных структур (категории, оргструктура, граф связей). Рекурсивный CTE состоит из двух частей, объединённых UNION ALL: «якорной» (базовой) и рекурсивной, которая ссылается на сам CTE.

-- Все подчинённые сотрудника с id = 1 (обход дерева оргструктуры)
WITH RECURSIVE subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE employee_id = 1              -- якорь: стартовая строка
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id  -- рекурсивная часть
)
SELECT * FROM subordinates;

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

Представления

Подробнее

Представление (View) — это виртуальная таблица, основанная на результате SQL-запроса. Представление не хранит данные само по себе (за исключением материализованных представлений), а лишь сохраняет сам запрос; при обращении к представлению этот запрос выполняется к исходным («базовым») таблицам.

Создание представления:

CREATE VIEW active_customers AS
SELECT CustomerID, CustomerName, City
FROM Customers
WHERE IsActive = TRUE;

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

SELECT * FROM active_customers WHERE City = 'London';

Зачем нужны представления:

Упрощение запросов — сложный запрос с множеством соединений и условий можно «спрятать» за простым именем и переиспользовать.
Безопасность — пользователю можно выдать доступ к представлению, не давая прав на исходные таблицы; так можно скрыть конфиденциальные столбцы (например, показать сотруднику данные без поля «зарплата»).
Абстракция — структуру базовых таблиц можно менять, сохраняя стабильный интерфейс представления для приложений.

Обновляемые представления

Через простое представление (без агрегаций, GROUP BY, DISTINCT, соединений и т.п.) можно выполнять INSERT, UPDATE, DELETE — изменения применятся к базовой таблице. Сложные представления, как правило, доступны только для чтения, но их можно сделать изменяемыми с помощью триггеров INSTEAD OF.

Материализованные представления (Materialized View)

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

Свойство	Обычное представление	Материализованное представление
Хранение данных	Нет (только запрос)	Да (результат хранится физически)
Актуальность данных	Всегда актуальны	Нужно обновлять (`REFRESH`)
Скорость чтения	Зависит от базового запроса	Высокая

Больше информации: PostgreSQL — CREATE VIEW

Хранимые процедуры

Подробнее

Хранимая процедура (Stored Procedure) — это именованный набор SQL-команд (и процедурной логики), который хранится на стороне сервера базы данных и вызывается по имени. Процедуры могут принимать параметры, содержать условия, циклы, обработку ошибок и управлять транзакциями.

Пример (PostgreSQL, PL/pgSQL):

CREATE PROCEDURE transfer_funds(sender INT, receiver INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE account_id = sender;
    UPDATE accounts SET balance = balance + amount WHERE account_id = receiver;
END;
$$;

Вызов процедуры:

CALL transfer_funds(1, 2, 100);

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

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

Недостатки:

Привязка к СУБД — процедуры пишутся на процедурных расширениях SQL, которые у каждой СУБД свои: PL/pgSQL (PostgreSQL), T-SQL (SQL Server), PL/SQL (Oracle). Перенос на другую СУБД затруднён.
Сложность сопровождения — логику в базе труднее отлаживать, тестировать и версионировать, чем код приложения.

Процедура и функция

Близкое понятие — хранимая функция. Главное отличие: функция обязана возвращать значение и обычно вызывается внутри запроса (SELECT my_func(...)), тогда как процедура вызывается отдельной командой CALL и предназначена для выполнения действий (в том числе с управлением транзакциями). См. также раздел Функции.

Больше информации: PostgreSQL — CREATE PROCEDURE

Триггеры

Подробнее

Триггер (Trigger) — это специальная хранимая процедура, которая автоматически выполняется в ответ на определённое событие в таблице: вставку (INSERT), изменение (UPDATE) или удаление (DELETE) строк. Триггер «срабатывает» сам, без явного вызова из приложения.

Классификация триггеров:

По моменту срабатывания:
- BEFORE — выполняется до операции (можно проверить или изменить данные перед записью);
- AFTER — выполняется после операции (например, для логирования или обновления связанных таблиц);
- INSTEAD OF — выполняется вместо операции (применяется в основном к представлениям, чтобы сделать их изменяемыми).
По уровню:
- строчные (FOR EACH ROW) — срабатывают для каждой затронутой строки;
- операторные (FOR EACH STATEMENT) — срабатывают один раз на весь оператор, независимо от числа строк.

Внутри триггера доступны специальные записи NEW (новое значение строки — для INSERT/UPDATE) и OLD (старое значение — для UPDATE/DELETE).

Пример (PostgreSQL): автоматическое проставление времени последнего изменения строки.

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Типичные сценарии применения:

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

Недостатки:

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

Больше информации: PostgreSQL — CREATE TRIGGER

Оптимизация и производительность

EXPLAIN

Подробнее

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

Основные аспекты команды EXPLAIN:

Последовательность выполнения запроса: Показывает, в каком порядке таблицы будут участвовать в запросе, как СУБД планирует соединять данные (если есть JOIN).
Использование индексов: Информация о том, будут ли использоваться индексы, и если да, то какие.
Типы соединений (JOIN): Указывает типы соединений, такие как:
- ALL — полный перебор таблицы (table scan), это медленнее и менее эффективно.
- INDEX — используется индекс, что быстрее, чем полный перебор.
- RANGE — используется диапазон значений индекса.
- REF — используется индекс по внешнему ключу.
- EQ_REF — используется уникальный индекс.
Оценка количества строк: Ожидаемое количество строк, которое будет обработано на каждом этапе запроса. Это помогает понять, сколько данных должно быть обработано.
Порядок сортировки: Может показать, будет ли результат сортироваться и каким образом, и потребуется ли для этого дополнительная операция сортировки.

Пример (для MySQL):

EXPLAIN SELECT * FROM users WHERE age > 25;

Этот запрос покажет план выполнения запроса по выбору всех пользователей старше 25 лет.

Пример вывода (MySQL):

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ALL	NULL	NULL	NULL	NULL	1000	Using where

id: идентификатор запроса или подзапроса.
select_type: тип запроса (например, простой или вложенный).
table: таблица, которая используется в текущем шаге.
type: тип соединения (в данном случае полный перебор таблицы — ALL).
possible_keys: возможные индексы, которые можно было бы использовать.
key: индекс, который был фактически использован (в данном случае индекс не использовался).
rows: примерное количество строк, которое будет обработано.
Extra: дополнительные сведения, такие как Using where, указывающие, что будет применяться условие.

Оптимизация запросов с помощью EXPLAIN

Использование индексов: Если команда показывает, что используется тип соединения ALL, это может означать, что индекс не используется. Добавление или оптимизация индексов может значительно ускорить запрос.
Избегайте полного сканирования таблицы: Полный перебор таблицы (table scan) может быть дорогим с точки зрения производительности. EXPLAIN помогает выявить такие случаи и оптимизировать запросы.
JOIN: Проверьте, как СУБД соединяет таблицы, и оптимизируйте запросы, добавляя индексы на ключевые поля для ускорения соединений.

Таким образом, EXPLAIN является мощным инструментом для анализа и оптимизации запросов в SQL.

Ограничения

EXPLAIN применяется к запросам, которые оптимизатор может разобрать и спланировать: SELECT, INSERT, UPDATE, DELETE (в ряде СУБД также CREATE TABLE ... AS и др.). При попытке использовать EXPLAIN с неподдерживаемым типом запроса возвращается ошибка.
Сам по себе EXPLAIN (без ANALYZE) только строит план и не выполняет запрос; вариант EXPLAIN ANALYZE действительно выполняет запрос, чтобы показать фактическое время и число строк (изменяющие данные команды при этом обычно оборачивают в транзакцию с откатом).

Больше информации с примерами можно найти здесь: Читаем EXPLAIN на максималках, Использование EXPLAIN. Улучшение запросов

Индексы

Подробнее

Индекс (Index) – это особая таблица (на самом деле совсем нет, это скорее метаинформация, но очень условно можно представить в виде таблиц), используемая поисковыми системами для поиска данных. Его активное использование играет важнейшую роль в повышении производительности sql серверов.

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

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

Как только таблица создана и в ней еще нет индексов, она выглядит как куча данных (Heap). В ней все записи хранятся хаотично, без определенного порядка.

Если в таблице необходимо найти определенные данные, sql server просканирует ее (Table scan). Пока в таблице не заданы индексы, поддерживающие ограничения (UNIQUE CONSTRAINT, UNIQUE INDEX или PRIMARY KEY), сервер прочитает все табличные записи (с первой до последней) и выберет те, которые удовлетворяют условиям поиска.

Это демонстрирует базовые функции indexes:

повышение скорости поиска информации и производительности запросов;
сохранение целостности данных через обеспечение уникальности строк таблицы.

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

Indexes имеют и недостатки:

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

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

Структура

Все индексы имеют одинаковую структуру. Они состоят из:

наборов страниц;
узлов, имеющих древовидную структуру, иерархическую по природе.

Все они хранятся в виде сбалансированных B-деревьев (B-tree). Начало такого дерева расположено в корневом узле (находящимся на вершине иерархии) и по сути является «входной дверью». Этот узел имеет одну страницу, в которой содержатся указатели на ключи последующих уровней.

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

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

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

Типы индексов (на примере MS SQL)

В Microsoft SQL Server используются следующие индексы: кластерные и некластерные.

Кластерный индекс

Основная его задача — сохранение табличных данных в виде, отсортированном по значению ключа. Таблице или представлению может быть присущ лишь единственный кластеризованный индекс (Clustered index), потому что табличные данные могут отсортировываться в едином возможном порядке – либо возрастания, либо убывания. По возможности, у каждой таблицы должен быть Clustered index.

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

Если у таблицы нет Clustered index, при создании ограничения PRIMARY KEY кластерный индекс формируется автоматически (по умолчанию). Ограничение UNIQUE по умолчанию создаёт некластерный индекс. Когда для таблиц/куч уже созданы Nonclustered indexes, то в процессе создания Clustered index все некластеризованные должны быть перестроены.

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

Некластерный индекс

Некластеризованными (Nonclustered) называют такие индексы, которые содержат:

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

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

Он может указывать на:

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

Nonclustered indexes могут быть расширены дополнительными столбцами (included column). А значит, листья будут сохранять значения индексированных и дополнительных неиндексированных столбцов. Это свойство дает возможность обойти определенные ограничения, возложенные на индекс. Данный подход позволяет включать неиндексируемые столбцы либо обходить ограничения на длину индекса.

Главные свойства Nonclustered indexes:

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

Специальные типы индексов

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

Фильтруемый

Фильтруемым (Filtered) индексом называют оптимизированный Nonclustered index, в котором задействован предикат фильтра для индексации части строк в таблице.

Тщательно спроектированный Filtered index способен:

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

Составной

Составным называют индекс, который:

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

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

Создание составных индексов целесообразно, когда:

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

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

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

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

Query Optimizer использует их в зависимости от структуры запроса.

Больше информации об оптимизации запросов можно найти здесь: Оптимизатор запроса. Часть первая, Оптимизатор запросов. Вторая часть

Уникальный

Уникальным (Unique) называют индекс, обеспечивающий уникальное значение всех строк по определенному ключу и гарантирующий, что в ключе индекса не будет одинаковых значений. Для составного ключа понятие уникальности касается всех index columns, но не распространяется на каждый столбец в отдельности.

Если в таблице формируется Unique index одновременно по ряду столбцов, это означает, что абсолютно каждая вариация значений в ключе будет уникальной.

SQL сервером создается автоматически Unique index для ключевых столбцов при формировании ограничений UNIQUE либо PRIMARY KEY. Но он формируется лишь при выполнении условия отсутствия дублей в ключевых столбцах таблицы.

Уникальный индекс создается автоматически при определении ограничений столбца:

первичным ключом (на один столбец либо сразу на несколько), при условии, что кластерный индекс ранее не создавался. В том случае, когда он все-таки уже создан, сервер создаст уникальный некластерный индекс по первичному ключу;
ограничением на уникальность значений – сервером создается Unique Nonclustered index. Когда кластерный индекс не был сформирован заранее, есть возможность создания именно Unique Clustered index.

Колоночный

Колоночным (Columnstore) называют индекс, в котором данные хранятся в столбцах. Использование Columnstore indexes наиболее целесообразно применять для крупных хранилищ, т.к. они помогут:

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

Пространственный

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

Основное условие создания пространственного индекса – наличие PRIMARY KEY для таблиц.

Полнотекстовый

Полнотекстовые (Full-text) индексы применяются для повышения эффективности поиска определенных слов в строках, где данные представлены в символах.

Действия по созданию и обслуживанию Full-text indexes называются «заполнениями». Встречаются заполнения:

полное – осуществляется SQL сервером после создания нового Full-text index. Размер таблицы влияет на затребованный объем ресурсов. При увеличении размера на операцию требуются ресурсы большего размера. Потому предусмотрена возможность откладывания этого процесса;
основанное на отслеживании изменений – применяется для того, чтобы обслуживать Full-text index после полного заполнения (первоначального).

Покрывающий

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

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

XML-индекс

XML – специфический тип индекса, предназначенный для работы с данными в столбцах таблицы, представленными в соответствующем формате. Он делает более эффективной обработку поисковых запросов к ним.

Встречаются XML-indexes:

первичные – индексируют, хранят в столбцах XML теги, пути, значения. Целесообразно создавать, когда таблица по первичному ключу имеет кластерный индекс;
вторичные – создаются лишь для таблиц с первичным XML-index. Применяются для увеличения производительности системы по определенному типу обращения к XML-столбцам. Встречаются типы XML-indexes: PATH, VALUE, PROPERTY.

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

Активно используются специальные индексы для таблиц данных:

оптимизированные для памяти (In-Memory OLTP). К таковым относятся Хэш индексы (Hash);
Nonclustered indexes, которые специально создаются для сканирования (как упорядоченного, так и диапазонного) и оптимизируются для памяти.

Создание и проектирование индексов в ms sql server

Польза индексов очевидна, потому и проектироваться они должны крайне аккуратно. Созданные тщательным образом способны улучшить производительность, а непрофессионально – понизить.

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

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

Базы данных

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

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

Если предполагается частое обновление данных в таблице, то для нее нужно применять минимум индексов.
Для таблицы со значительным количеством данных, которые предположительно будут редко изменяться, можно использовать то число индексов, которое улучшит производительность запросов. Но для таблиц небольшого объема не всегда целесообразно вообще их использовать. Такой поиск может выполняться дольше, чем обычное сканирование таблицы.
Для Clustered indexes используйте самые короткие поля, которые только допустимы. Лучше всего их применять на столбцах с уникальными значениями и в которых не допускается использование NULL. По этой причине чаще всего PRIMARY KEY выступает в роли Clustered index.
Производительность индекса напрямую зависит от того, насколько уникальны значения в столбце. Она снижается с увеличением дублей если в столбце и растет с уменьшением. Потому, при каждой возможности следует использовать уникальный индекс.
Если используется составной индекс, то в нем нужно учитывать порядок столбцов. Первыми идут те, в которых в выражениях используется WHERE. За ними – столбцы с наивысшими показателями уникальных значений. Остальные выстраиваются по мере понижения этого показателя.
Допускается использование индекса на вычисляемых столбцах таблицы, но лишь при условии соблюдения определенных требований (для вычисления значений такого столбца могут использоваться только детерминистические выражения, т.е. результат для определенного набора входящих параметров всегда должен быть одинаковым).

Запросы к базе данных

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

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

Оптимизация индексов

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

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

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

Согласно рекомендациям Microsoft, последующие действия будут зависеть от уровня фрагментации:

меньше 5% – о дефрагментации следует пока забыть;
от 5 до 30% – требуется выполнить реорганизацию индекса. Это потребует минимального количества ресурсов системы и ее можно провести без долговременной блокировки;
свыше 30% – следует выполнить перестроение индекса. При значительном уровне фрагментации это наиболее эффективно.

Реорганизация индекса

Реорганизацией называют процесс устранения фрагментации индекса. В его ходе происходит дефрагментация конечного уровня кластерных и некластерных индексов по таблицам и представлениям. Говоря простым языком – выполняется простое переупорядочивание страниц. В основе переупорядочивания лежит логический порядок конечных узлов (выполняете слева направо).

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

Перестроением называется операция по устранению фрагментации индекса. Он заключается в устранении старого и формировании нового.

Больше информации в Все, что необходимо знать про индексы MS SQL

Масштабирование

Общая информация

Область сравнения	Партицирование	Шардирование	Репликация
Основная функция	Повышение производительности	Ускорение обработки	Повышение стабильности
Метод	Разбиение по функциональности	Разбиение по объему	Копирование между серверами
Распределение	Потоковое	Физическое	По ведущим и ведомым серверам

Подробнее

Масштабирование — это процесс увеличения вычислительных ресурсов системы для обработки больших объемов данных и нагрузки. Масштабирование бывает двух типов:

Вертикальное масштабирование (Vertical Scaling, Scale-Up): Увеличение мощности одного сервера (например, добавление процессоров, памяти или дисков).

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

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

Горизонтальное масштабирование (Horizontal Scaling, Scale-Out): Добавление большего количества серверов для распределения нагрузки.

Преимущества: Легко масштабируется, можно добавлять неограниченное количество серверов.

Недостатки: Требует более сложного управления и настройки для поддержания согласованности данных и распределения нагрузки.

Больше информации можно найти здесь: Горизонтальное и вертикальное масштабирование инфраструктуры

Партицирование

Подробнее

Разбиение — это метод распределения данных в таблице на более мелкие части, называемые разделами (partitions). Каждый раздел может храниться отдельно, что помогает оптимизировать производительность запросов и управления данными.

Цель: Увеличить скорость выполнения запросов, уменьшить нагрузку на систему и упростить управление данными.

Типы разбиения:

Горизонтальное разбиение (horizontal partitioning) — строки таблицы распределяются по разным разделам.
Вертикальное разбиение (vertical partitioning) — колонки таблицы распределяются по разным разделам.

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

Недостатки: не поможет в случаях, когда нужно создать бэкап или восстановить из бэкапа, и не освободит место на диске.

Больше информации с примером партицирования для PostgreSQL можно найти здесь: Партицирование таблиц в PostgreSQL

Шардинг

Подробнее

Шардинг — это метод горизонтального разбиения данных на несколько баз данных или серверов (шардов), чтобы уменьшить нагрузку на один сервер и повысить доступность системы. Каждый "шард" хранит уникальный набор данных, которые распределяются по серверам по определенному алгоритму (например, по географическим регионам или по идентификаторам пользователей).

Цель: Масштабировать базу данных по мере роста данных и нагрузки на систему, распределяя их на несколько серверов.

Преимущества: Обеспечивает горизонтальное масштабирование, снижает нагрузку на один сервер, улучшает отказоустойчивость и скорость доступа к данным.

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

Больше информации можно найти здесь: Шардирование

Репликация

Подробнее

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

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

Типы репликации:

Синхронная — изменения данных одновременно применяются ко всем репликам.
Асинхронная — данные копируются с некоторой задержкой.

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

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

Больше информации можно найти здесь: Репликация баз данных

Механизмы кэширования

Подробнее

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

Уровни кэширования:

Кэш СУБД — сама база данных кэширует страницы данных и индексов в памяти (например, buffer pool / shared buffers), а также планы выполнения запросов.
Внешний кэш приложения — отдельное хранилище в памяти между приложением и БД: Redis, Memcached. Хранит результаты запросов, сессии, заранее посчитанные данные.
Кэш на стороне клиента и CDN — кэширование ответов ближе к пользователю (HTTP-кэш, CDN для статики).

Стратегии кэширования:

Cache-aside (ленивая загрузка) — приложение сначала проверяет кэш; при промахе (cache miss) читает данные из БД и кладёт их в кэш. Самая распространённая стратегия.
Read-through — приложение всегда обращается к кэшу, а тот сам подгружает данные из БД при промахе.
Write-through — запись идёт одновременно в кэш и в БД; данные всегда согласованы, но запись медленнее.
Write-back (write-behind) — запись идёт сначала в кэш, а в БД переносится позже, пакетно; это быстро, но есть риск потери данных при сбое.

Вытеснение и устаревание данных

Объём кэша ограничен, поэтому используются политики вытеснения (eviction): LRU (Least Recently Used — вытесняется дольше всего не использовавшееся), LFU (Least Frequently Used — реже всего используемое), FIFO. Чтобы данные не «протухали», задают время жизни записи — TTL (Time To Live), по истечении которого запись считается недействительной.

Типичные проблемы:

Инвалидация кэша — одна из самых сложных задач: нужно вовремя удалять или обновлять устаревшие данные, иначе пользователь увидит неактуальную информацию.
Несогласованность — данные в кэше и в БД могут расходиться.
Cache stampede («эффект толпы») — когда популярная запись истекает, множество запросов одновременно идёт в БД за одними и теми же данными, резко повышая нагрузку.

Кэширование особенно эффективно для систем с преобладанием операций чтения и редко меняющимися данными.

Пул соединений

Подробнее

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

Зачем это нужно

Установка нового соединения с БД — дорогая операция: нужно открыть сетевое соединение, пройти аутентификацию, выделить ресурсы на сервере. При высокой нагрузке постоянное открытие и закрытие соединений становится узким местом. К тому же каждое соединение потребляет память и ресурсы сервера, а их количество ограничено (max_connections).

Как работает пул:

при старте создаётся несколько соединений;
приложение «арендует» свободное соединение из пула на время запроса;
после завершения работы соединение не закрывается, а возвращается в пул для повторного использования;
если все соединения заняты, новые запросы ждут освобождения (или отклоняются по тайм-ауту).

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

Производительность — исключаются затраты на повторное установление соединений.
Контроль нагрузки — пул ограничивает максимальное число одновременных подключений к БД, защищая её от перегрузки.

Где реализуется

Пул может быть встроен в приложение (например, HikariCP в Java, пулы в драйверах) или вынесен в отдельный внешний пулер — для PostgreSQL это PgBouncer и Pgpool-II. Внешний пулер особенно полезен, когда экземпляров приложения много и каждый открывает свои соединения.

Резервное копирование и восстановление

Подробнее

Резервное копирование (Backup) — это создание копии данных, чтобы восстановить базу в случае сбоя оборудования, ошибки человека, повреждения данных или атаки. Без продуманной стратегии резервного копирования любая авария может привести к безвозвратной потере данных.

Виды резервных копий:

Полная (Full) — копия всей базы данных. Самая простая в восстановлении, но занимает больше всего места и времени.
Инкрементальная (Incremental) — сохраняет только изменения с момента последней любой копии. Экономит место, но для восстановления нужна вся цепочка копий.
Дифференциальная (Differential) — сохраняет изменения с момента последней полной копии. Компромисс между полной и инкрементальной.

Логическое и физическое копирование:

Логическое — выгрузка данных в виде SQL-команд или дампа (например, pg_dump в PostgreSQL, mysqldump в MySQL). Гибко и переносимо между версиями, но медленно на больших объёмах.
Физическое — копирование самих файлов БД на уровне диска (например, pg_basebackup). Быстрее на больших базах.

Восстановление на момент времени (PITR)

Point-in-Time Recovery (PITR) позволяет восстановить базу не просто до состояния последней копии, а на любой произвольный момент в прошлом (например, за секунду до ошибочного DELETE). Это достигается комбинацией полной физической копии и журнала предзаписи (WAL): сначала разворачивается копия, а затем «проигрываются» записи журнала до нужного момента.

Хорошие практики:

хранить копии отдельно от основного сервера (другой диск, другой ЦОД, облако);
регулярно проверять восстановление — резервная копия, из которой не получается восстановиться, бесполезна;
определять RPO (допустимый объём потерянных данных) и RTO (допустимое время восстановления) и подбирать стратегию под них.

Транзакции и согласованность данных

Транзакции

Подробнее

Транзакция — это последовательность операций, выполняемых как единое целое. Если хотя бы одна из операций не удаётся, вся транзакция откатывается, чтобы сохранить согласованность данных. Она защищает данные благодаря принципу «всё, или ничего».

Пример:

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

BEGIN TRANSACTION;
-- обновление таблицы orders
UPDATE orders
SET status = 'shipped'
WHERE order_id = 123;
--обновление таблицы inventory
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 456;
COMMIT TRANSACTION;

Если оба оператора завершаются успешно, транзакция фиксируется, и изменения в базе данных становятся постоянными.

Простое объяснение можно найти здесь: Что такое транзакция

ACID

Подробнее

Свойства ACID и РСУБД

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

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

Уровни изоляции транзакций

Подробнее

Уровни изоляции

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

Serializable (Сериализуемый):

Описание: Самый высокий уровень изоляции.
Преимущества: Предотвращает все типы аномалий (грязное чтение, неповторяющееся чтение, фантомное чтение).
Недостатки: Может значительно снизить производительность, так как транзакции фактически выполняются последовательно.

Repeatable Read (Повторяемое чтение):

Описание: Гарантирует, что данные, прочитанные транзакцией, не изменятся до её завершения.
Преимущества: Предотвращает грязное чтение и неповторяющееся чтение.
Недостатки: Не предотвращает фантомное чтение.

Read Committed (Фиксированное чтение):

Описание: Транзакция читает только зафиксированные данные.
Преимущества: Предотвращает грязное чтение.
Недостатки: Не предотвращает неповторяющееся чтение и фантомное чтение.

Read Uncommitted (Нефиксированное чтение):

Описание: Самый низкий уровень изоляции. Транзакция может читать незавершённые (не зафиксированные) данные других транзакций.
Преимущества: Наивысшая производительность.
Недостатки: Возможны все типы аномалий (грязное чтение, неповторяющееся чтение, фантомное чтение).

Управление транзакциями

Управление транзакциями включает в себя методы и техники для обеспечения выполнения транзакций в соответствии с ACID-свойствами. Это может включать:

Начало транзакции: Создание новой транзакции.
Фиксация транзакции (COMMIT): Сохранение всех изменений, сделанных в рамках транзакции.
Откат транзакции (ROLLBACK): Отмена всех изменений, сделанных в рамках транзакции.
Журналирование (Logging): Запись всех операций, выполненных в рамках транзакции, для возможности восстановления в случае сбоя.

Больше информации можно найти здесь: Уровни изоляции транзакций с примерами на PostgreSQL, Уровни изолированности транзакций для самых маленьких

Аномалии

Подробнее

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

Основные типы аномалий включают:

Потерянное обновление (Lost Update): Происходит, когда два или более транзакций пытаются одновременно обновить одну и ту же запись. В результате одно из обновлений может быть потеряно.

-- Транзакция 1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Транзакция 2
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;

Грязное чтение (Dirty Read): Происходит, когда одна транзакция читает данные, которые были изменены другой транзакцией, но еще не зафиксированы. Если другая транзакция откатывается, данные, считанные первой транзакцией, окажутся неверными.

-- Транзакция 1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Не фиксируем изменения

-- Транзакция 2
SELECT balance FROM accounts WHERE account_id = 1;
-- Читает измененный баланс

Неповторяющееся чтение (Non-repeatable Read): Происходит, когда данные, прочитанные транзакцией, изменяются другой транзакцией до завершения первой транзакции. В результате первая транзакция видит разные данные при повторном чтении.

-- Транзакция 1
SELECT balance FROM accounts WHERE account_id = 1;
-- Читает баланс

-- Транзакция 2
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;
COMMIT;

-- Транзакция 1
SELECT balance FROM accounts WHERE account_id = 1;
-- Читает измененный баланс

Фантомное чтение (Phantom Read): Происходит, когда одна транзакция несколько раз выполняет один и тот же запрос, и между этими запросами другая транзакция добавляет или удаляет строки, соответствующие этому запросу. В результате первая транзакция получает разные наборы строк.

-- Транзакция 1
SELECT * FROM accounts WHERE balance > 1000;
-- Получает набор строк

-- Транзакция 2
INSERT INTO accounts (account_id, balance) VALUES (3, 1500);
COMMIT;

-- Транзакция 1
SELECT * FROM accounts WHERE balance > 1000;
-- Получает другой набор строк

Для предотвращения этих аномалий используются различные уровни изоляции транзакций (Read Uncommitted, Read Committed, Repeatable Read, Serializable). Более высокий уровень изоляции обеспечивает большую защиту от аномалий, но может снижать производительность системы.

Блокировки

Подробнее

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

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

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

Гранулярность блокировок

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

Типы блокировок

Shared Lock (S-лок):

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

Exclusive Lock (X-лок):

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

Intent Locks (I-локи):

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

Типы: Intent Shared (IS), Intent Exclusive (IX) и Shared with Intent Exclusive (SIX) образуют иерархию блокировок разной гранулярности, что позволяет СУБД быстро проверять совместимость блокировок на разных уровнях (таблица/страница/строка), не сканируя все блокировки нижнего уровня.

Пример использования эксклюзивной и общей блокировок:

BEGIN TRANSACTION;

-- Установка эксклюзивной блокировки на строку
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Установка общей блокировки на строку
SELECT balance
FROM accounts WITH (HOLDLOCK)
WHERE account_id = 2;

COMMIT TRANSACTION;

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

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

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

Условия Коффмана

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

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

Диаграммы Холта (Holt)

Отслеживать возникновение взаимных блокировок удобно на диаграммах Холта (Holt). Диаграмма Холта представляет собой направленный граф, имеющий два типа узлов: процессы (показываются кружочками) и ресурсы (показываются квадратиками). Тот факт, что ресурс получен процессом и в данный момент занят этим процессом, указывается ребром (стрелкой) от ресурса к процессу. Ребро, направленное от процесса, к ресурсу, означает, что процесс в данный момент блокирован и находится в состоянии ожидания доступа к соответствующему ресурсу.

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

СУБД включает механизмы для обнаружения и разрешения дедлоков. Один из подходов - построение графа ожидания (wait-for graph) и выявление циклов в этом графе.

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

Динамический тупик (Livelock)

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

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

Лайвлок - это подмножество более широкого набора проблем, называемых голоданием.

Голодание (Starvation)

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

При лайвлоке все параллельные процессы одинаково “голодают”, и никакая работа не выполняется до конца.

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

Управление Локами

Lock Manager:

Компонент системы управления базами данных (СУБД), который управляет всеми запросами на блокировки. Обрабатывает запросы на блокировки и освобождение блокировок, разрешает конфликты, и управляет очередями ожидания.

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

Больше информации можно найти здесь: Блокировки, Блокировки в PostgreSQL: 1. Блокировки отношений, Блокировки в PostgreSQL: 2. Блокировки строк, Блокировки в PostgreSQL: 3. Блокировки других объектов, Блокировки в PostgreSQL: 4. Блокировки в памяти

Оптимистичные и пессимистичные блокировки

Подробнее

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

Пессимистичная блокировка (Pessimistic Locking)

Исходит из того, что конфликты вероятны, поэтому данные блокируются заранее — ещё до изменения. Другие транзакции вынуждены ждать освобождения блокировки. Реализуется, например, через SELECT ... FOR UPDATE (см. SELECT FOR UPDATE и SELECT FOR SHARE).

Плюсы: гарантированно нет потерянных обновлений; подходит при высокой конкуренции за одни и те же данные.
Минусы: блокировки снижают параллелизм, увеличивают время ожидания и риск взаимоблокировок (deadlock).

Оптимистичная блокировка (Optimistic Locking)

Исходит из того, что конфликты редки, поэтому данные не блокируются. Вместо этого при сохранении проверяется, не изменил ли кто-то запись с момента её чтения. Обычно для этого в таблицу добавляют столбец версии (version) или метку времени:

-- Прочитали строку с version = 5, что-то поменяли и сохраняем:
UPDATE products
SET price = 100, version = version + 1
WHERE product_id = 1 AND version = 5;

Если другая транзакция уже изменила строку (и version стал другим), UPDATE не затронет ни одной строки — это сигнал о конфликте, и операцию нужно повторить.

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

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

MVCC

Подробнее

MVCC (Multi-Version Concurrency Control) — это механизм управления параллелизмом в базах данных, который позволяет нескольким транзакциям читать и записывать данные одновременно, не блокируя друг друга. Это особенно полезно для повышения производительности в системах с высокой степенью параллелизма.

Основные принципы MVCC:

Множество версий данных: При каждом изменении строки данных создается новая версия этой строки, а старая версия сохраняется до тех пор, пока активны транзакции, которые могут её видеть. Это позволяет транзакциям читать данные, которые были актуальны на момент их начала, даже если параллельные транзакции вносят изменения.
Изоляция транзакций: MVCC позволяет добиться изоляции транзакций без блокировки данных для чтения. Это достигается за счет того, что каждая транзакция видит свою "срез" данных на момент начала.
Параллелизм:
- Чтение: Транзакции могут читать данные, не блокируя другие транзакции, поскольку каждая транзакция видит свою версию данных.
- Запись: Когда транзакция обновляет данные, создается новая версия строки. Таким образом, параллельные транзакции могут читать старые версии строки, не ожидая завершения записи.

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

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

Недостатки:

Необходимость управления множеством версий данных, что может увеличить объем хранимой информации.
Старые версии данных нужно периодически очищать (garbage collection), чтобы не перегружать систему.

Пример работы MVCC:

Представим, что транзакция T1 начала читать данные, когда значение в строке было 100. Затем транзакция T2 обновляет эту строку до 200. Транзакция T1 продолжает видеть значение 100, потому что это значение было актуально на момент её начала. В то же время транзакция T3, которая началась позже, видит новое значение 200.

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

Больше информации можно найти здесь: Newbie Guide: разбираемся с MVCC на простых примерах

Или в официальной документации (например, для PostgreSQL) (en): Chapter 13. Concurrency Control

Журнал предзаписи (WAL)

Подробнее

Журнал предзаписи (WAL, Write-Ahead Log) — это механизм, лежащий в основе надёжности большинства СУБД. Его главный принцип: любое изменение сначала записывается в журнал (на диск), и только потом применяется к самим данным. В разных СУБД он называется по-разному: WAL в PostgreSQL, redo log в Oracle и MySQL (InnoDB), transaction log в SQL Server.

Зачем это нужно

Запись в последовательный журнал гораздо быстрее, чем запись изменений в разные места файлов данных на диске. Благодаря WAL СУБД может подтвердить транзакцию (COMMIT), записав лишь компактную запись в журнал, а сами страницы данных обновить на диске позже. При этом гарантируется долговечность — свойство D в ACID.

Восстановление после сбоя

Если сервер аварийно завершился, при следующем запуске СУБД читает журнал и:

повторяет (redo) изменения зафиксированных транзакций, которые ещё не успели попасть в файлы данных;
откатывает (undo) изменения незавершённых транзакций.

Так база возвращается в согласованное состояние без потери подтверждённых данных.

Где ещё используется WAL:

Репликация — журнал передаётся на реплики, которые «проигрывают» его и поддерживают свою копию данных в актуальном состоянии (см. Репликация).
Восстановление на момент времени — журнал позволяет реализовать PITR (см. Резервное копирование и восстановление).

SELECT FOR UPDATE и SELECT FOR SHARE

Подробнее

В SQL конструкции SELECT FOR UPDATE и SELECT FOR SHARE используются для управления параллелизмом в транзакциях и блокировки строк, чтобы предотвратить конкурентные изменения данных. Эти команды важны для обеспечения целостности данных в многопользовательских системах.

SELECT FOR UPDATE

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

Только транзакция, которая выполнила SELECT FOR UPDATE, может изменять заблокированные строки. Другие транзакции не могут изменить, удалить или повторно заблокировать эти строки (FOR UPDATE/FOR SHARE) до завершения транзакции, однако обычное чтение (SELECT без блокировки) остаётся доступным — благодаря MVCC возвращается последняя зафиксированная версия строки.

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

Пример:

BEGIN;
SELECT * FROM employees WHERE department = 'Sales' FOR UPDATE;
-- Теперь строки, которые принадлежат отделу Sales, заблокированы.
-- Продолжаем работу с этими строками.
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';
COMMIT;

В этом примере транзакция сначала выбирает сотрудников отдела 'Sales' и блокирует эти строки для дальнейшего обновления. Другие транзакции не могут изменять или удалять эти строки до завершения транзакции.

SELECT FOR SHARE

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

Никто не может изменять или удалять строки, которые были выбраны с помощью SELECT FOR SHARE, пока текущая транзакция не завершится.

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

Пример:

BEGIN;
SELECT * FROM products WHERE category = 'Electronics' FOR SHARE;
-- Чтение данных, но блокировка на изменение этих строк другими транзакциями.
-- В другой транзакции не удастся выполнить UPDATE или DELETE этих строк до завершения текущей транзакции.
COMMIT;

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

Различия:

FOR UPDATE: Блокирует строки для всех транзакций, кроме текущей. Только текущая транзакция может изменять данные.
FOR SHARE: Разрешает другим транзакциям читать строки, но не позволяет изменять или удалять их.
Обе конструкции помогают управлять параллельными изменениями данных и предотвращать состояния гонки (race conditions).

Распределённые транзакции

Подробнее

Распределённая транзакция — это транзакция, которая затрагивает несколько независимых узлов (баз данных или сервисов) и должна выполниться на всех них по принципу «всё или ничего». Это сложная задача: обычные ACID-гарантии работают в пределах одной БД, а здесь нужно согласовать несколько систем, каждая из которых может отказать.

Двухфазная фиксация (2PC, Two-Phase Commit)

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

Фаза подготовки (prepare): координатор спрашивает все узлы, готовы ли они зафиксировать изменения. Каждый узел выполняет операцию, но не фиксирует её, и отвечает «готов» или «не готов».
Фаза фиксации (commit): если все ответили «готов», координатор даёт команду зафиксировать; если хоть кто-то отказался — команду откатить.

Минусы: 2PC — блокирующий протокол: если координатор откажет между фазами, узлы останутся с заблокированными ресурсами в неопределённом состоянии. Кроме того, он медленный и плохо масштабируется.

Saga (сага)

В микросервисной архитектуре вместо 2PC часто применяют паттерн Saga: распределённая операция разбивается на последовательность локальных транзакций в каждом сервисе. Если один из шагов завершается неудачей, выполняются компенсирующие транзакции, которые отменяют эффект уже выполненных шагов (например, «отменить бронь», «вернуть деньги»).

Плюсы: не держит блокировки между сервисами, хорошо масштабируется.
Минусы: нет строгой изоляции — система проходит через промежуточные несогласованные состояния и приходит к согласованности «в конечном счёте» (см. теорему CAP и BASE).

Подход	Согласованность	Масштабируемость	Применение
2PC	Строгая (атомарность)	Низкая	Несколько БД, нужна атомарность
Saga	В конечном счёте	Высокая	Микросервисы, длительные операции

Безопасность

Управление доступом

Подробнее

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

Аутентификация (Authentication) — проверка того, что пользователь является тем, за кого себя выдаёт (логин и пароль, сертификаты, внешние провайдеры).
Авторизация (Authorization) — определение того, какие действия разрешены уже аутентифицированному пользователю.

Привилегии и роли

Доступ выдаётся в виде привилегий на объекты — прав на конкретные операции (SELECT, INSERT, UPDATE, DELETE, EXECUTE и т.д.) над таблицами, представлениями, процедурами. Чтобы не выдавать права каждому пользователю по отдельности, применяют роли (RBAC, Role-Based Access Control): привилегии назначаются роли, а роль — пользователям.

Управление правами выполняется операторами группы DCL (см. SQL):

-- Создание роли и выдача прав только на чтение
CREATE ROLE analyst;
GRANT SELECT ON orders TO analyst;

-- Назначение роли пользователю
GRANT analyst TO ivan;

-- Отзыв права
REVOKE SELECT ON orders FROM analyst;

Принцип минимальных привилегий (Least Privilege)

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

Дополнительные механизмы:

Разграничение на уровне столбцов — выдача прав на отдельные столбцы или сокрытие столбцов через представления.
Защита на уровне строк (Row-Level Security) — политики, ограничивающие доступ к конкретным строкам (например, пользователь видит только свои записи).

Больше информации: PostgreSQL — Database Roles

Шифрование данных

Подробнее

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

Шифрование при передаче (in transit)

Защищает данные, пока они идут по сети между клиентом и сервером. Реализуется по протоколу TLS/SSL и предотвращает перехват и подмену данных (атака «человек посередине»). Практически все современные СУБД поддерживают подключение по TLS.

Шифрование при хранении (at rest)

Защищает данные, записанные на диск. Основные подходы:

Прозрачное шифрование (TDE, Transparent Data Encryption) — СУБД шифрует файлы данных целиком, прозрачно для приложений; защищает от кражи физических носителей или файлов.
Шифрование на уровне столбцов — шифруются только отдельные чувствительные поля (номера карт, паспортные данные). Это точечнее, но усложняет поиск и индексацию по таким полям.
Шифрование диска или файловой системы — выполняется на уровне ОС, ниже СУБД.

Шифрование и хеширование — это разные вещи

Важно не путать два понятия:

Шифрование — обратимое преобразование: данные можно расшифровать обратно, имея ключ. Применяется к данным, которые нужно читать (например, к номеру телефона).
Хеширование — необратимое преобразование. Применяется в первую очередь к паролям: в базе хранят не сам пароль, а его хеш (с использованием стойких алгоритмов — bcrypt, scrypt, Argon2 — и «соли»). При входе сравнивают хеши, а исходный пароль восстановить нельзя.

Управление ключами

Стойкость шифрования определяется не столько алгоритмом, сколько защитой ключей. Ключи нельзя хранить рядом с зашифрованными данными; для их безопасного хранения и ротации используют специальные хранилища (KMS, HashiCorp Vault, аппаратные модули HSM).

Аудит и журналирование

Подробнее

Журналирование (Logging) и аудит (Audit) связаны, но решают разные задачи.

Журналирование — это запись событий, происходящих в системе: ошибок, медленных запросов, подключений, запусков и остановок сервера. Главная цель — диагностика и поиск проблем.
Аудит — это целенаправленная фиксация того, кто, что и когда сделал с данными, в целях безопасности и соответствия требованиям (compliance). Аудит отвечает на вопрос «кто изменил эту запись и когда?».

Что обычно фиксируют при аудите:

успешные и неуспешные попытки входа (аутентификация);
обращения к чувствительным данным (SELECT по защищённым таблицам);
изменения данных (INSERT/UPDATE/DELETE);
изменения структуры (DDL) и прав доступа (DCL).

Как реализуется аудит:

Триггеры аудита — на таблицы вешаются триггеры, которые при каждом изменении записывают в отдельную таблицу историю: старое и новое значение, пользователя и время.
Встроенные средства СУБД — специализированные расширения и подсистемы: pgAudit (PostgreSQL), SQL Server Audit, Oracle Audit Vault.
Журналы сервера — текстовые логи СУБД, куда можно выводить выполняемые запросы.

Журнал транзакций — это не про аудит

Не следует путать аудит с журналом транзакций (WAL в PostgreSQL, redo log в Oracle, transaction log в SQL Server). Этот журнал предназначен для обеспечения долговечности (ACID) и восстановления после сбоев, а не для отслеживания действий пользователей, хотя физически он тоже фиксирует все изменения.

Зачем это нужно:

Безопасность — расследование инцидентов, обнаружение подозрительной активности.
Соответствие требованиям — многие стандарты (GDPR, PCI DSS, 152-ФЗ) требуют ведения журналов доступа к данным.
Восстановление и отладка — анализ того, что и когда произошло с данными.

SQL-инъекции

Подробнее

SQL-инъекция (SQL Injection) — одна из самых распространённых и опасных уязвимостей. Она возникает, когда пользовательский ввод напрямую подставляется в текст SQL-запроса. Злоумышленник может внедрить в ввод собственный SQL-код и выполнить его — прочитать чужие данные, обойти аутентификацию, изменить или удалить данные.

Как это происходит

Допустим, приложение строит запрос склейкой строк:

"SELECT * FROM users WHERE login = '" + ввод + "'"

Если в поле ввода передать ' OR '1'='1, запрос превратится в:

SELECT * FROM users WHERE login = '' OR '1'='1';

Условие '1'='1' всегда истинно, поэтому запрос вернёт всех пользователей. Ещё опаснее ввод вида '; DROP TABLE users; --, который может удалить таблицу.

Защита

Параметризованные запросы (prepared statements) — главный и самый надёжный способ. Данные передаются отдельно от текста запроса, поэтому никогда не интерпретируются как SQL-код:

-- Значение подставляется как параметр, а не как часть кода запроса
SELECT * FROM users WHERE login = ?;

ORM и query builder — современные библиотеки по умолчанию используют параметризацию.
Принцип минимальных привилегий — у учётной записи приложения должно быть только необходимое (см. Управление доступом), чтобы ограничить ущерб при успешной атаке.
Валидация и экранирование ввода — вспомогательная мера; полагаться только на неё нельзя.
Хранимые процедуры — могут снижать риск, но лишь если внутри них тоже не выполняется динамический SQL со склейкой строк.

Главное правило: никогда не доверяйте пользовательскому вводу и не вставляйте его в запрос конкатенацией строк — используйте параметризованные запросы.

Дополнительная информация

Архитектура

Подробнее

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

Клиент-серверная архитектура

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

Многоуровневая (tier) архитектура приложений

С точки зрения приложения, работающего с БД, выделяют:

Одноуровневую (1-tier) — приложение и БД на одной машине (например, встроенная SQLite).
Двухуровневую (2-tier) — «клиент → сервер БД» напрямую.
Трёхуровневую (3-tier) — «клиент → сервер приложений → сервер БД». Бизнес-логика вынесена в средний слой, что повышает масштабируемость и безопасность (клиент не обращается к базе напрямую).

Внутренние компоненты СУБД

Внутри сервер базы данных обычно состоит из:

Обработчик запросов (Query Processor) — разбирает SQL, строит и оптимизирует план выполнения (см. EXPLAIN).
Подсистема хранения (Storage Engine) — отвечает за чтение и запись данных и индексов на диск.
Менеджер буферов (Buffer Manager) — кэширует страницы данных в памяти (см. Механизмы кэширования).
Менеджер транзакций (Transaction Manager) — обеспечивает свойства ACID, управляет блокировками и журналом.

OLTP и OLAP

По характеру нагрузки системы делят на два класса:

Критерий	OLTP (транзакционные)	OLAP (аналитические)
Назначение	Обработка операций в реальном времени	Анализ и отчётность по большим объёмам
Типичные запросы	Короткие, частые, на запись и чтение	Сложные, редкие, преимущественно на чтение
Схема данных	Нормализованная	Денормализованная (звезда/снежинка)
Примеры	Интернет-магазин, банк, CRM	Хранилища данных, BI, дашборды

Разделение нагрузки на OLTP и OLAP — одна из причин, по которой данные из транзакционных систем переносят в аналитические хранилища с помощью ETL-процессов.

Строковое и колоночное хранение

Подробнее

То, как физически организованы данные на диске, сильно влияет на производительность разных типов нагрузки. Различают два основных способа хранения.

Строковое хранение (Row-oriented)

Данные хранятся построчно: все значения одной строки лежат рядом. Это классический способ для большинства реляционных СУБД (PostgreSQL, MySQL, Oracle).

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

Колоночное хранение (Column-oriented)

Данные хранятся по столбцам: все значения одного столбца лежат рядом. Так устроены аналитические СУБД (ClickHouse, Vertica, Amazon Redshift) и колоночные индексы.

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

Критерий	Строковое (row)	Колоночное (column)
Оптимально для	OLTP (запись, вся строка)	OLAP (аналитика по столбцам)
Чтение всей строки	Быстро	Медленно
Агрегация по столбцу	Медленно	Быстро
Сжатие данных	Хуже	Лучше

Теорема CAP

Подробнее

Теорема CAP (известная также как теорема Брюера) — эвристическое утверждение о том, что в любой реализации распределённых вычислений возможно обеспечить не более двух из трёх следующих свойств:

согласованность данных (англ. consistency) — во всех вычислительных узлах в один момент времени данные не противоречат друг другу;
доступность (англ. availability) — любой запрос к распределённой системе завершается откликом, однако без гарантии, что ответы всех узлов системы совпадают;
устойчивость к фрагментации (англ. partition tolerance) — расщепление распределённой системы на несколько изолированных секций не приводит к некорректности отклика от каждой из секций.

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

Следствия

С точки зрения теоремы CAP, распределённые системы вычислений в зависимости от пары практически поддерживаемых свойств из трёх возможных распадаются на три класса — CA, CP, AP (при этом в рамках одной программной системы могут быть реализованы стратегии обработки различных запросов с использованием различных систем вычислений).

В системе вычислений класса CA во всех узлах данные согласованы и обеспечена доступность, при этом она жертвует устойчивостью к распаду на секции. Такие системы возможны на основе технологического программного обеспечения, поддерживающего транзакционность в смысле ACID, примерами реализации таких систем вычислений могут быть решения на основе кластерных систем управления базами данных или распределённая служба каталогов LDAP.
Система вычислений класса CP в каждый момент обеспечивает целостный результат и способна функционировать в условиях распада, но достигает этого в ущерб доступности: может не выдавать отклик на запрос. Устойчивость к распаду на секции требует обеспечения дублирования изменений во всех узлах программной системы, реализующей данную систему вычислений, в связи с этим отмечается практическая целесообразность использования в таких системах распределённых пессимистических блокировок для сохранения целостности.
В системе вычислений класса AP не гарантируется целостность, но при этом выполнены условия доступности и устойчивости к распаду на секции. Хотя реализации систем вычислений такого рода известны задолго до формулировки принципа CAP (например, распределённые веб-кэши или DNS), рост популярности решений с этим набором свойств связывается именно с распространением теоремы CAP. Так, большинство NoSQL-систем принципиально не гарантируют целостности данных, и ссылаются на теорему CAP как на мотив такого ограничения. Задачей при построении AP-систем становится обеспечение некоторого практически целесообразного уровня целостности данных, в этом смысле про AP-системы говорят как о «целостных в конечном итоге» (англ. eventually consistent) или как о «слабо целостных» (англ. weak consistent).

BASE-архитектура

Во второй половине 2000-х годов сформулирован подход к построению распределённых систем, в которых требования целостности и доступности выполнены не в полной мере, названый акронимом BASE (от англ. Basically Available, Soft-state, Eventually consistent — базовая доступность, неустойчивое состояние, согласованность в конечном счёте), при этом такой подход напрямую противопоставляется ACID.

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

Больше информации с примерами систем можно найти здесь: Всё, что вы не знали о CAP теореме

Name		Name	Last commit message	Last commit date
gif		gif
img		img
svg		svg
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation

databases-101

План

Введение в базы данных

Базы данных и СУБД

NoSQL

ETL-процессы

ER-диаграмма

Ключи

Нормальные формы

Нормализация баз данных

1. Ненормализованная форма или нулевая нормальная форма (UNF) базы данных

2. Первая нормальная форма (1NF) базы данных

3. Вторая нормальная форма (2NF) базы данных

4. Третья нормальная форма (3NF)

5. Нормальная форма Бойса-Кодда (BCNF)

N.B

Денормализация форм

Схемы "звезда" и "снежинка"

Основы SQL

SQL

Создание таблиц, типы данных и ограничения

Изменение данных (INSERT, UPDATE, DELETE)

SELECT

Агрегатные функции

WHERE и HAVING

NULL и трёхзначная логика

ORDER BY

DISTINCT, LIMIT и OFFSET

CASE

Объединения таблиц

Операции над множествами

Вложенные запросы

Расширенные возможности SQL

Функции

Аналитические функции

Общие табличные выражения (CTE)

Представления

Хранимые процедуры

Триггеры

Оптимизация и производительность

EXPLAIN

Индексы

Масштабирование

Партицирование

Шардинг

Репликация

Механизмы кэширования

Пул соединений

Резервное копирование и восстановление

Транзакции и согласованность данных

Транзакции

ACID

Уровни изоляции транзакций

Аномалии

Блокировки

Оптимистичные и пессимистичные блокировки

MVCC

Журнал предзаписи (WAL)

SELECT FOR UPDATE и SELECT FOR SHARE

Распределённые транзакции

Безопасность

Управление доступом

Шифрование данных

Аудит и журналирование

SQL-инъекции

Дополнительная информация

Архитектура

Строковое и колоночное хранение

Теорема CAP

SQL на практике

EN

RU

Справочники

EN

RU

Практические задания