11. MySQL索引原理

索引存储结构

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
• MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换
• MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B Tree 和 B+ Tree

1. B Tree图示

B Tree是为了磁盘或其他存储设备而设计的一种多叉平衡查找树。

• B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
• 如果是三层树结构—支撑的数据可以达到20G，如果是四层树结构—支撑的数据可以达到几十T

2. B Tree和 B+ Tree的区别

B树和B+树的最大区别在于非叶子节点是否存储数据的问题。

- B树是非叶子节点和叶子节点都会存储数据。

- B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。 

非聚集索引(MyISAM)

• B+树叶子节点只会存储数据行（数据文件）的指针，简单来说数据和索引不在一起，就是非聚集索引。
• 非聚集索引包含主键索引和辅助索引都会存储指针的值

1. 主键索引

MyISAM的索引文件仅仅保存数据记录的地址

​ 索引文件中，主键后面跟着数据记录的地址，比方说要查询主键为15的记录，根据15查询到指针地址0x07，然后再去地址0x07中查询记录。

2. 辅助索引（次要索引）

在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。

同样也是一棵B+Tree,data 域保存数据记录的地址。

MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

• 主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引。
• 辅助索引只会存储主键值
• 如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

1. 主键索引

InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数 据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

InnoDB 主键索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。

根据主键查询时，则可以一次性的获取所有的数据。

2. 辅助索引（次要索引）

与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键, 然后用主键到主索引中检索获得记录。

select * from user where name='Alice'