为什么MySQL不建议使用SELECT-木庄网络博客

本文整理自网络，侵删。

因为简单，开发效率非常高，而且如果后期频繁添加或修改字段，SQL语句也不需要改变；
我认为过早优化是个不好的习惯，除非在一开始就能确定你最终实际需要的字段是什么，并为之建立恰当的索引；否则，我选择遇到麻烦的时候再对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查看一下语句的执行计划：

阅读剩余部分

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » 为什么MySQL不建议使用SELECT

为什么MySQL不建议使用SELECT

目录

1. 不必要的磁盘I/O

2. 加重网络时延

3. 无法使用覆盖索引

数据库系统概念第6版

相关推荐

评论