本文共 2059 字，大约阅读时间需要 6 分钟。
MySQL索引技秘谱：从基础到进阶深入解析
1. 索引的概念
索引可以被比作一本书的目录，能够快速定位书中的具体内容。在数据库查询中，索引起到了关键的作用。它存储在磁盘上，占用物理空间，能够显著提升查询效率。但过多的索引会对数据库的插入、更新操作产生负面影响。
2. MySQL索引类型
MySQL索引不仅仅是简单的二叉树，它支持多种数据结构和逻辑维度的组合。
2.1 数据结构维度
- B+树索引：所有数据存储在叶子节点，复杂度为O(log n)，适合范围查询。
- 哈希索引：适合等值查询，效率高，一次到位。
- 全文索引：支持在
char、text、varchar类型上创建，用于文本内容的快速检索。 - R-Tree索引：用于GIS数据类型的SPATIAL索引。
2.2 物理存储维度
- 聚集索引：以主键创建，叶子节点存储表数据（InnoDB存储引擎）。
- 非聚集索引：以非主键创建，叶子节点存储主键和索引列（InnoDB存储引擎）。
2.3 逻辑维度
- 主键索引：不允许空值，唯一性强。
- 普通索引：允许空值和重复值。
- 联合索引：多字段组合索引，遵循最左前缀原则。
- 唯一索引：列值唯一但允许空值。
- 空间索引：支持OpenGIS几何数据模型，用于空间数据检索。
3. 索引失效场景
索引在特定条件下可能失效，导致全表扫描，影响查询性能。常见失效原因包括：
- 查询条件中包含
OR。 - 字段类型为字符串且未使用引号。
- 使用
LIKE通配符。 - 联合索引查询条件不在索引列的最左端。
- 索引字段上使用内置函数或运算符。
- 索引字段上使用
!=、<>、IN等条件。 - 索引字段上使用
IS NULL或IS NOT NULL。 - 左连接或右连接查询字段编码不一致。
- MySQL估计全表扫描比索引查询更高效。
4. 不适合建立索引的场景
- 数据量少的表。
- 更新频繁的字段。
- 区分度低的字段（如性别）。
- where、group by、order by等后未使用的字段。
- 已有冗余索引的情况。
5. 为什么选择B+树而不是其他树结构？
B+树的优势体现在以下几个方面：
5.1 为什么不是一般二叉树？
- 二叉树在磁盘环境下效率较低，容易变成链表形式，导致全表扫描。
- 平衡二叉树虽然效率较高，但其每个节点存储的数据量有限，磁盘读取次数较多。
5.2 为什么选择B+树而不是平衡二叉树？
- B+树的非叶子节点仅存储键值，不存储数据，能够降低树的高度，减少磁盘读取次数。
5.3 为什么选择B+树而不是B树？
- B+树的所有数据都存储在叶子节点，数据按顺序排列，形成链表结构。这种设计使得范围查询、排序查询等操作显著高效。
6. B+树索引树的查询过程
以select * from employee where age=32为例，实际查询过程涉及以下步骤：
加载idx_age索引树的磁盘块。 根据索引树结构，逐步缩小搜索范围。 找到符合条件的叶子节点，获取主键值。 回到主键索引树，通过主键值定位具体数据行。 7. 回表的概念与减少方法
回表是指在索引树中未找到匹配数据时，需要回到主键索引树获取主键值，再定位具体数据行。
7.1 减少回表的方法
- 尽量使用覆盖索引。
- 在索引列上避免使用函数或运算符。
- 确保联合索引的最左列是主键或唯一索引。
8. 覆盖索引
覆盖索引是指查询的数据列全部存在于索引树的叶子节点上。例如，select id, age from employee where age=32不需要回表，因为所需数据已在索引树中。
9. 索引的最左前缀原则
联合索引的最左列是关键，索引树会优先遍历最左列的值。例如，联合索引(a, b, c)实际上包含了单独的a、(a, b)等多个索引。
10. 索引下推优化
索引下推优化是MySQL5.6新增的功能，允许索引树在遍历过程中直接筛选满足条件的记录，减少回表次数。
11. 大表添加索引的方法
- 创建新表并复制原表数据。
- 在新表上添加所需索引。
- 替换原表的数据和名称。
12. 检查索引命中情况
使用EXPLAIN命令查看查询执行计划，关注key字段，判断是否命中索引。
12.1 关键字段解读
- type：连接类型，性能从好到坏依次为
const、eq_ref、ref等。 - rows：估算的匹配行数。
- filtered：满足条件的记录百分比。
- extra：包含额外信息，如
Using index、Using where等。
13. Hash索引与B+树的区别
- B+树支持范围查询、联合索引和模糊查询。
- Hash索引效率在等值查询上更高，但不支持范围查询和模糊查询。
14. 索引的优缺点
优点
缺点
- 索引占用额外空间。
- 维护索引会增加开销。
- 索引可能影响插入、更新性能。
15. 聚簇索引与非聚簇索引
聚簇索引
- 数据和索引存储在同一结构中。
- 叶子节点存储完整的数据记录。
- 适合高频查询场景。
非聚簇索引
- 数据和索引存储分开。
- 需要回表获取数据。
- 适用于对查询性能要求不高的场景。
转载地址：http://pbbfk.baihongyu.com/