mysql一张表存多少数据的计算原理

一、计算公式：

假设我们的索引层级是3层，因为3层就可以存入很多的数据了，那么计算公式为：1170*1170*16= 21902400 (千万级条)，是不是有点蒙，哈哈哈，让我们一步一步来解析。

二、数据页

在操作系统中，我们知道为了跟磁盘交互，内存也是分页的，一页大小4KB。同样的在MySQL中为了提高吞吐率，数据也是分页的，不过MySQL的数据页大小是16KB。（确切的说是InnoDB数据页大小16KB）。详细学习可以参考官网 我们可以用如下命令查询到。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| Innodb_page_size | 16384 |

+------------------+-------+

1 row in set (0.00 sec)

今天咱们数据页的具体结构指针等不深究，知道它默认是16kb就行了，也就是说一个节点的数据大小是16kb

三、具体分析

其实我们一个数据页大致可看成如下图所示：

当我们把数据插入数据库时，mysql就会根据我们的索引将数据按照顺序插入数据区的数据1，数据2区，当随着数据区的数据越来越大，比如我们要执行一条语句：select * from XXX where id=1000，那么mysql就会帮我们从id=1去比对，一直比对到1000，这样是不是很耗时，所以，这个时候就有了目录区的产生，目录区里面记录的是某一段记录的最小值，和指向最小值数据的索引，它是数组，一段连续的内存区域（可以采用二分法查找），如下图所示：

那么，当我们再查询某一条语句的时候：select * from XXX where id=8，此时就可以采用二分法查找，id=8在哪个区域，如上图所示，那么肯定在目录2区，那么就会直接引用找到数据区的7这条记录，向后比对，是不是很快，当id很大时，效率会比没有目录页从id=1比对更快，随着数据存入的越来越多，一个数据页肯定是装不下的，那么mysql就会帮我们再次创建数据页，就会如下所示：

当然，如果id是唯一索引，假如一个数据页上只能存入6条数据，我在第一个数据页上存入的6条数据为：1，3，5，6，7，8，当再来一条数据9的时候，mysql新建数据页2，将9插入，如果插入的数据是2呢？那么这个时候不仅要将数据插入到1，3中，3后面的数据还要往后移，还要创建数据页2，然后将数据页1中的8移动到数据页2中，是不是很耗费性能，所以建议一定要用自增主键，这样数据页1中的数据存入后都就是不可移动的了，不建议用uuid等类似的作为主键，它可以排序，但是非常耗时，而且占用的字节数也比较大。那么当随着数据量越来越大，一直创建数据页也不是个办法，比如我现在要查找id=100的数据，mysql就只能从数据页1开始查询，不满足接着根据NEXT的指针找到数据页2，结果里面的数据还是不满足，一直往后查，那么就会将所有数据页都查一遍，这就是全表扫描，是不是豁然开朗。

那么聪明的mysql就想了一个办法，采用B+树，最后一层为叶子节点，存储索引和数据，所有的上层存储索引，那么就变成了：

那么我们此刻就可以来计算了，一个数据页的大小是16KB，我们用的是自增长主键，假设是bigint，那么就是8B，一个指针大小是6B，就是1前面的小空格占6B，那么第一层数据页能存储的节点数量是：161024/(8+6)=1170，第二层能存储的节点数量是：1170*1170=1368900，因为一个数据页能存储1170个数据节点，因为第三层的数据组成是数据+指针，如果字段非常多的话数据所占空间是不小的，我们这里以1kb(一条记录1KB是搓搓有余的了)计算，所以在第三层，每个节点为16kb，那么每个节点是可以放16个数据的，所以最终mysql可以存储的总数据为1170 * 1170 * 16 = 21902400 (千万级条)，这样，我们就将一张表能存储的数据量计算出来了，是不是so easy!!!!