count(*)
實(shí)現(xiàn)
1、MyISAM：將表的總行數(shù)存放在磁盤上，針對無過濾條件的查詢可以直接返回
如果有過濾條件的count(*)，MyISAM也不能很快返回
2、InnoDB：從存儲引擎一行行地讀出數(shù)據(jù)，然后累加計數(shù)
由于MVCC，在同一時刻，InnoDB應(yīng)該返回多少行是不確定
樣例
假設(shè)表t有10000條記錄
| session A | session B | session C |
|---|---|---|
| BEGIN; | ||
| SELECT COUNT(*) FROM t;（返回10000） | ||
| INSERT INTO t;（插入一行） | ||
| BEGIN; | ||
| INSERT INTO t（插入一行）; | ||
| SELECT COUNT(*) FROM t;（返回10000） | SELECT COUNT(*) FROM t;（返回10002） | SELECT COUNT(*) FROM T;（返回10001） |
最后時刻三個會話同時查詢t的總行數(shù)，拿到的結(jié)果卻是不同的
InnoDB默認(rèn)事務(wù)隔離級別是RR，通過MVCC實(shí)現(xiàn)
優(yōu)化
1、InnoDB是索引組織表
2、二級索引樹占用的空間比聚簇索引樹小很多
3、優(yōu)化器會在保證邏輯正確的前提下，遍歷最小的索引樹，盡量減少掃描的數(shù)據(jù)量
show table status
mysql> SHOW TABLE STATUS/G;*************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 100256 Avg_row_length: 47 Data_length: 4734976Max_data_length: 0 Index_length: 5275648 Data_free: 0 Auto_increment: NULL Create_time: 2019-02-01 17:49:07 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:
SHOW TABLE STATUS同樣通過采樣來估算（非常不精確），誤差能到40%~50%
維護(hù)計數(shù)
緩存
方案
缺點(diǎn)
丟失更新
1、Redis可能會丟失更新
2、解決方案：Redis異常重啟后，到數(shù)據(jù)庫執(zhí)行一次count(*)
邏輯不精確 – 致命
1、場景：顯示操作記錄的總數(shù)和最近操作的100條記錄
2、Redis和MySQL是兩個不同的存儲系統(tǒng)，不支持分布式事務(wù)，因此無法拿到精確的一致性視圖
時序A
session B在T3時刻，查到的100行結(jié)果里面有最新插入的記錄，但Redis還沒有+1，邏輯不一致
| 時刻 | session A | session B |
|---|---|---|
| T1 | ||
| T2 | 插入一行數(shù)據(jù)R; | |
| T3 | 讀取Redis計數(shù); 查詢最近100條記錄; | |
| T4 | Redis計數(shù)+1; |
時序B
session B在T3時刻，查到的100行結(jié)果里面沒有最新插入的記錄，但Redis已經(jīng)+1，邏輯不一致
| 時刻 | session A | session B |
|---|---|---|
| T1 | ||
| T2 | Redis計數(shù)+1; | |
| T3 | 讀取Redis計數(shù); 查詢最近100條記錄; | |
| T4 | 插入一行數(shù)據(jù)R; |
數(shù)據(jù)庫
| 時刻 | session A | session B |
|---|---|---|
| T1 | ||
| T2 | BEGIN; 表C中的計數(shù)值+1; | |
| T3 | BEGIN; 讀表C計數(shù)值; 查詢最新100條記錄; COMMIT; | |
| T4 | 插入一行數(shù)據(jù)R; COMMIT; |
count的性能
語義
1、count()是一個聚合函數(shù)，對于返回的結(jié)果集，一行一行地進(jìn)行判斷
如果count函數(shù)的參數(shù)值不是NULL，累計值+1，否則不加，最后返回累計值
2、count(字段F)
3、count(主鍵ID)、count(1)、count(*)
4、Server層要什么字段，InnoDB引擎就返回什么字段
性能對比
count(字段F)
1、如果字段F定義為不允許為NULL，一行行地從記錄里讀出這個字段，判斷通過后按行累加
2、如果字段F定義為允許NULL，一行行地從記錄里讀出這個字段，判斷通過后按行累加
3、如果字段F上沒有二級索引，只能遍歷整張表（聚簇索引）
4、由于InnoDB必須返回字段F，因此優(yōu)化器能做出的優(yōu)化決策將減少
count(主鍵ID)
count(1)
count(*)
效率排序
樣例
mysql> SHOW CREATE TABLE prop_action_batch_reward/G;*************************** 1. row *************************** Table: prop_action_batch_rewardCreate Table: CREATE TABLE `prop_action_batch_reward` ( `id` bigint(20) NOT NULL, `source` int(11) DEFAULT NULL, `serial_id` bigint(20) NOT NULL, `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `user_ids` mediumtext, `serial_index` tinyint(4) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uniq_serial_id_source_index` (`serial_id`,`source`,`serial_index`), KEY `idx_create_time` (`create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
count(字段F)
無索引
user_ids上無索引，而InnoDB又必須返回user_ids字段，只能遍歷聚簇索引
mysql> EXPLAIN SELECT COUNT(user_ids) FROM prop_action_batch_reward;+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+| 1 | SIMPLE | prop_action_batch_reward | ALL | NULL | NULL | NULL | NULL | 16435876 | NULL |+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+mysql> SELECT COUNT(user_ids) FROM prop_action_batch_reward;+-----------------+| count(user_ids) |+-----------------+| 17689788 |+-----------------+1 row in set (10.93 sec)
有索引
1、serial_id上有索引，可以遍歷uniq_serial_id_source_index
2、但由于InnoDB必須返回serial_id字段，因此不會遍歷邏輯結(jié)果等價的更優(yōu)選擇idx_create_time
mysql> EXPLAIN SELECT COUNT(serial_id) FROM prop_action_batch_reward;+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+| 1 | SIMPLE | prop_action_batch_reward | index | NULL | uniq_serial_id_source_index | 15 | NULL | 16434890 | Using index |+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+mysql> SELECT COUNT(serial_id) FROM prop_action_batch_reward;+------------------+| count(serial_id) |+------------------+| 17705069 |+------------------+1 row in set (5.04 sec)
count(主鍵ID)
優(yōu)化器選擇了最優(yōu)的索引idx_create_time來遍歷，而非聚簇索引
mysql> EXPLAIN SELECT COUNT(id) FROM prop_action_batch_reward;+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16436797 | Using index |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+mysql> SELECT COUNT(id) FROM prop_action_batch_reward;+-----------+| count(id) |+-----------+| 17705383 |+-----------+1 row in set (4.54 sec)
count(1)
mysql> EXPLAIN SELECT COUNT(1) FROM prop_action_batch_reward;+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437220 | Using index |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+mysql> SELECT COUNT(1) FROM prop_action_batch_reward;+----------+| count(1) |+----------+| 17705808 |+----------+1 row in set (4.12 sec)
count(*)
mysql> EXPLAIN SELECT COUNT(*) FROM prop_action_batch_reward;+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+| 1 | SIMPLE | prop_action_batch_reward | index | NULL | idx_create_time | 5 | NULL | 16437518 | Using index |+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+mysql> SELECT COUNT(*) FROM prop_action_batch_reward;+----------+| count(*) |+----------+| 17706074 |+----------+1 row in set (4.06 sec)
參考資料
《MySQL實(shí)戰(zhàn)45講》
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選
