CNDBA社区
1 count 函数说明
count 函数用于返回表中行数，有三种用法：
- COUNT(expr)
- COUNT(DISTINCT expr,[expr…])
- COUNT(*)/ COUNT(1)
链接如下：
https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_count
COUNT(expr)
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
If there are no matching rows, COUNT() returns 0.
mysql> SELECT student.student_name,COUNT(*)
FROM student,course
WHERE student.student_id=course.student_id
GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.
For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.
InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.
Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.
Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of rows with different non-NULL expr values.
If there are no matching rows, COUNT(DISTINCT) returns 0.
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...).
官网讲的比较清楚：
- COUNT(*) 和 COUNT(1) 是等价的，没有区别
- COUNT(*) 包含所有行
- COUNT(expr)只包含非空的行。
另外就是从 MySQL 5.7.18 开始， 通过遍历最小的可用二级索引来InnoDB处理SELECT COUNT( *)语句，除非索引或优化器提示指示优化器使用不同的索引。如果二级索引不存在，则扫描聚集索引。
2 测试示例
2.1 创建测试表
mysql> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE test_counts ( id INT AUTO_INCREMENT PRIMARY KEY, index_col INT, normal_col VARCHAR(255), INDEX (index_col) );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test_counts (index_col, normal_col)
-> VALUES
-> (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'),
-> (6, 'F'), (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
2.2 查看不同SQL的执行计划
-- 查看 count(*) 的执行计划
mysql> EXPLAIN SELECT COUNT(*) FROM test_counts;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_counts | NULL | index | NULL | index_col | 5 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 查看 count(1) 的执行计划
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_counts | NULL | index | NULL | index_col | 5 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 查看 count(id) 的执行计划（id 是主键）
mysql> EXPLAIN SELECT COUNT(id) FROM test_counts;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_counts | NULL | index | NULL | index_col | 5 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
-- 查看 count(index_col) 的执行计划（index_col 是带索引的字段）
mysql> EXPLAIN SELECT COUNT(index_col) FROM test_counts;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_counts | NULL | index | NULL | index_col | 5 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- 查看 count(normal_col) 的执行计划（normal_col 是不带索引的字段）
mysql> EXPLAIN SELECT COUNT(normal_col) FROM test_counts;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_counts | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
说明如下：
- 对于 count(*) 和 count(1)，MySQL 通常会扫描整个表或索引来计数，这两个操作在逻辑上是等价的，并且都会计算所有行的数量，不考虑列值是否为 NULL。在大多数情况下，执行计划会显示为全表扫描（ALL）或使用某个索引（如果优化器认为这样更高效）。
- 对于 count(index_col)，如果 index_col 上有索引，MySQL 可能会使用该索引来计数。如果 index_col 包含 NULL 值，则 COUNT(index_col) 只会计算非 NULL 值的数量。执行计划可能会显示使用 index_col 上的索引。
- 对于 count(normal_col)，由于 normal_col 没有索引，MySQL 通常会进行全表扫描来计算行数。执行计划可能会显示全表扫描（ALL）。
- 对于 count(id)，由于 id 是主键，MySQL 通常会使用主键索引来快速计算行数。执行计划可能会显示使用主键索引（PRIMARY）。注意我们这里不是，主键索引通常是聚集索引，它将数据行按照主键的顺序存储在磁盘上。虽然主键索引对于按主键查找数据非常高效，但对于 COUNT 操作，它可能不是最优选择。所以这里查询优化器认为使用index_col索引进行扫描的成本更低，所以选择使用了index_col索引。
如果想强制使用主键索引，可以使用FORCE INDEX 来强制指定：
mysql> EXPLAIN SELECT COUNT(id) FROM test_counts FORCE INDEX (PRIMARY);
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_counts | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
版权声明：本文为博主原创文章，未经博主允许不得转载。
