MySQL中的distinct與group by如何使用
先說大致的結論：
在語意相同，有索引的情況：
group by和distinct都能使用索引，效率相同。在語意相同，無索引的情況下：
distinct效率高於group by。原因是 distinct 和group by都會進行分組操作，但group by可能會進行排序，觸發 filesort，導致 sql 執行效率低下。
基於這個結論，你可能會問：
為什麼在語意相同，有索引的情況下，
group by和distinct效率相同？在什麼情況下，
group by會進行排序運算？
帶著這兩個問題找出答案。接下來，我們先來看看 distinct 和group by的基礎使用。
distinct的使用
distinct用法
SELECT DISTINCT columns FROM table_name WHERE where_conditions;
例如：
mysql> select distinct age from student; +------+ | age | +------+ | 10 | | 12 | | 11 | | NULL | +------+ 4 rows in set (0.01 sec)
DISTINCT 關鍵字用於傳回唯一不同的值。放在查詢語句中的第一個欄位前使用，並作用於主句所有欄位。
如果欄位具有NULL 值，並且對這個欄位使用DISTINCT子句，MySQL 將保留一個NULL 值，並刪除其它的NULL 值，因為DISTINCT子句將所有NULL 值視為相同的值。
distinct 多列去重
distinct 多列的去重，則是根據指定的去重的列信息來進行，即只有所有指定的列信息都相同，才會被認為是重複的訊息。
SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions; mysql> select distinct sex,age from student; +--------+------+ | sex | age | +--------+------+ | male | 10 | | female | 12 | | male | 11 | | male | NULL | | female | 11 | +--------+------+ 5 rows in set (0.02 sec)
group by的使用
對於基礎去重來說，group by 的使用和 distinct 類似。
單一列去重
語法：
SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;
執行：
mysql> select age from student group by age; +------+ | age | +------+ | 10 | | 12 | | 11 | | NULL | +------+ 4 rows in set (0.02 sec)
多列去重
語法：
SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;
執行：
mysql> select sex,age from student group by sex,age; +--------+------+ | sex | age | +--------+------+ | male | 10 | | female | 12 | | male | 11 | | male | NULL | | female | 11 | +--------+------+ 5 rows in set (0.03 sec)
區別範例
兩者的語法差異在於，group by可以進行單列去重，group by的原理是先將結果分組排序，然後傳回每組中的第一筆資料。並且是根據group by的後接字段進行去重的。
例如：
mysql> select sex,age from student group by sex; +--------+-----+ | sex | age | +--------+-----+ | male | 10 | | female | 12 | +--------+-----+ 2 rows in set (0.03 sec)
distinct和group by原理
在大多數例子中，DISTINCT可以被看作是特殊的GROUP BY，它們的實作都基於分組操作，且都可以透過鬆散索引掃描、緊湊索引掃描(關於索引掃描的內容會在其他文章中詳細介紹，就不在此細緻介紹了)來實現。
DISTINCT和GROUP BY都是可以使用索引進行掃描搜尋的。例如以下兩個sql（只單單看表格最後extra 的內容），我們對這兩個sql 進行分析，可以看到，在extra 中，這兩個sql 都使用了緊湊索引掃描Using index for group -by。
所以，在一般情況下，對於相同語意的DISTINCT和GROUP BY語句，我們可以對其使用相同的索引最佳化手段來進行最佳化。
mysql> explain select int1_index from test_distinct_groupby group by int1_index; +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test_distinct_groupby | NULL | range | index_1 | index_1 | 5 | NULL | 955 | 100.00 | Using index for group-by | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set (0.05 sec) mysql> explain select distinct int1_index from test_distinct_groupby; +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test_distinct_groupby | NULL | range | index_1 | index_1 | 5 | NULL | 955 | 100.00 | Using index for group-by | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set (0.05 sec)
但對於GROUP BY來說，在 MYSQL8.0 之前，GROUP Y預設會依據欄位進行隱含排序。
可以看到，下面這條 sql 語句在使用了暫存資料表的同時，也進行了 filesort。
mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ | 1 | SIMPLE | test_distinct_groupby | NULL | ALL | NULL | NULL | NULL | NULL | 97402 | 100.00 | Using temporary; Using filesort | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ 1 row in set (0.04 sec)
隱含排序
對於隱含排序，我們可以參考MySQL 官方的解釋：
https://dev.mysql.com/doc/refman/5.7 /en/order-by-optimization.html
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROlumns). However, relying on implicit GROlumns). However, relying on implicit GROlumns). However, relying on implicit GROlumns). However, relying on implicit GROlumns). However, relying on implicit GROlumns sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is precated.Tod dcatcated. BY clause.
大致解釋一下：
GROUP BY 預設隱含排序（指在GROUP BY 欄位沒有ASC 或DESC 指示符的情況下也會進行排序）。然而，GROUP BY 進行明確或隱式排序已經過時（deprecated）了，要產生給定的排序順序，請提供 ORDER BY 子句。
所以，在 MySQL8.0 之前,GROUP BY會預設根據作用欄位（GROUP BY的後接欄位）對結果進行排序。在能利用索引的情況下，GROUP BY不需要額外進行排序操作；但當無法利用索引排序時，MySQL 優化器就必須選擇透過使用臨時表然後再排序的方式來實現GROUP BY了。
且當結果集的大小超出系統設定臨時表大小時，MySQL 會將臨時表資料 copy 到磁碟上面再進行操作，語句的執行效率會變得極低。這也是 MySQL 選擇將此操作（隱式排序）棄用的原因。
基於上述原因，Mysql 在 8.0 時，對此進行了最佳化更新：
https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may dven er from lover query results may yh. sort order, provide an ORDER BY clause.
大致解釋一下：
從前（MySQL5.7 版本之前），Group by 會根據確定的條件進行隱含排序。在 MySQL 8.0 中，已經移除了這個功能，所以不再需要透過新增order by null 來禁止隱式排序了，但是，查詢結果可能與先前的 MySQL 版本不同。若要產生給定順序的結果，請按透過ORDER BY指定需要進行排序的欄位。
因此，我們的結論也出來了：
在語意相同，有索引的情況下：
group by和distinct都能使用索引，效率相同。因為group by和distinct近乎等價，distinct 可以被看做是特殊的group by。在語意相同，無索引的情況下：
distinct效率高於group by。原因是distinct和group by都會進行分組操作，但group by在MySQL8.0 之前會進行隱含排序，導致觸發filesort，sql 執行效率低下。但從MySQL8.0 開始，MySQL 就刪除了隱含排序，所以，此時在語意相同，無索引的情況下，group by和distinct的執行效率也是近乎等價的。
比起distinct來說，group by的語意明確。且由於distinct 關鍵字會對所有欄位生效，在進行複合業務處理時，group by的使用彈性更高，group by能根據分組情況，對資料進行更為複雜的處理，例如透過having對資料進行過濾，或透過聚合函數對資料進行運算。
以上是MySQL中的distinct與group by如何使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
PHP 的大數據結構處理技巧
May 08, 2024 am 10:24 AM
大數據結構處理技巧：分塊：分解資料集並分塊處理，減少記憶體消耗。生成器：逐一產生資料項，無需載入整個資料集，適用於無限資料集。流：逐行讀取檔案或查詢結果，適用於大檔案或遠端資料。外部儲存：對於超大資料集，將資料儲存在資料庫或NoSQL中。
如何在 PHP 中使用 MySQL 備份和還原？
Jun 03, 2024 pm 12:19 PM
在PHP中備份和還原MySQL資料庫可透過以下步驟實現：備份資料庫：使用mysqldump指令轉儲資料庫為SQL檔。還原資料庫：使用mysql指令從SQL檔案還原資料庫。
如何優化 PHP 中的 MySQL 查詢效能？
Jun 03, 2024 pm 08:11 PM
可以透過以下方式最佳化MySQL查詢效能：建立索引，將查找時間從線性複雜度降至對數複雜度。使用PreparedStatements，防止SQL注入並提高查詢效能。限制查詢結果，減少伺服器處理的資料量。最佳化連接查詢，包括使用適當的連接類型、建立索引和考慮使用子查詢。分析查詢，識別瓶頸；使用緩存，減少資料庫負載；優化PHP程式碼，盡量減少開銷。
如何使用 PHP 插入資料到 MySQL 表？
Jun 02, 2024 pm 02:26 PM
如何將資料插入MySQL表中？連接到資料庫：使用mysqli建立與資料庫的連線。準備SQL查詢：寫一個INSERT語句以指定要插入的欄位和值。執行查詢：使用query()方法執行插入查詢，如果成功，將輸出一條確認訊息。
如何在 PHP 中使用 MySQL 預存程序？
Jun 02, 2024 pm 02:13 PM
若要在PHP中使用MySQL預存程序：使用PDO或MySQLi擴充連接到MySQL資料庫。準備呼叫預存程序的語句。執行儲存程序。處理結果集（如果預存程序傳回結果）。關閉資料庫連線。
如何使用 PHP 建立 MySQL 表？
Jun 04, 2024 pm 01:57 PM
使用PHP建立MySQL表需要以下步驟：連接到資料庫。建立資料庫（如果不存在）。選擇資料庫。建立表。執行查詢。關閉連線。
