mariadb-10.4 - 没有索引提示的 MariaDB MAX_JOIN_SIZE 错误

Question

有 max_join_size = 300M 的 MariaDB 10.4.14 和里面有大约 150,000 条记录的硬币 InnoDB 表。

一个足够简单的查询会产生 MAX_JOIN_SIZE 错误:

SELECT * FROM coin z -- USE INDEX(PRIMARY)
WHERE z.id IN (5510, 5511, 5512 /* more item IDs up to 250 */)
  AND z.currency_id IN (8, 227)
  AND z.distribution_id IN (1, 2);

Error Code: 1104
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

但是带有索引提示的相同查询效果很好。命中 ["PRIMARY", "currency_id_idx", "distribution_id"] 甚至 issue_date_idx 中的索引并不重要即使根本没有索引 USE INDEX() 也能正常工作。

这里可能有什么问题？为什么查询在没有提示的情况下不起作用？

顺便说一句，此查询在 MariaDB 10.3.24 上运行良好，但在 10.5.5 上运行不佳

OPTIMIZE TABLE coin; -- didn't help

Table DDL and query EXPLAIN
CREATE TABLE `coin` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(90) NOT NULL DEFAULT '',
  `country_id` smallint(5) unsigned NOT NULL DEFAULT 0,
  `currency_id` smallint(5) unsigned NOT NULL DEFAULT 0,
  `distribution_id` tinyint(3) unsigned NOT NULL,
  `issue_date` date NOT NULL DEFAULT '0000-00-00',
  -- and other, total 29 fields
  PRIMARY KEY (`id`),
  KEY `issue_date_idx` (`issue_date`),
  KEY `currency_id_idx` (`currency_id`),
  KEY `distribution_id` (`distribution_id`),
  -- and other, total 21 indices for other fields which don't use currency_id or distribution_id
  CONSTRAINT `coin_ibfk_4` FOREIGN KEY (`currency_id`) REFERENCES `currency` (`id`),
  CONSTRAINT `coin_ibfk_11` FOREIGN KEY (`distribution_id`) REFERENCES `distribution` (`id`),
  -- and other, total 13 CONSTRAINTs
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN FORMAT=JSON -- for SELECT above without index hint
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "z",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "currency_id_idx", "distribution_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["id"],
      "rows": 50,
      "filtered": 100,
      "attached_condition": "z.`id` in (5510,5511,5512, /* ... total 100 */) and z.currency_id in (8,227) and z.distribution_id in (1,2)"
    }
  }
}

上面的 SELECT 在没有索引提示的情况下使用最大可能的 MAX_JOIN_SIZE 值并且不使用最大值 - 1:

SET MAX_JOIN_SIZE=18446744073709551615 -- this works
SET MAX_JOIN_SIZE=18446744073709551614 -- this doesn't work

Answer 1

可以通过恢复以前的默认值来修复 MAX_JOIN_SIZE 错误

optimizer_use_condition_selectivity = 1 # new default is 4 since 10.4.1