【MySQL深入】一、索引

夜白宋

331人浏览 · 2026-05-18 10:18:12

夜白宋 · 2026-05-18 10:18:12 发布

1、索引

mysql 【InnoDB引擎】每一个索引其实就是一颗B+树
mysql主键索引其实就是聚簇索引，唯一索引和普通索引就是非聚簇索引
B+树是多路平衡搜索树（每个节点可有多个子节点），最左前缀匹配原则（Leftmost Prefix Rule）是联合索引的使用规则，源于 B+ 树对复合键的排序方式（先按第一列排，再第二列……）
B+树一般来说3层以内最佳，因为每一层都会进行一次磁盘IO，所以层数越高，速度就会越慢。
所以根据B+树的特性，联合索引只需要查询一次非聚簇索引树，再进行一次回表查询，而单索引针对于联合字段查询需要查询多次非聚簇索引（Index Merge），或者只查单次，在进行筛选，所以性能不如联合索引

1.1 聚簇索引和非聚簇索引详解

节点类型	存储内容
非叶子节点	键值 + 指向下层页的页号（Page Pointer）
叶子节点（聚簇索引）	主键值 + 完整行数据
叶子节点（非聚簇索引）	索引列值 + 对应的主键值

1.2 计算最优存储数量

MySQL的页大小是16KB
非叶子节点存储键值+下一层页号，算16B，则非叶子节点可以存储1000条数据
叶子节点存储完整数据，算作1KB，则可以存储大约16行数据
那么根据层数，把根节点算一层，则1000100016=16000000数据
所以对于每行数据的大小+作为索引的字段类型会影响B+树的层高，从而导致查询速度变慢

1.3 页分裂

MySQL的索引B+树是按照作为索引值大小比对排序的，如果插入的数据值在某一页中，而恰好那一页已经满了，就只能进行页分裂，那么就会涉及到部分数据被移入到新页中，还要更新父节点的指针内容，如果父节点也满了，又会涉及到页分裂
所以自增或者有顺序的主键id，相对于无顺序的UUID来说，页分裂的次数会大大减少，性能会更好

1.4 索引下推

ICP（Index Condition Pushdown）把 WHERE 子句中“能用索引列判断”的过滤条件，提前下推到存储引擎层（如 InnoDB）执行，而不是等数据回表后再由 Server 层过滤，过滤条件所涉及的列必须在“当前使用的索引”中，所以多个字段单索引，多条件情况下也不会存在ICP。
联合索引在某个字段范围查询时，比如A,B,C三个字段的联合索引，查询条件A = 1 ADN B > 2 AND C = 3，C = 3的索引就完全失效，因为必须在B > 2的条件下，逐条比对C=3，这时候就用到ICP，减少从Innodb到server层的数据传输

1.5 覆盖索引

覆盖索引（Covering Index）是指：查询所需的所有列，都包含在所使用的索引中，因此无需回表（即无需访问聚簇索引或主键数据行）。

1.6 Explain分析索引指标

字段	含义	重点关注
`type`	访问类型（怎么找数据）	越靠左越好： `system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL`（全表扫描！）
`key`	实际使用的索引	`NULL` = 没用索引
`key_len`	使用的索引长度（字节）	值越小，说明联合索引没用全
`rows`	预估扫描行数	越大越危险（尤其接近总行数）
`Extra`	额外信息	出现 `Using filesort` / `Using temporary` 是性能杀手

Extra的信息指标

值	含义	说明
`Using index`	覆盖索引（Covering Index）	查询所需的所有列都包含在索引中，无需回表查询数据行。这是非常高效的。
`Using index condition`	索引条件下推（ICP, Index Condition Pushdown）	MySQL 将部分 WHERE 条件下推到存储引擎层，在索引扫描时就进行过滤，减少回表次数。
`Using where`	使用了 WHERE 子句过滤	表示从存储引擎返回的行需要在服务器层进一步用 WHERE 条件过滤。如果同时有 `Using index`，通常表示高效。
`Using join buffer (Block Nested Loop)`	使用了连接缓冲区	在 JOIN 操作中，MySQL 使用内存缓冲驱动表的部分数据来加速被驱动表的查找。

值	含义	优化建议
`Using filesort`	文件排序	MySQL 无法利用索引完成 `ORDER BY`，必须在内存或磁盘上进行额外排序。应优先考虑建立 `(WHERE列, ORDER BY列)` 联合索引。
`Using temporary`	使用临时表	MySQL 需要创建临时表来处理查询，常见于 `GROUP BY` 和 `ORDER BY` 列不一致、或复杂 `DISTINCT` 查询。尽量避免。
`Impossible WHERE`	不可能的 WHERE 条件	WHERE 子句逻辑矛盾（如 `WHERE 1=2`），MySQL 直接返回空结果。检查 SQL 逻辑。
`No tables used`	未使用任何表	查询不包含 `FROM` 子句（如 `SELECT 1`）。正常现象。

值	含义
`Distinct`	MySQL 在找到第一个匹配行后就停止搜索，用于优化 `SELECT DISTINCT`。
`FirstMatch(tbl_name)`	在半连接优化中，一旦为外层行找到匹配的内层行，就不再继续搜索。
`LooseScan(m..n)`	半连接 LooseScan 策略，用于 `IN` 子查询优化。
`Start temporary`, `End temporary`	与物化临时表相关的内部操作标记。
`Unique row not found`	对于类似 `UPDATE ... WHERE primary_key = X` 的语句，但主键 X 不存在。
`Zero limit`	`LIMIT 0`，表示不返回任何行。
`Using MRR`	使用了 Multi-Range Read 优化，将随机 I/O 转为顺序 I/O。
`Select tables optimized away`	优化器发现可以仅通过索引计算出结果（如 `SELECT MIN(key) FROM t`），无需访问表。
`Using intersect(...)`, `Using union(...)`, `Using sort_union(...)`	表示使用了 Index Merge 优化，分别对应交集、并集、排序并集。

2、其他索引

2.1 B树索引

相对于B+树来说，非叶子节点也存储了数据
所以相同的数据，B树的层高比B+树更高，带来的问题就是磁盘IO拖慢查询速度
可以说基本没有优势而言，很多数据库索引之所以用B树，是因为历史技术债务，而非是因为比B+树快

2.2 倒排索引（全文索引）

所谓倒排，其实是相对于正常查询而言，比如正常查询是通过ID查询内容，但是倒排索引，是先通过分词器将所有内容进行切分，然后将内容与数据ID进行映射，可以通过内容直接查询数据ID集合
核心组件在于倒排表，倒排词典（FST实现）
理解FST，先理解Trie（Prefix Tree），也叫前缀树和字典树

2.2.1 Trie

一个字符占一个节点，每个节点下面又有字符，每个节点都要预留所有可能字符的槽位
所以任意一个字符串都可以通过拆解字符，逐步查询
带来的问题就是树的层高和字符串的长度有关，而且会产生大量无用的空间，占用内存极其之大

2.2.2 FST

finite state transducer，有限状态转换器
在trie的基础上，实现双键映射，前缀，后缀共享，DAG（Directed Acyclic Gragh）有向无环图
比如bat → 30, cat → 30, bar → 20, car → 20
对于Trie来说

root → b → a → t (30)
     → c → a → t (30)
     → b → a → r (20)
     → c → a → r (20)

对于FST来说

        [0] ← root
         |
       /   \
   b/ (+0) c/ (+0)      ← 边1,2: 输入=b/c, 输出=0, 都指向 [1]
     \     /
      \   /
       [1]             ← 注意：b 和 c 的路径在此合并！
         |
       a/ (+0)          ← 边3: 输入=a, 输出=0, 到 [2]
         |
        [2]             ← 现在从这里分叉
        /   \
   t/ (+30) r/ (+20)    ← 边4,5: 输入=t/r, 输出=30/20, 到终态
     /       \
   [3]       [4]