MySQL COUNT 函數完整用法與效能優化指南

COUNT() 是 MySQL 中最常用的聚合函數，但不同寫法（COUNT(*)、COUNT(col)、COUNT(DISTINCT col)）在語意、效能與適用儲存引擎上有顯著差異。本文系統整理用法陷阱、引擎差異與優化技巧。

三種 COUNT 寫法語意比較

實測差異範例

-- 測試資料
id | name    | password
1  | name1   | 123456
2  | name2   | 123456
3  | name3   | 123456
4  | name4   | NULL

SELECT COUNT(*)       FROM `user`;  -- 4
SELECT COUNT(name)    FROM `user`;  -- 4
SELECT COUNT(password) FROM `user`; -- 3

儲存引擎對 COUNT(*) 效能的決定性影響

• MyISAM：表鎖架構，維護精確行數計數器，COUNT(*) 無 WHERE 時極速回傳（O(1)）。
• InnoDB：支援事務與 MVCC，無全域行數計數器。COUNT(*) 需掃描聚簇索引或次要索引，複雜度 O(N)。
  優化建議：大表常用計數場景，建議維護獨立計數表或 Redis 快取。

GROUP BY 進階分組統計

-- 每類動物數量
SELECT species, COUNT(*) FROM pet GROUP BY species;

-- 每性別數量（含 NULL）
SELECT sex, COUNT(*) FROM pet GROUP BY sex;

-- 種類+性別雙重分組
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

-- 篩選後分組
SELECT species, sex, COUNT(*) 
FROM pet 
WHERE species IN ('dog','cat') 
GROUP BY species, sex;

注意：SELECT owner, COUNT(*) FROM pet 缺少 GROUP BY 會報錯 ERROR 1140。

DISTINCT 進階用法與陷阱

1. 多欄位 DISTINCT 的語意陷阱

SELECT DISTINCT id, type FROM tbl;
-- 實際語意：id 與 type 組合唯一，非單獨 id 唯一

2. 解決「單欄去重但保留其他欄位」的方案

• GROUP_CONCAT（MySQL 4.1+）：SELECT id, GROUP_CONCAT(type) FROM tbl GROUP BY id
• 子查詢/視窗函數（MySQL 8.0+）：SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY type) AS rn FROM tbl 後篩選 rn=1
• 聚合函數輔助：SELECT id, type, COUNT(DISTINCT id) FROM tbl GROUP BY id, type（會產生多餘計數欄位）

效能優化清單

1. 避免 COUNT(col) 全表掃描：若只需總行數且為 MyISAM，用 COUNT(*)；InnoDB 大表考慮計數表。
2. 覆蓋索引優化 COUNT(col)：在 col 上建立索引，查詢可走 Index Only Scan。
3. COUNT(DISTINCT col) 大表優化：近似計數用 APPROX_COUNT_DISTINCT()（MySQL 8.0.13+），或預先彙總到維度表。
4. 分頁總數查詢：分離 SELECT COUNT(*) 與 SELECT ... LIMIT，避免 SQL_CALC_FOUND_ROWS 造成雙重掃描。

理解 COUNT 的語意邊界與引擎特性，是編寫高效 SQL 的基礎功夫。