為什麼不建議你使用SELECT * ?

語言: CN / TW / HK

文章來源:【公眾號:蟬沐風】

目錄

  • 不必要的磁碟 I/O

  • 加重網路時延

  • 無法使用覆蓋索引

  • 可能拖慢 JOIN 連線查詢

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

不過我在開發過程中直接使用 SELECT * 還是比較多的,原因有兩個:

  • 因為簡單,開發效率非常高,而且如果後期頻繁新增或修改欄位,SQL 語句也不需要改變。

  • 我認為過早優化是個不好的習慣,除非在一開始就能確定你最終實際需要的欄位是什麼,併為之建立恰當的索引;否則,我選擇遇到麻煩的時候再對 SQL 進行優化,當然前提是這個麻煩並不致命。

但是我們總得知道為什麼不建議直接使用 SELECT *,本文從 4 個方面給出理由。

不必要的磁碟 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 大小罷了,不會因為多幾個欄位就佔用額外的記憶體空間。

加重網路時延

承接上一點,雖然每次都是把 socket send buffer 中的資料傳送給客戶端,單次看來資料量不大,可架不住真的有人用 * 把 TEXT、MEDIUMTEXT 或者 BLOB 型別的欄位也查出來了,總資料量大了,這就直接導致網路傳輸的次數變多了。

如果 MySQL 和應用程式不在同一臺機器,這種開銷非常明顯。即使 MySQL 伺服器和客戶端是在同一臺機器上,使用的協議還是 TCP,通訊也是需要額外的時間。

無法使用覆蓋索引

為了說明這個問題,我們需要建一個表:

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,表示我們的查詢列表以及搜尋條件中只包含屬於某個索引的列,也就是使用了覆蓋索引,能夠直接摒棄回表操作,大幅度提高查詢效率。

可能拖慢 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 就真的按照巢狀迴圈查詢的方式進行連線查詢嗎?當然不是,畢竟這種巢狀迴圈查詢實在是太慢了!

在 MySQL 8.0 之前,MySQL 提供了基於塊的巢狀迴圈連線(Block Nested-Loop Join,BLJ)方法,MySQL 8.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 中放置更多的記錄,減少分批的次數,也就自然減少了對被驅動表的訪問次數。

-------------  END   -------------

掃碼 免費 獲取 600+頁 石杉老師原創精品文章彙總PDF

原創技術文章彙總

點個 在看 你最好看