查看表结构，sbtest1有主键、k_1二级索引、i_c二级索引

 CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `i_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin MAX_ROWS=1000000;

key_len为4二级索引

首先使用k_1查询，记录查询时间（进行10次取平均值）并且查看执行计划

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 | 4753363 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+

查询时间，第一次查询1分37秒，后10次查询平均耗时1.246秒：

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 min 37.62 sec

mysql> select (1.51 +1.72 +1.16 +1.09 +1.17 +1.15 +1.19 +1.18 +1.13 +1.16) /10 ;
+------------------------------------------------------------------+
| (1.51 +1.72 +1.16 +1.09 +1.17 +1.15 +1.19 +1.18 +1.13 +1.16) /10 |
+------------------------------------------------------------------+
|                                                         1.246000 |
+------------------------------------------------------------------+

key_len为480二级索引

删除key k_1，查看执行计划：

mysql> alter table sbtest1 drop key k_1;
Query OK, 0 rows affected (19.29 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          | i_c  | 480     | NULL | 4753363 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

查询时间如下，第一次查询11分5秒，与之前相差非常悬殊：

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

其余10次取平均值如下：

mysql> select (6.00 +1.27 +1.32 +1.23 +1.25 +1.22 +1.22 +1.25 +1.23 +1.25) /10;
+------------------------------------------------------------------+
| (6.00 +1.27 +1.32 +1.23 +1.25 +1.22 +1.22 +1.25 +1.23 +1.25) /10 |
+------------------------------------------------------------------+
|                                                         1.724000 |
+------------------------------------------------------------------+
1 row in set (0.07 sec)

key_len为4主键

删掉全部二级索引，让SQL走主键，第一次执行9分30秒：

mysql> alter table sbtest1 drop key i_c;
Query OK, 0 rows affected (0.53 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          | PRIMARY | 4       | NULL | 4753363 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

其余10次取平均耗时150秒

结论：

• 聚簇索引：每一个 InnoDB 存储引擎下的表都有一个特殊的索引用来保存每一行的数据，称为聚簇索引（通常都为主键），聚簇索引实际保存了 B-Tree 索引和行数据，所以大小实际上约等于为表数据量
• 二级索引：除了聚集索引，表上其他的索引都是二级索引，索引中仅仅存储了对应索引列及主键列

在 InnoDB 存储引擎中，count(*)函数是先从内存中读取数据到内存缓冲区，然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引；如果同时存在多个二级索引，会选择key_len 最小的二级索引；如果不存在二级索引，那么会走主键索引；如果连主键都不存在，那么就走全表扫描！这里我们由于走的是主键索引，所以 MySQL 需要先把整个主键索引读取到内存缓冲区，这是个从磁盘读写到内存的过程，而且主键索引基本等于整个表数据量（10GB+），所以非常耗时！

二级索引只包含对应的索引列及主键列，所以体积非常小。在select count(*)的查询过程中，只需要将二级索引读取到内存缓冲区，只有几十 MB 的数据量，所以速度会非常快。举个形象的比喻，我们想知道一本书的页数：

走聚集索引：从第一页翻到最后一页，知道总页数；

走二级索引：通过目录直接知道总页数。

作者：姚崇

Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。