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