索引设计与优化

数据建模的目标不是把需求里的名词翻译成表名和字段名，而是让数据能够稳定地写入、查询、演进和治理。对于 MySQL 这类 OLTP 数据库来说，表结构和索引设计基本决定了系统上线后的性能上限。

很多数据库问题看起来是 SQL 慢、锁等待、CPU 高、主从延迟，本质上往往是建模阶段没有把访问模式想清楚。本篇总结一些日常业务中常用的 MySQL 建模和索引设计方法。

从访问模式开始

建表前先回答四个问题：

谁会写入这张表：写入频率是多少，是否存在批量导入、异步回写、状态流转。
谁会查询这张表：核心查询条件是什么，是否需要分页、排序、聚合、模糊搜索。
事务边界在哪里：哪些字段必须在同一事务内修改，哪些可以异步补偿。
数据如何增长和归档：单表数据量、冷热分布、保留周期和删除策略是什么。

如果无法说清楚核心查询模式，就不要急着建索引。索引是为了服务查询路径，不是为了让表结构看起来完整。

基础建表规范

除非有特殊原因，业务表建议遵循以下规范：

使用 InnoDB 存储引擎。
使用 utf8mb4 字符集。
表名和字段名使用小写字母加下划线，避免拼音、缩写和数据库保留字。
表名尽量带业务域或模块前缀，比如 payment_order、shipping_address。
字典表可以使用 dict 前缀，比如 dict_country、payment_dict_order_status。
表和字段需要写清楚注释，枚举字段需要说明每个枚举值的含义。
禁止在高并发核心链路中依赖存储过程、触发器、复杂视图等数据库侧逻辑。
外键约束谨慎使用，高并发业务通常由应用层维护完整性。

常见公共字段可以统一为：

字段	类型	说明
`id`	`BIGINT UNSIGNED`	物理主键，单表自增或由发号器生成
`create_time`	`DATETIME`	创建时间
`update_time`	`DATETIME`	更新时间
`delete_time`	`DATETIME`	逻辑删除时间，未删除时可为空

对于分布式系统，主键是否使用数据库自增要结合部署方式判断。单库单表场景下，自增主键简单高效；分库分表、跨服务写入或需要提前生成 ID 的场景，可以使用分布式 ID，但要注意随机 ID 对聚簇索引写入的影响。

字段类型选择

字段类型要尽量贴近业务语义，并且越小越好。类型越大，单页能存放的记录越少，索引占用越大，Buffer Pool 命中率也会下降。

常见建议如下：

金额使用 DECIMAL，不要使用浮点数。
状态、类型等枚举值可以使用 TINYINT 或 SMALLINT，并用注释说明含义。
时间字段使用 DATETIME 或根据团队规范选择 TIMESTAMP，不要使用字符串存储时间。
固定长度字段使用 CHAR，可变长度字段使用 VARCHAR。
能使用整数表达的标识，不要使用长字符串作为高频索引字段。
除非业务上需要区分“未知”和“空值”，字段尽量设置为 NOT NULL。
大文本、JSON、附件地址等低频访问字段可以考虑拆到扩展表中。

字段不是越“灵活”越好。比如把结构化属性全部塞进 JSON，短期开发很快，但后续如果要过滤、排序、统计、校验、迁移，成本会回到业务系统里。

主键设计

InnoDB 的表数据按照主键构成聚簇索引，因此主键设计会影响所有数据访问。

一个好的主键通常具备以下特点：

短：二级索引叶子节点会保存主键值，主键越大，所有二级索引越大。
稳定：主键不应该因为业务属性变化而变化。
尽量有序：顺序写入更容易减少页分裂和随机 IO。
无业务含义或弱业务含义：用唯一索引约束业务唯一性，用主键承担物理定位。

不建议直接使用手机号、邮箱、订单号等业务字段作为物理主键。业务字段可能变更、脱敏、合规删除，也可能长度过大，作为主键会把这些问题放大到所有二级索引。

联合索引设计

联合索引不是把所有查询字段堆在一起，而是要根据查询模式排序。一个常用的判断顺序是：

等值匹配字段放前面。
区分度高、过滤能力强的字段优先。
范围查询字段放在后面。
排序字段尽量放在索引中，减少 filesort。
查询字段能被索引覆盖时，可以减少回表。

例如订单表常见查询：

SELECT id, status, amount
FROM payment_order
WHERE user_id = ?
  AND status = ?
  AND create_time >= ?
  AND create_time < ?
ORDER BY create_time DESC
LIMIT 20;

可以考虑建立：

KEY idx_user_status_time (user_id, status, create_time)

这里 user_id 和 status 是等值条件，create_time 既用于范围过滤又用于排序。这个索引能帮助数据库快速定位某个用户某类订单的时间范围。

需要注意，联合索引遵循最左前缀原则。如果索引是 (user_id, status, create_time)，那么只按 status 查询通常无法有效使用这个索引。索引顺序要服务真实查询，而不是字段在表里的排列顺序。

覆盖索引与回表

如果一条 SQL 需要的字段都在二级索引中，InnoDB 可以直接从二级索引返回结果，不需要再通过主键回到聚簇索引读取完整行，这就是覆盖索引。

覆盖索引适合高频、字段较少、分页或列表类查询。比如：

SELECT id, status, create_time
FROM payment_order
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 20;

如果建立 (user_id, create_time, status)，并且 id 是主键，二级索引叶子节点天然包含主键值，那么这个查询可以少很多回表成本。

但覆盖索引也不能滥用。为了覆盖少数查询而把很多字段都塞进索引，会增加写入成本、占用更多内存，并让索引维护变得困难。

分页与排序

深分页是常见慢查询来源。下面这种写法在页码很深时会扫描并丢弃大量记录：

SELECT *
FROM payment_order
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 100000, 20;

更好的方式是基于游标或上一页最后一条记录继续查询：

SELECT *
FROM payment_order
WHERE user_id = ?
  AND create_time < ?
ORDER BY create_time DESC
LIMIT 20;

如果需要稳定排序，建议在时间字段后追加主键作为兜底排序条件，避免同一时间戳下结果顺序不稳定：

ORDER BY create_time DESC, id DESC

对应索引也要考虑这个排序方式，比如 (user_id, create_time, id)。

低效查询模式

以下写法容易导致索引失效或扫描范围扩大：

在索引字段上使用函数或表达式，例如 DATE(create_time) = ?。
前缀模糊查询，例如 name LIKE '%keyword'。
字段类型不一致导致隐式转换。
OR 两侧条件无法同时使用合适索引。
负向条件过多，例如 !=、NOT IN。
查询条件跨越多个低区分度字段，却没有高选择性条件。
使用 SELECT * 返回不必要的大字段。

对于搜索、复杂过滤、全文检索、聚合分析等需求，不要强行压在 MySQL 单表查询上。合适的时候应该引入 Elasticsearch、ClickHouse、缓存、异步宽表或专门的查询模型。

索引数量控制

索引可以提升查询性能，但每个索引都会带来成本：

写入、更新、删除时需要同步维护索引。
索引会占用磁盘和 Buffer Pool。
优化器需要在更多索引之间做选择。
重复或近似索引会增加维护成本。

常见的重复索引包括：

KEY idx_user (user_id),
KEY idx_user_status (user_id, status)

如果没有只按 user_id 查询的特殊性能要求，idx_user 很可能可以被 idx_user_status 覆盖。索引治理的目标不是越少越好，而是每个索引都能说清楚服务哪条核心查询。

事务与操作规范

表结构和索引之外，还需要一些操作层面的规范：

写更新、删除语句时，先写 WHERE 条件，再补 SET 或 DELETE。
更新和删除必须命中索引，避免大范围锁等待。
单个事务不要包含用户交互、远程调用或大批量循环处理。
大批量修复数据要分批执行，并观察主从延迟和锁等待。
线上表结构变更需要评估锁表、回滚、复制延迟和业务兼容性。
不要轻易删除列，优先走废弃、灰度、观察、清理的流程。

数据库最怕“看起来只改一行”的操作背后实际扫描了全表。上线前用 EXPLAIN 看执行计划，用压测或影子流量验证核心 SQL，是数据建模落地的一部分。

建模检查清单

最后给一份简单的检查清单：

核心查询 SQL 是否已经列出来。
每个高频查询是否有明确索引支持。
主键是否短、稳定、尽量有序。
唯一约束是否由唯一索引保证。
字段类型是否足够精确，是否存在过度使用字符串或 JSON。
是否存在 SELECT *、深分页、前缀模糊匹配等高风险查询。
索引是否有重复、过宽或长期不用的情况。
数据增长后是否需要归档、分区、分库分表或冷热分离。

好的数据模型不是一次设计到完美，而是在业务访问模式变化时仍然能被理解、调整和演进。表结构和索引是系统里最难回滚的代码，前期多花一点时间，往往能换来后面少很多线上救火。

Star 关注

总字数:2552字

# 索引设计与优化

# 从访问模式开始

# 基础建表规范

# 字段类型选择

# 主键设计

# 联合索引设计

# 覆盖索引与回表

# 分页与排序

# 低效查询模式

# 索引数量控制

# 事务与操作规范

# 建模检查清单