MySQL之（一）索引及其原理

索引的本质

索引是帮助Mysql高效获取数据的排好序的数据结构，其作用是相当于书本的目录，为了快速查找内容。

索引的数据结构

1. 二叉树（当节点索引元素是连续的，会退化成链表，不会减少查找次数，索引失效，不适合做数据结构）
   
2. 红黑树 （层级太多，由于树的广度不高，高度很高，所以同时查询的次数较多，不适合做索引）
   
3. Hash表（根据索引的值哈希生成序列，这个序列对应磁盘文件地址的指针有一个映射关系，所以这也是mysql支持的数据结构。但是如果要查询某个区间值，这种数据结构就不支持了，只支持精确查找）
4. B-Tree（叶子节点具有相同的深度，叶子节点的指针为空，所有的元素不重复，节点中的数据索引从左到右递增排列）
   
5. B+Tree（非叶子节点不存储data，只存储索引，因此可以存更多的索引。叶子节点包含所有索引字段。叶子节点用指针连接，提高区间的访问性能，区间查询，Mysql就是使用了这种数据结构）Mysql为每一个节点都分配了内存空间（16KB）。假设一个字段索引为8B，存储下一个节点地址的内存空间为6B，那么一个节点就可以存储1170个索引，假设叶子节点存储的data有1k，那么一个根节点下的叶子节点就有16个，那么3级的B+Tree就可以存储1170117016个索引元素。
   

索引原理实践

下面我们用mysql的配置查看两种存储引擎的索引

MyISAM存储引擎

1. frm 存储表结构
2. MYD 存储表数据
3. MYI 存储索引

查找过程

假设我们查找49的这个索引，首先通过B+Tree找到索引值为49的叶子节点，叶子节点存储的是磁盘文件地址指针，就会去myd这个文件找这个地址上的内容。

InnoDB存储引擎

跟myisam不同，这是两个文件，一个frm表结构文件，一个ibx文件

跟MyISAM不同的是，B+Tree的叶子节点的data不在是磁盘文件地址的指针而是这一行其他字段的内容（这就是聚集索引，叶子节点包含了完整的数据记录）。myISAM就是非聚集索引。

面试题

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
因为InnoDB只有一个ibd文件，这个文件必须有一个b+tree组织的索引结构文件，没有这个树行不通，如果你不加一个字段为主键，那么mysql就会自己找一个不存在重复数据的字段作为主键，如果都找不到，就会自己加一个row_id的字段作为主键。所以必须建表的时候自己设置主键。设置整型是因为在B+tree对比节点的过程中，简单对比，可以快速找到叶子节点。主键自增是因为防止B+tree节点分裂又做平衡，比如先前插入8，后插入7，就会引起节点分裂

联合索引的底层存储结构

查询原则

1. 根据最左匹配原则，先从第一个索引值开始查询，如果第一个相同再比较第二个，第二个相同再比较第三个。通常情况下，字段用的越多的越靠左

联合索引最左前缀原理

上述sql并没有用到索引，这是因为在B+Tree树中，mysql找不到第一个查找的索引值，最终还是会导致全表扫描。

一下sql 只要查询了第一个字段，都会走索引，但是走的都不全