SQL Flashcards
(11 cards)
Транзакции
Последовательность операций с базой данных, которая выполняется как единое целое по принципу «всё или ничего». Если одна операция в цепочке завершится ошибкой — отменятся все изменения в рамках транзакции. Это гарантирует целостность данных даже при сбоях.
За счет каких свойств СУБД обеспечивается Durability (долговечность) из принципов ACID?
1)Журналирование (Logging): СУБД ведёт журнал транзакций (log), в который записываются все изменения, производимые транзакциями. Это позволяет системе иметь запись обо всех операциях, которые были выполнены, и в случае сбоя восстановить базу данных до последнего согласованного состояния.
2)Буферизация и кэширование: СУБД использует буферы и кэши для временного хранения данных. Однако перед тем как считать операцию выполненной, система гарантирует, что все изменения будут записаны на физический носитель (например, на диск). Это обеспечивает сохранность данных даже в случае сбоя системы.
3)Механизмы восстановления (Recovery Mechanisms): СУБД имеет механизмы для восстановления базы данных после сбоя. Это может включать в себя повторное выполнение транзакций на основе журнала (redo) или откат транзакций (undo), чтобы вернуть базу данных в согласованное состояние.
4)Синхронное и асинхронное сохранение данных: СУБД может использовать различные стратегии сохранения данных на диск. Синхронное сохранение гарантирует, что данные будут записаны на диск перед подтверждением завершения транзакции, что обеспечивает долговечность, но может снизить производительность. Асинхронное сохранение может повысить производительность, но требует дополнительных механизмов для обеспечения долговечности в случае сбоя.
5)Резервное копирование и восстановление (Backup and Restore): хотя это не является прямым механизмом обеспечения долговечности в рамках ACID, резервное копирование данных и возможность их восстановления также играют важную роль в обеспечении долговечности данных в долгосрочной перспективе.
Журнал транзакций (или лог транзакций)
Это специальный файл или набор файлов в системе управления базами данных (СУБД), в котором подробно записываются все изменения, производимые транзакциями в базе данных. Каждая запись в журнале содержит информацию о конкретных операциях, таких как вставка, обновление или удаление данных, а также метаданные, включая временные метки и идентификаторы транзакций.
Журнал транзакций служит нескольким целям:
Обеспечение долговечности данных (Durability): в случае сбоя системы журнал позволяет восстановить базу данных до последнего согласованного состояния, повторно выполнив записанные транзакции.
Откат транзакций (Rollback): если транзакция не может быть завершена успешно, журнал используется для отмены всех изменений, произведённых этой транзакцией, чтобы сохранить целостность данных.
Точка восстановления (Recovery Point): журнал позволяет определить точку, до которой можно восстановить базу данных, обеспечивая возможность вернуться к предыдущему согласованному состоянию.
Аудит и мониторинг: записи в журнале могут быть использованы для анализа действий пользователей, отслеживания изменений в базе данных и выявления возможных проблем или аномалий.
Почему может быть только 1 кластеризованный индекс?
Кластеризованный индекс в базе данных организует данные таблицы в определённом порядке, соответствующем ключу индекса. Это означает, что строки данных физически упорядочены в таблице согласно значениям ключа кластеризованного индекса. Из-за этого в таблице может быть только один кластеризованный индекс, поскольку данные могут быть упорядочены только одним способом.
Если бы в таблице было более одного кластеризованного индекса, это привело бы к противоречию в порядке хранения данных, так как строки не могут быть одновременно упорядочены двумя разными способами. Вместо этого для дополнительной индексации используются некластеризованные индексы, которые содержат ссылки на строки данных в таблице, но не изменяют порядок их хранения.
Где хранится индекс?
Индекс в системе управления базами данных (СУБД) хранится в виде отдельного объекта, отдельно от самих данных таблицы. Это позволяет СУБД быстро получать доступ к нужным данным без необходимости сканирования всей таблицы.
В зависимости от типа индекса и настроек СУБД, индексы могут храниться:
На диске в виде файлов. Индексы сохраняются в специальных файлах, которые оптимизированы для быстрого доступа к данным.
В оперативной памяти (RAM). Для ускорения доступа к часто используемым индексам СУБД может кэшировать их в оперативной памяти. Это сокращает время доступа к данным и улучшает производительность системы.
Отличие звезды от снежинки
Звезда (Star Schema) и Снежинка (Snowflake Schema) — это две основные реляционные модели данных, используемые при проектировании хранилищ данных (Data Warehouse) для аналитики. Обе основаны на концепции моделирование измерений. Их главное отличие — в степени нормализации таблиц измерений (Dimensions).
Представьте себе:
Факт (Fact Table): Центральная таблица, хранящая “события” или “показатели”. Например: продажи (количество, сумма, дата), клики, заказы. Содержит числовые метрики (measures) и внешние ключи (foreign keys) на таблицы измерений.
Измерение (Dimension Table): Таблицы, описывающие “объекты” или “контекст” вокруг фактов. Например: товар, клиент, магазин, время. Содержат описательные атрибуты (название товара, категория, имя клиента, город, дата).
Теперь к отличиям:
Звезда:
Структура: Все таблицы измерений соединены НАПРЯМУЮ с центральной таблицей фактов. На схеме это выглядит как звезда: факт в центре, измерения — как лучи.
Нормализация: Таблицы измерений ДЕНОРМАЛИЗОВАНЫ. Это значит, что все атрибуты измерения хранятся в одной плоской таблице, даже если между ними есть иерархии. Например:
Таблица DimProduct (Товар) будет содержать: ProductID, ProductName, CategoryID, CategoryName, SubcategoryID, SubcategoryName, BrandID, BrandName.
Плюсы:
1)Простота: Легко понять и спроектировать.
2)Скорость запросов: Меньше JOIN’ов при выполнении аналитических запросов. Часто достаточно одного JOIN между фактом и измерением.
3)Оптимизация для чтения: Идеально подходит для агрегаций и срезов данных.
Минусы:
1)Избыточность данных: Атрибуты иерархий (как CategoryName) повторяются для каждого товара в этой категории. Требует больше места на диске.
2)Потенциальная несогласованность: Если изменится название категории, нужно обновить все строки товаров в этой категории (риск ошибки или устаревания данных).
3)Меньшая гибкость для изменения иерархий: Добавление нового уровня иерархии (например, SuperCategory) требует изменения структуры таблицы и перезагрузки данных.
Снежинка (Snowflake Schema):
Структура: Таблицы измерений НОРМАЛИЗОВАНЫ. Иерархические атрибуты внутри измерения выносятся в отдельные таблицы, которые соединяются с основной таблицей измерения. Схема напоминает снежинку с ответвлениями. Например:
Плюсы:
1)Экономия дискового пространства: Устраняется избыточность данных.
2)Согласованность данных: Изменение названия категории делается в одном месте.
3)Гибкость: Легче добавлять новые уровни иерархий или атрибуты в нормализованные таблицы.
4)Более “чистая” модель: Соответствует классическим правилам нормализации реляционных БД.
Минусы:
1)Сложность: Схема сложнее для понимания и проектирования.
2)Медленнее запросы: Для получения полного описания (например, имени товара вместе с его категорией) требуется выполнить больше JOIN’ов. Это может замедлить выполнение аналитических запросов, особенно при больших объемах данных и сложных фильтрах.
3)Менее удобно для BI-инструментов: Некоторые инструменты могут хуже оптимизировать запросы к снежинке или требуют от пользователя знать сложную структуру.
Нормализация
Зачем это нужно?
1)Убить дубликаты (одна запись = одно место)
2)Избежать ошибок (если адрес клиента хранится в 10 местах и меняется — вы обновите только в одном)
3)Упростить изменения (добавить новый атрибут без перелопачивания всей таблицы)
4)Сделать базу гибкой и эффективной.
1NF: Одна ячейка — одно значение. Некоторое повторение группы колонок не допускается.
2NF: Таблица должна быть в 1NF + каждый неключевой атрибут полностью зависит от всего первичного ключа.
3NF: Таблица должна быть в 2NF + никакой неключевой атрибут не зависит от другого неключевого атрибута -только от первичного ключа
Zstd
Zstandard (ZSTD) — это современный алгоритм безпотерьного сжатия данных, ставший популярным в Data Engineering благодаря уникальному балансу между:
Физические модели хранения
A. Heap Storage
Аналог PostgreSQL: Обычные таблицы.
Структура: Данные хранятся в неупорядоченном виде.
Операции: Подходит для частых UPDATE/DELETE.
Когда использовать:
1)Таблицы с OLTP-нагрузкой (частые изменения строк),
2)Системные таблицы,
3)Небольшие справочники.
B. Append-Optimized (AO) Storage
Оптимизация: Для добавления данных (APPEND), а не изменений.
Структура: Данные пишутся блоками (не постранично как в Heap).
Преимущества:
+Высокая скорость вставки (INSERT),
+Эффективное сжатие,
+Меньше накладных расходов на VACUUM.
Недостатки:
-UPDATE/DELETE работают медленно (помечают строки как удалённые, требуют VACUUM).
Когда использовать:
1)Фактовые таблицы в DWH (данные пишутся пачками, редко меняются),
2)Логи, события, IoT-данные.
C.Column-Oriented Storage (AO_COLUMN)
Колоночное хранение: Данные физически группируются по колонкам (как в Parquet).
Плюсы:
+Сжатие: В 5-10x лучше, чем в row-based (особенно для повторяющихся значений),
+Скорость аналитики: Запросы читают только нужные колонки.
Минусы:
-Медленные INSERT (из-за сжатия),
_Неэффективен для SELECT * или частых точечных обновлений.
Когда использовать:
Таблицы для BI-аналитики (агрегации, срезы по колонкам),
Широкие таблицы (100+ колонок), где нужны только некоторые поля.
Строковый и столбчатый тип хранения
Строковое хранение
Как работает:
Данные хранятся по строкам. Все поля одной записи лежат рядом физически.
Плюсы ✅
+Быстрые операции записи:
Добавить новую строку — просто дописать её в конец.
+Эффективность для точечных запросов:
SELECT * FROM users WHERE UserID = 123 — данные одной строки читаются за 1 операцию.
+Оптимально для OLTP:
Транзакции (обновление/удаление строк) работают быстро (например, банковские операции).
Минусы ❌
Медленная аналитика:
-Для SELECT AVG(age) FROM users система вынуждена читать все строки целиком, включая ненужные поля (Name, City).
-Низкое сжатие:
Разнотипные данные в строке (текст + числа) плохо сжимаются.
-Избыточное чтение:
При запросе 1 колонки читается вся строка (100+ полей).
Где используется:
› PostgreSQL, MySQL
› Системы реального времени (обработка заказов)
Столбчатое хранение (Column Store)
Как работает:
Данные хранятся по колонкам. Значения одной колонки лежат последовательно.
Плюсы ✅
1)Сверхбыстрая аналитика:
SELECT AVG(age) — читается только колонка Age, а не миллионы строк.
2)Высокое сжатие:
Однотипные данные (например, все значения Age) сжимаются в 10-100x эффективнее (алгоритмы: RLE, Delta Encoding + ZSTD).
3)Векторизация операций:
Агрегации (SUM, MIN, COUNT) выполняются за 1 проход по колонке.
Минусы ❌
-Медленная запись:
Добавление новой строки = запись в каждую колонку отдельно.
-Неэффективность для SELECT *:
Сборка полной строки из колонок — дорогая операция.
-Сложность с транзакциями:
UPDATE/DELETE могут требовать перезаписи всей колонки.
Где используется:
› Amazon Redshift, ClickHouse
› Apache Parquet, ORC)
› BI-системы (Tableau, Power BI)
