SELECT?*?效率低原理解析

更新時(shí)間：2023年02月02日 08:48:24 作者：博學(xué)谷

這篇文章主要為大家介紹了SELECT?*?效率低原理解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

效率低的原因

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

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

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

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

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

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

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

2. 對(duì)于無(wú)用的大字段，如 varchar、blob、text，會(huì)增加 io 操作

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

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

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

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

那么，在磁盤上有兩棵 B+ 樹(shù)，即聚集索引和輔助索引（包括單列索引、聯(lián)合索引），分別保存(a,b,c,d,e,f)和(a,b)，如果查詢條件中where條件可以通過(guò)b列的索引過(guò)濾掉一部分記錄，查詢就會(huì)先走輔助索引，如果用戶只需要a列和b列的數(shù)據(jù)，直接通過(guò)輔助索引就可以知道用戶查詢的數(shù)據(jù)。

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

由于輔助索引的數(shù)據(jù)比聚集索引少很多，很多情況下，通過(guò)輔助索引進(jìn)行覆蓋索引（通過(guò)索引就能獲取用戶需要的所有列），都不需要讀磁盤，直接從內(nèi)存取，而聚集索引很可能數(shù)據(jù)在磁盤（外存）中（取決于buffer pool的大小和命中率），這種情況下，一個(gè)是內(nèi)存讀，一個(gè)是磁盤讀，速度差異就很顯著了，幾乎是數(shù)量級(jí)的差異。

索引知識(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ì)

1）減少開(kāi)銷

建一個(gè)聯(lián)合索引 (a,b,c) ，實(shí)際相當(dāng)于建了 (a)、(a,b)、(a,b,c) 三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫操作的開(kāi)銷和磁盤空間的開(kāi)銷。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷！

2）覆蓋索引

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

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

那么 MySQL 可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī) io 操作。減少 io 操作，特別是隨機(jī) io 其實(shí)是 DBA 主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

3）效率高

索引列多，通過(guò)聯(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. 如果只有單列索引，那么通過(guò)該索引能篩選出 1000W 10%=100w 條數(shù)據(jù)，然后再回表從 100w 條數(shù)據(jù)中找到符合 col2=2 and col3= 3 的數(shù)據(jù)，然后再排序，再分頁(yè)，以此類推（遞歸）；
B. 如果是（col1,col2,col3）聯(lián)合索引，通過(guò)三列索引篩選出 1000w 10% 10% *10%=1w，效率提升可想而知！