MySQL单表为何别超2000万？

目录一、前言二、MySQL是如何存储数据的1. 数据页Page2. 从页到索引——B树索引3. 存入数据如下4. 关键原理总结三、MySQL是如何查询到数据的1. 举个例子select * from table where id 52. 查询步骤总结四、2000万这个上限值如何算出来的1. B树承载的记录数量2. 行数超一亿就慢了吗3. B树承载的记录数量五、总结生死博弈的核心六、拓展问题1. 为啥设计单页大小16k?2. 字符串怎么做索引?3. 索引字段的长度有限制吗?一前 言本文核心介绍为何业界会有这样的说法—— “MySQL单表存储数据量最好别超过千万级别”当然这里是有前提条件的也是我们最常使用到的InnoDB存储引擎使用的是默认索引数据结构——B树;正常普通表数据列数量控制在几个到一二十个普通字段类型及长度。接下来咱们就探究一下原因逐步揭开答案。二MySQL是如何存储数据的核心结构B树 16KB数据页这里如下建一张普通表userCREATE TABLE user (id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键,name varchar(100) NOT NULL DEFAULT COMMENT 名字,age int(11) NOT NULL DEFAULT 0 COMMENT 年龄,PRIMARY KEY (id),KEY idx_age (age)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;数据页Page介绍InnoDB存储的最小单位固定为16KB 。每页存储表数据行记录、索引、元信息等。数据加载到内存时以页为单位减少磁盘I/O次数。页的结构假设我们有这么一张user数据表。其中id是唯一主键。这看起来的一行行数据为了方便我们后面就叫它们record吧。这张表看起来就跟个excel表格一样。excel的数据在硬盘上是一个xx.excel的文件。而上面user表数据在硬盘上其实也是类似放在了user.ibd文件下。含义是user表的innodb data文件又叫表空间。虽然在数据表里它们看起来是挨在一起的。但实际上在user.ibd里他们被分成很多小份的数据页每份大小16k。类似于下面这样。ibd文件内部有大量的页我们把视角聚焦一下放到页上面。整个页16k不大但record这么多一页肯定放不下所以会分开放到很多页里。并且这16k也不可能全用来放record对吧。因为record们被分成好多份放到好多页里了为了唯一标识具体是哪一页那就需要引入页号其实是一个表空间的地址偏移量。同时为了把这些数据页给关联起来于是引入了前后指针用于指向前后的页。这些都被加到了页头里。页是需要读写的16k说小也不小写一半电源线被拔了也是有可能发生的所以为了保证数据页的正确性还引入了校验码。这个被加到了页尾。那剩下的空间才是用来放我们的record的。而record如果行数特别多的话进入到页内时挨个遍历效率也不太行所以为这些数据生成了一个页目录具体实现细节不重要。只需要知道它可以通过二分查找的方式将查找效率从O(n) 变成O(lgn)。从页到索引—B树索引如果想查一条record我们可以把表空间里每一页都捞出来全表扫描再把里面的record捞出来挨个判断是不是我们要找的。行数量小的时候这么操作也没啥问题。行数量大了性能就慢了于是为了加速搜索我们可以在每个数据页里选出主键id最小的record而且只需要它们的主键id和所在页的页号。组成新的record放入到一个新生成的一个数据页中这个新数据页跟之前的页结构没啥区别而且大小还是16k。但为了跟之前的数据页进行区分。数据页里加入了页层级page level的信息从0开始往上算。于是页与页之间就有了上下层级的概念就像下面这样。突然页跟页之间看起来就像是一棵倒过来的树了。也就是我们常说的B树索引。最下面那一层page level 为0也就是所谓的叶子结点其余都叫非叶子结点。上面展示的是两层的树如果数据变多了我们还可以再通过类似的方法再往上构建一层。就成了三层的树。聚簇索引数据按主键组织成一棵B树。叶子节点存储完整行数据 非叶子节点存储主键值指向子页的指针类似目录。二级索引叶子节点存储主键值查询时需回表根据主键回聚簇索引查数据。行格式如COMPACT格式行数据包含事务ID、回滚指针、列值等信息。行大小影响单页存储的行数。存入数据如下比如表数据已存在id为1-10的数据存储简单比方如下然后需要插入id11的数据加载1号数据页入内存分析判定id11的数据大于id10那么锁定页号5判定5号页是否还可以存下数据11可以存下将id11的数据写入到5号页中。关键原理总结所有数据通过B树有序组织数据存储在数据页上页与页之间以双向链表连接非叶子节点提供快速定位路径叶子节点存储实际的数据。三MySQL是如何查询到数据的上面我们已经介绍了MySQL中使用页存储数据以及B树索引数据的结构那现在我们就可以通过这样一棵B树加速查询。举个例子select *from table where id 5比方说我们想要查找行数据5。会先从顶层页的record们入手。record里包含了主键id和页号页地址。如下图所示左边2号页最小id是1向右3号页最小id是4然后4号页最小是7最后5号页最小是10。那id5的数据如果存在5大于4小于7那必定在3号页里面。于是顺着的record的页地址就到了3号数据页里于是加载3号数据页到内存。在数据页里找到id5的数据行完成查询。另外需要注意的是上面的页的页号并不是连续的它们在磁盘里也不一定是挨在一起的。这个过程中查询了2个页1号跟3号如果这三个页都在磁盘中没有被提前加载到内存中那么最多需要经历两次磁盘IO查询它们才能被加载到内存中。如果考虑1号如果是root常驻内存那么需要磁盘IO一次即可定位到。查询步骤总结以聚簇索引搜索为例假设id是主键从根页开始搜索 加载根页常驻内存到Buffer Pool根据指针找到下一层节点。逐层定位叶子节点 在非叶子节点页存储主键指针中二分查找 定位id5所在范围的子页如页A。重复此过程直到叶子节点页。叶子节点二分查找 在叶子页内通过主键二分查找定位到行记录返回完整数据。I/O次数分析 树高为3时根页 中间页 叶子页 3次磁盘I/O 若页不在内存中。B树矮胖特性 3层即可支撑千万级数据接下来分析是高效查询的基础。四2000万这个上限值如何算出来的在我们清楚了MySQL是如何存储及查询数据后那么2000万这个数值又是如何得来的呢超过2000万比如存储一亿数据会如何B树承载的记录数量从上面的结构里可以看出B树的最末级叶子结点里放了record数据。而非叶子结点里则放了用来加速查询的索引数据。也就是说同样一个16k的页非叶子节点里每一条数据都指向一个新的页而新的页有两种可能。如果是末级叶子节点的话那么里面放的就是一行行record数据。如果是非叶子节点那么就会循环继续指向新的数据页。假设非叶子节点内指向其他内存页的指针数量为x非叶子节点指针扇出值叶子节点内能容纳的record数量为y叶子节点单页行数B树的层数为z树高那这棵B树放的行数据总量等于 (x ^ (z-1)) * y。核心公式单表最大行数 非叶节点扇出指针数 ^ (树高-1) × 单页行数非叶子节点指针扇出值—x 怎么算我们回去看数据页的结构。非叶子节点里主要放索引查询相关的数据放的是主键和指向页号。主键假设是bigint8Byte而页号在源码里叫FIL_PAGE_OFFSET4Byte那么非叶子节点里的一条数据是12Byte左右。整个数据页16k 页头页尾那部分数据全加起来大概128Byte加上页目录毛估占1k吧。那剩下的15k除以12Byte等于1280也就是可以指向x1280页。我们常说的二叉树指的是一个结点可以发散出两个新的结点。m叉树一个节点能指向m个新的结点。这个指向新节点的操作就叫扇出fanout。而上面的B树它能指向1280个新的节点恐怖如斯可以说扇出非常高了。单页行数—y的计算叶子节点和非叶子节点的数据结构是一样的所以也假设剩下15kb可以发挥。叶子节点里放的是真正的行数据。假设一条行数据1kb所以一页里能放y15行。行总数计算回到 (x ^ (z-1)) * y 这个公式。已知x1280y15。假设B树是两层那z2。则是(1280 ^ (2-1)) * 15 ≈ 2w假设B树是三层那z3。则是(1280 ^ (3-1)) * 15 ≈ 2.5kw这个2.5kw就是我们常说的单表建议最大行数2kw的由来。毕竟再加一层数据就大得有点离谱了。三层数据页对应最多三次磁盘IO也比较合理。临界点 当行数突破约2000万时树高可能从3层变为4层树高4时最大行数 ≈ 1280^3 × 15 结果已超过百亿远大于2000万性能断崖 树高从3→4查询I/O次数从3次增至4次 多一次磁盘寻址尤其在回表查询、高并发、深分页时性能骤降。行数超一亿就慢了吗上面假设单行数据用了1kb所以一个数据页能放个15行数据。如果我单行数据用不了这么多比如只用了250byte。那么单个数据页能放60行数据。那同样是三层B树单表支持的行数就是 (1280 ^ (3-1)) * 60 ≈ 1个亿。你看我一个亿的数据其实也就三层B树在这个B树里要查到某行数据最多也是三次磁盘IO。所以并不慢。B树承载的记录数量我们都知道现在MySQL的索引都是B树而有一种树跟B树很像叫B树也叫B-树。它跟B树最大的区别在于B树只在末级叶子结点处放数据表行数据而B树则会在叶子和非叶子结点上都放。于是B树的结构就类似这样B树将行数据都存在非叶子节点上假设每个数据页还是16kb掐头去尾每页剩15kb并且一条数据表行数据还是占1kb就算不考虑各种页指针的情况下也只能放个15条数据。数据页扇出明显变少了。计算可承载的总行数的公式也变成了一个等比数列。15 15^2 15^3 ... 15^z其中z还是层数的意思。为了能放2kw左右的数据需要z6。也就是树需要有6层查一次要访问6个页。假设这6个页并不连续为了查询其中一条数据最坏情况需要进行6次磁盘IO。而B树同样情况下放2kw数据左右查一次最多是3次磁盘IO。磁盘IO越多则越慢这两者在性能上差距略大。为此B树比B树更适合成为MySQL的索引。五总结生死博弈的核心B树叶子和非叶子结点的数据页都是16k且数据结构一致区别在于叶子节点放的是真实的行数据而非叶子结点放的是主键和下一个页的地址。B树一般有两到三层由于其高扇出三层就能支持2kw以上的数据且一次查询最多1~3次磁盘IO性能也还行。存储同样量级的数据B树比B树层级更高因此磁盘IO也更多所以B树更适合成为MySQL索引。索引结构不会影响单表最大行数2kw也只是推荐值超过了这个值可能会导致B树层级更高影响查询性能。单表最大值还受主键大小和磁盘大小限制。16KB页与B树的平衡 页大小限制了单页行数和指针数B树通过多阶平衡确保低树高。2000万不是绝对 若行小于1KB如只存ID上限可到5000万若行较大如含大字段可能500万就性能下降。优化建议控制单行大小避免TEXT/BLOB直接入表。分库分表单表接近千万级时提前规划。冷热分离历史数据归档。本质通过页大小和B树结构MySQL在磁盘I/O和内存效率之间取得平衡。超出平衡点时性能从“平缓下降”变为“断崖下跌”。六拓展问题为啥设计单页大小16k?MySQL索引采用的是B树数据结构每个叶子节点叶子块存储一个索引条目的信息。而MySQL使用的是页式存储Paged storage技术将磁盘上的数据划分为一个个固定大小的页面每个页面包含若干个索引条目。为了提高索引查询效率和降低磁盘I/O的频率MySQL设置了16KB的单页大小。这是因为在MySQL中内存大小限制MySQL的索引需要放在内存中进行查询如果页面过大将导致索引无法完全加载到内存中从而影响查询效率。磁盘I/O限制当需要查询一个索引时MySQL需要把相关的页面加载到内存中进行处理如果页面过大将增加磁盘I/O的开销降低查询效率。索引效率限制在B树数据结构中每个叶子节点存储着一个索引条目因此如果每个页面能够存放更多索引条目就可以减少B树结构的深度从而提高索引查询效率。综上所述MySQL索引单页大小设置为16KB可以兼顾内存大小、磁盘I/O和索引查询效率等多方面因素是一种比较优化的方案。需要注意的是对于某些特殊的应用场景可能需要根据实际情况对单页大小进行调整。字符串怎么做索引?在MySQL中可以通过B树索引结构对字符串类型的列进行排序。具体来说当使用B树索引进行排序时MySQL会根据字符串的字典序Lexicographic Order进行排序。字典序是指将字符串中的每个字符依次比较直到找到不同的字符为止。如果两个字符串在相同的位置上具有不同的字符则以这两个字符的ASCII码值比较大小并按照升序或降序排列。例如字符串abc和def比较大小时先比较a和d的ASCII码因为d的ASCII码大于a所以def大于abc。需要注意的是如果对长字符串进行排序可能会影响索引查询的性能因此可以考虑使用前缀索引或全文索引来优化。同时在实际开发中还需要注意选择适当的字符集和排序规则以确保排序结果正确和稳定。中文字符串怎么做索引?中文字符串排序在MySQL中可以使用多种方式最常见的有以下两种按拼音排序对于中文字符串可以按照拼音进行排序。可以使用拼音排序插件如pinyin或zhuyin插件来实现中文字符串按照拼音进行排序。这些插件会将中文字符串转换为拼音或注音后再进行排序。例如先安装pinyin插件INSTALL PLUGIN pinyin SONAME ha_pinyin.so;然后创建对应的索引并按拼音排序CREATE INDEX idx_name_pinyin ON mytable(name) USING BTREE WITH PARSER pinyin;SELECT * FROM mytable ORDER BY name COLLATE pinyin;按Unicode码点排序可以使用UTF-8字符集并选择utf8mb4_unicode_ci排序规则在使用此排序规则时MySQL会按照Unicode码点进行排序适合于较为通用的中文字符串排序需求。例如CREATE INDEX idx_name_unicode ON mytable(name) USING BTREE;SELECT * FROM mytable ORDER BY name COLLATE utf8mb4_unicode_ci;需要注意的是不同的排序方式可能会对性能产生影响因此需要根据具体需求选择合适的排序方式并进行必要的测试和验证。同时在进行中文字符串排序时还需要考虑到中文字符的复杂性例如同音字、繁简体等问题以确保排序结果正确和稳定。索引字段的长度有限制吗?在MySQL中索引的长度通常是由三个因素决定的数据类型、字符集和存储引擎。不同的数据类型、字符集和存储引擎所支持的最大索引长度也有所不同。一般情况下索引的长度不应该超过存储引擎所支持的最大索引长度。在InnoDB存储引擎中单个索引所能包含的最大字节数为767个字节前缀索引除外。如果索引的长度超过了最大长度则会导致创建索引失败。因此在设计表结构时需要根据索引列的数据类型和字符集等因素合理设置索引长度以充分利用索引的优势。对于字符串类型的索引还需要注意以下几点对于UTF-8字符集每个字符占用1-4个字节因此索引长度需要根据实际情况进行计算。例如一个VARCHAR(255)类型的列在utf8mb4字符集下的最大长度为255*41020个字节。可以使用前缀索引来减少索引的大小提高索引查询效率。在创建前缀索引时需要指定前缀长度。例如可以在创建索引时使用name(10)来指定name列的前10个字符作为索引。在使用全文索引对字符串进行搜索时MySQL会将文本内容分割成单个词汇后建立倒排索引。在建立索引时需要考虑到中英文分词的问题以确保全文索引的准确性和查询效率。综上所述索引的长度需要根据数据类型、字符集和存储引擎等多个因素进行综合考虑并合理设置索引长度以提高索引查询效率和利用率。