一条这样的SQL语句最多能查询出来多少条记录？

THUNDER王

发布于 2023-10-13 09:11:05

3490

发布于 2023-10-13 09:11:05

💂作者简介： THUNDER王，一名热爱财税和SAP ABAP编程以及热爱分享的博主。目前于江西师范大学本科在读，同时任汉硕云（广东）科技有限公司ABAP开发顾问。在学习工作中，我通常使用偏后端的开发语言ABAP，SQL进行任务的完成，对SAP企业管理系统，SAP ABAP开发和数据库具有较深入的研究。

前言

今天突发奇想，一条这样的 SQL 语句【SELECT * FROM user】能查询出多少条记录？虽然在实际业务操作中我们不会这么干，尤其对于数据量大的表不会这样干，但这是个值得想一想的问题。

寻找答案之旅

PS: 以下所涉及资料全部基于 MySQL 8

max_allowed_packet

在查询资料的过程中发现了这个参数 max_allowed_packet

上图参考了 MySQL 的官方文档，根据文档我们知道：

MySQL 客户端 max_allowed_packet值的默认大小为 16M（不同的客户端可能有不同的默认值，但最大不能超过 1G）
MySQL 服务端 max_allowed_packet·值的默认大小为 64M
max_allowed_packet值最大可以设置为 1G（1024 的倍数）

然而，根据上图的文档中所述：

The maximum size of one packet or any generated/intermediate string,or any parameter sent by the mysql_smt_send_long_data() C API function

one packet
generated/intermediate string
any parameter sent by the mysql_smt_send_long_data() C API function

这三个东东具体都是什么呢？ packet 到底是结果集大小，还是网络包大小还是什么？于是 google 了一下，搜索排名第一的是这个：

根据 “Packet Too Large” 的说明，通信包 (communication packet) 的定义如下：

是一个被发送到 MySQL 服务器的单个 SQL 语句
或者是一个被发送到客户端的单行记录
又或者是一个从主服务器 (replication source server) 被发送到从属服务器 (replica) 的二进制日志事件

1、3 点好理解，这也同时解释了，如果你发送的一条 SQL 语句特别大可能会执行不成功的原因，尤其是insert update 这种，单个 SQL 语句不是没有上限的，不过这种情况一般不是因为 SQL 语句写的太长，主要是由于某个字段的值过大，比如有 BLOB 字段。

那么第 2 点呢，单行记录，默认值是 64M，会不会太大了啊，一行记录有可能这么大的吗？有必要设置这么大吗？单行最大存储空间限制又是多少呢？

单行最大存储空间

MySQL 单行最大宽度是 65535 个字节，也就是 64KB 。无论是 InnoDB 引擎还是 MyISAM 引擎。

通过上图可以看到超过 65535不行，不过请注意其中的错误提示：“Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535”，如果字段是变长类型的如 BLOB 和 TEXT 就不包括了，那么我们试一下用和上图一样的字段长度，只把最后一个字段的类型改成 BLOB 和 TEXT

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

可见无论是改成 BLOB 还是 TEXT 都可以成功。但这里请注意，字符集是 latin1 可以成功，如果换成 utf8mb4 或者 utf8mb3 就不行了，会报错，仍然是：“Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.”为什么呢？

因为虽然不包括 TEXT 和 BLOB, 但总长度还是超了！

我们先看一下这个熟悉的 VARCHAR(255) ，你有没有想过为什么用 255，不用 256？

在 4.0 版本以下，varchar(255) 指的是 255 个字节，使用 1 个字节存储长度即可。当大于等于 256 时，要使用 2 个字节存储长度。所以定义 varchar(255) 比 varchar(256) 更好。但是在 5.0 版本以上，varchar(255) 指的是 255 个字符，每个字符可能占用多个字节，例如使用 UTF8 编码时每个汉字占用 3 字节，使用 GBK 编码时每个汉字占 2 字节。

例子中我们用的是 MySQL8 ，由于字符集是 utf8mb3 ，存储一个字要用三个字节，长度为 255 的话（列宽），总长度要 765 字节，再加上用 2 个字节存储长度，那么这个列的总长度就是 767 字节。所以用 latin1 可以成功，是因为一个字符对应一个字节，而 utf8mb3 或 utf8mb4 一个字符对应三个或四个字节，VARCHAR(10000) 就可能等于要占用 30000 多 40000 多字节，比原来大了 3、4 倍，肯定放不下了。

另外，还有一个要求，列的宽度不要超过 MySQL 页大小 （默认 16K）的一半，要比一半小一点儿。例如，对于默认的 16KB InnoDB 页面大小，最大行大小略小于 8KB。

下面这个例子就是超过了一半，所以报错，当然解决办法也在提示中给出了。

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

那么为什么是 8K，不是 7K，也不是 9K 呢？ 这么设计的原因可能是：MySQL 想让一个数据页中能存放更多的数据行，至少也得要存放两行数据（16K）。否则就失去了 B+Tree 的意义。B+Tree 会退化成一个低效的链表。

你可能还会奇怪，不超过 8K ？你前面的例子明明都快 64K 也能存下，那 8K 到 64K 中间这部分怎么解释？

答：如果包含可变长度列的行超过 InnoDB 最大行大小， InnoDB 会选择可变长度列进行页外存储，直到该行适合 InnoDB ，这也就是为什么前面有超过 8K 的也能成功，那是因为用的是VARCHAR这种可变长度类型。
当你往这个数据页中写入一行数据时，即使它很大将达到了数据页的极限，但是通过行溢出机制。依然能保证你的下一条数据还能写入到这个数据页中。

我们通过 Compact 格式，简单了解一下什么是 页外存储和 行溢出

MySQL8 InnoDB 引擎目前有 4 种行记录格式：

REDUNDANT
COMPACT
DYNAMIC（默认 default 是这个）
COMPRESSED

行记录格式 决定了其行的物理存储方式，这反过来又会影响查询和 DML 操作的性能。

Compact 格式的实现思路是：当列的类型为 VARCHAR、 VARBINARY、 BLOB、TEXT 时，该列超过 768byte 的数据放到其他数据页中去。

在 MySQL 设定中，当 varchar 列长度达到 768byte 后，会将该列的前 768byte 当作当作 prefix 存放在行中，多出来的数据溢出存放到溢出页中，然后通过一个偏移量指针将两者关联起来，这就是行溢出机制

PS: 假如你要存储的数据行很大超过了 65532byte 那么你是写入不进去的。假如你要存储的单行数据小于 65535byte 但是大于 16384byte，这时你可以成功 insert，但是一个数据页又存储不了你插入的数据。这时肯定会行溢出！

MySQL 这样做，有效的防止了单个 varchar 列或者 Text 列太大导致单个数据页中存放的行记录过少的情况，避免了 IO 飙升的窘境。

单行最大列数限制

mysql 单表最大列数也是有限制的，是 4096 ，但 InnoDB 是 1017

实验

前文中我们疑惑 max_allowed_packet 在 MySQL8 的默认值是 64M，又说这是限制单行数据的，单行数据有这么大吗？在前文我们介绍了行溢出，由于有了行溢出，单行数据确实有可能比较大。

那么还剩下一个问题，max_allowed_packet 限制的确定是单行数据吗，难道不是查询结果集的大小吗 ? 下面我们做个实验，验证一下。

（一）建表

CREATE TABLE t1 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(192)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;

经过测试虽然提示的是Row size too large (> 8126)但如果全部长度加起来是 8126 建表不成功，最终我试到 8097 是能建表成功的。为什么不是 8126 呢？可能是还需要存储一些其他的东西占了一些字节吧，比如隐藏字段什么的。

（二）用存储过程造一些测试数据，把表中的所有列填满

create
    definer = root@`%` procedure generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE col_value TEXT DEFAULT REPEAT('a', 255);  
  WHILE i < 5 DO
    INSERT INTO t1 VALUES
    (
      col_value, col_value, col_value,
      col_value, REPEAT('b', 192)
    );
    SET i = i + 1;
  END WHILE;
END;

将 max_allowed_packet设置的小一些，先用 show VARIABLES like '%max_allowed_packet%'; 看一下当前的大小，我的是67108864 这个单位是字节，等于 64M，然后用 set global max_allowed_packet =1024将它设置成允许的最小值 1024 byte。设置好后，关闭当前查询窗口再新建一个，然后再查看：