你的mysql到底能存多少数据呢？_mysql单表数据上限

前言

参考借鉴文章 我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？
这里自己在总结一下，原因是相关知识欠缺，看别人的文章研究很久才弄明白，所以这里记录一些心得。

作者：阿杆
链接：https://juejin.cn/post/7165689453124517896
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

抛砖

很多人说，MySQL每张表最好不要超过2000万条数据，否则就会导致性能下降。阿里的Java开发手册上也提出：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
但实际上，这个2000万或者500万都只是一个大概的数字，并不适用于所有场景，如果盲目的以为表数据只要不超过2000万条就没问题了，很可能会导致系统的性能大幅下降。
实际情况下，每张表由于自身的字段不同、字段所占用的空间不同等原因，它们在最佳性能下可以存放的数据量也就不同。

引玉

概念

简单讲一下：

1. 一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗单独的树。

2. 聚簇索引和非聚簇索引：
   主键索引也是聚簇索引（非叶子节点）：只存储主键和索引列等索引数据。
   非主键索引都是非聚簇索引（叶子节点）：存储真正的所有字段信息。

3. B+树的查询是从上往下一层层查询的，一般情况下我们认为B+树的高度保持在3层以内是比较好的，也就是上两层是索引，最后一层存数据，这样查表的时候只需要进行3次磁盘IO就可以了(实际上会少一次，因为根节点会常驻内存)，且能够存放的数据量也比较可观。

   如果数据量过大，导致B+数变成4层了，则每次查询就需要进行4次磁盘IO了，从而使性能下降。所以我们才会去计算InnoDB的3层B+树最多可以存多少条数据。

4. MySQL每个节点大小默认为16KB，也就是每个节点最多存16KB的数据，可以修改，最大64KB，最小4KB。

节点存储

在Innodb的B+树中，我们常说的节点被称之为 页(page)，每个页当中存储了用户数据，所有的页合在一起组成了一颗B+树；

页 是InnoDB存储引擎管理数据库的最小磁盘单位，我们常说每个节点16KB，其实就是指每页的大小为16KB。
这16KB的空间，里面需要存储 页格式 信息和 行格式 信息，其中行格式信息当中又包含一些元数据和用户数据。所以我们在计算的时候，要把这些数据的都计算在内。

下边具体介绍下啥是页格式和行格式；

注意：如果你不明白，那么就不要硬去理解，先去看后边的计算环节，结合计算再来一点点看这些介绍，因为后边的计算用到的数据都要出自这里的介绍。

页格式

每一页的基本格式，也就是每一页都会包含的一些信息，总结表格如下：

另外，当新记录插入到 InnoDB 聚集索引中时，InnoDB 会尝试留出 1/16 的页面空闲以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的页大约可用 15/16 的空间。如果以随机顺序插入记录，则页大约可用 1/2 到 15/16 的空间。

除了 User Records和Free Space 以外所占用的内存是 38+56+26+8=128字节，每一页留给用户数据的空间就还剩 16 × 15/16 × 1024−128=15232字节（保留了1/16）。
当然，这是最小值，因为我们没有考虑页目录。页目录留在后面根据再去考虑，这个得根据表字段来计算。

重点是页目录，下边计算会取一个平均值，根据图上说的是每个槽（不用管啥是槽）会放4~8条，取平均值就是6条，一个槽会占用2byte（取最大值吧）。

行格式

首先，我觉得有必要提一嘴，MySQL5.6的默认行格式为COMPACT(紧凑)，5.7及以后的默认行格式为DYNAMIC(动态)，不同的行格式存储的方式也是有区别的，还有其他的两种行格式，本文后续的内容主要是基于DYNAMIC(动态)进行讲解的。（了解即可）

特性对比（了解即可）：

• DYNAMIC(动态)：
  当使用 DYNAMIC 创建表时，InnoDB 会将较长的可变长度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的值剥离出来，存储到一个溢出页上，只在该列上保留一个 20 字节的指针指向溢出页。

  优点
  DYNAMIC 行格式避免了用大量数据填充 B+ 树节点从而导致长列的问题。
  DYNAMIC 行格式的想法是，如果长数据值的一部分存储在页外，则通常将整个值存储在页外是最有效的。
  使用 DYNAMIC 格式，较短的列会尽可能保留在 B+ 树节点中，从而最大限度地减少给定行所需的溢出页数。

• COMPACT(紧凑)：
  将前 768 个字节和 20 字节的指针存储在 B+ 树节点的记录中，其余部分存储在溢出页上。

  列是否存储在页外取决于页大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合 B+ 树页（文档里没说具体是多少

  声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/921322