到底為什么不建議使用SELECT * ？

來(lái)源：蟬沐風(fēng)（ID：chanmufeng1994）

Hollis的新書(shū)限時(shí)折扣中，一本深入講解Java基礎(chǔ)的干貨筆記！

“不要使用SELECT *”幾乎已經(jīng)成為了使用MySQL的一條金科玉律，就連《阿里Java開(kāi)發(fā)手冊(cè)》也明確表示不得使用*作為查詢的字段列表，更是讓這條規(guī)則擁有了權(quán)威的加持。

不過(guò)我在開(kāi)發(fā)過(guò)程中直接使用SELECT *還是比較多的，原因有兩個(gè)：

1. 因?yàn)楹?jiǎn)單，開(kāi)發(fā)效率非常高，而且如果后期頻繁添加或修改字段，SQL語(yǔ)句也不需要改變；
2. 我認(rèn)為過(guò)早優(yōu)化是個(gè)不好的習(xí)慣，除非在一開(kāi)始就能確定你最終實(shí)際需要的字段是什么，并為之建立恰當(dāng)?shù)乃饕?；否則，我選擇遇到麻煩的時(shí)候再對(duì)SQL進(jìn)行優(yōu)化，當(dāng)然前提是這個(gè)麻煩并不致命。

但是我們總得知道為什么不建議直接使用SELECT *，本文從4個(gè)方面給出理由。

1. 不必要的磁盤(pán)I/O

我們知道 MySQL 本質(zhì)上是將用戶記錄存儲(chǔ)在磁盤(pán)上，因此查詢操作就是一種進(jìn)行磁盤(pán)IO的行為（前提是要查詢的記錄沒(méi)有緩存在內(nèi)存中）。

查詢的字段越多，說(shuō)明要讀取的內(nèi)容也就越多，因此會(huì)增大磁盤(pán) IO 開(kāi)銷。尤其是當(dāng)某些字段是 TEXT、MEDIUMTEXT或者BLOB 等類型的時(shí)候，效果尤為明顯。

那使用SELECT *會(huì)不會(huì)使MySQL占用更多的內(nèi)存呢？

理論上不會(huì)，因?yàn)閷?duì)于Server層而言，并非是在內(nèi)存中存儲(chǔ)完整的結(jié)果集之后一下子傳給客戶端，而是每從存儲(chǔ)引擎獲取到一行，就寫(xiě)到一個(gè)叫做net_buffer的內(nèi)存空間中，這個(gè)內(nèi)存的大小由系統(tǒng)變量net_buffer_length來(lái)控制，默認(rèn)是16KB；當(dāng)net_buffer寫(xiě)滿之后再往本地網(wǎng)絡(luò)棧的內(nèi)存空間socket send buffer中寫(xiě)數(shù)據(jù)發(fā)送給客戶端，發(fā)送成功（客戶端讀取完成）后清空net_buffer，然后繼續(xù)讀取下一行并寫(xiě)入。

也就是說(shuō)，默認(rèn)情況下，結(jié)果集占用的內(nèi)存空間最大不過(guò)是net_buffer_length大小罷了，不會(huì)因?yàn)槎鄮讉€(gè)字段就占用額外的內(nèi)存空間。

2. 加重網(wǎng)絡(luò)時(shí)延

承接上一點(diǎn)，雖然每次都是把socket send buffer中的數(shù)據(jù)發(fā)送給客戶端，單次看來(lái)數(shù)據(jù)量不大，可架不住真的有人用*把TEXT、MEDIUMTEXT或者BLOB 類型的字段也查出來(lái)了，總數(shù)據(jù)量大了，這就直接導(dǎo)致網(wǎng)絡(luò)傳輸?shù)拇螖?shù)變多了。

如果MySQL和應(yīng)用程序不在同一臺(tái)機(jī)器，這種開(kāi)銷非常明顯。即使MySQL服務(wù)器和客戶端是在同一臺(tái)機(jī)器上，使用的協(xié)議還是TCP，通信也是需要額外的時(shí)間。

3. 無(wú)法使用覆蓋索引

為了說(shuō)明這個(gè)問(wèn)題，我們需要建一個(gè)表

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;

我們創(chuàng)建了一個(gè)存儲(chǔ)引擎為InnoDB的表user_innodb，并設(shè)置id為主鍵，另外為name和phone創(chuàng)建了聯(lián)合索引，最后向表中隨機(jī)初始化了500W+條數(shù)據(jù)。

InnoDB會(huì)自動(dòng)為主鍵id創(chuàng)建一棵名為主鍵索引（又叫做聚簇索引）的B+樹(shù)，這個(gè)B+樹(shù)的最重要的特點(diǎn)就是葉子節(jié)點(diǎn)包含了完整的用戶記錄，大概長(zhǎng)這個(gè)樣子。

如果我們執(zhí)行這個(gè)語(yǔ)句

SELECT * FROM user_innodb WHERE name = '蟬沐風(fēng)';

使用EXPLAIN查看一下語(yǔ)句的執(zhí)行計(jì)劃：

發(fā)現(xiàn)這個(gè)SQL語(yǔ)句會(huì)使用到IDX_NAME_PHONE索引，這是一個(gè)二級(jí)索引。二級(jí)索引的葉子節(jié)點(diǎn)長(zhǎng)這個(gè)樣子：

InnoDB存儲(chǔ)引擎會(huì)根據(jù)搜索條件在該二級(jí)索引的葉子節(jié)點(diǎn)中找到name為蟬沐風(fēng)的記錄，但是二級(jí)索引中只記錄了name、phone和主鍵id字段（誰(shuí)讓我們用的是SELECT *呢），因此InnoDB需要拿著主鍵id去主鍵索引中查找這一條完整的記錄，這個(gè)過(guò)程叫做回表。

想一下，如果二級(jí)索引的葉子節(jié)點(diǎn)上有我們想要的所有數(shù)據(jù)，是不是就不需要回表了呢？是的，這就是覆蓋索引。

舉個(gè)例子，我們恰好只想搜索name、phone以及主鍵字段。

SELECT id, name,  phone FROM user_innodb WHERE name = "蟬沐風(fēng)";

使用EXPLAIN查看一下語(yǔ)句的執(zhí)行計(jì)劃：

可以看到Extra一列顯示Using index，表示我們的查詢列表以及搜索條件中只包含屬于某個(gè)索引的列，也就是使用了覆蓋索引，能夠直接摒棄回表操作，大幅度提高查詢效率。

4. 可能拖慢JOIN連接查詢

我們創(chuàng)建兩張表t1，t2進(jìn)行連接操作來(lái)說(shuō)明接下來(lái)的問(wèn)題，并向t1表中插入了100條數(shù)據(jù)，向t2中插入了1000條數(shù)據(jù)。

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;

如果我們執(zhí)行下面這條語(yǔ)句

SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.m = t2.m;

這里我使用了STRAIGHT_JOIN強(qiáng)制令t1表作為驅(qū)動(dòng)表，t2表作為被驅(qū)動(dòng)表

對(duì)于連接查詢而言，驅(qū)動(dòng)表只會(huì)被訪問(wèn)一遍，而被驅(qū)動(dòng)表卻要被訪問(wèn)好多遍，具體的訪問(wèn)次數(shù)取決于驅(qū)動(dòng)表中符合查詢記錄的記錄條數(shù)。由于已經(jīng)強(qiáng)制確定了驅(qū)動(dòng)表和被驅(qū)動(dòng)表，下面我們說(shuō)一下兩表連接的本質(zhì)：

1. t1作為驅(qū)動(dòng)表，針對(duì)驅(qū)動(dòng)表的過(guò)濾條件，執(zhí)行對(duì)t1表的查詢。因?yàn)闆](méi)有過(guò)濾條件，也就是獲取t1表的所有數(shù)據(jù)；
2. 對(duì)上一步中獲取到的結(jié)果集中的每一條記錄，都分別到被驅(qū)動(dòng)表中，根據(jù)連接過(guò)濾條件查找匹配記錄

用偽代碼表示的話整個(gè)過(guò)程是這樣的：

// t1Res是針對(duì)驅(qū)動(dòng)表t1過(guò)濾之后的結(jié)果集
for (t1Row : t1Res){
  // t2是完整的被驅(qū)動(dòng)表
  for(t2Row : t2){
   if (滿足join條件 && 滿足t2的過(guò)濾條件){
      發(fā)送給客戶端
    }  
  }
}

這種方法最簡(jiǎn)單，但同時(shí)性能也是最差，這種方式叫做嵌套循環(huán)連接（Nested-LoopJoin，NLJ）。怎么加快連接速度呢？

其中一個(gè)辦法就是創(chuàng)建索引，最好是在被驅(qū)動(dòng)表（t2）連接條件涉及到的字段上創(chuàng)建索引，畢竟被驅(qū)動(dòng)表需要被查詢好多次，而且對(duì)被驅(qū)動(dòng)表的訪問(wèn)本質(zhì)上就是個(gè)單表查詢而已（因?yàn)?code style="margin: 0px 2px;padding: 2px 4px;outline: 0px;max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(40, 202, 113);">t1結(jié)果集定了，每次連接t2的查詢條件也就定死了）。

既然使用了索引，為了避免重蹈無(wú)法使用覆蓋索引的覆轍，我們也應(yīng)該盡量不要直接SELECT *，而是將真正用到的字段作為查詢列，并為其建立適當(dāng)?shù)乃饕?/p>

但是如果我們不使用索引，MySQL就真的按照嵌套循環(huán)查詢的方式進(jìn)行連接查詢嗎？當(dāng)然不是，畢竟這種嵌套循環(huán)查詢實(shí)在是太慢了！

在MySQL8.0之前，MySQL提供了基于塊的嵌套循環(huán)連接（Block Nested-Loop Join，BLJ）方法，MySQL8.0又推出了hash join方法，這兩種方法都是為了解決一個(gè)問(wèn)題而提出的，那就是盡量減少被驅(qū)動(dòng)表的訪問(wèn)次數(shù)。

這兩種方法都用到了一個(gè)叫做join buffer的固定大小的內(nèi)存區(qū)域，其中存儲(chǔ)著若干條驅(qū)動(dòng)表結(jié)果集中的記錄（這兩種方法的區(qū)別就是存儲(chǔ)的形式不同而已），如此一來(lái)，把被驅(qū)動(dòng)表的記錄加載到內(nèi)存的時(shí)候，一次性和join buffer中多條驅(qū)動(dòng)表中的記錄做匹配，因?yàn)槠ヅ涞倪^(guò)程都是在內(nèi)存中完成的，所以這樣可以顯著減少被驅(qū)動(dòng)表的I/O代價(jià)，大大減少了重復(fù)從磁盤(pán)上加載被驅(qū)動(dòng)表的代價(jià)。使用join buffer的過(guò)程如下圖所示：

我們看一下上面的連接查詢的執(zhí)行計(jì)劃，發(fā)現(xiàn)確實(shí)使用到了hash join（前提是沒(méi)有為t2表的連接查詢字段創(chuàng)建索引，否則就會(huì)使用索引，不會(huì)使用join buffer）。

最好的情況是join buffer足夠大，能容納驅(qū)動(dòng)表結(jié)果集中的所有記錄，這樣只需要訪問(wèn)一次被驅(qū)動(dòng)表就可以完成連接操作了。我們可以使用join_buffer_size這個(gè)系統(tǒng)變量進(jìn)行配置，默認(rèn)大小為256KB。如果還裝不下，就得分批把驅(qū)動(dòng)表的結(jié)果集放到join buffer中了，在內(nèi)存中對(duì)比完成之后，清空join buffer再裝入下一批結(jié)果集，直到連接完成為止。

重點(diǎn)來(lái)了！并不是驅(qū)動(dòng)表記錄的所有列都會(huì)被放到join buffer中，只有查詢列表中的列和過(guò)濾條件中的列才會(huì)被放到join buffer中，所以再次提醒我們，最好不要把*作為查詢列表，只需要把我們關(guān)心的列放到查詢列表就好了，這樣還可以在join buffer中放置更多的記錄，減少分批的次數(shù)，也就自然減少了對(duì)被驅(qū)動(dòng)表的訪問(wèn)次數(shù)。

完

我的新書(shū)《深入理解Java核心技術(shù)》已經(jīng)上市了，上市后一直蟬聯(lián)京東暢銷榜中，目前正在6折優(yōu)惠中，想要入手的朋友千萬(wàn)不要錯(cuò)過(guò)哦~長(zhǎng)按二維碼即可購(gòu)買(mǎi)~

長(zhǎng)按掃碼享受6折優(yōu)惠

往期推薦

黑吃黑？男子破解賭博網(wǎng)站漏洞，每月“薅羊毛”10多萬(wàn)元

美團(tuán)二面： Redis 5 種基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)？

淦，為什么 "??????" .length !== 3 ？？

有道無(wú)術(shù)，術(shù)可成；有術(shù)無(wú)道，止于術(shù)

歡迎大家關(guān)注Java之道公眾號(hào)

好文章，我在看??