面试官：当单表数据达到2000万时，MYSQL性能下降严重，为什么？

当单表数据量超过2000万条时，MySQL的性能急剧下降。

中国互联网技术圈有一句话：如果MySQL单表数据量大于2000个万行，性能就会明显下降。事实上，这个传闻据说来自百度。具体情况大概是这样的。 DBA在测试MySQL性能时发现，当单表大小在2000万行量级时，SQL操作的性能急剧下降。由此得出结论。随后又传出百度工程师跳槽到了业内其他公司，也带来了这个信息，于是这个说法就传遍了整个行业。

后来阿里巴巴《Java 开发手册》建议单表行数超过500万行，或者单表容量超过2GB时才进行分库分表。这是有阿里巴巴金铁法则支撑的。因此，很多人在设计大数据存储时，都会以此作为进行表操作的标准。

那么这个说法有理论依据吗？首先可以说这个性能问题是由很多因素组成的，比如MYSQL本身的硬件和参数设置等。那么今天我们可以从MySQL的索引结构来谈谈。

有MYSQL基础的同学都知道，MYSQL使用B+树作为索引结构。

问题？

B+树在InnoDB中可以存储多少行数据？这个问题的简单答案是2000万左右。为什么这么多？既然这是可以计算的，我们就开始了解InnoDB索引的数据结构和数据组织方法的问题。

我们都知道，计算机在存储数据时，是有一个最小存储单元的。这就像今天流通的现金的最小单位是十美分。在计算机中，磁盘存储数据的最小单位是扇区，一个扇区的大小为512字节。文件系统（如XFS/EXT4）的最小单位是块，块大小为4k。对于我们的InnoDB存储引擎来说，它也有自己的最小存储单位——页（Page），一个页的大小是16K。

下面的图片可以帮助您了解最小的存储单元：

在文件系统中，一个文件只有1个字节大小，但它必须占用4KB的磁盘空间。

innodb中的所有数据文件（ibd后缀的文件）始终是16384（16k）的整数倍。

磁盘扇区、文件系统和InnoDB存储引擎都有自己的最小存储单元。

在 MySQL 中，我们的 InnoDB 页的默认大小是 16k。当然也可以通过参数来设置：

数据表的数据是分页存储的，那么一页可以存储多少行数据呢？假设一条数据线的大小为1k，那么一页可以存储16行这样的数据。

如果数据库只是这样存储的话，那么查找数据就会出现问题，因为我们不知道我们要查找的数据在哪一页上，而且也不可能遍历所有的页，太慢了。于是人们想到了一种方法，以B+树的形式来组织这些数据。如图：

数据记录首先按照主键排序，存储在不同的页面中（为了方便理解，这里我们一页只存储3条记录，实际情况下，很多记录需要分页）存储）。除了存储数据的页面之外，还有存储键值+指针的页面，例如图中页数=3页。该页存储键值和指向数据页的指针。这样的页面由N个键值+指针组成。当然，这也是选择的。这种数据组织形式称为索引组织表。现在让我们看看。如何查找数据？

例如选择user *，其中id=5；

这里id为主键。我们搜索这棵B+树，首先找到根页面。如何知道用户表的根页在哪里？事实上，每个表的根页位置在表空间文件中是固定的，即页号=3（我们将在下面进一步证明这一点）。找到根页面后，使用二分查找的方法找到id=5的页面。数据应该在P5指针指向的页上，然后继续在页号=5上搜索。还可以使用二分查询的方法找到id=5的记录：

现在我们清楚地知道InnoDB中的主键索引是一棵B+树。我们来总结一下数据的组织和查询：

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

2。索引表采用非叶子节点和指针的二分查找方法来确定数据在哪一页，然后在数据表上查找需要的数据；

回到我们一开始的问题，通常情况下，一棵B+树可以存储多少行数据？

这里我们首先假设B+树的高度为2，即有一个根节点和若干个叶子节点。那么这棵B+树中存储的记录总数就是：根节点指针的数量*单个叶子节点的记录行数。

上面我们解释了一个叶子节点（页）的记录数 = 16K/1K=16。（这里假设一行记录的数据大小为1K，实际上很多互联网业务数据的记录大小通常在1K左右）。

现在我们需要计算非叶子节点可以存储多少个指针。其实，这也很容易计算。我们假设主键标识符是bigint类型，长度为8字节，并且在InnoDB源码中将指针大小设置为6字节。 ，总共14个字节，我们在一页上可以存储的这样的单元的数量实际上就意味着指针的数量，即16384/14=1170。那么可以计算出，一棵高度为2的B+树可以存储1170*16=18720条这样的数据记录。

根据同样的原理，我们可以计算出一棵3高的B+树可以存储1170*1170*16=21902400条这样的记录。因此，InnoDB中B+树的高度通常为1-3层，可以满足千万级数据存储。在查找数据时，一页查找就意味着一次IO，因此通过主键索引的查询通常只需要1-3次IO操作就可以找到数据。

如何获取InnoDB主键索引的B+树高度？

上面我们得出结论，B+树的高度通常为1-3。下面，我们从另一个方面来证明这个结论。 InnoDB 表空间文件中达成一致，3.页号表示主键索引的根页，B+树的页级别通过根页偏移量64来存储。如果页面层级为1，树高为2，页面层级为2，则树高为3。即B+树高=页面层级+1；下面我们尝试从实际环境中找到这个页面级别。

在实际操作之前，可以通过InnoDB的元数据表查看主键索引的根页的页码为3。也可以从书中得到确认《InnoDB存储引擎》。

执行结果：

可以看到dbt3数据库下的客户表和行表的主键索引根页的页码为3，而其他二级索引的页码为4、二级索引和主键索引的区别参见MySQL相关书籍，这里不做介绍。

我们对数据库表空间文件进行相关分析：

由于主键索引B+树的根页是从整个表空间文件的第三页开始的，所以可以计算出它在文件中的偏移量：16384 *3=49152（16384 是页面大小）。

另外，如《InnoDB存储引擎》中所述，页级值保存​​在根页偏移位置64的前2个字节中，因此页级值在整个文件中是偏移的。 :16384*3+64=49152+64=49216，在前2个字节中。

然后我们使用hexdump工具查看表空间文件指定偏移处的数据：

行元素表的页级为2，B+树的高度为页级+1=3 ;

region表页的level为0，B+树的高度为page level+1=1；

客户板的side level为2，B+树的高度为side level+1=3；

这三个表的数据量如下：

总结：

行表数据行数超过600万，B+树的高度为3， customer表中的数据行只有15万行，B+树的高度也为3。可以看出，尽管数据量相差较大，但是两个表树的高度都是3。也就是说，有两个表的索引查询效率差别不大，因为都只需要执行3次IO。所以如果你有一个1000万行的表，它的B+树高度仍然是3，查询效率仍然不会提高太多。

region表只包含5行数据，当然B+树的高度为1。

最后复习一道面试题

有一道MySQL的面试题。为什么MySQL索引使用B+树而不是其他树结构？例如B树？

复杂版本的问题可以参考这篇文章；

一个简单版本的答案：

由于B树保存数据不分叶子节点或非叶子节点，所以非叶子节点也可以保存指针的数量减少（有些资料也称为扇出）。如果指针少，就要保存大量数据，只能增加树的高度，导致IO操作增多，查询性能降低；

总结

本文从一个问题开始，逐步介绍InnoDB索引表的基本原理和查询方法，并结合已有的知识来回答问题并用实践证明。当然，为了使术语简单易懂，本文忽略了一些细节。例如，不可能将页面上的整个区域都用于数据存储。除了页面的填充因子之外，它还存储一些其他字段，如页面级别、索引号等，使得不可能完全使用页面来存储数据。关于二级索引的数据访问方法，可以在MySQL相关书籍中查阅。重点是与主键索引一起执行表查询。