MariaDB 10.6升级至11.4后查询未用索引，求配置级解决方案

问题描述 #

将MariaDB从10.6版本升级至11.4版本后，部分查询的优化器不再使用预期索引，导致性能大幅下降。例如以下UPDATE查询：

UPDATE empty_timetable_entries ete
SET last_update_date = NOW()
WHERE EXISTS(SELECT 1 AS a
     FROM tcs__slice_queue_filled_gaps tsqfg 
              JOIN temp__queue_list tql
                   ON tql.guid = tsqfg.queue_uid
     WHERE ete.departure_station_id = tsqfg.crawler_station_code_from_id
       AND ete.arrival_station_id = tsqfg.crawler_station_code_to_id
       AND ete.departure_date = tql.departure_date);

该查询在11.4中运行极慢，原因是优化器未使用tcs__slice_queue_filled_gaps表上的idx_tcs_crawler_station_from_to索引，而是选择全表扫描。

临时解决方案 #

1. 使用FORCE INDEX强制指定索引 #

通过FORCE INDEX指令强制优化器使用目标索引，可立即解决性能问题：

UPDATE empty_timetable_entries ete
SET last_update_date = NOW()
WHERE EXISTS(SELECT 1 AS a
     FROM tcs__slice_queue_filled_gaps tsqfg FORCE INDEX (idx_tcs_crawler_station_from_to)
              JOIN temp__queue_list tql
                   ON tql.guid = tsqfg.queue_uid
     WHERE ete.departure_station_id = tsqfg.crawler_station_code_from_id
       AND ete.arrival_station_id = tsqfg.crawler_station_code_to_id
       AND ete.departure_date = tql.departure_date); 

2. 重构查询语句 #

将EXISTS子查询改为JOIN写法，优化器会自动选择正确索引：

UPDATE empty_timetable_entries ete
    JOIN tcs__slice_queue_filled_gaps tsqfg
      ON ete.departure_station_id = tsqfg.crawler_station_code_from_id
     AND ete.arrival_station_id = tsqfg.crawler_station_code_to_id
    JOIN temp__queue_list tql
      ON tql.guid = tsqfg.queue_uid
     AND ete.departure_date = tql.departure_date
    SET ete.last_update_date = NOW();   

配置调整方案（使11.4索引逻辑与10.6对齐） #

由于系统中存在大量此类查询，重构SQL成本较高，可通过以下配置调整让11.4的优化器行为与10.6保持一致：

• 调整优化器开关
  关闭11.x新增的部分优化特性，回退到10.6的子查询处理逻辑：

  -- 会话级测试，验证生效后再设置全局
  SET optimizer_switch='semijoin=off,batched_key_access=off';
  -- 全局设置（需重启或动态生效）
  SET GLOBAL optimizer_switch='semijoin=off,batched_key_access=off';
  

• 使用旧版成本计算模型
  MariaDB 11.x更新了成本评估模型，切换为legacy模式可匹配10.6的判断逻辑：

  SET GLOBAL optimizer_cost_model='legacy';
  

• 更新表统计信息
  升级后统计信息可能过时，导致优化器误判，执行统计信息更新：

  ANALYZE TABLE tcs__slice_queue_filled_gaps, empty_timetable_entries, temp__queue_list;
  

• 强制索引评估
  修改参数让优化器优先评估索引的使用价值：

  SET GLOBAL eq_range_index_dive_limit=0;
  

注意：所有全局配置建议先在会话级别测试验证，确认对业务无负面影响后再全局生效，部分参数需重启MariaDB才能永久生效。

相关表结构 #

CREATE TABLE `tcs__slice_queue_filled_gaps` (
  `guid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '全局唯一标识符',
  `slice_uid` bigint(20) NOT NULL DEFAULT 0 COMMENT '全局唯一切片标识符',
  `queue_uid` bigint(20) NOT NULL DEFAULT 0 COMMENT '全局唯一文件标识符',
  `slice_md5checksum` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT md5(concat(rand(),rand(),rand())) COMMENT '切片MD5校验值',
  `file_md5checksum` varchar(32) NOT NULL DEFAULT md5(concat(rand(),rand(),rand())) COMMENT '文件MD5校验值',
  `load_date` date NOT NULL DEFAULT '1970-01-01',
  `route` varchar(256) NOT NULL DEFAULT '',
  `crawler_id` varchar(256) DEFAULT '',
  `plugin_type` varchar(256) NOT NULL DEFAULT '',
  `from_load_date_day_offset` int(11) NOT NULL DEFAULT 0,
  `date_depth` int(11) NOT NULL DEFAULT 0,
  `min_departure_date` date NOT NULL DEFAULT '1970-01-01',
  `max_departure_date` date NOT NULL DEFAULT '1970-01-01',
  `carrier_id` int(11) DEFAULT 0,
  `coach_class_id` int(11) DEFAULT 0,
  `crawler_station_code_from_id` int(11) DEFAULT 0,
  `crawler_station_code_to_id` int(11) DEFAULT 0,
  `train_brand_id` int(11) DEFAULT 0,
  `train_class_id` int(11) DEFAULT 0,
  `train_id` int(11) DEFAULT 0,
  `fare_code_id` int(11) DEFAULT 0,
  `change_stations` varchar(512) DEFAULT '0',
  PRIMARY KEY (`guid`),
  UNIQUE KEY `UK__slice_uid__queue_uid` (`slice_uid`,`queue_uid`),
  KEY `KEY__file_md5checksum` (`file_md5checksum`),
  KEY `KEY__slice_md5checksum` (`slice_md5checksum`),
  KEY `idx_tcs_queue_uid` (`queue_uid`),
  KEY `idx_tcs_crawler_station_from_to` (`crawler_station_code_from_id`,`crawler_station_code_to_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `empty_timetable_entries` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `departure_station_id` int(10) NOT NULL,
  `arrival_station_id` int(10) NOT NULL,
  `departure_date` date NOT NULL,
  `is_sold_out` tinyint(3) NOT NULL DEFAULT 1,
  `last_update_date` datetime DEFAULT current_timestamp(),
  `creation_date` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `dep_arr_id_dep_date_index` (`departure_station_id`,`arrival_station_id`,`departure_date`),
  KEY `dep_date_index` (`departure_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3180529 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `temp__queue_list` (
  `guid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '全局唯一标识符',
  `load_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '加载时间',
  `modification_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `slice_md5checksum` varchar(32) NOT NULL DEFAULT md5('Legacy') COMMENT '切片MD5校验值',
  `file_md5checksum` varchar(32) NOT NULL DEFAULT md5(concat(rand(),rand(),rand())) COMMENT 'MD5校验值 - 插入值',
  `departure_date` date NOT NULL DEFAULT '1970-01-01',
  `code_from` varchar(512) NOT NULL DEFAULT '',
  `code_to` varchar(512) NOT NULL DEFAULT '',
  `coach_class` varchar(256) NOT NULL DEFAULT '',
  `train_brand_code` varchar(100) DEFAULT NULL,
  `train_class_code` varchar(256) DEFAULT NULL,
  `train_number` varchar(50) DEFAULT NULL,
  `departure_time_offset_sec` int(11) NOT NULL DEFAULT 0,
  `duration_time_offset_sec` int(11) NOT NULL DEFAULT 0,
  `price_value` decimal(20,6) NOT NULL DEFAULT 0.000000,
  `currency_code` varchar(4) NOT NULL DEFAULT '',
  `fare_code` varchar(255) NOT NULL DEFAULT '',
  `change_stations` varchar(512) NOT NULL DEFAULT 'NULL',
  `processed_code` tinyint(4) NOT NULL DEFAULT 100 COMMENT '队列数据处理状态码',
  `error_code` tinyint(4) NOT NULL DEFAULT 100 COMMENT '错误码',
  `carrier_code` varchar(20) DEFAULT 'NULL',
  PRIMARY KEY (`guid`)
) ENGINE=MEMORY AUTO_INCREMENT=292634985 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

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