MySQL Count函數使用教學

2022-12-15 14:03:58

一、COUNT 的使用

select COUNT(key1) FROM t;

這個語句是用於統計在 t 表 key1 列值不為 NULL 的行數是多少。

看下面的這個：

select COUNT('abc') FROM t;

這個語句是用於統計在 t 表的所有記錄中，‘abc’ 這個表示式不為 NULL的行數是多少。很顯然，‘abc’ 這個表示式永遠不為 NULL, 所以上述語句其實就是統計 t 表裡有多少條記錄。

再看這個：

select COUNT(*) from t;

這個語句就是直接統計 t 表中有多少條記錄。

總結 + 注意：COUNT函數的引數可以是任意表示式，該函數用於統計在符合搜尋條件的記錄中，指定的表示式不為NULL的行數有多少。

二、COUNT是怎麼樣執行的

mysql> select count(*) from single_table;
+----------+
| count(*) |
+----------+
|    12610 |
+----------+
1 row in set (0.00 sec)
####### single_table 表結構 ########
CREATE TABLE `single_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=20000 DEFAULT CHARSET=utf8mb3 |

這個語句是要去查詢表 single_table 中共包含多少條記錄。由於聚簇索引和二級索引中的記錄是一一對應的，而二級索引記錄中包含的列是少於聚簇索引記錄的，所以同樣數量的二級索引記錄可以比聚簇索引記錄佔用更少的儲存空間。如果我們使用二級索引執行上述查詢，即數一下idx_key2中共有多少條二級索引記錄（存在多個二級索引，為什麼選擇idx_key2，下面會具體說明），是比直接數聚簇索引中共有多少聚簇索引記錄可以節省很多I/O成本。所以優化器會決定使用idx_key2執行上述查詢。

mysql> explain select count(*) from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key2 | 5       | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在執行上述查詢時，server層會維護一個名叫count的變數，然後：

（1）server層向InnoDB要第一條記錄。

（2）InnoDB找到idx_key1的第一條二級索引記錄，並返回給server層（注意：由於此時只是統計記錄數量，所以並不需要回表）。

（3）由於COUNT函數的引數是*，MySQL會將*當作常數0處理。由於0並不是NULL，server層給count變數加1。

（4）server層向InnoDB要下一條記錄。

（5）InnoDB通過二級索引記錄的next_record屬性找到下一條二級索引記錄，並返回給server層。

（6）server層繼續給count變數加1。

（7）... 重複上述過程，直到InnoDB向server層返回沒記錄可查的訊息。

（8）server層將最終的count變數的值傳送到使用者端。

三、COUNT函數的索引使用情況

下面我們增對 count(*)，count(1)，count(常數)，count(主鍵列)，count(普通列（有索引）)，count（普通列（無索引））

（1）count(*)，count(1)，count(常數)

mysql> show create table single_table;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| single_table | CREATE TABLE `single_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=20000 DEFAULT CHARSET=utf8mb3 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from single_table;
+----------+
| count(*) |
+----------+
|    12610 |
+----------+
1 row in set (0.00 sec)
## count(*) 採用了 idx_key2 索引
mysql> explain select count(*) from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key2 | 5       | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
## count(1) 採用了 idx_key2 索引
mysql> explain select count(1) from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key2 | 5       | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
## count('abc') 採用了 idx_key2 索引
mysql> explain select count('abc') from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key2 | 5       | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通過上述查詢結果可以看出：

count(*)、count(1)、count('abc') 均採用了 idx_key2，而索引idx_key2 對應的索引列為 key2，欄位型別為 int，佔用空間為最小的索引列。

結論：

對於 COUNT(*)、COUNT(1) 或者任意的 COUNT(常數) 來說，讀取哪個索引的記錄其實並不重要，因為server層只關心儲存引擎是否讀到了記錄，而並不需要從記錄中提取指定的欄位來判斷是否為NULL。所以優化器會使用佔用儲存空間最小的那個索引來執行查詢。

（2）count(主鍵列)

## count(id) 採用了 idx_key2 索引
mysql> explain select count(id) from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key2 | 5       | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

通過上述查詢結果可以看出：

count(id)採用了 idx_key2，而索引idx_key2 對應的索引列為 key2，欄位型別為 int，佔用空間為最小的索引列。

結論：

對於 COUNT(id) 來說，由於id是主鍵，不論是聚簇索引記錄，還是任意一個二級索引記錄中都會包含主鍵欄位，所以其實讀取任意一個索引中的記錄都可以獲取到id欄位，此時優化器也會選擇佔用空間最小的那個索引來執行查詢。

（3）count(普通列（有索引）)

## count('key1') 採用了 idx_key1 索引
mysql> explain select count(key1) from single_table;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key1 | 303     | NULL | 12590 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
## count(common_field) 未採用任何索引
mysql> explain select count(common_field) from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12590 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通過上述查詢結果可以看出：

count(key1)採用了 idx_key1，索引idx_key1對應的索引列即為key1。count(common_field)未採用任何索引，common_field也不存在任何索引。

結論：

對於COUNT(非主鍵列)來說，我們指定的列可能並不會包含在每一個索引中。這樣優化器只能選擇包含我們指定的列的索引去執行查詢，這就可能導致優化器選擇的索引並不是最小的那個。

四、補充

對於count(非空普通列)來說，使用索引情況會怎麼樣？會不會直接採用最小佔用空間索引呢？

mysql> show create table person_info;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                     |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person_info | CREATE TABLE `person_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `birthday` date NOT NULL,
  `age` int DEFAULT NULL,
  `phone_number` char(11) NOT NULL,
  `country` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> explain select count(phone_number) from person_info;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | person_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通過上述查詢結果可以看出：

雖然 phone_number 欄位為 not null，count(phone_number) 和 count(*) 結果一致，但是 phone_number 仍然並有選擇走索引。

五、總結

（1）對於COUNT(*)、COUNT(常數)、COUNT(主鍵) 形式的COUNT函數來說，優化器可以選擇最小索引執行查詢，從而提升效率，它們的執行過程是一樣的，只不過在判斷表示式是否為NULL時選擇不同的判斷方式，這個判斷為NULL的過程的代價可以忽略不計，所以我們可以認為 COUNT(*)、COUNT(常數)、COUNT(主鍵) 所需要的代價是相同的。

（2）對於 COUNT(非主鍵列) 來說，server層必須要從InnoDB中讀到包含非主鍵列的記錄，所以優化器並不能隨心所欲的選擇佔用空間最小的索引去執行。

到此這篇關於MySQL Count函數使用教學的文章就介紹到這了,更多相關MySQL Count內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL Count函數使用教學

目錄

一、COUNT 的使用

二、COUNT是怎麼樣執行的

三、COUNT函數的索引使用情況

四、補充

五、總結

熱門文章