OLTP и OLAP
Транзакционные и аналитические базы данных
«OLTP и OLAP» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое OLTP и OLAP?
Базы данных не бывают универсальными. Две принципиально разные рабочие нагрузки определили подходы к проектированию и эксплуатации баз данных: OLTP (оперативная обработка транзакций) и OLAP (оперативная аналитическая обработка).
Понимание различий необходимо каждому специалисту по данным. Правильный выбор между OLTP и OLAP определяет скорость запросов, стоимость хранения и общую архитектуру системы данных.
OLTP: для обработки транзакций
Системы OLTP обрабатывают большое количество коротких быстрых операций — вставок, обновлений и удалений, отражающих события бизнеса в реальном времени. Примеры: размещение заказа, обработка платежа или обновление записи клиента.
Ключевые свойства OLTP: низкая задержка каждой операции, высокая параллельность и строгая согласованность. Каждая транзакция должна соответствовать требованиям ACID, чтобы защищать целостность данных.
-- OLTP example: inserting a new order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1042, 88, 3, CURRENT_DATE);
-- Immediately update inventory
UPDATE inventory
SET stock = stock - 3
WHERE product_id = 88;OLAP: для анализа
Системы OLAP оптимизированы для сложных запросов, которые просматривают большие объёмы исторических данных, выявляя тенденции, закономерности и сводные показатели. Бизнес-аналитики и специалисты по данным используют OLAP, чтобы отвечать на вопросы вроде: «Каковы наши общие продажи по регионам за прошлый квартал?»
Запросы OLAP часто агрегируют миллионы строк и включают несколько соединений между таблицами фактов и измерений. Скорость отдельных операций записи вторична; важны пропускная способность чтения и гибкость запросов.
-- OLAP example: total sales by region for Q1 2024
SELECT
d.region,
SUM(f.sales_amount) AS total_sales,
COUNT(f.order_id) AS order_count
FROM fact_sales f
JOIN dim_date dd ON f.date_key = dd.date_key
JOIN dim_store d ON f.store_key = d.store_key
WHERE dd.year = 2024
AND dd.quarter = 1
GROUP BY d.region
ORDER BY total_sales DESC;Сравнение двух систем
Проще всего запомнить различие, подумав о том, кто использует каждую систему и как именно:
- OLTP: используется серверной частью приложений; тысячи пользователей работают одновременно; каждый запрос затрагивает несколько строк.
- OLAP: используется аналитиками и средствами подготовки отчётов; одновременно выполняется меньше запросов, но каждый просматривает миллионы строк.
Эти противоположные способы доступа приводят к очень разным схемам баз данных, стратегиям индексации и даже вариантам выбора оборудования.
-- OLTP: lookup a single customer's latest order (row-level access)
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.customer_id = 1042
ORDER BY o.order_date DESC
LIMIT 1;
-- OLAP: monthly revenue trend over the past year (aggregate scan)
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;Проектирование схемы: нормализованная и денормализованная
Базы данных OLTP предпочитают нормализованные схемы (3НФ или выше), чтобы устранить избыточность и сделать операции записи эффективными. Каждая сущность хранится в собственной таблице, что уменьшает объем данных, затрагиваемых транзакцией.
Базы данных OLAP предпочитают денормализованные схемы — особенно звездные и снежные схемы, — в которых данные предварительно соединены и избыточны. Это устраняет дорогостоящие соединения во время выполнения запроса и позволяет движкам колоночного хранения быстрее сканировать данные.
-- Normalized OLTP design (3NF)
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150) UNIQUE
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
total NUMERIC(10,2)
);
-- Denormalized OLAP fact table (star schema)
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
customer_key INT,
date_key INT,
product_key INT,
region VARCHAR(50),
category VARCHAR(50),
amount NUMERIC(12,2)
);Стратегии индексирования различаются
Системы OLTP активно используют индексы B-дерева для первичных и внешних ключей, обеспечивая быстрый поиск отдельных строк и эффективные соединения в рамках транзакции.
Системы OLAP получают преимущества от битовых индексов, колоночного хранения и секционирования. Сканирование целого столбца (например, всех сумм продаж) гораздо эффективнее, когда данные хранятся по столбцам, а не по строкам.
-- OLTP: B-tree index for fast order lookup by customer
CREATE INDEX idx_orders_customer
ON orders (customer_id);
-- OLTP: compound index for range queries
CREATE INDEX idx_orders_date_customer
ON orders (order_date, customer_id);
-- OLAP: partition fact table by year to prune scan
CREATE TABLE fact_sales_2024
PARTITION OF fact_sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');Параллелизм и блокировки
Системы OLTP должны обрабатывать тысячи параллельных операций записи без конфликтов. Базы данных используют блокировки на уровне строк и MVCC (многоверсионное управление параллелизмом), поэтому операции чтения никогда не блокируют операции записи, и наоборот.
Запросы OLAP преимущественно предназначены только для чтения. Блокировки редко становятся проблемой, но длительное сканирование может потреблять значительные ресурсы CPU и ввода-вывода. Большинство хранилищ данных выполняют OLAP в отдельной системе, которая наполняется пакетными операциями ETL или CDC (захватом изменений данных) из источника OLTP.
-- OLTP: explicit transaction with row-level lock
BEGIN;
SELECT balance
FROM accounts
WHERE account_id = 7
FOR UPDATE;
UPDATE accounts
SET balance = balance - 200
WHERE account_id = 7;
COMMIT;ETL: связь между OLTP и OLAP
Поскольку OLTP и OLAP используют несовместимые схемы, организации запускают ETL (извлечение, преобразование, загрузку) — конвейеры, которые по расписанию копируют и перестраивают данные из транзакционной базы данных в аналитическое хранилище (еженочно, ежечасно или почти в реальном времени).
Процесс ETL преобразует нормализованные строки OLTP в денормализованные записи фактов и измерений, одновременно применяя бизнес-логику (например, конвертацию валюты и сегментацию клиентов).
-- Simplified ETL INSERT from OLTP orders into OLAP fact table
INSERT INTO fact_sales (
customer_key,
date_key,
product_key,
amount
)
SELECT
dc.customer_key,
dd.date_key,
dp.product_key,
o.total_amount
FROM orders o
JOIN dim_customer dc ON dc.source_customer_id = o.customer_id
JOIN dim_date dd ON dd.calendar_date = o.order_date
JOIN dim_product dp ON dp.source_product_id = o.product_id
WHERE o.order_date = CURRENT_DATE - INTERVAL '1 day'
AND o.order_id NOT IN (SELECT source_order_id FROM fact_sales);Типичные шаблоны запросов OLAP
Запросы OLAP почти всегда включают агрегации (SUM, COUNT, AVG), группировку по нескольким измерениям и фильтрацию по диапазонам дат или категориям. Это строительные блоки панелей мониторинга и деловых отчетов.
Оконные функции особенно эффективны в рабочих нагрузках OLAP: они позволяют сравнивать показатели каждого периода с предыдущим без соединения таблицы с самой собой.
-- Year-over-year revenue comparison using a window function
SELECT
dd.year,
dd.quarter,
SUM(f.amount) AS revenue,
LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
ORDER BY dd.year) AS prev_year_revenue,
ROUND(
100.0 * (SUM(f.amount) -
LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
ORDER BY dd.year))
/ NULLIF(LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
ORDER BY dd.year), 0)
, 2) AS yoy_pct_change
FROM fact_sales f
JOIN dim_date dd ON f.date_key = dd.date_key
GROUP BY dd.year, dd.quarter
ORDER BY dd.quarter, dd.year;HTAP: стирая границы
Современные системы, такие как TiDB, SingleStore и PostgreSQL + колоночные расширения, реализуют HTAP (гибридную транзакционную и аналитическую обработку). Они стремятся обрабатывать оба типа рабочих нагрузок в одном движке, избегая эксплуатационной сложности поддержки раздельных систем OLTP и OLAP.
HTAP достигает этого, одновременно храня данные в двух форматах: в построчном хранилище для транзакционных операций записи и в колоночном хранилище для аналитического чтения, автоматически синхронизируя их.
-- PostgreSQL with cstore_fdw (columnar extension) example
-- Analytical table stored in columnar format
CREATE FOREIGN TABLE fact_sales_columnar (
date_key INT,
product_key INT,
region VARCHAR(50),
amount NUMERIC(12,2)
)
SERVER cstore_server
OPTIONS (filename '/data/fact_sales_columnar');
-- Regular OLTP table remains row-based
-- Both can be queried in the same SQL statement
SELECT f.region, SUM(f.amount)
FROM fact_sales_columnar f
GROUP BY f.region;Выбор подходящей системы
Выбор между OLTP и OLAP (или HTAP) определяется вашей основной рабочей нагрузкой:
- Если вы создаете приложение, которое регистрирует события в реальном времени, используйте базу данных OLTP (PostgreSQL, MySQL, сервер SQL).
- Если вы создаете слой отчетности на основе исторических данных, используйте хранилище OLAP (BigQuery, Редшифт, Сноуфлейк, ClickHouse).
- Если вам нужны оба типа обработки и вы хотите упростить эксплуатацию, рассмотрите варианты HTAP.
Во многих промышленных архитектурах используются обе системы: база данных OLTP как источник достоверных данных и отдельное хранилище данных для аналитики, соединенные конвейером ETL.
-- Quick diagnostic: check table access pattern
-- High seq_scan relative to idx_scan = analytical (OLAP-like) load
SELECT
relname AS table_name,
seq_scan,
idx_scan,
n_live_tup AS live_rows
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;Проверка знаний
Проверьте свое понимание ключевых различий между системами OLTP и OLAP.
Итоги урока
OLTP и OLAP — основные выводы:
- OLTP обрабатывает транзакционные рабочие нагрузки в реальном времени: быстрые параллельные операции записи на уровне строк с гарантиями ACID.
- OLAP обрабатывает аналитические рабочие нагрузки: сложные агрегации по большим историческим наборам данных с использованием денормализованных схем.
- Проектирование схемы зависит от рабочей нагрузки: нормализованная схема (3НФ) подходит для OLTP, а звездная или снежная — для OLAP.
- Конвейеры ETL связывают две системы, загружая преобразованные данные OLTP в аналитическое хранилище.
- Системы HTAP пытаются обслуживать обе рабочие нагрузки в одном движке, используя двойное построчное и колоночное хранение.
Выбор подходящей архитектуры с самого начала предотвращает болезненные миграции в будущем и гарантирует, что запросы будут выполняться с ожидаемой пользователями скоростью.
Часто задаваемые вопросы
Урок «OLTP и OLAP» бесплатный?
Да — полный текст урока «OLTP и OLAP» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «OLTP и OLAP»?
Транзакционные и аналитические базы данных Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.
Сколько времени занимает урок «OLTP и OLAP»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.