Базы данных, DWH Flashcards
OLTP vs OLAP
OLTP для дата инженера это источник данных, production база или её реплика, откуда забираем данные. Строковый тип хранения, работа преимущественно с одной строкой за раз, частые операции вставки/изменения/чтения/удаления. Много простых запросов с высокой степенью concurrency (два и более запроса с пересекающимся временным диапазоном запуска, которые обращаются к одному объекту БД). Может выполнять аналитические запросы, но не оптимизирована для них. Обычно на порядки (в десятки и сотни раз) меньше OLAP базы. Редко хранит историю, чаще SCD1 (перезапись). С ней чаще работают приложения, а не люди напрямую. Примеры: Postgres, MySQL, SQL Server.
OLAP это приёмник данных, зачастую – собственно и представляет собой хранилище (DWH). Может выполнять транзакционные запросы, но не оптимизирована для них. Ориентирована на сложные аналитические запросы (джоины, агрегации, цепочки CTE и/или подзапросов), преимущественно на чтение. Обновление и удаление строк часто ограничено или оптимизировано под замену целиком партиции (insert overwrite table <table> partition (year=<year>, month=<month>) и т.п.). Часто хранит историю (напр. SCD2). К ней могут часто писать ad-hoc запросы (одноразовые, для проверки гипотезы или изучения данных). Примеры: Greenplum, Clickhouse, Bigquery.</month></year>
MPP, распределённая система, перекос данных
Massive parallel processing, горизонтально масштабируемая система.
В рамках СУБД это означает, что данных хранятся и обрабатываются распределённо на нескольких узлах (серверах, хостах) в сети. Меньше каждый отдельный кусочек данных – быстрее обработка.
Здесь важной становится равномерность распределения. “Упряжка бежит со скоростью самой медленной лошади” – если на одном из узлов 90% данных, значит все остальные узлы будут ждать его.
Также появляется “shuffle” – передача данных по сети между серверами для выполнения операций группировки и соединения. См. широкие операции в Spark
CAP-теорема
Consistency – каждый запрос на чтение возвращает либо актуальные данные, либо ошибку.
Availability – каждый запрос к СУБД возвращает данные, но не обязательно с применением всех последних изменений.
Partition-tolerance – распределённая система продолжает функционировать корректно даже если её части “разъединятся”.
Есть связанный термин – Split brain syndrome, рассинхронизация данных между частями системы из-за несоблюдения P в CAP. Поэтому обычно в MPP системах не жертвуют P и выбирают между CP и AP.
Примеры CA: MySQL, SQL Server, MariaDB (SMP базы)
Примеры CP: MongoDB, HBase, Redis
Примеры AP: Cassandra, Clickhouse
Что такое индекс и когда применяется, когда мешает
Индекс в базе данных - это специальная структура данных, которая создается для ускорения поиска и выборки данных из таблицы. Работают, подобно указателю в книге - они хранят значения столбцов и указывают на местоположение соответствующих строк в таблице. Это позволяет быстро находить нужные строки без сканирования всей таблицы.
* Применять индексы нужно не раньше, чем когда в таблице появится минимум 10000 тысяч записей. Иначе заметного прироста в скорости обработки данных не будет.
* И к тем столбцам, по которым наиболее часто ведется поиск (WHERE, GROUP BY, JOIN).
В OLAP базах индексы используются редко, т.к. запросы работают с большим количеством строк за раз. Для этого больше подходят партиции.
- Большое количество индексов замедляют действия связанные с вставкой, обновлением и удалением строк в таблице базы данных, так как происходит обновление самих индексов
- В небольших таблицах
- В таблицах, которые будут часто подвержены добавлению или изменению новых данных
- С теми столбцами, с которыми будут производиться частые действия (например, выполнение сложных запросов на выборку данных)
- В столбцах со значением NULL
- В OLAP таблицах, где нужно отобрать значения по диапазону (по партициям), а не конкретные записи
Типы индексов
Кластеризованные (Clustered) индексы:
* Сортируют и физически хранят данные таблицы в определенном порядке
* Таблица может иметь только один кластеризованный индекс
* Эффективны для поиска по диапазонам значений
Некластеризованные (Non-Clustered) индексы:
* Хранятся отдельно от данных таблицы
* Таблица может иметь несколько некластеризованных индексов
* Эффективны для поиска по конкретным значениям
Уникальные (Unique) индексы:
* Гарантируют уникальность значений в индексируемых столбцах
* Предотвращают дублирование данных
Составные (Composite) индексы:
* Состоят из нескольких столбцов таблицы
* Эффективны для поиска по комбинации столбцов
Покрывающие (covering) индексы:
* Добавляет (копирует) используемые в запросе неключевые значения в сам индекс
Шардирование vs Репликация
Это два распространенных подхода к горизонтальному масштабированию и управлению данными в распредёленных системах. Рассмотрим каждый подробнее:
🔸 Sharding (расщепление): процесс разделения данных на отдельные фрагменты (шарды) и распределения их по различным узлам или серверам в системе. Каждый шард содержит подмножество данных, и общая база данных делится между несколькими серверами. Это позволяет распределять нагрузку и увеличивать пропускную способность системы. Ключи распределения (distribution key), перекосы (data skew) — всё сюда.
🔸 Replication (репликация): процесс создания и поддержания копий данных на нескольких серверах или узлах в распределенной системе. Копии данных (реплики) обычно хранятся на разных серверах для обеспечения отказоустойчивости и повышения доступности данных. Это позволяет уменьшить время восстановления и обеспечить высокую доступность данных (реплики работают на чтение и их можно физически разместить поближе к потребителю).
Sharding может быть более сложным в настройке и управлении, так как требует разделения данных и управления их распределением. Примером может являться то же распределение данных в Гринпламе, где при неправильном выборе ключа распределения вся нагрузка идёт на один из узлов. Репликация, с другой стороны, обычно более проста в реализации и управлении, так как требует создания и поддержания копий данных, но она не помогает в обработке и записи данных.
ACID vs BASE
ACID – классический набор свойств транзакции в OLTP базах, которые поддерживают строгую согласованность данных в ней. Часто спрашивают расшифровку.
Atomicity (атомарность) – транзакция выполняется либо целиком, либо откатывается (rollback). Сохранение промежуточного состояния недопустимо.
Consistency (согласованность) – данные до и после выполнения транзакции должны быть согласованы (например, ссылочная целостность должна выполняться, упрощённо в связанной таблице для каждого внешнего ключа должен быть первичный ключ).
Isolation (изолированность) – все транзакции запускаются в изолированном окружении так, что не влияют друг на друга.
Durability (долговечность) – выполненная до конца или откаченная транзакция сохраняет данные в таком состоянии даже при отключении питания сервера или других схожих неполадках.
BASE – термин из NoSQL и NewSQL СУБД, обозначающий согласованность в конечном счёте.
Не обязательно помнить расшифровку, просто скажи, что “это когда ты не знаешь наверняка, когда операция выполнилась до конца. Например, в кликхаусе обновление или вставка данных может тянуться часами.”
Уровни изоляции (UR, CR, RR, SR)
Относится к I в ACID для транзакций. Чем дальше от UR к SR, тем медленнее и надёжнее будет возвращаться результат селекта (или других операций).
Неверно выбранный уровень изоляции может привести к различным фантомным чтениям или изменениям – “мигающим” ошибкам. Это связано с транзакциями, которые завершают работу посреди чтения. Не всегда это критично, в основном важно на уровне бэкенд-разработки. Чаще всего для источников ставится CR или RR, чтобы не слишком сильно блокировать строки таблиц при чтении.
UR – uncommitted read, “грязное чтение”, повторное выполнение команды может вернуть другие данные из-за применения или отката транзакции. В Postgres на самом деле не реализован, принудительно заменяется на CR.
CR – committed read, фиксирует данные на начало выполнения каждого запроса в транзакции (последовательные одинаковые select’ы внутри одной транзакции могут отличаться из-за изменений этих же строк другой транзакцией).
RR – repeatable read – блокирует уже вставленные в таблицу данные на начало выполнения всей транзакции, но позволяет фантомные чтения (в случаях, когда другая транзакция вставляет новые строки в эту таблицу во время действия первой транзакции).
SR – serializable read, почти не применяется в реальности, потому что критически замедляет базу, заставляя все транзакции идти последовательно
CDC - change data capture
Альтернативный простому селекту способ выгрузки данных из СУБД. Читает изменения из журнала транзакций OLTP СУБД, поэтому не нагружает сами таблицы в БД, где может вестись активная работа под продакшн нагрузкой.
В отличии от обычной батч загрузки, способен отлавливать все промежуточные изменения в течение периода загрузки. Этим же создаёт проблему – нужно схлопывать эти версии (н. брать среднюю и последнюю за период), если нам не нужно всю эту историю хранить.
Пример: Debezium поверх Kafka
RBAC
Role-based access control – управление доступом на основе ролей. Автоматизация выдачи схожих прав в системах и сервисах – можно создать общую группу для аналитиков одного подразделения и при выходе нового сотрудника назначать ему одну группу. Роли будут наследоваться. Также сильно облегчает отзыв прав при переводе/увольнении сотрудника.Часто интегрируется с Active Directory (AD) или LDAP для интеграции со многими сервисами.
Как использовать партиции для фильтрации
Добавить значение в where. Без дополнительной обработки.
Например, такое использовать фильтрацию по партициямм не будет:
where date(dt_col) = '2025-01-01'
А такое будет:
where dt_col = '2025-01-01'
