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

語言: CN / TW / HK

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

阿里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. 無法使用覆蓋索引

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

mysql 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+樹的最重要的特點就是葉子節點包含了完整的用户記錄,大概長這個樣子。

主鍵索引

如果我們執行這個語句

mysql SELECT * FROM user_innodb WHERE name = '蟬沐風';

使用EXPLAIN查看一下語句的執行計劃:

發現這個SQL語句會使用到IDX_NAME_PHONE索引,這是一個二級索引。二級索引的葉子節點長這個樣子:

InnoDB存儲引擎會根據搜索條件在該二級索引的葉子節點中找到name蟬沐風的記錄,但是二級索引中只記錄了namephone和主鍵id字段(誰讓我們用的是SELECT *呢),因此InnoDB需要拿着主鍵id去主鍵索引中查找這一條完整的記錄,這個過程叫做回表

想一下,如果二級索引的葉子節點上有我們想要的所有數據,是不是就不需要回表了呢?是的,這就是覆蓋索引

舉個例子,我們恰好只想搜索namephone以及主鍵字段。

mysql SELECT id, name, phone FROM user_innodb WHERE name = "蟬沐風";

使用EXPLAIN查看一下語句的執行計劃:

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

4. 可能拖慢JOIN連接查詢

我們創建兩張表t1t2進行連接操作來説明接下來的問題,並向t1表中插入了100條數據,向t2中插入了1000條數據。

``mysql CREATE TABLEt1(idint NOT NULL,mint DEFAULT NULL,nint 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; ```

如果我們執行下面這條語句

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

這裏我使用了STRAIGHT_JOIN強制令t1表作為驅動表,t2表作為被驅動表

對於連接查詢而言,驅動表只會被訪問一遍,而被驅動表卻要被訪問好多遍,具體的訪問次數取決於驅動表中符合查詢記錄的記錄條數。由於已經強制確定了驅動表和被驅動表,下面我們説一下兩表連接的本質:

  1. t1作為驅動表,針對驅動表的過濾條件,執行對t1表的查詢。因為沒有過濾條件,也就是獲取t1表的所有數據;
  2. 對上一步中獲取到的結果集中的每一條記錄,都分別到被驅動表中,根據連接過濾條件查找匹配記錄

用偽代碼表示的話整個過程是這樣的:

c // 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的過程如下圖所示:

join buffer示意圖

我們看一下上面的連接查詢的執行計劃,發現確實使用到了hash join(前提是沒有為t2表的連接查詢字段創建索引,否則就會使用索引,不會使用join buffer)。

最好的情況是join buffer足夠大,能容納驅動表結果集中的所有記錄,這樣只需要訪問一次被驅動表就可以完成連接操作了。我們可以使用join_buffer_size這個系統變量進行配置,默認大小為256KB。如果還裝不下,就得分批把驅動表的結果集放到join buffer中了,在內存中對比完成之後,清空join buffer再裝入下一批結果集,直到連接完成為止。

重點來了!並不是驅動表記錄的所有列都會被放到join buffer中,只有查詢列表中的列和過濾條件中的列才會被放到join buffer中,所以再次提醒我們,最好不要把*作為查詢列表,只需要把我們關心的列放到查詢列表就好了,這樣還可以在join buffer中放置更多的記錄,減少分批的次數,也就自然減少了對被驅動表的訪問次數。

推薦閲讀