效率更高？)
一、開篇一道高頻面試題背后的問題在 MySQL 相關(guān)的面試中有一道題經(jīng)常被面試官問到distinct 和 group by 都能去重它們哪個(gè)效率更高很多候選人聽到這個(gè)問題后會(huì)下意識(shí)地回答「distinct 更快因?yàn)樗恼Z義更簡單」也有一些人會(huì)回答「group by 更快因?yàn)樗δ芨鼜?qiáng)」。實(shí)際上這道題并沒有一個(gè)脫離場(chǎng)景的唯一答案。要回答好這道題不能只停留在「誰比誰快」的結(jié)論上而應(yīng)該從 SQL 語義、執(zhí)行計(jì)劃、索引使用、臨時(shí)表、排序和優(yōu)化器行為幾個(gè)維度展開。本文將以 MySQL 8.0 為主要版本結(jié)合 InnoDB 存儲(chǔ)引擎從原理到實(shí)驗(yàn)系統(tǒng)地把 distinct 與 group by 的去重機(jī)制講清楚并給出不同場(chǎng)景下的選擇建議。整篇文章將覆蓋以下幾個(gè)方面distinct 與 group by 的基礎(chǔ)語法和語義差異兩種寫法在優(yōu)化器中的執(zhí)行計(jì)劃差異單列去重與多列去重的內(nèi)部處理方式count(distinct) 的特殊性和性能問題索引對(duì)去重效率的影響臨時(shí)表、文件排序和內(nèi)存限制的作用性能實(shí)測(cè)數(shù)據(jù)對(duì)比group by 相比 distinct 的額外能力不同業(yè)務(wù)場(chǎng)景下的選擇策略和優(yōu)化建議圍繞這道題常見的面試追問在正式開始之前需要先說明一點(diǎn)本文討論的是「用 group by 實(shí)現(xiàn)去重」與「直接使用 distinct 去重」之間的效率對(duì)比默認(rèn)查詢最終返回的都是去重后的結(jié)果集。理解了這一點(diǎn)后面的分析和實(shí)驗(yàn)才有統(tǒng)一的比較基準(zhǔn)。二、基礎(chǔ)回顧distinct 與 group by 的用法2.1 distinct 的語法與語義distinct 是 SELECT 語句中的一個(gè)關(guān)鍵字用來去除查詢結(jié)果中的重復(fù)行。它的位置通常緊跟在 SELECT 之后作用于 SELECT 列表中的全部列。也就是說distinct 去重的對(duì)象不是某一列而是整行記錄的組合。例如有一張用戶表 user里面記錄了用戶的城市和職業(yè)CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(50) NOT NULL, job VARCHAR(50) NOT NULL, KEY idx_city (city) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果只想查詢所有出現(xiàn)過的城市可以使用SELECT DISTINCT city FROM user;如果查詢的是 city 和 job 兩列去重的就是「城市和職業(yè)」這個(gè)二元組合SELECT DISTINCT city, job FROM user;注意distinct 關(guān)鍵字的位置不能隨意放在列后面它是一種作用于整行結(jié)果的關(guān)鍵字。比如下面這種寫法是不符合語法的SELECT city, DISTINCT job FROM user; -- 錯(cuò)誤寫法2.2 group by 的語法與語義group by 是 SQL 中的分組關(guān)鍵字它會(huì)按照指定列的值把結(jié)果集分成若干組每組最終輸出一行。由于每個(gè)分組的列值相同因此從結(jié)果上看group by 指定的列天然具有去重效果。同樣查詢所有出現(xiàn)過的城市用 group by 可以寫成SELECT city FROM user GROUP BY city;查詢城市和職業(yè)組合時(shí)SELECT city, job FROM user GROUP BY city, job;從去重效果來看上面兩條 group by 語句分別等價(jià)于對(duì)應(yīng)的 distinct 語句。但二者的語義并不完全相同group by 除了去重以外還允許對(duì)每個(gè)分組進(jìn)行聚合計(jì)算而 distinct 本身不具備聚合能力。三、從執(zhí)行計(jì)劃看本質(zhì)差異要判斷 distinct 和 group by 誰更高效首先要看優(yōu)化器如何解析和執(zhí)行這兩類語句。執(zhí)行計(jì)劃是分析 SQL 性能最直接的入口使用 EXPLAIN 可以查看優(yōu)化器選擇的訪問路徑。3.1 distinct 的執(zhí)行計(jì)劃先建立一個(gè)測(cè)試表并寫入一定量的數(shù)據(jù)DROP TABLE IF EXISTS t_distinct_test; CREATE TABLE t_distinct_test ( id INT PRIMARY KEY AUTO_INCREMENT, category INT NOT NULL, name VARCHAR(100) NOT NULL, create_time DATETIME NOT NULL, KEY idx_category (category) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;查看單列 distinct 的執(zhí)行計(jì)劃EXPLAIN SELECT DISTINCT category FROM t_distinct_test;如果 category 字段上存在索引 idx_category優(yōu)化器通常會(huì)優(yōu)先選擇該索引進(jìn)行掃描并利用Loose Index Scan或索引順序掃描來避免創(chuàng)建臨時(shí)表。此時(shí) Extra 列中可能會(huì)出現(xiàn)Using index for group-by之類的提示。需要說明的是雖然提示中有 group-by 字樣但它表示的是優(yōu)化器采用了松散索引掃描這種針對(duì)分組和去重場(chǎng)景的優(yōu)化策略distinct 和 group by 都可能受益。如果去重字段沒有可用索引執(zhí)行計(jì)劃往往會(huì)顯示出Using temporary這意味著 MySQL 需要借助臨時(shí)表來存儲(chǔ)已經(jīng)出現(xiàn)過的值并在插入時(shí)判斷是否重復(fù)。3.2 group by 的執(zhí)行計(jì)劃查看對(duì)同一列進(jìn)行分組的執(zhí)行計(jì)劃EXPLAIN SELECT category FROM t_distinct_test GROUP BY category;當(dāng) category 列有索引時(shí)group by 同樣可以走索引。與 distinct 類似優(yōu)化器也可能采用松散索引掃描。對(duì)于只包含分組列、沒有聚合函數(shù)的簡單分組查詢MySQL 的優(yōu)化器在很多情況下會(huì)把 group by 和 distinct 的執(zhí)行路徑優(yōu)化得非常接近甚至生成等價(jià)的執(zhí)行計(jì)劃。也就是說在最簡單的「單列去重且該列有索引」場(chǎng)景下distinct 和 group by 的性能幾乎沒有明顯差異二者都可能通過索引高效完成去重。3.3 關(guān)鍵區(qū)別不一定在執(zhí)行計(jì)劃而在功能定位需要糾正一個(gè)常見誤解很多人認(rèn)為 distinct 一定比 group by 快理由是 distinct 只需要判斷重復(fù)而 group by 還要分組排序。實(shí)際上MySQL 的 group by 并不一定要求排序只有顯式使用 ORDER BY 或者某些特定執(zhí)行路徑才會(huì)產(chǎn)生排序開銷。而 distinct 在必要時(shí)同樣會(huì)使用臨時(shí)表。因此單純從「關(guān)鍵字語義」判斷效率是不準(zhǔn)確的真正決定性能的是去重列是否有索引、數(shù)據(jù)量大小、去重后的基數(shù)高低、內(nèi)存是否充足、是否需要額外排序這些條件。四、單列去重的內(nèi)部處理4.1 有索引時(shí)的去重策略假如查詢語句是SELECT DISTINCT category FROM t_distinct_test;并且 category 列上建立了普通索引那么 InnoDB 的二級(jí)索引葉子節(jié)點(diǎn)中保存的是「索引列值加主鍵值」并且索引本身按照 category 的順序排列。由于相同 category 的記錄在索引中是連續(xù)存放的掃描索引時(shí)只需要讀取第一個(gè)值然后跳過后續(xù)相同的值即可。對(duì)于 group by 的等價(jià)寫法SELECT category FROM t_distinct_test GROUP BY category;MySQL 同樣可以利用索引的有序性進(jìn)行分組。Optimizer 在掃描索引時(shí)每遇到一個(gè)新的 category 值就輸出一行這被稱為松散索引掃描。這種掃描方式不需要把全部記錄加載到臨時(shí)表中內(nèi)存占用小執(zhí)行效率高。在有合適索引的情況下簡單 distinct 和簡單 group by 的去重成本都是 O(n) 級(jí)別的掃描成本差別很小。真正拉開差距的往往是那些更復(fù)雜的查詢形態(tài)。4.2 無索引時(shí)的臨時(shí)表處理當(dāng)去重列沒有索引時(shí)MySQL 無法借助有序性去重只能建立一個(gè)臨時(shí)表。臨時(shí)表可以是內(nèi)存表也可以是磁盤表。執(zhí)行過程大致如下掃描源表記錄對(duì)每一條記錄判斷其去重鍵是否已經(jīng)存在于臨時(shí)表如果不存在則插入臨時(shí)表并輸出或暫存該行如果存在則跳過當(dāng)數(shù)據(jù)量較大、臨時(shí)表超過 tmp_table_size 或 max_heap_table_size 限制時(shí)MySQL 會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)為磁盤臨時(shí)表使用 MyISAM 或 TempTable 存儲(chǔ)引擎性能會(huì)明顯下降。在這種情況下distinct 和 group by 都可能使用臨時(shí)表二者的底層開銷接近。相對(duì)而言group by 在支持聚合時(shí)還可以做更多優(yōu)化而 distinct 的能力邊界則停留在去重。五、多列去重的差異5.1 distinct 作用于整行distinct 的語義是對(duì) SELECT 列表中的所有列組成的行進(jìn)行去重。例如SELECT DISTINCT city, job FROM user;這條語句會(huì)返回 city 和 job 都不重復(fù)的組合。如果 user 表有 100 萬行但 city 和 job 的組合只有 1 萬種那么最終返回 1 萬行。在執(zhí)行計(jì)劃上如果 city 和 job 上存在聯(lián)合索引 idx_city_jobMySQL 可能通過該索引完成去重。但如果只對(duì) city 建了索引、job 沒有索引那么僅靠 city 索引無法保證 city 和 job 組合在索引中是連續(xù)有序的優(yōu)化器的選擇就會(huì)更復(fù)雜可能需要回表后再進(jìn)行去重或使用臨時(shí)表。5.2 group by 同樣作用于分組列組合使用 group by 的等價(jià)寫法是SELECT city, job FROM user GROUP BY city, job;這里 city 和 job 是分組列結(jié)果同樣是去重后的組合。與 distinct 相比二者的語義在「只查分組列」時(shí)幾乎一致。但如果查詢中增加了聚合函數(shù)語義就開始分化SELECT city, job, COUNT(*) AS cnt FROM user GROUP BY city, job;這種查詢 distinct 無法直接替代。即便使用 distinct 配合窗口函數(shù)或其他手段模擬寫法也復(fù)雜得多。這正是 group by 相比 distinct 最本質(zhì)的優(yōu)勢(shì)之一。5.3 多列去重中容易出現(xiàn)的一個(gè)誤區(qū)有些開發(fā)者會(huì)寫成SELECT DISTINCT city, job FROM user WHERE city 北京;并認(rèn)為 where 條件中的 city 已經(jīng)固定distinct 只需要對(duì) job 去重。這個(gè)理解在結(jié)果層面是對(duì)的但優(yōu)化器是否能據(jù)此降低去重成本取決于索引情況。如果存在 (city, job) 聯(lián)合索引利用索引最左前綴匹配city 固定后 job 依然有序去重效率會(huì)很高如果只有單列 city 索引job 部分仍可能需要額外去重。六、count(distinct) 的特殊問題在涉及 distinct 的面試題中count(distinct) 是一個(gè)繞不開的進(jìn)階點(diǎn)。它的性能和普通 distinct 查詢有明顯差異也經(jīng)常被用來考察候選人對(duì)執(zhí)行計(jì)劃的理解深度。6.1 count(distinct col) 能走普通索引嗎先看一個(gè)常見查詢SELECT COUNT(DISTINCT category) FROM t_distinct_test;這里要統(tǒng)計(jì) category 的去重個(gè)數(shù)。與 SELECT DISTINCT category 不同count(distinct) 需要得到去重后的記錄數(shù)量而不是去重后的明細(xì)行。當(dāng) category 上有索引時(shí)show 執(zhí)行計(jì)劃或用 EXPLAIN ANALYZE 觀察MySQL 是否能利用索引完成 count(distinct)在不同版本中表現(xiàn)不同。在 MySQL 8.0 中對(duì)于 COUNT(DISTINCT col) 這類聚合優(yōu)化器通?？梢話呙杷饕匀豢赡苄枰趻呙柽^程中進(jìn)行去重統(tǒng)計(jì)。如果 category 沒有索引或者需要對(duì)多列進(jìn)行 count(distinct)執(zhí)行成本會(huì)顯著上升因?yàn)樾枰S護(hù)一個(gè)較大的去重集合且無法簡單利用索引順序。6.2 count(distinct a, b) 的代價(jià)考察下面這種查詢SELECT COUNT(DISTINCT city, job) FROM user;這種寫法需要對(duì) city 和 job 的組合進(jìn)行去重后再計(jì)數(shù)。即便 city 上有索引、job 上也有索引如果不存在 (city, job) 聯(lián)合索引MySQL 也很難直接利用單個(gè)索引完成組合去重。底層實(shí)現(xiàn)上MySQL 可能在掃描結(jié)果的基礎(chǔ)上維護(hù)一個(gè)去重結(jié)構(gòu)。去重集合越大內(nèi)存占用越高一旦超過最大限制就會(huì)落盤性能急劇下降。這也是為什么在數(shù)據(jù)倉庫和大數(shù)據(jù)量報(bào)表中count(distinct) 經(jīng)常是性能瓶頸之一。6.3 count(distinct) 的一些優(yōu)化思路如果業(yè)務(wù)確實(shí)需要統(tǒng)計(jì)去重個(gè)數(shù)可以考慮以下優(yōu)化方案為需要去重的列建立合適的聯(lián)合索引使優(yōu)化器能夠有效利用索引順序把 count(distinct col) 改寫為對(duì)「列為主鍵或唯一鍵的子查詢」進(jìn)行計(jì)數(shù)使用 group by 加 count(*) 的寫法再對(duì)分組結(jié)果進(jìn)行計(jì)數(shù)必要時(shí)借助子查詢或派生表在大數(shù)據(jù)量場(chǎng)景下先在應(yīng)用層或 ETL 階段做近似去重如使用 HyperLogLog 思路檢查 innodb_buffer_pool_size 和臨時(shí)表相關(guān)參數(shù)降低落盤概率下面是一種常見的改寫方式使用派生表把去重和計(jì)數(shù)拆開讓執(zhí)行計(jì)劃更清晰-- 原始寫法 SELECT COUNT(DISTINCT category) FROM t_distinct_test; -- 改寫為先去重再計(jì)數(shù) SELECT COUNT(*) FROM ( SELECT category FROM t_distinct_test GROUP BY category ) AS dedup;這種改寫并不一定總是更快但至少可以讓去重過程和計(jì)數(shù)過程分離便于進(jìn)一步針對(duì)索引和執(zhí)行計(jì)劃進(jìn)行優(yōu)化。七、索引如何影響效率7.1 覆蓋索引與回表在討論 distinct 和 group by 的效率時(shí)索引是一個(gè)核心變量。以查詢單個(gè)去重列為例SELECT DISTINCT category FROM t_distinct_test;如果 category 上有普通索引并且查詢只需要 category 列那么該索引就是覆蓋索引。掃描二級(jí)索引時(shí)可以直接獲得 category 值不需要回表查詢聚簇索引IO 成本較低。但如果查詢還包含其他列例如SELECT DISTINCT category, name FROM t_distinct_test;而索引只有 idx_category(category)那么掃描 category 索引找到候選記錄后還需要根據(jù)主鍵回表讀取 name 列再進(jìn)行整行去重。此時(shí)掃描成本雖然可能降低但回表成本仍然存在是否劃算取決于過濾后的數(shù)據(jù)量。7.2 索引順序與去重方向如果去重列有索引且查詢不需要額外排序distinct 和 group by 都能按索引順序讀取。但如果查詢要求結(jié)果按另一列排序就需要額外排序SELECT DISTINCT category FROM t_distinct_test ORDER BY category;當(dāng) ORDER BY 的列與去重列一致且索引順序匹配時(shí)排序可以省去。反之如果排序字段不同優(yōu)化器可能先完成去重再做 filesort。group by 也有類似問題。group by 默認(rèn)并不保證結(jié)果有序雖然舊版本在某些存儲(chǔ)引擎下會(huì)隱式排序但 MySQL 8.0 已經(jīng)移除對(duì)隱式排序的依賴。因此不要假設(shè) group by 的結(jié)果天然按分組列排序。7.3 聯(lián)合索引的覆蓋能力對(duì)于多列去重聯(lián)合索引的設(shè)計(jì)非常關(guān)鍵。比如頻繁執(zhí)行SELECT DISTINCT city, job FROM user;此時(shí)建立聯(lián)合索引 idx_city_job(city, job) 對(duì)去重最有幫助。掃描該索引時(shí)city、job 組合天然有序相同組合連續(xù)出現(xiàn)去重成本最低。需要注意聯(lián)合索引的順序要和查詢中的列順序、過濾條件相匹配。如果更多查詢是「先按 job 過濾再按 city 去重」那么可能建立 (job, city) 聯(lián)合索引會(huì)更合適。聯(lián)合索引設(shè)計(jì)要結(jié)合真實(shí)業(yè)務(wù)查詢而不是機(jī)械套用某一種寫法。例如如果查詢還經(jīng)常包含 create_time 條件也可以考慮在高頻過濾列的基礎(chǔ)上組合去重列但索引列越多寫入和維護(hù)成本也會(huì)越高需要在查詢收益和寫入成本之間做平衡。八、臨時(shí)表、文件排序與內(nèi)存限制的作用當(dāng) distinct 或 group by 無法借助索引直接完成去重或分組時(shí)MySQL 通常會(huì)退回到臨時(shí)表。臨時(shí)表的工作方式以及和它密切相關(guān)的文件排序、內(nèi)存參數(shù)往往是不同 SQL 寫法之間出現(xiàn)性能差距的關(guān)鍵來源。8.1 臨時(shí)表會(huì)在哪些情況下被使用以下幾種情況很容易觸發(fā)Using temporary去重或分組列沒有可用索引。多列去重的組合與現(xiàn)有索引順序不匹配。去重或分組前還需要進(jìn)行函數(shù)計(jì)算、類型轉(zhuǎn)換、隱式轉(zhuǎn)換。SQL 中同時(shí)包含不同字段的 ORDER BY優(yōu)化器無法直接復(fù)用同一個(gè)索引順序。執(zhí)行計(jì)劃中出現(xiàn)Using temporary時(shí)說明 MySQL 需要借助一個(gè)額外的中間結(jié)構(gòu)來保存中間結(jié)果。這個(gè)中間結(jié)構(gòu)可能只存在于內(nèi)存中也可能落到磁盤上兩者的開銷差距非常大。8.2 內(nèi)存臨時(shí)表與磁盤臨時(shí)表的區(qū)別MySQL 會(huì)優(yōu)先創(chuàng)建內(nèi)存臨時(shí)表。內(nèi)存臨時(shí)表的大小受到tmp_table_size和max_heap_table_size兩個(gè)參數(shù)的共同限制取兩者中的較小值作為內(nèi)存臨時(shí)表的上限。當(dāng)中間結(jié)果超過這個(gè)上限后MySQL 會(huì)把臨時(shí)表轉(zhuǎn)成磁盤臨時(shí)表。磁盤臨時(shí)表可能使用 TempTable 引擎并落到 InnoDB 的磁盤表空間也可能在特定場(chǎng)景下使用 MyISAM。無論哪種實(shí)現(xiàn)磁盤 IO 的開銷都會(huì)讓處理速度明顯下降。所以同樣是去重在小數(shù)據(jù)量下 distinct 和 group by 可能都很快但數(shù)據(jù)量一旦超過內(nèi)存閾值導(dǎo)致臨時(shí)表落盤整體耗時(shí)可能成倍上升。這也是很多開發(fā)者在線上寫出慢 SQL測(cè)試環(huán)境卻表現(xiàn)正常的原因之一。8.3 filesort 對(duì)去重查詢的影響如果查詢結(jié)果需要按照非索引順序排序MySQL 就會(huì)使用 filesort。filesort 并不是一定寫文件它首先在sort_buffer_size指定的排序緩沖區(qū)中排序只有當(dāng)數(shù)據(jù)量超過緩沖區(qū)大小時(shí)才會(huì)使用外部歸并排序并寫到磁盤。以一個(gè)常見場(chǎng)景為例SELECT DISTINCT category FROM t_distinct_test ORDER BY name;這里去重列是 category排序列是 name。如果只有 category 索引優(yōu)化器可以先利用索引完成去重然后對(duì)去重結(jié)果執(zhí)行 filesort。去重后如果結(jié)果仍然很大filesort 的成本可能比去重本身更高。group by 也有相同問題。不要假設(shè)分組列和排序列一致就一定沒有排序也不要假設(shè) group by 一定有排序最終要看優(yōu)化器選擇的執(zhí)行路徑和表上的索引設(shè)置。8.4 調(diào)優(yōu)時(shí)應(yīng)該關(guān)注哪些參數(shù)如果確認(rèn) SQL 出現(xiàn)了臨時(shí)表或文件排序問題可以從這些參數(shù)和設(shè)計(jì)維度入手tmp_table_size控制內(nèi)存臨時(shí)表上限可結(jié)合業(yè)務(wù)數(shù)據(jù)量適當(dāng)調(diào)大但不能無限調(diào)大。max_heap_table_size與控制內(nèi)存表大小相關(guān)的參數(shù)和 tmp_table_size 共同生效。sort_buffer_size控制 filesort 緩沖區(qū)大小影響排序是否容易落盤。innodb_buffer_pool_size保證熱點(diǎn)索引和數(shù)據(jù)盡量留在緩沖池中減少磁盤讀。把去重/分組列和排序列納入索引設(shè)計(jì)從根源上減少臨時(shí)表和 filesort。九、性能實(shí)測(cè)不同場(chǎng)景下的數(shù)據(jù)對(duì)比前面更多是從執(zhí)行計(jì)劃角度分析下面用一個(gè)簡單的測(cè)試場(chǎng)景把不同條件下 distinct 和 group by 的表現(xiàn)做一個(gè)直觀對(duì)比。這里的數(shù)值是示例環(huán)境中的一組測(cè)試結(jié)果重點(diǎn)看趨勢(shì)不做絕對(duì)性能承諾。9.1 測(cè)試數(shù)據(jù)準(zhǔn)備繼續(xù)使用前面建立的t_distinct_test表向表中寫入 100 萬行測(cè)試數(shù)據(jù)category列基數(shù)約為 1 萬值分布不均勻。name列使用隨機(jī)字符串重復(fù)度較低。create_time覆蓋最近一年的時(shí)間范圍。分別測(cè)試無索引、單列 category 索引、聯(lián)合索引條件下的耗時(shí)。每次查詢執(zhí)行多次后取平均值避免單次波動(dòng)影響結(jié)論。9.2 單列去重場(chǎng)景對(duì)比語句SELECT DISTINCT category FROM t_distinct_test; SELECT category FROM t_distinct_test GROUP BY category;示例數(shù)據(jù)如下場(chǎng)景索引情況distinct 平均耗時(shí)group by 平均耗時(shí)結(jié)論單列去重 category無索引約 890 ms約 900 ms二者接近臨時(shí)表開銷明顯單列去重 categorycategory 上有索引約 45 ms約 48 ms差異很小都走索引去重可以看到在有索引時(shí)簡單 distinct 和簡單 group by 的耗時(shí)幾乎可以忽略差異在無索引時(shí)兩者都會(huì)受到臨時(shí)表影響耗時(shí)明顯上升。9.3 多列去重場(chǎng)景對(duì)比語句SELECT DISTINCT city, job FROM user; SELECT city, job FROM user GROUP BY city, job;示例數(shù)據(jù)如下場(chǎng)景索引情況distinct 平均耗時(shí)group by 平均耗時(shí)結(jié)論多列去重 city, job只有 city 單列索引約 1350 ms約 1380 ms無法直接利用索引組合成本高多列去重 city, job(city, job) 聯(lián)合索引約 65 ms約 62 ms聯(lián)合索引顯著降低去重成本這說明多列去重時(shí)聯(lián)合索引的收益要遠(yuǎn)大于在 distinct 和 group by 兩種寫法之間反復(fù)糾結(jié)。9.4 從實(shí)測(cè)結(jié)果中讀出的結(jié)論在簡單的單列去重、有合適索引時(shí)distinct 和 group by 性能差異很小。在無索引或多列去重?zé)o法利用聯(lián)合索引時(shí)二者的性能都會(huì)明顯下降且差距不大。真正決定性能的是索引設(shè)計(jì)、數(shù)據(jù)量、去重基數(shù)、臨時(shí)表是否落盤和排序需求。與其糾結(jié)用哪個(gè)關(guān)鍵字不如先檢查去重列有沒有合適的索引。十、group by 相比 distinct 的額外能力理解了執(zhí)行計(jì)劃之后就可以回答面試中常見的另一個(gè)問題既然 distinct 和 group by 都能去重為什么還要用 group by這是因?yàn)?group by 的功能邊界遠(yuǎn)不止去重。10.1 聚合計(jì)算group by 可以和聚合函數(shù)直接配合統(tǒng)計(jì)每個(gè)分組內(nèi)的數(shù)量、總和、平均值、最大值和最小值SELECT city, COUNT(*) AS user_count FROM user GROUP BY city;而 distinct 只能返回不重復(fù)的行本身不具備聚合能力。要想用 distinct 同時(shí)得到計(jì)數(shù)往往需要借助子查詢、窗口函數(shù)或額外掃描寫法復(fù)雜得多。10.2 HAVING 分組后過濾group by 之后可以使用 HAVING 對(duì)分組結(jié)果繼續(xù)過濾SELECT city, COUNT(*) AS user_count FROM user GROUP BY city HAVING COUNT(*) 100;這種“分組后再篩選組”的能力是 distinct 不具備的。10.3 多級(jí)匯總與 WITH ROLLUPgroup by 可以配合WITH ROLLUP生成小計(jì)行和總計(jì)行SELECT city, job, COUNT(*) AS cnt FROM user GROUP BY city, job WITH ROLLUP;這種用于報(bào)表和統(tǒng)計(jì)的寫法distinct 也無法直接實(shí)現(xiàn)。10.4 ONLY_FULL_GROUP_BY 下的語義差異MySQL 8.0 默認(rèn)開啟ONLY_FULL_GROUP_BY。啟用后SELECT 列表中的非聚合列必須出現(xiàn)在 group by 子句中-- ONLY_FULL_GROUP_BY 下可能報(bào)錯(cuò) SELECT city, job FROM user GROUP BY city;這說明 group by 承載的是一整套分組聚合語義而 distinct 只承載“結(jié)果去重”這一件事。二者定位不同不能簡單互替。十一、不同業(yè)務(wù)場(chǎng)景下的選擇策略與優(yōu)化建議綜合前面的分析可以把實(shí)際工作場(chǎng)景歸為幾類分別給出選擇建議。11.1 只需要去重不需要聚合如果需求只是取出去重后的列值或行組合推薦優(yōu)先使用 distinct。它的語義更直接代碼可讀性更好。在去重列有索引時(shí)性能和 group by 沒有本質(zhì)區(qū)別。11.2 去重后還要聚合、分組或過濾如果去重只是第一步后續(xù)還要 count、sum、avg或者需要按分組條件過濾應(yīng)直接使用 group by。這樣可以一次完成分組和聚合避免繞行派生表。11.3 多列去重優(yōu)先設(shè)計(jì)聯(lián)合索引對(duì)于列組合去重優(yōu)先把去重列按照業(yè)務(wù)查詢順序建成聯(lián)合索引。聯(lián)合索引能讓去重從臨時(shí)表方案變成索引掃描方案收益通常遠(yuǎn)超調(diào)整 SQL 關(guān)鍵字。11.4 面向接口的列表去重要區(qū)分層級(jí)有些“去重”問題本質(zhì)是業(yè)務(wù)模型問題例如分頁后出現(xiàn)重復(fù)數(shù)據(jù)、關(guān)聯(lián)查詢放大結(jié)果。此時(shí)不應(yīng)該通過無腦加 distinct 掩蓋問題而應(yīng)檢查連接條件、數(shù)據(jù)粒度或主鍵生成邏輯。11.5 超大表和報(bào)表場(chǎng)景在數(shù)據(jù)量很大的報(bào)表場(chǎng)景盡量避開直接 count(distinct)優(yōu)先用 group by 派生表分別完成去重和計(jì)數(shù)。對(duì)允許誤差的統(tǒng)計(jì)可以考慮 HyperLogLog 等近似去重方案。把高頻統(tǒng)計(jì)下推到數(shù)倉、ES、ClickHouse 等擅長聚合的組件中。檢查臨時(shí)表落盤情況必要時(shí)在 ETL 階段預(yù)先聚合。十二、高頻面試追問與回答要點(diǎn)回到這道面試題本身。面試官真正想考察的不是讓候選人背出一個(gè)“標(biāo)準(zhǔn)答案”而是看候選人能否根據(jù)執(zhí)行計(jì)劃和場(chǎng)景差異講清原因。12.1 distinct 一定比 group by 快嗎不是。在簡單單列去重且去重列有索引時(shí)兩者性能接近在無索引或多列去重時(shí)兩者的臨時(shí)表成本接近。性能主要取決于索引、數(shù)據(jù)量、去重基數(shù)和排序需求。12.2 count(distinct) 為什么經(jīng)常成為慢查詢因?yàn)?count(distinct) 需要維護(hù)去重結(jié)果才能統(tǒng)計(jì)數(shù)量去重集合越大越容易占用大量內(nèi)存超過限制后就會(huì)落盤。多列 count(distinct) 更難直接利用單個(gè)索引?？梢愿膶憺?group by 派生表再計(jì)數(shù)或考慮近似去重方案。12.3 group by 一定需要排序嗎不一定。MySQL 8.0 中 group by 默認(rèn)不會(huì)隱式保證排序結(jié)果只有顯式 ORDER BY 或某些執(zhí)行路徑才會(huì)產(chǎn)生排序成本。如果結(jié)果需要穩(wěn)定順序應(yīng)顯式寫 ORDER BY。12.4 松散索引掃描是什么松散索引掃描是 MySQL 利用索引有序性進(jìn)行去重或分組的一種方式。掃描索引時(shí)每遇到一個(gè)新的去重鍵就輸出一行跳過后續(xù)相同值相比把所有數(shù)據(jù)加載到臨時(shí)表能顯著節(jié)省內(nèi)存和 IO。12.5 多列去重時(shí)怎么設(shè)計(jì)索引優(yōu)先為去重列建立聯(lián)合索引索引列順序要和查詢條件、分組順序匹配。例如高頻查詢是 city 加 job 去重就考慮 (city, job) 聯(lián)合索引。同時(shí)應(yīng)結(jié)合實(shí)際查詢中的過濾列、排序需求來綜合設(shè)計(jì)。12.6 為什么 group by 能聚合而 distinct 不能因?yàn)?distinct 是面向整行結(jié)果集去重的關(guān)鍵字功能定位在“去掉重復(fù)行”group by 是分組子句核心作用是建立分組上下文再配合聚合函數(shù)對(duì)每一組進(jìn)行計(jì)算二者功能邊界不同。十三、總結(jié)與回答模板回到文章開頭的高頻面試題。比較穩(wěn)妥的回答方式是distinct 和 group by 在“簡單去重”這一目標(biāo)上可以等價(jià)實(shí)現(xiàn)而效率高低主要不取決于關(guān)鍵字本身而取決于去重列是否有索引、是否需要聯(lián)合索引、去重后的基數(shù)多大、臨時(shí)表和排序是否落盤。只有去重需求且語義清晰時(shí)優(yōu)先用 distinct去重之后還要聚合、分組過濾或匯總統(tǒng)計(jì)時(shí)用 group by。優(yōu)化時(shí)先看執(zhí)行計(jì)劃和索引設(shè)計(jì)再?zèng)Q定 SQL 寫法。整體判斷邏輯可以概括為三步第一步看需求是只去重還是要聚合第二步看執(zhí)行計(jì)劃是否存在全表掃描、臨時(shí)表、filesort第三步看索引能不能用覆蓋索引或聯(lián)合索引把去重成本降到最低。大多數(shù)場(chǎng)景下distinct 和 group by 的性能差距遠(yuǎn)沒有“索引不合理、臨時(shí)表落盤、排序字段沒有索引”帶來的影響大。掌握這個(gè)分析框架遠(yuǎn)比記住“誰更快”的結(jié)論更有價(jià)值。 面試官：MySQL中的 distinct 和 group by 哪個(gè)效率更高？
一、開篇一道高頻面試題背后的問題在 MySQL 相關(guān)的面試中有一道題經(jīng)常被面試官問到distinct 和 group by 都能去重它們哪個(gè)效率更高很多候選人聽到這個(gè)問題后會(huì)下意識(shí)地回答「distinct 更快因?yàn)樗恼Z義更簡單」也有一些人會(huì)回答「group by 更快因?yàn)樗δ芨鼜?qiáng)」。實(shí)際上這道題并沒有一個(gè)脫離場(chǎng)景的唯一答案。要回答好這道題不能只停留在「誰比誰快」的結(jié)論上而應(yīng)該從 SQL 語義、執(zhí)行計(jì)劃、索引使用、臨時(shí)表、排序和優(yōu)化器行為幾個(gè)維度展開。本文將以 MySQL 8.0 為主要版本結(jié)合 InnoDB 存儲(chǔ)引擎從原理到實(shí)驗(yàn)系統(tǒng)地把 distinct 與 group by 的去重機(jī)制講清楚并給出不同場(chǎng)景下的選擇建議。整篇文章將覆蓋以下幾個(gè)方面distinct 與 group by 的基礎(chǔ)語法和語義差異兩種寫法在優(yōu)化器中的執(zhí)行計(jì)劃差異單列去重與多列去重的內(nèi)部處理方式count(distinct) 的特殊性和性能問題索引對(duì)去重效率的影響臨時(shí)表、文件排序和內(nèi)存限制的作用性能實(shí)測(cè)數(shù)據(jù)對(duì)比group by 相比 distinct 的額外能力不同業(yè)務(wù)場(chǎng)景下的選擇策略和優(yōu)化建議圍繞這道題常見的面試追問在正式開始之前需要先說明一點(diǎn)本文討論的是「用 group by 實(shí)現(xiàn)去重」與「直接使用 distinct 去重」之間的效率對(duì)比默認(rèn)查詢最終返回的都是去重后的結(jié)果集。理解了這一點(diǎn)后面的分析和實(shí)驗(yàn)才有統(tǒng)一的比較基準(zhǔn)。二、基礎(chǔ)回顧distinct 與 group by 的用法2.1 distinct 的語法與語義distinct 是 SELECT 語句中的一個(gè)關(guān)鍵字用來去除查詢結(jié)果中的重復(fù)行。它的位置通常緊跟在 SELECT 之后作用于 SELECT 列表中的全部列。也就是說distinct 去重的對(duì)象不是某一列而是整行記錄的組合。例如有一張用戶表 user里面記錄了用戶的城市和職業(yè)CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, city VARCHAR(50) NOT NULL, job VARCHAR(50) NOT NULL, KEY idx_city (city) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果只想查詢所有出現(xiàn)過的城市可以使用SELECT DISTINCT city FROM user;如果查詢的是 city 和 job 兩列去重的就是「城市和職業(yè)」這個(gè)二元組合SELECT DISTINCT city, job FROM user;注意distinct 關(guān)鍵字的位置不能隨意放在列后面它是一種作用于整行結(jié)果的關(guān)鍵字。比如下面這種寫法是不符合語法的SELECT city, DISTINCT job FROM user; -- 錯(cuò)誤寫法2.2 group by 的語法與語義group by 是 SQL 中的分組關(guān)鍵字它會(huì)按照指定列的值把結(jié)果集分成若干組每組最終輸出一行。由于每個(gè)分組的列值相同因此從結(jié)果上看group by 指定的列天然具有去重效果。同樣查詢所有出現(xiàn)過的城市用 group by 可以寫成SELECT city FROM user GROUP BY city;查詢城市和職業(yè)組合時(shí)SELECT city, job FROM user GROUP BY city, job;從去重效果來看上面兩條 group by 語句分別等價(jià)于對(duì)應(yīng)的 distinct 語句。但二者的語義并不完全相同group by 除了去重以外還允許對(duì)每個(gè)分組進(jìn)行聚合計(jì)算而 distinct 本身不具備聚合能力。三、從執(zhí)行計(jì)劃看本質(zhì)差異要判斷 distinct 和 group by 誰更高效首先要看優(yōu)化器如何解析和執(zhí)行這兩類語句。執(zhí)行計(jì)劃是分析 SQL 性能最直接的入口使用 EXPLAIN 可以查看優(yōu)化器選擇的訪問路徑。3.1 distinct 的執(zhí)行計(jì)劃先建立一個(gè)測(cè)試表并寫入一定量的數(shù)據(jù)DROP TABLE IF EXISTS t_distinct_test; CREATE TABLE t_distinct_test ( id INT PRIMARY KEY AUTO_INCREMENT, category INT NOT NULL, name VARCHAR(100) NOT NULL, create_time DATETIME NOT NULL, KEY idx_category (category) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;查看單列 distinct 的執(zhí)行計(jì)劃EXPLAIN SELECT DISTINCT category FROM t_distinct_test;如果 category 字段上存在索引 idx_category優(yōu)化器通常會(huì)優(yōu)先選擇該索引進(jìn)行掃描并利用Loose Index Scan或索引順序掃描來避免創(chuàng)建臨時(shí)表。此時(shí) Extra 列中可能會(huì)出現(xiàn)Using index for group-by之類的提示。需要說明的是雖然提示中有 group-by 字樣但它表示的是優(yōu)化器采用了松散索引掃描這種針對(duì)分組和去重場(chǎng)景的優(yōu)化策略distinct 和 group by 都可能受益。如果去重字段沒有可用索引執(zhí)行計(jì)劃往往會(huì)顯示出Using temporary這意味著 MySQL 需要借助臨時(shí)表來存儲(chǔ)已經(jīng)出現(xiàn)過的值并在插入時(shí)判斷是否重復(fù)。3.2 group by 的執(zhí)行計(jì)劃查看對(duì)同一列進(jìn)行分組的執(zhí)行計(jì)劃EXPLAIN SELECT category FROM t_distinct_test GROUP BY category;當(dāng) category 列有索引時(shí)group by 同樣可以走索引。與 distinct 類似優(yōu)化器也可能采用松散索引掃描。對(duì)于只包含分組列、沒有聚合函數(shù)的簡單分組查詢MySQL 的優(yōu)化器在很多情況下會(huì)把 group by 和 distinct 的執(zhí)行路徑優(yōu)化得非常接近甚至生成等價(jià)的執(zhí)行計(jì)劃。也就是說在最簡單的「單列去重且該列有索引」場(chǎng)景下distinct 和 group by 的性能幾乎沒有明顯差異二者都可能通過索引高效完成去重。3.3 關(guān)鍵區(qū)別不一定在執(zhí)行計(jì)劃而在功能定位需要糾正一個(gè)常見誤解很多人認(rèn)為 distinct 一定比 group by 快理由是 distinct 只需要判斷重復(fù)而 group by 還要分組排序。實(shí)際上MySQL 的 group by 并不一定要求排序只有顯式使用 ORDER BY 或者某些特定執(zhí)行路徑才會(huì)產(chǎn)生排序開銷。而 distinct 在必要時(shí)同樣會(huì)使用臨時(shí)表。因此單純從「關(guān)鍵字語義」判斷效率是不準(zhǔn)確的真正決定性能的是去重列是否有索引、數(shù)據(jù)量大小、去重后的基數(shù)高低、內(nèi)存是否充足、是否需要額外排序這些條件。四、單列去重的內(nèi)部處理4.1 有索引時(shí)的去重策略假如查詢語句是SELECT DISTINCT category FROM t_distinct_test;并且 category 列上建立了普通索引那么 InnoDB 的二級(jí)索引葉子節(jié)點(diǎn)中保存的是「索引列值加主鍵值」并且索引本身按照 category 的順序排列。由于相同 category 的記錄在索引中是連續(xù)存放的掃描索引時(shí)只需要讀取第一個(gè)值然后跳過后續(xù)相同的值即可。對(duì)于 group by 的等價(jià)寫法SELECT category FROM t_distinct_test GROUP BY category;MySQL 同樣可以利用索引的有序性進(jìn)行分組。Optimizer 在掃描索引時(shí)每遇到一個(gè)新的 category 值就輸出一行這被稱為松散索引掃描。這種掃描方式不需要把全部記錄加載到臨時(shí)表中內(nèi)存占用小執(zhí)行效率高。在有合適索引的情況下簡單 distinct 和簡單 group by 的去重成本都是 O(n) 級(jí)別的掃描成本差別很小。真正拉開差距的往往是那些更復(fù)雜的查詢形態(tài)。4.2 無索引時(shí)的臨時(shí)表處理當(dāng)去重列沒有索引時(shí)MySQL 無法借助有序性去重只能建立一個(gè)臨時(shí)表。臨時(shí)表可以是內(nèi)存表也可以是磁盤表。執(zhí)行過程大致如下掃描源表記錄對(duì)每一條記錄判斷其去重鍵是否已經(jīng)存在于臨時(shí)表如果不存在則插入臨時(shí)表并輸出或暫存該行如果存在則跳過當(dāng)數(shù)據(jù)量較大、臨時(shí)表超過 tmp_table_size 或 max_heap_table_size 限制時(shí)MySQL 會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)為磁盤臨時(shí)表使用 MyISAM 或 TempTable 存儲(chǔ)引擎性能會(huì)明顯下降。在這種情況下distinct 和 group by 都可能使用臨時(shí)表二者的底層開銷接近。相對(duì)而言group by 在支持聚合時(shí)還可以做更多優(yōu)化而 distinct 的能力邊界則停留在去重。五、多列去重的差異5.1 distinct 作用于整行distinct 的語義是對(duì) SELECT 列表中的所有列組成的行進(jìn)行去重。例如SELECT DISTINCT city, job FROM user;這條語句會(huì)返回 city 和 job 都不重復(fù)的組合。如果 user 表有 100 萬行但 city 和 job 的組合只有 1 萬種那么最終返回 1 萬行。在執(zhí)行計(jì)劃上如果 city 和 job 上存在聯(lián)合索引 idx_city_jobMySQL 可能通過該索引完成去重。但如果只對(duì) city 建了索引、job 沒有索引那么僅靠 city 索引無法保證 city 和 job 組合在索引中是連續(xù)有序的優(yōu)化器的選擇就會(huì)更復(fù)雜可能需要回表后再進(jìn)行去重或使用臨時(shí)表。5.2 group by 同樣作用于分組列組合使用 group by 的等價(jià)寫法是SELECT city, job FROM user GROUP BY city, job;這里 city 和 job 是分組列結(jié)果同樣是去重后的組合。與 distinct 相比二者的語義在「只查分組列」時(shí)幾乎一致。但如果查詢中增加了聚合函數(shù)語義就開始分化SELECT city, job, COUNT(*) AS cnt FROM user GROUP BY city, job;這種查詢 distinct 無法直接替代。即便使用 distinct 配合窗口函數(shù)或其他手段模擬寫法也復(fù)雜得多。這正是 group by 相比 distinct 最本質(zhì)的優(yōu)勢(shì)之一。5.3 多列去重中容易出現(xiàn)的一個(gè)誤區(qū)有些開發(fā)者會(huì)寫成SELECT DISTINCT city, job FROM user WHERE city 北京;并認(rèn)為 where 條件中的 city 已經(jīng)固定distinct 只需要對(duì) job 去重。這個(gè)理解在結(jié)果層面是對(duì)的但優(yōu)化器是否能據(jù)此降低去重成本取決于索引情況。如果存在 (city, job) 聯(lián)合索引利用索引最左前綴匹配city 固定后 job 依然有序去重效率會(huì)很高如果只有單列 city 索引job 部分仍可能需要額外去重。六、count(distinct) 的特殊問題在涉及 distinct 的面試題中count(distinct) 是一個(gè)繞不開的進(jìn)階點(diǎn)。它的性能和普通 distinct 查詢有明顯差異也經(jīng)常被用來考察候選人對(duì)執(zhí)行計(jì)劃的理解深度。6.1 count(distinct col) 能走普通索引嗎先看一個(gè)常見查詢SELECT COUNT(DISTINCT category) FROM t_distinct_test;這里要統(tǒng)計(jì) category 的去重個(gè)數(shù)。與 SELECT DISTINCT category 不同count(distinct) 需要得到去重后的記錄數(shù)量而不是去重后的明細(xì)行。當(dāng) category 上有索引時(shí)show 執(zhí)行計(jì)劃或用 EXPLAIN ANALYZE 觀察MySQL 是否能利用索引完成 count(distinct)在不同版本中表現(xiàn)不同。在 MySQL 8.0 中對(duì)于 COUNT(DISTINCT col) 這類聚合優(yōu)化器通?？梢話呙杷饕匀豢赡苄枰趻呙柽^程中進(jìn)行去重統(tǒng)計(jì)。如果 category 沒有索引或者需要對(duì)多列進(jìn)行 count(distinct)執(zhí)行成本會(huì)顯著上升因?yàn)樾枰S護(hù)一個(gè)較大的去重集合且無法簡單利用索引順序。6.2 count(distinct a, b) 的代價(jià)考察下面這種查詢SELECT COUNT(DISTINCT city, job) FROM user;這種寫法需要對(duì) city 和 job 的組合進(jìn)行去重后再計(jì)數(shù)。即便 city 上有索引、job 上也有索引如果不存在 (city, job) 聯(lián)合索引MySQL 也很難直接利用單個(gè)索引完成組合去重。底層實(shí)現(xiàn)上MySQL 可能在掃描結(jié)果的基礎(chǔ)上維護(hù)一個(gè)去重結(jié)構(gòu)。去重集合越大內(nèi)存占用越高一旦超過最大限制就會(huì)落盤性能急劇下降。這也是為什么在數(shù)據(jù)倉庫和大數(shù)據(jù)量報(bào)表中count(distinct) 經(jīng)常是性能瓶頸之一。6.3 count(distinct) 的一些優(yōu)化思路如果業(yè)務(wù)確實(shí)需要統(tǒng)計(jì)去重個(gè)數(shù)可以考慮以下優(yōu)化方案為需要去重的列建立合適的聯(lián)合索引使優(yōu)化器能夠有效利用索引順序把 count(distinct col) 改寫為對(duì)「列為主鍵或唯一鍵的子查詢」進(jìn)行計(jì)數(shù)使用 group by 加 count(*) 的寫法再對(duì)分組結(jié)果進(jìn)行計(jì)數(shù)必要時(shí)借助子查詢或派生表在大數(shù)據(jù)量場(chǎng)景下先在應(yīng)用層或 ETL 階段做近似去重如使用 HyperLogLog 思路檢查 innodb_buffer_pool_size 和臨時(shí)表相關(guān)參數(shù)降低落盤概率下面是一種常見的改寫方式使用派生表把去重和計(jì)數(shù)拆開讓執(zhí)行計(jì)劃更清晰-- 原始寫法 SELECT COUNT(DISTINCT category) FROM t_distinct_test; -- 改寫為先去重再計(jì)數(shù) SELECT COUNT(*) FROM ( SELECT category FROM t_distinct_test GROUP BY category ) AS dedup;這種改寫并不一定總是更快但至少可以讓去重過程和計(jì)數(shù)過程分離便于進(jìn)一步針對(duì)索引和執(zhí)行計(jì)劃進(jìn)行優(yōu)化。七、索引如何影響效率7.1 覆蓋索引與回表在討論 distinct 和 group by 的效率時(shí)索引是一個(gè)核心變量。以查詢單個(gè)去重列為例SELECT DISTINCT category FROM t_distinct_test;如果 category 上有普通索引并且查詢只需要 category 列那么該索引就是覆蓋索引。掃描二級(jí)索引時(shí)可以直接獲得 category 值不需要回表查詢聚簇索引IO 成本較低。但如果查詢還包含其他列例如SELECT DISTINCT category, name FROM t_distinct_test;而索引只有 idx_category(category)那么掃描 category 索引找到候選記錄后還需要根據(jù)主鍵回表讀取 name 列再進(jìn)行整行去重。此時(shí)掃描成本雖然可能降低但回表成本仍然存在是否劃算取決于過濾后的數(shù)據(jù)量。7.2 索引順序與去重方向如果去重列有索引且查詢不需要額外排序distinct 和 group by 都能按索引順序讀取。但如果查詢要求結(jié)果按另一列排序就需要額外排序SELECT DISTINCT category FROM t_distinct_test ORDER BY category;當(dāng) ORDER BY 的列與去重列一致且索引順序匹配時(shí)排序可以省去。反之如果排序字段不同優(yōu)化器可能先完成去重再做 filesort。group by 也有類似問題。group by 默認(rèn)并不保證結(jié)果有序雖然舊版本在某些存儲(chǔ)引擎下會(huì)隱式排序但 MySQL 8.0 已經(jīng)移除對(duì)隱式排序的依賴。因此不要假設(shè) group by 的結(jié)果天然按分組列排序。7.3 聯(lián)合索引的覆蓋能力對(duì)于多列去重聯(lián)合索引的設(shè)計(jì)非常關(guān)鍵。比如頻繁執(zhí)行SELECT DISTINCT city, job FROM user;此時(shí)建立聯(lián)合索引 idx_city_job(city, job) 對(duì)去重最有幫助。掃描該索引時(shí)city、job 組合天然有序相同組合連續(xù)出現(xiàn)去重成本最低。需要注意聯(lián)合索引的順序要和查詢中的列順序、過濾條件相匹配。如果更多查詢是「先按 job 過濾再按 city 去重」那么可能建立 (job, city) 聯(lián)合索引會(huì)更合適。聯(lián)合索引設(shè)計(jì)要結(jié)合真實(shí)業(yè)務(wù)查詢而不是機(jī)械套用某一種寫法。例如如果查詢還經(jīng)常包含 create_time 條件也可以考慮在高頻過濾列的基礎(chǔ)上組合去重列但索引列越多寫入和維護(hù)成本也會(huì)越高需要在查詢收益和寫入成本之間做平衡。八、臨時(shí)表、文件排序與內(nèi)存限制的作用當(dāng) distinct 或 group by 無法借助索引直接完成去重或分組時(shí)MySQL 通常會(huì)退回到臨時(shí)表。臨時(shí)表的工作方式以及和它密切相關(guān)的文件排序、內(nèi)存參數(shù)往往是不同 SQL 寫法之間出現(xiàn)性能差距的關(guān)鍵來源。8.1 臨時(shí)表會(huì)在哪些情況下被使用以下幾種情況很容易觸發(fā)Using temporary去重或分組列沒有可用索引。多列去重的組合與現(xiàn)有索引順序不匹配。去重或分組前還需要進(jìn)行函數(shù)計(jì)算、類型轉(zhuǎn)換、隱式轉(zhuǎn)換。SQL 中同時(shí)包含不同字段的 ORDER BY優(yōu)化器無法直接復(fù)用同一個(gè)索引順序。執(zhí)行計(jì)劃中出現(xiàn)Using temporary時(shí)說明 MySQL 需要借助一個(gè)額外的中間結(jié)構(gòu)來保存中間結(jié)果。這個(gè)中間結(jié)構(gòu)可能只存在于內(nèi)存中也可能落到磁盤上兩者的開銷差距非常大。8.2 內(nèi)存臨時(shí)表與磁盤臨時(shí)表的區(qū)別MySQL 會(huì)優(yōu)先創(chuàng)建內(nèi)存臨時(shí)表。內(nèi)存臨時(shí)表的大小受到tmp_table_size和max_heap_table_size兩個(gè)參數(shù)的共同限制取兩者中的較小值作為內(nèi)存臨時(shí)表的上限。當(dāng)中間結(jié)果超過這個(gè)上限后MySQL 會(huì)把臨時(shí)表轉(zhuǎn)成磁盤臨時(shí)表。磁盤臨時(shí)表可能使用 TempTable 引擎并落到 InnoDB 的磁盤表空間也可能在特定場(chǎng)景下使用 MyISAM。無論哪種實(shí)現(xiàn)磁盤 IO 的開銷都會(huì)讓處理速度明顯下降。所以同樣是去重在小數(shù)據(jù)量下 distinct 和 group by 可能都很快但數(shù)據(jù)量一旦超過內(nèi)存閾值導(dǎo)致臨時(shí)表落盤整體耗時(shí)可能成倍上升。這也是很多開發(fā)者在線上寫出慢 SQL測(cè)試環(huán)境卻表現(xiàn)正常的原因之一。8.3 filesort 對(duì)去重查詢的影響如果查詢結(jié)果需要按照非索引順序排序MySQL 就會(huì)使用 filesort。filesort 并不是一定寫文件它首先在sort_buffer_size指定的排序緩沖區(qū)中排序只有當(dāng)數(shù)據(jù)量超過緩沖區(qū)大小時(shí)才會(huì)使用外部歸并排序并寫到磁盤。以一個(gè)常見場(chǎng)景為例SELECT DISTINCT category FROM t_distinct_test ORDER BY name;這里去重列是 category排序列是 name。如果只有 category 索引優(yōu)化器可以先利用索引完成去重然后對(duì)去重結(jié)果執(zhí)行 filesort。去重后如果結(jié)果仍然很大filesort 的成本可能比去重本身更高。group by 也有相同問題。不要假設(shè)分組列和排序列一致就一定沒有排序也不要假設(shè) group by 一定有排序最終要看優(yōu)化器選擇的執(zhí)行路徑和表上的索引設(shè)置。8.4 調(diào)優(yōu)時(shí)應(yīng)該關(guān)注哪些參數(shù)如果確認(rèn) SQL 出現(xiàn)了臨時(shí)表或文件排序問題可以從這些參數(shù)和設(shè)計(jì)維度入手tmp_table_size控制內(nèi)存臨時(shí)表上限可結(jié)合業(yè)務(wù)數(shù)據(jù)量適當(dāng)調(diào)大但不能無限調(diào)大。max_heap_table_size與控制內(nèi)存表大小相關(guān)的參數(shù)和 tmp_table_size 共同生效。sort_buffer_size控制 filesort 緩沖區(qū)大小影響排序是否容易落盤。innodb_buffer_pool_size保證熱點(diǎn)索引和數(shù)據(jù)盡量留在緩沖池中減少磁盤讀。把去重/分組列和排序列納入索引設(shè)計(jì)從根源上減少臨時(shí)表和 filesort。九、性能實(shí)測(cè)不同場(chǎng)景下的數(shù)據(jù)對(duì)比前面更多是從執(zhí)行計(jì)劃角度分析下面用一個(gè)簡單的測(cè)試場(chǎng)景把不同條件下 distinct 和 group by 的表現(xiàn)做一個(gè)直觀對(duì)比。這里的數(shù)值是示例環(huán)境中的一組測(cè)試結(jié)果重點(diǎn)看趨勢(shì)不做絕對(duì)性能承諾。9.1 測(cè)試數(shù)據(jù)準(zhǔn)備繼續(xù)使用前面建立的t_distinct_test表向表中寫入 100 萬行測(cè)試數(shù)據(jù)category列基數(shù)約為 1 萬值分布不均勻。name列使用隨機(jī)字符串重復(fù)度較低。create_time覆蓋最近一年的時(shí)間范圍。分別測(cè)試無索引、單列 category 索引、聯(lián)合索引條件下的耗時(shí)。每次查詢執(zhí)行多次后取平均值避免單次波動(dòng)影響結(jié)論。9.2 單列去重場(chǎng)景對(duì)比語句SELECT DISTINCT category FROM t_distinct_test; SELECT category FROM t_distinct_test GROUP BY category;示例數(shù)據(jù)如下場(chǎng)景索引情況distinct 平均耗時(shí)group by 平均耗時(shí)結(jié)論單列去重 category無索引約 890 ms約 900 ms二者接近臨時(shí)表開銷明顯單列去重 categorycategory 上有索引約 45 ms約 48 ms差異很小都走索引去重可以看到在有索引時(shí)簡單 distinct 和簡單 group by 的耗時(shí)幾乎可以忽略差異在無索引時(shí)兩者都會(huì)受到臨時(shí)表影響耗時(shí)明顯上升。9.3 多列去重場(chǎng)景對(duì)比語句SELECT DISTINCT city, job FROM user; SELECT city, job FROM user GROUP BY city, job;示例數(shù)據(jù)如下場(chǎng)景索引情況distinct 平均耗時(shí)group by 平均耗時(shí)結(jié)論多列去重 city, job只有 city 單列索引約 1350 ms約 1380 ms無法直接利用索引組合成本高多列去重 city, job(city, job) 聯(lián)合索引約 65 ms約 62 ms聯(lián)合索引顯著降低去重成本這說明多列去重時(shí)聯(lián)合索引的收益要遠(yuǎn)大于在 distinct 和 group by 兩種寫法之間反復(fù)糾結(jié)。9.4 從實(shí)測(cè)結(jié)果中讀出的結(jié)論在簡單的單列去重、有合適索引時(shí)distinct 和 group by 性能差異很小。在無索引或多列去重?zé)o法利用聯(lián)合索引時(shí)二者的性能都會(huì)明顯下降且差距不大。真正決定性能的是索引設(shè)計(jì)、數(shù)據(jù)量、去重基數(shù)、臨時(shí)表是否落盤和排序需求。與其糾結(jié)用哪個(gè)關(guān)鍵字不如先檢查去重列有沒有合適的索引。十、group by 相比 distinct 的額外能力理解了執(zhí)行計(jì)劃之后就可以回答面試中常見的另一個(gè)問題既然 distinct 和 group by 都能去重為什么還要用 group by這是因?yàn)?group by 的功能邊界遠(yuǎn)不止去重。10.1 聚合計(jì)算group by 可以和聚合函數(shù)直接配合統(tǒng)計(jì)每個(gè)分組內(nèi)的數(shù)量、總和、平均值、最大值和最小值SELECT city, COUNT(*) AS user_count FROM user GROUP BY city;而 distinct 只能返回不重復(fù)的行本身不具備聚合能力。要想用 distinct 同時(shí)得到計(jì)數(shù)往往需要借助子查詢、窗口函數(shù)或額外掃描寫法復(fù)雜得多。10.2 HAVING 分組后過濾group by 之后可以使用 HAVING 對(duì)分組結(jié)果繼續(xù)過濾SELECT city, COUNT(*) AS user_count FROM user GROUP BY city HAVING COUNT(*) 100;這種“分組后再篩選組”的能力是 distinct 不具備的。10.3 多級(jí)匯總與 WITH ROLLUPgroup by 可以配合WITH ROLLUP生成小計(jì)行和總計(jì)行SELECT city, job, COUNT(*) AS cnt FROM user GROUP BY city, job WITH ROLLUP;這種用于報(bào)表和統(tǒng)計(jì)的寫法distinct 也無法直接實(shí)現(xiàn)。10.4 ONLY_FULL_GROUP_BY 下的語義差異MySQL 8.0 默認(rèn)開啟ONLY_FULL_GROUP_BY。啟用后SELECT 列表中的非聚合列必須出現(xiàn)在 group by 子句中-- ONLY_FULL_GROUP_BY 下可能報(bào)錯(cuò) SELECT city, job FROM user GROUP BY city;這說明 group by 承載的是一整套分組聚合語義而 distinct 只承載“結(jié)果去重”這一件事。二者定位不同不能簡單互替。十一、不同業(yè)務(wù)場(chǎng)景下的選擇策略與優(yōu)化建議綜合前面的分析可以把實(shí)際工作場(chǎng)景歸為幾類分別給出選擇建議。11.1 只需要去重不需要聚合如果需求只是取出去重后的列值或行組合推薦優(yōu)先使用 distinct。它的語義更直接代碼可讀性更好。在去重列有索引時(shí)性能和 group by 沒有本質(zhì)區(qū)別。11.2 去重后還要聚合、分組或過濾如果去重只是第一步后續(xù)還要 count、sum、avg或者需要按分組條件過濾應(yīng)直接使用 group by。這樣可以一次完成分組和聚合避免繞行派生表。11.3 多列去重優(yōu)先設(shè)計(jì)聯(lián)合索引對(duì)于列組合去重優(yōu)先把去重列按照業(yè)務(wù)查詢順序建成聯(lián)合索引。聯(lián)合索引能讓去重從臨時(shí)表方案變成索引掃描方案收益通常遠(yuǎn)超調(diào)整 SQL 關(guān)鍵字。11.4 面向接口的列表去重要區(qū)分層級(jí)有些“去重”問題本質(zhì)是業(yè)務(wù)模型問題例如分頁后出現(xiàn)重復(fù)數(shù)據(jù)、關(guān)聯(lián)查詢放大結(jié)果。此時(shí)不應(yīng)該通過無腦加 distinct 掩蓋問題而應(yīng)檢查連接條件、數(shù)據(jù)粒度或主鍵生成邏輯。11.5 超大表和報(bào)表場(chǎng)景在數(shù)據(jù)量很大的報(bào)表場(chǎng)景盡量避開直接 count(distinct)優(yōu)先用 group by 派生表分別完成去重和計(jì)數(shù)。對(duì)允許誤差的統(tǒng)計(jì)可以考慮 HyperLogLog 等近似去重方案。把高頻統(tǒng)計(jì)下推到數(shù)倉、ES、ClickHouse 等擅長聚合的組件中。檢查臨時(shí)表落盤情況必要時(shí)在 ETL 階段預(yù)先聚合。十二、高頻面試追問與回答要點(diǎn)回到這道面試題本身。面試官真正想考察的不是讓候選人背出一個(gè)“標(biāo)準(zhǔn)答案”而是看候選人能否根據(jù)執(zhí)行計(jì)劃和場(chǎng)景差異講清原因。12.1 distinct 一定比 group by 快嗎不是。在簡單單列去重且去重列有索引時(shí)兩者性能接近在無索引或多列去重時(shí)兩者的臨時(shí)表成本接近。性能主要取決于索引、數(shù)據(jù)量、去重基數(shù)和排序需求。12.2 count(distinct) 為什么經(jīng)常成為慢查詢因?yàn)?count(distinct) 需要維護(hù)去重結(jié)果才能統(tǒng)計(jì)數(shù)量去重集合越大越容易占用大量內(nèi)存超過限制后就會(huì)落盤。多列 count(distinct) 更難直接利用單個(gè)索引?？梢愿膶憺?group by 派生表再計(jì)數(shù)或考慮近似去重方案。12.3 group by 一定需要排序嗎不一定。MySQL 8.0 中 group by 默認(rèn)不會(huì)隱式保證排序結(jié)果只有顯式 ORDER BY 或某些執(zhí)行路徑才會(huì)產(chǎn)生排序成本。如果結(jié)果需要穩(wěn)定順序應(yīng)顯式寫 ORDER BY。12.4 松散索引掃描是什么松散索引掃描是 MySQL 利用索引有序性進(jìn)行去重或分組的一種方式。掃描索引時(shí)每遇到一個(gè)新的去重鍵就輸出一行跳過后續(xù)相同值相比把所有數(shù)據(jù)加載到臨時(shí)表能顯著節(jié)省內(nèi)存和 IO。12.5 多列去重時(shí)怎么設(shè)計(jì)索引優(yōu)先為去重列建立聯(lián)合索引索引列順序要和查詢條件、分組順序匹配。例如高頻查詢是 city 加 job 去重就考慮 (city, job) 聯(lián)合索引。同時(shí)應(yīng)結(jié)合實(shí)際查詢中的過濾列、排序需求來綜合設(shè)計(jì)。12.6 為什么 group by 能聚合而 distinct 不能因?yàn)?distinct 是面向整行結(jié)果集去重的關(guān)鍵字功能定位在“去掉重復(fù)行”group by 是分組子句核心作用是建立分組上下文再配合聚合函數(shù)對(duì)每一組進(jìn)行計(jì)算二者功能邊界不同。十三、總結(jié)與回答模板回到文章開頭的高頻面試題。比較穩(wěn)妥的回答方式是distinct 和 group by 在“簡單去重”這一目標(biāo)上可以等價(jià)實(shí)現(xiàn)而效率高低主要不取決于關(guān)鍵字本身而取決于去重列是否有索引、是否需要聯(lián)合索引、去重后的基數(shù)多大、臨時(shí)表和排序是否落盤。只有去重需求且語義清晰時(shí)優(yōu)先用 distinct去重之后還要聚合、分組過濾或匯總統(tǒng)計(jì)時(shí)用 group by。優(yōu)化時(shí)先看執(zhí)行計(jì)劃和索引設(shè)計(jì)再?zèng)Q定 SQL 寫法。整體判斷邏輯可以概括為三步第一步看需求是只去重還是要聚合第二步看執(zhí)行計(jì)劃是否存在全表掃描、臨時(shí)表、filesort第三步看索引能不能用覆蓋索引或聯(lián)合索引把去重成本降到最低。大多數(shù)場(chǎng)景下distinct 和 group by 的性能差距遠(yuǎn)沒有“索引不合理、臨時(shí)表落盤、排序字段沒有索引”帶來的影響大。掌握這個(gè)分析框架遠(yuǎn)比記住“誰更快”的結(jié)論更有價(jià)值。