MySQL中無過濾條件的count詳解

2024-07-25 19:09:35

字體：大中小

來源：轉載

供稿：網友

count(*)

實現

1、MyISAM：將表的總行數存放在磁盤上，針對無過濾條件的查詢可以直接返回

如果有過濾條件的count(*)，MyISAM也不能很快返回

2、InnoDB：從存儲引擎一行行地讀出數據，然后累加計數

由于MVCC，在同一時刻，InnoDB應該返回多少行是不確定

樣例

假設表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的總行數，拿到的結果卻是不同的

InnoDB默認事務隔離級別是RR，通過MVCC實現

每個事務都需要判斷每一行記錄是否對自己可見

優化

1、InnoDB是索引組織表

聚簇索引樹：葉子節點是數據
二級索引樹：葉子節點是主鍵值

2、二級索引樹占用的空間比聚簇索引樹小很多

3、優化器會在保證邏輯正確的前提下，遍歷最小的索引樹，盡量減少掃描的數據量

針對無過濾條件的count操作，無論遍歷哪一顆索引樹，效果都是一樣的
優化器會為count(*)選擇最優的索引樹

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%

維護計數

緩存

方案

用Redis來保存表的總行數（無過濾條件）
這個表每插入一行，Redis計數+1，每刪除一行，Redis計數-1

缺點

丟失更新

1、Redis可能會丟失更新

2、解決方案：Redis異常重啟后，到數據庫執行一次count(*)

異常重啟并不常見，這時全表掃描的成本是可以接受的

邏輯不精確 – 致命

1、場景：顯示操作記錄的總數和最近操作的100條記錄

2、Redis和MySQL是兩個不同的存儲系統，不支持分布式事務，因此無法拿到精確的一致性視圖

時序A

session B在T3時刻，查到的100行結果里面有最新插入的記錄，但Redis還沒有+1，邏輯不一致

時刻	session A	session B
T1
T2	插入一行數據R;
T3		讀取Redis計數; 查詢最近100條記錄;
T4	Redis計數+1;

時序B

session B在T3時刻，查到的100行結果里面沒有最新插入的記錄，但Redis已經+1，邏輯不一致

時刻	session A	session B
T1
T2	Redis計數+1;
T3		讀取Redis計數; 查詢最近100條記錄;
T4	插入一行數據R;

數據庫

把計數值放到數據庫單獨的一張計數表C中
利用InnoDB的crash-safe的特性，解決了崩潰丟失的問題
利用InnoDB的支持事務的特性，解決了一致性視圖的問題
session B在T3時刻，session A的事務還未提交，表C的計數值+1對自己不可見，邏輯一致

時刻	session A	session B
T1
T2	BEGIN; 表C中的計數值+1;
T3		BEGIN; 讀表C計數值; 查詢最新100條記錄; COMMIT;
T4	插入一行數據R; COMMIT;

count的性能

語義

1、count()是一個聚合函數，對于返回的結果集，一行一行地進行判斷

如果count函數的參數值不是NULL，累計值+1，否則不加，最后返回累計值

2、count(字段F)

字段F有可能為NULL
表示返回滿足條件的結果集里字段F不為NULL的總數

3、count(主鍵ID)、count(1)、count(*)

不可能為NULL
表示返回滿足條件的結果集的總數

4、Server層要什么字段，InnoDB引擎就返回什么字段

count(*)例外，不返回整行，只返回空行

性能對比

count(字段F)

1、如果字段F定義為不允許為NULL，一行行地從記錄里讀出這個字段，判斷通過后按行累加

通過表結構判斷該字段是不可能為NULL

2、如果字段F定義為允許NULL，一行行地從記錄里讀出這個字段，判斷通過后按行累加

通過表結構判斷該字段是有可能為NULL
判斷該字段值是否實際為NULL

3、如果字段F上沒有二級索引，只能遍歷整張表（聚簇索引）

4、由于InnoDB必須返回字段F，因此優化器能做出的優化決策將減少

例如不能選擇最優的索引來遍歷

count(主鍵ID)

InnoDB會遍歷整張表（聚簇索引），把每一行的id值取出來，返回給Server層
Server層拿到id后，判斷為不可能為NULL，然后按行累加
優化器可能會選擇最優的索引來遍歷

count(1)

InnoDB引擎會遍歷整張表（聚簇索引），但不取值
Server層對于返回的每一行，放一個數字1進去，判斷是不可能為NULL，按行累加
count(1)比count(主鍵ID)快，因為count(主鍵ID)會涉及到兩部分操作

解析數據行
拷貝字段值

count(*)

count(*)不會把所有值都取出來，而是專門做了優化，不取值，因為『*』肯定不為NULL，按行累加
不取值：InnoDB返回一個空行，告訴Server層不是NULL，可以計數

效率排序

count(字段F) < count(主鍵ID) < count(1) ≈ count(*)
盡量使用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字段，因此不會遍歷邏輯結果等價的更優選擇idx_create_time

如果選擇idx_create_time，并且返回serial_id字段，這意味著必須回表

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)

優化器選擇了最優的索引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實戰45講》

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。

注：相關教程知識閱讀請移步到MYSQL教程頻道。

上一篇：MySQL中int最大值深入講解

下一篇：mysql 8.0.13手動安裝教程