1.背景作为一个在后端圈子里打转多年的老司机，是不是经常听说“mysql单表不要超过2000w”，“单表应该超过2000w考虑数据迁移”，“你的表数据很快就会达到2000w，怪不得查询速度慢”。这些名言同“群里只讨论技术，不开车，开车不超过120码，否则自动踢群”一样，我也只是听过，没试过，哈哈。接下来，让我们把车速踩到180码...2.实验看看...建表：CREATETABLEperson(idintNOTNULLAUTO_INCREMENTPRIMARYKEYcomment'primarykey',person_idtinyintnotnullcomment'用户id',person_nameVARCHAR(200)comment'用户名',gmt_createdatetimecomment'创建时间',gmt_modifieddatetimecomment'修改时间')comment'人员信息表';插入一条数据：insertintopersonvalues(1,1,'user_1',NOW(),now());使用mysql伪列rownum，设置伪列起点为1select(@i:=@i+1)asrownum,person_namefromperson,(select@i:=100)asinit;set@i=1;运行如下sql，连续执行20次，即2的20次方约等于100w条数据；执行23次，2的23次方约等于800w，所以可以插入几千万的测试数据。如果不想让数据翻倍或翻倍，而是想增加少量，有一个技巧，就是在SQL后面加一个where条件，比如id>某个值就可以控制数量了添加的数据。insertintoperson(id,person_id,person_name,gmt_create,gmt_modified)选择@i:=@i+1,left(rand()*10,10)asperson_id,concat('user_',@i%2048),date_add(gmt_create,interval+@i*cast(rand()*100assigned)SECOND),date_add(date_add(gmt_modified,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)fromperson;这里需要注意的是，可能当你执行了将近800w或者1000w的数据时，你会报错：Thetotalnumberoflocksexceedsthelocktablesize，这是因为你临时表的内存设置不够大，你只需要扩展设置参数。设置全球tmp_table_size=51210241024;(512M)SETglobalinnodb_buffer_pool_size=110241024*1024(1G);先看一组测试数据，这组数据是mysql8.0版本的，而且是在我的机器上，因为机器上还在运行ideas，浏览器等工具，所以没有用于机器配置或者数据库配置，所以测试数据仅限于参考。看到这组数据好像真的和题目一一对应，当数据达到2000w时，查询时间急剧增加；这是铁律吗？那我们来看看2kw的建议值是怎么来的？3.单表数的限制首先我们想一下，数据库中单表的最大行数是多少？CREATETABLEperson(idint(10)NOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主键',person_idtinyintnotnullcomment'userid',person_nameVARCHAR(200)comment'用户名',gmt_createdatetimecomment'创建时间',gmt_modifieddatetimecomment'修改时间')comment'人事信息表';看上面的表sql，id是主键，本身是唯一的，也就是说主键的大小可以限制表的上限。如果主键声明了int的大小，即32位，那么支持2^32-1~~21亿；如果是bigint，就是2^62-1？(36893488147419103232)，很难想象这有多大。通常，在达到此限制之前数据库可能已满！！有人统计过，如果建表时自增字段选择unsignedbigint，那么自增最大值为18446744073709551615，按照每秒增加一条记录的速度，大概什么时候用完？4、表空间我们来看一下索引的结构。对了，我们下面要讲的内容都是基于Innodb引擎的。大家都知道Innodb索引内部使用的是B+树表数据，在硬盘上的存储也是类似的。这样实际上是放在一个叫person.ibd（innodb数据）的文件中，也叫表空间；虽然在数据表中，它们看起来是一个接一个的，但实际上在文件中分成了很多个小数据页，每个都是16K。大致是这样的。当然，这只是我们抽象出来的。表空间中有很多段、区、组等概念，但是我们需要跳出来看。5.页的数据结构因为每页只有16K大小，但是如果数据很多，一页肯定放不下数据，数据肯定会分到其他页，所以顺序关联这些页面，肯定会有前后页面地址的记录，方便查找对应的页面；同时每一页都是唯一的，那么就会有一个唯一的标记来标示该页，这就是页码；页面中会记录数据，所以才会有读写操作，读写操作中断或者其他异常会导致数据不完整等，那么就会有校验机制，所以才会有校验代码在里面，而读操作最重要的就是效率问题。如果按照记录一条一条遍历，肯定很费力，所以会为数据生成相应的页目录（PageDirectory）；所以实际页面的内部结构如下所示。从图中可以看出，一个InnoDB数据页的存储空间大致分为7个部分，一些部分占用一定的字节数，一些部分占用不确定的字节数。在页面的7个组件中，我们自己存储的记录会按照我们指定的行格式存储在UserRecords部分。但是，当页面首次生成时，没有用户记录部分。每当我们插入一条记录时，我们都会从FreeSpace部分申请一个记录大小的空间，也就是未使用的存储空间，划分到UserRecords部分。当FreeSpace部分的空间完全被UserRecords部分取代时，就意味着这个页面用完了。如果有新的记录要插入，需要申请一个新的页面。这个过程的示意图如下所示。刚刚提到了添加数据的过程。然后说说数据搜索的过程。如果我们需要查找一条记录，我们可以将表空间中的每一页加载到内存中，然后逐条判断这些记录是否是我们想要的。小时候没毛病，记忆力还能支撑；但现实就是这么残酷，它不会给你这种情况；为了解决这个问题，mysql中有了索引的概念；大家都知道索引可以加快数据查询，那么到底是怎么回事呢？下面我就来看看吧。六、索引的数据结构mysql中索引的数据结构和刚才描述的页面几乎一模一样，大小也是16K，但是页面的最小主键id和页（数据页，索引page)记录在索引页码中，索引页中加入了层级信息，从0开始往上数，所以页与页之间有上下层的概念。看到这张图是不是感觉有点像，是不是很像二叉树，对，没错！就是一棵树，但是这里我们简单的画了三个节点，一个2层的结构。如果数据较多，可能会扩展成3层树。这就是我们常说的B+树。lower层的pagelevel=0，为叶子节点，其余为非叶子节点。看上图，我们只看一个节点。首先，它是一个非叶节点（索引页）。在它的内容区中，有两部分：id和页码地址。这个id是对应页面记录的最小值。记录id值，页码地址是指向对应页的指针；数据页几乎一样，不同的是数据页记录的是真正的行数据而不是页地址，id也是有顺序的。7.单表建议值接下来我们用3层2叉（实际上是M叉）的图示来说明查找一行数据的过程。比如我们要找一个id=6的行数据，因为页码和该页的最小id存放在非叶子节点，所以我们从顶层开始比较，先看目录在第10页，有[id=1,pagenumber=20],[id=5,pagenumber=30]，说明左节点的最小id为1，右节点的最小id为5;6>5，那么根据二分查找的规则，一定要继续寻找右边的结点。找到页码为30的节点后，发现该节点有子节点（非叶子节点），则继续比较。同理，6>5&&6<7，所以找到第60页，找到第60页后，发现这个节点是叶子节点（数据节点），所以把这个页的数据加载到内存中，进行一次-一对一比较，找到id=6的数据行。从上面的过程中，我们发现为了找到id=6的数据，一共查询了三个页面。如果三个页面都在磁盘上（没有提前加载到内存中），那么最多需要三个磁盘IO。需要说明的是，图中的页码只是举例，在实际情况中并不是连续的，也不是按顺序存储在磁盘上的。到此为止，我们大概了解了表中数据的结构和查询数据的过程，这样我们就可以大致预估这样的结构能存储多少数据。从上图我们知道B+编号的叶子节点有数据，非叶子节点用来存放索引数据。因此，对于同一个16K的页面，非叶子节点中的每条数据都指向一个新的页面，而新的页面有两种可能。如果是叶子节点，那么里面就有一行数据。如果是非叶子节点，那么它会继续指向新的页面。假设指向其他页面的非叶子节点的个数为x。叶子节点可以容纳的数据行数为yB+，层数为z，如下图所示。Total=x^(z-1)*y表示总数将等于x的z-1次方与Y的乘积。X=?页面的结构在文章开头已经介绍过了，索引也不例外。会有FileHeader（38byte），PageHeader（56Byte），Infimum+Supermum（26byte），FileTrailer（8byte），加上pagedirectory，大概1k，我们就当成1K，那么大小整个page是16K，剩下的15k用来存放数据。索引页中的主要记录是主键和页码。我们假设主键是Bigint（8byte），页码也是固定的（4Byte），那么索引页中的一条数据就是12byte；所以x=15*1024/12≈1280行。是=？叶节点和非叶节点的结构是一样的。同样，可以存储数据的空间也是15k；但真正的行数据存储在叶节点中，影响这一点的因素还有很多。比如字段的类型，字段的个数；每行数据占用的空间越大，页面上放置的行就越少；这里我们暂且按1k行数据计算，那一页可以存储15行，Y≈15。算到这里，是不是心里已经有了谱呢？根据上面的公式，Total=x^(z-1)y,已知x=1280,y=15假设B+树有两层，即Z=2,Total=(1280^1)15=19200假设B+树为三层，即Z=3，Total=(1280^2)*15=24576000(约2.45kw)Oops,ohmy!这不正是文章开头推荐的最大行数2000w吗！没错，B+号的最高等级最多是3级。试想一下，如果是4级，除了查询外，磁盘IO的数量会增加，Totalvalue将超过300亿。这是不合理的，所以3层应该是一个更合理的值。是这里吗？不是的，我们在讲Y的取值的时候只是假设了1K，比如我当前行的数据实际占用的空间不是1K，而是5K，所以单个数据页最多只能放3条数据.同理，还是按照Z=3的值计算，则Total=(1280^2)*3=4915200（近500w）。因此，在保持相同水平（相似的查询性能）的情况下，行数据大小不同，在某些情况下，最大推荐值实际上是不同的，还有很多其他影响查询性能的因素，例如数据库版本，服务器配置，SQL编写等MySQL为了提高性能，会将表的索引加载到内存中。当InnoDBbuffersize足够大的时候，可以完全加载到内存中，查询不会有问题。但是，当单表数据库达到一定量级的上限时，内存无法存储其索引，使得后续的SQL查询会产生磁盘IO，导致性能下降，因此增加硬件配置（如使用内存作为磁盘），它可能会带来立竿见影的性能提升。八、总结Mysql表数据是以页的形式存储的，页在磁盘中不一定是连续的。页空间为16K，并不是所有的空间都用来存放数据，会有一些固定的信息，比如页眉、页脚、页码、校验和等。在B+树中，叶子节点和非叶子节点的数据结构是一样的。区别在于叶子节点存储实际的行数据，而非叶子节点存储主键和页码。索引结构不会影响单表的最大行数，2kw只是一个推荐值。超过这个值可能会导致更高的B+树级别并影响查询性能。
