读【mysql单表最大两千万】文章有感

最新推荐文章于 2024-01-17 15:50:47 发布

安迪爸爸

最新推荐文章于 2024-01-17 15:50:47 发布

阅读量1.7k

点赞数 2

分类专栏：服务器文章标签： mysql

本文链接：https://blog.csdn.net/wzmde007/article/details/124061933

版权

服务器专栏收录该内容

25 篇文章 2 订阅

订阅专栏

前言

mysql我们都很熟悉，现在常用的存储引擎是innodb，mysql数据是存储在物理磁盘上的，而真正的数据处理又是在内存中执行的。由于磁盘的读写速度非常慢，如果每次操作都对磁盘进行频繁读写的话，那么性能一定非常差。为了上述问题，InnoDB将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB。这样的话，一次性至少读取1页数据到内存中或者将1页数据写入磁盘。通过减少内存与磁盘的交互次数，从而提升性能。

那么mysql单表的瓶颈是多大呢？阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，网上也有说是2000W，谁说的更准确呢？其实说的都没错，阿里的500W是结合应用场景综合得出的结论，应该是超过这个值，连表查询等操作存在瓶颈。而2000W呢，是B+树索引的三层的瓶颈，再大的话，磁盘io会更多，但2000W是铁定定律吗，其实这个两千万，是按照一行数据为1KB来计算得出的，加入你一行数据小于1KB可能存储的会更多。

今天看到这篇文章，计算这块写得很清晰，觉得不错，转载过来。

文章地址：今日头条

故事从好多年前说起。

想必大家也听说过数据库单表建议最大2kw条数据这个说法。如果超过了，性能就会下降得比较厉害。

巧了。

我也听说过。

但我不接受它的建议，硬是单表装了1亿条数据。

这时候，我们组里新来的实习生看到了之后，天真无邪地问我："单表不是建议最大两千万吗？为什么这个表都放了1个亿还不分库分表"？

我能说我是因为懒吗？我当初设计时哪里想到这表竟然能涨这么快。。。

我不能。

说了等于承认自己是开发组里的毒瘤，虽然我确实是，但我不能承认。

我如坐针毡，如芒刺背，如鲠在喉。

开始了一波骚操作。

"我这么做是有道理的"

"虽然这个表很大，但你有没有发现它查询其实还是很快"

"这个2kw是个建议值，我们要来看下这个2kw是怎么来的"

数据库单表行数最大多大？

我们先看下单表行数理论最大值是多少。

建表的SQL是这么写的，

CREATE 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`)
) ENGINE=InnoDB AUTO_INCREMENT=100037 DEFAULT CHARSET=utf8;

其中id就是主键。主键本身唯一，也就是说主键的大小可以限制表的上限。

如果主键声明为int大小，也就是32位，那么能支持2^32-1，也就是21个亿左右。

如果是bigint，那就是2^64-1，但这个数字太大，一般还没到这个限制之前，磁盘先受不了。

搞离谱点。

如果我把主键声明为 tinyint，一个字节，8位，最大2^8-1，也就是255。

CREATE TABLE `user` (
  `id` tinyint(2) 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`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

如果我想插入一个id=256的数据，那就会报错。

mysql> INSERT INTO `tmp` (`id`, `name`, `age`) VALUES (256, '', 60);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

也就是说，tinyint主键限制表内最多255条数据。

那除了主键，还有哪些因素会影响行数？

索引的结构

索引内部是用的B+树，这个也是八股文老股了，大家估计也背得很熟了。

为了不让大家有过于强烈的审丑疲劳，今天我尝试从另外一个角度给大家讲讲这玩意。

页的结构

假设我们有这么一张user数据表。

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)。

页结构

从页到索引

如果想查一条record，我们可以把表空间里每一页都捞出来，再把里面的record捞出来挨个判断是不是我们要找的。

行数量小的时候，这么操作也没啥问题。

行数量大了，性能就慢了，于是为了加速搜索，我们可以在每个数据页里选出主键id最小的record，而且只需要它们的主键id和所在页的页号。组成新的record，放入到一个新生成的一个数据页中，这个新数据页跟之前的页结构没啥区别，而且大小还是16k。

但为了跟之前的数据页进行区分。数据页里加入了页层级（page level）的信息，从0开始往上算。于是页与页之间就有了上下层级的概念，就像下面这样。

两层B+树结构

突然页跟页之间看起来就像是一棵倒过来的树了。也就是我们常说的B+树索引。

最下面那一层，page level 为0，也就是所谓的叶子结点，其余都叫非叶子结点。

上面展示的是两层的树，如果数据变多了，我们还可以再通过类似的方法，再往上构建一层。就成了三层的树。

三层B+树结构

那现在我们就可以通过这样一棵B+树加速查询。举个例子。

比方说我们想要查找行数据5。会先从顶层页的record们入手。record里包含了主键id和页号（页地址）。看下图黄色的箭头，向左最小id是1，向右最小id是7。那id=5的数据如果存在，那必定在左边箭头。于是顺着的record的页地址就到了6号数据页里，再判断id=5>4，所以肯定在右边的数据页里，于是加载105号数据页。在数据页里找到id=5的数据行，完成查询。