MySQL优化案例:count(*) 如何优化?

2023-12-23 07:09:13

想要优化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越小,扫描成本越小,执行效率越高;聚集索引最慢

图片


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

文章来源:https://blog.csdn.net/m0_38048955/article/details/135145745
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。