一、索引概述

（一）核心定义

索引是 MySQL 中帮助高效获取数据的有序数据结构，数据库系统会维护独立于原始数据的索引结构，通过索引可快速定位数据，核心作用是降低查询的 IO 成本和 CPU 排序成本。

（二）优缺点

优势	劣势
提升数据检索效率，减少磁盘 IO 开销	索引列占用额外存储空间（多列 / 大字段索引占用明显）
辅助数据排序，降低 CPU 排序成本	降低更新操作（INSERT/UPDATE/DELETE）效率（需同步维护索引结构）

二、索引结构

（一）常见索引结构及支持情况

MySQL 支持多种索引结构，默认及主流场景使用 B+tree 索引（未特别说明时，索引均指此类）。

索引结构	核心特点	InnoDB 支持	MyISAM 支持	Memory 支持
B+tree 索引	叶子节点存数据 / 主键，支持范围查询、排序，层级少效率高	是	是	是
Hash 索引	仅支持等值查询（=、IN），不支持范围查询 / 排序，查询效率极高（一次检索）	否（自适应 Hash 除外）	否	是（默认）
R-tree 索引	用于空间数据索引（如地理坐标）	否	否	是
Full-text 索引	用于文本关键词匹配（非精确值对比）	是（5.6+）	是	否

（二）关键结构对比与选择

1. 淘汰结构（缺点明显）

• 二叉树：顺序插入易形成链表，层级深，检索速度慢；
• 红黑树：大数据量下层级仍较深，IO 次数多，效率不足。

2. B+tree 索引（InnoDB 首选）

MySQL 对经典 B+tree 优化后的核心特性：

1. 所有数据仅存储在叶子节点，非叶子节点仅存键值和指针；
2. 叶子节点通过双向链表连接，大幅提升区间查询（如 BETWEEN ... AND ...）性能；
3. 单页存储更多键值，树高更低（百万级数据仅需 3-4 层），IO 效率高。

3. InnoDB 选择 B+tree 的原因

• 相较于二叉树 / 红黑树：层级更少，磁盘 IO 次数少，搜索效率高；
• 相较于 B-tree：非叶子节点不存数据，单页键值密度高，树高更低；
• 相较于 Hash 索引：支持范围查询和排序操作，适配更多业务场景。

三、索引分类

（一）按功能分类

索引类型	核心含义	特点	关键字	示例 SQL
主键索引	基于表主键创建的索引	默认自动创建，一张表仅 1 个	PRIMARY	建表时 id INT PRIMARY KEY（自动生成索引）
唯一索引	保证索引列值唯一（允许 NULL，NULL 可重复）	一张表可多个	UNIQUE	CREATE UNIQUE INDEX idx_phone ON tb_user(phone);
常规索引	无特殊约束，仅用于加速查询	一张表可多个	无（默认）	CREATE INDEX idx_name ON tb_user(name);
全文索引	用于文本关键词匹配（如文章内容检索）	一张表可多个	FULLTEXT	CREATE FULLTEXT INDEX idx_content ON article(content);

（二）InnoDB 按存储形式分类

1. 聚集索引（Clustered Index）

• 核心特点：数据与索引存储在一起，索引叶子节点直接保存完整行数据；
• 唯一性：一张表必须且仅能有 1 个聚集索引；
• 选取规则（优先级从高到低）：
  1. 有主键时，主键索引即为聚集索引；
  2. 无主键时，使用第一个唯一（UNIQUE）索引作为聚集索引；
  3. 无主键和合适唯一索引时，InnoDB 自动生成隐藏 rowid 作为聚集索引。

2. 二级索引（Secondary Index）

• 核心特点：数据与索引分离，索引叶子节点仅保存对应主键值；
• 查询逻辑：通过二级索引找到主键后，需回表查询聚集索引获取完整行数据（即 “回表查询”）。

四、索引核心语法

（一）创建索引

-- 1. 常规索引（单列）
CREATE INDEX idx_user_name ON tb_user(name);
-- 2. 唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
-- 3. 联合索引（多列，按顺序生效）
CREATE INDEX idx_pro_age_sta ON tb_user(profession, age, status);
-- 4. 全文索引
CREATE FULLTEXT INDEX idx_article_content ON article(content);
-- 5. 前缀索引（字符串长字段优化）
CREATE INDEX idx_email_prefix ON tb_user(email(5)); -- 取 email 前 5 个字符建索引

（二）查看索引

SHOW INDEX FROM tb_user; -- 查看 tb_user 表所有索引详情

（三）删除索引

DROP INDEX idx_user_name ON tb_user; -- 删除指定索引

五、SQL 性能分析（索引优化工具）

（一）查看 SQL 执行频率

统计数据库读写操作频次，判断优化重点（如读多则优化索引）：

SHOW GLOBAL STATUS LIKE 'Com_%'; -- Com_select（查询）、Com_insert（插入）等字段

（二）Profile 详情分析（定位耗时环节）

1. 检查是否支持 Profile：SELECT @@have_profiling;（返回 YES 则支持）；
2. 开启 Profile（session 级别）：SET profiling = 1;；
3. 执行目标 SQL 后，查看耗时概览：SHOW PROFILES;（获取 query_id）；
4. 查看指定 SQL 各阶段耗时：SHOW PROFILE FOR QUERY 1;（1 为 query_id）；
5. 查看 CPU/IO 消耗：SHOW PROFILE CPU, IO FOR QUERY 1;。

（三）EXPLAIN 执行计划（核心优化工具）

通过 EXPLAIN 可查看 SQL 索引使用情况、执行逻辑，关键字段含义如下：

字段	核心含义
id	查询序列号：id 相同按从上到下执行；id 不同，值越大越先执行
select_type	查询类型：SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等
type	连接类型（性能从优到差）：NULL > system > const > eq_ref > ref > range > index > all（需优化至 ref 及以上）
possible_keys	可能使用的索引（多个以逗号分隔）
key	实际使用的索引（NULL 表示未使用索引，需排查原因）
key_len	索引使用的字节数，长度越短越好（不损失精确性）
rows	MySQL 估计需扫描的行数（InnoDB 为估计值，越少越好）
filtered	返回结果行数占需读取行数的百分比，值越大越好（过滤效率高）

示例用法

EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age = 25;

六、索引使用规则（避坑 + 优化）

（一）索引失效场景（重点避坑）

1. 违反最左前缀法则：联合索引需从最左列开始查询，跳过中间列则后续列失效。
   • 示例：联合索引 (profession, age, status)，WHERE age = 25 AND status = 1 失效；WHERE profession = '软件' AND status = 1 仅 profession 生效。
2. 范围查询右侧失效：联合索引中，范围查询（>、<、BETWEEN）右侧的列索引失效。
   • 示例：WHERE profession = '软件' AND age > 25 AND status = 1，status 索引失效。
3. 索引列运算操作：索引列做函数、算术运算（如 SUBSTR(name,1,3) = '张'、id + 1 = 10），索引失效。
4. 字符串不加引号：字符串类型索引列查询时，值未加引号（如 WHERE phone = 17799990017），索引失效。
5. 头部模糊匹配：模糊查询中，头部通配符（LIKE '%张'）导致索引失效；尾部匹配（LIKE '张%'）有效。
6. OR 条件单侧无索引：OR 连接的条件中，一侧列有索引、另一侧无索引，则所有索引均失效。
   • 示例：WHERE phone = '17799990017' OR age = 23，若 age 无索引，phone 索引也失效。
7. 数据分布影响：MySQL 评估全表扫描比索引查询更快时（如数据量极少、索引列值重复率极高），会放弃使用索引。

（二）优化技巧

1. SQL 提示：手动指定索引使用策略，强制优化器选择合适索引：

-- 强制使用某索引
SELECT * FROM tb_user FORCE INDEX(idx_pro_age_sta) WHERE profession = '软件';
-- 优先使用某索引
SELECT * FROM tb_user USE INDEX(idx_pro_age_sta) WHERE profession = '软件';
-- 忽略某索引
SELECT * FROM tb_user IGNORE INDEX(idx_name) WHERE profession = '软件';

1. 覆盖索引：查询需返回的列均在索引中（无需回表），减少 SELECT * 使用。
   • 示例：索引 (name, age)，SELECT name, age FROM tb_user WHERE name = '张三' 触发覆盖索引（Extra 显示 Using index）。
2. 前缀索引：字符串长字段（如 email、address）可仅取前 N 个字符建索引，节约空间。
   • 语法：CREATE INDEX idx_email_5 ON tb_user(email(5));
   • 前缀长度选择：通过 “选择性” 判断（选择性 = 不重复索引值数 / 总记录数，越接近 1 越好）：

-- 计算 email 字段整体选择性
SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;
-- 计算 email 前 5 个字符的选择性
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) FROM tb_user;

1. 联合索引优先于单列索引：多条件查询时，联合索引可避免 “索引选择冲突”，且支持覆盖索引。
   • 示例：查询 WHERE phone = '17799990017' AND name = '韩信'，联合索引 (phone, name) 比单列索引 idx_phone+idx_name 更高效（无需优化器选择，直接命中）。

七、索引设计原则

1. 仅对数据量大、查询频繁的表建索引（小表索引收益低，还会增加维护成本）；
2. 优先对查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY） 字段建索引；
3. 选择区分度高的列作为索引（如手机号、身份证号），区分度越高，索引效率越高；
4. 字符串长字段（如 VARCHAR (100)）优先建前缀索引，节约存储空间；
5. 多条件查询优先建联合索引，减少单列索引数量（联合索引可覆盖查询，避免回表）；
6. 控制索引数量（并非越多越好）：索引过多会降低增删改效率，增加磁盘空间占用；
7. 索引列尽量设置 NOT NULL：优化器可更精准判断索引有效性，提升查询效率。

八、核心总结

索引的核心是 “以空间换时间”，需在查询效率和更新效率之间平衡。关键要点：

1. 结构：优先使用 B+tree 索引，适配绝大多数业务场景；
2. 分类：聚集索引（主键优先）效率最高，二级索引需注意回表问题；
3. 使用：避免索引失效场景，多条件查询用联合索引，长字符串用前缀索引；
4. 优化：用 EXPLAIN 分析执行计划，用 Profile 定位耗时环节；
5. 设计：遵循 “按需建索引、少而精” 原则，兼顾查询和更新效率。