数据库索引的基础理解
一个 200 万行的表，按 author_id 查详情要 8 秒，EXPLAIN 出来是全表扫描。对方说应该有索引吧，指的却是主键。这篇从那次翻车讲起：索引是什么、哪些字段该建、为什么不是越多越好、联合索引的最左前缀。
那次「我以为没索引」的翻车
有次帮人看一个 Python 后端。详情接口有个查询 WHERE author_id = ? AND status = 'published'，表里 200 万条记录，跑了 8 秒。看起来是 page-level 慢查询。
我先问他有没有索引。他说「应该有吧」。拉 EXPLAIN 出来一看，type: ALL，全表扫描。
他所谓的「应该有」是指主键索引。WHERE author_id = ? 时 MySQL 拿主键索引派不上用场，只会一行一行扫。
加完索引之后查询降到 5 毫秒。
我把这件事写下来，是因为我第一次听说索引原理时，也是这种「懂了但不知道怎么用」的状态。索引不是魔法，是 B+ 树组织的数据结构。
索引究竟是什么
我把数据库索引理解为书的目录。书最后五页印着「第三章第二节，『xx 的历史意义』，第 89 页」。从「第 89 页」翻过去就能找到那一段，省掉从第一页翻到第 89 页的力气。
数据库索引也是这个意思。某个字段建了索引，数据库会单独维护一个按这个字段排序的数据结构，查询时先在索引里定位大致位置，再读真实数据。当字段值分布均匀、查询选择性高的时候，这个「目录」能极大缩短查找时间。
哪些字段适合索引
我有一套自己的判断模板（不是死规定）：
高选择性的字段。比如 email、mobile、id_card、slug。值都不重复，索引能在树里快速定位某一行。
经常出现在 WHERE 里的字段。这种字段查询条件出现频率高，建索引的回报大。
JOIN 时用到的外键字段。orders.user_id 对应 users.id，如果 orders.user_id 没索引，每次连表都要扫整张订单表。
ORDER BY 的字段。索引天然按字段排好序，排序时直接读索引几乎不花额外成本。
复合索引的字段顺序。如果是 (a, b)，那么 WHERE a = ? 和 WHERE a = ? AND b = ? 都能命中，但 WHERE b = ? 单独用不上索引。
索引为什么不是越多越好
第一次建索引时我有过痴迷期，看到哪个字段没索引就加。后来学到几个反直觉的事实：
第一，每次 INSERT/UPDATE/DELETE 都要维护索引。插入一行新数据，主键的索引要更新，如果有 5 个二级索引要更新 5 次。所以索引会拖慢写入。
第二，索引占存储空间。一个普通字段加索引，B+ 树节点占的磁盘空间比数据本身还大，有时候几个 GB 跑不掉。
第三，覆盖率差的索引基本没用。比如 users.gender ENUM('male', 'female') 加索引，分布极端（80/20 或 99/1），查询时定位到一半还是得扫很多行，规划器甚至不走索引反而走全表扫。
第四，低频使用字段建了索引就是浪费。某字段十年查不了一次，写入却每次要更新它，这个索引完全是负担。
联合索引和最左前缀
我第一次栽跟头是联合索引顺序写反。我设计了一张 posts(status, created_at) 的索引，期望「按时间筛选已发布」用得上，结果 WHERE status = ? AND created_at < ? 走索引，WHERE created_at < ? 不走。
原因是 B+ 树按 status, created_at 字典序排，先是 status 相等的聚一起，再按 created_at 排序。单独按 created_at 排序完全没法用上这棵树，得另起一棵树。
调整方法是：把经常用 WHERE 单独查的字段放前面。我后来改成 posts(created_at, status)，单独按时间筛选和按状态+时间筛选都能用。这条原则叫做最左前缀。
CREATE INDEX idx_posts_created_status ON posts(created_at, status);EXPLAIN 看查询计划
实际工作里很少凭直觉建索引，我几乎每次都要走 EXPLAIN。它告诉你这条 SQL 是不是真的用上了索引、用的是哪一棵、走的是 range 还是 ref、扫了多少行。
EXPLAIN SELECT id, title FROM posts
WHERE status = 'published' AND created_at < '2026-01-01';type=ref key=idx_posts_created_status rows=1320
Extra=Using where最关键的是 type 列。ALL 是全表扫描，看到这个基本要查问题。ref/range 都是正常走索引。key 列实际命中的索引名，NULL 就是没用到。rows 是扫描行数的估算，越大越要警惕。Extra 里出现 Using filesort 或者 Using temporary 就意味着这条 SQL 在 MySQL 内部另开了工作区处理 order/group，是另一个性能来源。
顺手放一段我常带的对照表，记这几个状态什么时候出现：
const 主键 / 唯一索引等值，最多 1 行
eq_ref JOIN 主键/唯一索引，每个匹配 1 行
ref 非唯一索引等值，可能多行
range 索引上的范围扫描 (>, <, BETWEEN, IN)
index 走索引但扫了整棵树（注意！）
ALL 全表扫描，肯定有问题一个反例：函数计算能废掉索引
我以前想筛选最近 30 天的文章，写：
SELECT * FROM posts
WHERE DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 30 DAY);跑出来 type=ALL，全表扫。因为索引里存的是原始值，用函数算过的值跟它对不上。
修法是改成范围查询：
SELECT * FROM posts
WHERE created_at >= NOW() - INTERVAL 30 DAY;type=range key=idx_posts_created rows=12000这一改规划器直接走索引范围扫。索引对函数无感。
我建索引的实际流程
第一步，线上慢查询告警先抓出来（pg_stat_statements 或慢日志）。第二步，EXPLAIN 跑一遍确认是 ALL。第三步，根据 WHERE/ORDER BY/JOIN 用到的字段建候选索引。第四步，EXPLAIN 再跑一遍确认生效，看 key 字段是否变成了刚建的索引名、rows 是否缩到合理范围。第五步，观察写入指标有没有显著下降（这一步很多人省略，结果索引建了写入 QPS 掉了 20%，生产事故再来排查原因）。
EXPLAIN SELECT id, title FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;把整套原则浓缩成一句话：高频读、低频写、高选择性的字段优先建索引；联合索引按最左前缀规则排序；不要对字段做函数后再比较；写比读多的表别堆太多索引；永远 EXPLAIN 验证，永远从真实查询条件出发而不是凭感觉。