БД и SQL Flashcards
(30 cards)
В чем суть нормализации данных? Нормальные формы
Нормализация — это разбиение данных на логически связанные таблицы для:
устранения дублирования (избыточности),
обеспечения целостности данных,
упрощения обновлений.
1НФ
1. Все значения в столбцах должны быть атомартными (неделимыми). Не должно быть “ячеек”, в которых содержалось бы более одного значения
2. Каждая строка должна быть уникальной (нет дубликатов строк)
2НФ
1. Выполняются требования 1НФ
2. Все неключевые атрибуты должны зависеть только от первичного ключа
3НФ
1. Выполняются требования 2НФ
2. Не должно быть транзитивных зависимостей между неключевыми атрибутами (неключевые атрибуты не должны зависеть друг от друга)
Денормализация данных
Денормализация данных в базе данных — процесс, направленный на повышение производительности и сокращение числа операций, необходимых для извлечения и обновления информации.
Этот процесс включает в себя объединение данных из нескольких нормализованных таблиц в одну, что приводит к дублированию информации.
Денормализация может ускорить выполнение запросов и упростить структуру базы данных, однако она также может вызвать проблемы с целостностью данных и увеличить затраты на их хранение.
Денормализация — это процесс, обратный нормализации, который включает в себя объединение таблиц или добавление избыточных данных для повышения производительности запросов. Основные цели денормализации:
Ускорение извлечения данных за счет уменьшения количества операций JOIN.
Упрощение структуры базы данных для аналитических запросов.
Повышение производительности при частом выполнении определённых запросов.
Денормализация может привести к увеличению избыточности данных и потенциальным проблемам с целостностью, но она часто используется в аналитических системах, где скорость доступа к данным имеет первостепенное значение.
Когда нормализация может мешать производительности?
При частом джойне многих таблиц — например, в витринах или отчётах.
В аналитических системах (OLAP) предпочтительнее денормализованные структуры (звезда/снежинка), чтобы ускорить агрегации и уменьшить число join’ов.
Что такое SCD и для чего он нужен? Какие типы SCD знаешь (например, Type 1, Type 2)? Зачем нужно?
SCD (Slowly Changing Dimension) — это способ хранить историю изменений в измерениях (например, имя клиента, адрес).
Используется в хранилищах данных для:
отслеживания изменений,
аналитики “на момент времени”.
0 - Для данного типа данных, какие-либо изменения отсутствуют.
1— перезаписываем старое значение (без истории).
2 — создаём новую запись с временным диапазоном (valid_from/valid_to).
3 — сохраняем старое значение в отдельной колонке (ограниченно).
Чтобы хранить историчность
Можно ли еще как-то хранить историчность?
- Хронологические таблицы (Audit log / Append-only)
— вместо обновлений всегда вставлять новую запись с меткой времени и флагом «актуальности».
— очень похоже на SCD Type 2, но без флагов «close_date» — просто журнал изменений. - Таблицы со сквозным временем (Bitemporal tables)
— хранят две временные оси:
* когда запись действительна (valid_time),
* когда запись была записана (transaction_time).
— позволяют отвечать на вопросы «какие данные я знал в X-й момент». - Event Sourcing (Событийный подход)
— каждое изменение записывается как отдельное событие в логе (Kafka, event store).
— текущее состояние вычисляется последовательным применением всех событий. - Change Data Capture (CDC)
— копирование всех транзакций из OLTP-базы (Debezium, Oracle GoldenGate) в поток,
— в хранилище сохраняются эти изменения, а история накапливается «как есть». - Версионированные табличные форматы (Delta Lake, Apache Hudi, Iceberg)
— поддержка «time travel» и снапшотов: можно читать данные на любой точке времени,
— хранится полная история файлов и операций (insert/update/delete). - Materialized Views с историческими срезами
— на каждые N часов/дней строится MV с данными «на момент времени»,
— в хранилище остаётся набор снимков.
Что значит “Slowly” в SCD?
“Slowly” в SCD означает, что атрибуты размерности меняются нечасто, с “медленной” скоростью по сравнению с потоком транзакционных (фактовых) данных.
Изменения происходят редко (например, смена адреса клиента, ребрендинг продукта), а не при каждой транзакции.
Именно эти «медленные» изменения и нужно отслеживать: хранить текущую версию и при необходимости историю прошлых состояний.
Slowly хорошо когда у нас от загрузки до загрузки не очень много строчек меняется и большинство из них остаются такими же по значению и по итогу для реализации SCD 2 не так много ресурсов нужно
И действительно есть сохранение этого места хранения, потому что мы дописываем не так много строчек каждый раз
Если что-то часто меняется (каждый день), то SCD уже не будет подходить, потому что почти каждый раз будет перезаписываться большая часть таблицы и будут большие джоины потому что всё постоянно меняется
Как работает оконная функция?
Оконные функции в SQL позволяют вычислять агрегаты или ранжирования не по всему набору данных, а в пределах «окна» — динамически определяемой группы строк вокруг каждой обрабатываемой строки.
Как это работает:
Определение окна
В запросе вы задаёте ключевые части:
PARTITION BY … — делит все строки на независимые группы (partition).
ORDER BY … — внутри каждой группы определяет порядок строк.
ROWS BETWEEN … или RANGE BETWEEN … — указывает, какие строки включать относительно текущей (например, от начала группы до текущей, или ±N строк).
Вычисление функции
Для каждой строки SQL-движок:
определяет её окно (набор строк, попавших в рамки, заданные выше),
применяет оконную функцию — SUM(), AVG(), ROW_NUMBER(), LAG(), LEAD(), и т. д.
результат возвращает как значение в новой колонке для этой строки.
Как работает оконная функция без PARTITION BY?
Если не указать PARTITION BY, функция работает по всей таблице как по одной группе.
Что такое кумулятивная сумма в контексте оконных функций?
Это нарастающая сумма значений в определённом порядке.
Транзакционные и аналитические системы? Чем отличаются OLTP и OLAP?
Транзакционные системы (OLTP — Online Transaction Processing) используются для обработки большого количества коротких операций: создание, обновление, удаление данных. Примеры — банковские системы, CRM, e-commerce. Основные требования — высокая скорость обработки, обеспечение целостности данных, поддержка транзакций. Структура БД обычно нормализована, чтобы избежать дублирования.
Аналитические системы (OLAP — Online Analytical Processing) предназначены для анализа данных, построения отчётов, агрегаций и работы с историей. Они обрабатывают большие объёмы информации, но с меньшей частотой изменений. Для повышения скорости аналитики данные часто хранятся в денормализованной форме (звезда, снежинка), используются витрины и колоночные СУБД.
Главное различие: OLTP — для операций и хранения “свежих” данных, OLAP — для анализа и принятия решений на основе исторических данных.
Какие свойства ACID знаешь?
ACID – классический набор свойств транзакции в OLTP базах, которые поддерживают строгую согласованность данных в ней. Часто спрашивают расшифровку.
Atomicity (атомарность) – транзакция выполняется либо целиком, либо откатывается (rollback). Сохранение промежуточного состояния недопустимо.
Consistency (согласованность) – данные до и после выполнения транзакции должны быть согласованы (например, ссылочная целостность должна выполняться, упрощённо в связанной таблице для каждого внешнего ключа должен быть первичный ключ).
Isolation (изолированность) – все транзакции запускаются в изолированном окружении так, что не влияют друг на друга.
Durability (долговечность) – гарантирует, что после завершения транзакции все изменения данных сохраняются, даже если происходит сбой системы
Основные механизмы:
Журнал транзакций (WAL): Каждое изменение данных сначала записывается в журнал перед изменением самих данных. Если происходит сбой, журнал позволяет восстановить данные до последней успешной транзакции.
Персистентность данных на диске: Все данные сохраняются на диск и не теряются при сбоях.
Автоматическое восстановление после сбоя: Система может восстанавливать данные, исходя из содержимого журнала.
Как в Clickhouse можно с данными работать если мы не хотим нормализовывать?
В ClickHouse, наоборот, принято хранить данные в максимально денормализованном виде — это позволяет выжать всю скорость из колоночного хранилища и не тратить ресурсы на JOIN-ы. Если вы не хотите нормализовывать, можно:
Широкие таблицы
— создать одну «мастер-таблицу», в которой сразу лежат все необходимые поля для аналитики.
— хранить сотни колонок, в том числе критерии фильтрации и признаки (features) для ML.
Многозначные колонки (Array / Nested)
— вместо отдельных дочерних таблиц использовать типы Array(…) или Nested(…).
— например, tags Array(String) или
JSON / Map-типы
— хранить документ целиком в String-поле или в формате JSON, а по мере надобности извлекать через JSONExtract*.
— Map-типы (Map(String, String)) позволяют положить произвольные пары «ключ–значение» в одну колонку.
Materialized Views
— на лету поддерживать готовые агрегаты/семплы и не делать нормализацию «в живых» запросах.
— при вставке в основную (денормализованную) таблицу, MV автоматически обновляет вспомогательные таблицы.
ClickHouse Dictionaries
— если есть небольшие справочники, можно хранить их не в отдельных таблицах, а в Dictionary Engine, и делать быстрые look-ups прямо в запросе без JOIN.
Проекции (Projections)
— начиная с последних версий, ClickHouse позволяет внутри одной таблицы заранее вычислить и хранить частичные агрегаты или упорядоченные куски данных, что ещё сильнее упрощает архитектуру без нормализации.
Есть DataLake (Spark+Hadoop). Стоит ли в нем нормализировать?
Data Lake — это зона “сырых” данных (Raw Zone)
Вы храните здесь исходные источники «as-is» (логи, файлы, стримы), чтобы иметь возможность в любой момент вернуться к ним. Любая нормализация (разбиение на 3NF-таблицы, удаление дубликатов, создание справочников) — это уже преобразование, а не хранение сырья.
Schema-on-read vs Schema-on-write
В Data Lake вы обычно используете принцип Schema-on-read (читаем и интерпретируем данные по запросу). Нормализация — это схема-on-write, она жёстко фиксирует структуру при загрузке. Это противоречит идее гибкого хранилища сырых данных.
Избыточная сложность и переизбыточность
Если разделить всё на тонкие нормализованные таблицы прямо в Hadoop, вы получите тысячи маленьких файлов/партиций, сложных зависимостей и очень дорогие join-ы при аналитике. Spark-джобы будут страдать от огромного shuffle.
Лучшие практики: выделить слои
Raw Zone: ничего не трогаем, храним as-is.
Staging/Refined Zone: тут уже можно делать простые очистки и небольшую денормализацию.
Curated or DWH Zone: в этих зонах (на Hive, в Oracle/ClickHouse или Iceberg) вы нормализуете или денормализуете под конкретные BI/ML-кейсы.
Где нормализовать, если нужно?
Как правило, нормализацию делают в downstream-слоях:
в core-слое вашего DWH (3NF, Snowflake) для обеспечения целостности; в mart-слое — собирают витрины (широкие таблицы) для аналитиков и моделей.
В ACID Durability за счет чего обеспечивается?
Журнал транзакций (WAL): Каждое изменение данных сначала записывается в журнал перед изменением самих данных. Если происходит сбой, журнал позволяет восстановить данные до последней успешной транзакции.
Персистентность данных на диске: Все данные сохраняются на диск и не теряются при сбоях.
Автоматическое восстановление после сбоя: Система может восстанавливать данные, исходя из содержимого журнала.
Что такое журнал транзакций?
WAL (Write-Ahead Logging) — это метод, при котором все изменения данных сначала записываются в журнал, а затем в саму базу данных. Это позволяет системе:
Восстановить данные после сбоя.
Обеспечить согласованность данных.
В CDC (Change Data Capture) используется для отслеживания изменений в базе данных в реальном времени. Журнал фиксирует все изменения, что позволяет аналитическим системам или приложениям выявлять и обрабатывать только измененные данные
Что такое транзакционность?
Что такое индексы? Где они хранятся?
Индекс в базе данных - это специальная структура данных, которая создается для ускорения поиска и выборки данных из таблицы. Работают, подобно указателю в книге - они хранят значения столбцов и указывают на местоположение соответствующих строк в таблице. Это позволяет быстро находить нужные строки без сканирования всей таблицы.
* Применять индексы нужно не раньше, чем когда в таблице появится минимум 10000 тысяч записей. Иначе заметного прироста в скорости обработки данных не будет.
* И к тем столбцам, по которым наиболее часто ведется поиск (WHERE, GROUP BY, JOIN).
Где хранятся?
Физически индексы — это специализированные структуры данных, хранящиеся на диске отдельно от таблицы, чаще всего в виде B-деревьев (B-tree)
B-tree индекс (наиболее распространённый)
Иерархическая структура:
корень → внутренние узлы → листья.
Упорядоченные ключи: на нижнем уровне (листах) хранятся:
значения из индексируемой колонки,
указатели на строки в таблице (например, TID, rowid, ctid).
Листы связаны между собой в виде двусвязного списка — это ускоряет сканирование по диапазону (BETWEEN, ORDER BY, и т.д.).
На диске:
Хранятся как страницы фиксированного размера (например, 8 КБ).
Кластеризованные и некластеризованные индексы. Почему кластеризованный может быть один?
Кластеризованные (Clustered) индексы:
* Сортируют и физически хранят данные таблицы в определенном порядке
* Таблица может иметь только один кластеризованный индекс
* Эффективны для поиска по диапазонам значений
Некластеризованные (Non-Clustered) индексы:
* Хранятся отдельно от данных таблицы
* Таблица может иметь несколько некластеризованных индексов
* Эффективны для поиска по конкретным значениям
Кластеризованный индекс
Таблица на физическом уровне хранит строки в порядке значений кластеризованного индекса.
Поскольку данные можно физически отсортировать только по одному столбцу,
невозможно одновременно расположить строки и по user_id, и по created_at —
таблица может быть упорядочена лишь по одному из них.
Минусы индексов. Когда их лучше не использовать? Точно ли частые запросы на выборку данных индекс будет портить?
Минусы:
Замедляют INSERT, UPDATE, DELETE
— потому что индекс тоже надо обновить (вставить новое значение, удалить старое, пересортировать B-дерево и т.д.).
Занимают дополнительное место на диске.
— особенно если индекс сложный или составной.
Индекс не всегда используется
— например, если:
1. фильтрация по колонке с большим количеством NULL,
2. низкая селективность (например, поле is_active = true в 99% случаев — бесполезен),
3. запрос требует полного прохода по таблице (например, SELECT * без WHERE).
Когда лучше не использовать:
Таблица маленькая
— До 10 000 строк индекс может быть бесполезен, а иногда даже замедляет из-за лишнего оверхеда.
Часто выполняются INSERT, UPDATE, DELETE
— Индексы надо постоянно обновлять, что замедляет работу.
Таблица используется в OLAP-нагрузке
— Где обрабатываются большие объемы данных сразу, а не выборка отдельных строк. В этом случае лучше использовать партиционирование, а не индексы.
Фильтрация по колонке, где много NULL
— В некоторых СУБД такие колонки хуже индексируются или индекс не используется вовсе (зависит от реализации).
Если мы построили индекс по какому-то из столбцов (атрибутов таблицы) и решили проапдейтить выборочко 20% таблицы. Все ли будет хорошо или мы с чем-то можем столкнуться. С каким-то неприятностями
Всё будет хорошо, если:
Ты не обновляешь колонку, по которой построен индекс — в этом случае индекс не затрагивается, и обновление проходит быстро.
Возможны проблемы, если обновляется колонка, по которой построен индекс:
1. Индекс обновляется:
Сначала удаляется старая запись из индекса.
Потом добавляется новая с обновлённым значением.
Это требует CPU, памяти, и вызывает лишние I/O.
- Падение производительности UPDATE:
Особенно заметно, если обновляешь большой объём строк, и индекс — сложный (например, составной или уникальный). - Фрагментация индекса:
При частых обновлениях индекс может стать разреженным, и чтения по индексу замедлятся.
Это проявляется особенно в B-деревьях (например, в PostgreSQL, Oracle). - Кластеризованный индекс (если есть):
Таблица хранится физически в порядке значений ключа.
Обновление таких значений требует перемещения строк в таблице → больше I/O → возможно, блокировки. - Рост журнала транзакций (WAL):
Каждое обновление индексной записи тоже логируется.
Это может временно перегрузить систему, если обновлений много.
Как избежать проблем с индексами при массовом UPDATE:
1. Обновляй данные батчами
Не делай UPDATE всей таблицы сразу. Разбей обновление на части — по 1000–5000 строк за один запрос. Это уменьшит нагрузку на диск, память и транзакционный журнал.
- Избегай обновления колонок, по которым построены индексы
Если это возможно — лучше не менять значения в индексируемых колонках. Такие изменения требуют удаления и вставки значений в индекс, что гораздо дороже. - Если нужно обновить много строк по индексной колонке — подумай об удалении и вставке
Иногда быстрее и безопаснее удалить строки и заново вставить обновлённые, чем выполнять массовый UPDATE. - Периодически перестраивай индексы (REINDEX)
После большого количества UPDATE или DELETE индекс может “раздуться” (bloat) и начать тормозить. REINDEX поможет восстановить его эффективность. - Следи за фрагментацией и размером индексов
В PostgreSQL можно использовать pg_stat_user_indexes или pgstattuple, чтобы узнать, насколько индекс раздут и фрагментирован. При необходимости — VACUUM или REINDEX. - Смотри на нагрузку на WAL/undo-логи
В PostgreSQL, Oracle и других СУБД массовые обновления создают сильную нагрузку на журнал транзакций. Это может привести к замедлениям, особенно если в это время идут другие активные операции.
Какие основные слои в DWH?
У DWH
1. Staging
2. Core слой
3. Data mart layer
- Raw Layer (Staging / Bronze / Источник как есть)
Сюда данные загружаются в неизменённом виде — как пришли из источников - Core Layer (Business Core / Integrated / Silver)
Это центральный слой, где данные уже:
очищены,
обогащены справочниками,
приведены к бизнес-логике.
Тут реализуются SCD, бизнес-правила, агрегаты.
Часто строится по Data Vault / 3NF / Kimball (звезда/снежинка).
- Data Mart / Marts Layer (Presentation / Gold)
Это витрины данных под конкретные задачи аналитиков или отчётов.
Могут быть денормализованы (звёзды), содержат агрегаты, отчётные метрики.
Оптимизированы под быстрые SELECT-запросы.
Модели данных в каком-то слое? В каком слое была? Какие есть модели данных? Anchor? Data Vault? Слышала про них?
Моделирование данных применяется в первую очередь на Core Layer и Data Mart Layer, но цели разные:
- Core Layer — «истина», гибкость, историчность
Здесь применяются нормализованные модели, заточенные под:
объединение источников,
поддержку истории (SCD),
гибкое расширение.
Типичные модели:
3NF (Третья нормальная форма) — классическая реляционная модель.
Data Vault — для сложных интеграций, гибкости и масштабирования.
Anchor Modeling — расширенная нормализация, часто применяется в аналитических DWH.
- Data Mart Layer — для аналитиков, отчётов
Здесь — денормализованные модели, чтобы быстро считать отчёты, KPI, графики.
Типичные модели:
Star Schema (звезда) — факт + измерения (простая, быстрая, понятная).
Snowflake Schema (снежинка) — измерения тоже нормализованы (сложнее, но экономит место).
Какие основные типы таблиц в снежинке? Как отличается снежинка от звезды?
Это два подхода к организации данных в витринах (data marts) хранилища данных. Оба применяются в моделировании по Кимбалу.
Star Schema (звезда)
Простая модель, в которой факты соединены с измерениями через ключи.
Структура:
Факт — одна центральная таблица
Измерения — денормализованные таблицы, напрямую связаны с фактом
Плюсы:
Простая структура, легко читается
Быстрее в запросах — меньше джойнов
Удобна для BI-инструментов
Минусы:
Дублирование данных в измерениях
Увеличенный объем хранения
Snowflake Schema (снежинка)
Более сложная версия модели звезды, где измерения дополнительно нормализованы.
Отличие: В снежинке измерения дополнительно разделены на подкатегории.
Структура:
Факт — как и в звезде
Измерения — нормализованы, разбиты на подтаблицы (например: страна → город → магазин)
Плюсы:
Меньше дублирования данных
Соблюдение принципов нормализации
Удобна при сложных иерархиях
Минусы:
Больше джойнов — ниже производительность
Сложнее поддержка и понимание
Меньшая совместимость с BI-инструментами
Факт (fact table):
Таблица, хранящая числовые показатели (метрики) бизнес-процессов, например:
Сумма продаж
Количество заказов
Выручка
Затраты
Признаки:
Содержит ключи на измерения
Содержит агрегируемые значения (например, SUM, COUNT)
Измерения (dimension tables)
Таблицы, содержащие описания объектов, по которым можно группировать и фильтровать факты.
Примеры измерений:
Дата (день, месяц, квартал)
Продукт (название, категория, бренд)
Клиент (возраст, регион, сегмент)
Магазин (город, адрес, тип)
Data Vault и Anchor Modeling
Data Vault
Применяется: Core Layer
Назначение: Гибкое, масштабируемое и историчное хранилище данных
Структура:
Hub — ключевые бизнес-сущности (например, user_id, product_id)
Link — связи между сущностями (например, user ↔ order)
Satellite — атрибуты и история изменений (например, имя, email, статус)
Особенности:
Поддерживает SCD-2
Хорошо масштабируется
Удобен для объединения разнородных источников
Требует больше таблиц, чем классическая модель
Anchor Modeling
Применяется: Core Layer
Назначение: Максимально нормализованная и гибкая модель для хранения истории
Суть:
Каждое свойство сущности хранится в отдельной таблице
Все изменения версионируются
Легко расширять: добавление нового поля не требует изменения существующих таблиц
Особенности:
Поддерживает полную историчность
Максимально нормализована
Очень много таблиц, повышенная сложность запросов и поддержки
