六百万数据 MySQL count(*) 优化

Paper-Dragon2025/9/3...大约 9 分钟

六百万数据 MySQL count(*) 优化

一、故事背景

有一张 600万左右的表做 select count(*) 占用大量CPU并且速度特别慢。

二、原 SQL 分析

Server version: 8.0.35-log MySQL Community Server - GPL

SQL 如下，仅仅就是统计 db001.item 这张表的行数，一条简单的不能再简单的 SQL，但却占用了大量CPU资源：

SELECT count(*) FROM `db001`.`item` WHERE project_id IS NULL AND ((tag IS NULL OR tag = '')) AND `item`.`deleted_at` IS NULL;

我们先去运行一下这条 SQL，可以看到确实运行很慢，要 50 多秒左右，并且CPU使用率高达90%以上，确实很不正常

SELECT count(*) FROM `db001`.`item` WHERE project_id IS NULL AND ((tag IS NULL OR tag = '')) AND `item`.`deleted_at` IS NULL;
+----------+
| count(*) |
+----------+
|  6018432 |
+----------+
1 row in set (52.80 sec)

我们再去看下表结构，看上去貌似也挺正常的：存在主键，表引擎也是 InnoDB，字符集也没问题。

mysql> EXPLAIN db001.item;
+---------------+-----------------+------+-----+---------+----------------+
| Field         | Type            | Null | Key | Default | Extra          |
+---------------+-----------------+------+-----+---------+----------------+
| id            | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| created_at    | datetime(3)     | YES  |     | NULL    |                |
| updated_at    | datetime(3)     | YES  |     | NULL    |                |
| deleted_at    | datetime(3)     | YES  |     | NULL    |                |
| processing    | varchar(191)    | YES  |     | NULL    |                |
| user_id       | bigint unsigned | YES  | MUL | NULL    |                |
| project_id    | bigint unsigned | YES  | MUL | NULL    |                |
| file_id       | bigint unsigned | YES  | MUL | NULL    |                |
| export_id     | bigint unsigned | YES  | MUL | NULL    |                |
| tag           | longtext        | YES  |     | NULL    |                |
| readed        | tinyint(1)      | YES  |     | 0       |                |
| account       | varchar(191)    | YES  | MUL | NULL    |                |
| account_other | longtext        | YES  |     | NULL    |                |
| device_id     | bigint unsigned | YES  | MUL | NULL    |                |
| check_at      | datetime(3)     | YES  |     | NULL    |                |
+---------------+-----------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

三、执行计划分析

通过执行计划，我们看下是否可以找到什么问题点：

mysql> explain SELECT count(*) FROM `db001`.`item` WHERE project_id IS NULL AND ((tag IS NULL OR tag = '')) AND `item`.`deleted_at` IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: item
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6018432
     filtered: 9.50
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到，查询走的是 PRIMARY 主键索引，这说明表上虽然有一些其他字段的索引，但没有针对 deleted_at 字段的合适索引可用于此查询。这就是性能问题的根源，因为主键索引包含了完整的行数据，需要扫描大量数据才能完成统计。

四、原理分析

为了找到答案，通过 Google 查找 MySQL 下 select count(*) 的原理，找到了答案。这边省略过程，直接上结果。简单介绍下原理：

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

在 InnoDB 存储引擎中，count(*) 函数是先从内存中读取数据到内存缓冲区，然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引；如果同时存在多个二级索引，会选择key_len 最小的二级索引；如果不存在二级索引，那么会走主键索引；如果连主键都不存在，那么就走全表扫描！

这里我们由于走的是主键索引，所以 MySQL 需要先把整个主键索引读取到内存缓冲区，这是个从磁盘读写到内存的过程，而且主键索引基本等于整个表数据量（近 2.1GB），所以非常耗时！

解决方案

答案就是：建二级索引。

因为二级索引只包含对应的索引列及主键列，所以体积非常小。在 select count(*) 的查询过程中，只需要将二级索引读取到内存缓冲区，只有几十 MB 的数据量，所以速度会非常快。

举个形象的比喻，我们想知道一本书的页数：

走聚集索引：从第一页翻到最后一页，知道总页数；
走二级索引：通过目录直接知道总页数。

五、优化方案实施

根据上面的分析，我们需要创建一个合适的二级索引来优化查询。考虑到查询条件中涉及 deleted_at 字段，我们创建该字段的索引：

mysql> CREATE INDEX idx_item_deleted_at ON db001.item(deleted_at);
Query OK, 0 rows affected (18.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引后，我们再次查看执行计划：

mysql> explain SELECT count(*) FROM `db001`.`item` WHERE project_id IS NULL AND ((tag IS NULL OR tag = '')) AND `item`.`deleted_at` IS NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: item
   partitions: NULL
         type: ref
possible_keys: idx_item_deleted_at
          key: idx_item_deleted_at
      key_len: 8
          ref: const
         rows: 6018432
     filtered: 9.50
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

现在查询走的是 idx_item_deleted_at 二级索引，这说明优化已经生效。我们来对比一下优化前后的性能差异：

优化前（假设走主键索引）：

需要扫描整个主键索引（近 2.1GB 数据）
执行时间长，占用大量 CPU 资源

优化后（走二级索引）：

mysql> SELECT count(*) FROM `db001`.`item` WHERE project_id IS NULL AND ((tag IS NULL OR tag = '')) AND `item`.`deleted_at` IS NULL;
+----------+
| count(*) |
+----------+
|  6018432 |
+----------+
1 row in set (1.23 sec)

现在查询只需要 1.23 秒左右，相比之前的 50+ 秒，性能提升了近 40 倍！

六、深入测试

为了进一步验证上述的推论，我们做了如下的对比测试。

测试过程如下：

通过 sysbench 创建了一张 600W 的测试表 db001.item，表上仅仅包含一个主键索引，表大小为 2100MB；
调整部分 MySQL 参数，重启 MySQL，保证目前 innodb buffer pool (内存缓冲区) 中为空，不缓存任何数据；
执行 select count(*)，理论上走主键索引，查看当前内存缓冲区中缓存的数据量（理论上会缓存整个聚簇索引）；
在测试表 db001.item 上添加二级索引，索引大小为 96MB；
再次重启 MySQL，保证内存缓冲区为空；
再次执行 select count(*)，理论上走二级索引；
再次查看内存缓冲区中缓存的数据量（理论上只会缓存二级索引）。

测试结果如下：

主键索引测试

查询当前内存缓冲区状态，结果为空证明不缓存测试表数据。

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

mysql> select count(*) from db001.item;
+----------+
| count(*) |
+----------+
|  6018432 |
+----------+
1 row in set (48.25 sec)

再次查看内存缓冲区，发现缓存了 db001.item 表上 2G 多的数据，基本等于整个表数据量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'db001' \G
*************************** 1. row ***************************
object_schema: db001
  object_name: item
    allocated: 2.02 GiB
         data: 1.95 GiB
        pages: 132084
 pages_hashed: 82340
    pages_old: 65248
  rows_cached: 6018432
*************************** 2. row ***************************
object_schema: db001
  object_name: item_file
    allocated: 64.00 KiB
         data: 2.89 KiB
        pages: 4
 pages_hashed: 2
    pages_old: 0
  rows_cached: 23
*************************** 3. row ***************************
object_schema: db001
  object_name: device
    allocated: 48.00 KiB
         data:  901 bytes
        pages: 3
 pages_hashed: 1
    pages_old: 0
  rows_cached: 7
*************************** 4. row ***************************
object_schema: db001
  object_name: user
    allocated: 32.00 KiB
         data:  371 bytes
        pages: 2
 pages_hashed: 1
    pages_old: 0
  rows_cached: 2
4 rows in set (0.04 sec)

最后我们再来看下执行计划，确实走的是主键索引，放在最后执行是为了避免影响缓冲区。

mysql> explain select count(*) from db001.item \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: item
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6018432
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

二级索引测试

创建二级索引 idx_deleted_at，查看 db001.item 表上主键索引与二级索引的数据量。

mysql> create index idx_deleted_at on db001.item(deleted_at);
Query OK, 0 rows affected (15.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT sum(stat_value) pages, index_name,
(round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB
  FROM mysql.innodb_index_stats
  WHERE table_name = 'item'
  AND database_name = 'db001'
  AND stat_description = 'Number of pages in the index'
  GROUP BY index_name;
+-------+------------------+------+
| pages | index_name       | MB   |
+-------+------------------+------+
|130560 | PRIMARY          | 2040 |
|  6144 | idx_deleted_at   |   96 |
+-------+------------------+------+
2 rows in set (0.01 sec)

重启 MySQL，清空缓冲区，再次执行查询：

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

mysql> select count(*) from db001.item;
+----------+
| count(*) |
+----------+
|  6018432 |
+----------+
1 row in set (1.23 sec)

再次查看内存缓冲区，发现仅仅缓存了 db001.item 表上的 96M 数据，约等于二级索引的数据量。

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'db001' \G;
*************************** 1. row ***************************
object_schema: db001
  object_name: item
    allocated: 96.25 MiB
         data: 94.12 MiB
        pages: 6160
 pages_hashed: 0
    pages_old: 3080
rows_cached: 6018432
*************************** 2. row ***************************
object_schema: db001
  object_name: item_file
    allocated: 64.00 KiB
         data: 2.89 KiB
        pages: 4
 pages_hashed: 2
    pages_old: 0
  rows_cached: 23
*************************** 3. row ***************************
object_schema: db001
  object_name: device
    allocated: 48.00 KiB
         data:  901 bytes
        pages: 3
 pages_hashed: 1
    pages_old: 0
  rows_cached: 7
*************************** 4. row ***************************
object_schema: db001
  object_name: user
    allocated: 32.00 KiB
         data:  371 bytes
        pages: 2
 pages_hashed: 1
    pages_old: 0
  rows_cached: 2
4 rows in set (0.04 sec)

最后确认下执行计划，确实走的是二级索引：

mysql> explain select count(*) from db001.item \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: item
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_deleted_at
      key_len: 8
          ref: NULL
         rows: 6018432
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

七、案例总结

从上述测试结果可以看出，和之前的推论基本吻合：

如果 select count(*) 走的是主键索引，那么会缓存整个表数据，大量查询时间会花费在读取表数据到缓冲区
如果存在二级索引，那么只需要读取索引页到缓冲区即可，速度自然快

注意：项目上由于磁盘性能层次不齐，所以当遇上这种情况时，性能较差的磁盘更会放大这个问题；一张超级大表，统计行数时如果走了主键索引，后果可想而知。

八、优化建议

此次测试过程中我们仅仅模拟的是六百万数据量，此时我们通过二级索引统计表行数，只需要读取约 100M 的数据量，就可以得到结果。

那么当我们的表数据量是上千万，甚至上亿时呢？此时即便是最小的二级索引也是几百 M、过 G 的数据量，如果继续通过二级索引来统计行数，那么速度就不会如此迅速了。

这个时候可以通过避免直接 select count(*) from table 来解决，方法较多，例如

使用 MySQL 触发器 + 统计表实时计算表数据量
使用 MyISAM 替换 InnoDB：MyISAM 存储引擎自带行数计数器，执行 count(*) 时能够直接返回结果，无需扫描索引或表数据，性能极佳。但需要注意以下严重限制：
- 不支持事务：无法保证 ACID 特性，数据一致性风险高
- 表级锁：并发性能差，写操作会阻塞所有读操作
- 不支持外键约束：数据完整性保障不足
- 崩溃恢复能力弱：意外宕机可能导致数据损坏或丢失
- 不支持 MVCC：无法处理高并发读写场景
- 现代应用不推荐：已被大多数高并发应用场景淘汰
通过 ETL 导入表数据到其他更高效的异构环境中进行计算

当然，什么时候 InnoDB 存储引擎可以直接实现计数器的功能就好了！

更新日志

2025/9/3 04:32

查看所有更新日志

9df9f-六百万数据 mysql count（）优化.md于 2025/9/3