Ask Hoegh(4)——select count(*)和select count(1)、count(column)有區別嗎?
問:select count(*)可以用來查詢表的記錄數,聽說count(1)、count(主鍵)、count(非主鍵列)也可以,這幾個有什麼區別嗎?
答:不考慮Null的情況
答:不考慮Null的情況
- count(1)和count(主鍵)、count(*) 這幾個只掃描主鍵Index就可以得到資料
- count(非主鍵列)是掃描表的
- 所以相對來說,count(1)和count(主鍵)這兩個效率高
- 還有一種寫法是count(ROWID)這也是隻掃描Index的,效率高
-
事實上,效率上的問題不用考慮太多,count(*),count(1)和count(主鍵)都沒錯,結合實際資料庫執行一下,選擇你合適的就可以
- count(主鍵)肯定沒有空值
- count(*)和count(1)能取出含有空值的所有記錄數
- count(非主鍵列)不含空值
以hr測試使用者下的employees表為例,分別輸出查詢結果以及執行計劃,便於大家進行對比分析。
COUNT(*)
----------
107
SQL> select count(1) from employees;
COUNT(1)
----------
107
SQL> select count(employee_id) from employees;--主鍵列
COUNT(EMPLOYEE_ID)
------------------
107
SQL> select count(commission_pct) from employees;--非主鍵列(包含空值)
COUNT(COMMISSION_PCT)
---------------------
35
SQL> select count(rowid) from employees;
COUNT(ROWID)
------------
107
SQL> select count(*) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(1) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(employee_id) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(commission_pct) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 214 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select count(rowid) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1284 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
查詢結果
SQL> select count(*) from employees;COUNT(*)
----------
107
SQL> select count(1) from employees;
COUNT(1)
----------
107
SQL> select count(employee_id) from employees;--主鍵列
COUNT(EMPLOYEE_ID)
------------------
107
SQL> select count(commission_pct) from employees;--非主鍵列(包含空值)
COUNT(COMMISSION_PCT)
---------------------
35
SQL> select count(rowid) from employees;
COUNT(ROWID)
------------
107
執行計劃
SQL> set autot trace expSQL> select count(*) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(1) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(employee_id) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL> select count(commission_pct) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 214 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select count(rowid) from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1284 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
~~~~~~~ the end~~~~~~~~~
hoegh2016.02.03
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30162081/viewspace-1986201/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- select count(*)和select count(1)的區別
- count(*) 和count(column)之區別
- count (*) 和 count (1) 和 count (列名) 區別
- count(*) 和 count(1)和count(列名)區別
- count(*)、count(1)和count(列名)的區別
- count(0),count(1),count(*)總結與count(column)
- count(1),count(*),count(列)的區別
- SQL Server中count(*)和Count(1)的區別SQLServer
- MySQL:SELECT COUNT 小結MySql
- 優化select count(*) from t1優化
- C# 中List中的Count和Count(),有什麼區別C#
- 【優化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)等優化
- MySQL:count(*) count(欄位) 實現上區別MySql
- MySQL的COUNT語句--count(*)、 count(常量)、 count(列名)MySql
- 安裝mysql遇到ERROR: 1136 Column count doesn't match value count at row 1MySqlError
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- mysql中count(1)與count(*)比較MySql
- 圖解MySQL:count(*) 、count(1) 、count(主鍵欄位)、count(欄位)哪個效能最好?圖解MySql
- pymysql.err.OperationalError: (1136, “Column count doesn‘t match value count at row 1“)報錯反省。MySqlError
- 提高MSSQL資料庫效能(1)對比count(*) 和 替代count(*)SQL資料庫
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- Ask Hoegh(5)——buffer cache和buffer有什麼區別?
- 【MySQL】效能優化之 count(*) VS count(col)MySql優化
- springboot jap自定義原生sql 接收SELECT count(*) 的返回long型別結果Spring BootSQL型別
- 不就是SELECT COUNT語句嗎,竟然能被面試官虐的體無完膚面試
- count(*) 優化優化
- count(*)優化優化
- 理解exists count
- Count BFS Graph
- Initianization Parameter (4) : CPU_COUNT
- 還在用SELECT COUNT統計資料庫表的行數?Out了資料庫
- 7.36 BITMAP_COUNT
- count(*)小優化優化
- std::count 函式函式
- 解析Count函式函式
- mysql 1558 - Column count of mysql.proc is wrong 的解決MySql
- ejbql有無max,count等函式???函式
- Terraform中的for_each和countORM