技术文档

Appearance

数据库索引设计原则 ​

一、索引到底是什么：先讲个图书馆的故事 ​

1. 没有索引的世界，是一场灾难 ​

想象你走进一家拥有 500 万册藏书的图书馆，想找一本《活着》。如果这家图书馆的书架没有任何分类、没有任何检索系统，你只能从第一个书架开始，一本一本地看过去。运气好的话第一天能找到，运气不好可能要找上好几年。

而现实中的图书馆是怎么做的？它有一套检索目录：你告诉系统书名，系统告诉你"在文学区 3 楼 A 排 12 架"，你直接走过去拿，全程不过几分钟。

数据库索引，就是这套"检索目录"。 当数据库里只有几百条数据时，从头翻到尾（专业术语叫"全表扫描"）其实也挺快；但当数据涨到几十万、几百万、几千万条时，有没有索引，就是"毫秒级响应"和"等到天荒地老"的区别。

再比如你查字典，想找"赢"字，你会先翻音序检字表找到 "Y"，再定位到具体的页码，而不是从第 1 页翻到第 1500 页。索引干的就是检字表的活儿：用一份排好序的"目录"，帮你跳过绝大部分无关数据，直达目标。

2. 索引不是"免费的午餐" ​

看到这里你可能会想：既然索引这么好，那把每个字段都加上索引，岂不是查询天下无敌？

这就好比图书馆给每本书都编了 20 套目录：按书名一套、按作者一套、按出版社一套、按字数一套……听起来很美好，但现实是：

  • 目录本身要占地方。书库里一半的架子用来放目录卡片，真正放书的地方反而少了。
  • 每次新书入库，要同时更新 20 套目录。原本登记一本书只要 1 分钟，现在要 20 分钟。
  • 书卖掉了、位置变了，20 套目录全都要改一遍。

数据库也一样：每多一个索引，就要多占一份磁盘空间；每执行一次插入、更新、删除，所有相关索引都要跟着维护一遍。索引加得太多，查询没快多少，写入却慢得令人发指。

所以，索引设计的核心命题不是"要不要加"，而是"加在哪里、加多少、怎么加"。这正是本文要解决的问题。

二、五条核心设计原则：每条都踩过坑才总结出来 ​

原则一：把索引留给"高频选手"，别给"冷门演员" ​

一句话版本：经常用来筛选、排序、关联的字段才配拥有索引。

以电商订单系统为例，一张订单表通常有这些字段：订单ID、用户ID、订单状态、创建时间、金额、收货地址、订单备注……

日常业务中最高频的查询是哪些？

  • "查出这个用户的所有订单" → 用到 user_id
  • "查最近 7 天的新订单" → 用到 create_time
  • "查所有待发货的订单" → 用到 status
  • 订单列表页按时间倒序展示 → 用到 create_time 排序

所以 user_id、create_time、status 是毫无疑问的索引候选人。

反面案例：有人觉得"订单备注"也该加个索引，理由是"万一客服要搜备注呢"。但现实中，按备注精确查找订单的需求一个月都未必有一次。为这个字段建索引，等于给一年只上一次台的替补演员单独配一个化妆间——白白占用空间，还拖慢每一次订单写入。

判断标准很简单：打开你的慢查询日志和业务代码，统计一下哪些字段频繁出现在 WHERE、ORDER BY、JOIN ... ON 里。高频出现的建索引，一年用不上一次的，别建。

原则二：联合索引要守"最左前缀"的规矩 ​

一句话版本：多字段联合索引就像查字典，必须先按第一个音序查，后面的条件才有意义。

假设你给订单表建了一个联合索引 (user_id, create_time)。这个索引在底层的排列方式是：先按 user_id 排序，user_id 相同的内部再按 create_time 排序——就像字典先按拼音排序，同音字再按笔画排序。

基于这个排序规则，各种查询的索引生效情况如下：

sql
-- ✅ 完全生效：两个字段都用上了
SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2026-08-01';

-- ✅ 生效：用了最左边的 user_id
SELECT * FROM orders WHERE user_id = 1001;

-- ✅ 生效：user_id 定位 + create_time 天然有序，排序都不用额外做
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC;

-- ❌ 失效：跳过了最左边的 user_id，索引帮不上忙
SELECT * FROM orders WHERE create_time > '2026-08-01';

为什么最后一条会失效？回到字典的例子：字典目录是"先拼音、后笔画"排的。如果你只知道一个字的笔画数，却不知道读音，目录对你毫无用处，只能一页页翻。同理，索引先按 user_id 排列，抛开 user_id 单查 create_time，数据库在索引里根本无从下手。

错误示范：有人建了 (create_time, user_id) 的索引（字段顺序反了），结果业务里最高频的"查某用户的订单"查询用不上索引，反而冷门查询占了便宜。联合索引的字段顺序，应该按"最常出现在查询条件里的字段放最左"来设计，等值条件优先于范围条件。

原则三：看字段"基数"下菜碟 ​

一句话版本：基数 = 字段不重复的值多不多。值越多样，索引越有价值；值翻来覆去就那几个，索引基本是摆设。

"基数"（Cardinality）这个词听着唬人，其实概念很生活化：一个字段的基数，就是它不重复取值的数量占总数据量的比例。

  • 用户ID：100 万个用户就有接近 100 万个不同的值 → 高基数
  • 订单状态：翻来覆去就"待付款、待发货、已发货、已完成、已取消"5 种 → 低基数

为什么高基数字段适合建普通 B+ 树索引？ 因为索引的价值在于"缩小搜索范围"。按用户ID查，一次就能从 100 万条缩小到几条，效率提升立竿见影。

为什么低基数字段用普通索引效果差？ 按"订单状态 = 待付款"查，可能一下子就命中 30 万条。数据库拿着索引找到 30 万个位置，还得回表 30 万次（回表的概念下文会讲），算下来比直接全表扫描还慢——优化器遇到这种情况，往往会干脆放弃索引。

那低基数字段就没办法了吗？在支持位图索引（bitmap index）的数据库里（如 Oracle、部分分析型数据库），低基数字段是位图索引的绝配：它用一串 0 和 1 的位图标记每行是否满足条件，存储极小，做"状态=A 且 类型=B"这类组合筛选时按位运算，速度飞快。典型的应用场景是数据仓库里的统计分析。注意：MySQL 的 InnoDB 不支持位图索引，在 MySQL 里遇到低基数字段，更好的做法是把它和高基数字段组合成联合索引，比如 (user_id, status)，先按用户缩小范围，再在用户内部筛状态。

重复度极高的字段（比如整张表 95% 的行都是同一个值），则完全不值得建独立索引——建了优化器也不用，纯属浪费。

原则四：克制！单表索引别超过 5~8 个 ​

一句话版本：索引是"写入时交的税"，加得越多，写入越慢。

很多慢查询是索引太少造成的，但同样多的性能问题是索引太多造成的。经验法则是：单表索引数量控制在 5~8 个以内。

为什么？因为每一次 INSERT、UPDATE、DELETE，数据库不只是改数据本身，还要同步维护这张表上的每一个索引。假设一张表有 10 个索引，插入一条订单就要同时往 11 个地方写数据（1 份数据 + 10 份索引）。更要命的是，索引底层是 B+ 树，插入可能引发节点分裂、页面重组，成本远高于简单地"追加一行"。

在秒杀、大促这类写入洪峰场景下，索引过多的表会第一个倒下：写入延迟飙升，连接池被打满，整个系统雪崩。

索引还需要"售后维护"：业务是在迭代的。上个版本的核心功能可能是"按渠道筛选订单"，于是加了渠道字段索引；这个版本功能下线了，但索引还留在那里，默默地拖累每一次写入。建议每个季度做一次索引审计：找出那些从未被使用的索引（MySQL 可以查 sys.schema_unused_indexes），确认无业务依赖后果断删除。索引和代码一样，也需要定期"断舍离"。

原则五：能用覆盖索引，就别让数据库"回表"跑腿 ​

一句话版本：回表 = 找到书的位置后还要进书库拿书；覆盖索引 = 目录卡片上直接写着你要的答案。

先解释"回表"。MySQL InnoDB 里，主键索引的叶子节点直接存着整行数据（这叫聚簇索引），而普通索引的叶子节点只存"索引字段的值 + 主键值"。

用普通索引查询时，过程是这样的：

普通索引里找到 user_id=1001 的记录 → 拿到主键 order_id
→ 再拿 order_id 去主键索引里找一次 → 才能拿到完整的订单行

多走的这一趟，就叫"回表"。 查一条回一次表还能忍，查 10 万条回 10 万次表，性能直接崩掉。

覆盖索引的思路是：把查询需要的字段都塞进索引里，让数据库在索引上直接拿到所有答案，不用回表。

反面案例（要回表）：

sql
-- 只有 (user_id) 索引，但还要取 status 和 amount
SELECT status, amount FROM orders WHERE user_id = 1001;

优化写法（覆盖索引）：把索引改成 (user_id, status, amount)，同样的 SQL 直接在索引里就能拿到 status 和 amount，一次索引查询搞定，省掉全部回表开销。

注意分寸：覆盖索引不是让你把整张表所有字段都塞进索引——那就等于复制了一份表，写入成本翻倍。只对查询频率极高、返回字段少而固定的核心 SQL 设计覆盖索引，比如"用户订单列表页只展示状态、金额、时间"这种场景。

三、索引失效避坑指南：这些写法会让索引白建 ​

建了索引却用不上，是开发中最常见的"冤案"。以下几个场景，每个都配了错误写法和正确写法。

坑 1：在索引字段上使用函数 ​

sql
-- ❌ 错误：对 create_time 用了函数，索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2026-09-01';

-- ✅ 正确：改用范围查询，索引正常生效
SELECT * FROM orders
WHERE create_time >= '2026-09-01 00:00:00'
  AND create_time <  '2026-09-02 00:00:00';

原理：索引里存的是原始值，你对它做了计算，数据库就无法在索引中直接定位了。就像目录里登记的是完整书名，你却问"书名的前三个字是'活着'的书在哪"——目录帮不了你。

坑 2：隐式类型转换 ​

sql
-- ❌ 错误：phone 字段是字符串类型，查询却传了数字
SELECT * FROM users WHERE phone = 13800000000;

-- ✅ 正确：类型保持一致，加上引号
SELECT * FROM users WHERE phone = '13800000000';

原理：字段是字符串、查询给数字时，MySQL 会偷偷把字段值转成数字再比较——等于对每一行都调了一次转换函数，索引随之失效。这种坑特别隐蔽，SQL 不报错，只是慢。

坑 3：不等于判断（!= 或 <>） ​

sql
-- ❌ 慎用：不等于判断通常无法有效利用索引
SELECT * FROM orders WHERE status != '已完成';

-- ✅ 优化：如果业务允许，改成明确的范围或枚举
SELECT * FROM orders WHERE status IN ('待付款', '待发货', '已发货');

原理："不等于"意味着命中绝大多数数据，优化器一算账，觉得全表扫描更划算，就放弃索引了。改成正向枚举，把范围收窄，索引才用得上。

坑 4：模糊查询以通配符开头 ​

sql
-- ❌ 错误：% 开头的 LIKE 无法使用索引
SELECT * FROM products WHERE name LIKE '%手机%';

-- ✅ 优化一：如果业务允许，只用前缀匹配（可以用索引）
SELECT * FROM products WHERE name LIKE '华为%';

-- ✅ 优化二：确实需要全文检索的场景，改用全文索引或搜索引擎
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');
-- 或者把搜索需求交给 Elasticsearch

原理：索引是按字段值从左到右排序的，"以什么开头"能查，"包含什么"没法查——就像字典目录能帮你找"张"字头的姓，却帮不了你找"名字里带'伟'的所有人"。

坑 5：OR 连接了没有索引的字段 ​

sql
-- ❌ 错误：user_id 有索引但 email 没有，整体退化为全表扫描
SELECT * FROM users WHERE user_id = 1001 OR email = 'a@b.com';

-- ✅ 优化一：给 email 也加上索引
-- ✅ 优化二：拆成 UNION，两条腿各自走索引
SELECT * FROM users WHERE user_id = 1001
UNION
SELECT * FROM users WHERE email = 'a@b.com';

坑 6：联合索引跳过最左字段 ​

这个在原则二已经详细讲过，再强调一次：建了 (user_id, create_time)，却只按 create_time 查询，索引失效。建索引前先想清楚业务到底怎么查。

最后的兜底建议：写完 SQL 别急着上线，先加 EXPLAIN 跑一下，看看 key 列有没有用上预期的索引、rows 列扫描了多少行。这是验证索引是否生效的唯一可靠标准，比任何经验都靠谱。

四、场景化索引设计模板：拿来即用 ​

模板 1：电商订单系统 ​

业务特点：写入频繁、按用户/时间/状态查询为主、有列表分页。

sql
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  create_time DATETIME NOT NULL,
  update_time DATETIME NOT NULL,
  remark VARCHAR(500),

  -- 用户订单列表页：按用户查、按时间排序（覆盖常用场景）
  KEY idx_user_time (user_id, create_time),
  -- 运营后台：按状态+时间筛选（如"最近待发货订单"）
  KEY idx_status_time (status, create_time)
) ENGINE = InnoDB;

设计说明：主键自增保证写入顺序友好；idx_user_time 同时支撑"查用户订单"和"按时间排序"；idx_status_time 支撑运营筛选；备注字段不建索引。

模板 2：社交 Feed / 关注关系 ​

业务特点：读多写多、关系查询（"我关注了谁"）极频繁、按时间线展示。

sql
CREATE TABLE follow (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  follower_id BIGINT NOT NULL,      -- 关注者（我）
  followee_id BIGINT NOT NULL,      -- 被关注者（Ta）
  create_time DATETIME NOT NULL,

  -- 防重复关注
  UNIQUE KEY uk_pair (follower_id, followee_id),
  -- "我的关注列表"：查我关注的所有人，按关注时间排序
  KEY idx_follower_time (follower_id, create_time),
  -- "我的粉丝列表"：查谁关注了我
  KEY idx_followee_time (followee_id, create_time)
) ENGINE = InnoDB;

设计说明：uk_pair 唯一索引一箭双雕——既防重复数据，又支撑"我是否关注了 Ta"的查询；两个方向的关系查询各配一个索引，这是社交类表的标准姿势。

模板 3：内容管理系统（CMS 文章） ​

业务特点：读多写少、按分类/状态/时间展示、有标题搜索需求。

sql
CREATE TABLE article (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  category_id INT NOT NULL,
  author_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,       -- draft/published/offline
  publish_time DATETIME,
  content TEXT,

  -- 前台列表页：按分类筛已发布文章，按发布时间倒序
  KEY idx_category_status_time (category_id, status, publish_time),
  -- 作者后台：查"我的文章"
  KEY idx_author_time (author_id, publish_time),
  -- 标题搜索：用全文索引替代 LIKE '%xx%'
  FULLTEXT KEY ft_title (title)
) ENGINE = InnoDB;

设计说明：前台最高频的查询是"某分类下的已发布文章列表"，所以把 category_id、status、publish_time 组合成联合索引，等值条件在前、排序字段在后；正文内容不参与任何索引；标题搜索交给全文索引。

五、结语：索引设计是一种"取舍的艺术" ​

回顾全文，索引设计的所有原则其实都指向同一个朴素的道理：索引是用"空间"和"写入成本"换"查询速度"的买卖，好的设计就是让这笔买卖的收益最大化。

记住这几句话，就掌握了索引设计的精髓：

  1. 索引是图书馆的检索目录，不是越多越好，够用就好。
  2. 高频筛选、排序、关联字段优先建索引；冷门字段别浪费。
  3. 联合索引守最左前缀，字段顺序按业务查询频率排。
  4. 高基数用 B+ 树，低基数谨慎（或考虑位图索引/联合索引），重复度极高的字段别建。
  5. 单表索引控制在 5~8 个，定期清理废弃索引。
  6. 能用覆盖索引就别回表。
  7. 写完 SQL 用 EXPLAIN 验证，别凭感觉。

最后想说：索引设计没有银弹，最好的老师永远是你的真实业务数据和慢查询日志。原则提供方向，数据验证结论。愿你的每一条 SQL 都快如闪电。

网站备案号:津ICP备15001525号-2