【MySQL】COUNT
- 2019 年 10 月 3 日
- 筆記
count(*)、count(1)、count(主鍵)、count(欄位)的執行效率
在沒有where條件的情況下
MyISAM引擎返回結果會比InnoDB快上很多，主要是因為MyISAM會單獨記錄了表的總行數，而InnoDB沒有這麼做。
為什麼沒有這麼做呢？主要InnoDB支援了事務的原因，在事務中不同的版本上查詢出來的結果是不一樣的。例如表中總行數現有10條，事務A啟動後未查詢，這時啟動事務B對錶插入一條數據。這時候事務A查詢錶行數為10條，事務B查詢得行數為11條。InnoDB默認使用了可重複讀的隔離級別。
mysql中有個show table status的查詢，這個查詢結果中記錄了錶行數的欄位Rows。查詢執行速度很快，但這個結果不可以用，因為這個結果是mysql取樣估算得來的，比較不準確。
對錶數據為54萬的數據進行查詢比較，其中a欄位未加索引可為空，d欄位未加索引不可為空，b欄位加了索引不可為空，c欄位加了索引可為空。
執行結果耗時：
[SQL] -- 1 select count(*) from cyj_test ; 受影響的行: 0 時間: 0.086ms [SQL] -- 2 select count(1) from cyj_test; 受影響的行: 0 時間: 0.083ms [SQL] -- 3 select count(id) from cyj_test; 受影響的行: 0 時間: 0.101ms [SQL] -- 4 未加索引可為空 select count(a) from cyj_test; 受影響的行: 0 時間: 0.635ms [SQL] -- 5 加了索引不可為空 select count(b) from cyj_test; 受影響的行: 0 時間: 0.101ms [SQL] -- 6 加了索引可為空 select count(c) from cyj_test; 受影響的行: 0 時間: 0.129ms [SQL] -- 7 未加索引不可為空 select count(d) from cyj_test; 受影響的行: 0 時間: 0.426ms根據執行時間可得執行效率為：count(*)≈count(1)>count(主鍵)≈>count(加了索引不可為空欄位)>count(加了索引可為空欄位)>count(未加了索引不可為空欄位)>count(未加了索引可為空欄位)
EXPLAIN結果
-- 1 EXPLAIN select count(*) from cyj_test ;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | index | idex_b | 4 | 544598 | 100 | Using index |
-- 2 EXPLAIN select count(1) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | index | idex_b | 4 | 544598 | 100 | Using index |
-- 3 EXPLAIN select count(id) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | index | idex_b | 4 | 544598 | 100 | Using index |
-- 4 未加索引可為空 EXPLAIN select count(a) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | ALL | 544598 | 100 |
-- 5 加了索引不可為空 EXPLAIN select count(b) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | index | idex_b | 4 | 544598 | 100 | Using index |
-- 6 加了索引可為空 EXPLAIN select count(c) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | index | idex_c | 123 | 544598 | 100 | Using index |
-- 7 未加索引不可為空 EXPLAIN select count(d) from cyj_test;| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | cyj_test | ALL | 544598 | 100 |
EXPLAIN結果得知未加索引的會遍歷全表掃描得到查詢結果，沒有走索引，所以4和7查詢速度會比其他慢了很多。
count(*)、count(1)、count(id)、count(b)都走了index_b的索引，count(c)走了index_c的索引。這裡你可能會有幾個問題要問：
1、count(*)、count(1)、count(id)為什麼不走主鍵索引而走了index_b呢？
因為mysql默認使用了InnoDB，索引是B+樹的形式。這裡主鍵索引的頁子節點存的是數據，而普通索引樹存的是主鍵值，所以主鍵索引肯定比普通索引樹的大很多，優化器會使用找到的那棵最小的樹來進行遍歷，所以走了
index_b。
2、那為什麼走了index_b而不是走了index_c呢？
從
EXPLAIN結果得知，index_b的key_len為4，index_c的key_len為123，key_len表示索引中使用的位元組數，所以肯定使用index_b的數據量更小。
從EXPLAIN我們簡單得知了沒加索引會比加了索引的查詢慢了很多，那麼都加了索引的情況下會是怎麼樣的呢？其實是mysql對count()、count(1)、count(id)、count(b)、count(c)的判斷各不相同導致的。註：取值和不取值會影響執行速度，因為取值會對數據行進度解析以得到想要的欄位。
count(*)
InnoDB遍歷整張表，但不取值，count(*)肯定不為空，按行累加就行了。
count(1)
InnoDB遍歷整張表，但不取值，server層對於每一行數據返回1，判斷1不可能空，按行累加。
count(id)
InnoDB遍歷整張表，把每一行的id取出來返回給server層，server層判斷不可能為空，按行累加。
count(不可為空欄位)
InnoDB遍歷整張表，把每一行的這個欄位取出來返回給server層，server層判斷不可能為空，按行累加。
count(可空欄位)
InnoDB遍歷整張表，把每一行的這個欄位取出來返回給server層，server層判斷是不是為空，不為空的按行累加。
count(判斷 or null)
假設存在一張子任務表，表主要資訊如下：
CREATE TABLE `app_task_child` ( `task_child_id` varchar(40) NOT NULL, `status` int(11) NOT NULL DEFAULT '1' COMMENT '1.待提交；2.審核中；3.已提交；4.已歸檔；', `task_id` varchar(40) DEFAULT NULL COMMENT '母任務', PRIMARY KEY (`task_child_id`), KEY `FK6m...` (`task_id`), CONSTRAINT `FK6m...` FOREIGN KEY (`task_id`) REFERENCES `app_task` (`task_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;現在有一個需求：統計出各任務下的子任務數、已歸檔數、審核中數的數據。
SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 4) AS ongoingNum, count(t.STATUS = 2) AS archiveNum FROM app_task_child t GROUP BY t.task_id
上面的SQL會查詢出圖一的數據來，這數據一看就知道不對，已歸檔數和審核中的數量肯定錯了。文章上面大概有說到一個意思：count計算的是除了NULL值，其他數據都會加1，例如0或false也都是會加數量1。
t.STATUS = ?判斷為false或true，所以count總為加1，導致結果總跟子任務數是一樣的。那麼就需要想辦法當為false時把結果置為NULL。例如有下面兩種方法都能得到正確的結果：
-- 方法一 SELECT SQL_NO_CACHE t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(IF(t. STATUS = 4, true, NULL)) AS ongoingNum, count(IF(t. STATUS = 2, true, NULL)) AS archiveNum FROM app_task_child t GROUP BY t.task_id-- 方法二 SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 4 or NULL) AS ongoingNum, count(t.STATUS = 2 or NULL) AS archiveNum FROM app_task_child t GROUP BY t.task_id方法一的不難理解，這裡不進行說明。
方法二(判斷 or NULL)可以理解為當判斷為0時，會走or後面的表達式，當判斷為1時，不走or後面的表達式。判斷為1的直接count為1，判斷為0時進行NULL的表達式判斷，而且0 or NULL為NULL。
在mysql中的or和and判斷不像java那樣，更像是JavaScript這種弱類型語言的判斷，可以把NULL直接進行判斷。例如下圖中的判斷結果
count(判斷 or null)性能怎麼樣？
對面上的表進行加status索引。
ALTER TABLE `app_task_child` ADD INDEX `index_status` (`status`) USING BTREE ;執行sql
-- 寫法一 EXPLAIN SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(t.STATUS = 2 or null) AS archiveNum FROM app_task_child t GROUP BY t.task_id;結果為：
| … | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|
| … | index | FK6m… | FK6m… | 123 | 39 | 100 |
執行sql
-- 寫法二 EXPLAIN SELECT t.task_id AS taskId, count(t.task_child_id) AS taskChildNum, count(*) AS archiveNum FROM app_task_child t where t.status = 2 GROUP BY t.task_id;結果為：
| … | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|
| … | ref | FK6m…,index_status | index_status | 123 | const | 1 | 100 | Using index condition; Using temporary; Using filesort |
就只單單從type欄位一個為ref一個為index就可得知寫法二性能完爆寫法一（可以參考別人的文章）
。那麼為什麼上面不用寫法二呢？實際開發中統計的往往不只統計一個num，可能會統計八九個。所以如果使用寫法二，需要寫八九個SQL去執行，而寫法一隻需要一條SQL搞定。還有就是這時寫法二花費在資料庫連接上的損耗加起來往往是比寫法一性能更差些。
如果不在status欄位上加索引，EXPLAIN比較出來的結果也是方法二性能稍微好一點，這點大家可以自己試一下
