Mysql 索引结构 B+树

1.索引的官方定义

索引是一种能帮助数据库快速高效地获取数据的一种排好序的数据结构

2.索引的数据结构

• 二叉树（最坏情况下效率低）
• 红黑树（树的高度不好控制，数据量大时层数太大）
• Hash表（等值的查找效率高，但是范围查找以及排序效率低）
• B-Tree（所有节点均存数据，查找时间不稳定，树的高度变化大）
• B+Tree（B-Tree的变种，只有叶子节点存放数据，每个中间节点存放的索引多，树的高度稳定，查找时间稳定，叶子节点之间使用指针双向相连，方便范围查找）

3.B+Tree

目前应用最广泛的索引数据结构
点击此处进入B+Tree的直观展示图

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子结点包含所有的索引字段
• 叶子结点之间用指针双向连接，可以提高范围查找的效率

B+Tree示例图

4.B+Tree可以索引的数据个数的探讨

首先查询数据库定义的节点大小这里以查到的16kb为例

SHOW GLOBAL STATUS like 'Innodb_page_size';

当高度为3时：（以B+Tree示例图为例）
每个节点最多可以存放的索引个数是
16384/(8+6)=1170
8 指的是Integer类型主键所占的字节数（视实际主键的类型而定这里只是假设）
6 指的是一个指针所占的字节数（从MySql源码中可以读到）

那么非叶子节点的数量的最大值就是1170*1170
高度为3的B+Tree叶子节点数量最大值（也就是可以索引的最大数据量）就是 1170 * 1170 * （16384/(8+data的大小)）

5.存储引擎，MyISAM和InnoDB

1. 所谓数据库存储引擎，都是形容表的
   存储引擎都是表级别的，设计表的时候可以选择。
   

2. 不同存储引擎组织表结构和表数据的存储方式不一样
   在数据库安装目录的data文件夹下可以找到各个库以及表的存储文件

   • InnoDB，以索引来组织数据存储结构，索引和数据组织在一起
     .frm文件：表的定义信息
     .ibd文件：表的数据+索引信息

   • MyISAM，索引和数据分开存放，索引里面存放对应行的物理地址
     .frm文件：表的定义信息
     .MYD文件：表的数据信息
     .MYI文件：表的索引信息

3. InnoDB索引原理

   • 表数据文件本身就是按B+Tree组织的一个索引结构文件

   • 主键索引是聚集索引（聚簇索引）

   • 主键索引叶子节点包含了完整的一条数据记录，非主键索引叶子结点包含主键（非主键索引/二级索引 是非聚集索引）

   • 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
     如果表设计时没有加入主键，InnoDB会在后台生成一个主键，方便B+Tree维护，自增整型主键在insert数据时方便维护索引，不会对已有的索引结构造成重大破坏或者重构。整型比较大小的效率高。

   • 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

   • 回表/回行，对非主键索引而言，查找到的叶子节点是存放主键id的，需要再次查找主键索引找到对应数据
     

4. MyISAM索引原理（非聚集索引）

   • 主键索引是非聚集索引
   • 主键索引和非主键索引的叶子节点只存放数据在物理磁盘上的指针
   • 索引文件和数据文件分离