Mysql深入五：Mysql底层数据结构选择与B+Tree分析

Mysql底层结构选择 B+Tree

1、为什么不二分查找法作索引？

解释：前提是有序，不适合做索引

2、为什么不使用二叉树做索引？

解释：二叉树虽然简单，但是树的高度太高，不适合用做索引

3、为什么不使用平衡二叉树做索引？

解释：与二叉树对比，高度会自动平衡，虽然解决了二叉树的高度问题，但是解决的不透彻，高度依然没有得到很好的改善

4、为什么不使用BTree？（多路平衡数，一个节点存储多个数据）

解释：高度得到很好的控制，等值（=）查询时，效率特别高，但是Mysql会大量使用范围查询（<、>等），BTree对于这些查询不友好，效率特别低

5、B+Tree

会把数据存到叶子一份，叶子是单向关联（数据库是双向关联），有序，对于范围条件查询，效率高

如下图

 

B+Tree分析

引擎：

• B+Tree，根据不同的储存引擎，不一样存储，一般是Innodb和MyISAM
• Innodb是聚集索引（也叫聚合索引），MyISAM是非聚集索引
• 聚集索引意思是索引和数据存放在一起，在B+Tree的叶子节点中，在mysql中也是在存放同一个文件中 .ibd （data+index）
• 非聚集索引意思是索引和数据不是存放在一起，只有索引是在叶子节点，在mysql中分开存储在两个文件中.MYD （data 数据）、.MYI （index 索引）

 

为什么主键尽量选择整型而不选择字符串？

分析：

1. 假设varchar（20），那varchar的长度就是20*3=60，还有加上6个长度记录位置信息，所以就是66B
2. 在数据库中一页一个节点16K，那一个节点能存储多少数据？就是16*1024/66=248
3. 如果是int，4+6=10B，一个节点能存储16*1024/10=1638

结论：

这就看出差距了，一个节点存越多数据，相当于层数越低，寻址次数越少

所以在工作中，尽量能不用varchar，就不用，能使用数值就用数值做主键，提高性能

 

树的高度为3的情况下，Mysql能存多少数据？

假设使用主键bigint 长度为8

pk bigint：8+6B=14B（除开8之外，还有要6个长度记录位置信息（官方规定），一个数据包括数据和紧跟其后的位置指针，就是一个数据占有14B的长度）

Innodb_page_size：16K（show variables like 'innodb_page_size'）page：是mysql里面的逻辑存储单元，大小是16384B=16K，B+Tree一个节点可以理解成一个页

数据计算：

1、第一行只有一个节点：16K，存放的是索引，需要14B大小

计算一个节点可以存放多少个数据：16K*1024B/14B=1170个数据

2、第二行，第一行一个数据对应第二行一个节点，其中一个节点又是16k：数据1170*1170=1368900

3、第三行不仅存放索引，考虑还要存放数据

一个节点16K，一个数据为1k（一个数据不是14B了，需要占用更多内存，理论上很多表都是为1K）

那一个节点就是16/1=16个数据

第三行量为数据=1368900*16=2190,2400

结论：叶子节点存的就是数据，所以树的高度为3的情况下，mysql大概能存多少数据2000w数据
 

 

下面是我针对自己假想的一张表，表的数据比较少，三层大概能存4亿多数据（B+树数据写错了，排序）