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

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

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

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

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

1. 不必要的磁盘I/O

咱们晓得 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大小罢了，不会因为多几个字段就占用额定的内存空间。

2. 减轻网络时延

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

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

3. 无奈应用笼罩索引

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

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，示意咱们的查问列表以及搜寻条件中只蕴含属于某个索引的列，也就是应用了笼罩索引，可能间接摒弃回表操作，大幅度提高查问效率。

4. 可能拖慢JOIN连贯查问

咱们创立两张表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表作为被驱动表

对于连贯查问而言，驱动表只会被拜访一遍，而被驱动表却要被拜访好多遍，具体的拜访次数取决于驱动表中合乎查问记录的记录条数。因为曾经强制确定了驱动表和被驱动表，上面咱们说一下两表连贯的实质：

  1. t1作为驱动表，针对驱动表的过滤条件，执行对t1表的查问。因为没有过滤条件，也就是获取t1表的所有数据；
  2. 对上一步中获取到的后果集中的每一条记录，都别离到被驱动表中，依据连贯过滤条件查找匹配记录

用伪代码示意的话整个过程是这样的：

// 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中搁置更多的记录，缩小分批的次数，也就天然缩小了对被驱动表的拜访次数。

举荐浏览

  • 用好MySQL索引，你必须晓得的一些事件
  • 图解｜这次，彻底了解MySQL的索引
  • 图解｜12张图解释MySQL主键查问为什么这么快
  • 一条SQL更新语句是如何执行的？
  • 一条SQL查问语句是如何执行的？