Mysql专栏（一）索引结构

Mysql中的存储引擎以及索引结构

1. Innodb

在平时使用Mysql的过程中，最经常使用的就是Innodb存储引擎了（因为只有它支持事务）
在Innodb存储引擎中，索引结构有三类，一是B+树（手动建的索引和主键索引都是B+树），二是Hash索引（没错，mysql中是存在hash索引的，只不过是Mysql自己创建出来的，不能进行自行创建），三是全文索引（没错，mysql中也可以创建全文索引，不过不支持中文，分词做的很差，所以一般没人用）。

B+树

Innodb为什么要选用B+树的结构这个已经算是常识问题了，我这里就总结一下，B+树范围查询效率高、B+树相同数据量树的层数低。
其实层数低是最大的优点，层数低意味着磁盘的IO次数也会很少。
如下图所示，Mysql以Page为单位从磁盘上读取数据，一个Page可以容纳1000条数据，每条数据使用1KB的空间。那么在Mysql的聚簇索引中，三层的树就可以容纳1000 X 1000 X 16 = 1600_0000的数据量。

自适应Hash

当开启innodb_adaptive_hash_index自适应Hash时（默认就是开启的），Innodb会自动检测二级索引上的等值查询情况，当等值查询频率过高时，会自动将索引上的部分字段在Mysql内存中建立Hash索引（直接在内存中建，所以性能基本无损耗）。但是过多的Hash索引会显著占用Mysql本来的内存缓存空间，所以可以根据实际业务情况来决定是否开启。

---

我们自己建的索引也有聚簇索引和非聚簇索引之分，这里细讲一下。

1. 聚簇索引
   如果以主键为聚簇索引，则索引中每个节点值为主键值，叶子节点上为具体的主键所在行数据。
2. 非聚簇索引
   非聚簇索引与聚簇索引只有一个区别，非聚簇索引叶子节点上的数据为聚簇索引的索引值，简单来讲，如果根据聚簇索引条件查询，能够直接得到行数据，而根据非聚簇索引条件查询，要先查出主键值，再拿主键值从聚簇索引中查出相应行数据（也就是回表查询），但是要注意，如果你只根据非聚簇索引查询主键值或者索引过的值，就不用回表了。

聚簇索引另一个理解为聚集索引，表明索引和数据是聚集在一起存放的意思。Innodb的表数据是存放在磁盘的idb文件的，而idb文件是用B+树的结构组织起来的，所以Innodb中的每张表必须要有主键。

2. MyisAm

MyisAm存储引擎中只存在非聚簇索引并使用的也是B+树结构，只不过物理上的存储格式会有所不同，所以在MyisAm中不需要有主键约束。
MyisAm最先支持全文检索功能，不过同样的不支持中文。

Mysql中的join方式

emmmm，说实话，这个和索引关联的并不是很密切，但是也有一点，所以就归在这里了。

关于数据库中最简易实现的Join就是Nested loop Join（双层for循环），M * N（M和N为两张表的总行数，下同）的时间复杂度，这个是最基本的暴力法。
Mysql中默认使用的是Block Nested loop join，增加了块缓存来缓存更多的数据减小数据的扫描时间和消耗，但是这种在做业务时不推荐使用，因其本质上依然是M * N。
如果在join的键上存在索引的话，Mysql在扫描过程中也会应用到索引信息，这时就升级成了Index Nested loop Join，比如说以下语句

select * from A a left join B b on a.uid = b.uid

这时，Mysql查询优化器会首先选择驱动表（外层循环的那张表），选择过程如下

1. A表的uid字段有索引，B表的uid字段无索引，则会选用无索引的B表当驱动表
2. 两张表的uid字段都有索引，则会使用表行数小的做为驱动表

由于使用到了索引信息，所以时间复杂度会降低到 M * log(N)，因此如果真有必要的join操作，建立索引是必不可少的。
题外话：在Mysql8.0以后，支持了Hash join的方式，将无索引情况下的时间复杂度降到了M + N。

扩展知识点

1. 为什么Mysql使用聚簇索引+非聚簇索引？当发生数据行移动或者页分裂时，辅助索引树不需要更新，因为辅助索引树存储的是主索引的主键关键字，而不是数据具体的物理地址。
2. 一条sql语句只会使用一个索引。
3. Mysql索引结构为有序树，并使用二分法进行检索，所以全模糊查询、索引列参与计算都不会使用索引树进行查询。
4. Mysql索引树的层数和索引键的长度成正相关，索引键占用空间越大，其索引树高度越大，IO次数越多。
5. 联合索引存储方式
   假设有数据如下所示，其中b、c字段为联合索引index_xxx
   
   则联合索引index_xxx的B+数结构如下所示，因其储存方式所有才有了最左匹配原则