MariaDB 10.3迁移升级至10.5后JOIN查询异常缓慢求助

将运行MariaDB 10.3.27-MariaDB-0+deb10u1的Galera集群数据库迁移至新硬件并升级到10.5.18-MariaDB-0+deb11u1-log版本后，使用mysqldump导出导入的所有JOIN查询速度极慢。示例查询如下：

SELECT *
  FROM claim_notes
         INNER JOIN claims
                 ON claim_notes.claim_id = claims.id
         INNER JOIN users
                 ON claim_notes.user_id = users.id
 WHERE claim_notes.email_queue = 1

该查询在旧集群耗时0.7秒，新集群则需3分51秒。新旧环境的数据库Schema（含所有索引）及数据集完全一致，排除查询优化不佳或数据集差异导致的问题。通过EXPLAIN发现优化器执行计划差异显著：

旧环境执行计划 #

+------+-------------+-------------+--------+------------------------------------------------------+-------------+---------+---------------------------------------+------+-------+
| id   | select_type | table       | type   | possible_keys                                        | key         | key_len | ref                                   | rows | Extra |
+------+-------------+-------------+--------+------------------------------------------------------+-------------+---------+---------------------------------------+------+-------+
|    1 | SIMPLE      | claim_notes | ref    | claim_id,email_queue,user_id,claim_type_user_dtstamp | email_queue | 1       | const                                 |    3 |       |
|    1 | SIMPLE      | users       | eq_ref | PRIMARY                                              | PRIMARY     | 4       | guido_commercial.claim_notes.user_id  |    1 |       |
|    1 | SIMPLE      | claims      | eq_ref | PRIMARY                                              | PRIMARY     | 4       | guido_commercial.claim_notes.claim_id |    1 |       |
+------+-------------+-------------+--------+------------------------------------------------------+-------------+---------+---------------------------------------+------+-------+
3 rows in set (0.001 sec)

新环境执行计划 #

+------+-------------+-------------+------+------------------------------------------------------+-------------+---------+-------+------+------------------------------------+
| id   | select_type | table       | type | possible_keys                                        | key         | key_len | ref   | rows | Extra                              |
+------+-------------+-------------+------+------------------------------------------------------+-------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | claims      | ALL  | PRIMARY                                              | NULL        | NULL    | NULL  | 1    |                                    |
|    1 | SIMPLE      | users       | ALL  | PRIMARY                                              | NULL        | NULL    | NULL  | 1    | Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | claim_notes | ref  | claim_id,email_queue,user_id,claim_type_user_dtstamp | email_queue | 1       | const | 5    | Using where                        |
+------+-------------+-------------+------+------------------------------------------------------+-------------+---------+-------+------+------------------------------------+
3 rows in set (0.000 sec)

所有关联字段（如claim_notes.claim_id、claims.id等）均已建立索引，但新环境中2个表未使用索引。已尝试设置eq_range_index_dive_limit为0但无效。相关表结构（省略无关字段）如下：

CREATE TABLE `claims` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created` timestamp NOT NULL DEFAULT current_timestamp(),
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=173985 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `claim_notes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `claim_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `email_queue` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `claim_id` (`claim_id`),
  KEY `email_queue` (`email_queue`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26559123 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `active` tinyint(4) DEFAULT NULL,
  `username` varchar(32) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=576 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1. 重新生成表统计信息 #

mysqldump导入后，统计信息可能不完整或不准确，导致优化器误判执行计划。执行以下语句更新统计信息：

ANALYZE TABLE claims, users, claim_notes;

2. 调整优化器参数，禁用BNL Join #

MariaDB 10.4+默认启用Block Nested Loop（BNL）Join，当统计信息不准确时，优化器可能选择低效的BNL方式。临时关闭测试：

SET SESSION optimizer_switch='block_nested_loop=off';

若测试有效，可在配置文件（my.cnf/my.ini）中添加以下配置永久生效：

optimizer_switch='block_nested_loop=off'

3. 强制指定执行顺序（临时应急方案） #

使用STRAIGHT_JOIN强制优化器按照旧环境的表顺序执行查询，确保利用主键索引：

SELECT *
FROM claim_notes
STRAIGHT_JOIN claims ON claim_notes.claim_id = claims.id
STRAIGHT_JOIN users ON claim_notes.user_id = users.id
WHERE claim_notes.email_queue = 1;

4. 验证字符集与排序规则一致性 #

确认所有表的字符集和排序规则完全一致，避免隐性转换导致索引失效：

SHOW CREATE TABLE claims;
SHOW CREATE TABLE users;
SHOW CREATE TABLE claim_notes;

确保CHARSET和COLLATE字段完全匹配。

5. 检查InnoDB缓冲池配置 #

新硬件的innodb_buffer_pool_size若设置过小，会导致索引无法缓存，影响查询性能。查看当前配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

对于专用数据库服务器，建议设置为物理内存的50%-70%，修改后重启服务生效。

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