MySQL 800万行日志表性能异常，求排查优化方案

问题现象 #

• 日志表channel_account_log数据量超800万行时，所有表的简单SELECT/LIMIT查询耗时均超20秒；行数降至750万以下后，所有查询恢复正常（耗时<1秒）。
• 数据量超800万时，删除1万条数据耗时超60秒；行数低于750万后，删除10万条仅需20秒。
• 目前表中仅剩50万行，性能正常。

数据库环境 #

• MySQL版本：5.7.23
• 服务器配置：Linux系统，160GB磁盘（剩余120GB+），8GB内存
• 其他表数据量：300行~100万行不等
• 数据库备份大小：原6.7GB，清理后2.9GB

慢查询语句 #

SELECT COUNT(*) from logs_table;
SELECT COUNT(id) from logs_table;
SELECT * FROM logs_table LIMIT 1000;
SELECT * FROM logs_table LIMIT 1;
SELECT COUNT(*) FROM any_other_table;
SELECT * FROM any_other_table;

日志表结构 #

CREATE TABLE `channel_account_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT, 
  `version` bigint(20) NOT NULL, 
  `channel_account_id` bigint(20) NOT NULL, 
  `date_created` datetime NOT NULL, 
  `request_data` longtext, 
  `request_date` datetime DEFAULT NULL, 
  `requesturl` varchar(255) DEFAULT NULL, 
  `response_data` longtext, 
  `response_date` datetime DEFAULT NULL, 
  `responseurl` varchar(255) DEFAULT NULL, 
  `status` bigint(20) NOT NULL, 
  `status_message` longtext, 
  PRIMARY KEY (`id`), 
  KEY `FK_7lv26awkgdej6du76hq1111i` (`channel_account_id`), 
  CONSTRAINT `FK_7lv26awkgdej6du76hq1111i` FOREIGN KEY (`channel_account_id`) REFERENCES `channel_account` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 25641307 DEFAULT CHARSET = utf8

执行计划（EXPLAIN SELECT COUNT(id) from channel_account_log;） #

表状态信息 #

索引信息 #

排查分析 #

1. 内存配置不足 #

MySQL 5.7默认innodb_buffer_pool_size仅128M，而你的日志表数据文件达7.6GB，远超出内存容量。当数据量超过缓冲池承载范围时，会频繁触发磁盘IO，导致所有查询变慢。建议将innodb_buffer_pool_size设置为物理内存的50%~70%（例如5GB），减少磁盘交换开销。

2. 索引选择异常 #

从执行计划看，COUNT(id)选择了低基数的channel_account_id索引（基数仅8670），而非主键索引。InnoDB会优先选择最小的非聚簇索引做COUNT统计，但该索引因基数低，扫描效率反而更差。可强制指定主键索引验证性能：SELECT COUNT(id) from channel_account_log FORCE INDEX(PRIMARY);

3. 表碎片影响性能 #

大量删除数据后性能恢复，说明原表存在严重碎片。InnoDB删除数据后不会立即释放磁盘空间，碎片会降低磁盘IO效率。可在业务低峰期执行OPTIMIZE TABLE channel_account_log;整理碎片，或用ALTER TABLE channel_account_log ENGINE=InnoDB;重建表。

4. 外键关联额外开销 #

日志表存在外键关联到channel_account，数据量极大时，删除等操作会触发外键检查的额外开销。可临时关闭外键检查（SET FOREIGN_KEY_CHECKS=0;）执行批量删除，操作完成后重新开启，注意确保数据一致性。

5. 版本与配置优化 #

MySQL 5.7.23版本较旧，建议升级到5.7最新小版本或8.0，获取更好的性能优化。同时检查以下配置：

• innodb_log_file_size: 设置为256M~1GB，提升事务日志效率
• innodb_flush_log_at_trx_commit: 若对一致性要求不高，可设为2，减少磁盘刷写频率
• query_cache_type: 5.7中查询缓存默认关闭，无需开启，避免额外开销

总结 #

MySQL本身可轻松处理千万级数据，你的问题主要是内存配置不足导致的磁盘IO瓶颈，叠加索引选择异常、表碎片等因素。优先调整innodb_buffer_pool_size，再结合碎片整理、索引优化即可解决问题。

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