MySQL 索引与执行计划

MySQL 索引与执行计划

聚簇索引（主键索引）是什么

聚簇索引（clustered index）不是一种“特殊的索引类型”，而是“数据本身按某个索引键值的顺序存储”的一种存储方式。
在 InnoDB 里，这个“按哪一列排好序、把整行数据放在叶子节点”的索引，就叫聚簇索引；
如果表有主键，主键索引通常就是聚簇索引。可以简单理解为：

聚簇索引 = “按某个键排好序的 B+ 树，叶子节点直接放的是整行数据”。
其他（二级/非聚簇）索引 = “另一棵 B+ 树，叶子放的是索引列 + 主键”。

如果你没定义主键，但有一个“所有列都 NOT NULL 的 UNIQUE 索引”：InnoDB 会用第一个这样的唯一索引作为聚簇索引。

如果既没有主键，也没有合适的唯一索引：InnoDB 会内部生成一个隐藏列（row_id），并在其上建立一个隐藏的聚簇索引 GEN_CLUST_INDEX。

“聚簇索引”和“主键索引”是什么关系？

严格讲：
- 主键（primary key）是一个约束，唯一+非空；
- 聚簇索引是一种索引/存储方式。

叶子节点存的是整行数据

非聚簇索引（二级索引）叶子节点存的是索引值+主键值，

InnoDB 必须有聚簇索引，MyISAM 没有

回表查询是什么、为什么会慢

通过主键查：直接走聚簇索引，到叶子就能拿到整行数据。

通过其他索引查：先在二级索引树找到 (username, id)；再拿 id 去聚簇索引查完整行（回表）。

回表就是拿着主键再去聚簇索引里面把完整行数据取出来，相当于多查询了一次B+树，很可能会引发大量的随机IO，

什么情况下会发生回表？

查询走了二级索引，并且select的字段不在索引覆盖范围内，就会导致回表。

索引下推（ICP）：MySQL 把 WHERE 里能用“索引列”直接判断的那部分条件，下推到存储引擎层，在遍历索引时就过滤；只有满足这部分条件的记录，才去“回表”读整行，从而减少回表次数和 I/O。

联合索引结构简单理解

联合索引（也叫复合索引）本质上是“一棵 B+ 树，但它的排序键是多列组合成一个元组（a,b,c,…）”。

叶子节点：按 (col1, col2, …, colN) 排好序的键值（在 InnoDB 里还带着主键值）。
非叶子节点：同样按多列组合排序，用来做“范围/等值查找”的导航。
正因为这棵树是“先按第 1 列排序，第 1 列相同时再按第 2 列排序……”，才有了最左前缀原则：只有从最左列开始连续的条件，才能直接用上这棵树的有序性来加速查找。

为什么建议用自增主键

InnoDB 的表就是按主键排好序存的（聚簇索引），自增能让插入几乎都落在“最后一个页”，减少页分裂，插入更快更稳定。
每个二级索引都会在叶子节点存一份主键；主键越短（整型比 UUID 小），所有索引越省空间，缓存越好，回表也越快。
纯数字的整型做主键，在索引中的比较速度远比字符串（比如 36 字节的 UUID）快。

自增 BIGINT UNSIGNED：8 字节；INT：4 字节。

UUID 用字符串存（char(36)）大约 36 字节，用 BINARY(16) 也至少 16 字节，而且往往还是“字符串比较”。

主键越短 → 二级索引越小 → 内存能缓存的记录越多 → 回表越快。

什么是Filesort，什么情况下会出现

在 MySQL 中，filesort 只是一个内部排序算法的名字（名字起得很烂，极具误导性）。只要 MySQL 发现结果集需要排序，但没有合适的索引能直接提供这个顺序时，就会触发 filesort 操作。总结：需要排序，但索引帮不上忙，就会 filesort。如果查询结果集大小没超过sort_buffer的大小，就会在内存中进行排序，否则就在磁盘排序。use index > use filesort (内存>>>磁盘)

只要出现以下关键字，MySQL 就需要返回一个“有序的结果集”：

ORDER BY：最常见，显式要求排序。
GROUP BY：分组计算前，必须先按分组字段排序（把相同的值聚在一起）。
DISTINCT：去重，底层通常也是先排序再去除相邻重复行。
某些聚合函数或连接（JOIN）条件：为了优化执行，内部可能需要先排序。

如何优化filesort？

1、建立联合索引，

2、减小select的数据量，只select必要的字段

3、加大排序缓冲区sort_buffer的大小

4、利用LIMIT减少排序负担

posted @ 2026-04-14 13:59 xzlrf 阅读(15) 评论(0) 收藏举报

刷新页面返回顶部

xzlrf

MySQL 索引与执行计划