Mysql索引底层原理

一、什么是索引

1.1、索引是帮助MySQL高效获取数据的排好序的数据结构

1.2、我们为什么需要使用索引，我们看下图是一张数据库表，最左边的每条数据的物理地址，Col1和Col2是字段，如果我们查找Clo2=89，没有任何索引优化的情况，我们就需要在磁盘里一条一条的读取出来去内存比较那条数据是89，不要认为这些数据都在磁盘上相互紧密一起的，举个例子，今天你在A表插入了一条数据，连续几天都在B表、C表插入数据，又在A表插入第二条数据，现在肯定不是紧密相连的了，都在磁盘上分散存储的，这种情况我一条一条的从磁盘拿取到内存，频繁的读取磁盘IO，性能损耗太大了，就需要使用索引排序做优化。

1.3、索引有哪几种，索引主要是二叉树、红黑树、B-Tree树、B+Tree树

1.4、索引结构的网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二、二叉树索引结构

打开索引结构的网站选择下图

这就是画好的二叉树结构

二叉树是一个key-value的结构，Col2字段建立二叉树索引，key就是Col2字段的值，value就是物理地址，比方说我查询006，我要查询6次，在通过物理地址去磁盘定位到具体的数据，查询的6次也是在磁盘的上做的交互，最后一次才把他取出来放到内存里，这样就是全表扫描。

三、红黑树结构

打开索引结构的网站选择红黑树结构画图

下图就是画好的红黑树数据结构也叫二叉平衡树结构，遵循的原则是左小右大，左边的数据小，右边的数据大，比如我们定位一下006那个值，我用红黑树定位3次就OK了，但是如果上千万的数据，这棵树得多高啊，这样不断在磁盘查询，效率低下得很。

四、B-Tree树

我们通过二叉树和红黑树可以认识到一点，要想查询得快就必须要树的高度低，B-Tree树就出现了，我们来看下图这张B-Tree树，B-Tree树是由主键或者uuid构成的，比方说15是主键，data是物理地址，B-Tree树也是在磁盘中的，它分配了一个大的空间，来存储数据页的节点，什么叫数据页的节点，红黑树和二叉树都是数据节点，数据页节点就是在一个较大的空间里面存储很多个数据节点，这样的话树的高度就会低了，一个数据页节点的大小是16kb,一般data分配1kb，一个B-Tree树的数据页可以存储16个索引元素，每个元素旁边是下一个数据页的存储地址，比如15和56中间那块空间就对应了他们之间的数据页地址，根据这个地址找到下一个存储空间，这么来算的话，一张千万级的数据量的表，B-tree树的高度远远小于红黑树和二叉树，B树的数据是从小到大从左往右排列的，查找也更加的方便,同事也具备左大右小红黑树的优势，比方56那个节点，左边都是小于56的，右边都是大于56的

五、B+Tree树

mysql真正选择的数据结构就是B+Tree树，B+Tree树可以理解为B-Tree树的升级版，看下图，这是一个三层的B+Tree树，第一层和第二层是非叶子节点没有data只存储索引，第三层是叶子节点存放索引和data，这个data不是物理地址而是数据库表里的整行数据，为了构建这个B+树，用了非叶子节点用了冗余索引，比方下图的15，15对应的数据页节点开头就是15，这样主要是构建出这样的结构方便定位查询，<------>是双向指针的意思，这意味着他们是相连接的，指针连接代表他们互相存储了各自地址，B+Tree树按照数据页节点的方式分配，每个数据页节点大小为16kb，如果按照bingint方式建立索引，数据页一个索引是8个字节，旁边是下一个数据页的存储空间地址大概是6个字节，(16x1024)/(8+6)=1170个索引，一个数据页的叶子节点也是16kb，data存储的是整行数据，最多分配1kb左右，三棵树全部满载的情况下1170x1170x16=21902400,三颗树的高度可以放两千多万的数据，这也就是mysql为什么选择B+Tree树，B+Tree树是怎么查询的，首先会通过主键索引建立起树，比方说我查询30这个数据，会把第一个根节点的数据页放到内存里面去，通过二分查询法定位到30所在的空间地址，通过这个地址找到下一个数据页，再把这个数据页拿出来去内存张做比较定位到空间地址，再把这个空间地址定位到30哪里，data是存储整行数据，然后直接取出来，如果是>30，我定位到30那个地方，他是双向指针我直接定位到下一个指针地址依次内推，大于30的全部拿出来，

五、聚集索引与非聚集索引

在介绍聚集索引与非聚集索引和非聚集索引之前，先要谈谈存储引擎，MySQL5.5之前使用的是MyISAM存储引擎，5.5之后使用的是InnoDB存储引擎，MyISAM存储引擎是非聚集索引,InnoDB存储引擎是聚集索引

MyISAM存储引擎

frm:数据库表结构的文件
MYD：表数据的文件
MYI：存储的是索引树

从上图就可以看出来，索引树和数据是分开的，下面我们再来看看MyISAM存储引擎是怎么查询的，我们可以看到下图的一张Col表，现在查询一条sql，select * from col where Col1 = 30,首先在MYI文件会构建好一颗索引树(下图上半部分)，定位到30这里，通过30的物理地址跨文件去MYD里找这条记录的数据。

InnoDB存储引擎
frm:数据库表的结构
ibd:就是索引和数据一起存储起来的

看下图在左边那张，在第三层叶子节点上多出了两行灰色的，就是数据库表的信息，而我们的索引是主键，主键索引对应每行的记录，索引与数据库表信息绑定在了一起就叫聚集索引，一张数据库表只能有一个聚集索引，依旧是select * from col where Col1 = 30，会去索引树定位好，定位到叶子节点的30的位置，直接把数据拿出来，而MyISAM的非聚集索引需要通过跨文件，效率肯定不如聚集索引，右边这张图是普通索引也可以叫二级索引，比如一个nam字段建立的索引就是右边的，字符串会通过ASCII编码比较大小，普通索引的叶子节点并不是存储的整行数据，而是主键，比如一个where条件name=“张三”，定位到叶子节点找到张三的id，再通过id回表去聚集索引里面查询。

InnoDB存储引擎存储的主键
InnoDB存储引擎存储的主键最好使用整型，而且主键必须加上，我们通过上面已经可以指定B+Tree树是通过从小到大横着排列的，整型比较好比较大小排列，而uuid字符串需要通过ASCII码来比较大小，效率肯定是整型强。为什么必须加上主键，通过上面的学习我们也可以指定，聚集索引是由主键自动构建的，如果没有加主键，B+Tree树会在数据库选择一列各自不相同的列来当主键，如果没找到的话就会自动就一列散列用uuid的方式拼出来当主键使用用于构建B+Tree树

六、联合索引的结构

上图就是联合索引的B+Tree树结构，name、age、position三个字段做一个联合索引，这里我们再来理解一下索引是排好序的这句话，我们都知道联合索引有一个最佳左前缀法则，必须从左边开始才会有效，不能越级从中间或者最后，这就是因为排好序这三个字，在上图是name age position从上到下排序的，异味着排序规则是nam第一、age第二、position第三，先会通过ASCII码比较name的大小，把name比较出来了，如果name只有一个那就不用再比较后面两个了，如果name的有多个继续age的比较，age在比出来有多个在比较position，如果我跳过name直接比较age或者position，排序的是name开始的，根本没有排序就是全表扫描