业务反馈，开发崩溃

某天，项目中业务人员反馈系统中某功能查询非常的慢，几乎等待了几十秒才有反应。于是联系了开发，开发人员调取日志，排查应用，定位 SQL，其实只是一个 select count(*)，这可急坏了开发人员，非常困惑，最简答的一个统计 SQL，为什么查询这么慢！不知道到如何优化与处理。

一个效果显著的简单操作

在我知道这个问题，询问了开发人员哪张表后，做了一定分析后，增加索引，无需开发做任何修改，性能得到大幅提升。为什么一个索引会有如此的性能提升，我先卖个关子，后面一一道来。

问题复现，优化处理

首先我们造一个 500W 数据量的表，别结构如下：

mysql> show create table sbtest1\G;
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

直接来 count(*) 一下，看看执行时间吧。

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (2.42 sec)

执行时间 2.42 秒。

现在看看执行计划：

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          | PRIMARY | 4       | NULL | 4808163 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果显示：走 PRIMARY 索引。

是不是有疑惑了，查询走了主键索引，看着没什么问题呀，500W 的数据 2 秒执行完，还可以。那你就错了，我这里只是一个简单的测试表，这才几个字段，测试表造的数据又是多么的简单，倘若在真实生产环境，这 500W 的数据可不是 2 秒就能返回结果的。

稍等片刻，我来优化…… …… …… ……

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (0.68 sec)

执行时间明显缩短，我到底做了怎样的操作，为什么执行效率大幅提高，想知道其中的奥妙吗？干货即将送达。

扒一扒 count(*) 的原理

为了解开这其中的疑惑，首先我们不得不说一下 MySQL 的 count(*) 原理。

在 MySQL 中存在两种索引：

• 聚簇索引：MySQL 中的每个 InnoDB 存储引擎的表，都有一个特殊索引来保存每行记录，这个索引就是聚簇索引。通常情况下其实就是主键。聚簇索引保存的是行记录和 b-tree 索引，这个索引所占用的空间大小和行记录总数差不多大。
• 二级索引：另外一类索引就是二级索引，它保存的只是本身索引列和主键列。占用的空间明显小很多了。

介绍完 MySQL 的两种索引，我们继续说 MySQL 的 count(*) 执行过程。

在 MySQL 的 InnoDB 存储引擎中，count(*) 会从内存中读取数据到缓冲区中，如果内存中没有，会提前一步在磁盘中把数据读取到内存中，然后在缓存区中完成记录数的统计。MySQL 会先通过 ken_len 最小的那个二级索引计算，如果没有二级索引就通过主键计算，如果连主键都没有那就要通过全表扫描来完成计算了。

主键索引也就是聚簇索引，会把行记录和 b-tree 索引都读取出来，所占用的空间大小和行记录总数差不多大；二级索引保存的只是本身索引列和主键列，占用的空间会小的多，当然读取出来就节省了很多时间。

我举个通俗易懂的例子吧，比如学校给你一个任务，让你统计一下高三年级一共有多少学生？利用上面说的两种索引来统计。

• 聚簇索引：从学号 1，一个一个数到 600，有 600 人。
• 二级索引：直接看学号 600，那不就是 600 人吗！

通过这些就不难说明了，上述演示的过程，第一次是通过主键来计算统计数据量，而第二次其实我做的也是创建了一个二级索引，通过二级索引来计算统计，速度快了很多，我们可以看看其对应的执行过程。

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 | 4808163 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

执行过程 key 这一列内容为：k_1，这个就是二级索引的索引名称。

显而易见的验证

验证聚簇索引

查询 MySQL 缓存区，确保缓冲区中不存在测试表的缓存：

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'sbtest';
Empty set (0.05 sec)

执行 select count(*)：

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (2.42 sec)

再次查看 MySQL 缓存区，查询测试表的缓存：

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'sbtest';
+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+
| sbtest        | sbtest1     | 125.84 MiB | 115.46 MiB |  8054 |            0 |      3021 |      588845 |
+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+
1 row in set (0.13 sec)

结果显示：缓存了 100M+ 的数据。

查看执行计划：

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          | PRIMARY | 4       | NULL | 4808163 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果显示 select count(*) 走的是主键索引。

验证二级索引

创建一个二级索引：

mysql> create index k_1 on sbtest1(k);
Query OK, 0 rows affected (53.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

为了验证本次过程，我们重启一下 MySQL，目的是清空 MySQL 的缓存。

......  
MySQL 重启完成  
......  

查询 MySQL 缓存区，确保缓冲区中不存在测试表的缓存：

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'sbtest';
Empty set (0.05 sec)

执行 select count(*)：

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (0.73 sec)

再次查看 MySQL 缓存区，查询测试表的缓存：

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'sbtest';
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| sbtest        | sbtest1     | 65.09 MiB | 62.06 MiB |  4166 |            0 |      1459 |     2501935 |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
1 row in set (0.08 sec)

结果显示：缓存了 60M+ 的数据。

查看执行计划：

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 | 4808163 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

结果显示 select count(*) 走的是二级索引 k_1。

更加直观看一下两种索引占用的空间大小

首先我们查看一下 MySQL 的 innodb_page_size 的大小：

mysql> show variables like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

大小是 16K。

统计一下聚簇索引和二级索引占用的空间大小：

mysql> select 
    ->   sum(stat_value) pages,
    ->   index_name index_name,
    ->   (round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB
    ->   from mysql.innodb_index_stats 
    ->   where table_name = 'sbtest1' AND database_name = 'sbtest' AND stat_description = 'Number of pages in the index' 
    -> group by index_name;
+-------+------------+------+
| pages | index_name | MB   |
+-------+------------+------+
| 67456 | PRIMARY    | 1054 |
|  4774 | k_1        |   75 |
+-------+------------+------+
2 rows in set (0.01 sec)

这样结果太明显了，主键索引占用了几乎 1 个 G 的空间，而 k_1 这个二级索引值占用了 75M 的空间。

总结与思考

MySQL 的 select count(*) 在底层实现统计的过程中通过二级索引优于主键索引优于全表扫描，这是因为二级索引只缓存主键列和索引列，主键索引几乎缓存了所有的行记录，前者势必比后者缓存的内容少的多，当然计算的效率肯定要快的多。

我们再思考一下，假如数据量不是 500W，而二级索引占用的空间都 1G、10G，甚至几十 G 了，速度也就不可接受了，怎么办？

这个时候我们不能局限于二级索引了，而可考虑：

1. 单纯的统计，我们可以考虑用 MyISAM 引擎，它自带计数器，当然了，局限性就不一一列举了，此方案了解即可吧。
2. 数据仓库等其他可接入的系统来完成此工作。
3. 缓存中间件也不失一个好的建议。
4. 做一个类似触发器计数的功能？
5. MySQL 8.0 的并行查询，嗯，好功能。
6. 历史数据迁移，就不让你查询那么多数据了，这个有点霸道了，可以换着说法，根据业务需求，历史数据迁移，只保留某些数据（按规则）。
7. 分库分表，不多说什么，还是物理上的优化。
8. 服务器硬件资源提升，比如 SSD 硬盘等（治标不治本）。
9. 其他（肯定不止以上 8 种，如果你还有其他想法，请尊留言）。

好了，至此我们基本学习完 select count(*)相关内容了，内容比较多，当然也有不足之处，欢迎朋友们指正补充。