MySQL log_error表COUNT查询首次执行耗时过长问题排查求助

问题现象 #

执行COUNT(id)查询时首次耗时极长（1分37秒），第二次执行速度恢复正常（0.57秒），表实际数据量仅9万余条，其他数据库表无此类问题。

相关SQL及输出 #

首次COUNT查询 #

mysql> select count(id) from log_error;
+-----------+
| count(id) |
+-----------+
|     98187 |
+-----------+
1 row in set (1 min 37.39 sec)

表结构 #

mysql> show create table log_error;

| log_error | CREATE TABLE `log_error` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` int DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `log_time` int DEFAULT NULL,
  `prefix` varchar(255) DEFAULT NULL,
  `message` text,
  PRIMARY KEY (`id`),
  KEY `idx_log_level` (`level`),
  KEY `idx_log_category` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=575504 DEFAULT CHARSET=utf8mb3 |

1 row in set (0.04 sec)

第二次COUNT查询 #

mysql> select count(id) from log_error;
+-----------+
| count(id) |
+-----------+
|     98187 |
+-----------+
1 row in set (0.57 sec)

EXPLAIN分析结果 #

mysql> explain select count(id) from log_error;
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | log_error | NULL       | index | NULL          | idx_log_level | 5       | NULL | 95044 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

排查分析 #

1. 索引碎片严重：表的AUTO_INCREMENT值已达575504，但实际仅98187条数据，说明历史上存在大量删除操作。InnoDB索引会因频繁删除产生大量空洞（碎片），首次扫描索引时需读取更多磁盘页，导致IO耗时剧增；第二次执行时索引页已缓存到内存，速度恢复正常。

2. 缓冲池未预热：首次查询时，该表的索引页不在InnoDB缓冲池（Buffer Pool）中，必须从磁盘读取；第二次查询时数据已加载到内存，无需磁盘IO，因此速度极快。

3. 索引选择逻辑：优化器选择idx_log_level二级索引而非主键索引（聚簇索引），是因为二级索引体积更小，扫描成本更低，但该二级索引同样受碎片影响，导致首次扫描耗时久。

解决建议 #

• 重建表消除碎片：执行以下命令重建表，清理索引碎片，减少磁盘IO次数（建议业务低峰期操作，会锁表）：

  ALTER TABLE log_error ENGINE=InnoDB;
  -- 或
  OPTIMIZE TABLE log_error;
  

• 调整缓冲池大小：检查innodb_buffer_pool_size参数，若设置过小无法缓存常用表数据，对于专用数据库服务器，建议将该值设为服务器内存的50%-70%，提升数据缓存能力。

• 更新统计信息：执行ANALYZE TABLE log_error;，让优化器获取更准确的表统计数据，确保索引选择最优。

• 强制使用主键索引测试：若主键索引碎片更少，可尝试强制使用主键索引执行查询，对比耗时：

  SELECT COUNT(id) FROM log_error FORCE INDEX(PRIMARY);
  

内容的提问来源于stack exchange，提问作者Pavel Bariev