MySQL主键索引需要创建吗？InnoDB聚簇索引与索引优化解析

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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; SHOW INDEX FROM t_user;执行完SHOW INDEX你会看到三行记录PRIMARY、uk_phone以及……没有第三行了。PRIMARY那行的Column_name是idNon_unique是 0Index_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 abcEXPLAIN的Extra列会出现Using index说明这次查询完全在二级索引里完成一次回表都没有。这里有个细节InnoDB 的二级索引叶子节点除了索引列还隐含存了主键值所以SELECT id, nickname FROM t_user WHERE nickname abc同样是覆盖索引不需要显式把id加进索引列。2.3 主键长度会放大所有二级索引这是最容易被低估的一点。既然每个二级索引的叶子节点都要存主键值那么主键有多长所有二级索引就有多长。假设一张表有 5 个二级索引、1000 万行数据主键从BIGINT8 字节换成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 字节的键会被复制到每一个二级索引的叶子节点空间放大效应前面已经算过第三缓存命中率下降随机写入让缓冲池的热点页不断被换出。雪花 IDSnowflake 类方案是折中方案。它是 64 位整型高位是时间戳整体趋势递增比 UUID 温和得多。但它也不是没问题趋势递增不等于严格递增同一毫秒内多个节点生成的 ID 顺序是不确定的仍然会在局部产生页分裂只是幅度小很多。另外雪花方案强依赖机器时钟时钟回拨会导致 ID 重复这个问题必须在生成端处理掉不能指望数据库兜底。一个比较务实的组合是聚簇主键用自增BIGINT业务唯一标识另建UNIQUE KEY。比如订单表id BIGINT AUTO_INCREMENT PRIMARY KEYorder_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) ) ENGINEInnoDB;这里用(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列是ALLkey列是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 indexrows从 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输出十几列新手容易看花眼。实际排查时优先盯这四列列名关注点判断标准type访问类型ALL是全表扫描大表上要警惕index是全索引扫描也不理想range、ref、eq_ref、const逐级更优key实际使用的索引为NULL说明没用上任何索引和你预期的索引名不一致说明优化器选了别的rows预估扫描行数和实际返回行数差距大说明索引区分度不够Extra补充信息Using index是覆盖索引Using filesort说明排序没走索引Using temporary说明用了临时表看一个具体例子。假设有张订单表执行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 索引失效的几种典型写法建了索引不代表用得上。下面这几种写法是线上最常见的“索引白建了”场景写法问题改法WHERE phone 13800138000字符串列传了数字触发隐式转换加引号WHERE phone 13800138000WHERE DATE(created_at) 2024-01-01对索引列用了函数改成范围查询created_at 2024-01-01 AND created_at 2024-01-02WHERE nickname LIKE %abc前置通配符无法走索引改用后缀匹配或引入检索方案WHERE id 1 100索引列参与运算把运算挪到等号右边WHERE a 1 OR b 2OR 两侧列不同可能退化为全表扫描拆成两个查询 UNION或建(a, b)联合索引隐式转换这一条尤其坑。手机号、订单号这类字段如果建表时定义成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看看它有没有被真正用过——两边的信息互相印证判断起来心里就有底了。