MySQL索引详解
约 11828 字大约 39 分钟
除了对于准备面试来说非常重要之外，善用索引对 SQL 的性能提升非常明显，是一个性价比较高的 SQL 优化手段。
索引介绍
1. 什么是索引？
索引（Index）是一种 用于提高数据库查询效率的数据结构，其作用类似于 书的目录，可以加速数据的检索。
2. 为什么需要索引？
如果没有索引，查询时需要 遍历整个表（全表扫描，Full Table Scan），当数据量变大时，查询速度会大幅下降。
索引的作用：
- 加快查询速度，避免全表扫描。
- 提高排序效率，避免大量的
ORDER BY计算。 - 优化
JOIN操作，提高多表查询效率。
3. 索引的底层数据结构
在 MySQL 中，索引主要使用 B+ 树 结构，而不是 B 树或 Hash 索引。
3.1 B+ 树索引
- MySQL InnoDB 和 MyISAM 都使用 B+ 树作为索引结构。
- B+ 树是对 B 树的改进，更适用于数据库索引：
- 非叶子节点不存储数据，只存储索引，叶子节点存储数据，提高磁盘读取性能。
- 所有数据都存储在叶子节点，便于范围查询。
- 叶子节点使用双向链表连接，可以高效支持顺序扫描。
示例（B+ 树索引示意图）：
30
/ \
10 50
/ \ / \
5 20 35 60 <-- 叶子节点存数据索引加速查询的过程：
- 从根节点（30）开始查找。
- 如果要找
20，则进入左子树（10）。 - 继续进入
20所在的叶子节点，读取数据。
3.2 Hash 索引
- Hash 索引基于哈希表，查询速度非常快（O(1)）。
- MySQL Memory 引擎 使用 Hash 索引。
- 缺点：
- 不支持范围查询（只能匹配单个值）。
- 不支持 ORDER BY、GROUP BY。
- 不支持联合索引的最左前缀匹配。
3.3 红黑树（Red-Black Tree）
- 红黑树是一种平衡二叉搜索树，查询速度较快。
- 适用于内存索引结构，但 MySQL 并未直接使用红黑树作为索引。
- 适用于 key-value 存储，如 Redis 使用的跳表（Skip List）和红黑树。
4. 索引在 MySQL 中的使用
| 索引类型 | 适用场景 | 特点 |
|---|---|---|
| B+ 树索引 | 适用于大多数查询 | 支持范围查询、排序、聚簇索引 |
| Hash 索引 | 适用于高并发等值查询 | 只支持等值查询，不支持范围查询 |
| 红黑树 | 适用于内存结构 | MySQL 没有直接使用，适用于 Redis |
结论**
- MySQL 主要使用 B+ 树索引，因为它支持范围查询、排序和高效的磁盘存储。
- Hash 索引适用于等值查询，但不能做范围查询，在 MySQL 中使用较少（Memory 引擎）。
- 红黑树适用于内存数据结构，但 MySQL 索引没有采用。
索引的合理使用可以 大幅提升数据库查询效率，但滥用索引可能会降低写入性能，因此需要根据业务场景选择合适的索引类型。
索引的优缺点
1. 索引的优点
索引的主要作用是 提升数据库查询效率，其核心优势包括：
1.1 提高查询速度
- 索引减少数据扫描量，避免全表扫描，提高查询效率。
- 减少磁盘 I/O 次数，通过 B+ 树索引，可以更快地找到目标数据，而无需遍历整个表。
示例（使用索引 vs. 不使用索引）：
-- 无索引，全表扫描（低效）
SELECT * FROM users WHERE age = 25;
-- 创建索引后，快速查找（高效）
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age = 25;结果：
- 无索引时：MySQL 需要遍历
users表的所有行，时间复杂度 O(n)。 - 有索引时：MySQL 通过 B+ 树快速查找，时间复杂度 O(log n)。
1.2 通过唯一性索引，保证数据的唯一性
- 唯一索引 (
UNIQUE) 确保表中的某一列数据不会重复，保证数据完整性。
示例（唯一索引）：
CREATE UNIQUE INDEX idx_email ON users(email);
INSERT INTO users (id, name, email) VALUES (1, '张三', 'zhangsan@example.com');
INSERT INTO users (id, name, email) VALUES (2, '李四', 'zhangsan@example.com'); -- 失败，email 不能重复作用：
- 防止重复数据插入，提高数据一致性。
1.3 加速 ORDER BY 和 GROUP BY 查询
- 索引加快排序，避免额外的排序操作，减少 CPU 计算。
示例（索引优化 ORDER BY）：
-- 无索引时，需要额外的排序操作
SELECT * FROM users ORDER BY age;
-- 有索引时，可以直接按索引顺序读取数据
CREATE INDEX idx_age ON users(age);
SELECT * FROM users ORDER BY age;结果：
- 无索引时：MySQL 需要使用 临时表排序（文件排序）。
- 有索引时：MySQL 直接按索引顺序返回数据，提高查询效率。
1.4 加快多表 JOIN 查询
- 在 多表关联查询 时，索引可以 减少连接操作的计算量，加快查询速度。
示例（索引优化 JOIN）：
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;- 如果
orders.user_id没有索引，MySQL 需要遍历整个orders表进行匹配。 - 有索引时，可以 快速查找匹配的行，减少计算量。
2. 索引的缺点
尽管索引能提升查询效率，但也带来了一些额外的成本。
2.1 影响数据插入、更新、删除（DML 操作性能下降）
- 索引需要维护，每次
INSERT、UPDATE、DELETE时，索引都需要更新，影响写入性能。
示例（索引对 INSERT 性能的影响）：
CREATE INDEX idx_name ON users(name);
INSERT INTO users (id, name, age) VALUES (1, '张三', 25);- 索引维护开销：
- 无索引时：直接插入数据，写入速度快。
- 有索引时：
- 数据插入表中，同时更新 B+ 树索引结构。
- 若索引字段是唯一索引，还需 检查是否重复，增加计算量。
优化策略：
- 避免对高频更新的字段建立索引。
- 使用自增主键，保证索引顺序增长，减少索引维护开销。
2.2 占用额外的存储空间
- 索引是物理结构，需要额外的磁盘空间存储。
示例（索引占用空间）：
SHOW TABLE STATUS LIKE 'users'; -- 查看索引占用空间
SHOW INDEX FROM users; -- 查看表的索引情况- 索引越多，表的物理大小越大，占用的存储空间越多。
优化策略：
- 合理选择索引字段，避免冗余索引。
- 定期清理无效索引。
2.3 不一定总是提高查询性能
- 索引查询不一定比全表扫描快！
- 当数据量较小时，全表扫描可能更快：
- 小表（如 1000 行数据）使用索引和不使用索引的差别很小。
- 如果索引命中率低（如
LIKE '%关键字%'），索引查询可能反而更慢。
示例（索引 vs. 全表扫描）：
EXPLAIN SELECT * FROM small_table WHERE id = 10;- 若
small_table只有 500 行，索引查询和全表扫描的性能差异很小。 - 若
small_table有 1000 万行，索引能大幅提升查询速度。
3. 什么时候不适合使用索引？
- 小表（行数 < 1000），全表扫描可能比索引更快。
- 高频更新的字段（索引维护开销大）。
- 低选择性字段（如
性别、是否启用）：- 例如
sex只有M/F两种值，索引作用不大，MySQL 可能会直接全表扫描。
- 例如
4. 总结
| 优点 | 说明 |
|---|---|
| 提高查询速度 | 通过 B+ 树快速定位数据，避免全表扫描 |
| 唯一索引保证数据一致性 | 避免重复数据插入 |
加速 ORDER BY、GROUP BY 查询 | 直接利用索引排序，避免额外计算 |
优化 JOIN 查询 | 在多表关联时提高匹配效率 |
| 缺点 | 说明 |
|---|---|
影响 INSERT、UPDATE、DELETE | 维护索引结构需要额外计算 |
| 占用存储空间 | 索引存储在磁盘上，会增加数据库体积 |
| 小表可能不需要索引 | 数据量少时，全表扫描可能更快 |
结论：索引的合理使用**
- 大表必须建立索引，避免全表扫描。
- 高频更新的列慎用索引，避免索引维护开销。
- 选择性高的列适合作为索引，如
用户 ID、订单编号等。 - 适当使用覆盖索引，减少回表查询，提高查询效率。
索引的合理设计 可以极大提升查询性能，但滥用索引 可能会降低写入性能，需要根据业务场景平衡索引的使用。
索引底层数据结构选型
1. Hash 表
1.1 Hash 表的原理
- Hash 表（哈希表）是基于键值映射（Key-Value）的数据结构。
- 通过 哈希函数 计算 Key 对应的索引位置，从而快速访问数据：
hash = hashfunc(key); index = hash % array_size; - 查找的 时间复杂度接近 O(1)，比树形结构更快。
1.2 Hash 表的优缺点
| 优点 | 缺点 |
|---|---|
| 查询速度极快（O(1)） | 不支持范围查询 |
适用于等值查询（=） | 不支持 ORDER BY、GROUP BY、LIKE、范围查询 |
| 不依赖数据排序 | 哈希冲突 可能降低性能 |
1.3 MySQL 为什么不使用 Hash 作为索引结构？
不支持范围查询
SELECT * FROM users WHERE id < 500;- B+ 树可以高效地遍历 1 到 499 的数据。
- Hash 索引无法有序存储，只能逐个计算 Hash 值，导致效率低下。
不支持
ORDER BY和GROUP BY- Hash 表的存储是无序的，无法直接支持排序操作。
主键冲突影响性能
- 如果哈希函数冲突较多，查询时需要遍历链表或红黑树（JDK 1.8 HashMap 使用红黑树），影响性能。
MySQL InnoDB 仅支持
自适应哈希索引（Adaptive Hash Index）- InnoDB 结合 B+ 树和 Hash 索引，自动检测热点查询，并将其转换为哈希索引，以优化等值查询性能。
2. 二叉查找树（BST）
2.1 BST 原理
- 二叉查找树 满足：
- 左子树的值小于根节点。
- 右子树的值大于根节点。
- 左右子树都是二叉查找树。
示例（BST 结构）：
50
/ \
30 70
/ \ / \
10 40 60 802.2 BST 的问题
树的高度不稳定
- 极端情况下（顺序插入），BST 可能退化成链表：
10 \ 20 \ 30 \ 40 - 最坏情况下，查询时间复杂度退化为 O(n)，影响查询效率。
- 极端情况下（顺序插入），BST 可能退化成链表：
磁盘 I/O 问题
- 数据库索引的主要瓶颈是磁盘 I/O，BST 每次查找只能获取一个节点，导致频繁磁盘读取。
✅ 结论：BST 不适合作为数据库索引。
3. AVL 树
3.1 AVL 树原理
- AVL 树（自平衡二叉查找树）保证左右子树高度差不超过 1。
- 插入和删除时需要进行旋转，以保持平衡。
✅ 查询效率比 BST 更稳定，时间复杂度 O(log n)。
3.2 AVL 树的缺点
- 插入和删除需要频繁旋转
- 旋转操作耗时较多，影响写入性能。
- 每个节点存储一个数据
- 磁盘 I/O 依然是瓶颈，每次读取一个节点需要一次 I/O，查询时可能需要多次磁盘读取。
✅ AVL 树适用于内存索引，但不适合作为数据库索引。
4. 红黑树
4.1 红黑树的原理
- 红黑树是一种近似平衡的二叉查找树，保证左右子树高度不会相差太多。
- 通过“变色 + 旋转”来维持平衡，以减少插入和删除的开销。
✅ 红黑树相比 AVL 树，插入和删除更高效。
4.2 红黑树的缺点
- 查询效率比 AVL 树稍差
- 红黑树 不是严格平衡的，树的高度比 AVL 树略高。
- 磁盘 I/O 仍然是瓶颈
- 数据库索引的目标是减少磁盘 I/O，红黑树仍然存在 I/O 问题。
✅ 红黑树更适合内存数据结构，如 Java TreeMap、TreeSet，但不适合作为数据库索引。
5. B 树 & B+ 树
5.1 B 树（B-Tree）
- B 树是多路平衡查找树，每个节点可以存储多个键值：
- 叶子节点和内部节点都存储数据。
- 查询效率随层级变化，不稳定。
5.2 B+ 树（B+Tree）
✅ B+ 树是 MySQL 的主要索引结构，适用于磁盘存储：
- 叶子节点存储数据，内部节点仅存索引
- B+ 树的所有数据都存储在 叶子节点，内部节点只存索引，提高查询效率。
- 叶子节点通过双向链表连接
- B+ 树的范围查询更快，只需顺序遍历叶子节点，无需递归回溯。
5.3 B+ 树 vs. B 树
| 结构 | 叶子节点存数据 | 内部节点存数据 | 查询效率 | 适用场景 |
|---|---|---|---|---|
| B 树 | ✅ | ✅ | 不稳定 | 内存索引 |
| B+ 树 | ✅ | ❌ | 稳定 | 数据库索引 |
✅ MySQL 选择 B+ 树作为索引结构的原因：
- 磁盘 I/O 友好：B+ 树 每次 I/O 读取多个索引，减少磁盘访问次数。
- 查询稳定：B+ 树 所有数据存储在叶子节点，查询路径固定。
6. MySQL 的索引实现
| 存储引擎 | 索引类型 | 数据结构 |
|---|---|---|
| InnoDB | 聚簇索引（Clustered Index） | B+ 树 |
| MyISAM | 非聚簇索引（Non-Clustered Index） | B+ 树 |
| Memory | Hash 索引 | 哈希表 |
✅ InnoDB 索引机制
- 主键索引（Clustered Index）
- 主键索引的 叶子节点存储完整数据。
- 二级索引（Secondary Index）
- 二级索引存储的是主键，查询时需要 先查索引，再查主键（回表查询）。
结论
| 数据结构 | 是否适合作为 MySQL 索引 | 主要问题 |
|---|---|---|
| Hash | ❌ | 不支持范围查询、ORDER BY |
| BST | ❌ | 查询不稳定，可能退化为链表 |
| AVL 树 | ❌ | 插入删除代价高，磁盘 I/O 低效 |
| 红黑树 | ❌ | 查询效率不如 B+ 树 |
| B 树 | ❌ | 查询路径不稳定 |
| B+ 树 | ✅ | 磁盘 I/O 友好，查询稳定 |
✅ MySQL 选择 B+ 树作为索引结构，因其在磁盘存储和查询性能方面表现最佳。
索引类型总结（详细解析）
1. 按数据结构分类
1.1 BTree 索引
- MySQL 默认最常用的索引类型，基于 B+ 树 实现。
- 叶子节点存储数据，非叶子节点存储 key 和指针，适用于 范围查询、排序、JOIN 等操作。
- MyISAM 和 InnoDB 都使用 B+ 树：
- InnoDB：主键索引（聚簇索引）+ 二级索引（非聚簇索引）。
- MyISAM：所有索引都是 非聚簇索引。
✅ 适用场景：绝大多数 OLTP（在线事务处理） 场景。
1.2 哈希索引
- 基于哈希表（key-value 映射），查询复杂度 O(1)。
- 只支持等值查询 (
=)，不支持范围查询 (<,>,BETWEEN)。 - 适用于 Memory 存储引擎，InnoDB 提供 自适应哈希索引（Adaptive Hash Index）。
❌ 限制：
- 不支持排序、范围查询、模糊查询 (
LIKE)。 - Hash 冲突可能降低查询效率。
✅ 适用场景：
- 高频等值查询（如缓存、Session 存储）。
1.3 RTree 索引
- 用于存储地理空间数据，支持
geometry数据类型（点、多边形等）。 - 适用于 GIS（地理信息系统）查询，如 GPS 位置查询。
❌ 限制：
- 查询效率较低，通常使用 ElasticSearch 或 MongoDB 代替。
✅ 适用场景：
- 地图数据存储，地理位置检索。
1.4 全文索引
- 针对
CHAR,VARCHAR,TEXT字段进行全文搜索，支持 分词查询。 - 查询方式：
MATCH(column) AGAINST('keyword')。 - 适用于 MyISAM 和 InnoDB（MySQL 5.6+）。
❌ 限制：
- 不适用于高并发 OLTP 业务，查询效率不如专门的搜索引擎（如 ElasticSearch）。
✅ 适用场景：
- 博客、新闻站点的文章搜索（如站内搜索）。
2. 按存储方式分类
2.1 聚簇索引（Clustered Index）
- 索引结构和数据存储在一起。
- InnoDB 的主键索引就是聚簇索引：
- 叶子节点存储完整数据。
- 非主键索引存储的是主键值（回表查询）。
✅ 适用场景：
- 大数据量、高查询频率的表。
- 主键查询效率高（避免回表）。
2.2 非聚簇索引（Secondary Index / 非聚集索引）
- 索引结构和数据存储分开。
- MyISAM 存储引擎的所有索引都是非聚簇索引：
- 叶子节点存储数据的物理地址。
❌ 限制：
- 查询时需要回表获取数据（比 InnoDB 效率低）。
✅ 适用场景：
- 查询不以主键为主的应用场景。
3. 按应用场景分类
3.1 主键索引
- 唯一 + 不可为 NULL + 效率最高。
- InnoDB 的主键索引是聚簇索引，存储实际数据。
✅ 适用场景：
- 所有表都应该有主键，建议使用 自增 ID 作为主键。
3.2 普通索引
- 只提供查询加速，不保证唯一性。
- 适用于频繁出现在
WHERE的字段。
✅ 适用场景：
- 查询条件频繁的列（如
status、category_id）。
3.3 唯一索引
- 类似主键索引，但允许 NULL。
- 确保列值唯一性，同时提高查询效率。
✅ 适用场景：
- 唯一性约束（如
email、username）。
3.4 覆盖索引
- 索引中包含了查询需要的所有字段，避免回表，提高查询速度。
示例（覆盖索引）：
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三'; -- 直接从索引中返回数据✅ 适用场景：
- 减少回表查询，提高查询效率。
3.5 联合索引
- 多个列组合成一个索引。
- 遵循最左前缀匹配原则。
示例（联合索引）：
CREATE INDEX idx_name_age ON users(name, age);WHERE name = '张三' AND age = 25✅ 使用索引。WHERE age = 25❌ 索引失效（name 必须在前）。
✅ 适用场景：
- 多个列经常一起查询，如
(category_id, status)。
3.6 前缀索引
- 只对
VARCHAR或TEXT字段的前几个字符建立索引，减少索引存储空间。
示例（前缀索引）：
CREATE INDEX idx_email_prefix ON users(email(10));- 适用于 长文本字段（如邮箱、URL）。
✅ 适用场景：
- 文本字段过长时，索引空间优化。
4. MySQL 8.x 新增索引特性
4.1 隐藏索引
- 不会被优化器使用，但仍然需要维护。
- 用于灰度发布、索引性能测试。
示例（隐藏索引）：
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;✅ 适用场景：
- 测试索引优化方案，避免直接删除索引。
4.2 降序索引
- MySQL 8.0 之前，索引默认是升序。
- 8.0 之后支持降序索引，优化
ORDER BY DESC查询。
示例（降序索引）：
CREATE INDEX idx_created_at_desc ON orders(created_at DESC);✅ 适用场景：
ORDER BY DESC查询优化。
4.3 函数索引
- 索引可以包含计算后的值（MySQL 8.0.13+）。
- 适用于
LOWER(),DATE()这类查询。
示例（函数索引）：
CREATE INDEX idx_lower_name ON users((LOWER(name)));
SELECT * FROM users WHERE LOWER(name) = 'zhangsan';✅ 适用场景：
- 索引包含计算列，提高查询效率。
结论
| 分类 | 类型 | 适用场景 |
|---|---|---|
| 数据结构 | BTree | 默认索引结构，适用于大多数查询 |
| Hash | 仅适用于等值查询（=） | |
| RTree | 适用于地理位置查询（GIS） | |
| 全文索引 | 适用于全文搜索（替代方案：ElasticSearch） | |
| 存储方式 | 聚簇索引 | InnoDB 主键索引，存储实际数据 |
| 非聚簇索引 | 二级索引，需要回表查询 | |
| 应用场景 | 主键索引 | 表的主键，唯一且高效 |
| 联合索引 | 多列查询，遵循最左前缀匹配 | |
| 覆盖索引 | 查询字段包含在索引中，避免回表 | |
| MySQL 8.x | 隐藏索引 | 索引灰度测试 |
| 降序索引 | ORDER BY DESC 查询优化 | |
| 函数索引 | 索引计算列，优化 LOWER() 查询 |
✅ 合理使用索引，提高查询效率，降低存储和维护成本。
主键索引（Primary Key）详解
1. 什么是主键索引？
- 主键索引（Primary Key Index） 是数据表中的 唯一标识字段，用于 唯一标识表中的每一行数据。
- 一个表只能有一个主键，并且：
- 不能为 NULL（不能为空值）。
- 不能重复（唯一性）。
- 默认是聚簇索引（InnoDB 存储引擎）。
✅ 示例：创建主键索引
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
name VARCHAR(50) NOT NULL
);id列被定义为 主键索引，它的值是 唯一且非空 的。
2. MySQL InnoDB 如何选择主键？
- 当表中没有显式指定主键时，InnoDB 存储引擎会自动选择主键：
- 优先选择 唯一索引（
UNIQUE）且NOT NULL的列 作为主键。 - 如果没有唯一索引，MySQL 自动创建一个 6Byte 的隐藏自增主键。
- 优先选择 唯一索引（
✅ 示例：MySQL 选择唯一索引作为主键
CREATE TABLE orders (
order_number VARCHAR(20) UNIQUE NOT NULL, -- 这个字段会被作为主键
user_id INT NOT NULL
);- 由于
order_number是 唯一索引且非空，InnoDB 自动将其作为主键索引。
✅ 示例：MySQL 自动创建 6Byte 隐藏主键
CREATE TABLE products (
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);- 没有
PRIMARY KEY和UNIQUE NOT NULL的列，InnoDB 会自动创建一个 6Byte 的隐藏自增主键。
3. InnoDB 的主键索引是聚簇索引
- 聚簇索引（Clustered Index） 是 主键索引和数据存储在一起 的索引方式。
- 叶子节点存储完整数据，因此：
- 主键查询最快（避免二次查询）。
- 二级索引存储的是主键值，查询时需要 先查二级索引，再回表查数据（回表查询）。
✅ 示例：InnoDB 的聚簇索引
CREATE TABLE employees (
emp_id INT PRIMARY KEY, -- 主键索引
name VARCHAR(100),
department VARCHAR(50)
);- 数据存储在
B+树叶子节点，按emp_id排序存储。 - 查询
emp_id=1001：- 直接在索引中找到数据，不需要回表。
✅ 示例：二级索引的回表查询
CREATE INDEX idx_name ON employees(name);
SELECT * FROM employees WHERE name = 'Alice';- 查询过程：
- 在
idx_name索引中查找Alice，找到对应的emp_id。 - 回表查询
employees表，根据emp_id获取完整数据。
- 在
💡 结论： 主键索引查询更快，因为数据直接存储在索引中，避免了回表查询。
4. 为什么推荐使用自增 ID 作为主键？
- 推荐使用
AUTO_INCREMENT的自增 ID 作为主键，因为：- 索引顺序增长，避免页分裂（降低写入开销）。
- 减少索引维护成本（索引重组成本低）。
- 查询效率最高（主键索引可直接定位数据）。
❌ 不推荐 UUID / 随机字符串作为主键
CREATE TABLE users (
uuid CHAR(36) PRIMARY KEY, -- UUID 作为主键（不推荐）
name VARCHAR(50) NOT NULL
);- UUID 作为主键的缺点：
- 值是随机的，数据写入会导致 索引页分裂，影响写入性能。
- 占用更多存储空间（UUID 36 字节，而
BIGINT仅 8 字节）。 - 查询速度比自增 ID 慢。
✅ 推荐使用自增 ID
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 自增 ID 作为主键（推荐）
name VARCHAR(50) NOT NULL
);5. 主键索引 vs. 唯一索引
| 索引类型 | 是否唯一 | 是否可 NULL | 是否是聚簇索引（InnoDB） | 存储数据方式 |
|---|---|---|---|---|
| 主键索引（Primary Key） | ✅ 唯一 | ❌ 不能 NULL | ✅ 聚簇索引 | 叶子节点存完整数据 |
| 唯一索引（Unique Index） | ✅ 唯一 | ✅ 可 NULL | ❌ 非聚簇索引 | 叶子节点存主键值（需要回表查询） |
💡 结论：
- 主键索引是最优的查询索引，因为 数据存储在叶子节点，查询时不需要回表。
- 唯一索引虽然也能保证唯一性，但查询时可能需要回表（因为叶子节点存的是主键值）。
6. 结论
| 特性 | 描述 |
|---|---|
| 唯一性 | 主键索引保证数据的唯一性 |
| 非空 | 主键字段不能为 NULL |
| 聚簇索引 | InnoDB 默认使用 聚簇索引（数据存储在索引中） |
| 索引选择 | 若未指定主键，MySQL 会 自动选择唯一索引（非 NULL） 作为主键 |
| 自增主键推荐 | 避免索引页分裂，提高查询效率 |
| UUID 作为主键的缺点 | 索引乱序、占用存储空间大、查询慢 |
✅ 最佳实践：
- 所有表都应该有主键。
- 优先选择
AUTO_INCREMENT自增 ID 作为主键，保证索引顺序增长，避免索引分裂，提高查询性能。 - 避免使用 UUID 作为主键，因为它会导致索引碎片化，影响性能。
7. 相关 SQL 示例
7.1 创建自增主键
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);7.2 创建唯一索引
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL -- 唯一索引
);7.3 删除主键
ALTER TABLE users DROP PRIMARY KEY;7.4 删除唯一索引
ALTER TABLE employees DROP INDEX email;✅ 主键索引是数据库中最重要的索引类型，合理使用可以大幅提高查询效率！
二级索引（Secondary Index）详解
1. 什么是二级索引？
- 二级索引（Secondary Index） 又称为 辅助索引 / 非主键索引。
- 与主键索引不同，二级索引的 叶子节点存储的是主键的值，而不是完整的数据。
- 查询过程：
- 先查二级索引，获取主键 ID。
- 再通过主键 ID 在主键索引（聚簇索引）中查找完整数据（回表查询）。
✅ 示例
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '张三';查询过程：
- 在
idx_name索引中查找name = '张三'，找到对应的id。 - 通过
id在 主键索引（聚簇索引） 中查找完整数据。
💡 结论：二级索引需要回表查询，主键索引不需要回表，查询更快。
2. 二级索引的类型
2.1 唯一索引（Unique Index）
- 约束数据唯一性，防止重复值。
- 允许 NULL 值（但多个 NULL 可能视为不同）。
- 索引的叶子节点存储的是主键值，查询时可能需要回表。
✅ 示例
CREATE UNIQUE INDEX idx_email ON users(email);email字段不会出现重复值，但可以为NULL。
查询过程
SELECT * FROM users WHERE email = 'abc@example.com';- 在
idx_email索引中查找 主键 ID。 - 回表查询 完整数据。
2.2 普通索引（Index）
- 仅用于查询加速，不保证唯一性。
- 允许重复值和 NULL 值。
- 查询时可能需要回表。
✅ 示例
CREATE INDEX idx_status ON users(status);status字段查询更快，但允许多个相同的status值。
2.3 前缀索引（Prefix Index）
- 仅适用于字符串（VARCHAR / TEXT）列。
- 只索引字符串的前几个字符，减少索引大小，提高查询性能。
✅ 示例
CREATE INDEX idx_email_prefix ON users(email(10));- 只索引
email的前 10 个字符，节省索引空间。
💡 适用场景
- 长文本字段，且前几个字符足以区分数据（如
email、url）。
2.4 全文索引（Full Text Index）
- 适用于全文搜索（
CHAR,VARCHAR,TEXT）。 - 支持
MATCH(column) AGAINST('keyword')查询。 - MySQL 5.6+ 开始支持 InnoDB。
✅ 示例
CREATE FULLTEXT INDEX idx_content ON articles(content);- 适用于 文章、博客等文本搜索。
❌ 限制
- 不适用于高并发 OLTP 业务，查询效率较低，推荐使用 ElasticSearch 代替。
3. 二级索引的查询优化
3.1 避免不必要的回表查询
- 使用覆盖索引（Covering Index），让索引包含查询的所有字段，避免回表。
✅ 示例
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';- 查询
name, age，这些字段 都在索引中，无需回表。
3.2 联合索引（Composite Index）
- 多个字段组合成一个索引，查询更高效。
- 遵循最左前缀匹配原则。
✅ 示例
CREATE INDEX idx_name_status ON users(name, status);查询优化
SELECT * FROM users WHERE name = '张三' AND status = 1; -- ✅ 使用索引
SELECT * FROM users WHERE status = 1 AND name = '张三'; -- ✅ 使用索引
SELECT * FROM users WHERE name = '张三'; -- ✅ 使用索引
SELECT * FROM users WHERE status = 1; -- ❌ 不能完全使用索引💡 结论
- 最左前缀匹配：查询时 必须从最左侧开始匹配，否则索引可能失效。
4. 二级索引 vs. 主键索引
| 对比项 | 主键索引（Primary Key Index） | 二级索引（Secondary Index） |
|---|---|---|
| 数据存储 | 叶子节点存储完整数据 | 叶子节点存储主键值 |
| 查询方式 | 直接查询 | 先查索引，再回表查询 |
| 查询性能 | 最高（无回表） | 可能需要回表（除非覆盖索引） |
| 是否唯一 | 唯一 | 可唯一 / 非唯一 |
| 索引类型 | 聚簇索引 | 非聚簇索引 |
✅ 最佳实践
- 主键查询最快（不需要回表）。
- 二级索引可以加速查询，但可能需要回表，可以通过 覆盖索引、联合索引优化查询。
5. 结论
| 索引类型 | 作用 | 是否唯一 | 是否回表 | 适用场景 |
|---|---|---|---|---|
| 主键索引 | 记录唯一标识 | ✅ 唯一 | ❌ 不回表 | 高效查询 |
| 唯一索引 | 确保字段唯一 | ✅ 唯一 | ✅ 可能回表 | 唯一性约束 |
| 普通索引 | 提高查询速度 | ❌ 可重复 | ✅ 可能回表 | 频繁查询字段 |
| 前缀索引 | 适用于长字符串 | ❌ 可重复 | ✅ 可能回表 | VARCHAR 长字段 |
| 全文索引 | 适用于文本搜索 | ❌ 可重复 | ✅ 可能回表 | TEXT 查询 |
| 联合索引 | 组合多个字段 | ❌ 可重复 | ✅ 可能回表 | 复合查询 |
✅ 最佳实践
- 尽量使用主键索引查询（避免回表）。
- 使用覆盖索引减少回表，提高查询速度。
- 合理使用联合索引，遵循最左前缀匹配。
- 字符串字段使用前缀索引，减少索引存储空间。
💡 结论：合理使用二级索引，提高查询效率，避免回表查询！ 🚀
聚簇索引 vs. 非聚簇索引
1. 聚簇索引（Clustered Index）
1.1 聚簇索引的定义
- 聚簇索引是一种数据存储方式，不是一种独立的索引类型。
- 索引结构和数据存储在一起，即 叶子节点不仅存索引值，还存完整数据。
- InnoDB 存储引擎的主键索引就是聚簇索引。
1.2 聚簇索引的存储结构
- 在 InnoDB 中，表数据存储在 B+ 树的叶子节点，并且按照 主键顺序存储。
- 每个表只能有一个聚簇索引，因为数据存储方式唯一。
✅ 示例
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 聚簇索引
name VARCHAR(50) NOT NULL
);id是主键，数据按id顺序存储。
1.3 聚簇索引的查询方式
SELECT * FROM users WHERE id = 1001;- 直接从 B+ 树叶子节点找到
id=1001的完整数据，不需要回表查询。
✅ 聚簇索引查询优势：
- 查询速度快（主键索引查询直接获取完整数据）。
- 范围查询和排序效率高（数据按主键有序存储）。
1.4 聚簇索引的优缺点
✅ 优点
- 查询速度快（直接定位数据）。
- 优化排序和范围查询（按主键存储数据）。
❌ 缺点
- 插入、删除、更新开销大（影响 B+ 树的平衡）。
- 主键不宜太长（主键被二级索引引用，影响存储）。
2. 非聚簇索引（Non-Clustered Index）
2.1 非聚簇索引的定义
- 索引结构和数据分开存储。
- 叶子节点存储索引值+主键值，而不存完整数据。
- MySQL 的二级索引（辅助索引）属于非聚簇索引。
- MyISAM 存储引擎所有索引都是非聚簇索引。
2.2 非聚簇索引的存储结构
- 在 InnoDB 中，二级索引叶子节点存主键值，而不是数据地址。
- 查询时先查二级索引，再回表查主键索引获取完整数据（回表查询）。
✅ 示例
CREATE INDEX idx_name ON users(name);name索引存储name -> id，查询时需要 先查idx_name再回表查id。
2.3 非聚簇索引的查询方式
SELECT * FROM users WHERE name = 'Alice';查询过程（回表查询）：
- 在
idx_name索引中查找Alice，找到对应的id。 - 使用
id在主键索引（聚簇索引）中查找完整数据。
✅ 非聚簇索引查询优势
- 支持多个索引（可为多个字段建立索引）。
- 更新代价较小（索引结构独立于数据）。
❌ 非聚簇索引查询缺点
- 回表查询导致额外的 I/O。
- 索引查询可能比主键索引慢。
3. 聚簇索引 vs. 非聚簇索引
| 对比项 | 聚簇索引（Clustered Index） | 非聚簇索引（Non-Clustered Index） |
|---|---|---|
| 存储方式 | 索引和数据存储在一起 | 索引和数据分开存储 |
| 叶子节点存储 | 存完整数据 | 存储主键值 |
| 查询速度 | 快（直接获取数据） | 可能需要回表查询 |
| 插入、更新 | 代价大（影响 B+ 树平衡） | 代价小 |
| 索引数量 | 只能有一个 | 可以有多个 |
| 适用场景 | 主键查询、范围查询 | 组合索引、非主键查询 |
✅ 最佳实践
- 主键查询尽量使用聚簇索引，提高查询效率。
- 二级索引可以加速查询，但尽量减少回表（可使用覆盖索引）。
4. 非聚簇索引一定需要回表查询吗？
❌ 不一定！
- 如果索引包含查询的所有字段，则不需要回表（覆盖索引）。
✅ 示例
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = 'Alice';- 查询的
name和age都包含在idx_name_age索引中，直接从索引返回数据，不需要回表。
✅ 覆盖索引优势
- 减少 I/O 开销（无需回表查询）。
- 提高查询效率（直接从索引返回数据）。
结论
| 索引类型 | 存储方式 | 查询方式 | 查询效率 | 适用场景 |
|---|---|---|---|---|
| 聚簇索引 | 索引和数据一起存储 | 直接读取数据 | 最快（无回表） | 主键查询、范围查询 |
| 非聚簇索引 | 索引和数据分开存储 | 先查索引，再回表查主键 | 可能需要回表（较慢） | 非主键查询、组合索引 |
| 覆盖索引 | 索引存储所有查询字段 | 直接返回索引数据 | 高效（避免回表） | 优化二级索引查询 |
✅ 最佳实践
- 主键查询优先使用聚簇索引，提高查询性能。
- 避免过多的非聚簇索引回表查询，可以使用 覆盖索引。
- 使用联合索引优化查询，遵循最左前缀匹配原则。
🚀 合理使用索引，优化 MySQL 查询性能！
覆盖索引 vs. 联合索引（详细解析）
1. 覆盖索引（Covering Index）
1.1 覆盖索引的定义
- 如果一个索引包含（覆盖）所有需要查询的字段的值，则称之为覆盖索引。
- 避免回表查询，直接从索引中获取数据，提高查询效率。
✅ 示例
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = 'Alice';idx_name_age包含name和age字段。- 查询的字段
name和age已经包含在索引中，不需要回表查询。
1.2 为什么覆盖索引能提高查询效率？
- 避免回表查询（非聚簇索引的叶子节点存的是主键值，查询完整数据通常需要回表）。
- 减少磁盘 I/O（直接从索引中读取数据）。
- 适用于大数据量、高并发查询。
✅ 示例：回表查询 vs. 覆盖索引
-- 普通索引（可能需要回表）
SELECT * FROM users WHERE name = 'Alice';
-- 覆盖索引（无需回表）
SELECT name, age FROM users WHERE name = 'Alice';- 第一种查询：查找
name = 'Alice'，索引idx_name只能获取id，然后需要回表查询完整数据。 - 第二种查询：所有查询字段
name, age都在idx_name_age索引中，直接从索引返回数据，避免回表，提高查询效率。
1.3 如何判断 SQL 是否使用了覆盖索引？
- 使用
EXPLAIN查看Extra列：Using index→ 使用覆盖索引（无需回表）。Using where+Using index→ 同时使用索引过滤和覆盖索引。Using filesort→ 索引未覆盖，需要额外排序（优化建议：建立合适的索引）。
✅ 示例
EXPLAIN SELECT name, age FROM users WHERE name = 'Alice';如果 Extra 显示 Using index，说明 SQL 使用了覆盖索引。
2. 联合索引（Composite Index）
2.1 联合索引的定义
- 联合索引（Composite Index）是对多个列同时建立的索引。
- 可以加速多列查询，减少单独索引合并的计算开销。
✅ 示例
CREATE INDEX idx_name_age ON users(name, age);- 索引
idx_name_age覆盖name, age，可以优化如下查询：SELECT * FROM users WHERE name = 'Alice' AND age = 25; - 比单独
INDEX(name) + INDEX(age)更高效，因为 避免索引合并，提高查询性能。
2.2 最左前缀匹配原则
- MySQL 使用联合索引时，会优先匹配索引的最左字段。
- 从左到右依次匹配索引列，一旦遇到范围查询（如
<,>,BETWEEN），后续索引可能失效。
✅ 示例
CREATE INDEX idx_name_age_class ON users(name, age, class);SQL 是否能使用索引？
SELECT * FROM users WHERE name = 'Alice'; -- ✅ 使用索引（name 在最左）
SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- ✅ 使用索引（符合最左前缀）
SELECT * FROM users WHERE age = 25; -- ❌ 无法使用索引（跳过 name）
SELECT * FROM users WHERE age = 25 AND class = 'A'; -- ❌ 无法使用索引（跳过 name）WHERE name = 'Alice'可以使用索引，因为name在最左侧。WHERE age = 25无法使用索引，因为age不是索引的最左列。
2.3 联合索引 vs. 单列索引
| 索引类型 | 查询方式 | 适用场景 |
|---|---|---|
| 单列索引 | INDEX(name)，INDEX(age) | 仅查询 name 或 age |
| 联合索引 | INDEX(name, age) | 同时查询 name 和 age，更高效 |
✅ 联合索引适用于高并发、大数据查询，优先匹配 最左前缀。
3. 覆盖索引 vs. 联合索引
| 对比项 | 覆盖索引（Covering Index） | 联合索引（Composite Index） |
|---|---|---|
| 定义 | 索引覆盖查询字段，避免回表 | 多个列组合成索引 |
| 查询效率 | 高（减少 I/O，避免回表） | 高（优化多列查询） |
| 索引结构 | 可单列索引或联合索引 | 必须多列索引 |
| 优化方式 | 覆盖查询字段 | 遵循最左前缀匹配 |
| 适用场景 | 需要查询的字段都在索引中 | 多列查询，避免索引合并 |
结论
✅ 最佳实践
- 使用覆盖索引减少回表，提高查询效率。
- 合理设计联合索引，遵循最左前缀匹配原则。
- 避免创建多个单列索引，优先考虑联合索引。
- 定期使用
EXPLAIN分析查询，优化索引结构。
🚀 掌握覆盖索引 + 联合索引，优化 MySQL 查询性能！
索引下推（Index Condition Pushdown, ICP）详解
1. 什么是索引下推？
索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 引入的一项索引优化技术，允许 存储引擎在索引遍历过程中执行部分 WHERE 条件的判断，直接在存储引擎层过滤数据，从而 减少回表次数，提高查询效率。
2. 索引下推的工作原理
在 MySQL 5.6 之前，如果查询使用了非主键索引（如联合索引），存储引擎只能通过索引字段获取主键 ID，然后 回表 取出完整数据，并由 Server 层（MySQL 上层） 进行 WHERE 条件过滤。
✅ MySQL 5.6 引入 ICP 后的优化
- 存储引擎层可以提前过滤一部分
WHERE条件，减少无效数据返回 Server 层，减少回表查询次数，提高查询效率。
✅ 索引下推 vs. 无索引下推
| 对比项 | 无索引下推 | 索引下推（ICP） |
|---|---|---|
| 数据过滤层 | Server 层 | 存储引擎层 |
| 回表查询 | 先回表再筛选 | 先筛选再回表 |
| 性能 | 可能回表多次 | 减少回表，提高查询速度 |
3. 索引下推示例
3.1 测试环境
创建 user 表，并创建联合索引：
CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`zipcode` VARCHAR(20) NOT NULL,
`birthdate` DATE NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_zipcode_birthdate` (`zipcode`,`birthdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;执行查询：
EXPLAIN SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;3.2 无索引下推（MySQL 5.6 之前）
- 存储引擎 先通过
zipcode查找匹配的行，并获取 主键 ID。 - 存储引擎 进行 回表查询 获取完整数据。
- Server 层 再检查
MONTH(birthdate) = 3，过滤不符合条件的行。
🔴 问题：
- 所有
zipcode = '431200'的记录都需要回表，导致 大量无效回表查询。
3.3 开启索引下推（MySQL 5.6+）
- 存储引擎 在索引查找
zipcode = '431200'时，同时检查MONTH(birthdate) = 3。 - 只有符合
MONTH(birthdate) = 3的行才会进行回表查询，减少无效回表。
✅ 优化点：
- 减少回表次数，提升查询效率。
- 减少 Server 层的计算量，降低数据传输量。
4. 使用 EXPLAIN 查看索引下推
执行：
EXPLAIN SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;结果：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | user | range | idx_zipcode_birthdate | idx_zipcode_birthdate | 22 | NULL | 50000 | 50.00 | Using index condition |
📌 关键字段
Using index condition说明 MySQL 启用了索引下推，即 MySQL 在索引层就对birthdate进行筛选，减少回表。
5. 索引下推的适用场景
✅ 适用于以下情况
- 使用
range、ref、eq_ref等索引查找方式（如WHERE col >= x AND col <= y）。 - 联合索引的非主键索引字段参与查询（如
zipcode, birthdate）。 - InnoDB 和 MyISAM 存储引擎（适用于非聚簇索引）。
- 适用于查询范围过滤，而不是精确匹配。
❌ 不适用的情况
- 主键索引（聚簇索引）：主键索引查询不需要回表，本身已经高效。
- 索引字段包含
LIKE '%xxx'（前缀通配符查询）：MySQL 无法高效利用索引。 - 子查询（Subquery）：子查询通常会创建临时表，无法使用索引下推。
- 存储过程（Stored Procedure）：存储引擎无法调用存储函数，无法执行索引下推。
6. 索引下推 vs. 覆盖索引
| 特性 | 索引下推（ICP） | 覆盖索引（Covering Index） |
|---|---|---|
| 优化方式 | 索引层筛选数据，减少回表 | 索引包含查询字段，避免回表 |
| 适用范围 | 联合索引、非主键索引 | 查询字段全部在索引中 |
| 数据存储 | 需要回表查询完整数据 | 直接从索引返回数据 |
| 查询性能 | 提高索引效率，减少 I/O | 最高效，避免回表 |
| 适用场景 | range、ref、eq_ref 查询 | SELECT name FROM users WHERE name = 'Alice' |
✅ 最佳实践
- 尽量使用覆盖索引，避免回表查询。
- 对于范围查询，使用索引下推优化回表策略。
- 定期使用
EXPLAIN分析查询计划，检查Using index condition。
** 结论**
| 优化技术 | 作用 | 适用场景 | 查询优化效果 |
|---|---|---|---|
| 索引下推（ICP） | 存储引擎提前过滤数据，减少回表次数 | 非主键索引、范围查询、联合索引 | 减少无效回表，降低 I/O |
| 覆盖索引 | 索引包含查询字段，避免回表 | 查询字段全部在索引中 | 最高效，查询速度最快 |
✅ 索引下推是一种 MySQL 查询优化技术，主要用于减少回表查询，提高索引利用率。
🚀 结合覆盖索引和索引下推，可大幅提升 MySQL 查询性能！
正确使用索引的最佳实践
1. 选择合适的字段创建索引
✅ 建议
不为 NULL 的字段
- 索引字段应尽量避免
NULL，因为NULL可能会导致索引优化困难。 - 替代方案：使用
0, 1, true, false等短值替代NULL。
- 索引字段应尽量避免
被频繁查询的字段
- 适用于
SELECT查询频繁的列，例如 用户email、订单order_id。
- 适用于
被
WHERE过滤的字段WHERE语句中频繁出现的字段应该加索引，提高查询效率。
频繁用于
ORDER BY、GROUP BY的字段- 索引存储数据是有序的，可以加快排序查询时间。
作为外键或
JOIN连接的字段- 被 JOIN 关联的字段应加索引，提高多表查询效率。
2. 慎重在高频更新字段上建立索引
✅ 建议
- 索引会影响
INSERT、UPDATE、DELETE操作的性能。 - 高频更新字段应慎重加索引，因为 索引需要同步更新。
❌ 错误示例
CREATE INDEX idx_salary ON employees(salary); -- `salary` 经常被修改，索引维护成本高✅ 优化方案
- 仅在查询性能明显受影响时，考虑为
salary添加索引。
3. 限制索引数量（建议不超过 5 个）
✅ 建议
- 索引过多会降低写入性能，并增加查询优化器的负担。
- 一个表建议最多 5 个索引，避免 MySQL 优化器评估过多索引，影响查询效率。
❌ 错误示例
CREATE INDEX idx_1 ON users(name);
CREATE INDEX idx_2 ON users(email);
CREATE INDEX idx_3 ON users(age);
CREATE INDEX idx_4 ON users(status);
CREATE INDEX idx_5 ON users(city);
CREATE INDEX idx_6 ON users(country); -- ⚠️ 索引过多✅ 优化方案
- 优先使用联合索引，减少索引数量，提高查询效率。
4. 优先使用联合索引而非单列索引
✅ 建议
- 联合索引比多个单列索引更高效，因为 MySQL 只会选择一个索引执行查询。
- 遵循最左前缀匹配原则。
❌ 错误示例
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);✅ 优化方案
CREATE INDEX idx_name_age ON users(name, age); -- 使用联合索引idx_name_age适用于以下查询：SELECT * FROM users WHERE name = 'Alice'; -- ✅ 使用索引 SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- ✅ 使用索引 SELECT * FROM users WHERE age = 25; -- ❌ 无法使用索引（最左匹配原则）
5. 避免冗余索引
✅ 建议
- 冗余索引会占用存储空间，并影响写入性能。
❌ 错误示例
CREATE INDEX idx_name_city ON users(name, city);
CREATE INDEX idx_name ON users(name); -- ⚠️ `idx_name` 是冗余索引✅ 优化方案
- 删除
idx_name，只保留idx_name_city，可同时用于name和name, city查询。
6. 字符串索引建议使用前缀索引
✅ 建议
- 长字符串索引会增加索引大小，影响查询效率。
- 前缀索引可以减少索引大小，提高查询性能。
❌ 错误示例
CREATE INDEX idx_email ON users(email); -- ⚠️ `email` 是长字符串，索引占用大✅ 优化方案
CREATE INDEX idx_email_prefix ON users(email(10)); -- 只索引 `email` 的前 10 个字符7. 避免索引失效
✅ 索引失效的常见原因
SELECT *不会直接导致索引失效，但不建议使用SELECT * FROM users WHERE name = 'Alice'; -- ✅ 仍然使用索引，但可能影响查询优化- 组合索引未遵守最左前缀匹配
CREATE INDEX idx_name_age_city ON users(name, age, city); SELECT * FROM users WHERE age = 25 AND city = 'NY'; -- ❌ 无法使用索引 - 索引字段使用计算、函数
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- ❌ 索引失效 SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'; -- ✅ 使用索引 LIKE以%开头SELECT * FROM users WHERE name LIKE '%Alice'; -- ❌ 索引失效 SELECT * FROM users WHERE name LIKE 'Alice%'; -- ✅ 索引生效OR查询中某个字段没有索引SELECT * FROM users WHERE name = 'Alice' OR age = 25; -- ❌ 可能不使用索引
8. 删除长期未使用的索引
✅ 建议
- 定期检查并删除未使用的索引，减少存储占用，优化查询。
✅ MySQL 5.7+ 查询未使用索引
SELECT * FROM sys.schema_unused_indexes;- 发现未使用索引后，可以使用
DROP INDEX删除：ALTER TABLE users DROP INDEX idx_old;
9. 使用 EXPLAIN 分析 SQL 语句
✅ 建议
- 使用
EXPLAIN关键字检查索引是否生效。
EXPLAIN SELECT * FROM users WHERE name = 'Alice';📌 关键字段解读
| 字段 | 含义 |
|---|---|
| type | 访问方式（index / range / ALL） |
| possible_keys | 可能使用的索引 |
| key | 实际使用的索引 |
| rows | 预计扫描的行数（越少越好） |
| Extra | 额外信息（Using index 代表覆盖索引） |
10. 结论
✅ 索引优化最佳实践
| 优化点 | 建议 |
|---|---|
| 创建合适的索引 | 避免 NULL，使用频繁查询字段 |
| 减少索引数量 | 每张表不超过 5 个索引 |
| 优先使用联合索引 | 避免多个单列索引 |
| 删除冗余索引 | 减少存储占用，提高查询效率 |
| 使用前缀索引 | 优化长字符串索引 |
| 避免索引失效 | 避免计算、函数、% 开头的 LIKE 查询 |
| 定期删除未使用索引 | 提高存储和查询效率 |
使用 EXPLAIN 分析 SQL | 检查索引是否生效 |
🚀 合理使用索引，可大幅提升 MySQL 查询性能，优化数据库效率！ |
篇幅问题，我这里只是简单介绍了一下 MySQL 执行计划，详细介绍请看：MySQL 执行计划分析这篇文章。