数据建模方法

SunnyFan大约 15 分钟约 4384 字

数据建模方法

简介

数据建模是数据库设计的核心环节，直接影响系统的性能、可维护性和扩展能力。良好的数据模型能够准确反映业务需求，平衡数据冗余与查询效率，并为后续的业务演进奠定坚实基础。本文将系统地介绍 ER 模型、范式与反范式设计、维度建模以及时序数据建模四种主流数据建模方法，结合实际案例讲解各类方法的设计原则和应用场景。

特点

ER 模型直观表达实体关系，适合业务需求分析和沟通
范式化设计减少数据冗余，保障数据一致性
维度建模优化分析查询性能，是数据仓库建设的核心方法
时序数据建模针对海量时间序列数据提供高效的存储和查询方案

ER 模型

ER（Entity-Relationship）模型是最经典的数据库建模方法，通过实体、属性和关系三个基本概念描述数据结构。

ER 模型设计原则

-- 电商系统核心 ER 模型
-- 实体：客户、商品、订单、订单明细、分类

-- 客户实体
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerNo VARCHAR(20) NOT NULL UNIQUE,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(200),
    Phone VARCHAR(20),
    Region VARCHAR(50),
    RegisterDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    LastLoginDate DATETIME,
    Status TINYINT NOT NULL DEFAULT 1,
    INDEX IX_Customers_Region (Region),
    INDEX IX_Customers_Email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品分类实体（支持层级结构）
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName VARCHAR(100) NOT NULL,
    ParentID INT NULL,
    Level TINYINT NOT NULL DEFAULT 1,
    SortOrder INT NOT NULL DEFAULT 0,
    FOREIGN KEY (ParentID) REFERENCES Categories(CategoryID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品实体
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductNo VARCHAR(30) NOT NULL UNIQUE,
    ProductName VARCHAR(200) NOT NULL,
    CategoryID INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    CostPrice DECIMAL(18,2),
    Stock INT NOT NULL DEFAULT 0,
    Description TEXT,
    Status TINYINT NOT NULL DEFAULT 1,
    CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
    INDEX IX_Products_Category (CategoryID),
    INDEX IX_Products_Status (Status, CreatedDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单实体
CREATE TABLE Orders (
    OrderID BIGINT PRIMARY KEY AUTO_INCREMENT,
    OrderNo VARCHAR(30) NOT NULL UNIQUE,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(18,2) NOT NULL,
    DiscountAmount DECIMAL(18,2) NOT NULL DEFAULT 0,
    PayAmount DECIMAL(18,2) GENERATED ALWAYS AS (TotalAmount - DiscountAmount) STORED,
    Status TINYINT NOT NULL DEFAULT 0,
    ShippingAddress VARCHAR(500),
    Remark VARCHAR(500),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    INDEX IX_Orders_Customer (CustomerID, OrderDate),
    INDEX IX_Orders_Date (OrderDate, Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单明细实体（关联订单和商品的多对多关系）
CREATE TABLE OrderDetails (
    DetailID BIGINT PRIMARY KEY AUTO_INCREMENT,
    OrderID BIGINT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    DiscountRate DECIMAL(5,4) NOT NULL DEFAULT 0,
    LineAmount DECIMAL(18,2) GENERATED ALWAYS AS (Quantity * UnitPrice * (1 - DiscountRate)) STORED,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    INDEX IX_Details_Order (OrderID),
    INDEX IX_Details_Product (ProductID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

实体关系映射

实体关系	关系类型	外键位置	说明
客户 - 订单	一对多	订单表	一个客户可有多个订单
订单 - 订单明细	一对多	订单明细表	一个订单包含多个明细行
商品 - 订单明细	一对多	订单明细表	一个商品可出现在多个明细中
分类 - 商品	一对多	商品表	一个分类包含多个商品
分类 - 分类	自引用	分类表	支持多级分类层级

范式与反范式

范式化设计通过消除数据冗余来保障数据一致性，而反范式化则通过适度冗余来提升查询性能。

范式化设计示例

-- 第三范式（3NF）设计：消除传递依赖
-- 员工表（满足3NF）
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentID INT NOT NULL,
    PositionID INT NOT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(18,2),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
    FOREIGN KEY (PositionID) REFERENCES Positions(PositionID)
);

-- 部门表（独立实体，消除传递依赖）
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
    DepartmentName VARCHAR(100) NOT NULL,
    ManagerID INT,
    Location VARCHAR(200)
);

-- 职位表（独立实体）
CREATE TABLE Positions (
    PositionID INT PRIMARY KEY AUTO_INCREMENT,
    PositionName VARCHAR(100) NOT NULL,
    Level TINYINT NOT NULL,
    BaseSalary DECIMAL(18,2)
);

-- 3NF 查询需要多表关联
SELECT e.EmployeeName, d.DepartmentName, p.PositionName, e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
INNER JOIN Positions p ON e.PositionID = p.PositionID
WHERE d.DepartmentName = '技术部';

反范式化设计示例

-- 反范式化：在订单表中冗余客户名称（避免频繁关联查询）
CREATE TABLE Orders_Denormalized (
    OrderID BIGINT PRIMARY KEY AUTO_INCREMENT,
    OrderNo VARCHAR(30) NOT NULL UNIQUE,
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,       -- 冗余字段
    CustomerRegion VARCHAR(50) NOT NULL,       -- 冗余字段
    OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(18,2) NOT NULL,
    ProductCount INT NOT NULL DEFAULT 0,       -- 冗余汇总字段
    Status TINYINT NOT NULL DEFAULT 0
);

-- 反范式化的统计表（预计算汇总数据）
CREATE TABLE DailySalesSummary (
    SummaryDate DATE NOT NULL,
    Region VARCHAR(50) NOT NULL,
    TotalOrders INT NOT NULL DEFAULT 0,
    TotalAmount DECIMAL(18,2) NOT NULL DEFAULT 0,
    AvgOrderAmount DECIMAL(18,2) NOT NULL DEFAULT 0,
    UniqueCustomers INT NOT NULL DEFAULT 0,
    TopProductID INT,
    PRIMARY KEY (SummaryDate, Region),
    INDEX IX_Summary_Region (Region, SummaryDate DESC)
);

-- 定时更新统计表
INSERT INTO DailySalesSummary (SummaryDate, Region, TotalOrders, TotalAmount, AvgOrderAmount, UniqueCustomers)
SELECT
    DATE(o.OrderDate) AS SummaryDate,
    o.CustomerRegion AS Region,
    COUNT(*) AS TotalOrders,
    SUM(o.TotalAmount) AS TotalAmount,
    AVG(o.TotalAmount) AS AvgOrderAmount,
    COUNT(DISTINCT o.CustomerID) AS UniqueCustomers
FROM Orders_Denormalized o
WHERE DATE(o.OrderDate) = CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(o.OrderDate), o.CustomerRegion
ON DUPLICATE KEY UPDATE
    TotalOrders = VALUES(TotalOrders),
    TotalAmount = VALUES(TotalAmount),
    AvgOrderAmount = VALUES(AvgOrderAmount),
    UniqueCustomers = VALUES(UniqueCustomers);

范式 vs 反范式适用场景

设计方式	优点	缺点	适用场景
范式化（3NF）	数据一致性好、更新简单、存储空间小	查询需要多表关联、复杂查询性能差	OLTP 系统、写密集场景
反范式化	查询性能好、减少关联、适合汇总	数据冗余、更新需同步多份、一致性风险	OLAP 系统、读密集场景
混合模式	兼顾一致性和性能	设计复杂度较高	大多数生产系统

维度建模

维度建模是数据仓库领域的核心建模方法，以星型模型和雪花模型为代表，优化分析查询性能。

星型模型设计

-- 销售数据仓库星型模型

-- 事实表：销售事实
CREATE TABLE FactSales (
    SalesID BIGINT PRIMARY KEY AUTO_INCREMENT,
    DateKey INT NOT NULL,                     -- 日期维度外键
    ProductKey INT NOT NULL,                  -- 产品维度外键
    CustomerKey INT NOT NULL,                 -- 客户维度外键
    StoreKey INT NOT NULL,                    -- 门店维度外键
    PromotionKey INT,                         -- 促销维度外键
    SalesQuantity INT NOT NULL,               -- 度量：销售数量
    SalesAmount DECIMAL(18,2) NOT NULL,       -- 度量：销售金额
    CostAmount DECIMAL(18,2) NOT NULL,        -- 度量：成本金额
    DiscountAmount DECIMAL(18,2),             -- 度量：折扣金额
    GrossProfit DECIMAL(18,2) GENERATED ALWAYS AS (SalesAmount - CostAmount - IFNULL(DiscountAmount, 0)) STORED,
    FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey),
    FOREIGN KEY (ProductKey) REFERENCES DimProduct(ProductKey),
    FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey),
    FOREIGN KEY (StoreKey) REFERENCES DimStore(StoreKey),
    INDEX IX_FactSales_Date (DateKey),
    INDEX IX_FactSales_Product (ProductKey, DateKey),
    INDEX IX_FactSales_Customer (CustomerKey, DateKey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 维度表：日期维度
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,                  -- 格式：20240101
    FullDate DATE NOT NULL,
    DayOfWeek TINYINT NOT NULL,
    DayName VARCHAR(10) NOT NULL,
    DayOfMonth TINYINT NOT NULL,
    WeekOfYear TINYINT NOT NULL,
    Month TINYINT NOT NULL,
    MonthName VARCHAR(10) NOT NULL,
    Quarter TINYINT NOT NULL,
    Year INT NOT NULL,
    IsWeekend TINYINT NOT NULL,
    IsHoliday TINYINT NOT NULL DEFAULT 0,
    HolidayName VARCHAR(50),
    INDEX IX_DimDate_YearMonth (Year, Month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 维度表：产品维度（缓慢变化维度 SCD Type 2）
CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,                   -- 业务主键
    ProductName VARCHAR(200) NOT NULL,
    CategoryName VARCHAR(100) NOT NULL,
    SubCategoryName VARCHAR(100),
    BrandName VARCHAR(100),
    UnitPrice DECIMAL(18,2),
    SCDEffectiveDate DATE NOT NULL,           -- SCD 生效日期
    SCDEndDate DATE NOT NULL DEFAULT '9999-12-31',  -- SCD 失效日期
    IsCurrent TINYINT NOT NULL DEFAULT 1,     -- 是否当前记录
    INDEX IX_DimProduct_BK (ProductID, IsCurrent),
    INDEX IX_DimProduct_Category (CategoryName, SubCategoryName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用分析查询

-- 按月度、产品类别分析销售趋势
SELECT
    d.Year,
    d.Month,
    p.CategoryName,
    SUM(f.SalesQuantity) AS TotalQuantity,
    SUM(f.SalesAmount) AS TotalSales,
    SUM(f.GrossProfit) AS TotalProfit,
    ROUND(SUM(f.GrossProfit) / NULLIF(SUM(f.SalesAmount), 0) * 100, 2) AS ProfitRate
FROM FactSales f
INNER JOIN DimDate d ON f.DateKey = d.DateKey
INNER JOIN DimProduct p ON f.ProductKey = p.ProductKey
WHERE d.Year = 2024 AND p.IsCurrent = 1
GROUP BY d.Year, d.Month, p.CategoryName
ORDER BY d.Month, TotalSales DESC;

-- 同比增长率分析
SELECT
    curr.Year,
    curr.Month,
    curr.TotalSales AS CurrentYearSales,
    prev.TotalSales AS PreviousYearSales,
    ROUND((curr.TotalSales - prev.TotalSales) / NULLIF(prev.TotalSales, 0) * 100, 2) AS YoYGrowth
FROM (
    SELECT d.Year, d.Month, SUM(f.SalesAmount) AS TotalSales
    FROM FactSales f INNER JOIN DimDate d ON f.DateKey = d.DateKey
    WHERE d.Year = 2024
    GROUP BY d.Year, d.Month
) curr
INNER JOIN (
    SELECT d.Year, d.Month, SUM(f.SalesAmount) AS TotalSales
    FROM FactSales f INNER JOIN DimDate d ON f.DateKey = d.DateKey
    WHERE d.Year = 2023
    GROUP BY d.Year, d.Month
) prev ON curr.Month = prev.Month;

时序数据建模

时序数据建模针对时间序列数据的特点进行优化，广泛应用于 IoT、监控、金融等领域。

时序数据表设计

-- 设备传感器数据采集（使用分区表优化大表查询）
CREATE TABLE SensorData (
    DataID BIGINT AUTO_INCREMENT,
    DeviceID VARCHAR(50) NOT NULL,
    SensorType VARCHAR(30) NOT NULL,
    CollectTime DATETIME(3) NOT NULL,         -- 毫秒精度
    Value DOUBLE NOT NULL,
    Quality TINYINT NOT NULL DEFAULT 1,       -- 数据质量标识
    CreateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (DataID, CollectTime),
    INDEX IX_Sensor_Device (DeviceID, CollectTime DESC),
    INDEX IX_Sensor_Type (SensorType, CollectTime DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(CollectTime)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 设备元数据表
CREATE TABLE Devices (
    DeviceID VARCHAR(50) PRIMARY KEY,
    DeviceName VARCHAR(200) NOT NULL,
    DeviceType VARCHAR(50) NOT NULL,
    Location VARCHAR(200),
    InstallationDate DATE,
    Status TINYINT NOT NULL DEFAULT 1,
    LastReportTime DATETIME,
    INDEX IX_Devices_Type (DeviceType, Status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 时序数据降采样汇总表
CREATE TABLE SensorDataHourly (
    DeviceID VARCHAR(50) NOT NULL,
    SensorType VARCHAR(30) NOT NULL,
    TimeHour DATETIME NOT NULL,
    AvgValue DOUBLE NOT NULL,
    MinValue DOUBLE NOT NULL,
    MaxValue DOUBLE NOT NULL,
    SampleCount INT NOT NULL,
    StdDev DOUBLE,
    PRIMARY KEY (DeviceID, SensorType, TimeHour),
    INDEX IX_Hourly_Time (TimeHour, DeviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

时序数据查询与降采样

-- 写入传感器数据（批量插入）
INSERT INTO SensorData (DeviceID, SensorType, CollectTime, Value) VALUES
('DEV-001', 'Temperature', '2024-06-15 10:00:00.000', 25.3),
('DEV-001', 'Temperature', '2024-06-15 10:00:01.000', 25.4),
('DEV-001', 'Temperature', '2024-06-15 10:00:02.000', 25.5),
('DEV-001', 'Humidity',    '2024-06-15 10:00:00.000', 65.2),
('DEV-002', 'Temperature', '2024-06-15 10:00:00.000', 22.1);

-- 查询设备最近一小时的实时数据
SELECT CollectTime, Value
FROM SensorData
WHERE DeviceID = 'DEV-001'
  AND SensorType = 'Temperature'
  AND CollectTime >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY CollectTime ASC;

-- 小时级降采样聚合（定时任务执行）
INSERT INTO SensorDataHourly (DeviceID, SensorType, TimeHour, AvgValue, MinValue, MaxValue, SampleCount, StdDev)
SELECT
    DeviceID,
    SensorType,
    DATE_FORMAT(CollectTime, '%Y-%m-%d %H:00:00') AS TimeHour,
    AVG(Value) AS AvgValue,
    MIN(Value) AS MinValue,
    MAX(Value) AS MaxValue,
    COUNT(*) AS SampleCount,
    STDDEV(Value) AS StdDev
FROM SensorData
WHERE CollectTime >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
  AND CollectTime < DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY DeviceID, SensorType, DATE_FORMAT(CollectTime, '%Y-%m-%d %H:00:00')
ON DUPLICATE KEY UPDATE
    AvgValue = VALUES(AvgValue),
    MinValue = VALUES(MinValue),
    MaxValue = VALUES(MaxValue),
    SampleCount = VALUES(SampleCount),
    StdDev = VALUES(StdDev);

分区管理与数据生命周期

-- 按月新增分区（每月初执行）
ALTER TABLE SensorData ADD PARTITION (
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01'))
);

-- 归档旧数据到历史表
CREATE TABLE SensorData_Archive LIKE SensorData;
ALTER TABLE SensorData_Archive REMOVE PARTITIONING;

-- 将旧分区数据迁移到归档表
INSERT INTO SensorData_Archive
SELECT * FROM SensorData PARTITION (p202401);

-- 确认归档无误后删除旧分区（释放磁盘空间）
ALTER TABLE SensorData DROP PARTITION p202401;

-- 查看分区信息
SELECT
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH / 1024 / 1024 AS DataMB
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'SensorData' AND TABLE_SCHEMA = DATABASE();

多粒度降采样策略

-- 分钟级降采样（保留 7 天）
CREATE TABLE SensorDataMinute (
    DeviceID VARCHAR(50) NOT NULL,
    SensorType VARCHAR(30) NOT NULL,
    TimeMinute DATETIME NOT NULL,
    AvgValue DOUBLE NOT NULL,
    MinValue DOUBLE NOT NULL,
    MaxValue DOUBLE NOT NULL,
    SampleCount INT NOT NULL,
    PRIMARY KEY (DeviceID, SensorType, TimeMinute)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 日级降采样（长期保留）
CREATE TABLE SensorDataDaily (
    DeviceID VARCHAR(50) NOT NULL,
    SensorType VARCHAR(30) NOT NULL,
    TimeDate DATE NOT NULL,
    AvgValue DOUBLE NOT NULL,
    MinValue DOUBLE NOT NULL,
    MaxValue DOUBLE NOT NULL,
    SampleCount INT NOT NULL,
    StdDev DOUBLE,
    P95Value DOUBLE,  -- 95 分位数
    PRIMARY KEY (DeviceID, SensorType, TimeDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 数据保留策略建议
-- 原始数据：保留 30 天（分区自动管理）
-- 分钟级汇总：保留 90 天
-- 小时级汇总：保留 1 年
-- 日级汇总：永久保留

-- 查询时根据时间范围选择合适的粒度
-- 最近 1 小时：查原始数据
-- 最近 24 小时：查分钟级汇总
-- 最近 30 天：查小时级汇总
-- 超过 30 天：查日级汇总

优点

系统化的建模方法确保数据结构清晰，降低后期维护成本
范式化设计从源头减少数据异常，保障业务数据准确性
维度建模为分析查询提供极佳性能，支持灵活的多维分析
时序建模结合分区和降采样策略，有效解决海量数据存储和查询挑战

缺点

建模过程需要深入理解业务，前期投入的时间成本较高
范式与反范式之间的取舍需要丰富的实践经验，新人容易过度设计或冗余不足
维度建模的 SCD 策略增加了 ETL 的复杂度和数据管理成本
时序数据量增长迅速，长期存储成本和分区管理是持续的运维挑战

数据建模方法是数据库设计的基础，选择合适的建模方法对系统成败至关重要。OLTP 系统应以范式化设计为主，在关键查询路径上适度反范式化以提升性能；OLAP 系统应采用维度建模，构建清晰的星型模型以支持高效的多维分析；对于时序数据，需要结合分区策略和降采样机制来平衡存储成本与查询效率。在实际项目中，通常会组合使用多种建模方法，在核心业务区域保持范式化，在报表分析区域使用维度建模，以实现业务需求与技术性能的最佳平衡。

关键知识点

数据库主题一定要同时看数据模型、读写模式和执行代价。
很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
高可用、备份、迁移和治理与查询优化同样重要。

项目落地视角

所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
上线前准备回滚脚本、备份点和校验方案。
把连接池、锁等待、慢查询和容量增长纳入日常巡检。

常见误区

脱离真实数据分布讨论索引或分片。
只看单条 SQL，不看整条业务链路的事务和锁。
把测试环境结论直接等同于生产环境结论。

进阶路线

继续向执行计划、存储引擎、复制机制和数据治理层深入。
把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
沉淀成数据库设计规范、SQL 审核规则和变更流程。

适用场景

当你准备把《数据建模方法》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
适合数据建模、查询优化、事务控制、高可用和迁移治理。
当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
所有优化结论都结合执行计划、样本数据和监控指标验证。
高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
检查执行计划是否走错索引、是否发生排序或全表扫描。
排查长事务、隐式类型转换、统计信息过期和参数嗅探。

数据建模工具与规范

建模工具推荐

工具	类型	说明
MySQL Workbench	图形化	MySQL 官方，支持正向/逆向工程
PowerDesigner	企业级	支持概念/逻辑/物理三层模型
dbdiagram.io	在线	使用 DSL 语法定义 ER 图，快速分享
DrawSQL	在线	可视化建表，支持团队协作
Flyway / Liquibase	版本控制	数据库 Schema 版本管理

建模规范清单

-- 命名规范
-- 表名：小写下划线分隔，使用复数名词（customers, orders）
-- 字段名：小写下划线分隔（customer_name, order_date）
-- 主键：id 或 表名_id（user_id, order_id）
-- 外键：关联表名_id（customer_id, product_id）
-- 索引：ix_表名_字段名（ix_orders_customer_id）
-- 唯一约束：uk_表名_字段名（uk_users_email）

-- 字段规范
CREATE TABLE example (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,          -- 主键统一用 BIGINT
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 创建时间
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 更新时间
    is_deleted TINYINT NOT NULL DEFAULT 0,         -- 逻辑删除标记
    status TINYINT NOT NULL DEFAULT 1,             -- 状态字段用 TINYINT
    remark VARCHAR(500),                           -- 备注字段长度限制
    INDEX ix_example_status (status)               -- 高频查询字段建索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表注释说明业务含义';

模型评审要点

数据模型评审检查项：
1. 主键设计 — 自增 ID 还是业务主键？是否需要雪花 ID？
2. 字段类型 — 金额用 DECIMAL、状态用 TINYINT、时间用 DATETIME
3. 索引设计 — 查询模式是否覆盖？是否有冗余索引？
4. 外键约束 — 生产环境是否使用物理外键（通常不用）？
5. 分区策略 — 大表是否按时间/范围分区？
6. 归档方案 — 历史数据如何归档和清理？
7. 字符集 — 统一使用 utf8mb4（支持 Emoji）
8. 默认值 — NOT NULL 字段是否有 DEFAULT
9. 命名规范 — 是否符合团队约定
10. 扩展性 — 预估数据量增长后的表现

数据建模版本管理

# 使用 Flyway 管理数据库 Schema 版本
# 项目结构
# db/migration/
#   V1__create_users_table.sql
#   V2__create_orders_table.sql
#   V3__add_user_phone_column.sql
#   V4__create_index_orders_date.sql

# 每个 SQL 文件是一个版本，按顺序执行
# V1__create_users_table.sql
# CREATE TABLE users (...);

# 生产环境执行迁移
# flyway migrate
# flyway info    # 查看迁移状态
# flyway validate # 验证完整性

复盘问题

如果把《数据建模方法》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
《数据建模方法》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
相比默认实现或替代方案，采用《数据建模方法》最大的收益和代价分别是什么？

数据建模方法

数据建模方法

简介

特点

ER 模型

ER 模型设计原则

实体关系映射

范式与反范式

范式化设计示例

反范式化设计示例

范式 vs 反范式适用场景

维度建模

星型模型设计

常用分析查询

时序数据建模

时序数据表设计

时序数据查询与降采样

分区管理与数据生命周期

多粒度降采样策略

优点

缺点

总结

关键知识点

项目落地视角

常见误区

进阶路线

适用场景

落地建议

排错清单

数据建模工具与规范

建模工具推荐

建模规范清单

模型评审要点

数据建模版本管理

复盘问题

延伸阅读