首先咱们拆解下核心原因，你这查询慢得离谱根本不是服务器性能不够，而是IO模式和数据量不匹配：

• 你的name二级索引足足有116GB，但InnoDB缓冲池只有55GB，超过一半的索引数据得从EBS磁盘读。虽然是7000 IOPS的SSD，但遍历二级索引是随机读操作——和复制表时的顺序读完全不是一个量级，顺序读能跑满带宽，随机读却受限于IOPS，这就是复制30分钟但查询要5小时的关键原因。
• EXPLAIN显示Using where; Using index，说明MySQL确实在走name索引，但它得逐条检查索引里的name值是否非空，这个过程要遍历整个116GB的索引，大量磁盘随机IO直接拖慢了速度。

下面给你几个优先级从高到低的可行方案：

1. 用汇总表做准实时统计（最快见效） #

这是最省心的优化方式，不用动原表或索引：

• 先建一个专门存统计值的小表：

  CREATE TABLE profile_stats (
      stat_name VARCHAR(50) PRIMARY KEY,
      stat_value BIGINT NOT NULL
  );
  

• 手动初始化一次统计值（虽然这次慢，但只跑一次）：

  INSERT INTO profile_stats (stat_name, stat_value)
  VALUES ('count_non_null_name', (SELECT COUNT(*) FROM profile WHERE name IS NOT NULL))
  ON DUPLICATE KEY UPDATE stat_value = VALUES(stat_value);
  

• 然后通过触发器或定时任务维护这个值：
  • 触发器：在profile表的INSERT/UPDATE/DELETE时，根据操作类型调整统计值（比如插入一条name非空的记录就+1，删除就-1，更新时从null变非空就+1，反之-1）
  • 定时任务：如果业务允许几分钟延迟，每小时跑一次统计更新就行，完全不影响主业务。
• 之后查询直接读这个小表：SELECT stat_value FROM profile_stats WHERE stat_name = 'count_non_null_name';，瞬间出结果。

2. 重建name索引减少碎片 #

如果索引存在大量碎片，MySQL遍历索引时需要读取更多数据页，会额外增加IO开销：

• 可以用ALTER TABLE profile ENGINE=InnoDB;重建表和所有索引（这个操作会锁表，一定要在业务低峰期执行，且需要预留原表1.5倍左右的磁盘空间）
• 或者单独重建name索引：ALTER TABLE profile DROP INDEX name, ADD INDEX name(name);，锁表时间会比重建整表短一些。
  重建后索引会更紧凑，遍历所需的IO次数会明显减少，能显著提升查询速度。

3. 调整InnoDB缓冲池配置 #

虽然缓冲池已经有55GB，但优化配置能让它更高效：

• 开启多缓冲池实例：在my.cnf里设置innodb_buffer_pool_instances = 8（根据内存大小调整，8-16个都合适），减少缓冲池的锁竞争，提升并发下的缓冲效率。
• 调整日志相关参数：如果业务允许少量数据丢失风险，把innodb_flush_log_at_trx_commit设为2；同时把innodb_log_file_size设到4GB（MySQL 5.5最大支持值），减少日志刷盘的频率。

4. 优化EBS磁盘IO性能（可选） #

如果上述方法还不够，可以考虑提升磁盘的随机IO能力：

• 把多个7000 IOPS的EBS卷做成RAID 0，总IOPS能翻倍，但要注意搭配定期备份保证数据安全。
• 换成AWS的io2/io2 Block Express磁盘，能提供更高IOPS和更低延迟，但成本会有所增加。

最后提醒下，因为你用的是MySQL 5.5，很多新版本的InnoDB优化（比如针对count(*)的索引优化）没法享受，所以汇总表的方案性价比最高，建议优先尝试。

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