無論在工作還是面試中，關(guān)于 SQL 中不要用“SELECT *”，都是大家聽爛了的問題，雖說聽爛了，但普遍理解還是在很淺的層面，并沒有多少人去追根究底，探究其原理。

[[332296]]

圖片來自 Pexels

面試官：“小陳，說一下你常用的 SQL 優(yōu)化方式吧。”

陳小哈：“那很多啊，比如不要用 SELECT *，查詢效率低。巴拉巴拉...”

面試官：“為什么不要用 SELECT * ?它在哪些情況下效率低呢?”

陳小哈：“SELECT * 它好像比寫指定列名多一次全表查詢吧，還多查了一些無用的字段。”

面試官：“嗯...”

陳小哈：“emmm~ 沒了”

陳小哈：“....??(幾個(gè)意思)”

面試官：“嗯...好，那你還有什么要問我的么?”

陳小哈：“我問你個(gè)錘子，把老子簡(jiǎn)歷還我!”

廢話不多說，本文帶你深入了解一下"SELECT * "效率低的原因及場(chǎng)景。

[[332297]]

本文很干!請(qǐng)自備茶水，沒時(shí)間看記得先收藏，來自一位被技術(shù)經(jīng)理毒打多年的程序員的忠告!

效率低的原因

先看一下最新《阿里 Java 開發(fā)手冊(cè)(泰山版)》中 MySQL 部分描述：

4 - 1. 【強(qiáng)制】在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫明。

說明：

• 增加查詢分析器解析成本。
• 增減字段容易與 resultMap 配置不一致。
• 無用字段增加網(wǎng)絡(luò) 消耗，尤其是 text 類型的字段。

開發(fā)手冊(cè)中比較概括的提到了幾點(diǎn)原因，讓我們深入一些看看：

①不需要的列會(huì)增加數(shù)據(jù)傳輸時(shí)間和網(wǎng)絡(luò)開銷

總結(jié)如下三點(diǎn)：

• 用“SELECT * ”數(shù)據(jù)庫需要解析更多的對(duì)象、字段、權(quán)限、屬性等相關(guān)內(nèi)容，在 SQL 語句復(fù)雜，硬解析較多的情況下，會(huì)對(duì)數(shù)據(jù)庫造成沉重的負(fù)擔(dān)。
• 增大網(wǎng)絡(luò)開銷;* 有時(shí)會(huì)誤帶上如 log、IconMD5 之類的無用且大文本字段，數(shù)據(jù)傳輸 size 會(huì)幾何增漲。如果 DB 和應(yīng)用程序不在同一臺(tái)機(jī)器，這種開銷非常明顯
• 即使 MySQL 服務(wù)器和客戶端是在同一臺(tái)機(jī)器上，使用的協(xié)議還是 TCP，通信也是需要額外的時(shí)間。

②對(duì)于無用的大字段，如 varchar、blob、text，會(huì)增加 IO 操作

準(zhǔn)確來說，長(zhǎng)度超過 728 字節(jié)的時(shí)候，會(huì)先把超出的數(shù)據(jù)序列化到另外一個(gè)地方，因此讀取這條記錄會(huì)增加一次 IO 操作。(MySQL InnoDB)

③失去 MySQL 優(yōu)化器“覆蓋索引”策略優(yōu)化的可能性

SELECT * 杜絕了覆蓋索引的可能性，而基于 MySQL 優(yōu)化器的“覆蓋索引”策略又是速度極快，效率極高，業(yè)界極為推薦的查詢優(yōu)化方式。

例如，有一個(gè)表為 t(a，b，c，d，e，f)，其中，a 為主鍵，b 列有索引。

那么，在磁盤上有兩棵 B+ 樹，即聚集索引和輔助索引(包括單列索引、聯(lián)合索引)，分別保存(a，b，c，d，e，f)和(a，b)。

如果查詢條件中 where 條件可以通過 b 列的索引過濾掉一部分記錄，查詢就會(huì)先走輔助索引;如果用戶只需要 a 列和 b 列的數(shù)據(jù)，直接通過輔助索引就可以知道用戶查詢的數(shù)據(jù)。

如果用戶使用 SELECT *，獲取了不需要的數(shù)據(jù)，則首先通過輔助索引過濾數(shù)據(jù)，然后再通過聚集索引獲取所有的列，這就多了一次 B+ 樹查詢，速度必然會(huì)慢很多。

由于輔助索引的數(shù)據(jù)比聚集索引少很多，很多情況下，通過輔助索引進(jìn)行覆蓋索引(通過索引就能獲取用戶需要的所有列)，都不需要讀磁盤，直接從內(nèi)存取。

而聚集索引很可能數(shù)據(jù)在磁盤(外存)中(取決于 buffer pool 的大小和命中率)，這種情況下，一個(gè)是內(nèi)存讀，一個(gè)是磁盤讀，速度差異就很顯著了，幾乎是數(shù)量級(jí)的差異。

[[332298]]

索引知識(shí)延申

上面提到了輔助索引，在 MySQL 中輔助索引包括單列索引、聯(lián)合索引(多列聯(lián)合)，單列索引就不再贅述了，這里提一下聯(lián)合索引的作用。

聯(lián)合索引 (a，b，c)

聯(lián)合索引 (a，b，c)實(shí)際建立了(a)、(a，b)、(a，b，c)三個(gè)索引。

我們可以將組合索引想成書的一級(jí)目錄、二級(jí)目錄、三級(jí)目錄，如 index(a，b，c)。

相當(dāng)于 a 是一級(jí)目錄，b 是一級(jí)目錄下的二級(jí)目錄，c 是二級(jí)目錄下的三級(jí)目錄。要使用某一目錄，必須先使用其上級(jí)目錄，一級(jí)目錄除外。

如下圖：

聯(lián)合索引的優(yōu)勢(shì)

聯(lián)合索引的優(yōu)勢(shì)有如下三個(gè)：

①減少開銷

建一個(gè)聯(lián)合索引(a，b，c)，實(shí)際相當(dāng)于建了(a)、(a，b)、(a，b，c)三個(gè)索引。

每多一個(gè)索引，都會(huì)增加寫操作的開銷和磁盤空間的開銷。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開銷!

②覆蓋索引

對(duì)聯(lián)合索引(a，b，c)，如果有如下 SQL 的：

SELECT a,b,c from table where a='xx' and b = 'xx'; 

那么 MySQL 可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機(jī) IO 操作。

減少 IO 操作，特別是隨機(jī) IO 其實(shí)是 DBA 主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

③效率高

索引列多，通過聯(lián)合索引篩選出的數(shù)據(jù)越少。比如有 1000W 條數(shù)據(jù)的表，有如下 SQL：

select col1,col2,col3 from table where col1=1 and col2=2 and col3=3; 

假設(shè)：假設(shè)每個(gè)條件可以篩選出 10% 的數(shù)據(jù)。

A：如果只有單列索引，那么通過該索引能篩選出 1000W10%=100w 條數(shù)據(jù)，然后再回表從 100W 條數(shù)據(jù)中找到符合 col2=2 and col3= 3 的數(shù)據(jù)，然后再排序，再分頁，以此類推(遞歸)。

B：如果是(col1，col2，col3)聯(lián)合索引，通過三列索引篩選出 1000W10% 10% *10%=1W，效率提升可想而知!

索引是建的越多越好嗎?答案自然是否定的：

• 數(shù)據(jù)量小的表不需要建立索引，建立會(huì)增加額外的索引開銷。
• 不經(jīng)常引用的列不要建立索引，因?yàn)椴怀Ｓ?，即使建立了索引也沒有多大意義。
• 經(jīng)常頻繁更新的列不要建立索引，因?yàn)榭隙〞?huì)影響插入或更新的效率。
• 數(shù)據(jù)重復(fù)且分布平均的字段，因此他建立索引就沒有太大的效果(例如性別字段，只有男女，不適合建立索引)。
• 數(shù)據(jù)變更需要維護(hù)索引，意味著索引越多維護(hù)成本越高。
• 更多的索引也需要更多的存儲(chǔ)空間。

心得體會(huì)

相信能看到這里這老鐵要么是對(duì) MySQL 有著一腔熱血的，要么就是喜歡滾鼠標(biāo)的。

有朋友問我，你對(duì) SQL 規(guī)范那么上心，平時(shí)你寫代碼不會(huì)用 SELECT * 吧?

咋可能啊，天天用!代碼里也在用(一臉羞愧)，其實(shí)我們的項(xiàng)目普遍很小，數(shù)據(jù)量也上不去，性能上還沒有遇到瓶頸，所以比較放縱。

寫本篇文章主要是這個(gè)知識(shí)點(diǎn)網(wǎng)上總結(jié)的很少很散，也不規(guī)范，算是給自己也是給大家總結(jié)一份比較詳細(xì)的，值得記一下的。以后給面試官說完讓他沒法找你茬!

作者：陳哈哈

編輯：陶家龍

出處：https://urlify.cn/ZvM3qe