聚簇索引怎么用常见问题排查手册

聚簇索引常见问题排查手册(MySQL InnoDB)

前置：InnoDB 只有聚簇索引，聚簇索引就是主键索引，数据行本身就存在主键索引的叶子节点，索引和数据绑定在一起;二级索引叶子节点存的是主键值，不是行地址。适用：后端面试、数据库故障排查、慢 SQL 优化，只讲实战现象、原因、排查步骤、修复方案。

一、先记基础要点(排查的前提)

一张表只能有一个聚簇索引，一般就是主键 PRIMARY。

如果不定义主键，InnoDB 会选第一个非空唯一索引当聚簇索引;没有非空唯一索引，自动生成隐藏 rowid 作为聚簇索引。

二级索引查询：先走二级索引拿到主键，再回表，去聚簇索引拿完整行数据，这个动作叫回表。

聚簇索引天然有序，主键有序，磁盘上数据物理顺序跟着主键走。

二、问题 1：没建主键，自动 rowid 聚簇索引，性能差

现象

建表没写主键，业务正常跑，但是插入、更新、查询越来越慢;大表导入数据速度明显偏低，迁移导出耗时久。

原因

隐藏 rowid 对业务不可见，无法控制顺序;无法利用有序插入;无法避免不必要回表;备份、数据同步、分表逻辑不好控制，rowid 不能外部引用。

排查方式

show create table 表名;

看有没有 PRIMARY KEY;没有主键、没有非空唯一键，就是用了 rowid。

处理方案

给表增加自增主键，优先用单调递增整数 id，不要用随机字符串做主键。

注意：大表加主键会锁表，业务低峰执行。

三、问题 2：主键无序(UUID、随机 ID)，聚簇索引页分裂严重

现象

插入新数据的时候频繁慢，CPU、IO 上涨;插入性能抖动;碎片很高;ibd 文件膨胀很快，实际数据不大，文件体积很大。

原因

聚簇索引数据按主键排序存放。UUID、雪花随机 ID 是乱序，新记录不一定追加到索引末尾，需要在已有数据页中间插入，触发页分裂、页迁移，产生大量碎片。

排查方式

观察 insert 语句慢查询日志;

查看 information_schema.innodb_buffer_stats_by_table，页面拆分指标;

查看表碎片：show table status like '表名'G，看 Data_free。

处理方案

优先单调自增 int/bigint 做主键;

如果必须用 UUID，改用 UUIDv1 或者有序 UUID;

碎片严重时，低峰期 alter table xxx engine=InnoDB 重建表整理碎片。

四、问题 3：主键太长，二级索引体积暴涨

现象

表数据量不大，但是磁盘占用很高;二级索引查询变慢，缓冲池命中率变差。

原因

二级索引叶子节点保存的是主键。主键越长，每一条二级索引记录占用空间越大，索引整体膨胀，缓存能放下的索引行数变少，更容易刷磁盘。比如用 char (32) UUID 做主键，每一个二级索引都多带 32 字节。

排查方式

show create table，看主键字段长度;对比数据量和索引大小。

处理方案

改用 bigint 自增主键;长唯一业务字段放到普通唯一二级索引，不作为聚簇索引。

五、问题 4：查询命中二级索引，但是大量回表，SQL 很慢

现象

explain 看到 type 是 ref/range，索引也命中了，但是 SQL 执行耗时久。

原因

查询需要的字段不在二级索引里面，拿到主键之后，要回聚簇索引读取完整行，大量随机 IO。

排查方式

explain 看 Extra，没有 Using index，说明没有覆盖索引，发生回表。

处理方案

建立覆盖索引，把查询需要的字段加到联合二级索引里，避免回表访问聚簇索引。

六、问题 5：联合主键作为聚簇索引，顺序不合理，查询失效

现象

联合主键 (a,b)，where b=xxx 查不到索引，变成全表扫描。

原因

聚簇索引也是 B + 树，遵守最左前缀原则。联合主键本身就是聚簇索引，顺序写反就无法利用。

排查方式

explain 观察 where 单独使用后面字段，type 变成 ALL。

处理方案

调整联合主键顺序，区分高频查询前缀;或者不要把高频筛选后置字段放进联合主键。

七、问题 6：主键更新，代价极高，业务随便 update 主键

现象

更新主键字段的时候 SQL 巨慢，锁等待、事务阻塞，容易引发死锁。

原因

聚簇索引就是数据本身，修改主键等于修改这条记录在 B + 树上的位置，需要删除旧索引节点、插入新节点;所有二级索引存的主键值全部要同步更新。一张表二级索引越多，更新主键代价呈倍数放大。

排查方式

慢查询里面大量 update 主键语句;innodb 锁等待日志看到主键行锁冲突。

处理方案

业务禁止更新主键。主键一旦写入，终身不变。业务唯一标识分开，主键只做内部定位。

八、问题 7：选择了业务唯一字段当主键，热点写入集中(热点行)

现象

按时间、按用户 id 做主键，大量请求挤在同一个主键区间，热点页、行锁冲突严重，并发上不去。

原因

聚簇索引有序，相同范围主键落在同一个数据页，并发写入争抢同一个页，锁竞争激烈。

排查方式

show engine innodb status，看事务等待集中在同一行或者相邻主键;监控行锁等待时间飙升。

处理方案

打散热点，或者调整分片策略;不要把高频并发更新的业务维度直接作为主键。