MySQL索引原理揭秘
MySQL 索引是数据库高效查询的核心机制，其原理基于特定的数据结构（主要是 B+Tree）和数据库引擎（如 InnoDB）的实现策略。索引本质上是一种空间换时间的策略，虽然会占用额外存储空间，但能极大提升查询速度。
B+树的精妙设计
B+树是B树的升级版，具有几个关键特点：所有数据都存储在叶子节点，非叶子节点只存储键值；叶子节点通过指针连接形成链表；树的高度通常维持在3-4层。
这种设计带来巨大优势：范围查询效率极高，因为只需找到起始点就能顺着链表扫描；查询稳定性好，任何查询都需要相同次数的IO；更适合磁盘存储，节点大小通常设置为磁盘页大小(16KB)的整数倍。
InnoDB 引擎索引的关键实现策略
聚集索引决定了表中数据的物理存储顺序，一张表只能有一个聚集索引。其主要结构特点是：
- 叶子节点存储的是完整的数据行（不仅仅是主键值）。因此，通过聚集索引查找数据非常直接高效（一次 B+Tree 查找即可拿到完整行数据）。
- 非叶子节点存储索引键值（主键或其他被选为聚集索引的列值）和指向子节点（页）的指针
InnoDB中主键就是聚集索引。如果没有主键，InnoDB会选择一个唯一非空列，都没有则会隐式创建一个自增列。
非聚集索引(二级索引)存储的是主键值而非数据指针
其主要结构特点:
- 叶子节点存储的不是完整的数据行，而是该索引的键值 + 对应行的聚集索引键值（主键值）。
- 非叶子节点存储索引键值和指向子节点的指针（同 B+Tree 结构）。
查询时需要先查索引再回表查主键。回表过程:
- 在二级索引的 B+Tree 中查找，定位到目标记录的叶子节点。
- 从该叶子节点中读取记录的聚集索引键值（主键值）。
- 拿着这个主键值，回到聚集索引的 B+Tree 中进行一次查找（称为回表 - Bookmark Lookup）。
- 在聚集索引的叶子节点中找到该主键对应的完整数据行
二级索引虽然需要回表操作，但在特定场景下非常高效。例如用户表有(user_id,username)两个字段，如果只为username建索引，查询"SELECT user_id FROM users WHERE username='张三'"时，索引覆盖了查询字段，无需回表。
覆盖索引是指索引包含了查询需要的所有字段，那么 MySQL 无需回表就能直接从二级索引的叶子节点中获取到所需数据，是性能优化的银弹。设计表结构时应考虑常用查询模式，有意识地创建覆盖索引。
EXPLAIN：SQL性能分析的X光机
EXPLAIN命令能显示MySQL如何执行查询，关键列包括：
type：从优到差依次为system > const > eq_ref > ref > range > index > ALL
key：实际使用的索引
key len: 实际使用索引的长度，辅助判断索引是否全部使用上
rows：预估需要检查的行数
Extra：额外信息如"Using index"表示使用了覆盖索引，"Using index condition" 表示使用了索引下推(mysql5.6以后)，即mysql在存储引擎层完成过滤，而不需要在server层二次处理。
分析慢查询时，先EXPLAIN看执行计划，重点关注是否使用了合适索引，避免出现"ALL"(全表扫描)这种性能杀手，最少应该达到range级别。
索引优化实战案例
以mysql官方测试数据(从mysql测试数据官方获取,按官方文档导入数据) 我们设计几个sql优化场景。
使用的是mysql 5.7版本。
创建索引:
ALTER TABLE employees ADD INDEX idx_last_name_hire_date (last_name, hire_date);
- 全值匹配：
EXPLAIN
SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d')
索引完全匹配是最理想情况，type通常为ref。
- 使用覆盖索引:
EXPLAIN
SELECT last_name,hire_date FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d');
仅查询索引字段，避免回表，与全值匹配不同的是Extra 为Using index(覆盖索引)
- 最左前缀原则：联合索引从左至右不能断，跨过联合索引的开头会导致索引失效
EXPLAIN
SELECT * FROM employees WHERE hire_date = STR_TO_DATE('1986-06-26', '%Y-%m-%d')
跨过last_name，导致索引失效，type变为ALL, 全表扫描。
Extra 显示 Using where 说明MySQL在存储引擎检索数据后，还需在服务器层进行二次过滤，需要避免。
- 范围查询会导致之后的索引列失效(5.6之前)：
mysql5.6之后的版本，联合索引的中间列使用范围查询时，确实可通过索引下推技术使之后的索引列仍被高效使用。例如：
ALTER TABLE employees ADD INDEX idx_last_name_hire_date_gen (last_name, hire_date,gender);
EXPLAIN
SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date = STR_TO_DATE('1986-01-01', '%Y-%m-%d') AND gender='M';
EXPLAIN
SELECT * FROM employees WHERE last_name = 'Facello' AND hire_date between STR_TO_DATE('1986-01-01', '%Y-%m-%d') AND STR_TO_DATE('1992-01-01', '%Y-%m-%d') AND gender='M';
后者与前者索引列的长度都是54，后者Extra 显示 Using index condition 说明索引下推技术起了作用。
- 函数操作破坏索引：
EXPLAIN
SELECT * FROM employees WHERE left(last_name,4) = 'Face'
函数的使用导致索引失效，全表扫描,尽量避免对索引列进行计算或者使用函数
可以使用范围查询代替，比如:
EXPLAIN
SELECT * FROM employees WHERE last_name like 'Face%'
使用范围查询则使用了索引
- 不等以及null会使索引失效，or因为索引下推依旧可以有效的使用索引列
EXPLAIN
SELECT * FROM employees WHERE last_name != 'Facello';
EXPLAIN
SELECT * FROM employees WHERE last_name IS NOT NULL;
两者都导致索引失效
可以看到有可能使用的索引(possible keys),可以使用 force index进行优化
EXPLAIN
SELECT * FROM employees force index (idx_last_name_hire_date_gen) WHERE last_name != 'Facello'
OR查询时，索引下推技术的作用
EXPLAIN
SELECT * FROM employees WHERE last_name ='Facello' OR last_name ='Bill';
索引优化黄金法则
不要过度索引：每个索引都会增加写操作成本，通常一张表不超过5个索引为宜。索引不是越多越好，而是要为关键查询路径精心设计。理解业务查询模式，针对性创建组合索引，才能发挥索引的最大威力。