为什么说MySQL单个表的行数不能超过2000w？考虑数据迁移”，“你的表数据很快就到2000w了，难怪查询速度慢”这些名言和民间说法与“只在群里讨论技术，不开车，开车不超过120”是一样的码，否则会自动踢团”，只听说过，没试过，哈哈。接下来我们踩下车速，试着开到180码....实验看看...建个表CREATETABLEperson(idintNOTNULLAUTO_INCREMENTPRIMARYKEYcomment'主键',person_idtinyintnotnullcomment'用户id',person_nameVARCHAR(200)comment'用户名',gmt_createdatetimecomment'创建时间',gmt_modifieddatetimecomment'修改time')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的数据时，你会报错：锁总数超过锁表大小，这是因为你临时表的内存设置不够大，你只需要扩展设置参数。设置全局tmp_table_size=512*1024*1024;(512M)SETglobalinnodb_buffer_pool_size=1*1024*1024*1024(1G);先看一组测试数据，这组数据是在mysql8.0的版本中，并且在我的机器上，由于机器上还在运行idea、浏览器等各种工具，所以不用于机器配置或数据库配置，所以测试数据仅限于参考。看到这组数据好像真的和题目一一对应，当数据达到2000w时，查询时间急剧增加；这是铁律吗？那我们来看看2kw的建议值是怎么来的？单表数的限制首先我们想一下，数据库中单表的最大行数是多少？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，按照每秒增加一条记录的速度，大概什么时候用完？我们来看看表空间下面的索引结构。对了，我们下面要讲的内容都是基于Innodb引擎的。大家都知道Innodb索引内部使用的是B+树表数据，在硬盘上的存储也是类似的，其实是放在一个叫person.ibd（innodb数据）的文件中，也叫表空间；虽然在数据表中，它们看起来是一个接一个的，但实际上在文件中被分成了很多小的数据页，每个都是16K。大致是这样的。当然，这只是我们抽象出来的。表空间中有很多段、区、组等概念，但是我们需要跳出来看。page的数据结构是因为每个page只有16K大小，但是如果数据很多，一个page肯定放不下数据，数据肯定会分到其他page中，所以为了关联这些页面，一定会有前后页面地址的记录，方便查找对应的页面；同时每一页都是唯一的，那么就会有一个唯一的标记来标示该页，这就是页码；页面中会记录数据，所以会有读写操作，读写操作会中断或者其他异常会导致数据不完整等，这时会有校验机制，所以会有校验码其中，读操作最重要的是效率。如果一条一条遍历记录，那肯定很费力，所以会为数据生成相应的页目录（PageDirectory）；所以实际页面的内部结构如下所示。从图中可以看出，一个InnoDB数据页的存储空间大致分为7个部分，一些部分占用一定的字节数，一些部分占用不确定的字节数。在页面的7个组件中，我们自己存储的记录会按照我们指定的行格式存储在UserRecords部分。但是，当页面首次生成时，没有用户记录部分。每当我们插入一条记录时，我们都会从FreeSpace部分申请一个记录大小的空间，也就是未使用的存储空间，划分到UserRecords部分。当FreeSpace部分的空间完全被UserRecords部分取代时，就意味着这个页面用完了。如果有新的记录要插入，需要申请一个新的页面。这个过程的示意图如下所示。刚刚提到了添加数据的过程。然后说说数据搜索的过程。如果我们需要查找一条记录，我们可以将表空间中的每一页加载到内存中，然后逐条判断这些记录是否是我们想要的。小时候没毛病，记忆力还能支撑；但现实就是这么残酷，它不会给你这种情况；为了解决这个问题，mysql中有了索引的概念；大家都知道索引可以加快数据查询，那么到底是怎么回事呢？下面我就来看看吧。索引数据结构mysql中索引的数据结构和刚才描述的页面几乎一模一样，大小也是16K，只是记录了页面（数据页、索引页）的最小主键id和页码在index页面中，并且在index页面中加入了层级信息，从0开始往上数，所以页面之间就有了上下层级的概念。看到这张图是不是感觉有点像，是不是很像二叉树，对，没错！就是一棵树，但是这里我们简单的画了三个节点，一个2层的结构。如果数据较多，可能会扩展成3层树。这就是我们常说的B+树。lower层的pagelevel=0，为叶子节点，其余为非叶子节点。看上图，我们只看一个节点。首先，它是一个非叶节点（索引页）。在它的内容区中，有两部分：id和页码地址。这个id是对应页面记录的最小值。记录id值，页码地址是指向对应页的指针；数据页几乎一样，不同的是数据页记录的是真正的行数据而不是页地址，id也是有顺序的。单表的建议值接下来我们用一个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+树级别并影响查询性能。
