数据库查询，为什么不推荐使用SELECT *？

terry 2年前 (2023-09-26) 阅读数 48 #数据库

来源：Chanmufeng1994

“不要使用SELECT *”几乎成为使用MySQL的金科玉律。连《阿里Java开发手册》都明确表示禁止使用。 *作为查询字段列表，这条规则有权威加持。阿里巴巴Java开发手册

但是我在开发过程中直接使用SELECT *。原因有两个：

因为简单，所以开发效率很高，如果以后发生的话。
我认为过早优化是一个坏习惯，除非你能从一开始就确定你真正需要哪些字段并为它们创建适当的索引。 ;否则，如果遇到问题，我会选择优化 SQL。当然，基本前提是问题不是致命的。

但是我们总是需要知道为什么不建议直接使用SELECT *。本文从四个方面论证了这一点。

1。冗余磁盘I/O

我们知道MySQL本质上是将用户记录存储在磁盘上，因此查询操作就是执行磁盘IO（前提是要查询的记录没有缓存在内存中）。

查询的字段越多，需要读取的内容就越多，从而增加了磁盘IO开销。特别是如果某些字段是 TEXT、MIDTEXT 或 BLOB 等。类型。

MySQL使用SELECT *会占用更多内存吗？

理论上不会，因为在Server层的情况下，并不是将整组结果存储在内存中，然后同时传输到客户端。相反，每当存储引擎接收到一行时，它就会进入一个名为的文件。 net_buffer内存区域中，内存大小由系统变量net_buffer_length控制。。默认为 16 KB；如果 net_buffer 已满，系统将写入本地网络堆栈内存。写入套接字发送缓冲区的数据被发送到客户端。发送成功后（客户端扫描完成），net_buffer被清零，然后继续读写下一行。

也就是说，默认情况下结果集占用的最大内存空间只有net_buffer_length。由于字段较少，因此不会占用额外的内存空间。

`2。加剧网络延迟`

继上一点之后，虽然socket发送缓冲区的数据每次都发送到客户端，但单次数据量虽然不大，但却难以承受。实际上有人使用 * 来搜索 TEXT、MIDTEXT 或 BLOB 类型的字段。数据总量大，直接导致网络传输。出现的次数有所增加。

如果 MySQL 和应用程序不在同一台计算机上，则此开销非常明显。即使MySQL服务器和客户端在同一台机器上，使用的协议仍然是TCP，通信需要额外的时间。

`3。不能使用覆盖索引`

为了说明问题，我们需要创建一个表为主键，为name和phone创建公共索引。最后在表中随机初始化了500W+块数据。

InnoDB 自动创建一棵 B+ 树作为主键索引（又名聚集索引）作为主键id。这棵B+树最重要的特点就是叶子节点包含完整的用户记录，大概是这样的。？索引，这是二级索引。二级索引的叶子节点如下所示：

InnoDB 存储引擎在二级叶子节点中找到 name Kabóca 沐风的记录。二级索引仅记录字段name、phone和主键idid SELECT♶（谁请求了 ♶），因此InnoDB - 必须使用主键 id 在主键索引中查找整个记录。这个过程的名称是回到表。

我们想一下，如果二级索引的叶子节点包含了所有需要的数据，那么表不应该被返回吗？是的，这就是封面索引。

例如，我们恰好只想搜索字段 name、phone 和主键字段。

SELECT id, name,  phone FROM user_innodb WHERE name = "蝉沐风";

使用命令EXPLAIN查看语句的执行计划：

可以看到Extra列显示了，这意味着我们只使用了索引。包含某些Indexed列的项，即通过使用覆盖索引，可以直接消除返表操作，大大提高查询效率。

`4。可以减慢JOIN连接查询速度`

我们创建两张表t1、t2对下面的问题进行join操作0♶♶♶♶1s并且数据录入在

表中输入1000条数据，在t2中输入1000条数据。 
CREATE TABLE `t1` (
  `id` int NOT NULL,
  `m` int DEFAULT NULL,
  `n` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT;

CREATE TABLE `t2` (
  `id` int NOT NULL,
  `m` int DEFAULT NULL,
  `n` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT;
如果我们执行如下语句： 
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.m = t2.m;
这里我使用了STRAIGHT_JOIN命令t1作为控制表，t2❙
作为连接查询一般来说，控制表可以只能访问一次，但折叠桌可以多次访问。具体的访问次数取决于查询记录对应的控制表中的记录条数。既然主表和折叠表已经是必然确定的，那么我们就来说说两个表之间关系的本质： 
t1作为主表，基于表t1进行查询关于主表的过滤条件。由于没有过滤条件，即我们得到了表中的所有数据t1； 
 将上一步得到的结果集的每条记录分别传输到受控表中，并根据连接过滤器的条件进行查找。匹配记录
的整个过程用伪代码表达如下： 
// t1Res是针对驱动表t1过滤之后的结果集
for (t1Row : t1Res){
  // t2是完整的被驱动表
  for(t2Row : t2){
   if (满足join条件 && 满足t2的过滤条件){
      发送给客户端
    }  
  }
}
 这种方法最简单，但同时性能也最差。此方法称为 Nested-LoopJoin(Nested-LoopJoin, NLJ)。如何加快连接速度？ 
一种方法是创建索引。最好在受控表属于连接条件的字段上创建索引（t2）。毕竟折叠板必须被多次查询，而这对于折叠板来说非常重要。表访问本质上是单表查询（由于定义了结果集t1，因此也定义了t2相关的各个连接的查询条件）。 
由于我们使用了索引，为了避免无法使用覆盖索引的错误，不要直接SELECT *，而是使用实际用作查询列的字段并创建。他们正确的索引。
但是如果我们不使用索引，MySQL真的会像嵌套循环查询一样执行连接查询吗？当然不是，毕竟这种嵌套循环查询太慢了！ 
在MySQL8.0之前，MySQL提供了基于块的嵌套循环连接（Block Nested-Loop Join，BLJ）方法，MySQL8.0引入了in♷dhain♷，它是其中一种提出了问题的解决方案，即最小化对受控表的访问次数。 
两种方法都使用一个固定大小的内存区域，称为join buffer，用于存储驱动表结果集中的多条记录（两种方法的区别只是存储形式不同） ，这样，当受控表记录加载到内存中时，就会同时与多个控制表中的join buffer中的记录进行匹配，因为匹配过程是在内存中的。完成了，所以这样可以显着降低驱动板的I/O成本，并且大大降低从磁盘重新加载驱动板的成本。 join buffer的使用流程如下图所示： join buffer图
我们查看了上面join查询的执行计划，发现if♷（前提At2表的连接查询字段没有创建索引，否则会使用索引，并且不会使用连接缓冲区）。 
最好的情况是连接缓冲区足够大，可以容纳控制表结果集中的所有记录，因此控制表只需要访问一次即可完成连接操作。可以使用 join_buffer_size 系统变量进行设置。默认大小为256 KB。如果还是无法加载，则必须将驱动表的结果集批量放入join buffer。内存中比较完成后，清除连接缓冲区并加载以下结果。设置直至连接完成。
重点来了！并非驱动程序表记录的所有列都放置在连接缓冲区中。只有查询列表列和过滤条件列会放入连接缓冲区，所以再次提醒我们，最好不要使用查询列表*。 。我们只需要把对我们重要的列放在查询列表中，这样我们就可以将更多的放入join buffer记录中，减少batch的数量，自然也就减少了对受控对象的访问次数。桌子。 。