MySQL中不建議使用SELECT *的原因是什麼

「不要使用SELECT *」幾乎已經成為了MySQL使用的一條金科玉律，就連《阿里Java開發手冊》也明確表示不得使用*作為查詢的字段列表，更是讓這條規則擁有了權威的加持。

不過我在開發過程中直接使用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查看一下语句的执行计划：

可以看到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表作为被驱动表

对于连接查询而言，驱动表只会被访问一遍，而被驱动表却要被访问好多遍，具体的访问次数取决于驱动表中符合查询记录的记录条数。现在，我们来讲一下两个表连接的本质，因为驱动表和被驱动表已经被强制确定

• 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中放置更多的记录，减少分批的次数，也就自然减少了对被驱动表的访问次数

以上是MySQL中不建議使用SELECT *的原因是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！