数据库设计优化：表结构设计原则、索引策略、分区表与分库分表

数据库设计，说白了就是给数据找个好房子住。房子盖得不好，住的人多了就得出问题。我在回扣交易系统里踩过不少坑，今天把这些经验掰开揉碎了讲给你听。

一、表结构设计原则

先说说表结构。我见过太多人一上来就建表，结果上线后天天改字段。嗯，这里有几个原则你得记住。

1. 字段类型选择要抠门

能用 INT 就别用 BIGINT，能用 VARCHAR(50) 就别用 VARCHAR(255)。为什么？

• 存储空间：一个 BIGINT 占 8 字节，INT 只占 4 字节。你想想看，一亿条记录就差 400MB。
• 查询速度：数据页能装下的行数更多，IO 次数自然就少了。
• 索引效率：索引树更矮，查询更快。

2. 范式与反范式要平衡

第三范式是理论上的好学生，但实际业务中往往需要反范式。我在项目中遇到过这样一个场景：

回扣交易系统里，订单表需要频繁查询用户姓名和商品名称。如果严格遵循第三范式，每次都要 JOIN 用户表和商品表。结果呢？一个简单的列表页，查询耗时 3 秒多。

我的做法是：在订单表里冗余了 user_name 和 product_name 字段。虽然违反了第三范式，但查询速度从 3 秒降到了 0.1 秒。

3. 预留字段是个坑

很多人喜欢在表里加 reserve1、reserve2 这样的预留字段。我劝你千万别这么干。

• 预留字段的类型和长度是固定的，业务需求来了往往不匹配。
• 代码里到处都是 reserve1，可读性极差。
• MySQL 对 NULL 值的处理有额外开销。

更好的做法是：用 JSON 类型存储扩展字段。MySQL 5.7 之后对 JSON 的支持已经很好了，可以建虚拟列加索引。

二、索引策略

索引这东西，用好了是神器，用不好就是累赘。我见过有人一张表建了 20 个索引，结果写入速度比蜗牛还慢。

1. 索引不是越多越好

每个索引都是一棵 B+ 树。写入数据时，所有索引都要更新。你想想看，10 个索引就是 10 棵树要维护。

索引数量	查询提升	写入损耗
0-3 个	明显	可忽略
4-6 个	边际递减	开始明显
7 个以上	几乎无提升	严重影响写入

我建议：单表索引控制在 5 个以内。超过这个数，你得想想是不是表结构设计有问题。

2. 联合索引的字段顺序

联合索引遵循最左前缀原则。举个例子：

-- 创建联合索引
ALTER TABLE order ADD INDEX idx_user_status_time (user_id, status, create_time);

-- 能用到索引的查询
SELECT * FROM order WHERE user_id = 1001;
SELECT * FROM order WHERE user_id = 1001 AND status = 1;
SELECT * FROM order WHERE user_id = 1001 AND status = 1 AND create_time > '2024-01-01';

-- 用不到索引的查询
SELECT * FROM order WHERE status = 1;
SELECT * FROM order WHERE create_time > '2024-01-01';

字段顺序怎么排？记住一句话：区分度高的放前面。比如 user_id 的区分度远高于 status，所以放第一个。

3. 覆盖索引是个好东西

覆盖索引的意思是：索引里已经包含了查询需要的所有字段，不需要回表查数据行。这能省掉一次 IO 操作。

-- 假设有索引 idx_user_id (user_id)
-- 这个查询需要回表
SELECT user_id, user_name FROM order WHERE user_id = 1001;

-- 改成覆盖索引
ALTER TABLE order ADD INDEX idx_user_id_name (user_id, user_name);
-- 现在这个查询直接从索引里拿数据，不用回表
SELECT user_id, user_name FROM order WHERE user_id = 1001;

我在回扣交易系统里，把高频查询的字段都塞进了索引里。查询性能提升了 30% 以上。

三、分区表

分区表不是银弹，但用对场景效果很好。说白了就是把一张大表拆成多个小表，查询时只扫描相关分区。

1. 什么时候用分区表

• 数据量大：单表超过 500 万行，或者超过 10GB。
• 有明显的时间维度：比如按月份分区。
• 需要定期清理历史数据：直接 TRUNCATE 分区比 DELETE 快得多。

2. 分区类型选择

分区类型	适用场景	示例
RANGE	按时间、ID 范围	PARTITION BY RANGE (YEAR(create_time))
LIST	按枚举值	PARTITION BY LIST (status)
HASH	均匀分布数据	PARTITION BY HASH (user_id) PARTITIONS 8
KEY	类似 HASH，但用 MySQL 内置函数	PARTITION BY KEY (id) PARTITIONS 8

我个人习惯：回扣交易系统里，订单表按 create_time 做 RANGE 分区，每个月一个分区。查询最近三个月的订单，只扫描三个分区，速度杠杠的。

3. 分区表的坑

四、分库分表

分区表解决不了所有问题。当单库的写入压力太大，或者单表数据量超过 1 亿行时，就得考虑分库分表了。

1. 什么时候需要分库分表

• 单库连接数不够：MySQL 默认最大连接数 151，虽然可以调大，但 CPU 和内存扛不住。
• 单表数据量过大：超过 1 亿行，索引深度增加，查询变慢。
• 写入瓶颈：单库的写入 TPS 达到上限。

2. 分片策略

分片键的选择是关键。选错了，后面全是坑。

-- 按 user_id 取模分片
-- 分 16 个库，每个库 16 张表
-- 分片规则：user_id % 16 = 库编号，user_id / 16 % 16 = 表编号

-- 查询时带上 user_id
SELECT * FROM order_${dbIndex}.order_${tableIndex} 
WHERE user_id = 1001 AND order_id = 20240101001;

我建议：分片键要满足两个条件：

1. 查询频率高：90% 以上的查询都带这个字段。
2. 分布均匀：不能出现热点数据。

3. 分库分表的挑战

分库分表不是银弹，它带来了新的问题：

• 跨库查询：不能 JOIN 了，得在应用层做聚合。
• 分布式事务：需要引入 Seata 或 TCC 方案。
• 全局主键：不能用自增 ID 了，得用雪花算法或 Leaf。
• 数据迁移：扩缩容时数据要重新分布，非常麻烦。

五、知识体系总览

下面这张图是我自己整理的数据库设计优化知识体系，你可以照着这个思路来排查问题。

好了，数据库设计优化这块就聊这么多。记住一句话：没有最好的方案，只有最合适的方案。根据你的业务场景和数据量，选择合适的优化手段，别一上来就搞分库分表，那玩意儿维护成本真不低。