无JOIN查询触发MAX_JOIN_SIZE超限报错的原因排查

背景信息 #

表结构 #

CREATE TABLE `tags` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id`  int(10) unsigned NOT NULL DEFAULT 1,
  `type`       varchar(20)      NOT NULL DEFAULT '',
  `name`       varchar(100)     NOT NULL DEFAULT '',
  `count`      int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp            NULL DEFAULT NULL,
  `updated_at` timestamp            NULL DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `tags_type_index`        (`type`),
  KEY `tags_domain_id_foreign` (`domain_id`),
  KEY `tags_name_index`        (`name`),
  KEY `tags_count_index`       (`count`) USING BTREE,
  CONSTRAINT `tags_domain_id_foreign` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

执行的查询语句 #

select
    *
from
    tags
where
    type = 'tag'
    and
    domain_id = 95
limit 10;

查询相关数据 #

• 无LIMIT时实际匹配行数：58,131行
• 表总数据量：1,466,914行
• domain_id=95的总行数：86,871行

问题现象 #

设置SET MAX_JOIN_SIZE = 100000;时，上述查询触发报错：The SELECT would examine more than MAX_JOIN_SIZE rows；设置为200000时则正常执行。

疑惑点 #

1. 该查询未使用JOIN，却触发了MAX_JOIN_SIZE的限制
2. 实际符合条件的行数仅58,131，远小于100000

查询的EXPLAIN结果 #

{ "id": 1, "select_type": "SIMPLE", "table": "tags", "type": "ref", "possible_keys": "tags_type_index,tags_domain_id_foreign", "key": "tags_domain_id_foreign", "key_len": "4", "ref": "const", "rows": "166160", "Extra": "Using where" } 

原因分析 #

1. MAX_JOIN_SIZE的实际作用范围
   虽然参数名包含JOIN，但它并非只限制JOIN查询。MariaDB中，该参数用于限制优化器预估的查询要检查的总行数，所有SELECT查询都会被评估，只要预估扫描行数超过设定值就会触发报错。

2. 预估行数与实际行数的差异
   EXPLAIN结果中的rows字段显示优化器预估要扫描166160行，这个数值是基于表的统计信息计算的，并非实际匹配行数。当前查询选择了tags_domain_id_foreign单字段索引，先获取所有domain_id=95的行，再过滤type='tag'的记录。由于统计信息可能过时或不够精准，优化器预估的扫描行数（166160）超过了设置的100000，因此触发报错。

解决方案建议 #

• 更新表统计信息：执行ANALYZE TABLE tags;，让优化器获取更准确的行数预估，可能会降低预估扫描行数。
• 创建联合索引：创建(domain_id, type)的联合索引，这样查询可以直接通过索引同时过滤两个条件，大幅减少扫描行数，既解决报错问题又提升查询效率。
• 调整MAX_JOIN_SIZE参数：如果确认该查询是业务必需的合理查询，可以适当调大参数值，但需注意平衡，避免放过真正的低效查询。

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