點選「 IT碼徒 」， 關註，置頂 公眾號
每日技術幹貨，第一時間送達！
面試官：「小陳，說一下你常用的SQL最佳化方式吧。」
陳小哈：「那很多啊，比如不要用SELECT *，查詢效率低。巴拉巴拉...」
面試官：「為什麽不要用SELECT * ？它在哪些情況下效率低呢？」
陳小哈：「SELECT * 它好像比寫指定列名多一次全表查詢吧，還多查了一些無用的欄位。」
面試官：「嗯...」
陳小哈：「emmm~ 沒了」
陳小哈：「....??（幾個意思）」
面試官：「嗯...好，那你還有什麽要問我的麽？」
陳小哈：「我問你個錘子，把老子簡歷還我！」
無論在工作還是面試中，關於SQL中不要用「S ELECT *」，都是大家聽爛了的問題，雖說聽爛了，但普遍理解還是在很淺的層面，並沒有多少人去追根究底，探究其原理。
廢話不多說，本文帶你深入了解一下"SELECT * "效率低的原因及場景。
1
效率低的原因
先看一下最新【阿裏java開發手冊（泰山版）】中 MySQL 部份描述：
4 - 1. 【強制】在表查詢中，一律不要使用 * 作為查詢的欄位列表，需要哪些欄位必須明確寫明。
說明：
增加查詢分析器解析成本。
增減欄位容易與 resultMap 配置不一致。
無用欄位增加網路 消耗，尤其是 text 型別的欄位。
開發手冊中比較概括的提到了幾點原因，讓我們深入一些看看：
1. 不需要的列會增加數據傳輸時間和網路開銷
用「SELECT * 」資料庫需要解析更多的物件、欄位、許可權、內容等相關內容，在 SQL 語句復雜，硬解析較多的情況下，會對資料庫造成沈重的負擔。
增大網路開銷；* 有時會誤帶上如log、IconMD5之類的無用且大文本欄位，數據傳輸size會幾何增漲。如果DB和應用程式不在同一台機器，這種開銷非常明顯。
即使 mysql 伺服器和客戶端是在同一台機器上，使用的協定還是 tcp，通訊也是需要額外的時間。
2. 對於無用的大欄位，如 varchar、blob、text，會增加 io 操作
準確來說，長度超過 728 字節的時候，會先把超出的數據序列化到另外一個地方，因此讀取這條記錄會增加一次 io 操作。（MySQL InnoDB）
3. 失去MySQL最佳化器「覆蓋索引」策略最佳化的可能性
SELECT * 杜絕了覆蓋索引的可能性，而基於MySQL最佳化器的「覆蓋索引」策略又是速度極快，效率極高，業界極為推薦的查詢最佳化方式。
例如，有一個表為t(a,b,c,d,e,f)，其中，a為主鍵，b列有索引。
那麽，在磁盤上有兩棵 B+ 樹，即聚集索引和輔助索引（包括單列索引、聯合索引），分別保存(a,b,c,d,e,f)和(a,b)，如果查詢條件中where條件可以透過b列的索引過濾掉一部份記錄，查詢就會先走輔助索引，如果使用者只需要a列和b列的數據，直接透過輔助索引就可以知道使用者查詢的數據。
如果使用者使用select *，獲取了不需要的數據，則首先透過輔助索引過濾數據，然後再透過聚集索引獲取所有的列，這就多了一次b+樹查詢，速度必然會慢很多。
圖片取自博文 【我去，為什麽最左字首原則失效了？】
由於輔助索引的數據比聚集索引少很多，很多情況下，透過輔助索引進行覆蓋索引（透過索引就能獲取使用者需要的所有列），都不需要讀磁盤，直接從記憶體取，而聚集索引很可能數據在磁盤（外存）中（取決於buffer pool的大小和命中率），這種情況下，一個是記憶體讀，一個是磁盤讀，速度差異就很顯著了，幾乎是數量級的差異。
2
索引知識延申
上面提到了輔助索引，在MySQL中輔助索引包括單列索引、聯合索引（多列聯合），單列索引就不再贅述了，這裏提一下聯合索引的作用
聯合索引 (a,b,c)
聯合索引 (a,b,c) 實際建立了 (a)、(a,b)、(a,b,c) 三個索引
我們可以將組合索引想成書的一級目錄、二級目錄、三級目錄，如index(a,b,c)，相當於a是一級目錄，b是一級目錄下的二級目錄，c是二級目錄下的三級目錄。要使用某一目錄，必須先使用其上級目錄，一級目錄除外。
如下：
| where條件 | 效果 |
| where a=1 and c=1 | 只使用了一級目錄，c在三級目錄，沒有使用二級目錄，那麽三級目錄就沒法使用 |
| where a=1 and b=1 | 只使用了一級目錄、二級目錄。 |
聯合索引的優勢
1） 減少開銷
建一個聯合索引 (a,b,c) ，實際相當於建了 (a)、(a,b)、(a,b,c) 三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對於大量數據的表，使用聯合索引會大大的減少開銷！
2）覆蓋索引
對聯合索引 (a,b,c)，如果有如下 sql 的，
SELECT a,b,c fromtablewhere a='xx'and b = 'xx';
那麽 MySQL 可以直接透過遍歷索引取得數據，而無需回表，這減少了很多的隨機 io 操作。減少 io 操作，特別是隨機 io 其實是 DBA 主要的最佳化策略。所以，在真正的實際套用中，覆蓋索引是主要的提升效能的最佳化手段之一。
3）效率高
索引列多，透過聯合索引篩選出的數據越少。比如有 1000W 條數據的表，有如下SQL：
select col1,col2,col3 fromtablewhere col1=1and col2=2and col3=3;
假設：假設每個條件可以篩選出 10% 的數據。
A. 如果只有單列索引，那麽透過該索引能篩選出 1000W10%=100w 條數據，然後再回表從 100w 條數據中找到符合 col2=2 and col3= 3 的數據，然後再排序，再分頁，以此類推（遞迴）；
B. 如果是（col1,col2,col3）聯合索引，透過三列索引篩選出 1000w10% 10% *10%=1w，效率提升可想而知！
索引是建的越多越好嗎
答案自然是否定的
數據量小的表不需要建立索引，建立會增加額外的索引開銷
不經常參照的列不要建立索引，因為不常用，即使建立了索引也沒有多大意義
經常頻繁更新的列不要建立索引，因為肯定會影響插入或更新的效率
數據重復且分布平均的欄位，因此他建立索引就沒有太大的效果（例如性別欄位，只有男女，不適合建立索引）
數據變更需要維護索引，意味著索引越多維護成本越高。
更多的索引也需要更多的儲存空間
3
心得體會
有朋友問我，你對SQL規範那麽上心，平時你寫程式碼不會用SELECT * 吧？
咋可能啊，天天用。。程式碼裏也在用（一臉羞愧），其實我們的計畫普遍很小，數據量也上不去，效能上還沒有遇到瓶頸，所以比較放縱。
寫本篇文章主要是這個知識點網上總結的很少很散，也不規範，算是給自己也是給大家總結一份比較詳細的，值得記一下的。以後給面試官說完讓他沒法找你茬。
— END —
PS：防止找不到本篇文章，可以收藏點贊，方便翻閱尋找哦。
往期推薦
