MySQL COUNT查询性能优化求助：大表count(1)查询耗时过长

问题描述 #

执行以下COUNT查询耗时约2秒，远低于预期：

SELECT count(1) FROM pages WHERE site_id = 123456 AND online = 1 AND ignored = 0

该表约有500万条记录，大小2GB，应用中更大更复杂的查询反而更快。已确认查询使用了Index 2，且近期执行过OPTIMIZE TABLE，但性能无明显改善。

表结构 #

CREATE TABLE `pages` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`created` DATETIME NOT NULL,
`modified` DATETIME NOT NULL,
`site_id` INT(11) NOT NULL,
`path` LONGTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_bin',
`online` TINYINT(4) NULL DEFAULT '1',
`ignored` TINYINT(4) NULL DEFAULT '0',
`redirected_to_page_id` INT(11) NULL DEFAULT '0',
`latest_http_response` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_bin',
`noindex_nofollow_result` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_bin',
`deleted` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`) USING BTREE,
INDEX `Index 2` (`site_id`, `online`, `ignored`, `redirected_to_page_id`, `deleted`) USING BTREE,
INDEX `Index 4` (`site_id`, `deleted`, `noindex_nofollow_result`) USING BTREE,
INDEX `Index 5` (`crawl_job_id`) USING BTREE,
INDEX `Index 3` (`site_id`, `latest_http_response`, `online`, `ignored`, `deleted`) USING BTREE
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=13135003;

EXPLAIN执行结果 #

1. 压缩覆盖索引，减少IO开销 #

当前Index 2包含了redirected_to_page_id和deleted两个无关字段，会增大索引体积，导致更多磁盘IO和内存占用。创建仅包含查询过滤字段的紧凑覆盖索引：

CREATE INDEX idx_site_online_ignored ON pages(site_id, online, ignored);

这个索引比Index 2小得多，InnoDB统计时只需扫描更小的索引树，能显著提升速度。若没有其他查询依赖Index 2，可在新索引生效后删除它。

2. 更新表统计信息 #

InnoDB优化器依赖准确的表统计信息选择执行计划，过时的统计可能导致预估偏差。执行以下命令更新：

ANALYZE TABLE pages;

更新后重新执行EXPLAIN，确认rows预估是否更接近实际匹配行数。

3. 采用近似计数（业务允许时） #

如果不需要精确计数，可直接从元数据获取近似值，耗时毫秒级：

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'pages';

4. 缓存计数结果 #

若计数无需实时更新，可通过缓存或统计表优化：

• 应用层缓存：用Redis等工具缓存计数，在pages表数据变更（插入/更新/删除符合条件的记录）时同步更新缓存。
• 数据库统计表：创建site_page_counts表，存储每个site_id对应的符合条件的页数，通过触发器在pages表数据变更时自动更新统计值，查询时直接读取小表即可。

5. 调整InnoDB缓冲区配置 #

确保innodb_buffer_pool_size足够大，能将常用索引和数据加载到内存。专用数据库服务器建议设置为可用内存的50%-70%，查看当前配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

缓冲区过小会导致频繁磁盘IO，拖慢查询速度。

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