SQL 中 count(1)、count(*)、count(某列) 的區別

在 SQL 中，count() 是常用的聚合函數，但 count(1)、count(*) 和 count(某列) 的語義和性能存在細微差異，這也是面試中的高頻考點。以下從定義、行為、性能和適用場景四個維度詳細解析：

一、核心定義與行為差異

語法	定義	是否忽略 NULL
count(*)	統計 所有行 的數量，無論列值是否為 NULL。	否
count(1)	對每行插入一個常量值 1，然后統計 1 的數量，本質與 count(*) 相同。	否
count(某列)	統計指定列中非 NULL 值的數量。	是

二、示例說明差異

假設有表 students 如下：

id	name	age
1	Alice	20
2	Bob	NULL
3	NULL	22
4	NULL	NULL

• SELECT count(*)：結果為 4（統計所有行）。
• SELECT count(1)：結果為 4（統計所有行）。
• SELECT count(name)：結果為 2（僅統計 name 列非 NULL 的行）。
• SELECT count(age)：結果為 2（僅統計 age 列非 NULL 的行）。

三、性能差異與優化

1. count(*) 與 count(1)

   • 多數數據庫（如 MySQL、PostgreSQL）：二者執行效率完全相同，優化器會將 count(1) 視為 count(*)。
   • 少數數據庫（如 Oracle）：count(1) 在特定索引掃描時可能略快，但差異可忽略不計。

2. count(某列)

   • 無索引：需逐行掃描，檢查列值是否為 NULL，效率較低。
   • 有索引：若統計的列存在索引，數據庫可能直接通過索引樹快速統計非 NULL 值（無需掃描全量數據行）。

四、適用場景建議

1. 統計總行數：
   推薦使用 count(*)，語義清晰且性能最優。

2. 統計非空值數量：
   使用 count(某列)，例如統計用戶表中填寫了郵箱的用戶數：

   SELECT count(email) FROM users;
   

3. 性能優化場景：

   • 若需頻繁統計非空值，可為該列添加索引。
   • 避免在 count() 中使用表達式（如 count(column+1)），會強制全表掃描。

4. 關鍵區別總結

   函數	統計范圍	是否包含 NULL	性能建議
   COUNT(*)	所有行	? 是	?? 最優
   COUNT(1)	所有行（同 COUNT(*)）	? 是	等同 COUNT(*)
   COUNT(列)	指定列的非 NULL 值	? 否	需檢查列值

五、面試應答要點

1. 行為差異：

   • count(*) 和 count(1) 統計所有行，count(某列) 僅統計非 NULL 值。

2. 性能考量：

   • count(*) 和 count(1) 性能無顯著差異。
   • count(某列) 若有索引則可能更快，否則可能慢于 count(*)。

3. 數據庫特定優化：

   • MySQL 對 count(*) 有特殊優化，即使表無索引也可能快速返回結果（依賴存儲引擎）。
   • Oracle 在大表上 count(1) 可能略優于 count(*)（需結合執行計劃分析）。

示例場景：
假設用戶表 users 有 100 萬行數據，其中 email 列有 20% 的 NULL 值：

• SELECT count(*)：返回 100 萬。
• SELECT count(email)：返回 80 萬。
• 若 email 列有索引，count(email) 可能比 count(*) 更快（索引掃描比全表掃描高效）。

---

六、索引掃描與全表掃描

在數據庫中，索引掃描（Index Scan）比全表掃描（Table Scan）更高效的核心場景是：當查詢只需訪問少量數據時。以下是詳細分析和常見面試考點：

1. 索引掃描的觸發條件

索引掃描通常在以下情況觸發：

• 過濾條件有效：查詢的WHERE子句包含索引列（如WHERE age > 30），且過濾后的數據量較小（通常認為占全量數據的5-10%以下）。
• 覆蓋索引：查詢所需的字段（如SELECT id, age）全部包含在索引中，無需回表查詢。
• 索引有序性：查詢需要排序（如ORDER BY age），且索引本身有序（避免額外排序操作）。

2. 全表掃描的適用場景

全表掃描通常在以下情況更優：

• 過濾條件不高效：例如WHERE name LIKE '%張%'（左模糊無法利用索引）。
• 查詢大部分數據：例如WHERE age > 10（若大部分數據滿足條件，全表掃描可能更快）。
• 無合適索引：查詢字段未建立索引，或索引選擇性低（如性別字段）。

3. 常見面試問題點

Q1：索引掃描一定比全表掃描快嗎？

A：不一定。

• 反例：當查詢需要返回大部分數據（如WHERE age > 10），全表掃描可能更快。因為索引掃描需先訪問索引，再回表查詢數據，若數據量過大，IO成本反而更高。

Q2：如何判斷數據庫是否選擇索引掃描？

A：通過執行計劃（如MySQL的EXPLAIN）查看：

• type字段為index或range表示使用索引掃描。
• type字段為ALL表示全表掃描。

Q3：索引選擇性對掃描方式的影響？

A：索引選擇性（Unique Ratio）指索引列中不同值的數量與總行數的比例。

• 高選擇性（如用戶ID）：索引掃描效率高。
• 低選擇性（如性別）：可能導致優化器放棄索引，選擇全表掃描。

Q4：如何優化索引掃描？

A：

• 創建復合索引（如(age, gender)）覆蓋常用查詢。
• 避免索引冗余（如已有(a, b)索引，無需單獨創建(a)索引）。
• 使用覆蓋索引減少回表。

Q5：數據庫如何選擇掃描方式？

A：優化器基于以下因素估算成本：

• 索引選擇性和統計信息。
• 表數據量和分布。
• 內存和IO性能。

4. 示例對比分析

假設表users有100萬行數據，age列有索引：

查詢場景	掃描方式	原因
WHERE age = 20（1萬行）	索引掃描	過濾后數據量小，索引掃描效率高。
WHERE age > 10（90萬行）	全表掃描	過濾后數據量大，全表掃描避免多次IO。
SELECT id, age WHERE age=20	索引掃描（覆蓋）	索引包含所有所需字段，無需回表。
SELECT * WHERE name LIKE '%張%'	全表掃描	左模糊無法利用索引。

總結

• 索引掃描優勢：過濾少量數據、覆蓋索引、利用有序性。
• 全表掃描優勢：查詢大量數據、無合適索引、過濾條件低效。
• 核心原則：索引是為了減少IO，若索引掃描的IO成本反而更高，優化器會選擇全表掃描。