主頁 > 知識庫 > MySQL中無過濾條件的count詳解

MySQL中無過濾條件的count詳解
熱門標(biāo)簽：淄博400電話申請 電話機(jī)器人市場趨勢 電銷機(jī)器人 行業(yè) 南昌高頻外呼系統(tǒng)哪家公司做的好 百度地圖標(biāo)注后不顯示 電銷機(jī)器人各個(gè)細(xì)節(jié)介紹 溫州瑞安400電話怎么申請 俄國地圖標(biāo)注app 昆明電信400電話辦理

count(*)

實(shí)現(xiàn)

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

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

2、InnoDB：從存儲引擎一行行地讀出數(shù)據(jù)，然后累加計(jì)數(shù)

由于MVCC，在同一時(shí)刻，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）

最后時(shí)刻三個(gè)會話同時(shí)查詢t的總行數(shù)，拿到的結(jié)果卻是不同的

InnoDB默認(rèn)事務(wù)隔離級別是RR，通過MVCC實(shí)現(xiàn)

  • 每個(gè)事務(wù)都需要判斷每一行記錄是否對自己可見

優(yōu)化

1、InnoDB是索引組織表

  • 聚簇索引樹：葉子節(jié)點(diǎn)是數(shù)據(jù)
  • 二級索引樹：葉子節(jié)點(diǎn)是主鍵值

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

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

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

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: 4734976
Max_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ù)計(jì)數(shù)

緩存

方案

  • 用Redis來保存表的總行數(shù)（無過濾條件）
  • 這個(gè)表每插入一行，Redis計(jì)數(shù)+1，每刪除一行，Redis計(jì)數(shù)-1

缺點(diǎn)

丟失更新

1、Redis可能會丟失更新

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

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

邏輯不精確 – 致命

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

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

時(shí)序A

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

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

時(shí)序B

session B在T3時(shí)刻，查到的100行結(jié)果里面沒有最新插入的記錄，但Redis已經(jīng)+1，邏輯不一致

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

數(shù)據(jù)庫

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

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

count的性能

語義

1、count()是一個(gè)聚合函數(shù)，對于返回的結(jié)果集，一行一行地進(jìn)行判斷

如果count函數(shù)的參數(shù)值不是NULL，累計(jì)值+1，否則不加，最后返回累計(jì)值

2、count(字段F)

  • 字段F有可能為NULL
  • 表示返回滿足條件的結(jié)果集里字段F不為NULL的總數(shù)

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

  • 不可能為NULL
  • 表示返回滿足條件的結(jié)果集的總數(shù)

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

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

性能對比

count(字段F)

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

  • 通過表結(jié)構(gòu)判斷該字段是不可能為NULL

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

  • 通過表結(jié)構(gòu)判斷該字段是有可能為NULL
  • 判斷該字段值是否實(shí)際為NULL

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

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

  • 例如不能選擇最優(yōu)的索引來遍歷

count(主鍵ID)

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

count(1)

  1. InnoDB引擎會遍歷整張表（聚簇索引），但不取值
  2. Server層對于返回的每一行，放一個(gè)數(shù)字1進(jìn)去，判斷是不可能為NULL，按行累加
  3. count(1)比count(主鍵ID)快，因?yàn)閏ount(主鍵ID)會涉及到兩部分操作
  • 解析數(shù)據(jù)行
  • 拷貝字段值

count(*)

  1. count(*)不會把所有值都取出來，而是專門做了優(yōu)化，不取值，因?yàn)椤?』肯定不為NULL，按行累加
  2. 不取值：InnoDB返回一個(gè)空行，告訴Server層不是NULL，可以計(jì)數(shù)

效率排序

  1. count(字段F) count(主鍵ID) count(1) ≈ count(*)
  2. 盡量使用count(*)

樣例

mysql> SHOW CREATE TABLE prop_action_batch_reward\G;
*************************** 1. row ***************************
 Table: prop_action_batch_reward
Create 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é)果等價(jià)的更優(yōu)選擇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)

優(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í)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • MySQL 大表的count()優(yōu)化實(shí)現(xiàn)
  • MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧
  • 關(guān)于mysql中innodb的count優(yōu)化問題分享
  • 聊聊MySQL的COUNT(*)的性能
  • 詳解 MySQL中count函數(shù)的正確使用方法
  • 淺談MySQL 統(tǒng)計(jì)行數(shù)的 count
  • mysql count提高方法總結(jié)
  • MySQL中count(*)、count(1)和count(col)的區(qū)別匯總
  • mySQL count多個(gè)表的數(shù)據(jù)實(shí)例詳解
  • MySQL COUNT函數(shù)的使用與優(yōu)化

標(biāo)簽：拉薩 嘉峪關(guān) 吐魯番 葫蘆島 甘南 安徽 洛陽

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL中無過濾條件的count詳解》，本文關(guān)鍵詞  MySQL,中無,過濾,條件,的,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL中無過濾條件的count詳解》相關(guān)的同類信息！
  • 本頁收集關(guān)于MySQL中無過濾條件的count詳解的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章