暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 探索 | count(*) 和 count(1) 谁的效率更高？

探索 | count(*) 和 count(1) 谁的效率更高？

数据与人 2024-01-17
420
点击上方"数据与人", 右上角选择“设为星标”
分享干货，共同成长！ 
前言
相信大多数DBA都看见过这样一条SQL优化原则：用count(1)替换count(*)；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)的执行效率，是一样的；网上流传的一些优化原则，已经是不适用于当前版本，要注意及时更新自己的知识库。

既然用count(1)替换count(*)，并不能起到优化作用，两者的执行效率是一样的。那么，count(*)应该如何优化呢？让我们继续往下看。

count(*)处理

想要优化count(*)，首先得了解清楚，MySQL是如何处理count(*)的？在MySQL不同版本、不同存储引擎中，对于count(*)的处理方式，是存在差异的。

MyISAM

使用过MyISAM存储引擎的DBA，应该都有这感觉：不管表有多大，count(*)总是能够秒出结果。这是因为，MyISAM表将count(*)结果记录下来了
For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.
（1）查看执行计划，看不出来这个优化
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=MyISAM AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=10000000
1 row in set (0.00 sec)

mysql> explain select count(*) from sbtest1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
（2）重启mysql实例(避免buffer pool的干扰)，执行count(*)，秒出结果，查看profile，物理读为0
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 (0.00 sec)

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00020175 | 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.000108 | 0.000000 |   0.000095 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                17 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000005 | 0.000000 |   0.000005 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         835 |
| Opening tables       | 0.000014 | 0.000000 |   0.000014 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5648 |
| init                 | 0.000017 | 0.000000 |   0.000017 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 3 |     0 | handle_query          | sql_select.cc        |         121 |
| System lock          | 0.000009 | 0.000000 |   0.000009 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         321 |
| optimizing           | 0.000006 | 0.000000 |   0.000005 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | optimize              | sql_optimizer.cc     |         151 |
| executing            | 0.000006 | 0.000000 |   0.000006 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         119 |
| end                  | 0.000003 | 0.000000 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         199 |
| query end            | 0.000004 | 0.000000 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4952 |
| closing tables       | 0.000007 | 0.000000 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5004 |
| freeing items        | 0.000014 | 0.000000 |   0.000013 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5578 |
| cleaning up          | 0.000010 | 0.000000 |   0.000010 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1864 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
12 rows in set, 1 warning (0.00 sec)

InnoDB(5.7.18之前版本)

在MySQL 5.7.18之前版本，MySQL是通过扫描聚集索引(即全表扫描)，来获取count(*)的结果
Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.
（1）查看执行计划，走的是全表扫描
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> explain select count(*) from sbtest1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
（2）重启mysql实例(避免buffer pool的干扰)，执行count(*)，查看profile，耗时105s左右，物理读4446672
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 (1 min 45.81 sec)

mysql> show profiles;
+----------+--------------+------------------------------+
| Query_ID | Duration     | Query                        |
+----------+--------------+------------------------------+
|        1 | 105.81688950 | 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.000093 | 0.000037 |   0.000043 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                17 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions |   0.000005 | 0.000002 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         835 |
| Opening tables       |   0.000013 | 0.000006 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5648 |
| init                 |   0.000018 | 0.000008 |   0.000009 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 3 |     0 | handle_query          | sql_select.cc        |         121 |
| System lock          |   0.000007 | 0.000003 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         321 |
| optimizing           | 105.816679 | 7.909304 |   1.584205 |            105509 |                  30 |      4446672 |             0 |             0 |                 0 |                 0 |            555848 |     0 | optimize              | sql_optimizer.cc     |         151 |
| executing            |   0.000019 | 0.000011 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         119 |
| end                  |   0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         199 |
| query end            |   0.000009 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4952 |
| closing tables       |   0.000010 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5004 |
| freeing items        |   0.000020 | 0.000016 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5578 |
| cleaning up          |   0.000014 | 0.000011 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | dispatch_command      | sql_parse.cc         |        1864 |
+----------------------+------------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
12 rows in set, 1 warning (0.00 sec)
InnoDB(5.7.18之后版本)
从MySQL 5.7.18版本开始，MySQL会尽量选择扫描二级索引，来获取count(*)的结果
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.
（1）查看执行计划，走的是二级索引k_1
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)

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)
（2）重启mysql实例(避免buffer pool的干扰)，执行count(*)，查看profile，耗时6s左右，物理读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.99 sec)

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 5.99044700 | 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.000081 | 0.000038 |   0.000036 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 5 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000003 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000015 | 0.000007 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
| init                 | 0.000018 | 0.000010 |   0.000009 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 4 |     0 | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000008 | 0.000003 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000004 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         158 |
| statistics           | 0.000012 | 0.000006 |   0.000006 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         374 |
| preparing            | 0.000013 | 0.000007 |   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.990151 | 1.727173 |   0.094130 |             10985 |                   0 |       269008 |             0 |             0 |                 0 |                 0 |             31023 |     0 | exec                  | sql_executor.cc      |         202 |
| end                  | 0.000013 | 0.000006 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000012 | 0.000010 |   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.000022 | 0.000019 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| logging slow query   | 0.000069 | 0.000057 |   0.000011 |                 0 |                   0 |            0 |             8 |             0 |                 0 |                 0 |                 0 |     0 | log_slow_do           | log.cc               |        1716 |
| cleaning up          | 0.000015 | 0.000013 |   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)


count(*)优化

通过上面的测试，对于MySQL是如何处理count(*)的，已经有较为清晰的了解。那么为什么MySQL要从扫描聚集索引优化成扫描二级索引呢？我们知道，对于InnoDB表而言，主键即数据；聚集索引的叶子节点存放的是完整行记录，而二级索引的叶子节点存放的只是索引列+主键，因此二级索引要比聚集索引小，扫描成本会更低；而且，二级索引key_len越小，扫描成本就越低，执行效率就越高。
下面看一组测试数据
（1）查看执行计划
mysql> explain select count(*) from sbtest1 force index(primary);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 9745977 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from sbtest1 force index(k_1);
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
| 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(*) from sbtest1 force index(idx_c);
+----+-------------+---------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key   | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c | 480     | NULL | 9745977 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from sbtest1 force index(idx_pad);
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_pad | 240     | NULL | 9745977 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
（2）执行SQL语句，并查看profile
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(*) from sbtest1 force index(primary);
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 45.89 sec)

mysql> select count(*) from sbtest1 force index(k_1);
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.01 sec)

mysql> select count(*) from sbtest1 force index(idx_c);
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 2.48 sec)

mysql> select count(*) from sbtest1 force index(idx_pad);
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (33.07 sec)

mysql> show profiles;
+----------+--------------+---------------------------------------------------+
| Query_ID | Duration     | Query                                             |
+----------+--------------+---------------------------------------------------+
|        1 | 105.88656775 | select count(*) from sbtest1 force index(primary) |
|        2 |   6.01027875 | select count(*) from sbtest1 force index(k_1)     |
|        3 |  62.48036425 | select count(*) from sbtest1 force index(idx_c)   |
|        4 |  33.06777175 | select count(*) from sbtest1 force index(idx_pad) |
+----------+--------------+---------------------------------------------------+
4 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.000113 | 0.000049 |   0.000050 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                22 |     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.000007 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
| init                 |   0.000020 | 0.000010 |   0.000010 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 5 |     0 | handle_query          | sql_select.cc        |         128 |
| System lock          |   0.000008 | 0.000004 |   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.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         374 |
| preparing            |   0.000014 | 0.000006 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 3 |     0 | optimize              | sql_optimizer.cc     |         482 |
| executing            |   0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         126 |
| Sending data         | 105.886257 | 7.623801 |   1.607489 |            101300 |                  31 |      4446672 |            16 |             0 |                 0 |                 0 |            553374 |     0 | exec                  | sql_executor.cc      |         202 |
| end                  |   0.000015 | 0.000012 |   0.000003 |                 1 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         206 |
| query end            |   0.000011 | 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.000006 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5009 |
| freeing items        |   0.000029 | 0.000030 |   0.000007 |                 1 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| logging slow query   |   0.000043 | 0.000048 |   0.000012 |                 1 |                   0 |            0 |             8 |             0 |                 0 |                 0 |                 0 |     0 | log_slow_do           | log.cc               |        1716 |
| cleaning up          |   0.000012 | 0.000016 |   0.000004 |                 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.000074 | 0.000055 |   0.000014 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 2 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000005 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000015 | 0.000012 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
| init                 | 0.000017 | 0.000013 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000008 | 0.000007 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000004 | 0.000004 |   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.000011 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         482 |
| executing            | 0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         126 |
| Sending data         | 6.010014 | 1.658054 |   0.095197 |             10949 |                   0 |       268992 |             0 |             0 |                 0 |                 0 |             33624 |     0 | exec                  | sql_executor.cc      |         202 |
| end                  | 0.000018 | 0.000008 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000012 | 0.000010 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4956 |
| closing tables       | 0.000009 | 0.000007 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5009 |
| freeing items        | 0.000021 | 0.000018 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| logging slow query   | 0.000041 | 0.000033 |   0.000007 |                 0 |                   0 |            0 |             0 |             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)

mysql> show profile all for query 3;
+----------------------+-----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| 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.000069 | 0.000053 |   0.000011 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions |  0.000007 | 0.000005 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
| Opening tables       |  0.000014 | 0.000012 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
| init                 |  0.000017 | 0.000014 |   0.000003 |                 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.000004 | 0.000004 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         158 |
| statistics           |  0.000013 | 0.000011 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         374 |
| preparing            |  0.000011 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         482 |
| executing            |  0.000002 | 0.000002 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         126 |
| Sending data         | 62.480103 | 4.004693 |   1.079968 |            108531 |                   8 |      2626768 |             8 |             0 |                 0 |                 0 |            327429 |     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.000010 | 0.000007 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     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.000025 | 0.000040 |   0.000010 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| logging slow query   |  0.000048 | 0.000042 |   0.000009 |                 1 |                   0 |            0 |             8 |             0 |                 0 |                 0 |                 0 |     0 | log_slow_do           | log.cc               |        1716 |
| cleaning up          |  0.000013 | 0.000011 |   0.000003 |                 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 4;
+----------------------+-----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| 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.000077 | 0.000056 |   0.000013 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions |  0.000006 | 0.000005 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         809 |
| Opening tables       |  0.000015 | 0.000012 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5781 |
| init                 |  0.000015 | 0.000012 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         128 |
| System lock          |  0.000008 | 0.000007 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           |  0.000004 | 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.000010 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     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         | 33.067508 | 2.744633 |   0.553442 |             57118 |                   3 |      1406704 |             0 |             0 |                 0 |                 0 |            126814 |     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.000010 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4956 |
| closing tables       |  0.000008 | 0.000006 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        5009 |
| freeing items        |  0.000019 | 0.000016 |   0.000004 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5622 |
| logging slow query   |  0.000052 | 0.000043 |   0.000009 |                 0 |                   0 |            0 |             8 |             0 |                 0 |                 0 |                 0 |     0 | log_slow_do           | log.cc               |        1716 |
| cleaning up          |  0.000012 | 0.000009 |   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)
（3）汇总数据如下表，二级索引key_len越小，扫描成本越小，执行效率越高；聚集索引最慢
索引
key_len
物理读
耗时
primary
4
4446672
105
k_1
4
268992
6
idx_c
480
2626768
62
idx_pad
240
1406704
33


总结

对于count(*)优化，我们可以考虑如下两种方式：
（1）如果对于count(*)准确性要求不是很高，可以考虑将count(*)结果放到缓存中，定期刷新、或者是通过incr/decr更新；
（2）如果对于count(*)准确性要求高，只能从MySQL数据库获取，可以考虑为对应表key_len较小的列建立二级索引，以优化count(*)执行效率。

更多精彩内容，关注我们▼▼

sql优化selectmysql执行计划mysql
文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论