MySQL COUNT(*)性能原理詳解

更新時(shí)間：2022年08月08日 11:50:02 作者：GreatSQL???????

這篇文章主要介紹了MySQL COUNT(*)性能原理詳解，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下

前言

在實(shí)際開發(fā)過程中，統(tǒng)計(jì)一個(gè)表的數(shù)據(jù)量是經(jīng)常遇到的需求，用來統(tǒng)計(jì)數(shù)據(jù)庫表的行數(shù)都會(huì)使用COUNT(*)，COUNT(1)或者COUNT(字段)，但是表中的記錄越來越多，使用COUNT(*)也會(huì)變得越來越慢，今天我們就來分析一下COUNT(*)的性能到底如何。

1.COUNT(1)、COUNT(*)與COUNT(字段)哪個(gè)更快？

執(zhí)行效果：

COUNT(*)MySQL 對(duì)count(*)進(jìn)行了優(yōu)化，count(*)直接掃描主鍵索引記錄，并不會(huì)把全部字段取出來，直接按行累加。
COUNT(1)InnoDB引擎遍歷整張表，但不取值，server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，按行累加。
COUNT(字段)如果這個(gè)“字段”是定義為NOT NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，server 層判斷不能為NULL，按行累加;如果這個(gè)“字段”定義允許為NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，然后把值取出來再判斷一下，不是 NULL才累加。

實(shí)驗(yàn)分析

本文測(cè)試使用的環(huán)境：

[root@zhyno1 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

[root@zhyno1 ~]# uname -a
Linux zhyno1 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

測(cè)試數(shù)據(jù)庫采用的是（存儲(chǔ)引擎采用InnoDB，其它參數(shù)默認(rèn)）：

(Mon Jul 25 09:41:39 2022)[root@GreatSQL][(none)]>select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)

實(shí)驗(yàn)開始：

#首先我們創(chuàng)建一個(gè)實(shí)驗(yàn)表

CREATE TABLE test_count (
  `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(20) NOT NULL,
  `salary` int(1) NOT NULL,
  KEY `idx_salary` (`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入1000W條數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE insert_1000w()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE i<=10000000 DO
        INSERT INTO test_count(name,salary) VALUES('KAiTO',1);
        SET i=i+1;
    END WHILE;
END//
DELIMITER ;
#執(zhí)行存儲(chǔ)過程
call insert_1000w();

接下來我們分別來實(shí)驗(yàn)一下：

COUNT(1)花費(fèi)了4.19秒

(Sat Jul 23 22:56:04 2022)[root@GreatSQL][test]>select count(1) from test_count;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (4.19 sec)

COUNT(*)花費(fèi)了4.16秒

(Sat Jul 23 22:57:41 2022)[root@GreatSQL][test]>select count(*) from test_count;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.16 sec)

COUNT(字段)花費(fèi)了4.23秒

(Sat Jul 23 22:58:56 2022)[root@GreatSQL][test]>select count(id) from test_count;
+-----------+
| count(id) |
+-----------+
|  10000000 |
+-----------+
1 row in set (4.23 sec)

我們可以再來測(cè)試一下執(zhí)行計(jì)劃

COUNT(*)

(Sat Jul 23 22:59:16 2022)[root@GreatSQL][test]>explain select count(*) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

(Sat Jul 23 22:59:48 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

COUNT(1)

(Sat Jul 23 23:12:45 2022)[root@GreatSQL][test]>explain select count(1) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(Sat Jul 23 23:13:02 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(1) AS `count(1)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

COUNT(字段)

(Sat Jul 23 23:13:14 2022)[root@GreatSQL][test]>explain select count(id) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | idx_salary | 4       | NULL | 9980612 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(Sat Jul 23 23:13:29 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(`test`.`test_count`.`id`) AS `count(id)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

需要注意的是COUNT里如果是非主鍵字段的話

(Tue Jul 26 14:01:57 2022)[root@GreatSQL][test]>explain select count(name) from test_count where id <100 ;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

實(shí)驗(yàn)結(jié)果

1.從上面的實(shí)驗(yàn)我們可以得出，COUNT(*)和COUNT(1)是最快的，其次是COUNT(id)。
2.count(*)被MySQL查詢優(yōu)化器改寫成了count(0)，并選擇了idx_salary索引。
3.count(1)和count(id)都選擇了idx_salary索引。

實(shí)驗(yàn)結(jié)論

總結(jié)：COUNT(*)=COUNT(1)>COUNT(id)

MySQL的官方文檔也有說過：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference

翻譯： InnoDB以相同的方式處理SELECT COUNT（*）和SELECT COUNT（1）操作。沒有性能差異

所以說明了對(duì)于COUNT(1)或者是COUNT(*)，MySQL的優(yōu)化其實(shí)是完全一樣的，沒有存在沒有性能的差異。

但是建議使用COUNT(*)，因?yàn)檫@是MySQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法。

2.COUNT(*)與TABLES_ROWS

在InnoDB中，MySQL數(shù)據(jù)庫每個(gè)表占用的空間、表記錄的行數(shù)可以打開MySQL的information_schema數(shù)據(jù)庫。在該庫中有一個(gè)TABLES表，這個(gè)表主要字段分別是：

TABLE_SCHEMA : 數(shù)據(jù)庫名
TABLE_NAME：表名
ENGINE：所使用的存儲(chǔ)引擎
TABLES_ROWS：記錄數(shù)
DATA_LENGTH：數(shù)據(jù)大小
INDEX_LENGTH：索引大小

TABLE_ROWS用于顯示這個(gè)表當(dāng)前有多少行，這個(gè)命令執(zhí)行挺快的，那這個(gè)TABLE_ROWS能代替count(*)嗎？

我們用TABLES_ROWS查詢一下表記錄條數(shù)：

(Sat Jul 23 23:15:14 2022)[root@GreatSQL][test]>SELECT TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'test_count';
+------------+
| TABLE_ROWS |
+------------+
|    9980612 |
+------------+
1 row in set (0.03 sec)

可以看到，記錄的條數(shù)并不準(zhǔn)確，因?yàn)镮nnoDB引擎下TABLES_ROWS行計(jì)數(shù)僅是大概估計(jì)值。

3.COUNT(*)是怎么樣執(zhí)行的?

首先要明確的是，MySQL有多種不同引擎，在不同的引擎中，count(*)有不同的實(shí)現(xiàn)方式，本文主要介紹的是在InnoDB引擎上的執(zhí)行流程

在InnoDB存儲(chǔ)引擎中，count(*)函數(shù)是先從內(nèi)存中讀取表中的數(shù)據(jù)到內(nèi)存緩沖區(qū)，然后掃描全表獲得行記錄數(shù)的。簡(jiǎn)單來說就是全表掃描，一個(gè)循環(huán)解決問題，循環(huán)內(nèi): 先讀取一行，再?zèng)Q定該行是否計(jì)入count循環(huán)內(nèi)是一行一行進(jìn)行計(jì)數(shù)處理的。

在MyISAM引擎中是把一個(gè)表的總行數(shù)存在了磁盤上，因此執(zhí)行count(*)的時(shí)候會(huì)直接返回這個(gè)數(shù)，效率很高。

之所以InnoDB 不跟 MyISAM一樣把數(shù)字存起來，是因?yàn)榧词故窃谕粋€(gè)時(shí)刻的多個(gè)查詢，由于多版本并發(fā)控制(MVCC)的原因，InnoDB表應(yīng)該返回多少行也是不確定的。而且不論是在事務(wù)支持、并發(fā)能力還是在數(shù)據(jù)安全方面，InnoDB都優(yōu)于MyISAM。

雖然如此，InnoDB對(duì)于count(*)操作還是做了優(yōu)化的。InnoDB是索引組織表，主鍵索引樹的葉子節(jié)點(diǎn)是數(shù)據(jù)，而普通索引樹的葉子節(jié)點(diǎn)是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對(duì)于count(*)這樣的操作，遍歷哪個(gè)索引樹得到的結(jié)果邏輯上都是一樣的。因此，MySQL 優(yōu)化器會(huì)找到最小的那棵樹來遍歷。

需要注意的是我們?cè)谶@篇文章里討論的是沒有過濾條件的count(*)，如果加了WHERE條件的話，MyISAM引擎的表也是不能返回得這么快的。

4.總結(jié)

1.COUNT(*)=COUNT(1)>COUNT(id)
2.COUNT函數(shù)的用法，主要用于統(tǒng)計(jì)表行數(shù)。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)
3.因?yàn)?code>COUNT(*)是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，所以MySQL對(duì)他進(jìn)行了很多優(yōu)化，MyISAM中會(huì)直接把表的總行數(shù)單獨(dú)記錄下來供COUNT(*)查詢，而InnoDB則會(huì)在掃表的時(shí)候選擇最小的索引來降低成本。這些優(yōu)化的前提是沒有進(jìn)行WHERE和GROUP的條件查詢。
4.在InnoDB中COUNT(*)和COUNT(1)實(shí)現(xiàn)上沒有區(qū)別，而且效率一樣，但是COUNT(字段)需要進(jìn)行字段的非NULL判斷，所以效率會(huì)低一些。
5.因?yàn)?code>COUNT(*)是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，并且效率高，所以還是建議使用COUNT(*)查詢表的行數(shù)。
6.正如前面COUNT(name)的用例那樣，在建表過程中需要根據(jù)業(yè)務(wù)需求建立性能較高的索引，同時(shí)也要注意避免建立不必要的索引。