数据库性能优化：表结构的优化（从基础设计到分库分表）

一、表结构优化的核心思路与具体方法

数据库表结构优化的核心目标是：减少数据冗余、提升查询效率、降低维护成本、适配业务增长。

1. 基础设计：遵循范式，适度反范式

遵循三大范式（核心）：

第一范式（1NF）：列不可再分，避免一个字段存储多个值（如用hobby字段存 “篮球，足球”，应拆分为关联表）。

第二范式（2NF）：主键外的字段必须完全依赖主键，避免部分依赖（如订单表中，商品名称不应依赖商品ID（非主键），应拆分出商品表）。

第三范式（3NF）：非主键字段不能传递依赖（如订单表中，用户地址不应依赖用户ID，应拆分出用户表）。

适度反范式（性能优先）：

完全遵循范式可能导致多表联查，性能下降。可针对性反范式：

示例：电商订单表中，冗余存储商品名称（而非仅存商品ID），避免查询订单时联查商品表；

场景：高频查询、少量更新的场景（如订单快照、报表统计）。

2. 字段设计：精准、精简、无冗余

这是表结构优化的核心，直接影响存储和查询效率：

优化方向	具体做法	反例（错误示范）
字段类型匹配	选择最小且合适的类型：- 整数：tinyint（1 字节）< smallint（2 字节）< int（4 字节）< bigint（8 字节） 字符串：固定长度用char，可变长度用varchar（避免用text存短字符串） 小数：金额用decimal(10,2)（精准），非精准用float/double	用int存性别（仅 0/1）、 用varchar(255)存手机号（固定 11 位）、 用float存金额（有精度丢失）
避免 NULL 值	尽量给字段设NOT NULL，并设置默认值（如status默认 0，create_time默认当前时间）	大量字段允许 NULL，查询时需额外判断IS NULL，索引效率低
减少冗余字段	避免重复存储相同含义的字段（如user_name和username）	订单表中同时存user_id、user_name、user_phone（可通过联查用户表获取）
慎用大字段	大字段（text/blob）单独拆分到子表，主表仅存关联 ID	文章表中直接存content（大文本），导致查询列表时加载大量无用数据

补充：

什么是tinyint？

tinyint 是关系型数据库（如 MySQL、SQL Server）中最基础的整数类型之一，核心特点是占用存储空间极小，专门用于存储范围很小的整数。

特性说明：

特性	具体说明
存储空间	1 字节（8 位），是所有整数类型中最小的（比 smallint/int/bigint 更省空间）
取值范围	- 无符号（unsigned）：0 ~ 255- 有符号（默认）：-128 ~ 127
常用场景	存储状态值、标识位、枚举类小数值（如性别、开关、订单状态等）

什么是smallint?

smallint（也常写作 small integer）是关系型数据库（MySQL、SQL Server、PostgreSQL 等）中的整数类型，定位介于 tinyint 和 int 之间，核心特点是存储空间小、取值范围适中。

特性说明:

特性	具体说明
存储空间	2 字节（16 位），比 tinyint 多 1 字节，比 int 少 2 字节
取值范围	- 无符号（unsigned）：0 ~ 65535- 有符号（默认）：-32768 ~ 32767
常用场景	存储小范围计数 / 编码（如商品库存、省份编码、年级、分数等），且值超出 tinyint 范围

什么是bigint?

bigint（也写作 big integer）是 MySQL、SQL Server、PostgreSQL 等主流数据库支持的整数类型，定位是超大范围整数存储.

特性说明:

特性	具体说明
存储空间	8 字节（64 位），是 int 的 2 倍、smallint 的 4 倍、tinyint 的 8 倍
取值范围	- 有符号（默认）：-9223372036854775808 ~ 9223372036854775807（约 ±9e18）- 无符号（unsigned）：0 ~ 18446744073709551615（约 1.8e19）
常用场景	海量数据的主键 ID（如用户 ID、订单 ID）、超大计数（如平台总交易额、累计访问量）、分布式 ID 存储

什么是decimal?

decimal（也写作 numeric，两者在主流数据库中等价）是定点数类型，区别于 float/double 这类浮点数，它不会产生精度丢失。

decimal 的声明格式为 decimal(M, D)，其中：

M：总位数（精度），范围 1~65，代表整数 + 小数的总位数；

D：小数位数（标度），范围 0~30，且 D ≤ M；

存储空间：根据 M 的大小动态分配（1~16 字节），比浮点数稍大，但换来了绝对精准。

特性	具体说明
精度	完全精准，无舍入误差（浮点数 float/double 会因二进制存储产生精度丢失）
取值范围	依赖 M 和 D，例如 decimal(10,2) 范围是 -99999999.99 ~ 99999999.99
常用场景	金额（如订单金额、商品价格）、税率、汇率、精确计数（如库存余量）

注意：

(1) 合理设置 M 和 D：

不要过度放大 M（如用 decimal(20,2) 存普通订单金额），会浪费存储空间；

D 需匹配业务场景：金额用 2（元角分），汇率 / 税率用 4（如 6.9875），避免后续修改字段。

(2) 避免与浮点数混合计算：

若 decimal字段和 float 字段混合运算，会触发类型转换，可能引入精度丢失，示例：

-- 错误：float 参与计算
SELECT (total_amount * CAST(0.1 AS float)) FROM order_main;
-- 正确：全部用 decimal
SELECT (total_amount * 0.10) FROM order_main;

(3) 处理四舍五入：当插入 / 计算的值超出 D 位小数时，MySQL 会自动四舍五入（而非截断），示例：

-- 插入 100.126 到 decimal(10,2) 字段，会自动转为 100.13
INSERT INTO order_main (order_no, total_amount) VALUES ('TEST', 100.126);

(4) 性能考量：decimal 的计算速度略慢于 float/double，但在金融场景中，精度优先于性能；若业务无精度要求（如统计浏览量的平均值），可选用 float。

3. 索引优化：高效索引，避免滥用

索引是查询优化的核心，但过多索引会降低插入 / 更新效率：

必加索引：主键（PRIMARY KEY）、外键（FOREIGN KEY）、高频查询字段（如user_id、order_no）、联合查询字段（如user_id + create_time）。

避免无效索引：

不索引低基数字段（如gender，仅 0/1）；

不索引过长字符串（可截取前 10 位索引，如name(10)）；

避免重复索引（如同时建idx_user_id``idx_user_id_create_time，前者冗余）。

索引设计技巧：

联合索引遵循 “最左匹配原则”，如 idx_a_b_c ，能匹配 a 、a+b 、a+b+c ，但不匹配 b 、b+c 。

4. 分表分库：应对大数据量

分表分库（Sharding）是将原本存储在单张表 / 单个数据库中的数据，拆分到多张表 / 多个数据库中存储的技术方案，核心目标是：

降低单表 / 单库的数据量，提升查询 / 写入性能；

分散数据库服务器的 CPU、内存、IO 压力；

支持业务的水平扩展（加机器即可扩容）。

适用场景（必须满足其一）

单表数据量：MySQL 单表超过 1000 万行（或数据文件超过 10GB），查询 / 更新效率显著下降；

单库压力：单数据库的 QPS（每秒查询数）超过服务器承载上限（如单机 MySQL QPS 达 1 万 +）；

业务增长：预估未来 1-2 年数据量 / 访问量会突破单机瓶颈。

分表分库主要分为垂直拆分和水平拆分两大类，实际场景中常组合使用：

垂直拆分（按 “列 / 业务” 拆分）

核心逻辑：将表 / 库按 “功能 / 字段维度” 拆分，把不常用、大字段或不同业务的内容分离。

垂直分库：按业务模块拆分数据库（解耦 + 分散压力）。

示例：电商系统拆分为「用户库（user_db）」「订单库（order_db）」「商品库（product_db）」，每个库部署在不同服务器。

垂直分表：将单表的字段拆分为多张表（减少行大小，提升查询效率）。

示例：用户表 user 拆分为：

• user_base：核心字段（id、手机号、密码、姓名），高频查询；
• user_ext：扩展字段（头像、简介、地址、爱好），低频查询；
• user_log：行为日志（登录时间、登录 IP），大数量且低频关联。

垂直拆分示例（MySQL）：

-- 垂直分表：用户基础表
CREATE TABLE `user_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `password` varchar(64) NOT NULL COMMENT '加密密码',
  `nickname` varchar(64) NOT NULL COMMENT '昵称',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 垂直分表：用户扩展表
CREATE TABLE `user_ext` (
  `user_id` bigint(20) NOT NULL COMMENT '关联user_base.id',
  `avatar` varchar(255) DEFAULT '' COMMENT '头像URL',
  `address` varchar(512) DEFAULT '' COMMENT '收货地址',
  `hobby` varchar(255) DEFAULT '' COMMENT '爱好',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

垂直拆分的优缺点：

优点	缺点
1. 降低单表字段数量，提升查询效率；2. 业务模块解耦，便于维护；3. 实现简单，无需复杂路由规则	1. 无法解决单表数据量过大的问题（如订单表仍有亿级数据）；2. 跨表关联查询需多表 JOIN，增加开发成本

水平拆分（按 “行” 拆分）

核心逻辑：将单表的行数据，按指定规则（如范围、哈希）拆分到多张表 / 多个库中，每张表的结构完全一致，但数据不重叠。

水平分表：同一数据库内，将单表拆分为多张结构相同的子表。

示例：订单表 order_main 拆分为 order_main_202401 、order_main_202402（按月份），或 order_main_00 ~ order_main_99（按用户 ID 哈希）。

水平分库：将拆分后的子表分布到不同数据库服务器中（分表 + 分库结合）。

示例：订单表拆分为 100 张子表，分散到 10 个数据库（每个库 10 张表），部署在 10 台服务器。

（1）水平拆分的常用规则（核心）

拆分规则	实现方式	适用场景	示例
范围拆分	按时间、ID 范围划分	订单、日志等有时间维度的数据	订单表按月份拆分：order_202401、order_202402；按 ID 范围：order_1（1-100 万）、order_2（100 万 - 200 万）
哈希拆分	对关键字段（如 user_id）取模：user_id % 100	用户、订单等需均匀分布的数据	user_id=12345，12345%100=45 → 存入 order_main_45
地理拆分	按用户地域（如省份、城市）划分	本地化业务、多区域部署	华北用户订单→order_north，华南用户→order_south

（2）水平拆分示例（按时间分表）

-- 按月份水平分表：2024年1月订单表
CREATE TABLE `order_main_202401` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2024年2月订单表（结构与202401完全一致）
CREATE TABLE `order_main_202402` LIKE `order_main_202401`;

（3）水平拆分的优缺点

优点	缺点
1. 无限扩展数据量（加表 / 加库即可）；2. 均匀分散查询 / 写入压力；3. 支持高并发、海量数据场景	1. 实现复杂，需处理分表路由、跨表查询；2. 事务难度提升（跨库事务需分布式事务）；3. 运维成本增加（多库多表管理）

分表分库的实现方式

根据技术复杂度，分为 “手动实现” 和 “框架实现” 两类：

(1) 手动实现（适合小型系统）

核心逻辑：代码中根据拆分规则，手动指定要操作的表 / 库。

示例（Java 伪代码，按月份分表）：

// 根据创建时间获取对应的分表名
public String getOrderTableName(Date createTime) {
    String month = DateUtil.format(createTime, "yyyyMM");
    return "order_main_" + month;
}

// 查询指定用户2024年1月的订单
public List<Order> queryOrder(Long userId) {
    String tableName = getOrderTableName(DateUtil.parse("2024-01-01"));
    String sql = "SELECT * FROM " + tableName + " WHERE user_id = ?";
    return jdbcTemplate.query(sql, new Object[]{userId}, new OrderRowMapper());
}

缺点：硬编码多、扩展性差，仅适合简单拆分规则。

(2) 框架实现（主流方案）

使用成熟的分库分表框架，自动处理路由、分片、跨表查询，主流框架：

Sharding-JDBC（应用层框架）：轻量级，基于 JDBC 封装，应用直连数据库，无需额外部署中间件，学习成本低，适合中小系统；

MyCat（中间件）：独立部署的数据库中间件，应用连接 MyCat，由 MyCat 转发请求到真实数据库，适合大型分布式系统。

Sharding-JDBC 核心配置示例（按 user_id 哈希分表）：

# application.yml 配置
spring:
  shardingsphere:
    datasource:
      names: ds0  # 数据源名称
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order_db?useUnicode=true
        username: root
        password: root
    rules:
      sharding:
        tables:
          order_main:  # 逻辑表名（代码中使用的表名）
            actual-data-nodes: ds0.order_main_${0..99}  # 真实表：order_main_00~order_main_99
            database-strategy:  # 分库规则（此处仅分表，分库同理）
              none:
            table-strategy:  # 分表规则：按user_id哈希取模
              inline:
                sharding-column: user_id
                algorithm-expression: order_main_${user_id % 100}

分表分库的注意事项

(1) 提前规划拆分规则：

拆分规则一旦确定，后期修改成本极高（如从 “按时间拆分” 改为 “按用户 ID 拆分”，需迁移全量数据），需结合业务特点（如订单查时间、用户查 ID）确定。

(2) 避免跨分片查询：

跨表 / 跨库的 COUNT、SUM、JOIN 等操作性能极差，尽量通过业务设计规避（如统计数据单独存储到报表表）。

(3) 分布式事务处理：

跨库操作需解决事务一致性问题，常用方案：Seata（阿里分布式事务框架）、最终一致性（异步补偿）。

(4) 数据迁移与扩容：

拆分后的数据扩容（如从 100 张表扩到 200 张）需平滑迁移，避免停机，可通过双写、灰度切换实现。

(5) 运维配套：

需配套多库多表的监控（如慢查询、连接数）、备份（多库批量备份）、故障恢复方案。

5. 其他优化细节

设置合理的存储引擎：

MySQL：读多写少用InnoDB（支持事务、行锁），纯查询用MyISAM（不推荐）；

时序数据用TSDB，文档数据用MongoDB（非关系型适配场景）。

控制表字段数量：单表字段不宜过多（建议≤50 个），过多字段会增加行大小，降低查询效率。

添加必要的元字段：统一添加create_time（创建时间）、update_time（更新时间）、is_delete（软删除标识），便于数据追溯和维护。

二、优化示例（MySQL）

反例：设计不合理的订单表

CREATE TABLE `bad_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(255) DEFAULT NULL, -- 无索引，允许NULL
  `user_id` int(11) DEFAULT NULL, -- 无索引，允许NULL
  `user_name` varchar(255) DEFAULT NULL, -- 冗余字段（可联查用户表）
  `product_ids` varchar(255) DEFAULT NULL, -- 多值存储（违反1NF）
  `amount` float DEFAULT NULL, -- 金额用float，精度丢失
  `create_time` datetime DEFAULT NULL, -- 允许NULL，无默认值
  `content` text, -- 大字段直接存储
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

优化后：合理的订单表设计

-- 主表：核心订单信息
CREATE TABLE `order_main` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, -- 用bigint避免主键溢出
  `order_no` varchar(64) NOT NULL COMMENT '订单编号', -- 非NULL，固定长度
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额', -- 精准小数，非NULL
  `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '订单状态：0-待支付，1-已支付',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_delete` tinyint(4) NOT NULL DEFAULT 0 COMMENT '软删除：0-未删，1-已删',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`), -- 订单号唯一索引
  KEY `idx_user_id_create_time` (`user_id`, `create_time`) -- 联合索引，适配用户+时间查询
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- 子表1：订单商品关联表（拆分多值字段）
CREATE TABLE `order_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `product_id` bigint(20) NOT NULL COMMENT '商品ID',
  `product_name` varchar(128) NOT NULL COMMENT '商品名称（冗余，避免联查）',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '商品单价',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品关联表';

-- 子表2：订单扩展信息（拆分大字段）
CREATE TABLE `order_ext` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `content` text COMMENT '订单备注（大字段）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单扩展表';

总结

1.字段设计是基础：优先选择匹配业务的最小字段类型，避免 NULL 和冗余，大字段拆分存储；

2.索引是核心：按需创建高效索引（主键、联合索引），避免冗余和低基数索引；

3.范式与反范式结合：遵循范式减少冗余，在高频查询场景适度反范式提升性能，大数据量时需分表分库。