MariaDB/MySQL中count(*)查询异常缓慢问题排查求助

问题现象 #

执行EXPLAIN SELECT COUNT(*) FROM contact_activity显示，查询计划会使用仅包含单个int列、keylen为5的二级索引，但该查询耗时波动极大，在2到6分钟之间变化，且99.99%的时间消耗在sending data阶段。

背景信息 #

• MariaDB版本：10.11.7
• 表类型：按日期范围分区的InnoDB表，按月分区（PARTITION BY RANGE COLUMNS(activity_datetime)）
• 数据量：6亿条记录
• 常规操作耗时正常，约2-3分钟
• 索引情况（均为BTREE索引，无文本类型索引）：
  • 主键：int(11)+datetime，索引大小约为表大小的30%
  • 索引id_contact：可空、非唯一，int(11)类型，索引大小约为表大小的15%
  • 索引id_list：可空、非唯一，int(11)类型，索引大小约为表大小的15%
• 服务器配置：独立64GB内存服务器，仅部署数据库
• InnoDB核心配置：
  • innodb_buffer_pool_size：50GB
  • innodb_buffer_pool_chunk_size：1GB
  • innodb_read_io_threads：8（从默认4调整）
• 其他排查结果：SHOW ENGINE INNODB STATUS无异常，死锁极少；隔离级别曾设为REPEATABLE-READ，调整后确认既非问题原因也非解决方案

已尝试的无效调整 #

• 切换隔离级别为REPEATABLE-READ
• 用count(id)替代count(*)
• 调整系统变量：
  • 提升innodb_buffer_pool_size至50GB
  • 提升innodb_buffer_pool_chunk_size至2GB
  • 提升innodb_read_io_threads至8
• 在带索引的表副本上测试相同查询
• 在无分区的表副本上测试相同查询

查询耗时详情 #

1   Starting           28 µs
2   Checking Permissions   6 µs
3   Opening Tables         23 µs
4   After Opening Tables.  15 µs
5   System Lock            18 µs
6   Table Lock             4 µs
7   Init                   7 µs
8   Optimizing             4 µs
9   Statistics             19 µs
10  Preparing              26 µs
11  Executing              2 µs
12  Sending Data           358.4 s
13  End Of Update Loop.    20 µs
14  Query End              3 µs
15  Commit                 6 µs
16  Closing Tables         4 µs
17  Unlocking Tables       2 µs
18  Closing Tables         15 µs
19  Starting Cleanup       3 µs
20  Freeing Items          6 µs
21  Updating Status        14 µs
22  Reset For Next Command 4 µs

建表语句 #

CREATE TABLE `contact_activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_list` int(11) DEFAULT NULL,
  `activity` varchar(50) DEFAULT NULL,
  `id_contact` int(11) DEFAULT NULL,
  `id_isp` int(11) DEFAULT NULL,
  `id_esp` int(11) DEFAULT NULL,
  `id_esp_connection` int(11) DEFAULT NULL,
  `id_message` int(11) DEFAULT NULL,
  `id_campaign` int(11) DEFAULT NULL,
  `activity_datetime` datetime NOT NULL,
  `ip` varchar(150) DEFAULT NULL,
  `country_code` varchar(50) DEFAULT NULL,
  `browser` varchar(50) DEFAULT NULL,
  `os` varchar(50) DEFAULT NULL,
  `data` text DEFAULT NULL,
  `id_platform` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`,`activity_datetime`),
  KEY `id_contact` (`id_contact`),
  KEY `id_list` (`id_list`)
) ENGINE=InnoDB AUTO_INCREMENT=629997644 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE  COLUMNS(`activity_datetime`)
(PARTITION `pMonth_231` VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_232` VALUES LESS THAN ('2023-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_233` VALUES LESS THAN ('2023-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_234` VALUES LESS THAN ('2023-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_235` VALUES LESS THAN ('2023-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_236` VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_237` VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_238` VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_239` VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2310` VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2311` VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2312` VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_241` VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_242` VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_243` VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_244` VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_245` VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_246` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_247` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_248` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_249` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2410` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2411` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2412` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMaxMonth` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 

分析与解决方案 #

核心问题拆解 #

1. 索引与分区的扫描冲突：二级索引体积虽小，但分区表执行全表COUNT时需逐个扫描每个分区的索引，若部分分区索引未加载到缓冲池，会触发大量随机IO，这是耗时波动的核心原因。
2. 可空索引的额外开销：当前使用的二级索引为可空列，InnoDB需额外处理NULL值计数逻辑，增加了不必要的开销。
3. 缓冲池容量不足：6亿行的二级索引总大小远超50GB缓冲池，每次查询都需从磁盘加载大量索引页，磁盘IO性能波动直接导致查询耗时不稳定。

针对性解决方法 #

方法1：创建非空单列最小化索引

创建一个非空的单列索引，让InnoDB用最紧凑的结构计数：

ALTER TABLE contact_activity ADD KEY idx_created_at (created_at);

created_at是NOT NULL的timestamp列，索引体积与现有二级索引相当，但非空属性可避免NULL值处理开销，优化器会优先选择该索引执行COUNT操作。

方法2：分区预统计+汇总（毫秒级查询）

利用分区特性定期统计各分区行数，存储到辅助表，查询时直接汇总：

1. 创建辅助表存储分区行数：

CREATE TABLE partition_row_counts (
  partition_name VARCHAR(64) PRIMARY KEY,
  row_count BIGINT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 编写定时事件更新分区行数：

DELIMITER //
CREATE EVENT update_partition_counts
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE p_name VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'contact_activity';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO p_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM contact_activity PARTITION(', p_name, ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    REPLACE INTO partition_row_counts (partition_name, row_count) VALUES (p_name, @cnt);
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

3. 查询总条数：

SELECT SUM(row_count) FROM partition_row_counts;

适合对实时性要求不极高的场景，可将查询耗时降至毫秒级。

方法3：优化InnoDB缓冲池与IO参数

• 调整缓冲池实例数，避免锁竞争：

SET GLOBAL innodb_buffer_pool_instances = 8;

• 开启自适应刷新与邻页刷新优化磁盘IO：

SET GLOBAL innodb_adaptive_flushing = ON;
SET GLOBAL innodb_flush_neighbors = 1;

• 调整预读阈值，让InnoDB更积极预读索引页：

SET GLOBAL innodb_read_ahead_threshold = 8;

方法4：开启MariaDB并行查询

利用多核CPU加速分区扫描：

SET GLOBAL query_cache_type = OFF;
SET GLOBAL parallel_query = ON;
SET GLOBAL parallel_threads_per_join = 8; -- 根据CPU核心数调整

执行查询时指定并行：

SELECT /*+ PARALLEL(8) */ COUNT(*) FROM contact_activity;

内容的提问来源于stack exchange，提问作者Michael Modan