深入解析mysql中的索引（原理详解）
最新回答
弃殇
2022-11-29 20:15:22
MySQL索引是帮助数据库高效获取数据的排好序的数据结构，其核心原理基于B+树实现高效查询与存储管理。
一、索引的核心数据结构：B+树的优势
MySQL选择B+树作为索引结构而非其他树形（如二叉树、红黑树、B树），主要因其具备以下特性：
- 非叶子节点仅存储索引键：不存储实际数据，仅作为索引的冗余存储，使得单个节点可容纳更多键值，降低树的高度。例如，16KB的页中，若键值（8字节bigint主键）加指针（6字节）共14字节，则单个非叶子节点可存储约1170个指针，显著提升扇出能力。
- 叶子节点存储完整数据或主键：聚簇索引的叶子节点直接包含数据行，非聚簇索引（二级索引）存储主键值，通过“回表”操作获取完整数据。
- 叶子节点链表连接：所有叶子节点通过指针形成有序链表，支持高效的范围查询（如BETWEEN、>等），而B树需中序遍历子树，效率较低。
- 高度可控性：B+树通过减少非叶子节点数据存储，使树高度通常为1-3层。以高度为3的B+树为例，可存储约2190万条数据（1170×1170×16），查询仅需1-3次I/O操作。
二、索引类型与存储引擎实现
聚簇索引（Clustered Index）：
InnoDB：表数据按主键构建的B+树存储，叶子节点直接包含完整数据行。若未显式定义主键，InnoDB会选择唯一非空列或生成隐藏的6字节ROWID作为索引。
MyISAM：不支持聚簇索引，索引文件（.MYI）与数据文件（.MYD）分离，查询需先定位索引再读取数据，效率低于InnoDB。
二级索引（Secondary Index）：
非主键列的索引，叶子节点存储索引列值与主键值。查询时需先通过二级索引找到主键，再通过聚簇索引获取数据（回表）。例如，查询name='Alice'时，若name为二级索引，需两次B+树查找。
联合索引：
多列组合的B+树索引，排序规则为先按第一列排序，相同则按第二列排序，依此类推。例如，索引(a,b)可优化查询WHERE a=1 AND b=2，但无法优化WHERE b=2（索引失效）。
三、索引失效的常见场景
- 违反最左前缀原则：联合索引未从左到右匹配，如索引(a,b,c)，查询条件为WHERE b=1或WHERE a=1 AND c=2时，索引仅部分生效或失效。
- 范围查询后的列失效：联合索引中，范围查询（如>、LIKE）后的列无法使用索引。例如，索引(a,b,c)，查询WHERE a=1 AND b>2 AND c=3时，c列索引失效。
- 隐式类型转换：如索引列为字符串类型，但查询条件使用数字，导致索引失效。例如，varchar类型的id列，查询WHERE id=123（未加引号）会触发类型转换。
- 使用函数或运算：对索引列使用函数（如UPPER(name)）或运算（如age+1=10）会导致索引失效。
- OR条件未全为索引列：若OR条件中存在非索引列，则整个查询无法使用索引。例如，索引(a)，查询WHERE a=1 OR b=2时，索引仅用于a=1部分。
四、索引设计建议
- 优先使用整型自增主键：整型比较速度快，自增特性减少B+树节点分裂，提升插入性能。UUID作为主键会导致随机写入，增加页分裂概率。
- 控制索引列数量：单表索引建议不超过5个，避免索引维护开销。
- 覆盖索引优化：通过二级索引直接获取所需列，避免回表。例如，查询SELECT id,name FROM user WHERE name='Alice'，若name为二级索引且包含id，则无需回表。
- 定期分析索引使用情况：通过EXPLAIN分析查询执行计划，或使用SHOW INDEX FROM table_name查看索引冗余情况，及时删除未使用的索引。
热门标签
