到底為什么不建議使用SELECT * ？
來源：蟬沐風（ID：chanmufeng1994）
“不要使用SELECT *”幾乎已經(jīng)成為了使用MySQL的一條金科玉律，就連《阿里Java開發(fā)手冊》也明確表示不得使用*作為查詢的字段列表，更是讓這條規(guī)則擁有了權威的加持。
不過我在開發(fā)過程中直接使用SELECT *還是比較多的，原因有兩個：
因為簡單，開發(fā)效率非常高，而且如果后期頻繁添加或修改字段，SQL語句也不需要改變； 我認為過早優(yōu)化是個不好的習慣，除非在一開始就能確定你最終實際需要的字段是什么，并為之建立恰當?shù)乃饕环駝t，我選擇遇到麻煩的時候再對SQL進行優(yōu)化，當然前提是這個麻煩并不致命。
但是我們總得知道為什么不建議直接使用SELECT *，本文從4個方面給出理由。
1. 不必要的磁盤I/O
我們知道 MySQL 本質上是將用戶記錄存儲在磁盤上，因此查詢操作就是一種進行磁盤IO的行為（前提是要查詢的記錄沒有緩存在內存中）。
查詢的字段越多，說明要讀取的內容也就越多，因此會增大磁盤 IO 開銷。尤其是當某些字段是 TEXT、MEDIUMTEXT或者BLOB 等類型的時候，效果尤為明顯。
那使用SELECT *會不會使MySQL占用更多的內存呢？
理論上不會，因為對于Server層而言，并非是在內存中存儲完整的結果集之后一下子傳給客戶端，而是每從存儲引擎獲取到一行，就寫到一個叫做net_buffer的內存空間中，這個內存的大小由系統(tǒng)變量net_buffer_length來控制，默認是16KB；當net_buffer寫滿之后再往本地網(wǎng)絡棧的內存空間socket send buffer中寫數(shù)據(jù)發(fā)送給客戶端，發(fā)送成功（客戶端讀取完成）后清空net_buffer，然后繼續(xù)讀取下一行并寫入。
也就是說，默認情況下，結果集占用的內存空間最大不過是net_buffer_length大小罷了，不會因為多幾個字段就占用額外的內存空間。
2. 加重網(wǎng)絡時延
承接上一點，雖然每次都是把socket send buffer中的數(shù)據(jù)發(fā)送給客戶端，單次看來數(shù)據(jù)量不大，可架不住真的有人用*把TEXT、MEDIUMTEXT或者BLOB 類型的字段也查出來了，總數(shù)據(jù)量大了，這就直接導致網(wǎng)絡傳輸?shù)拇螖?shù)變多了。
如果MySQL和應用程序不在同一臺機器，這種開銷非常明顯。即使MySQL服務器和客戶端是在同一臺機器上，使用的協(xié)議還是TCP，通信也是需要額外的時間。
3. 無法使用覆蓋索引
為了說明這個問題，我們需要建一個表
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;
我們創(chuàng)建了一個存儲引擎為InnoDB的表user_innodb，并設置id為主鍵，另外為name和phone創(chuàng)建了聯(lián)合索引，最后向表中隨機初始化了500W+條數(shù)據(jù)。
InnoDB會自動為主鍵id創(chuàng)建一棵名為主鍵索引（又叫做聚簇索引）的B+樹，這個B+樹的最重要的特點就是葉子節(jié)點包含了完整的用戶記錄，大概長這個樣子。
如果我們執(zhí)行這個語句
SELECT * FROM user_innodb WHERE name = '蟬沐風';
使用EXPLAIN查看一下語句的執(zhí)行計劃：
發(fā)現(xiàn)這個SQL語句會使用到IDX_NAME_PHONE索引，這是一個二級索引。二級索引的葉子節(jié)點長這個樣子：
InnoDB存儲引擎會根據(jù)搜索條件在該二級索引的葉子節(jié)點中找到name為蟬沐風的記錄，但是二級索引中只記錄了name、phone和主鍵id字段（誰讓我們用的是SELECT *呢），因此InnoDB需要拿著主鍵id去主鍵索引中查找這一條完整的記錄，這個過程叫做回表。
想一下，如果二級索引的葉子節(jié)點上有我們想要的所有數(shù)據(jù)，是不是就不需要回表了呢？是的，這就是覆蓋索引。
舉個例子，我們恰好只想搜索name、phone以及主鍵字段。
SELECT id, name, phone FROM user_innodb WHERE name = "蟬沐風";
使用EXPLAIN查看一下語句的執(zhí)行計劃：
可以看到Extra一列顯示Using index，表示我們的查詢列表以及搜索條件中只包含屬于某個索引的列，也就是使用了覆蓋索引，能夠直接摒棄回表操作，大幅度提高查詢效率。
4. 可能拖慢JOIN連接查詢
我們創(chuàng)建兩張表t1，t2進行連接操作來說明接下來的問題，并向t1表中插入了100條數(shù)據(jù)，向t2中插入了1000條數(shù)據(jù)。
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;
如果我們執(zhí)行下面這條語句
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.m = t2.m;
這里我使用了STRAIGHT_JOIN強制令
t1表作為驅動表，t2表作為被驅動表
對于連接查詢而言，驅動表只會被訪問一遍，而被驅動表卻要被訪問好多遍，具體的訪問次數(shù)取決于驅動表中符合查詢記錄的記錄條數(shù)。由于已經(jīng)強制確定了驅動表和被驅動表，下面我們說一下兩表連接的本質：
t1作為驅動表，針對驅動表的過濾條件，執(zhí)行對t1表的查詢。因為沒有過濾條件，也就是獲取t1表的所有數(shù)據(jù)；對上一步中獲取到的結果集中的每一條記錄，都分別到被驅動表中，根據(jù)連接過濾條件查找匹配記錄
用偽代碼表示的話整個過程是這樣的：
// t1Res是針對驅動表t1過濾之后的結果集
for (t1Row : t1Res){
// t2是完整的被驅動表
for(t2Row : t2){
if (滿足join條件 && 滿足t2的過濾條件){
發(fā)送給客戶端
}
}
}
這種方法最簡單，但同時性能也是最差，這種方式叫做嵌套循環(huán)連接（Nested-LoopJoin，NLJ）。怎么加快連接速度呢？
其中一個辦法就是創(chuàng)建索引，最好是在被驅動表（t2）連接條件涉及到的字段上創(chuàng)建索引，畢竟被驅動表需要被查詢好多次，而且對被驅動表的訪問本質上就是個單表查詢而已（因為t1結果集定了，每次連接t2的查詢條件也就定死了）。
既然使用了索引，為了避免重蹈無法使用覆蓋索引的覆轍，我們也應該盡量不要直接SELECT *，而是將真正用到的字段作為查詢列，并為其建立適當?shù)乃饕?/p>
但是如果我們不使用索引，MySQL就真的按照嵌套循環(huán)查詢的方式進行連接查詢嗎？當然不是，畢竟這種嵌套循環(huán)查詢實在是太慢了！
在MySQL8.0之前，MySQL提供了基于塊的嵌套循環(huán)連接（Block Nested-Loop Join，BLJ）方法，MySQL8.0又推出了hash join方法，這兩種方法都是為了解決一個問題而提出的，那就是盡量減少被驅動表的訪問次數(shù)。
這兩種方法都用到了一個叫做join buffer的固定大小的內存區(qū)域，其中存儲著若干條驅動表結果集中的記錄（這兩種方法的區(qū)別就是存儲的形式不同而已），如此一來，把被驅動表的記錄加載到內存的時候，一次性和join buffer中多條驅動表中的記錄做匹配，因為匹配的過程都是在內存中完成的，所以這樣可以顯著減少被驅動表的I/O代價，大大減少了重復從磁盤上加載被驅動表的代價。使用join buffer的過程如下圖所示：
我們看一下上面的連接查詢的執(zhí)行計劃，發(fā)現(xiàn)確實使用到了hash join（前提是沒有為t2表的連接查詢字段創(chuàng)建索引，否則就會使用索引，不會使用join buffer）。
最好的情況是join buffer足夠大，能容納驅動表結果集中的所有記錄，這樣只需要訪問一次被驅動表就可以完成連接操作了。我們可以使用join_buffer_size這個系統(tǒng)變量進行配置，默認大小為256KB。如果還裝不下，就得分批把驅動表的結果集放到join buffer中了，在內存中對比完成之后，清空join buffer再裝入下一批結果集，直到連接完成為止。
重點來了！并不是驅動表記錄的所有列都會被放到join buffer中，只有查詢列表中的列和過濾條件中的列才會被放到join buffer中，所以再次提醒我們，最好不要把*作為查詢列表，只需要把我們關心的列放到查詢列表就好了，這樣還可以在join buffer中放置更多的記錄，減少分批的次數(shù)，也就自然減少了對被驅動表的訪問次數(shù)。
歡迎添加小編微信，進入交流群
推薦閱讀：