关于mysql:到底为什么不建议使用SELECT

9次阅读

共计 3744 个字符，预计需要花费 10 分钟才能阅读完成。

作者「蝉沐风」，一个认真写文章的技术人
公众号 @蝉沐风

“不要应用 SELECT *”简直曾经成为了 MySQL 应用的一条清规戒律，就连《阿里 Java 开发手册》也明确示意不得应用* 作为查问的字段列表，更是让这条规定领有了权威的加持。

不过我在开发过程中间接应用 SELECT * 还是比拟多的，起因有两个：

因为简略，开发效率十分高，而且如果前期频繁增加或批改字段，SQL 语句也不须要扭转；
我认为过早优化是个不好的习惯，除非在一开始就能确定你最终理论须要的字段是什么，并为之建设失当的索引；否则，我抉择遇到麻烦的时候再对 SQL 进行优化，当然前提是这个麻烦并不致命。

然而咱们总得晓得为什么不倡议间接应用SELECT *，本文从 4 个方面给出理由。

咱们晓得 MySQL 实质上是将用户记录存储在磁盘上，因而查问操作就是一种进行磁盘 IO 的行为（前提是要查问的记录没有缓存在内存中）。

查问的字段越多，阐明要读取的内容也就越多，因而会增大磁盘 IO 开销。尤其是当某些字段是 TEXT、MEDIUMTEXT或者BLOB 等类型的时候，成果尤为显著。

那应用 SELECT * 会不会使 MySQL 占用更多的内存呢？

实践上不会，因为对于 Server 层而言，并非是在内存中存储残缺的后果集之后一下子传给客户端，而是每从存储引擎获取到一行，就写到一个叫做 net_buffer 的内存空间中，这个内存的大小由零碎变量 net_buffer_length 来管制，默认是 16KB；当 net_buffer 写满之后再往本地网络栈的内存空间 socket send buffer 中写数据发送给客户端，发送胜利（客户端读取实现）后清空net_buffer，而后持续读取下一行并写入。

也就是说，默认状况下，后果集占用的内存空间最大不过是 net_buffer_length 大小罢了，不会因为多几个字段就占用额定的内存空间。

承接上一点，尽管每次都是把 socket send buffer 中的数据发送给客户端，单次看来数据量不大，可架不住真的有人用 * 把 TEXT、MEDIUMTEXT 或者BLOB 类型的字段也查出来了，总数据量大了，这就间接导致网络传输的次数变多了。

如果 MySQL 和应用程序不在同一台机器，这种开销非常明显。即便 MySQL 服务器和客户端是在同一台机器上，应用的协定还是 TCP，通信也是须要额定的工夫。

为了阐明这个问题，咱们须要建一个表

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_NAME_PHONE` (`name`,`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

咱们创立了一个存储引擎为 InnoDB 的表 user_innodb，并设置id 为主键，另外为 name 和phone创立了联结索引，最初向表中随机初始化了 500W+ 条数据。

InnoDB 会主动为主键 id 创立一棵名为主键索引（又叫做聚簇索引）的 B + 树，这个 B + 树的最重要的特点就是叶子节点蕴含了残缺的用户记录，大略长这个样子。

如果咱们执行这个语句

SELECT * FROM user_innodb WHERE name = '蝉沐风';

应用 EXPLAIN 查看一下语句的执行打算：

发现这个 SQL 语句会应用到 IDX_NAME_PHONE 索引，这是一个二级索引。二级索引的叶子节点长这个样子：

InnoDB 存储引擎会依据搜寻条件在该二级索引的叶子节点中找到 name 为蝉沐风 的记录，然而二级索引中只记录了 name、phone 和主键 id 字段（谁让咱们用的是 SELECT * 呢），因而 InnoDB 须要拿着主键 id 去主键索引中查找这一条残缺的记录，这个过程叫做回表。

想一下，如果二级索引的叶子节点上有咱们想要的所有数据，是不是就不须要回表了呢？是的，这就是 笼罩索引。

举个例子，咱们恰好只想搜寻 name、phone 以及主键字段。

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

应用 EXPLAIN 查看一下语句的执行打算：

能够看到 Extra 一列显示Using index，示意咱们的查问列表以及搜寻条件中只蕴含属于某个索引的列，也就是应用了笼罩索引，可能间接摒弃回表操作，大幅度提高查问效率。

咱们创立两张表 t1，t2 进行连贯操作来阐明接下来的问题，并向 t1 表中插入了 100 条数据，向 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，NLJ）。怎么放慢连贯速度呢？

其中一个方法就是创立索引，最好是在被驱动表（t2）连贯条件波及到的字段上创立索引，毕竟被驱动表须要被查问好屡次，而且对被驱动表的拜访实质上就是个单表查问而已（因为 t1 后果集定了，每次连贯 t2 的查问条件也就定死了）。

既然应用了索引，为了防止重蹈无奈应用笼罩索引的覆辙，咱们也应该尽量不要间接SELECT *，而是将真正用到的字段作为查问列，并为其建设适当的索引。

然而如果咱们不应用索引，MySQL 就真的依照嵌套循环查问的形式进行连贯查问吗？当然不是，毕竟这种嵌套循环查问切实是太慢了！

在 MySQL8.0 之前，MySQL 提供了 基于块的嵌套循环连贯 （Block Nested-Loop Join，BLJ）办法，MySQL8.0 又推出了hash join 办法，这两种办法都是为了解决一个问题而提出的，那就是尽量减少被驱动表的拜访次数。

这两种办法都用到了一个叫做 join buffer 的固定大小的内存区域，其中存储着若干条驱动表后果集中的记录（这两种办法的区别就是存储的模式不同而已），如此一来，把被驱动表的记录加载到内存的时候，一次性和 join buffer 中多条驱动表中的记录做匹配，因为匹配的过程都是在内存中实现的，所以这样能够显著缩小被驱动表的 I / O 代价，大大减少了反复从磁盘上加载被驱动表的代价。应用 join buffer 的过程如下图所示：

咱们看一下下面的连贯查问的执行打算，发现的确应用到了 hash join（前提是没有为t2 表的连贯查问字段创立索引，否则就会应用索引，不会应用join buffer）。

最好的状况是 join buffer 足够大，能包容驱动表后果集中的所有记录，这样只须要拜访一次被驱动表就能够实现连贯操作了。咱们能够应用 join_buffer_size 这个零碎变量进行配置，默认大小为 256KB。如果还装不下，就得分批把驱动表的后果集放到join buffer 中了，在内存中比照实现之后，清空 join buffer 再装入下一批后果集，直到连贯实现为止。

重点来了！并不是驱动表记录的所有列都会被放到 join buffer 中，只有查问列表中的列和过滤条件中的列才会被放到 join buffer 中，所以再次揭示咱们，最好不要把 * 作为查问列表，只须要把咱们关怀的列放到查问列表就好了，这样还能够在 join buffer 中搁置更多的记录，缩小分批的次数，也就天然缩小了对被驱动表的拜访次数。