MySQL-数据库索引

索引的基本概念

索引是数据库中用于提高查询效率的数据结构，类似于书籍的目录。它能够帮助MySQL高效地定位数据，而不必扫描整个表。

索引的主要作用：

加快数据检索速度
保证数据的唯一性（唯一索引）
加速表与表之间的连接
减少分组和排序的时间

索引的类型

按存储结构分类

聚簇索引（PRI）：数据行实际存储在索引的叶子节点，InnoDB引擎特有，每个表只能有一个，主键自动成为聚簇索引
非聚簇索引（MUL/UNI）：叶子节点存储的是主键值而非数据行，需要二次查找（回表）获取完整数据，包括普通索引和唯一非主键索引

按数据结构分类

B-Tree索引：最常见的索引类型，适用于全键值、键值范围或键前缀查找
Hash索引：基于哈希表实现，只支持等值比较查询
Full-text索引：全文索引，用于全文搜索
R-Tree索引：空间索引，用于地理数据存储

按功能分类

主键索引：特殊的唯一索引，不允许有空值
覆盖索引：索引包含查询所需全部字段，避免回表操作，可以是复合索引的特殊使用方式
唯一索引：索引列的值必须唯一，允许有空值
复合索引：多个列组合的索引
前缀索引：只索引字段的前面部分字符
普通索引：最基本的索引，无特殊限制

索引实现的架构原理

B-tree核心原理

B-Tree(Balanced Tree，平衡树)是一种自平衡的树数据结构，能够保持数据有序，允许插入、删除和查找操作在对数时间内完成。

B-tree关键字、记录和索引是放在一起的。

mermaid

graph TD
    A[磁盘块1<br/>17, 35</br>data, data] -->|指针p1| B[磁盘块2<br/>8, 12<br/>data, data]
    A -->|指针p2| C[磁盘块3<br/>26, 30<br/>data, data]
    A -->|指针p3| D[磁盘块4<br/>65, 87<br/>data, data]
    
    B -->|指针p1| E[磁盘块5<br/>3, 5<br/>data, data]
    B -->|指针p2| F[磁盘块6<br/>9, 10<br/>data, data]
    B -->|指针p3| G[磁盘块7<br/>13, 15<br/>data, data]
    
    C -->|指针p2| H[磁盘块8<br/>28, 29<br/>data, data]
    
    D -->|指针p1| I[磁盘块9<br/>36, 60<br/>data, data]
    D -->|指针p2| J[磁盘10<br/>75, 79<br/>data, data]
    D -->|指针p3| K[磁盘11<br/>99, 90<br/>data, data]

B-tree特点

多级索引结构：数据组织成平衡的树形结构，每个节点包含多个键值对
有序存储：节点中的键值对按顺序排列，支持二分查找算法
自平衡特性：插入和删除时会自动调整结构保持平衡
磁盘友好设计：节点大小通常与磁盘页大小匹配，减少I/O操作

B-tree优点

高效查询性能：查询时间复杂度为O(logN)，百万级数据只需3-4次I/O即可定位
范围查询优化：数据有序存储使得范围查询(如BETWEEN, >, <)非常高效
排序和分组加速：索引本身的有序性可以直接用于ORDER BY和GROUP BY操作
唯一性保证：唯一索引和主键索引能确保数据唯一性
高并发支持：InnoDB的行级锁基于索引实现

B-tree 查找过程

B-tree的所有节点（中间+叶子）均可存储数据，范围查询需要回溯树结构，IO次数取决于树高，适用于内存数据库、少量数据。

如果要查找数据项29：

第一次IO（磁盘块1）
- 加载根节点（磁盘块1）到内存
- 内存中二分查找确定：17 ≤ 29 < 35 → 选择P2指针
- 注意：虽然内存中的二分查找需要时间，但相比磁盘IO（约10ms级）可忽略不计
第二次IO（磁盘块3）
- 通过P2加载中间节点（磁盘块3）
- 内存中二分查找：26 ≤ 29 < 30 → 选择P2指针
- 关键点：非叶子节点存储的是"路标"值，用于路由决策
第三次IO（磁盘块8）
- 加载叶子节点（磁盘块8）
- 内存中二分查找找到29的记录
- 在B-Tree中，数据可能存储在任何节点（包括中间节点）

B+Tree 核心原理

B+tree根节点和支节点只存储键值和索引信息，数据存储在叶子节点上。

mermaid

graph TD
    A[磁盘块1<br/>28] -->|指针p1| B[磁盘块2<br/>10, 17]
    A -->|指针p2| C[磁盘块3<br/>36， 79]
    
    B -->|指针p1| D[磁盘块4<br/>3, 5, 9, 10<br/>data, data, data, data]
    B -->|指针p2| E[磁盘块5<br/>10, 12, 13, 15<br/>data, data, data, data]
    B -->|指针p3| F[磁盘块6<br/>17, 26<br/>data, data]
    
    C -->|指针p1| G[磁盘块7<br/>28, 29, 30, 35<br/>data, data, data, data]
    C -->|指针p2| H[磁盘块8<br/>36, 60, 65, 75<br/>data, data, data, data]
    C -->|指针p3| I[磁盘块9<br/>79, 87, 90, 99<br/>data, data, data, data]

MySQL InnoDB引擎默认使用B+Tree作为索引结构：

B+tree 结构

B+ 树是一种多路平衡搜索树，非叶子节点存储键值，叶子节点存储完整数据（聚簇索引）或键值+指针（非聚簇索引），叶子节点通过双向链表连接。

B+tree 优势

高效范围查询：叶子节点链表支持快速顺序访问，适合 WHERE id BETWEEN 10 AND 20 等查询。
低树高：多路分支（每个节点存储多个键）减少树高，降低磁盘 I/O。
磁盘优化：节点大小与 InnoDB 页面（默认 16KB）对齐，最大化 I/O 效率。
并发支持：支持细粒度锁（如行锁、间隙锁）和 MVCC（多版本并发控制），适合事务性数据库。

B+tree 劣势

键值重复存储，增加空间开销。
插入/更新可能导致页面分裂，维护成本较高。

B+tree 查找过程

B+tree仅叶子节点存储数据，查询范围在叶子节点链表直接遍历（更高效），IO次数取决于树高，适合磁盘数据库（如 MySQL InnoDB）。InnoDB 默认页大小 16KB，与磁盘块对齐，一次 IO 加载整个节点。

如果要查找数据项29：

第一次 IO（加载根节点：磁盘块1）
- 加载根节点（磁盘块1）到内存
- 磁盘块1 内容：[28]（键值）
- 29 ≥ 28 → 进入 指针 p2（指向 磁盘块3）
第二次 IO（加载中间节点：磁盘块3）
- 加载中间（支）节点（磁盘块3）到内存
- 磁盘块3 内容：[36, 79]（键值）
- 29 < 36 → 进入 指针 p1（指向 磁盘块7）
第三次 IO（加载叶子节点：磁盘块7）
- 加载叶子节点（磁盘块7）到内存
- 磁盘块7 内容：[28, 29, 30, 35]（键值 + 数据）
- 二分查找找到 29，返回对应数据

B+树与B树的区别

B+树是B树的优化版本，专为数据库场景设计，主要改进点包括

数据存储位置：
- B树：所有节点都存储数据
- B+树：仅叶子节点存储数据，非叶子节点只存储索引键值
叶子节点链接：
- B+树的叶子节点通过指针形成有序链表，极大优化范围查询
- B树没有这种链接，范围查询需要多次回溯父节点
查询稳定性：
- B树查询可能在非叶子节点结束，导致查询时间不稳定
- B+树所有查询都必须到达叶子节点，时间复杂度稳定
空间利用率：
- B+树非叶子节点不存储数据，相同空间可存储更多索引键值
- 通常B+树比B树高度低1层，IO次数减少25%

索引的创建与管理

创建索引

sql

-- 创建普通索引
CREATE INDEX idx_name ON table_name(column_name);
ALTER TABLE 表名 ADD INDEX idx_name(column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column_name);
ALTER TABLE 表名 ADD unique index idx_name(column_name);

-- 创建复合索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);
ALTER TABLE 表名 INDEX idx_name(col1, col2, col3);

-- 创建主键索引（通常在建表时指定）
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 创建全文索引
ALTER TABLE table_name ADD FULLTEXT INDEX idx_name (column_name);

查看索引

sql

SHOW INDEX FROM table_name;

输出字段说明

Table - 表名
Non_unique - 索引是否允许重复值：
- 0 表示唯一索引
- 1 表示非唯一索引
Key_name - 索引名称：
- PRIMARY 表示主键索引
- 其他为自定义索引名
Seq_in_index - 索引中的列序号（从1开始）
Column_name - 索引列的名称
Collation - 列在索引中的排序方式：
- A 表示升序
- D 表示降序
- NULL 表示未排序
Cardinality - 索引中唯一值的估计数量（用于优化器判断索引选择性）
Sub_part - 索引前缀长度（如果只索引列的一部分）：
- NULL 表示整个列被索引
- 数字表示索引的字符数
Packed - 指示键是否被压缩（通常为NULL）
Null - 列是否包含NULL值：
- YES 表示允许NULL
- '' 表示不允许NULL
Index_type - 索引类型：
- BTREE (最常见的B树索引)
- HASH
- FULLTEXT (全文索引)
- RTREE (空间索引)
Comment - 索引的附加信息
Index_comment - 创建索引时指定的注释

删除索引

sql

DROP INDEX index_name ON table_name;
-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;

索引的使用策略

适合创建索引的场景

主键自动创建唯一索引
频繁作为查询条件的字段
与其他表关联的外键
排序字段和分组字段
统计或分组字段

不适合创建索引的场景

频繁更新的字段
数据量小的表
区分度低的字段（如性别）
很少被查询的字段
text/blob等大字段

索引失效的常见情况

使用!=或<>操作符
使用OR连接条件（除非所有字段都有索引）
对索引列使用函数或计算
使用LIKE以通配符开头
类型转换导致索引失效
不符合最左前缀原则的复合索引查询

复合索引的最左前缀原则

最左前缀原则是 MySQL 复合索引使用的核心规则，指在使用复合索引时，查询条件必须从索引的最左列开始，并且不能跳过中间的列。

对于复合索引 INDEX(A, B, C)，其实际结构相当于：

按 A 排序
在 A 相同的情况下按 B 排序
在 A 和 B 都相同的情况下按 C 排序

复合索引遵循最左前缀匹配原则，例如：

sql

-- 创建复合索引
CREATE INDEX idx_name ON table(col1, col2, col3);

-- 能使用索引的查询
SELECT * FROM table WHERE col1 = 'a';
SELECT * FROM table WHERE col1 = 'a' AND col2 = 'b';
SELECT * FROM table WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c';

-- 不能使用或部分使用索引的查询
SELECT * FROM table WHERE col2 = 'b'; -- 不能使用
SELECT * FROM table WHERE col2 = 'b' AND col3 = 'c'; -- 不能使用
SELECT * FROM table WHERE col1 = 'a' AND col3 = 'c'; -- 只能用到col1的索引

覆盖索引与回表

覆盖索引

覆盖索引并非一种特殊的索引类型，而是一种优化策略。当查询可以通过扫描索引直接获取所有需要的数据，而无需回表时，我们就说这个查询使用了覆盖索引。

优点

减少 I/O 操作：索引通常远小于表数据，仅读取索引减少了磁盘访问量
降低 CPU 消耗：减少需要解析和处理的数据量
避免回表：这是最直接的好处，消除了二次查询的开销
对 InnoDB 特别有用：InnoDB 的二级索引叶子节点存储了主键值，若二级索引能覆盖查询，则可避免对主键索引的二次查询

案例

例如，假设有 users 表，并在 (username, city) 上建立了复合索引：

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(100),
  city VARCHAR(100),
  age INT
);

-- 复合索引
CREATE INDEX idx_username_city ON users(username, city);

-- 执行查询
SELECT username, city FROM users WHERE username = 'john_doe';

这个查询要返回的 username 和 city 字段都包含在复合索引 idx_username_city 中。数据库引擎只需扫描该索引就能得到结果，无需回表。使用 EXPLAIN 分析此查询，会在 Extra 列看到 Using index，表明使用了覆盖索引

回表

回表发生在使用二级索引（非聚簇索引）进行查询时。二级索引的叶子节点通常只存储索引列的值和对应的主键值。如果 SELECT 语句需要获取的列不在当前使用的二级索引中，数据库引擎就必须先通过二级索引找到对应的主键值，然后再根据这些主键值回到聚簇索引（主键索引）或原表中去查找完整的行数据。这个过程就是回表

缺点

增加磁盘 I/O：每次回表都需要额外访问数据表（或聚簇索引）的数据页
降低查询速度：额外的步骤会增加查询的延迟，数据量大或并发高时问题更明显

案例

例如，假设在 users 表的 username 字段上有普通索引 idx_username：

sql

-- 单列索引
CREATE INDEX idx_username ON users(username);
-- 执行查询
SELECT username, age FROM users WHERE username = 'john_doe';

在 idx_username 索引中查找 username = 'john_doe' 的条目，并获取其对应的主键 id。
由于 age 字段不在 idx_username 索引中，数据库需回表：根据找到的主键 id 值，回到聚簇索引（主键索引）中查找该 id 的完整行数据，以获取 age 字段的值

这个过程需要扫描两棵索引树（二级索引树和聚簇索引树），效率低于覆盖索引。

索引的优化建议

避免过度索引，每个索引都会占用存储空间并影响写入性能
优先考虑区分度高的列作为索引
尽量使用覆盖索引（查询的列都在索引中）
对于长字符串字段，考虑使用前缀索引
定期分析表（ANALYZE TABLE）以更新索引统计信息
使用EXPLAIN分析查询执行计划

EXPLAIN工具的使用

EXPLAIN可以帮助分析SQL语句如何使用索引：

sql

EXPLAIN SELECT * FROM users WHERE username = 'john';

关键字段解释：

type：访问类型，从好到差依次为：system > const > eq_ref > ref > range > index > ALL
possible_keys：可能使用的索引
key：实际使用的索引
rows：预估需要读取的行数
Extra：额外信息，如"Using index"表示使用了覆盖索引

索引的维护

重建索引：当索引碎片较多时

sql

ALTER TABLE table_name ENGINE=InnoDB;

或

sql

OPTIMIZE TABLE table_name;

监控索引使用情况

sql

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

定期维护：对于频繁更新的表，定期检查索引效率

不同存储引擎的索引实现

InnoDB

使用B+Tree索引结构
聚簇索引（主键索引）的叶子节点存储完整数据
二级索引存储主键值
支持自适应哈希索引

MyISAM

使用B+Tree索引结构
非聚簇索引，索引和数据分开存储
索引叶子节点存储数据指针

Memory

默认使用哈希索引
也支持B-Tree索引

MySQL 执行计划(EXPLAIN)

执行计划(EXPLAIN)是MySQL查询优化的重要工具，它能显示MySQL如何执行SQL语句，帮助开发者分析和优化查询性能。

基本使用方法

sql

-- 基础语法
EXPLAIN SELECT * FROM table_name WHERE condition;

-- 或者
DESC SELECT * FROM table_name WHERE condition;

-- 或更详细的分析
EXPLAIN FORMAT=JSON SELECT * FROM table_name WHERE condition;

执行计划字段解析

字段	说明	优化建议
id	查询标识符，相同id表示同一查询块	-
select_type	查询类型： SIMPLE(简单查询) PRIMARY(主查询) SUBQUERY(子查询) DERIVED(派生表)	避免复杂子查询
table	访问的表名	-
partitions	匹配的分区	分区优化
type	访问类型(性能关键指标)： system > const > eq_ref > ref > range > index > ALL	至少达到range级别
possible_keys	可能使用的索引	检查索引设计
key	实际使用的索引	强制索引可用USE INDEX
key_len	使用的索引长度	联合索引使用情况
ref	与索引比较的列或常量	-
rows	预估需要检查的行数	数值越小越好
filtered	条件过滤的百分比	100%最佳
Extra	额外信息： Using index(覆盖索引) Using where(使用WHERE过滤) Using temporary(使用临时表) Using filesort(使用文件排序)	避免Using filesort和Using temporary

重点类型详解

select_type查询类型

SIMPLE：简单查询
PRIMARY：主键查询
SUBQUERY：子查询
DERIVED：派生查询

type访问类型(性能从优到劣)

system：表只有一行数据
const：通过主键或唯一索引查询，最多返回一条记录
eq_ref：联表查询时使用主键或唯一索引关联
ref：辅助索引，使用非唯一索引查找
- union all 语句就是将 in 语句拆分多个单语句
range：辅助索引，索引范围扫描
index：索引全文扫描
ALL：全表扫描(需优化)

Extra字段常见值

Using index：查询只需要通过索引就能获取所需数据(覆盖索引)
Using where：服务器在存储引擎检索行后再进行过滤
Using temporary：需要创建临时表处理查询(需优化)
Using filesort：需要额外排序操作(需优化)

MySQL-数据库索引 ​

索引的基本概念 ​

索引的主要作用： ​

索引的类型 ​

按存储结构分类 ​

按数据结构分类 ​

按功能分类 ​

索引实现的架构原理 ​

B-tree核心原理 ​

B-tree特点 ​

B-tree优点 ​

B-tree 查找过程 ​

B+Tree 核心原理 ​

B+tree 结构 ​

B+tree 优势 ​

B+tree 劣势 ​

B+tree 查找过程 ​

B+树与B树的区别 ​

索引的创建与管理 ​

创建索引 ​

查看索引 ​

输出字段说明 ​

删除索引 ​

索引的使用策略 ​

适合创建索引的场景 ​

不适合创建索引的场景 ​

索引失效的常见情况 ​

复合索引的最左前缀原则 ​

覆盖索引与回表 ​

覆盖索引 ​

优点 ​

案例 ​

回表 ​

缺点 ​