索引 - 爱编程的小杰

一、索引概述

1. 索引的本质与作用

索引是帮助MySQL高效获取数据的有序数据结构。它通过在数据之外维护特定的数据结构（如B+Tree、Hash等），以引用（指针）的形式指向数据行，从而实现快速查找。例如，当执行SELECT * FROM students WHERE name = 'BETTY';时，无索引需要全表扫描，而有索引则可通过数据结构直接定位目标数据，大幅减少IO操作。

2. 索引的优缺点

优势	劣势
提升数据检索效率，降低IO成本	索引列占用额外存储空间
优化数据排序与分组性能，减少CPU消耗	INSERT/UPDATE/DELETE操作性能可能下降

二、索引结构

MySQL的索引结构由存储引擎实现，常见类型如下：

1. B+Tree索引（最常用）

特点：
- 非叶子节点存储键值，叶子节点存储数据和相邻节点指针，形成有序链表，支持范围查询。
- InnoDB、MyISAM、Memory引擎均支持。
与B-Tree的区别：
- B+Tree的叶子节点包含全部数据，非叶子节点仅用于索引，相同数据量下层级更少，查询效率更高。
- 示例：4阶B+Tree通过层级划分，可快速定位键值范围，适用于范围查询和排序。

2. Hash索引

特点：
- 通过哈希算法将键值映射到槽位，查询效率高（通常一次检索），但仅支持等值查询（=、IN），不支持范围查询。
- Memory引擎默认使用，InnoDB支持自适应Hash（根据B+Tree自动构建）。
局限性：存在哈希冲突，需通过链表解决；无法用于排序和范围过滤。

3. 其他索引结构

R-tree（空间索引）：MyISAM专用，用于地理空间数据，使用较少。
Full-text（全文索引）：倒排索引结构，适用于文本搜索，InnoDB 5.6+和MyISAM支持。

存储引擎与索引结构支持

索引结构	InnoDB	MyISAM	Memory
B+Tree	✔️	✔️	✔️
Hash	自适应支持	❌	✔️
R-tree	❌	✔️	❌
Full-text	5.6+支持	✔️	❌

三、索引分类

1. 逻辑分类

分类	含义	特点	关键字
主键索引	基于表主键创建	自动生成，唯一	PRIMARY
唯一索引	确保列值唯一	可多个	UNIQUE
常规索引	加速普通查询	可多个	-
全文索引	文本关键词搜索	可多个	FULLTEXT

2. 物理分类（InnoDB专用）

聚集索引（Clustered Index）：
- 数据与索引合并存储，叶子节点包含行数据。
- 选取规则：优先主键，其次唯一索引，否则自动生成rowid。
二级索引（Secondary Index）：
- 数据与索引分离，叶子节点存储主键值，查询需“回表”（通过主键查完整数据）。
- 示例：查询name='Arm'时，先通过二级索引找到主键，再回表获取其他字段。

四、索引语法

1. 创建索引

-- 常规索引
CREATE INDEX idx_user_name ON tb_user(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
-- 联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);
-- 全文索引（InnoDB 5.6+）
CREATE FULLTEXT INDEX idx_user_email ON tb_user(email);

2. 查看与删除索引

-- 查看表索引
SHOW INDEX FROM tb_user;
-- 删除索引
DROP INDEX idx_user_name ON tb_user;

五、SQL性能分析

1. 执行频率统计

通过SHOW GLOBAL STATUS LIKE 'Com_______';查看INSERT/UPDATE/DELETE/SELECT的执行次数，定位高频操作。

2. 慢查询日志

开启配置（修改/etc/my.cnf），并重启MySQL服务：

slow_query_log=1          # 开启慢日志
long_query_time=2         # 慢查询阈值（秒）

日志路径：默认/var/lib/mysql/localhost-slow.log，记录超时SQL。

3. Profile分析

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT  @@have_profiling ;

开启Profile：SET profiling = 1;
查看耗时：

SHOW PROFILES;             # 查看所有SQL耗时
SHOW PROFILE FOR QUERY [id]; # 查看指定SQL各阶段耗时

4. Explain执行计划

通过EXPLAIN SELECT ...分析查询性能

重点关注：

type：连接类型（性能从高到低：const > ref > range > all）。
key：实际使用的索引，NULL表示未使用索引。
rows：预估扫描行数，数值越小越好。
Extra： using index condition查找使用了索引，但是需要回表 using where; using index表示覆盖索引，无需回表。

六、索引使用技巧

1. 索引失效场景

最左前缀法则失效：联合索引（如profession, age, status）需从左到右使用，跳过列（如仅用age和status）会导致索引部分失效(后面的字段索引失效)。
联合索引中使用范围查询：联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
索引列运算：对索引列使用函数（如SUBSTRING(phone, 10, 2)）或表达式，索引将失效。
字符串未加引号：WHERE phone = 17799990015（应为'17799990015'）会导致索引失效。
头部模糊查询：LIKE '%工程'或'%工%'会导致索引失效，尾部模糊（'软件%'）不影响。
OR条件：OR两侧字段未全建立索引时，索引可能失效（如id=10 OR age=23，age无索引则全表扫描）。
数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。

2. 优化策略

覆盖索引：查询字段包含在索引中，避免SELECT *。例如，联合索引(profession, age, status)可覆盖SELECT id, profession, age, status ...，减少回表。

SQL提示：强制使用/忽略索引：

use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）
```
  explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
```

ignore index：忽略指定的索引

  explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ：强制使用索引

  explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

前缀索引：对长字符串（如email）取前缀建立索引，减少索引体积：
```
  CREATE INDEX idx_email_prefix ON tb_user(email(5)); -- 取前5个字符
```
联合索引优先于单列索引：多条件查询（如phone+name）使用联合索引，避免多次单索引扫描。

七、索引设计原则

数据量与查询频率：仅对大数据量、高频查询的表建立索引。
核心查询字段：为WHERE、ORDER BY、GROUP BY涉及的字段建立索引。
区分度优先：选择唯一值多的列（如主键），唯一索引区分度最高（1）。
控制索引数量：索引过多会影响写入性能，单表索引建议不超过5个。
避免NULL值：索引列设为NOT NULL，优化器可更高效选择索引。
前缀与联合索引：长字符串用前缀索引，多条件查询用联合索引。

八、总结

索引是MySQL性能优化的核心手段，合理选择索引结构（如B+Tree应对范围查询，Hash应对等值查询）、避免索引失效场景、遵循设计原则，可显著提升查询效率。通过EXPLAIN和慢查询日志分析索引使用情况，结合业务场景动态调整索引策略，是数据库优化的关键。

索引