select count( *)應該是一個比較常用的語句,用來統計記錄行數。但是,慢慢地你會發現,這個語句越來越慢了,為什麼呢?
MySQL 中,不同的存儲引擎,count( *)的實現方式是不同的。
1、MyISAM 引擎,比較簡單粗暴,直接將表的總行數存儲在磁碟上,因此效率很高;
2、InnoDB 引擎中,執行count(*)時會遍歷全表,需要一行行的把數據查出來,因此性能較差;
為啥 MyISAM 就可以這樣做呢?因為它不支持事務啊,不用擔心數據不一致的問題,因此可以快速返回count(*)。
假設,表 t 中有 1000 條數據,有下面三個用戶並行的會話:
1、A 啟動事務,查詢表的總行數;
2、C 直接插入一條數據,然後查詢總行數;
3、B 啟動事務,插入一條數據,然後查詢總行數;
4、C 查詢總行數;
注意,上面啟動的事務都沒有提交。
A、B、C 查詢的結果都不相同。
B 讀到的是 1002,是因為可重複讀隔離級別的存在。
而 C 未開啟事務,因此無法看到別的事務的更新。
InnoDB 中,主鍵索引存儲的是數據,輔助索引存儲的只是主鍵值。輔助索引比主鍵索引小得多,輕量得多。
這種情況下,InnoDB 在執行count( *)時,就會判斷使用哪個索引,會選擇最小的樹來進行遍歷。在保證邏輯正確的前提下,盡量減少掃描的數據量,是資料庫系統設計的通用法則之一。
綜上,InnoDB 引擎中,在每一個會話中,都需要逐行讀取數據,對 count( *) 優化,然後計數返回總行數。
count( *)、count( 1)、count( 主鍵)、count( 欄位)的區別
(1)對於count(主鍵id),InnoDB 會遍歷全表,取每行的主鍵 id,返回給 server 層,server 層拿到數據後,進行判斷累加。
(2)對於count(1),InnoDB 仍遍歷全表,但是不取值,server 層對返回的每一行數據新增一個 1,然後進行判斷累加;
(3)對於count( 欄位):
1、如果這個「欄位」是定義為 not null 的話,一行行地從記錄裡面讀出這個欄位,判斷不能為 null,按行累加
2、如果這個「欄位」定義允許為 null,那麼執行的時候,判斷到有可能是 null,還要把值取出來再判斷一下,不是 null 才累加。
性能區別:
1.列名為主鍵, count( 1) = count( *)= count( 列名)
2.列名不為主鍵, count( 1) = count( *) > count( 列名)
3.列名不為主鍵, 列名創建索引count( 1) = count(*)= count( 列名)
使用選擇:
1.COUNT( *)和COUNT( 1)本質上一樣,具體響應時間跟存儲引擎和WHERE條件有關。個人習慣使用COUNT( *)。
2.索引對COUNT()函數很重要,如果要用到索引,MySQL會自動優化使用合適的索引。
3.COUNT( 列名)需要注意統計的是非NULL的列。
以上就是有關mysql中count( *)、count( 1)、count( 主鍵)、count( 欄位)的區別,要了解更多資料庫知識請上學步園。