相信大多数DBA都看见过这样一条SQL优化原则:用count(1)替换count(*);相信也有不少DBA因这个问题被开发diss过,用count(*)非常慢,应该用count(1),然后改用count(1)后,还真是秒出结果;那么究竟是什么回事呢?count(1)真的比count(*)快那么多吗?count(1)和count(*)的区别究竟在哪里?接下来我们就来一一揭晓。
(1)首先看看下面这个测试结果,count(1)还真是比count(*)快很多?先别着急下结论,我们继续往下看。
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.03 sec)
mysql> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
(2)接下来看看profile,可以发现,count(*)慢是因为存在大量物理读,而count(1)快是因为其均为逻辑读;原因也很简单,第一次执行count(*)时,需要将数据从磁盘读到buffer pool,后续count(1)只需要从buffer pool读取数据即可
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 6.02750600 | select count(*) from sbtest1 |
| 2 | 1.44906050 | select count(1) from sbtest1 |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000079 | 0.000037 | 0.000034 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000005 | 0.000003 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000015 | 0.000008 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
| init | 0.000018 | 0.000009 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000007 | 0.000004 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000012 | 0.000007 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000014 | 0.000007 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000002 | 0.000001 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 6.027225 | 1.686240 | 0.110227 | 11113 | 0 | 269008 | 0 | 0 | 0 | 0 | 30340 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000014 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000011 | 0.000008 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000016 | 0.000016 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000022 | 0.000022 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
| logging slow query | 0.000050 | 0.000050 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_do | log.cc | 1716 |
| cleaning up | 0.000015 | 0.000015 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile all for query 2;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000076 | 0.000057 | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000005 | 0.000004 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000013 | 0.000011 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
| init | 0.000014 | 0.000012 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000003 | 0.000003 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000014 | 0.000011 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000009 | 0.000008 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000002 | 0.000001 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 1.448847 | 1.449632 | 0.000000 | 42 | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000017 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000011 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000008 | 0.000008 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000023 | 0.000024 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
| cleaning up | 0.000012 | 0.000012 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)
(3)再连续执行几次count(*)和count(1),耗时均稳定在1.5s左右,因为都是从buffer pool读取数据
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
mysql> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.46 sec)
mysql> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.43 sec)
mysql> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
从上面测试可知,count(1)比count(*)快很多,可能只是因为读内存和读磁盘造成的错误印象。那么count(*)和count(1)究竟有没有区别呢?接下来我们继续分析。
(1)首先看看执行计划,均走的是k_1索引
mysql> explain select count(*) from sbtest1;
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9745977 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(1) from sbtest1;
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9745977 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
(2)重启mysql实例(避免buffer pool的干扰),执行count(*),查看profile,物理读为269008
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (5.97 sec)
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 5.96868700 | select count(*) from sbtest1 |
+----------+------------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000091 | 0.000041 | 0.000039 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 19 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000005 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000013 | 0.000007 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
| init | 0.000017 | 0.000009 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000007 | 0.000003 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000013 | 0.000007 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000013 | 0.000006 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000002 | 0.000001 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 5.968395 | 1.713639 | 0.077255 | 11044 | 1 | 269008 | 0 | 0 | 0 | 0 | 31597 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000015 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000012 | 0.000009 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000008 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000021 | 0.000017 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
| logging slow query | 0.000045 | 0.000038 | 0.000007 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_do | log.cc | 1716 |
| cleaning up | 0.000030 | 0.000030 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)
(3)再次重启mysql实例(避免buffer pool的干扰),执行count(1),查看profile,物理读也为269008
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (5.89 sec)
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 5.89469525 | select count(1) from sbtest1 |
+----------+------------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000099 | 0.000041 | 0.000045 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000007 | 0.000003 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000014 | 0.000006 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
| init | 0.000018 | 0.000009 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000007 | 0.000004 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000004 | 0.000001 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000013 | 0.000007 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000013 | 0.000006 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000002 | 0.000001 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 5.894386 | 1.660191 | 0.119088 | 11013 | 0 | 269008 | 0 | 0 | 0 | 0 | 31771 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000017 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000012 | 0.000010 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000009 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000022 | 0.000026 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
| logging slow query | 0.000060 | 0.000041 | 0.000010 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_do | log.cc | 1716 |
| cleaning up | 0.000014 | 0.000011 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
16 rows in set, 1 warning (0.00 sec)
(4)从上述测试结果可知,count(*)和count(1)的执行计划相同,profile消耗也相同
(5)翻阅MySQL官方文档(5.6和5.7),也可以找到说明,count(*)和count(1)是一模一样的,没有性能差异
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
count(*)和count(1)的执行效率,是一样的;网上流传的一些优化原则,已经是不适用于当前版本,要注意及时更新自己的知识库。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。