MySQL案例：count(*)效率优化
原创MySQL案例：count(*)效率优化
原创
brightdeng@DBA
修改于 2020-10-27 20:06:14
修改于 2020-10-27 20:06:14
前言
阅读过上一篇文章的童鞋应该都知道，用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(*)执行效率。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。