Mysql为什么只能支持2000w左右的数据量？

首先说明一下：

MySQL并没有硬性规定只能支持到2000万左右的数据量。
其实，MySQL能够处理的数据量远远超过这个数字。无论是开源社区版还是商业版，
MySQL在适当的硬件和配置下，都能够支持非常大的数据集。

通常所说的“MySQL只能支持2000万左右的数据量”，是在谈论具体的一些使用场景时大概的估算值，
受到某些配置、硬件资源或设计上的限制。在这些情况下，MySQL的性能可能会遇到瓶颈。
这些瓶颈可能来自于硬件限制、表结构设计、配置不当、查询优化、并发处理等。

2000万的数据量是如何估算的？

这里就不得提及数据库索引的构建，以及InnoDB存储引擎的结构和它是如何存储数据与索引的了。

索引的构建

索引是数据库中的一个数据结构，可以帮助快速定位到表中特定行的记录，就像书的目录能帮助你快速找到某个主题的章节一样。在MySQL的InnoDB存储引擎中，索引通常是使用B-Tree（具体来说是B+Tree）数据结构来构建的。

构建索引的过程如下：

1. 选择索引列：通常，你会为表中的主键、经常用于查询条件（WHERE子句）的列、经常参与连接操作的列（JOIN条件）或者是经常需要排序和分组的列（ORDER BY、GROUP BY子句）创建索引。
2. 确定索引类型：可以是单列索引，也可以是多列组合索引。组合索引考虑列的顺序，这会影响其效率。
3. 创建索引：使用SQL语句CREATE INDEX或者在创建表的时候直接定义。
4. InnoDB处理索引：InnoDB存储引擎会在后台创建并维护相应的B+Tree结构，每个索引都会对应一棵B+Tree。

InnoDB的结构

InnoDB是MySQL的默认存储引擎，它支持事务、行级锁定和外键。它的主要结构包括：

1. 表空间（Tablespace）：InnoDB使用表空间来存储数据和索引。表空间可以是单个文件（file-per-table模式）也可以是共享的（如ibdata文件）。
2. 数据页（Data Page）：InnoDB将数据存储在页中，通常大小为16KB。数据页按行记录组织。
3. B+Tree索引结构：这种索引结构有几个特点——平衡的树形结构，所有的叶子节点都在同一层，叶子节点之间是双向链表，叶子节点包含所有数据信息。

InnoDB如何存储数据与索引

在InnoDB中，表数据本身就是按照主键顺序存储的，这种结构被称为聚簇索引（Clustered Index）。每张表的聚簇索引是其主键索引，意味着表数据都存放在主键索引的B+Tree结构中的叶子节点上。如果表没有显式的主键，InnoDB会选择一个唯一索引代替；如果没有唯一索引，InnoDB会自动生成一个隐藏的row ID来作为主键。

对于非主键索引（也称为二级索引或辅助索引），叶子节点不直接存储行数据，而是存储相应行的主键值。当通过辅助索引查找数据时，会先在辅助索引的B+Tree中找到主键，然后再通过主键在聚簇索引中检索实际的行数据。

索引与2000万数据的关系

索引可以显著提高查询效率，尤其是在数据量大的表中。
但同时，索引也占用磁盘空间，并且在插入、更新和删除操作时需要额外的维护成本，因为不仅是数据本身，索引也需要相应地更新。

在数据库设计中，特别是当涉及到大量数据的时候，考虑B+树索引的层数是非常重要的。主键列被广泛使用bigint类型，这主要是因为bigint类型的整数范围很大（从-2^63到263-1），
能够支持极大量的数据行，这对于有大量数据的系统来说非常有用。

B+树索引层数的影响

B+树索引的层数决定了查询需要多少次磁盘I/O操作才能找到指定的数据。
理论上，B+树的每增加一层，就能够索引更多的数据，但同时也意味着访问数据时需要更多的磁盘访问次数。
因此，数据库系统通常尽可能地减少这些层数。

如何估算B+树索引的层数

假设我们使用的是InnoDB存储引擎，一个页（Page）默认是16KB大小，而一个bigint类型的索引大约需要8字节（实际上可能更多一点，因为还包括了页的指针，假设为14字节）。我们可以大概估算一下：

1. 首先算每个叶子结点能够存储的数据量（假设每条数据1k，那么每个page能存储16条，非叶子节点的上一层节点数 = 数据量 / 16）
2. 然后，假设每个非叶子节点存储的节点数量是 x，那么第一层就是 x = 16384 / 14；第二层就是，x的平方，三层就是x的三次方，以此类推
3. 计算值与非叶子节点的上一层节点数比较即可得到层数

为何MySQL建议树的层数不超过三层？

B+树的层数建议限制在3层以内，主要是基于性能的考虑。当B+树的层数增加时，每次查询数据所需的磁盘I/O次数也会增加，因为每一层都可能涉及到一次磁盘I/O（尽管数据库的缓存机制可以减少这种情况发生的频率）。磁盘I/O通常比CPU计算和内存访问要慢得多，因此，为了维持数据库查询的高性能，建议尽量减少层数。

现在我们来估算一下三层B+树能存放多少索引条目。以InnoDB存储引擎为例，它的默认页大小是16KB（16384字节）。
我们假设现在数据库中的每一条数据为1k，每个索引条目大小为14字节（如果包含事务ID和回滚指针的话），
那么每个数据页假设存储16条数据。

下面是计算过程：

1. 第一层：每个页可以存储的索引条目数为 16384 / 14 ≈ 1170 ，第一层有1170个叶子节点。
2. 第二层：如果第二层也是完全填满的，那么它可以索引 1170 * 1170 ≈ 1368900， 第一层有1368900个叶子节点。
3. 第三层（叶子节点）：同理，第三层可以索引 1368900 * 16 ≈ 21902400 条数据。

这里是以bigint类型举例，当使用的类型不同时，存储的索引量不同，而且还与每行的数据大小有关。

总结

因此“2000万的数据量是如何估算的”这个说法是错误的，我们要根据自己的业务场景，具体情况具体分析。
可以使用预估的方式，计算在B+树的层数为3时，最大的数据量，当数据量远大于这个数值时，可以通过增加内存的方式，或者分库分表解决查询慢的情况。

最后说一句(求关注，求赞，别白嫖我)

最近无意间获得一份阿里大佬写的刷题笔记和面经，一下子打通了我的任督二脉，进大厂原来没那么难。

这是大佬写的， 7701页的阿里大佬写的刷题笔记，让我offer拿到手软

求一键三连：点赞、分享、收藏

点赞对我真的非常重要！在线求赞，加个关注我会非常感激！@小郑说编程