点滴生活

Appearance

索引设计 ​

1. 这是什么 ​

索引是数据库为了加速查询而维护的数据结构。
索引设计的目标，是让常用查询路径更快、更稳定。

一句话理解：

  • 索引不是“额外附加品”
  • 它本质上是在提前为高频查询修一条更快的路

2. 为什么重要 ​

数据库性能问题里，索引通常是影响最大的因素之一。
索引设计不合理，会导致：

  • 全表扫描
  • 回表过多
  • 写入放大
  • 维护成本增加

3. 先建立直觉：索引不是越多越好 ​

很多人一看到慢 SQL，就会本能地：

  • 再加一个索引

这很危险。
索引虽然提升读性能，但也会带来代价：

  • 写入变慢
  • 更新变慢
  • 删除变慢
  • 占用更多空间

所以真正的问题不是：

  • 能不能建索引

而是：

  • 这个索引值不值得建

4. 核心内容 ​

4.1 主键索引与二级索引 ​

在 InnoDB 里，最重要的区别是：

  • 主键索引叶子节点里保存整行数据
  • 二级索引叶子节点里通常保存主键值

这意味着：

  • 二级索引查到结果后，可能还要再回主键索引取完整数据

4.2 联合索引 ​

联合索引是把多个字段按顺序放进一个索引中，例如：

sql
KEY idx_user_status_created (user_id, status, created_at)

联合索引的价值在于：

  • 同时服务多列组合查询
  • 降低多索引分散带来的成本

4.3 覆盖索引 ​

如果查询所需字段全部在索引中，就不需要再回表。
这通常意味着：

  • 查询更轻
  • I/O 更少

所以覆盖索引往往很高效。

4.4 回表 ​

如果查询命中了二级索引，但要取的字段不全在索引里，就需要：

  1. 先从二级索引查到主键
  2. 再回主键索引里拿整行

这个额外过程就是回表。

4.5 最左前缀匹配 ​

联合索引能否高效使用，和列顺序强相关。
最左前缀的核心意思是：

  • 查询条件要尽量从联合索引左边开始连续匹配

例如：

  • (user_id, status, created_at)

更容易服务：

  • user_id
  • user_id + status
  • user_id + status + created_at

4.6 选择性与索引顺序 ​

字段顺序不仅要看语法，还要看业务查询模式和数据分布。
高频条件、过滤性更强的字段，常常更值得优先考虑。

5. 学习重点 ​

这一章最重要的是掌握：

  • 索引不是越多越好
  • 联合索引顺序必须围绕高频查询设计
  • 覆盖索引和回表会直接影响性能
  • 最左前缀是联合索引设计的关键规则

6. 常见问题 ​

6.1 一有慢 SQL 就盲目加索引 ​

这会很快把表变成“读快一点、写慢很多”的状态。

6.2 联合索引顺序凭感觉决定 ​

这样最容易出现“明明建了索引，但效果不理想”的问题。

6.3 写操作多的表建立过多索引 ​

索引维护成本会被放大得很明显。

7. 动手验证 ​

当前环境没有 mysql 客户端，所以这里提供可直接复制到 MySQL 环境执行的实验脚本。

7.1 建测试表 ​

sql
DROP TABLE IF EXISTS order_idx_demo;

CREATE TABLE order_idx_demo (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    remark VARCHAR(255) DEFAULT NULL,
    KEY idx_user_status_created (user_id, status, created_at),
    KEY idx_status (status)
);

7.2 观察联合索引命中 ​

sql
EXPLAIN
SELECT user_id, status, created_at
FROM order_idx_demo
WHERE user_id = 1001
  AND status = 'PAID'
ORDER BY created_at DESC;

重点观察：

  • key
  • rows
  • Extra

7.3 观察覆盖索引 ​

上面那条查询只取了联合索引里的列，更适合观察覆盖索引效果。
再对比这条：

sql
EXPLAIN
SELECT *
FROM order_idx_demo
WHERE user_id = 1001
  AND status = 'PAID'
ORDER BY created_at DESC;

你可以观察：

  • 为什么取全部列时更容易回表

7.4 观察最左前缀影响 ​

sql
EXPLAIN
SELECT *
FROM order_idx_demo
WHERE status = 'PAID'
  AND created_at >= '2026-01-01 00:00:00';

它和联合索引 (user_id, status, created_at) 的顺序不完全匹配。
你可以观察：

  • 为什么这条查询未必能很好利用联合索引

8. 练习建议 ​

  • 设计几个典型查询的索引方案
  • 分析索引调整前后的执行计划
  • 总结索引设计的基本准则
  • 对比“覆盖索引”和“需要回表”的查询差异

9. 自测问题 ​

  • 联合索引为什么和字段顺序强相关？
  • 覆盖索引为什么常常更高效？
  • 什么情况下索引反而会带来负担？
  • 回表为什么会增加查询成本？

10. 自测核对要点 ​

  • 索引设计必须围绕查询路径和数据分布
  • 联合索引顺序会直接决定利用效果
  • 覆盖索引通常优于需要回表的查询
  • 索引收益和写入维护成本必须一起衡量