mysql中的group by用法詳解

MySQL中的GROUP BY是數(shù)據(jù)聚合分析的核心功能，主要用于將結(jié)果集按指定列分組，并結(jié)合聚合函數(shù)進(jìn)行統(tǒng)計計算。以下從基本語法到高級用法進(jìn)行詳細(xì)解析：

一、基本語法與核心功能

SELECT 分組列, 聚合函數(shù)(計算列)
FROM 表名
[WHERE 條件]
GROUP BY 分組列
[HAVING 分組過濾條件]
[ORDER BY 排序列];

核心功能：

• 數(shù)據(jù)分組：按一列或多列的值將數(shù)據(jù)劃分為邏輯組。
• 聚合計算：對每個分組應(yīng)用聚合函數(shù)（如COUNT、SUM、AVG、MAX、MIN）進(jìn)行統(tǒng)計。
• 結(jié)果過濾：通過HAVING對分組后的結(jié)果進(jìn)行篩選（區(qū)別于WHERE的分組前過濾）。

二、基礎(chǔ)用法示例

1. 單列分組統(tǒng)計

統(tǒng)計每個部門的員工數(shù)量和平均工資：

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;  -- 

2. 多列組合分組

按部門和職位統(tǒng)計員工數(shù)量：

SELECT department, job_title, COUNT(*) 
FROM employees
GROUP BY department, job_title;  -- 

3. 與WHERE結(jié)合使用

僅統(tǒng)計薪資超過2000元的員工部門平均工資：

SELECT department, AVG(salary)
FROM employees
WHERE salary > 2000
GROUP BY department;  -- 

三、高級特性與擴(kuò)展

1. HAVING子句過濾分組

篩選員工數(shù)量超過5人的部門：

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 5;  -- 

2. WITH ROLLUP生成匯總行

生成部門及職位的薪資小計和總計：

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY department, job_title WITH ROLLUP;  -- 

3. GROUP_CONCAT合并列值

統(tǒng)計每個用戶購買的所有產(chǎn)品（逗號分隔）：

SELECT user_id, GROUP_CONCAT(product_name SEPARATOR ', ') 
FROM orders
GROUP BY user_id;  -- 

4. 按表達(dá)式/函數(shù)分組

按年份統(tǒng)計訂單數(shù)量：

SELECT YEAR(order_date) AS year, COUNT(*)
FROM orders
GROUP BY YEAR(order_date);  -- 

四、注意事項與常見錯誤

ONLY_FULL_GROUP_BY模式
MySQL 8.0+默認(rèn)啟用該模式，要求SELECT中的非聚合列必須出現(xiàn)在GROUP BY中，否則報錯。

-- 錯誤示例（salary未聚合且未分組）
SELECT department, salary FROM employees GROUP BY department;
-- 修正方法：添加聚合函數(shù)或分組字段
SELECT department, MAX(salary) FROM employees GROUP BY department;

WHERE與HAVING的區(qū)別

• WHERE在分組前過濾行數(shù)據(jù)，不可使用聚合函數(shù)。
• HAVING在分組后過濾組數(shù)據(jù)，必須與聚合條件結(jié)合。

性能優(yōu)化建議

• 在分組列上創(chuàng)建索引（如ALTER TABLE employees ADD INDEX(department)）。
• 避免對大表直接分組，可先通過臨時表或子查詢縮小數(shù)據(jù)范圍。

五、經(jīng)典案例場景

1. 按時間維度聚合

統(tǒng)計每月的銷售總額：

SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount)
FROM sales
GROUP BY year, month;  -- 

2. 多層級統(tǒng)計

分析每個客戶每年的訂單總金額及平均金額：

SELECT customer_id, YEAR(order_date), 
       SUM(total_amount), AVG(total_amount)
FROM orders
GROUP BY customer_id, YEAR(order_date);  -- 

3. 數(shù)據(jù)去重

查找重復(fù)郵箱的用戶：

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;  -- 

六、聚合效率優(yōu)化

在MySQL中優(yōu)化GROUP BY聚合效率需要從索引設(shè)計、查詢邏輯、執(zhí)行引擎特性等多維度入手。以下基于最新優(yōu)化實踐和數(shù)據(jù)庫引擎特性，總結(jié)9大核心優(yōu)化策略：

1、索引優(yōu)化策略

復(fù)合索引精準(zhǔn)匹配分組列
• 創(chuàng)建與GROUP BY順序完全匹配的復(fù)合索引（如GROUP BY a,b則創(chuàng)建(a,b)索引），可觸發(fā)松散索引掃描，減少90%以上的磁盤I/O。
• 典型案例：當(dāng)對(department, job_title)分組時，復(fù)合索引idx_dept_job可使查詢跳過全表掃描，直接通過索引完成分組。

覆蓋索引避免回表
• 確保SELECT列與聚合函數(shù)涉及的列均包含在索引中。例如索引(category, sales)，查詢SELECT category, SUM(sales)時可直接通過索引完成計算，無需訪問數(shù)據(jù)行。

利用函數(shù)索引應(yīng)對復(fù)雜分組
• 對含表達(dá)式的分組（如YEAR(date_col)），創(chuàng)建虛擬列或函數(shù)索引（MySQL 8.0+支持）。例如：

ALTER TABLE orders ADD COLUMN year_date INT AS (YEAR(order_date)) VIRTUAL;
CREATE INDEX idx_year ON orders(year_date);

2、查詢設(shè)計與執(zhí)行優(yōu)化

減少分組字段數(shù)量與復(fù)雜度
• 每增加一個分組字段，排序復(fù)雜度呈指數(shù)級增長。優(yōu)先合并相關(guān)字段（如將province和city合并為region字段）。
• 避免在GROUP BY中使用函數(shù)，否則索引失效。需改寫為基于原字段分組，如將GROUP BY DATE(created_at)改為GROUP BY created_at_date預(yù)計算列。

分階段過濾與聚合
• 先通過子查詢過濾無關(guān)數(shù)據(jù)再分組：

SELECT department, AVG(salary) 
FROM (SELECT * FROM employees WHERE salary > 5000) AS filtered 
GROUP BY department;  -- 比直接HAVING效率提升40%

內(nèi)存排序與臨時表優(yōu)化
• 調(diào)整tmp_table_size和max_heap_table_size參數(shù)（建議設(shè)置為物理內(nèi)存的20%），避免臨時表落盤。
• 監(jiān)控Created_tmp_disk_tables狀態(tài)變量，若頻繁出現(xiàn)磁盤臨時表，需優(yōu)化索引或拆分查詢。

3、高級優(yōu)化技術(shù)

分區(qū)表加速大數(shù)據(jù)處理
• 按時間或業(yè)務(wù)維度分區(qū)（如按月分區(qū)），使GROUP BY僅掃描特定分區(qū)。例如對10億級日志表按event_date分區(qū)后，月度統(tǒng)計耗時從分鐘級降至秒級。

物化視圖與結(jié)果緩存
• 對高頻聚合查詢使用物化視圖（如通過CREATE TABLE mv AS SELECT...定期刷新），減少實時計算壓力。
• 應(yīng)用層緩存重復(fù)查詢結(jié)果（如Redis緩存日匯總數(shù)據(jù)），降低數(shù)據(jù)庫負(fù)載。

并行查詢（MySQL 8.0+）
• 啟用parallel_query功能，通過多線程處理復(fù)雜分組：

SET SESSION optimizer_switch='parallel_query=on';
SELECT region, SUM(revenue) FROM sales GROUP BY region;  -- 利用多核CPU加速

4、診斷工具與注意事項

• 執(zhí)行計劃分析
使用EXPLAIN FORMAT=JSON觀察using_index（是否用索引）、using_temporary（是否用臨時表）、filesort（排序方式）等關(guān)鍵指標(biāo)。

• 嚴(yán)格模式規(guī)避錯誤
啟用ONLY_FULL_GROUP_BY模式，防止非聚合列誤用導(dǎo)致結(jié)果不穩(wěn)定。

性能優(yōu)化對比案例

通過上述策略組合，可系統(tǒng)性解決GROUP BY性能瓶頸。實際應(yīng)用中建議結(jié)合EXPLAIN分析和A/B測試，選擇最適合業(yè)務(wù)場景的優(yōu)化方案。

七、擴(kuò)展知識

• NULL值的處理：GROUP BY將NULL視為獨立分組。
• 排序結(jié)合：分組后使用ORDER BY對結(jié)果排序（如按平均工資降序）。
• 動態(tài)分組：通過CASE WHEN實現(xiàn)條件分組（如按薪資區(qū)間統(tǒng)計）。

通過靈活組合這些功能，GROUP BY可滿足復(fù)雜的數(shù)據(jù)分析需求。實際應(yīng)用中需結(jié)合索引優(yōu)化和查詢邏輯設(shè)計，以提升執(zhí)行效率。

補充：mysql中between的用法

mysql中between的用法

between的介紹

日常sql查詢過程中經(jīng)常要篩選某個屬性或某個表達(dá)式結(jié)果的某個范圍內(nèi)的數(shù)據(jù)，這個時候我們經(jīng)常通過 > 或者 < 來進(jìn)行篩選，有的時候再項目中由于 > 和 < 經(jīng)常會和起始標(biāo)志符沖突，所以需要進(jìn)行轉(zhuǎn)義，這個過程很容易出現(xiàn)一些問題，其實在sql的關(guān)鍵字中，有一個非常實用的關(guān)鍵字可以進(jìn)行范圍查詢，這個關(guān)鍵字就是between，接下來我們就來深入的了解一下between的用法。

between的語法

between關(guān)鍵字是一個邏輯操作符用來篩選指定屬性或表達(dá)式某一范圍內(nèi)或范圍外的數(shù)據(jù)。between關(guān)鍵字常用在where關(guān)鍵字后與select或update或delete共同使用。between的使用語法如下：

expr [NOT] BETWEEN begin_expr AND end_expr;

在整個表達(dá)式中，expr表示的是一個單一的屬性或者是一個計算的表達(dá)式，整個表達(dá)式中的三個參數(shù) expr、begin_expr、end_expr 必須是同一種數(shù)據(jù)類型。

• between篩選的是 expr >= begin_expr并且 expr <= end_expr 的數(shù)據(jù)，如果不存在則返回的是0；
• not between篩選的是 expr < begin_expr或者 expr > end_expr 的數(shù)據(jù)，如果不存在則返回的是0；
• 如果 expr 返回的是 NULL，則between 也返回的是null (暫未驗證)

between的用法

假如我們有一張數(shù)據(jù)庫表如下所示

CREATE TABLE `t_income` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一自增id',
  `income_date` varchar(255) NOT NULL COMMENT '收入年月',
  `amount` float NOT NULL COMMENT '收入金額',
  `target_amount` float NOT NULL DEFAULT '0' COMMENT '目標(biāo)收入',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
  PRIMARY KEY (`id`)  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• 查詢表中amount>=10并且amount<=50的數(shù)據(jù)

select * from t_income where amount between 10 and 50;

• 查詢表中amount 和 target_amount 總和 >=100并且<=500的數(shù)據(jù)

select * from t_income where (amount + target_amount) between 100 and 500;

• 查詢表中create_time 在 2019-01-01 到 2019-09-01 這個日期范圍內(nèi)的數(shù)據(jù)

select * from t_income where create_time between cast('2019-01-01' as DATE) and cast('2019-09-01' as DATE);

• 查詢表中amount < 10 或者 amount > 50 的數(shù)據(jù)

select * from t_income where amount not between 10 and 50;

between的總結(jié)

通過上面的講解，我們現(xiàn)在應(yīng)該已經(jīng)基本的學(xué)會了between的用法，但是如果在開發(fā)中，我們要查詢某個屬性大于某一個值 并且小于某個值的話，我們就只能用 > and < 啦

到此這篇關(guān)于mysql中的group by高級用法詳解的文章就介紹到這了,更多相關(guān)mysql group by用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論