如何提升InnoDB表扫描性能（勿建议添加索引规避扫描）

首先得明确：你现在的核心矛盾是InnoDB全表/分区扫描性能远不如MyISAM，且无法通过加索引、升级硬件解决，只能靠临时调优撑到新系统上线。结合你的环境（Percona 5.7.19、48G缓冲池、按天分区的宽时序表），给你几个针对性的临时方案：

一、优化InnoDB I/O相关参数（立竿见影的调优） #

InnoDB比MyISAM体积大2-3倍，I/O绝对是瓶颈，优先调这些：

• 提升I/O线程数：把innodb_read_io_threads和innodb_write_io_threads都设成16（默认是4），利用多磁盘/多核的并行I/O能力，执行命令：

  SET GLOBAL innodb_read_io_threads = 16;
  SET GLOBAL innodb_write_io_threads = 16;
  

  记得写到my.cnf里重启生效。
• 调整刷盘策略（临时牺牲一点安全性换性能）：把innodb_flush_log_at_trx_commit设为2，这样日志每秒刷盘一次，而不是每次事务都刷；同时把innodb_flush_method改成O_DIRECT_NO_FSYNC（Percona专属，跳过文件系统缓存直接写磁盘，减少I/O开销）。
• 减少缓冲池锁竞争：把innodb_buffer_pool_instances设为8（48G缓冲池的话，每个实例6G，刚好符合Percona的建议），避免全表扫描时缓冲池的全局锁拖慢速度。

二、最大化分区的优势 #

你已经按天分区了，再挖点潜力：

• 强制分区修剪：确保所有扫描查询的WHERE条件里明确指定timestamp的范围，比如WHERE timestamp BETWEEN '2024-05-01 00:00:00' AND '2024-05-01 23:59:59'，让MySQL只扫描目标分区，避免不小心扫全部分区。
• 并行扫描分区：如果你的聚合/告警进程是单线程的，改成多线程并行处理不同分区（比如一个线程扫一天的分区），然后合并结果，充分利用多核CPU。Percona 5.7支持并行查询，但需要开启optimizer_switch='parallel_execution=on'，不过这个对全表扫描的提升有限，不如应用层多线程直接。

三、缓冲池热数据保护 #

全表扫描很容易把缓冲池里的热索引数据挤出去，导致后续查询变慢：

• 设置innodb_old_blocks_time=1000（单位毫秒），这个参数控制新加载的页面多久后才会进入热数据区，全表扫描的临时数据会先放在冷区，不会把常用的二级索引数据挤走，保护缓冲池的有效利用率。

四、尝试InnoDB压缩行格式 #

你已经缩短了行长度，再试试COMPRESSED行格式：

• 对大表执行ALTER TABLE your_table ROW_FORMAT=COMPRESSED;，Percona的InnoDB压缩对浮点型列的压缩效果不错，能大幅减少磁盘占用（可能把体积降到接近MyISAM），虽然会增加一点CPU消耗，但如果I/O是瓶颈的话，这个交换完全值得。注意要选业务低峰期操作，因为ALTER TABLE会锁表。

五、临时缓存重复查询结果 #

你提到告警和聚合进程每半小时重复处理全天数据，这是巨大的浪费：

• 临时加个缓存层：比如每次处理完当天的聚合结果，把存在一个独立的小表（比如daily_agg_20240501）里，下次直接查这个小表，不用再扫大分区。如果是告警规则的重复计算，也可以把中间结果存在内存缓存（比如Redis）里，半小时内直接复用。
• 改成增量处理：临时改一下代码，记录上次处理的最大timestamp，下次只处理新增的数据，不用全扫全天数据——这个改代码量应该不大，却能直接把扫描量降到几十分之一。

六、临时切回MyISAM的注意事项 #

如果以上方案都不够，切回MyISAM也不是不行，但要注意：

• 调优MyISAM的缓存：把key_buffer_size设为8G（不要超过10G，避免占用太多内存影响系统），read_buffer_size和read_rnd_buffer_size设为64M，提升全表扫描的读缓存效率。
• 注意锁问题：MyISAM是表级锁，如果你的写入操作（插入新数据）比较频繁，可能会导致查询被阻塞，所以尽量把写入和扫描操作错开，或者用LOW_PRIORITY执行写入，让扫描优先。

最后要强调：这些都是临时过渡方案，你正在推进的表结构和查询方式改造才是长期解决办法——比如把宽表拆成窄表（时序数据常用的“标签+值”模式），但在新系统上线前，上面的方案应该能帮你撑住性能。

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