SQL Academy · 课时

OLTP 与 OLAP

Q: 「OLTP 与 OLAP」这节课中我会学到什么？

事务型数据库与分析型数据库。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

事务型数据库与分析型数据库。

OLTP 与 OLAP 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 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 数据库倾向于使用规范化模式（3NF 或更高），以消除冗余并提高写入效率。每个实体都存放在自己的表中，从而减少每个事务涉及的数据量。

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处理分析工作负载：在大型历史数据集上执行复杂聚合，并使用反规范化模式。
模式设计应遵循工作负载：OLTP 使用规范化模式（3NF），OLAP 使用星型模式或雪花模式。
ETL 数据管道连接这两个系统，将转换后的 OLTP 数据加载到分析型数据仓库中。
HTAP系统尝试通过双重的行存储和列存储，从单一引擎提供两类工作负载。

从一开始就选择正确的架构，可以避免日后痛苦的迁移，并确保查询速度符合用户预期。

常见问题解答

「OLTP 与 OLAP」课时是免费的吗？

是的 — 「OLTP 与 OLAP」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「OLTP 与 OLAP」这节课中我会学到什么？

事务型数据库与分析型数据库。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「OLTP 与 OLAP」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy