MySQL索引篇（三）

                                     MySQL索引篇（三）

索引原理分析

索引存储结构

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

BTREE和B+TREE数据结构

数据结构示例网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

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

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

BTREE和B+TREE的区别

• BTREE非叶子节点和叶子节点都会存储数据。
• B+TREE只有叶子节点会存储数据，而且存储的数据都在一行上，并且这些数据都有指针指向，是有序的。

非聚集索引（MyISAM）

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

主键索引

这里假设一个表一共有三列，以Col1为主键，上图是一个MyISAM表的主键索引示意图；可以看出MyISAM的索引文件仅仅保存数据记录地址。

辅助索引（次要索引）

在MyISAM中主键索引和辅助索引（Secondary key）在结构上买有任何区别，只是主键索引要求key是唯一的，而辅助索引的key是可以重复的。如果在Col2上建立一个辅助索引，索引结构示意图如下：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引；如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应数据记录。

聚集索引（InnoDB）

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

主键索引

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

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

辅助索引（次要索引）

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

 聚集索引的实现方式使得按照主键搜索十分高效，但是辅助索引需要检索两边索引，首先检索辅助索引获得主键，然后用主键到主键索引中获得数据记录。