MySQL索引数据存储原理详解

MySQL索引数据存储原理详解

MySQL索引是数据库性能优化的关键技术之一。本文将深入探讨MySQL索引的存储原理，包括索引的分类、存储结构、InnoDB和MyISAM两种存储引擎的对比、数据页结构、行格式、B+Tree索引的原理等多个方面。

索引的分类

从存储结构上划分

• BTree索引（B+tree，B-tree）
• 哈希索引
• FULLINDEX全文索引
• RTree索引

从应用层次上来划分

• 外键索引：InnoDB是MySQL目前唯一支持外键索引的内置引擎
• 普通索引（INDEX）
• 唯一索引（UNIQUE INDEX）
• 主键索引 (PRIMARY KEY)
• 单列索引
• 组合索引（联合索引）
• 全文索引 (FULLTEXT INDEX)
• 空间索引（SPATIAL INDEX）

从表记录的排列顺序和索引的排列顺序是否一致来划分

• 聚簇索引（聚集索引，一级索引）：表记录的排列顺序和索引的排列顺序一致
• 非聚簇索引（非聚集索引，二级索引，普通索引）：表记录的排列顺序和索引的排列顺序不一致

InnoDB和MyISAM存储引擎的对比

• MyISAM：非事务安全，支持全文类型索引，效率较高，适合小型应用
• InnoDB：事务安全，支持行级锁，适合大量insert和update操作

MySQL存储原理

索引页的结构

InnoDB的逻辑存储结构由TableSpace、Segement、Extent和Page组成。Page是InnoDB磁盘管理的最小单位，大小默认为16KB。

InnoDB数据页结构

数据页结构包括File Header、Page Header、Infimum+Supremum、User Records、Free Space、Page Directory和File Trailer。

InnoDB行格式

行格式主要包括Compact、Redundant、Dynamic和Compressed。其中Compact是最常用的行格式，从MySQL 5.1版本开始默认使用。

B+Tree索引的原理

B+Tree结构中，所有数据都存储在叶子节点，非叶子节点只存储索引。这种设计可以提高查询效率，因为每次磁盘IO都可以获取到完整的数据。

索引为何选择B+Tree

B+Tree结构可以有效减少磁盘IO次数，提高查询效率。即使在千万级别的数据表中，B+Tree的树高也相对较低，通常只需要几次磁盘IO就可以获取到数据。

索引实战

使用EXPLAIN关键字可以分析SQL查询的执行计划，帮助优化查询性能。通过EXPLAIN可以查看查询的类型、使用的索引、扫描的行数等信息。

图 4 InnoDB引擎结构示意图

图 5 InnoDB存储引擎数据页结构