10亿行MySQL表场景下如何优化基础SELECT查询速度

核心问题根因 #

你现在的查询慢的核心原因是回表IO开销过大：

• 现有page_word二级索引仅存储了page_word和主键id，执行SELECT page_id, word_source, word_frequency时，每匹配到一个page_word的索引条目，都需要根据主键id回表查询聚簇索引获取剩余字段
• 10亿行数据量级下，匹配到的条目通常达到数十万/数百万级，大量随机磁盘IO直接导致查询耗时到百秒级
• COUNT(*)快是因为仅需要统计索引匹配条目数，不需要回表，仅扫描二级索引即可完成

可落地优化方案（按优先级排序） #

1. 新建联合覆盖索引（改造成本最低，收益最高） #

直接创建包含查询所需所有字段的联合索引，彻底消除回表开销：

CREATE INDEX idx_page_word_cover ON inner_pages_content (`page_word`, `page_id`, `word_source`, `word_frequency`) USING BTREE;

创建完成后，你的目标查询可以完全通过该二级索引拿到所有需要的字段，不需要访问主表，耗时可以降到和COUNT(*)同一量级（数百毫秒级）。

2. 调整InnoDB缓冲池配置 #

• CloudSQL实例的innodb_buffer_pool_size建议设置为实例内存的70%~80%，优先缓存热点索引数据
• 你新建的覆盖索引大小远小于100GB全表大小，只需配置数十GB内存即可将索引完全缓存到内存，进一步降低查询耗时

3. 查询语句优化 #

• 单次查询IN子句中的关键词数量建议控制在50个以内，关键词过多时可以拆分为多个并行查询后合并结果
• 避免在page_word上使用前缀模糊匹配（如LIKE '%xxx'），否则无法命中索引

4. 进阶优化（如上述方案仍不满足性能要求） #

如果查询并发很高或者后续数据量继续增长，可以考虑：

• 按page_word的哈希值或者首字母进行分表，拆分单表数据量
• 迁移到专门的全文检索引擎（如Elasticsearch），更适合关键词匹配、相关性计算场景

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