乐趣区

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

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

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

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

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

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

1. 不必要的磁盘 I /O

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

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

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

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

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

2. 减轻网络时延

承接上一点,尽管每次都是把 socket send buffer 中的数据发送给客户端,单次看来数据量不大,可架不住真的有人用 * 把 TEXTMEDIUMTEXT 或者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 为主键,另外为 namephone创立了联结索引,最初向表中随机初始化了 500W+ 条数据。

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

如果咱们执行这个语句

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

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

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

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

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

举个例子,咱们恰好只想搜寻 namephone 以及主键字段。

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

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

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

4. 可能拖慢 JOIN 连贯查问

咱们创立两张表 t1t2 进行连贯操作来阐明接下来的问题,并向 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 查问语句是如何执行的?
退出移动版