MySQL为什么建议单表不要超过2000万行
前言:
在工作中或者面试中,经常会说mysql单表牛不要超过2000w行,超过两千万行建议分表,否则查询效率会急剧下降。
那么一张表最多可以存多少数据呢,今天我们从底层技术进行刨析,分析下根本原因。
底层存储:
我们看到的表,都是一行一行存储,实际上在mysql底层文件中是以b+树的结构存储,b+ 树的数据类型这块就不多做描述。
b+ 树:
b+ 树是一个多路平衡树,非叶子节点不存储数据只存储索引,叶子节点会去存储索引和具体数据行,同时在我们的每个节点上,都会去存储多个索引id,当我们需要检索id的适合,会去根据先在当前节点上查找,定位到下一层的节点位置,再去下一层查找,直到找到叶子节点,拿到需要查找的数据行。所以我们的每次查询,都是需要遍历到叶子节点才可以拿到数据。
例如:
我们需要查找索引为8的数据,就需要经过3层逐层查找,按照层级时间复杂度是O(logn)。
页:
如上图,我们的每个节点其实就是一个页,InnoDB存储引擎中的页是其内存管理和磁盘存储的基本单位。
在页中包括页头、数据列、页目录、页尾四部分,在查找数据的时候,会通过页目录进行检索,使用二分查找法,时间复杂度为O(logn)。
计算验证三层B+树可以存储多少数据?
我们每个页默认的大小是16kB,抛去页头、页目录、页尾,剩余的数据区大概有15kB的大小。
总行数计算公式:
maxRow
=
X
Z
−
1
×
Y
\text{maxRow} = X^{Z-1} \times Y
maxRow=XZ−1×Y
其中B+树 层数为Z,单个叶子节点数据行数为Y,单页的索引指针数为X。
非叶子节点数据区,包括索引指针以及页号。
按照我们主键索引为bigint类型,占用8字节,页号占用4字节,一条数据占用12字节。那么我们计算:
单页的索引指针数:
X
=
15
K
B
/
12
B
=
1280
X = 15KB / 12B = 1280
X=15KB/12B=1280
叶子节点数据区,包含索引、页号以及具体数据行。
假设,我们一行的数据包括索引和页号算1KB,那么我们计算:
单个叶子节点的数据行数:
Y
=
15
K
B
/
1
K
B
=
15
Y = 15KB / 1KB = 15
Y=15KB/1KB=15
那么我们如果有三层B+树,总存储量就是:
maxRow
=
X
Z
−
1
×
Y
=
128
0
2
∗
15
=
24576000
\text{maxRow} = X^{Z-1} \times Y = 1280^2 * 15 = 24576000
maxRow=XZ−1×Y=12802∗15=24576000
所以我们可以计算出,三层结构B+树,就可以存储2400wan数据了。
这个也就是我们常说的单表数据2000万的一个由来了。
文章到这里也就结束了,如果有问题,欢迎大家在下面讨论。