数据库索引基础
一年前只要 4 毫秒的查询，如今烧掉 800 毫秒。表从 4 万行涨到了 4000 万行，可每一次读都是全表扫描——因为从来没人建索引。加一个正确的索引，一次发布就能把查询打回 2 毫秒；加一个错误的索引，除了拖慢每次写入，什么都没改变。这就是数据库索引的全部故事：几字节的元数据可能比一 TB 内存更值钱，但前提是你得放在对的地方。
索引是一种独立的有序数据结构（通常是 B 树），它把列的值映射到磁盘位置，让数据库不用扫表就能找到行。你可以把它想象成图书馆的卡片目录：书在书架上乱序摆放（堆表），但目录能精确告诉你书在哪一层书架。
一个索引到底要花多少钱
索引不是免费的。每个索引都占用存储，而每次插入、更新、删除都要维护表上的所有索引，给写入增加时延。权衡的是"现在的读速度"对"永远的写开销"。一张有五个索引的表，插入速度可能比没索引的表慢 20%–40%，具体取决于你的数据库和索引类型。
正因为如此，索引是深思熟虑的投资，而不是默认项。最好的索引服务你的应用真正反复跑的查询——登录校验、订单历史、去重查找——而不是恰好出现在表结构里的那些列。与其凭猜，最好的做法是用 EXPLAIN 看真实执行计划。想搭好底层的表结构基础，建议先看我们的 数据分析基础。国内做数据仓时也常配合 数据工程基础 一起看。
机制：B 树、哈希与覆盖索引
大多数数据库索引是 B 树：平衡树让数据保持有序，点查询和范围扫描都做到 O(log n)。相比之下，哈希索引只支持精确相等查询、不能做范围查询，这解释了为什么通用数据库里 B 树占主导。
覆盖索引（covering index）包含查询所需的全部列，数据库能完全从索引回答而不碰表。这是索引里杠杆最高的优化：一个 (customer_id, order_date) 上的覆盖索引，可以"按月份统计我的订单数"这种查询做到零访问表。
查询优化器怎么用索引
你并不"使用"索引——是查询优化器决定要不要用它。优化器估算每种策略返回多少行，挑选最便宜的。它可能无视一个完美的索引，如果列被包在函数里：WHERE EXTRACT(YEAR FROM order_date) = 2026 用不上 order_date 上的索引，而 WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' 可以。基数规则也重要：如果查询命中全表 5%–20% 以上的行，优化器常直接跳过索引，因为顺序读全表更便宜。
所以你应该看执行计划而不是猜。主流数据库都会展示真实计划：PostgreSQL 和 MySQL 用 EXPLAIN，SQLite 用 EXPLAIN QUERY PLAN，SQL Server 的 SSMS 有图形计划。
按场景做索引决策
你需要哪个索引取决于你要服务哪个查询。常见场景这样对应到索引选择：
- 主键查找：主键会自动得到唯一索引，绝不重复建。
- 外键 JOIN：子表上被 JOIN 和 WHERE 引用的列应该建索引——这是真实表里最常被遗忘的索引。
- 单列过滤：等值过滤通常一个单列索引就够。
- 多列过滤：把选择性最强的列放最前面的联合索引，胜过多个单列索引。
- 排序与范围：被索引的列能满足 ORDER BY 和范围扫描而无需额外排序。联合索引里把等值列放前、范围/排序列放后。
- 只索引查询：把 SELECT 的列加进索引让它成为覆盖索引，彻底跳过表访问。
联合索引 (status, created_at) 很适合"找最近 50 条待处理订单"：先按 status 等值收窄、再用 created_at 范围排序。如果把列序反过来——(created_at, status)——对这条查询就完全没帮助。
为什么绝不要"全列建索引"
新手常在每个过滤列上都加索引，然后奇怪为什么写入变慢、数据库膨胀。这个策略三重重挫：浪费存储、拖慢所有写入、让优化器挑到次优索引被判糊涂。纪律是基于观察到的查询模式索引，而不是基于假设。想让查询写得又高效又规范，可以系统学一下 Python 自动化脚本 来批量分析慢查询，管理产线数据时也可以把 数据库管理 纳入日常。
主流数据库索引能力对比
索引是通用的，但每个数据库都有自己的口味。知道你的数据库白送哪些能力会改变你的设计方式。
| 数据库 | 核心索引能力 | 参考定价 |
|---|---|---|
| PostgreSQL | B 树、哈希、GIN、GiST、BRIN；部分与表达式索引；EXPLAIN 输出丰富 | 开源免费；阿里云/腾讯云托管实例从每月约 100 元档起 |
| MySQL | InnoDB 用 B 树、MEMORY 表用哈希，FULLTEXT 与空间索引，支持 EXPLAIN | 开源免费；云托管 RDS 从每月约 100 元档起 |
| SQL Server | 聚集与非聚集索引、筛选与 columnstore 索引、数据库引擎优化顾问 | Express 免费（限 10 GB）；商用版按授权计费 |
| SQLite | B 树索引，3.8 起支持部分与表达式索引，EXPLAIN QUERY PLAN | 开源、完全免费 |
| MongoDB | 单字段、复合、多键（数组）、文本、地理与哈希索引，TTL 索引 | 社区版免费；Atlas 免费档 M0，更高档从每月约 60 元起 |
结论：你选哪种数据库并不改变"列、基数和覆盖索引"这些根本，只改变语法和你依赖的辅助工具。
一套可辩护的通用索引配方
如果你面对一个有真实流量但毫无索引的表，按这个顺序能获得每个索引的最大价值：
- 给每个外键列建索引。这是不能妥协的，能立刻修复大多数 JOIN 慢。
- 为最快的 3–5 条热点慢查询加联合索引，匹配每条查询的等值和排序列。
- 把最重要热点查询做成覆盖索引，把 SELECT 的列加进去，跳过表访问。
- 删掉没人用的索引。查目录或优化器建议，移除冗余的单列索引。
- 每次改动后重测读写，用执行计划加墙钟时延而不是凭感觉。
这和你在 数据库设计原则 里用到的成本意识、以测量为先的心态是一致的一整套，配合 数据库设计基础 一起见效。
常见问题
联合索引里哪一列放最前？
放选择性最强的列（过滤到的行里不同值最多的那列），并让等值列排在范围或排序列前面。这样 B 树能最快收窄结果集。用执行计划验证——优化器给你索引扫描的行估算，就知道你做对了没有。
为什么我新建的索引被查询无视了？
三个常见原因：列被包在函数里导致值在运行期被转换、查询命中行占比太大让索引扫描不划算、统计信息太旧。把查询转成 sargable（可索引）形式、看计划、运行 ANALYZE 或更新统计来刷新优化器的估算。
索引是不是总让 SELECT 变快、让写入变慢？
写入大多是的，因为每行变化都要更新每个索引。读取只在索引胜过表扫描时才变快；覆盖索引甚至可以彻底消灭表访问。净效果取决于你的读写比例和查询模式，所以应该测量而不是假设。
索引会不会太多？怎么判断？
会。症状是插入变慢、存储变大、优化器花在选择索引上的时间超过执行。用数据库的"未使用索引"视图（PostgreSQL 的 pg_stat_all_indexes），删掉近零使用、且只是某联合索引冗余前缀的索引。
聚集索引和非聚集索引有什么区别？
聚集索引（InnoDB 主键、SQL Server 的默认表组织）按键物理重排表行，每表只能有一个。非聚集索引是单独的、存回指到行的指针的结构。用哪种会影响查找是否需要一次额外的按 id 回表跳转。