B+树

Mysql单表数据超过2000万性能下降，为什么？

本文源自知乎作者：@程序员阿牛，

前置知识

什么是B+树。

缘由

​ 中国互联网圈子有这样一个说法：MySQL 单表数据量大于 2000 万行，性能会明显下降。事实上，这个传闻据说最早起源于百度。具体情况大概是这样的，当年的 DBA 测试 MySQL性能时发现，当单表的量在 2000 万行量级的时候，SQL 操作的性能急剧下降，因此，结论由此而来。然后又据说百度的工程师流动到业界的其它公司，随之也带去了这个信息，所以，就在业界流传开这么一个说法。

​ 再后来，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。

​ 接下来我们从B+树的原理入手，讨论一下2000万数据量这件事到底有没有理论依据。

B+树

B+树可以理解为B-树的升级版，相比于B-树，它拥有更紧凑的结构，其主要特点有：

• B+树的层级更少，相较于B-树，B+树每个非叶子节点存储的关键字更多，树的层级更少，所以查询数据会更快；
• B+树查询数据速度稳定，因为B+树所有关键字数据地址都存放在叶子节点上，所以每次查找的次数都相同，所以查询速度要比B-树稳定；
• B+树具备天然排序功能，因为B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据的时候更方便，数据紧密性更高，缓存的命中率也会比B-树高；
• B+树全节点遍历更快，因为B+树遍历整棵树只需要遍历叶子节点即可，而不像B-树一样需要对每一层进行遍历（B-树是中序遍历），这有利于数据库做全表扫描；

问题

​ 众所周知，Mysql是以B+树作为其索引结构(仅是主流而已，不代表B+树是唯一索引结构)。那么问题是：在InnoDB中，一棵B+树可以存放多少数据，一个简单的回答是:约2千万。下面，就来讨论一下是怎么算出来的吧。

InnoDB组织数据结构的方式

​ 计算机在存储数据的时候有最小存储单元，对于磁盘来讲，存储数据的最小单位是扇区，一个扇区的大小是512字节。文件系统的最小单元是块，一个块的大小是4k。同理，InnoDB也有最小存储单位，那就是页，一个页的大小是16K，InnoDB的所有数据文件（后缀为ibd的文件），它的大小始终为16384（16K）的整数倍。

​ 数据表中的数据都是存储在页中的，所以一个页能存出多少行数据呢？我们假设一行数据是1k，那么一个页中可以存放16行数据。

​ 如果数据库只按这样的方式存储，那么如何查找这些数据也会产生问题，因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样做的话太慢了，所以人们想了一种巧妙地办法来组织这些数据。如图：

​ 我们先将数据记录按主键进行排序，分别存放在不同的页中（为了便于理解我们这里一个页中只存放3条记录，实际情况可以存放很多），除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3的页，该页存放键值和指向数据页的指针，这样的页由N个键值+指针组成。当然它也是排好序的。这样的数据组织形式，我们称为索引组织表。现在来看下，要查找一条数据，怎么查？

​ 如select * from user where id=5;

​ 这里id是主键,我们通过这棵B+树来查找，首先找到根页，你怎么知道user表的根页在哪呢？其实每张表的根页位置在表空间文件中是固定的，即page number=3的页，找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中，那么进一步去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录。

现在我们清楚了InnoDB中主键索引B+树是如何组织数据、查询数据的，我们总结一下：

1. InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2. 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？

假设一棵B+树高为2，即存在一个根节点和若干个子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

在上文中我们讨论过单个叶子节点（也就是一页）的记录数为16条（单行数据为1k的情况下）。

那么非叶子节点能存放多少指针呢，假设主键Id为bigint类型，长度为8字节，指针大小在InnoDB源码中设置为6字节，这就是14字节，一个页中能存放多少个这样的数据单元呢？16384/14=1170。

所以我们很容易就能得到结论：一棵高度为2的B+树，能够存放1170 * 16=18720条这样的数据。

根据这个原理，一棵高度为3的B+树，能够存放1170 * 1170 * 16=21902400条记录。

8720条这样的数据。

根据这个原理，一棵高度为3的B+树，能够存放1170 * 1170 * 16=21902400条记录。