索引是 MySQL 数据库性能优化的核心手段，合理使用索引能将查询效率提升数十甚至上百倍。但不当的索引设计反而会导致性能下降。本文将从索引的底层原理出发，详解各类索引的特性与适用场景，并结合实战案例分享索引优化的最佳实践。
一、索引的本质与工作原理
1. 什么是索引？
索引（Index）是存储引擎用于快速查找记录的一种数据结构，它就像书籍的目录，能让数据库无需扫描整个表就能定位到目标数据。
没有索引时，MySQL 会执行全表扫描（Full Table Scan），逐行检查每条记录是否符合查询条件，时间复杂度为 O (n)。
有索引时，通过索引结构可直接定位数据位置，时间复杂度通常为 O (log n)。
2. 底层数据结构：B + 树
MySQL 中绝大多数存储引擎（如 InnoDB）的索引采用B + 树结构，其设计兼顾了查询效率与磁盘 IO 特性：
-
B + 树的特点：
- 多路平衡查找树，每个节点可存储多个关键字
- 叶子节点按顺序链接，形成双向链表，便于范围查询
- 非叶子节点仅作为索引，实际数据只存储在叶子节点
-
为什么选择 B + 树？：
- 相比二叉树，层级更少（通常 3-4 层即可支持千万级数据），减少磁盘 IO
- 相比哈希索引，支持范围查询和排序操作
- 相比 B 树，叶子节点的链表结构更适合全表扫描和范围查询
3. 聚簇索引与非聚簇索引
InnoDB 引擎中存在两种索引类型，理解它们的区别是索引优化的关键：
-
聚簇索引（Clustered Index）：
- 索引与数据存储在一起，叶子节点包含完整的行数据
- 每张表只能有一个聚簇索引（通常是主键索引）
- 查询时通过聚簇索引可直接获取完整数据，无需回表
-
非聚簇索引（Secondary Index）：
- 索引与数据分离，叶子节点存储的是聚簇索引的键值（主键值）
- 一张表可以有多个非聚簇索引
- 查询时需要先通过非聚簇索引找到主键，再通过聚簇索引获取数据（回表操作）
-- 示例：聚簇索引与非聚簇索引查询路径
-- 表结构：id(主键)，name(普通索引)，age
SELECT * FROM user WHERE id = 100; -- 直接通过聚簇索引获取数据
SELECT * FROM user WHERE name = '张三'; -- 非聚簇索引→主键→聚簇索引（回表）
二、MySQL 索引类型及适用场景
1. 主键索引（PRIMARY KEY）
- 特性：唯一且非空，默认创建聚簇索引
- 适用场景：表中必须有且仅有一个主键，通常用于唯一标识记录
- 创建方式：
-- 创建表时指定
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY (id) -- 主键索引
);
-- 已有表添加
ALTER TABLE user ADD PRIMARY KEY (id);
2. 唯一索引（UNIQUE）
- 特性：索引列的值必须唯一，但允许为空（最多一个 NULL 值）
- 适用场景：需要保证列值唯一性的字段（如手机号、邮箱）
- 创建方式：
CREATE UNIQUE INDEX idx_user_phone ON user(phone);
3. 普通索引（INDEX）
- 特性：最基本的索引类型，无唯一性约束
- 适用场景：频繁出现在 WHERE 条件、JOIN 连接中的字段
- 创建方式：
CREATE INDEX idx_user_age ON user(age);
-- 或简化写法
ALTER TABLE user ADD INDEX idx_user_name (name);
4. 联合索引（Composite Index）
- 特性：由多个字段组合而成的索引，遵循最左前缀原则
- 适用场景：查询条件包含多个字段的组合查询
- 创建方式：
-- 创建（age, name）联合索引
CREATE INDEX idx_user_age_name ON user(age, name);
- 最左前缀原则：
联合索引(a, b, c)可有效匹配的查询条件：WHERE a = ?WHERE a = ? AND b = ?WHERE a = ? AND b = ? AND c = ?
无法匹配的情况：WHERE b = ?（缺少最左前缀 a）WHERE a = ? AND c = ?（跳过了 b，c 无法使用索引）
5. 全文索引（FULLTEXT）
- 特性：用于全文搜索，支持在大文本中搜索关键词
- 适用场景：文章内容、评论等长文本字段的模糊查询
- 注意事项：
- 仅 InnoDB 和 MyISAM 支持
- 不支持中文（需借助第三方插件如 ngram）
- 创建方式：
CREATE FULLTEXT INDEX idx_article_content ON article(content);
-- 查询用法
SELECT * FROM article WHERE MATCH(content) AGAINST('数据库 索引');
三、索引失效的常见场景
即使创建了索引，在某些情况下 MySQL 也会选择不使用索引，导致全表扫描：
1.使用函数或表达式操作索引列：
-- 索引失效：对索引列使用了函数
SELECT * FROM user WHERE YEAR(birthday) = 1990;
-- 优化方案：改写为索引列可直接匹配
SELECT * FROM user WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';
2.使用不等于（!=、<>）、NOT IN、IS NOT NULL：
-- 可能导致索引失效
SELECT * FROM user WHERE age != 20;
3.模糊查询以 % 开头：
-- 索引失效：%在开头
SELECT * FROM user WHERE name LIKE '%三';
-- 索引有效：%在结尾
SELECT * FROM user WHERE name LIKE '张%';
4.联合索引不满足最左前缀原则：
-- 联合索引(idx_age_name)，以下查询无法使用索引
SELECT * FROM user WHERE name = '张三';
5.隐式类型转换：
-- 索引失效：字符串字段与数字比较（隐式转换）
SELECT * FROM user WHERE phone = 13800138000;
-- 正确用法：保持类型一致
SELECT * FROM user WHERE phone = '13800138000';
6.OR 连接的条件中存在未建索引的列：
-- 若age有索引但name无索引，整个查询索引失效
SELECT * FROM user WHERE age = 20 OR name = '张三';
四、索引优化实战技巧
1. 合理设计索引数量
- 索引不是越多越好，每张表建议索引数量不超过 5 个
- 频繁更新的表应减少索引（索引会降低写入性能）
- 小表（数据量 < 1 万）无需创建索引（全表扫描更快）
2. 选择合适的索引列
- 优先为 WHERE、JOIN、ORDER BY、GROUP BY 涉及的字段创建索引
- 选择区分度高的字段（如身份证号），避免为性别等低区分度字段建索引
- 避免为大字段（如 TEXT、BLOB）创建索引
3. 联合索引的设计原则
- 将最常用的查询字段放在前面
- 将区分度高的字段放在前面
- 考虑索引覆盖（包含查询所需的所有字段，避免回表）：
-- 若查询只需要id,age,name，可创建联合索引(idx_age_name)
-- 因id是主键，索引叶子节点已包含，实现索引覆盖
SELECT id, age, name FROM user WHERE age = 20;
4. 定期维护索引
- 分析索引使用情况，删除无用索引：
-- 查看索引使用情况（需开启userstat=1）
SELECT * FROM sys.schema_unused_indexes;
- 优化碎片化索引（尤其是频繁删除更新的表）：
-- InnoDB重建索引
ALTER TABLE user ENGINE = InnoDB;
-- 或优化指定索引
ALTER TABLE user OPTIMIZE INDEX idx_user_age;
5. 慢查询分析与索引优化流程
- 开启慢查询日志，定位低效 SQL：
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 执行时间超过1秒的查询
2.使用 EXPLAIN 分析 SQL 执行计划：
EXPLAIN SELECT * FROM user WHERE age = 20 AND name LIKE '张%';
索引是 MySQL 性能优化的 "双刃剑"，合理使用能显著提升查询效率，但过度使用或设计不当会导致写入性能下降和存储空间浪费。
核心原则：
- 理解 B + 树索引的工作原理，尤其是聚簇索引与非聚簇索引的区别
- 遵循最左前缀原则设计联合索引
- 避免索引失效的常见场景
- 结合业务场景和数据特征设计索引，而非盲目创建
- 定期分析和优化索引，保持数据库性能稳定
通过本文的学习，希望你能建立起系统的索引设计思维，在实际开发中写出高效的 SQL 查询。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
