Forrest
返回归档
⌚MySQL

MySQL-索引

更新于 2026/01/234 分钟阅读
文章目录

索引的分类

按照数据结构分

  1. B+ 树索引
  2. hash索引
  3. full-text索引

按照物理存储结构分

  1. 主键索引（聚簇索引）
  2. 二级索引

按照字段分

  1. 主键索引
  2. 唯一索引
  3. 普通索引
  4. 前缀索引

为什么使用了索引后，查询会加快

Mysql默认使用innodb存储引擎，索引数据结构默认为B+树

  1. 索引使用树形结构组织数据，相比于无索引的场景，查询的时间复杂度由O(N)变为O(logN)，大大减少了需要扫描的数据量
  2. 同时，将随机的IO转变为了顺序IO，减少了等待磁盘旋转的时间

从数据页的角度看B+树

innodb的数据是按【数据页】为单位来读写的；数据库的I/O最小单位是16KB

数据页的组成部分

image

不同的数据页之间通过指针相互连接，构造一个双向的链表。

image

不同的数据页的物理地址不是连续的，但在逻辑上是连续的。

具体的数据页内部的数据存储

将数据分为多个分组，每个分组中有相应的数据，数据之间通过单项链表进行连接

image

具体的查询过程

  1. 从根结点开始，通过二分法找到id对应存储的page
  2. 在page中（叶子结点），在多个slot中使用二分查找，id对应数据所在的slot
  3. 找到slot后，在这个slot中遍历一遍，找到对应id的具体数据

image

为什么MySQL的索引数据结构是B+树

  1. B+树的非叶子结点存放的仅存放索引，因此在数据量相同的情况下，相比于同时存储数据和索引的B树，能够存储更多的索引内容，所以B+树会更加矮胖一些，查询底层节点的IO次数更少
  2. 同时，B+树的树高度稳定，一般只有三到四层，所以查询效率也稳定
  3. B+树的非叶子结点中有大量的冗余节点，当插入、删除节点时，B+树都不会发生太大的变化，效率比较高
  4. B+树的叶子结点之间通过链表连接，有利于范围查询

什么场景需要建立索引

  1. 表数据（行数）比较多，同时查询需求旺盛
  2. 字段的区分度高
  3. 字段经常用于where查询、group by、order by

索引失效的场景

  1. 对于前缀索引：使用了 %xx% 或%x
  2. 对于联合索引：不满足最左前缀原则
  3. 对于普通索引：对于索引字段进行计算，类型转换的场景
  4. where子句中包含or

如何进行索引优化

  1. 首先主键是自增的
  2. 防止索引失效
  3. 覆盖索引优化
  4. 前缀索引优化

explain中字段的含义

image

  1. possible_keys：可能使用到的索引
  2. key字段表示实际使用的索引，如果为NULL，则说明没有使用索引
  3. key_len表示索引的长度
  4. rows表示扫描的行数
  5. type表示数据扫描的类型，这是我们需要重点关注的部分

types

常见的扫描查询效率由低到高排序

  • ALL：全表扫描
  • index：全索引扫描
  • range：索引范围扫描
  • ref：非唯一索引的扫描
  • eq_ref：一般用于多表的联查
  • const：使用常量值进行了查询，一般是 where id=1 这种

索引下推

索引下推(ICP)是 MySQL 5.6 及以上版本引入的查询优化技术，核心目的是减少回表次数、降低服务器层与存储引擎层的数据交互开销。

建表

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  age INT,
  city VARCHAR(20),
  -- 联合索引：name + age
  INDEX idx_name_age (name, age)
);

INSERT INTO user VALUES 
(1, '张三', 20, '北京'),
(2, '张三', 25, '上海'),
(3, '李四', 30, '广州');

执行SQL

SELECT * FROM user WHERE name = '张三' AND age > 22;

有无 ICP 的执行流程对比

无 ICP（MySQL 5.5 及以下） 有 ICP（MySQL 5.6+）
1. 存储引擎遍历 idx_name_age 索引，找到所有 name='张三' 的索引条目（包含 id=1、id=2）；
  1. 基于这两个 id 回表，取出整行数据（id、name、age、city）；
  2. 返回给服务器层，服务器层过滤 age>22，最终保留 id=2 的记录。 | 1. 存储引擎遍历 idx_name_age 索引，找到 name='张三' 的条目后，直接在索引中判断 age>22；
  3. 仅保留 age=25 的索引条目（id=2），基于此回表；
  4. 返回给服务器层，无需再过滤。 |