MariaDB从10.3.39迁移到10.6.16后执行计划变化致性能问题求助

问题概述 #

将数据库从MariaDB 10.3.39升级到10.6.16后，大量未优化的老旧查询出现严重性能退化。以某核心查询为例：旧版本执行耗时2秒，新版本需30秒，执行计划差异显著。

示例查询 #

SELECT SQL_NO_CACHE
`Users`.`EMAIL` AS `ID`,
`Users`.`ER_NR`,
`Users`.`SUB_ER_NR`,
`Users`.`USER_ID`,
`ANREDE`,
`PRAEFIX`,
`VORNAME`,
`ZWEITER_VORNAME`,
`NAME`,
`Users`.`EMAIL`,
IF(`IST_ANSPRECHPARTNER` = 'Ja', 'Ja', 'Nein') AS `IST_ANSPRECHPARTNER`,
(
        SELECT
                IF(COUNT(*) > 0, 'Ja', 'Nein') AS CC
        FROM
                `Users_Sell_Courses`
        WHERE
                `USER_ID` = `Users`.`USER_ID`
                AND `SELL_ID` IN (
                        SELECT
                                `SELL_ID`
                        FROM
                                `Sell_Courses`
                        WHERE
                                `COURSE_ID` = 63
                )
) AS `IST_PRAXISANLEITER`,
(
        SELECT
                IF(COUNT(*) > 0, 'Ja', 'Nein') AS CC
        FROM
                `Users_Sell_Courses`
        WHERE
                `USER_ID` = `Users`.`USER_ID`
                AND `SELL_ID` IN (
                        SELECT
                                `SELL_ID`
                        FROM
                                `Sell_Courses`
                        WHERE
                                `COURSE_ID` = 64
                )
) AS `IST_WUNDMANAGER`,
`Customer`.`STATUS`,
`Customer`.`FA_NAME`,
`PICK_ER_TYP`,
`BEGINN`,
`ENDE`,
`BLACKLIST`,
`MAILING`,
(
        SELECT
                COUNT(*)
        FROM
                `Sell_Courses`
        WHERE
                `Sell_Courses`.`SUB_ER_NR` = `Customer`.`SUB_ER_NR`
                AND (
                        YEAR(`VERTRAGSENDE`) = 0
                        OR `VERTRAGSENDE` > NOW()
                )
) AS `SoldCourses`,
`Package_Contracts`.`PACKAGE`
FROM
`Users`,
`Customer`
LEFT JOIN `Package_Contracts` ON (
        `Customer`.`SUB_ER_NR` = `Package_Contracts`.`SUB_ER_NR`
        OR `Customer`.`RECHNUNG_ZAHLER_SUB_ER_NR` = `Package_Contracts`.`SUB_ER_NR`
),
`Picklist_ER_Typ`
WHERE
`Users`.`SUB_ER_NR` = `Customer`.`SUB_ER_NR`
AND `Users`.`USER_STATUS` = 'Aktiv'
AND `Customer`.`ER_TYP` = `Picklist_ER_Typ`.`CUR_ID`
HAVING
`IST_ANSPRECHPARTNER` = 'Ja'
ORDER BY
`ID`

执行计划关键差异 #

MariaDB 10.3.39执行计划 #

1   PRIMARY Users   ref SUB_ER_NR_2,SUB_ER_NR,IDX_USER_STATUS   IDX_USER_STATUS 1   const   165446  Using index condition; Using where; Using filesort
1   PRIMARY Customer    eq_ref  UQX-SUB_ER_NR,SUB_ER_NR,IDX_ER_TYP  UQX-SUB_ER_NR   20  Users.SUB_ER_NR 1   Using where
1   PRIMARY Picklist_ER_Typ eq_ref  PRIMARY PRIMARY 4   Customer.ER_TYP 1   
1   PRIMARY Package_Contracts   ALL SUB_ER_NR   NULL    NULL    NULL    524 Range checked for each record (index map: 0x2)
6   DEPENDENT SUBQUERY  Sell_Courses    ref IDX_SUB_ER_NR,IDX_VERTRAGSENDE  IDX_SUB_ER_NR   4   Customer.SUB_ER_NR  1   Using index condition; Using where
4   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
4   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where
2   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
2   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where

MariaDB 10.6.16执行计划 #

1   PRIMARY Picklist_ER_Typ ALL PRIMARY NULL    NULL    NULL    19  Using temporary; Using filesort
1   PRIMARY Customer    ref IDX_ER_TYP  IDX_ER_TYP  5   Picklist_ER_Typ.CUR_ID  267 
1   PRIMARY Users   ref SUB_ER_NR_2,SUB_ER_NR,IDX_USER_STATUS   SUB_ER_NR_2 26  func    18  Using index condition; Using where
1   PRIMARY Package_Contracts   ALL SUB_ER_NR   NULL    NULL    NULL    599 Range checked for each record (index map: 0x2)
6   DEPENDENT SUBQUERY  Sell_Courses    ref IDX_SUB_ER_NR,IDX_VERTRAGSENDE  IDX_SUB_ER_NR   4   Customer.SUB_ER_NR  1   Using index condition; Using where
4   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
4   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where
2   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
2   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where

核心差异：

• 10.3版本以Users为驱动表，使用IDX_USER_STATUS索引过滤出165446条活跃用户，再关联其他表；
• 10.6版本以Picklist_ER_Typ为驱动表，全表扫描后关联Customer得到267条数据，再通过SUB_ER_NR_2索引关联Users，完全未使用IDX_USER_STATUS，导致后续关联逻辑效率暴跌。

当前临时方案及痛点 #

已通过在Users表添加FORCE INDEX(IDX_USER_STATUS)恢复单查询性能，但系统内大量类似查询逐一修改耗时极高；尝试调整optimizer_switch参数恢复旧版执行逻辑未成功，需批量优化方案。

批量优化建议 #

1. 回退优化器成本模型 #

MariaDB 10.4+引入了新的复杂成本模型（optimizer_cost_model=complex），可能导致执行计划选择偏差。全局或会话级别切换回旧版简单成本模型：

-- 全局生效（需写入配置文件永久保留）
SET GLOBAL optimizer_cost_model='simple';
-- 会话级别临时测试
SET SESSION optimizer_cost_model='simple';

2. 同步优化器开关与旧版本 #

对比10.3和10.6的optimizer_switch默认值，将差异项调整为10.3的设置：

-- 查看当前开关配置
SHOW VARIABLES LIKE 'optimizer_switch';
-- 示例：关闭派生表合并（若10.3默认关闭）
SET GLOBAL optimizer_switch='derived_merge=off';

常见差异项包括derived_merge、condition_pushdown、mrr_cost_based等，需根据实际对比结果调整。

3. 更新统计信息 #

升级后表统计信息可能过时，导致优化器做出错误的执行计划选择：

ANALYZE TABLE Users, Customer, Picklist_ER_Typ, Users_Sell_Courses, Sell_Courses, Package_Contracts;

4. 优化索引设计 #

针对Users表创建覆盖过滤与连接条件的联合索引，引导优化器优先选择：

CREATE INDEX IDX_USER_STATUS_SUB_ER_NR ON Users(USER_STATUS, SUB_ER_NR);

该索引同时满足USER_STATUS='Aktiv'的过滤条件和与Customer表的连接条件，比单字段索引更具优先级。

5. 全局强制索引策略（极端场景） #

若上述方案无效，可通过视图封装强制索引逻辑，批量替换应用中对Users表的查询：

CREATE OR REPLACE VIEW Users_optimized AS SELECT * FROM Users FORCE INDEX(IDX_USER_STATUS);

需确保应用查询中替换Users为Users_optimized，适合无法修改大量查询的场景。

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