你的mysql到底能存多少数据呢?

前言

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

作者:阿杆
链接:https://juejin.***/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的默认行格式为***PACT(紧凑),5.7及以后的默认行格式为DYNAMIC(动态),不同的行格式存储的方式也是有区别的,还有其他的两种行格式,本文后续的内容主要是基于DYNAMIC(动态)进行讲解的。(了解即可)

特性对比(了解即可):

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

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

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

    列是否存储在页外取决于页大小和行的总大小。当一行太长时,选择最长的列进行页外存储,直到聚集索引记录适合 B+ 树页(文档里没说具体是多少😅)。小于或等于 40 字节的 TEXT 和 BLOB 直接存储在行内,不会分页。

言归正传:每一行的基本格式,总结表格如下:

重点是事务ID和指针字段、行记录头信息 ,计算的时候要用,因为固定的值也要算上。

字符编码不同情况下的存储(重要)

char 、varchar、text 等需要设置字符编码的类型,在计算所占用空间时,需要考虑不同编码所占用的空间。

  • varchar、text等类型会有长度字段列表来记录他们所占用的长度,但char是固定长度的类型,情况比较特殊,假设字段 name 的类型为 char(10) ,则有以下情况:
  • 对于长度固定的字符编码(比如ASCII码),字段 name 将以固定长度格式存储,ASCII码每个字符占一个字节,那 name 就是占用 10 个字节。
  • 对于长度不固定的字符编码(比如utf8mb4),至少将为 name 保留 10 个字节。如果可以,InnoDB会通过修剪尾部空格空间的方式来将其存到 10 个字节中。
    如果空格剪完了还存不下,则将尾随空格修剪为 列值字节长度的最小值(一般是 1 字节)。
    列的最大长度为: 字符编码的最大字符长度×N,比如 name 字段的编码为 utf8mb4,那就是 4×10。
  • 大于或等于 768 字节的 char 列会被看成是可变长度字段(就像varchar一样),可以跨页存储。例如,utf8mb4 字符集的最大字节长度为 4,则 char(255) 列将可能会超过 768 个字节,进行跨页存储。

总结一下:

字符编码不同,字段所占用的字节也会不同,固定字符编码(比如ASCII码)就是写多少就是多少,char(10),就占用10个字节,不固定的(比如utf8mb4)超过10个字节就用字符编码的最大字符长度(utf8mb4的这个值是4,结尾的mb4就代表4个字节,这个可以自己去查)x N,那么char(20)占用字节=4 x 20 =80,如果结果大于768,那么就会跨页存储(知道就行)。

计算

计算InnoDB的3层B+树最多可以存多少条数据
计算之前,先回顾一下前边的东西,页格式和行格式;
存储总量 = 可变数据 + 固定数据;
可变数据就是我们实际要存储的数据,固定数据就是内置好的数据,从上边的两个表格就能看到了。

通过页格式的介绍,我们知道,刨除固定数据量之后,我们得到的剩余存储空间是15232字节

那么,我们开始计算不固定的数据量,也就是我们要存储的实际信息,这个就只能举例说明了。

  • 简单一例:

先计算单个节点的:
假设我们的主键id为 bigint 型,也就是8个字节;
那索引页中每行数据占用的空间就等于 8 + 6(事物ID[上表有写]) + 5(行记录头) = 19 字节。
每页可以存 15232 ÷ 19 ≈ 801 条索引数据。
那算上页目录的话,按每个槽平均6条数据计算的话,至少有 801 ÷ 6 ≈ 134 个槽,需要占用 268 字节的空间。
所以最终的结果是(15232-268)÷ 19 ≈ 787条索引数据;
三层的数据量:
三层的叶子节点就是787²(反正就是这么算的,不用管,记住公式),也就是787x787= 619369 个叶子节点,每个节点可以存储787条数据,最终能存储 787 x 619369 = (自己算)条数据。
主键为 int 的表可以存放 993 ^ 2 = 986049 个叶子节点,下边会用。

  • 简单二例:

– 这是一张非常普通的课程安排表,除id外,仅包含了课程id和老师id两个字段
– 且这几个字段均为 int (4个字节)型(当然实际生产中不会这么设计表,这里只是举例)。
CREATE TABLE course_schedule (
id int NOT NULL,
teacher_id int NOT NULL,
course_id int NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

每行数据所占用的空间就是 4 + 4 + 4 + 6 + 7 + 5 = 30 字节,每个叶子节点可以存放 15232÷30≈507条数据。
页目录占用空间:507 ÷ 685 x 2 = 170字节
算上页目录的槽位所占空间,每个叶子节点可以存放 (15232 - 170) ÷ 30502 条数据;
那么三层B+树可以存放的最大数据量就是 502×986049(主键是int)=494,996,将近5亿条数据!
  • 常规一例:

CREATE TABLE blog (
id bigint unsigned NOT NULL AUTO_INCREMENT ***MENT ‘博客id’,
author_id bigint unsigned NOT NULL ***MENT ‘作者id’,
title varchar(50) CHARACTER SET utf8mb4 NOT NULL ***MENT ‘标题’,
description varchar(250) CHARACTER SET utf8mb4 NOT NULL ***MENT ‘描述’,
school_code bigint unsigned DEFAULT NULL ***MENT ‘院校代码’,
cover_image char(32) DEFAULT NULL ***MENT ‘封面图’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ***MENT ‘创建时间’,
release_time datetime DEFAULT NULL ***MENT ‘首次发表时间’,
modified_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ***MENT ‘修改时间’,
status tinyint unsigned NOT NULL ***MENT ‘发表状态’,
is_delete tinyint unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY author_id (author_id),
KEY school_code (school_code) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

1、行记录头信息:肯定得有,占用5字节。
2、可变长度字段列表:表中 title占用1字节,description占用2字节(虽然看着没超过255,但是用的是utf8mb4编码,所以实际是255 x 4,所以占用两个字节),共3字节。
3、null值列表:表中仅school_code、cover_image、release_time3个字段可为null,故仅占用1字节。
4、事务ID和指针字段:两个都得有,占用13字节。
5、字段内容信息:

  • id、author_id、school_code 均为bigint型,各占用8字节,共24字节。
  • create_time、release_time、modified_time 均为datetime类型,各占8字节,共24字节。
  • status、is_delete 为tinyint类型,各占用1字节,共2字节。
  • cover_image 为char(32),字符编码为表默认值utf8,由于该字段实际存的内容仅为英文字母(存url的),结合前面讲的字符编码不同情况下的存储 ,故仅占用32字节。
  • title、description 分别为varchar(50)、varchar(250),这两个应该都不会产生溢出页(不太确定),字符编码均为utf8mb4,实际生产中70%以上都是存的中文(3字节),25%为英文(1字节),还有5%为4字节的表情,则存满的情况下将占用:
    (50 + 250) × ( 0.7 × 3 + 0.25 × 1 + 0.05 × 4) = 765 字节。
统计上面的所有分析,共占用 869 字节,则每个叶子节点可以存放 15232 ÷869≈17条,算上页目录,仍然能放 17 条。
则三层B+树可以存放的最大数据量就是 17 × 619369(根据主键是bigint) = 10,529,27317,约一千万条数据
转载请说明出处内容投诉
CSS教程_站长资源网 » 你的mysql到底能存多少数据呢?

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买