1. 先把概念掰开：主键和索引到底是不是一回事

“mysql主键索引需要创建吗”这句话，我在技术群里一年能撞见十几次。问的人多半是这种情况：建表语句里写了PRIMARY KEY (id)，回头又在 ORM 自动生成的 DDL 里看到一行KEY idx_id (id)，心里开始打鼓——主键到底算不算索引？是不是还要再补一个索引才稳妥？这个问题看似基础，但只要往深里走一步，就会牵出 InnoDB 的聚簇索引结构、主键选型、二级索引回表成本、索引冗余判断这一整条链路。写这篇文章，就是想把这根链条从头到尾捋一遍：先给结论，再讲清为什么，最后落到可以照着操作的表结构和验证语句。

先说结论，省得有人看完开头就去改代码：在 MySQL 的 InnoDB 存储引擎里，给表定义了主键，主键索引就已经存在了，不需要你再单独CREATE INDEX一次。索引的名字固定叫PRIMARY，你没法给它改名，也没法像普通索引那样直接DROP INDEX PRIMARY。那些在建表语句里额外写一行KEY idx_id (id)的做法，本质上是在同一列上又建了一棵完全重复的 B+ 树，纯属浪费磁盘和写入性能。

但结论只对一半人有价值。真正容易踩坑的地方在于：主键索引“自带”这件事，很多人理解成“主键能顶替所有索引”，于是在高频查询字段上一个索引都不建，最后慢查询堆成山。所以这篇文章的定位是给两类人看的——一类是刚接触 MySQL、对主键和索引关系还模糊的新手，另一类是写了几年 SQL、但对“什么时候主键够用、什么时候必须补索引”没有形成明确判断标准的老手。下面从最基础的语义开始拆。

1.1 约束是规则，索引是数据结构

很多人把主键和索引混为一谈，根子在于没区分“约束”和“索引”这两个层次。主键（PRIMARY KEY）首先是一条约束，它表达两件事：这一列（或这几列的组合）不能为 NULL，并且在整张表里不能重复。这是业务语义层面的规则，是给数据库引擎看的“合法性检查”。

索引（INDEX）则是数据结构，是 B+ 树，是为了让查询少扫行而额外维护的一份有序副本。它回答的是“怎么找得快”这个问题。两者本来是两条平行线，只不过 InnoDB 在实现主键约束的时候，选择了“建一棵唯一 B+ 树并把名字固定为 PRIMARY”这种方式。于是约束和索引在物理层面合二为一了。

这个区分不是文字游戏。它直接解释了一个现象：为什么你可以给一张表建多个唯一索引，但只能有一个主键。因为唯一索引是“索引 + 唯一性检查”，而主键是“唯一索引 + 非空 + 唯一标识一行的语义地位”。InnoDB 需要挑一个索引来组织整张表的物理数据，这个被选中的索引就是聚簇索引，主键默认扮演这个角色。

顺带说一句容易被忽略的：如果你建表时压根没写主键，InnoDB 也不会让表处于“无索引”状态。它的兜底逻辑是，先找第一个所有列都 NOT NULL 的唯一索引，把它升级成聚簇索引；如果一个都找不到，就内部生成一个 6 字节的隐藏列DB_ROW_ID来当聚簇索引的键。这个隐藏列是全局共享的，高并发插入时会在这个点上产生竞争。所以“不建主键”不是省事，是把一张表的物理组织权交给了一个你控制不了的隐藏计数器。

1.2 为什么有人觉得“主键没建索引”

这个误解通常来自三个地方。第一个是可视化工具的展示方式。某些数据库管理工具在“索引”标签页里只列KEY和UNIQUE KEY，把PRIMARY单独放在“表结构”页签里，看多了就以为主键不在索引列表里。

第二个来源是建表语句的书写习惯。不少人写 DDL 时会写成：

CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (id), KEY idx_id (id), -- 这一行是多余的 KEY idx_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

KEY idx_id (id)这一行，MySQL 是照单全收的，不会报错，也不会提示你重复。它就这么安安静静地多建了一棵 B+ 树，每次 INSERT、UPDATE、DELETE 都要多维护一份。表小的时候察觉不到，等到单表几千万行、写入 QPS 上来了，这部分开销就变成实打实的写入延迟。

第三个来源是 ORM 框架的“保险式”生成策略。有些代码生成器在识别不到主键注释时，会给所有字段统一加索引，包括那个明显是主键的 id 字段。这类 DDL 一旦进了生产库，后面清理起来还得走变更流程，属于典型的“工具替你做了决定，但没告诉你代价”。

提示：判断一张表有没有冗余索引，不要靠肉眼扫 DDL。直接查sys.schema_redundant_indexes或者information_schema.statistics，让数据说话。

1.3 一条 SQL 把这件事验证到底

理论说再多，不如自己跑一遍。下面这段可以在任意测试库执行，MySQL 5.7 和 8.0 都适用：

CREATE DATABASE IF NOT EXISTS demo_idx; USE demo_idx; DROP TABLE IF EXISTS t_user; CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', nickname VARCHAR(64) NOT NULL COMMENT '昵称', phone VARCHAR(20) NOT NULL COMMENT '手机号', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SHOW INDEX FROM t_user;

执行完SHOW INDEX，你会看到三行记录：PRIMARY、uk_phone，以及……没有第三行了。PRIMARY那行的Column_name是id，Non_unique是 0，Index_type是BTREE。这就是最直接的证据：主键索引已经存在，它的名字就叫PRIMARY，你没写过一行创建它的语句，它就在那里。

再补一刀，尝试删掉它：

ALTER TABLE t_user DROP INDEX PRIMARY;

MySQL 会直接甩给你一个错误，大意是“不能以这种方式删除主键，请改用 DROP PRIMARY KEY”。而即使你改用ALTER TABLE t_user DROP PRIMARY KEY;，如果id是自增列，还会再报一个错，要求你先去掉AUTO_INCREMENT属性。这层层阻拦本身就说明：主键索引不是一个普通索引，它是表结构的地基，动它等于重建整张表。

2. InnoDB 的聚簇索引：主键为什么“自带”索引

上一节是从语法和数据字典层面看，这一节往物理存储里钻一层。理解了聚簇索引的布局，你就能自己推导出后面所有关于主键选型、索引长度、回表成本的结论，而不用死记硬背别人的经验值。

2.1 B+ 树的叶子节点存的是整行数据

InnoDB 的表本质上是一棵以主键为键的 B+ 树，这棵树的叶子节点直接存放行的完整数据（除了 TEXT/BLOB 这类溢出列会另存到溢出页）。行与行之间按主键值从小到大排序，页与页之间用双向链表串起来。所以你执行SELECT * FROM t_user WHERE id = 100时，引擎做的事情是从根节点开始，逐层比较主键值往下走，最后在叶子节点定位到那一行，整行数据一次性拿到，不需要再跳转。

这就是“聚簇”两个字的含义：数据跟着索引走，索引和数据是同一份结构。这和 MyISAM 完全不一样，MyISAM 的索引文件和数据文件是分开的，索引叶子节点存的是行号，拿到行号还要再去数据文件里定位一次。所以同样是主键查询，InnoDB 通常比 MyISAM 少一次寻址。

理解了这一点，“主键索引需不需要创建”这个问题就变得有点好笑了：如果主键索引不存在，整张表的物理数据就没地方放。它不是“要不要建”的可选项，而是表存在的形式本身。

顺带算一笔账，让你对 B+ 树的容量有个直观感受。InnoDB 默认页大小 16KB，假设主键是BIGINT，占 8 字节，页内指针占 6 字节，那么一个非叶子节点页能容纳大约16384 / (8 + 6) ≈ 1170个子节点指针。假设每行数据平均 1KB，一个叶子页能放 16 行。那么一棵三层 B+ 树大概能存1170 × 1170 × 16 ≈ 2190 万行。也就是说，两千万行以内的表，主键等值查询只需要三次页访问就能定位，这也是为什么主键查询的耗时几乎不随数据量增长。

2.2 二级索引为什么要“回表”

真正体现主键价值的，是二级索引。你建在nickname上的那个普通索引，是一棵独立的 B+ 树，它的叶子节点存的不是行数据，而是主键值。为什么这么设计？因为如果在每个二级索引里都存一份完整行数据，一张表有五个索引就存五份数据，空间和一致性维护成本都受不了。存主键值是成本最低的方案：主键能唯一定位一行，长度也相对可控。

代价就是回表。执行SELECT * FROM t_user WHERE nickname = 'abc'时，引擎先走idx_nickname找到匹配的叶子节点，拿到主键id，再拿这个id回到聚簇索引里查一次，才能取到phone、created_at这些没在索引里的列。这个“从二级索引跳回主键索引”的动作，就是回表。

回表的次数取决于匹配行数。如果nickname = 'abc'命中 1 行，那就回表 1 次；如果命中 1 万行，就回表 1 万次，每次都是一次随机 I/O。这就是为什么在区分度低的字段上建索引，效果可能还不如全表扫描——优化器算了一下，回表 1 万次的成本超过了顺序扫一遍。

想避免回表，就得让二级索引“自带”查询需要的所有列，也就是覆盖索引。比如把索引改成KEY idx_nick_phone (nickname, phone)，再查SELECT nickname, phone FROM t_user WHERE nickname = 'abc'，EXPLAIN的Extra列会出现Using index，说明这次查询完全在二级索引里完成，一次回表都没有。这里有个细节：InnoDB 的二级索引叶子节点除了索引列，还隐含存了主键值，所以SELECT id, nickname FROM t_user WHERE nickname = 'abc'同样是覆盖索引，不需要显式把id加进索引列。

2.3 主键长度会放大所有二级索引

这是最容易被低估的一点。既然每个二级索引的叶子节点都要存主键值，那么主键有多长，所有二级索引就有多长。假设一张表有 5 个二级索引、1000 万行数据，主键从BIGINT（8 字节）换成CHAR(32)（utf8mb4 下最多 128 字节），多出来的空间不是 1000 万乘以 120 字节那么简单，而是要在 5 个索引上各乘一遍，还要算上页分裂带来的填充率损失。

具体点说，一条二级索引记录大致是“索引列值 + 主键值 + 页内指针”。假设索引列 8 字节、指针 6 字节，主键 8 字节时单条记录约 22 字节，一页能放16384 / 22 ≈ 744条；主键变成 32 字节时单条记录约 46 字节，一页只能放16384 / 46 ≈ 356条。同样的数据量，索引树的高度可能从 3 层涨到 4 层，查询多一次页访问，缓存命中率也跟着下降。

所以主键设计的第一原则不是“好不好看”，而是短且有序。BIGINT UNSIGNED自增是绝大多数业务表的合理选择，32 位整型在数据量可能破 20 亿行的场景下不够用，直接上 64 位；字符串主键（订单号、身份证号、UUID）尽量只做业务唯一键，用唯一索引约束，别拿去当聚簇索引的键。

注意：如果业务上确实需要用字符串做主键（比如某些分布式场景下的有序 ID），务必控制长度。VARCHAR(32)和VARCHAR(64)在千万级表上的差距，可能就是你半夜被报警叫起来的原因。

3. 主键设计的关键取舍：不是随便加个 id 就行

到这里，“主键自带索引”这件事应该没有疑问了。但紧接着的问题是：既然主键这么重要，那主键该怎么选？这一节聊的都是选型层面的判断，每一条背后都有具体的性能账可以算。

3.1 自增整型的优势与它的代价

自增BIGINT主键最大的好处是顺序写入。新插入的行主键值总是比之前的大，B+ 树的叶子节点永远在右侧追加，页分裂只发生在最右边那一个页上，其他页保持紧凑。这对写入密集的业务非常友好，插入性能稳定，索引碎片少。

它在高并发下还有一个隐藏优势：页内记录是按主键顺序排列的，新行总在末尾，缓冲池里热点页集中，不需要频繁把中间的页读进内存再刷回去。

但自增主键不是没有代价。第一个代价是分库分表时的合并困难。单库单表时自增很香，一旦要拆成多个物理分片，每个分片各自从 1 开始自增，主键就冲突了。这时候要么改成步长自增（auto_increment_increment配合auto_increment_offset），要么换成分布式 ID 生成方案。前者在扩容缩容时调整起来很别扭，后者又引入了新的组件依赖。

第二个代价是主键值的可枚举性。连续自增的 ID 暴露在 URL 或接口参数里，等于把数据量告诉了外面的人，也方便了遍历抓取。这不是数据库层面的性能问题，但确实是选型时要考虑的业务因素。常见做法是主键自增、对外另发一个随机业务编号，两者分工明确。

第三个代价跟自增锁有关。MySQL 8.0 之前，innodb_autoinc_lock_mode默认是 1（连续模式），批量插入时会对自增分配加表级锁，高并发批量插入场景下会有等待。8.0 之后默认改成 2（交错模式），锁的问题基本解决，但同一语句内分配到的自增 ID 可能不连续。如果你的业务依赖“连续无空洞”的 ID，这一点要提前确认。

3.2 UUID 和雪花 ID 到底坑在哪

用 UUID 做主键的坑，几乎是面试题级别的经典。UUID v4 是完全随机的 128 位值，转成字符串是 36 个字符。把它当聚簇索引的键，后果有三个：第一，插入位置随机，新行可能落在 B+ 树的任意位置，频繁触发页分裂，页填充率从 90% 以上掉到 50% 左右，索引体积凭空膨胀近一倍；第二，主键太长，36 字节的键会被复制到每一个二级索引的叶子节点，空间放大效应前面已经算过；第三，缓存命中率下降，随机写入让缓冲池的热点页不断被换出。

雪花 ID（Snowflake 类方案）是折中方案。它是 64 位整型，高位是时间戳，整体趋势递增，比 UUID 温和得多。但它也不是没问题：趋势递增不等于严格递增，同一毫秒内多个节点生成的 ID 顺序是不确定的，仍然会在局部产生页分裂，只是幅度小很多。另外，雪花方案强依赖机器时钟，时钟回拨会导致 ID 重复，这个问题必须在生成端处理掉，不能指望数据库兜底。

一个比较务实的组合是：聚簇主键用自增BIGINT，业务唯一标识另建UNIQUE KEY。比如订单表，id BIGINT AUTO_INCREMENT PRIMARY KEY，order_no VARCHAR(32) UNIQUE KEY。外部交互全部用order_no，内部关联和分页全部用id。这样既拿到了顺序写入的性能，又满足了业务可读性和唯一性要求，两者的职责不混。

3.3 业务字段直接当主键的风险

把身份证号、手机号、订单号这些业务字段直接设成主键，看起来很“自然”，实际上问题不少。首先是长度，身份证号 18 位、订单号动辄 20 多位，作为聚簇索引的键，会把所有二级索引一起撑大。其次是可变性，业务规则一变，主键值可能要改，而主键修改意味着整行数据在 B+ 树里搬家，所有二级索引的叶子节点也要跟着更新，代价极高。

还有一类更隐蔽的风险：业务字段的唯一性假设可能不成立。某些场景下手机号会回收重用，某些场景下同一个业务编号在历史数据里确实出现过重复（迁移数据时尤其常见）。一旦唯一性被打破，主键约束会直接把写入打回，而这时候表可能已经跑了几千万行，改结构的心智成本很高。

我的建议一直很明确：代理主键（无业务含义的自增 ID）+ 业务字段建唯一索引，这个组合在 95% 的业务表上都适用。剩下 5% 是特殊场景，比如中间关联表可以用联合主键，日志类表可以用时间戳加序列的组合，这些后面单独说。

3.4 联合主键什么时候该用

联合主键（复合主键）不是不能用，而是要用对地方。最典型的场景是多对多的关联表。比如用户和角色的关系表：

CREATE TABLE t_user_role ( user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_id), KEY idx_role (role_id) ) ENGINE=InnoDB;

这里用(user_id, role_id)做联合主键，天然保证了同一个用户不会重复绑定同一个角色，省掉了一个额外的唯一索引。同时，因为聚簇索引的键是(user_id, role_id)，按user_id查角色列表时可以走聚簇索引，效率很高。而按role_id反查用户列表时，因为不满足最左前缀，走不了聚簇索引，所以额外建了idx_role。

联合主键的第二个场景是分库分表的组合分片键。某些方案要求主键里必须包含分片键，以便路由时能直接定位。这时候联合主键不只是索引设计，还承担了路由元数据的角色。

但联合主键有两个必须警惕的点。第一，顺序很关键。PRIMARY KEY (a, b)和PRIMARY KEY (b, a)是完全不同的两棵树，前者能支持按a查、按a + b查，不支持单独按b查。第二，联合主键会被完整复制到每个二级索引里。如果联合主键是三个BIGINT，那就是 24 字节，同样会放大所有二级索引。所以联合主键的列数要克制，能两列解决就别上三列。

4. 什么时候真的需要“额外建索引”

回到最初那个问题。主键索引不需要创建，这个结论成立。但“不需要创建主键索引”和“不需要创建任何索引”是两回事，很多人恰恰在这第二步上栽跟头。这一节讲的是判断标准：什么样的查询需要补索引，补什么样的索引。

4.1 主键索引只对主键列有效

这是最朴素的道理，也是最常被忽略的。主键索引的键是主键列，它只能加速“按主键列过滤或排序”的查询。你写WHERE nickname = 'abc'，主键索引帮不上任何忙，优化器只能选择全表扫描（或者走你建的其他索引）。

判断一条 SQL 需不需要补索引，我一般看三个信号：

第一，这条 SQL 在慢查询日志里反复出现，且Rows_examined远大于Rows_sent。扫描 10 万行只返回 10 行，说明过滤条件没有索引支撑。

第二，EXPLAIN的type列是ALL，key列是NULL。ALL就是全表扫描，大表上基本等于性能事故。

第三，这条 SQL 属于核心链路且调用频率高。有些报表 SQL 扫全表也就跑几分钟，一天跑一次，那没必要为它建索引；但如果是首页接口里每秒钟几千次的查询，扫一次全表都不可接受。

补索引的时候要注意最左前缀。建了KEY idx_ab (a, b)，能支持WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? ORDER BY b，但支持不了WHERE b = ?。所以联合索引的列顺序要按“等值条件在前、范围条件在后、排序列跟上”的思路排，同时兼顾区分度高的列优先。

4.2 覆盖索引是性价比最高的一类索引

如果一条查询只需要索引里已有的列，就构成覆盖索引，完全不用回表。这类索引的收益往往比“多建一个过滤索引”更大，因为它同时省掉了随机 I/O。

举个实际例子。某报表接口要查“某个用户最近 20 条订单的金额”，SQL 是：

SELECT id, amount, created_at FROM t_order WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 20;

表上只有KEY idx_user (user_id)，执行计划会先用idx_user定位到该用户的所有订单（假设 5000 条），拿到 5000 个主键，逐个回表取amount和created_at，再在内存里排序取前 20 条。回表 5000 次，大部分是白干的。

把索引改成KEY idx_user_time_amount (user_id, created_at, amount)，情况立刻不一样：user_id等值定位后，created_at在索引里已经有序，直接从末尾倒着取 20 条，amount也在索引里，全程零回表。EXPLAIN的Extra会显示Using index，rows从 5000 降到 20。

这里有个细节要提醒：覆盖索引的列顺序不是随便排的。(user_id, created_at, amount)里，user_id用于等值过滤，created_at用于排序，amount只是“搭车”带上，不参与过滤和排序，所以放最后。如果写成(user_id, amount, created_at)，排序就用不上索引了，Extra里会出现Using filesort。

4.3 唯一索引和主键的边界在哪

既然主键本身就是唯一索引，那什么时候该用唯一索引而不是主键？答案很直接：一张表只能有一个主键，但可以有多个唯一索引。所以凡是“需要唯一性约束、但不是这张表的物理主键”的字段，就该建唯一索引。

举个典型场景：软删除。很多业务表用is_deleted标记删除，这时候“手机号唯一”这个约束就不能简单地建在phone上，因为删除后的记录还占着这个手机号。常见做法是把唯一索引建成(phone, is_deleted)的组合，或者用一个“删除时间戳”字段参与唯一键，让已删除记录的唯一性判断带上时间维度。

另一个边界是NULL 的处理。主键列不允许 NULL，唯一索引列在 MySQL 里允许多个 NULL（因为 NULL 不等于 NULL，唯一性检查对 NULL 是放行的）。如果你的业务字段可能为空，但又要保证“非空时唯一”，唯一索引比主键更合适。这个差异在数据迁移和清洗时经常引发意外，值得单独记一笔。

4.4 冗余索引怎么判断和清理

索引不是越多越好。每多一个索引，写入时就多维护一棵 B+ 树，INSERT从“写一行”变成“写一行 + 更新 N 棵索引树”，高并发写入场景下这个放大效应非常明显。经验值是一张表的索引数量控制在 5 个以内，超过就要审视一下是不是有冗余。

冗余的典型形态有两种。第一种是前缀重复：已经有了KEY idx_ab (a, b)，再建一个KEY idx_a (a)就是完全冗余的，因为(a, b)天然能支持只按a的查询。第二种是完全重复：同一组列、同样顺序建了两次，包括我们开头说的PRIMARY KEY (id)加KEY idx_id (id)。

判断是否冗余，别靠人眼，直接查系统表：

SELECT table_schema, table_name, redundant_index_name, redundant_index_columns, dominant_index_name, dominant_index_columns FROM sys.schema_redundant_indexes WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema');

schema_redundant_indexes是 MySQL 5.7 之后自带的视图，它会告诉你哪个索引被哪个索引覆盖了。清理之前记得先在预发环境验证，把索引删掉跑一轮回归，确认没有查询因此变慢。有些看起来冗余的索引，实际上承担了覆盖索引的角色——(a)虽然被(a, b)覆盖，但如果查询只用到a且数据量大，单独的小索引在缓冲池里更省空间，删掉未必是好事。这种判断只能靠实际压测，不能靠规则。

5. 实操验证与常见问题排查实录

前面几节讲的是原理和判断标准，这一节全是手上的活。我把平时排查索引问题的一套流程整理出来，基本可以照着走。

5.1 用 EXPLAIN 读懂执行计划的关键列

EXPLAIN输出十几列，新手容易看花眼。实际排查时优先盯这四列：

看一个具体例子。假设有张订单表，执行：

EXPLAIN SELECT id, amount FROM t_order WHERE user_id = 10086 AND status = 2;

如果输出是type: ref、key: idx_user、rows: 5000、Extra: Using where，说明只用了user_id的单列索引，status是在回表之后过滤的，5000 次回表只为了筛出其中一小部分。这时候把索引改成KEY idx_user_status (user_id, status)，rows会明显下降，Extra里的Using where也可能消失。

MySQL 8.0 还有个EXPLAIN ANALYZE，会真正执行 SQL 并给出实际耗时和实际行数，比传统EXPLAIN的预估值可靠得多。排查复杂查询时，我一般先用EXPLAIN看方向，再用EXPLAIN ANALYZE确认细节。

5.2 索引失效的几种典型写法

建了索引不代表用得上。下面这几种写法，是线上最常见的“索引白建了”场景：

隐式转换这一条尤其坑。手机号、订单号这类字段，如果建表时定义成VARCHAR，查询时忘了加引号，MySQL 会把字符串列转成数字再比较，索引直接失效。更麻烦的是这种查询在测试环境数据量小的时候完全看不出问题，上线后才爆。我的习惯是：所有字符串类型字段的查询条件一律带引号，哪怕是纯数字内容。

5.3 踩过的几个坑

第一个坑跟索引列顺序和 ORDER BY 的关系有关。有次给一张表建了(status, created_at)的联合索引，查询是WHERE status = 1 ORDER BY created_at DESC，想着应该能用上索引排序。结果Extra里一直是Using filesort。后来发现是索引建成了(created_at, status)，顺序反了，created_at在前面导致status的等值过滤用不上最左前缀。调换顺序后filesort消失。

第二个坑是统计信息不准导致优化器选错索引。某张表数据量变化很大，information_schema.statistics里的基数估计严重滞后，优化器误判了某个索引的区分度，选了一个回表代价很高的执行计划。执行一次ANALYZE TABLE t_order;更新统计信息后恢复正常。这类问题在数据批量导入或大批量删除之后特别容易出现，建议把ANALYZE TABLE加进数据变更流程里。

第三个坑是索引长度超限。给一个VARCHAR(255)的 utf8mb4 字段建索引，单列索引长度是 255 × 4 = 1020 字节，超过 InnoDB 单列索引 767 字节的限制（老版本行格式）。现代版本用DYNAMIC行格式能支持到 3072 字节，但索引太长本身就不合理。正确做法是用前缀索引KEY idx_name (nickname(20))，只索引前 20 个字符，兼顾区分度和空间。前缀长度的选择方法是看SELECT COUNT(DISTINCT LEFT(nickname, 20)) / COUNT(*)的值，接近全列区分度就够用了。

6. 索引维护与上线前的检查清单

索引设计不是一次性工作，表在长、查询在变，索引也得跟着调。这一节讲的是长期维护层面的经验，以及上线前我会过一遍的检查项。

6.1 索引的隐性成本与容量规划

索引带来的读性能提升是显性的，写入成本和空间成本是隐性的，容易被忽略。一张表加一个索引，INSERT的耗时可能增加 10% 到 30%，具体取决于索引数量和索引列的长度。批量导入场景下这个差距会被放大，我见过一个批量导入任务因为表上多了三个几乎不用的索引，耗时从 8 分钟涨到 20 分钟。

空间方面，索引占用的磁盘通常和表数据相当，甚至更多。索引多的表，数据文件 10GB，索引文件加起来 15GB 很常见。做容量规划时，索引空间要单独估算，别只按数据量乘以一个系数拍脑袋。

还有一个容易被忽略的点：索引会占用缓冲池。缓冲池是有限的，索引页和数据页在抢同一块内存。索引建得太多，热数据页被挤出去，命中率下降，整体性能反而变差。所以索引优化的目标从来不是“让每个查询都走索引”，而是“让核心查询走对索引，其余查询接受合理的扫描成本”。

6.2 上线前的索引检查清单

新表或者索引变更上线前，我一般会过一遍下面这几项：

• 主键是否显式定义，且类型为定长整型（BIGINT或INT），带UNSIGNED；
• 是否存在与主键重复的索引（如KEY idx_id (id)），有则删除；
• 是否存在前缀互相覆盖的冗余索引，用sys.schema_redundant_indexes核对；
• 每个联合索引的列顺序是否匹配实际查询的最左前缀、等值条件、排序需求；
• 核心查询是否被覆盖索引覆盖，EXPLAIN的Extra是否出现Using index；
• 字符串列的索引是否用了前缀长度，前缀长度是否经过区分度验证；
• 单表索引总数是否控制在 5 个左右，超出部分是否有明确的查询支撑；
• 索引变更是否在预发环境跑过一轮回归和压测，慢查询日志有没有新增条目。

这套清单不长，但每一条都对应过真实的线上问题。尤其是前两条，看起来最基础，实际上是最容易在多人协作的项目里被破坏的——今天有人加一个索引，明天有人改个字段类型，半年后回头看 DDL，已经跟当初完全不是一回事了。定期做一次索引体检，比事后救火划算得多。

最后再补一个小技巧。执行SHOW INDEX FROM 表名的时候，重点关注Cardinality这一列，它表示索引列的不重复值数量估计。如果某个索引的Cardinality长期接近表的行数，说明区分度好；如果它远小于行数，说明这个索引的过滤能力有限。Cardinality是采样估算的，不是精确值，但用来做横向比较已经够用。我排查“某个索引到底该不该留”时，第一步就是看这一列，再去sys.schema_unused_indexes看看它有没有被真正用过——两边的信息互相印证，判断起来心里就有底了。