MySQL优化案例：count(*) 如何优化？

2023-3-7

图片

图片

想要优化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: sbtest1Create 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=100000001 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: sbtest1Create 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=10000001 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: sbtest1Create 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=10000001 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越小，扫描成本越小，执行效率越高；聚集索引最慢
图片
总结
对于count(*)优化，我们可以考虑如下两种方式：
（1）如果对于count(*)准确性要求不是很高，可以考虑将count(*)结果放到缓存中，定期刷新、或者是通过incr/decr更新；
（2）如果对于count(*)准确性要求高，只能从MySQL数据库获取，可以考虑为对应表key_len较小的列建立二级索引，以优化count(*)执行效率。
来源：
https://cloud.tencent.com/developer/article/1730610
THE END
MySQL
MySQL面试题：事务 ACID 知道么，原理是什么？
<<上一篇
position定位在html布局中的使用技巧和注意事项
下一篇>>
相关推荐
PgAssistant：PostgreSQL 数据库性能优化助手
PostgreSQL 18新特性之DML语句RETURNING增强
MongoDB 8发布了，性能如何？
MySQL全文本搜索（Full-Text Search）深度解读
PostgreSQL：复合索引和多个索引哪个好？
PostgreSQL：该做参数调优，还是查询优化？
深入理解 SQL 联结表：从基础到优化一篇掌握
PostgreSQL 数据库：强大的排序处理规则