MySQL 8使用index merge intersect的慢查询优化方案咨询

优化思路 #

现在你的查询走了index merge intersect逻辑，本质是MySQL分别检索三个单列索引的结果集后计算交集，这个交集计算本身有额外开销，再加上1.5万行的匹配计算，最终耗时到200ms。结合你不能新增复合索引的限制，可以参考以下优化方案：

• 优先选择过滤性最高的单列索引强制走查
  先分别统计三个过滤条件单独的匹配行数：

  SELECT COUNT(*) FROM accounts WHERE city_id=1;
  SELECT COUNT(*) FROM accounts WHERE country_id=7;
  SELECT COUNT(*) FROM accounts WHERE age=3;
  

  找到返回行数最少、过滤性最强的索引，用FORCE INDEX强制查询走该索引，剩下的两个条件直接在结果集里过滤，多数情况下会比三个索引合并取交集的速度快很多，示例写法：

  SELECT COUNT(`id`) AS AGGREGATE
  FROM `accounts` FORCE INDEX(你选的索引名)
  WHERE `city_id` = 1 AND `country_id` = 7 AND `age` = 3
  

• 新增独立的预聚合汇总表
  如果业务对该类统计查询的实时性要求不高，可以单独建一张聚合汇总表，完全不占用原表的索引配额：

  CREATE TABLE `accounts_count_agg` (
    `city_id` bigint unsigned NOT NULL,
    `country_id` bigint unsigned NOT NULL,
    `age` tinyint unsigned NOT NULL,
    `cnt` bigint unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY (`city_id`,`country_id`,`age`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

  可以通过定时任务、触发器或者业务代码双写的方式同步数据，查询时直接取对应维度的cnt值，耗时可以降到10ms以内。
• 去除冗余的过滤条件
  一般行政区域数据存在关联关系：city_id关联的城市本身就所属固定的province_id和country_id，如果你的业务逻辑里city_id=1对应的国家本来就是country_id=7，可以直接删掉WHERE条件里的country_id=7，少一个字段的匹配就能直接降低索引合并的开销。
• 调整MySQL优化器参数
  可以临时关闭索引合并交叉逻辑，让优化器自动选择最优的单列索引走查，避免额外的交集计算开销：

  SET SESSION optimizer_switch = 'index_merge_intersection=off';
  

  测试性能提升明显的话可以考虑全局生效，另外适当调大read_buffer_size、join_buffer_size参数，给索引合并的中间运算提供足够内存，也能降低耗时。

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