深入理解mysql的索引和数据结构

mysql的B+Tree结构

mysql现在在互联网公司用的非常多，因为它优秀的数据结构，优秀的索引结构，我们都知道不管哪个数据库，它的数据都是持久化到银盘上的，如果说一个数据库，没有任何的索引和数据结构来存储这个数据，那么试想一下，我们要从一批数据中找到想要的一条数据，得做多少工夫，也就是会花费多少磁盘IO，那么带来的开销也是不可估计的；

在mysql的官网有一句话写的是索引是帮助mysql高效获取数据的排好序的数据结构；所有要好好理解这句话，是什么意思呢？就是mysql中的索引结构是帮助mysql将数据库中的数据进行一定的数据结构排序，那么排好序了过后，那么用户通过一定的条件去获取数据就可以从mysql提供的数据结构能够快速的检索到用户想要的数据，我们知道常用的数据结构有二叉树、红黑树，hash表和b-tree，那么mysql使用的是那种数据结构呢？

比如我们有一个表，如下：

 

 

比如要得到col1=6的数据，一般sql为：select * from col where col1=6;

二叉树：二叉树是一个树形结构，它的特点是右边的叶子始终比头结点的数字要小，而右边始终比头结点的数字要大，那么就比如如果mysql使用二叉树，那么如果我要得到col=6的数据，那么是不是至少要循环6次才能找到我想要的数据，但是如果用二叉树呢？上面我已经说了二叉树的特性，所以如果像这种的话，生成的二叉树就是这样：

还是一样的需要6次，而且如果数据量非常大的话，那么这种二叉树的结构也是变的非常大，大的可怕，可能超过10层也是有可能的，所以mysql也不会采用这种二叉树的结构来作为数据的存储。

 

红黑数：那么使用红黑数呢？我们都知道java中的hashmap在java8以后就是红黑数了，红黑树也是一个二叉树，准确点来说它是一颗平衡的红黑树，那么比如要找到第6条数据如果用红黑树呢？

红黑数每次插入新的元素过后会进行平衡，所以这种结构就比二叉树要好一点，要找到col=6，也至少要3次循环才能找到，但是我们思考一个问题就是一个表中的数据可是非常大的，比如上千万的数据量，那么如果用红黑树来存储，这个数有多大，一般查找都是从根节点开始找的，那么这种数据结构显然也不是mysql采用的数据结构。

hash表：

如上襦，hash表示好像很合适，比如hash表中如果有数据插入进来，那么会计算hash得到一个位置，然后进行查询，如果后面插入的数据出现了hash碰撞，那么会在这个位置上建立链表，查询的时候也很快啊，比如查询2的jim，是不是只要得到一个hash然后就可以得到这个链表，然后再循环这个链表就可以得到我们的数据了，但是这个是精确查找，如果我们是范围查找呢？显然hash表也是不太满足的，但是mysql也提供了数据存储的结构，也有hash。

 

B-Tree：

B-Tree就是这种数据结构，叶节点具有相同的深度，叶节点的指针为空，所有索引元素不重复，节点中的数据索引从左到右递增排列；这种数据结构是否就满足了mysql的需求，不管数据量有多大，始终都只有三层，那么检索的效率就很高了，这是原始的B-Tree数据结构，但是我们仔细的想一下一个问题就是如果说你要查询数据id=18的怎么找，因为btree是拍好序的数据结构，是不是就可以在第一层通过折半查找找到15到56的区间，然后在找到第二层，20到49，最后找到我们想要的data数据，但是这里存在两个问题：

1.每一层都带了数据data，那么如果加载内存去找的时候是不是需要更多的内存，如果数据量非常大的情况下是不是就可能是内存不够用；

2.如果我是要范围查找呢？比如大于等于20小于等于40的，那么是不是要找20的要从上到下，找21也要从上到下的去找，范围查找这个就不太满足了。

所以针对这两个问题，mysql设计出了一种新的数据结构就是B+TREE，也就是B+树的数据结构。

 

B+Tree：非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引，叶子节点包含所有索引字段，叶子节点用指针连接，提高区间访问的性能。

注意看B+Tree和B-Tree有什么不同，说白了B+Tree就是B-Tree的变种，它的非叶子节点不存储数据，在叶子节点存储数据，在mysql中，叶子节点存储的数据可能是数据的地址，也有可能是数据本身，也就是

data可能是一个地址，也有可能是真实的数据，这个和mysql的存储引擎有关系myisam和innodb不一样的；

那像这种数据结构，每个节点的都是有序的，如果说我要查找18这个数据，那么是不是可以通过折半查找先在第一层找到15-56这个区间，然后在第二层找到15-20这个区间，最后找到18这个数据，因为第一层和第二层是不存储数的，只是存储索引，所以数据量非常小，mysql应该是将第1、2层的的索引放入了内存，这样查找起来就非常快，而且我们可以看到这种b+tree的数据结构是不是树形只有三层，那么要找到我们需要的数据就浪费的磁盘IO就只有3次，是不是就大大提升了性能，而且每一层都是拍好序的，所以要理解mysql的那句话，排好序的数据结构。

而且如果说我们要范围查找，比如大于18的数据，你看它的数据结构中，叶子节点中也就是第三层数据与数据之间有一个指针的关系的，相互指向的，所以这种要查找范围就非常简单了，先找到第一个满足的条件，然后通过指针移动就可以很块的找到所需要的数据，所以B+Tree是一种变种的B-tree，它的变种特性为：

1.非叶子节点不存储数据；

2.叶子节点之间建立了双向链表关系。

MyISAM存储引擎

在mysql中的数据存储引擎有两种是用的非常多的，一种是myisam，一种是innodb，但是这两种有什么区别吗？我们先来说myisam这种存储引擎，比如我们新建一张表，存储引擎为 myisam

对应的user表的数据文件为：

这三个文件是什么呢?首先frm就是一种表的ddl文件，类似于ddl文件，就是存放了表的创建信息文件，而myd呢就是user表的数据文件，意思就是myiasm类型的db文件，而myi文件就是user表的索引文件，在myi文件中的叶子节点中的数据data就是一个地址，这个地址指向的是myd中的数据位置，大概就是这么个意思。

 

看这个索引文件MYI的文件是不是就很清楚了，其中第三层叶子节点中存储的是一个地址，这个地址指向了myd中的数据对应的位置，所以MYISAM存储引擎是通过索引来找到对应的地址，比如说我要查询18的这个数据，那么就会通过索引的机制找到18的地址，然后去myd文件中找到这条数据返回；像这种存储引擎的数据结构就叫做非聚集的或者叫做非聚簇的数据结构，也就是索引和数据不在一个文件中；一般我们在建表的时候都需要指定一个表的主键，而这个主键一般要是自动增长的integer最好，为什么呢？因为b+tree的数据结构决定了，首先从两个方面， 1.b+tree是一个有序的数据结构，如果你的主键是无序的，那么每次插入都需要移动前面的数据给你插入进去，那么比如你插入新数据，你插入的新数据的主键不是有序的，那么mysql会移动b+tree的数据结构，然后有序的插入进去，这样太浪费插入性能了，如果是字符串类，需要比对asc码值，然后进行插入，所以建议是有序的自动增长类型；

2.第二点就是因为b+tree的节点存储的是索引，当然integer的数据占用的空间更小，比对和查找都是非常快的，如果你存储一个32位uuid作为主键，那么去查找的时候要对uuid的每一位都需要进行对比，不管插入还是查询都非常耗性能的，但是这也不是很大的问题，就算是字符串作为索引也很快，这和b+tree的特性有关，不管多少数据都只有三层，而且是有序的，查找消耗的磁盘io就非常低。

还有就是如果说你的一个表中没有主键，那么mysql怎么来生成b+tree呢？如果说你表中没有主键，那么mysql会找你的列中比如上面图的col1和col2中那个列中的数据没有重复的，如果找到那么就将这个列作为主键来使用；

如果说找不到呢？找不到就mysql就会生成一个隐藏列来作为主键生成b+tree；但是建议都建立主键，这么简单的事情还需要mysql消耗性能来给你创建，我觉得不太好，因为mysql的资源是非常宝贵的。

 

INNODB存储引擎

innodb存储引擎可能是现在用的最多的一种存储引擎了，这种存储引擎和myisam有什么区别呢？这种存储引擎唯一的区别就是在b+tree中的叶子节点存储的是数据，而不是数据对应的地址

它就两个文件，针对一个表来说，frm是一样的，也是存储的表的信息文件，而ibd就是索引+数据了

和myisam区别就是叶子节点存储的就是这个表的完整的一行数据，那这样我们找18这个数据，是不是只要找到了18，也就代表我不要去数据文件中找数据了，就可以直接得到完整的数据了，所以减少了磁盘IO的次数；

说到这里需要提的一个事情是什么呢？就是mysql对于磁盘的读写是随机的，也就是可能不是连续的page页，它不像kafka这种，对于磁盘的读写是顺序写的，所以SSD和机械硬盘的性能差别不大，而mysql是随机读写的，所以要求的磁盘肯定要是SSD，而且要好的那种SSD才行。

 

非主键索引

不管是mysql还是oracle中，mysql默认将主键作为索引来使用，但是我们平常还经常用到了非主键索引，那么非主键索引的b+tree又是怎么创建的呢？

这个就是非主键的索引，这种还是很好理解的，就是说如果要找到name=Alice的数据，那么肯定首先也是从头开始寻找，很快找到了Alice所在的节点，这个时候这个数据是18，18是什么？18就是你这条数据的主键，这个数据通过这个18去主键索引中回查，也就是去到了主键索引的b+tree中查找这条数据，这个非主键所以这样设计的好处就是保证数据的一致性和节约空间；

 

联合主键

联合主键是什么呢？就是说一个表的几个字段组成的主键就是联合主键，比如上图，name、age、position作为联合主键，联合主键构成的b+tree也是有序的，比如说我们要找Bill 32 dev的数据，那么是第一层，然后第二层，一样的比对方法，最好找到了数据，比对会对每一个进行比对，只是比对的时候使用的asc进行比对的。

所以联合主键建立索引就要根据你设置的主键的顺序来的，比如创建是name,age,position,那么你如果是

select * from xxx where name='Bill ' and age=32是可以使用这个索引的，但是如果是

select * from xxx where and age=32 and postition='dev'是不能使用主键的索引的，所以这个就是左前缀原理，所以在设计索引的时候要充分考虑到索引的一些特性去设计。

 

所以还是要好好理解mysql的那句话：索引是帮助mysql高效获取数据的排好序的数据结构。