如何让配置一致的MariaDB从库采用相同的查询执行计划？

问题背景 #

在MariaDB 10.11.5主从复制环境中，两台从库的my.cnf配置、MariaDB二进制文件及硬件完全一致，数据保持同步，且已执行以下语句生成持久化统计信息：

ANALYZE TABLE `photo-gallery-extra`, `photo-gallery`, `photo` PERSISTENT FOR ALL;

同一查询在两台从库上执行计划差异显著：

• 第一台从库（执行速度快）：以photo-gallery为驱动表，使用added索引扫描，避免全表扫描和文件排序
• 第二台从库（执行速度慢）：以photo-gallery-extra为驱动表，执行全表扫描并触发Using temporary; Using filesort

第一台从库的查询及执行计划 #

查询语句：

EXPLAIN EXTENDED
SELECT
    `p`.*,
    `photo-gallery`.`lid` AS `glid`,
    UNIX_TIMESTAMP(`photo-gallery`.`added`) AS `streamdate`,
    `photo-gallery`.`count`,
    `photo-gallery`.`owner` AS `uid`,
    `photo-gallery`.`views`,
    `photo-gallery`.`rate`,
    `photo-gallery`.`ratecnt`,
    `photo-gallery`.`ratesum`,
    `photo-gallery-extra`.`title` AS `gtitle`
FROM
    `photo-gallery`
INNER JOIN
    `photo-gallery-extra` ON `photo-gallery`.`gid` = `photo-gallery-extra`.`gid`
INNER JOIN (
    SELECT
        `q`.`gid`,
        `r`.`id`,
        `r`.`lid`,
        `r`.`title`,
        `r`.`gif`,
        `r`.`width`,
        `r`.`height`
    FROM
        `photo` AS `q`
    LEFT JOIN
        `photo` AS `r` ON `q`.`id` = `r`.`id`
    WHERE
        `q`.`mod` != 2
    GROUP BY
        `q`.`gid`
) AS `p` ON `photo-gallery`.`gid` = `p`.`gid`
WHERE
    `photo-gallery`.`status` = 1
    AND `photo-gallery`.`moderated` != 2
ORDER BY
    `photo-gallery`.`added` DESC
LIMIT 0, 999;

执行计划：

+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+
| id   | select_type     | table               | type   | possible_keys                              | key     | key_len | ref                       | rows | filtered | Extra                              |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+
|    1 | PRIMARY         | photo-gallery       | index  | PRIMARY,moderated,status,status_2,status_3 | added   | 5       | NULL                      | 1671 |    59.69 | Using where                        |
|    1 | PRIMARY         | photo-gallery-extra | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.photo-gallery.gid | 1    |   100.00 |                                    |
|    1 | PRIMARY         | <derived2>          | ref    | key0                                       | key0    | 4       | testdb.photo-gallery.gid | 2    |   100.00 |                                    |
|    2 | LATERAL DERIVED | q                   | ref    | mod,mod_2,gid                              | gid     | 4       | testdb.photo-gallery.gid | 10   |    69.93 | Using index condition; Using where |
|    2 | LATERAL DERIVED | r                   | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.q.id              | 1    |   100.00 |                                    |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+

第二台从库的查询及执行计划 #

查询语句：

EXPLAIN EXTENDED SELECT `p`.*, `photo-gallery`.`lid` AS `glid`, UNIX_TIMESTAMP(`photo-gallery`.`added`) AS `streamdate`, `photo-gallery`.`count`, `photo-gallery`.`owner` AS `uid`, `photo-gallery`.`views`, `photo-gallery`.`rate`, `photo-gallery`.`ratecnt`, `photo-gallery`.`ratesum`, `photo-gallery-extra`.`title` AS `gtitle` FROM `photo-gallery` INNER JOIN `photo-gallery-extra` ON `photo-gallery`.`gid`=`photo-gallery-extra`.`gid` INNER JOIN (SELECT `q`.`gid`, `r`.`id`, `r`.`lid`, `r`.`title`, `r`.`gif`, `r`.`width`, `r`.`height` FROM `photo` AS `q` LEFT JOIN `photo` AS `r` ON `q`.`id`=`r`.`id` WHERE `q`.`mod`!=2 GROUP BY `q`.`gid`) AS `p` ON `photo-gallery`.`gid`=`p`.`gid` WHERE `photo-gallery`.`status`=1 AND `photo-gallery`.`moderated`!=2 ORDER BY `photo-gallery`.`added` DESC LIMIT 0,999;

执行计划：

+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
| id   | select_type     | table               | type   | possible_keys                              | key     | key_len | ref                             | rows    | filtered | Extra                              |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
|    1 | PRIMARY         | photo-gallery-extra | ALL    | PRIMARY                                    | NULL    | NULL    | NULL                            | 1370470 |   100.00 | Using temporary; Using filesort    |
|    1 | PRIMARY         | photo-gallery       | eq_ref | PRIMARY,moderated,status,status_2,status_3 | PRIMARY | 4       | testdb.photo-gallery-extra.gid | 1       |    29.78 | Using where                        |
|    1 | PRIMARY         | <derived2>          | ref    | key0                                       | key0    | 4       | testdb.photo-gallery-extra.gid | 2       |   100.00 |                                    |
|    2 | LATERAL DERIVED | q                   | ref    | mod,mod_2,gid                              | gid     | 4       | testdb.photo-gallery.gid       | 10      |    69.92 | Using index condition; Using where |
|    2 | LATERAL DERIVED | r                   | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.q.id                    | 1       |   100.00 |                                    |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
5 rows in set, 1 warning (0.001 sec)

解决方法 #

1. 使用查询提示强制执行计划 #

直接修改查询语句，通过提示强制优化器选择和第一台从库一致的执行逻辑：

EXPLAIN EXTENDED
SELECT
    `p`.*,
    `photo-gallery`.`lid` AS `glid`,
    UNIX_TIMESTAMP(`photo-gallery`.`added`) AS `streamdate`,
    `photo-gallery`.`count`,
    `photo-gallery`.`owner` AS `uid`,
    `photo-gallery`.`views`,
    `photo-gallery`.`rate`,
    `photo-gallery`.`ratecnt`,
    `photo-gallery`.`ratesum`,
    `photo-gallery-extra`.`title` AS `gtitle`
FROM
    `photo-gallery` FORCE INDEX(added)
STRAIGHT_JOIN
    `photo-gallery-extra` ON `photo-gallery`.`gid` = `photo-gallery-extra`.`gid`
INNER JOIN (
    SELECT
        `q`.`gid`,
        `r`.`id`,
        `r`.`lid`,
        `r`.`title`,
        `r`.`gif`,
        `r`.`width`,
        `r`.`height`
    FROM
        `photo` AS `q`
    LEFT JOIN
        `photo` AS `r` ON `q`.`id` = `r`.`id`
    WHERE
        `q`.`mod` != 2
    GROUP BY
        `q`.`gid`
) AS `p` ON `photo-gallery`.`gid` = `p`.`gid`
WHERE
    `photo-gallery`.`status` = 1
    AND `photo-gallery`.`moderated` != 2
ORDER BY
    `photo-gallery`.`added` DESC
LIMIT 0, 999;

• FORCE INDEX(added)：强制photo-gallery表使用added索引，和第一台从库的执行计划对齐
• STRAIGHT_JOIN：强制优化器按照SQL中表的顺序执行连接，避免选择photo-gallery-extra作为驱动表

2. 调整优化器参数 #

临时或永久修改第二台从库的优化器参数，引导其生成最优计划：

• 会话级临时生效：

SET optimizer_switch='derived_merge=off';

该参数关闭派生表合并，可能让优化器更倾向于选择第一台从库的执行逻辑，测试后如果有效再考虑永久配置。

• 全局永久生效：
  修改my.cnf配置文件，添加以下内容后重启MariaDB：

optimizer_switch='derived_merge=off'

注意：该参数会影响所有查询，需评估全局影响后再配置。

3. 重新刷新统计信息 #

尽管已执行过ANALYZE TABLE，但仍可能存在统计信息不一致的情况，重新执行以下语句：

ANALYZE TABLE `photo-gallery`, `photo-gallery-extra`, `photo` PERSISTENT FOR ALL;

执行完成后重新查看执行计划，确认是否恢复为最优逻辑。

4. 启用查询计划缓存 #

开启MariaDB的查询计划缓存，让优化器复用已验证的最优计划：
修改my.cnf配置文件：

query_cache_type = ON
query_cache_size = 64M

重启MariaDB后，相同查询会直接复用缓存中的最优执行计划。注意：MariaDB 10.11中查询缓存已标记为废弃，若后续版本升级需留意替代方案。

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