MariaDB 10.4两张相似表查询性能差异：从秒级到数小时

问题描述 #

三张核心表结构如下（已匿名处理）：

1. 主测量数据表

CREATE TABLE `measurement_data` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `item_id` varchar(64) NOT NULL COMMENT 'Item identifier',
  `measurement_value` float DEFAULT NULL,
  `measurement_timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  -- 其他列...
  PRIMARY KEY (`id`),
  KEY `idx_item_id` (`item_id`),
  KEY `idx_timestamp` (`measurement_timestamp`),
  KEY `idx_item_id_timestamp` (`item_id`,`measurement_timestamp`)
) ENGINE=InnoDB;

2. 有效条目参考表

CREATE TABLE `valid_items` (
  `ITEM_ID` varchar(20) CHARACTER SET utf8 NOT NULL,
  `ITEM_CODE` varchar(20) CHARACTER SET utf8 NOT NULL,
  `CLASSIFICATION` char(2) DEFAULT NULL,
  -- 其他列...
  PRIMARY KEY (`ITEM_ID`),
  KEY `idx_item_code` (`ITEM_CODE`)
) ENGINE=InnoDB;

3. 历史测量数据表

CREATE TABLE `legacy_measurements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `barcode` varchar(64) NOT NULL DEFAULT '',
  `value` float DEFAULT NULL,
  `record_time` datetime DEFAULT NULL,
  -- 其他列...
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_barcode` (`barcode`),
  KEY `idx_record_time` (`record_time`)
) ENGINE=InnoDB;

查询语句与执行计划 #

慢查询（耗时数分钟甚至无法完成） #

SELECT m.item_id
FROM measurement_data m
WHERE m.item_id NOT IN (SELECT v.ITEM_ID FROM valid_items v)
AND m.measurement_timestamp > '2020-01-01'
LIMIT 1000;

EXPLAIN输出：

快查询（耗时约50秒完成） #

SELECT l.barcode AS ITEM_ID
FROM legacy_measurements l
WHERE l.barcode NOT IN (SELECT v.ITEM_ID FROM valid_items v)
AND l.record_time > '2020-01-01'
AND l.category_field REGEXP '^([0-9]{7}|CA[^a-zA-Z0-9]*[0-9]{7}|CA[0-9]{7})$'
LIMIT 1000;

EXPLAIN输出：

关键观察 #

• 两个查询目标一致：找出测量表中存在但参考表中不存在的条目
• 子查询逻辑完全相同
• 快查询对时间戳索引执行范围扫描，子查询用主键做等值匹配，仅扫描1行
• 慢查询执行全索引扫描，子查询扫描百万级行数，效率极低
• 核心差异：慢查询源表item_id存在重复，仅普通BTREE索引；快查询源表barcode有唯一索引
• varchar长度差异暂不认为是性能瓶颈

核心疑问 #

1. 为何两个相似查询性能差异如此巨大？
2. 重写慢查询的最有效方式是什么？
3. MariaDB 10.4有哪些特有优化手段可利用？
4. 是否需要添加特定索引？如果是，应该加哪些？

已尝试的优化手段 #

• 执行ANALYZE TABLE更新统计信息
• 用LEFT JOIN ... IS NULL替代NOT IN
• 用NOT EXISTS替代NOT IN
  以上操作均未改善性能，EXPLAIN结果无变化。

优化解答 #

1. 性能差异根源 #

• 索引使用逻辑差异：快查询中legacy_measurements的record_time索引被用于范围扫描，快速过滤出符合时间条件的行；而慢查询中measurement_data选择了idx_item_id_timestamp索引做全扫描，没有利用idx_timestamp的范围扫描，导致需要遍历更多数据。
• 子查询执行方式差异：快查询的子查询是eq_ref类型，利用valid_items的主键做等值查找，每次仅查1行；慢查询的子查询是index类型，遍历idx_item_code全索引来匹配item_id，相当于每次子查询都扫一遍参考表，重复执行百万次后性能雪崩。
• 数据唯一性影响：legacy_measurements的barcode是唯一索引，数据库可以更高效地去重和匹配；而measurement_data的item_id重复多，数据库无法提前过滤重复值，导致无效的子查询执行次数暴增。

2. 慢查询重写方案 #

推荐优先使用**NOT EXISTS结合覆盖索引**，或者先去重再做匹配：

方案一：先去重再过滤

SELECT DISTINCT m.item_id
FROM measurement_data m
WHERE m.measurement_timestamp > '2020-01-01'
AND NOT EXISTS (
    SELECT 1 FROM valid_items v
    WHERE v.ITEM_ID = m.item_id
)
LIMIT 1000;

先通过DISTINCT减少item_id的重复数量，降低子查询的执行次数。

方案二：用临时表存储候选item_id

CREATE TEMPORARY TABLE temp_candidates (
    item_id varchar(64) NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO temp_candidates
SELECT DISTINCT item_id
FROM measurement_data
WHERE measurement_timestamp > '2020-01-01';

SELECT item_id
FROM temp_candidates tc
WHERE NOT EXISTS (
    SELECT 1 FROM valid_items v
    WHERE v.ITEM_ID = tc.item_id
)
LIMIT 1000;

DROP TEMPORARY TABLE temp_candidates;

临时表存储去重后的候选ID，再与参考表做匹配，避免重复扫描主表。

3. MariaDB 10.4特有优化手段 #

• 优化器开关调整：开启optimizer_switch='semijoin=on,materialization=on'，让优化器自动选择半连接或物化子查询，避免相关子查询的重复执行。
• 窗口函数去重：利用ROW_NUMBER()窗口函数提前过滤重复的item_id，减少后续匹配的数据量：

WITH unique_items AS (
    SELECT item_id, measurement_timestamp
    FROM (
        SELECT 
            item_id, 
            measurement_timestamp,
            ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY measurement_timestamp DESC) AS rn
        FROM measurement_data
        WHERE measurement_timestamp > '2020-01-01'
    ) t
    WHERE rn = 1
)
SELECT item_id
FROM unique_items ui
WHERE NOT EXISTS (
    SELECT 1 FROM valid_items v
    WHERE v.ITEM_ID = ui.item_id
)
LIMIT 1000;

• 并行查询：MariaDB 10.4支持并行查询，可通过SET max_parallel_workers_per_gather = 4;开启，适合大表扫描场景。

4. 索引优化建议 #

• 调整主表索引顺序：将idx_item_id_timestamp改为(measurement_timestamp, item_id)，这样可以先通过时间范围快速过滤，再获取item_id，避免全索引扫描：

DROP INDEX idx_item_id_timestamp ON measurement_data;
CREATE INDEX idx_timestamp_item_id ON measurement_data (measurement_timestamp, item_id);

这个索引是覆盖索引，查询时无需回表，同时能利用时间条件做范围扫描。

• 参考表字符集对齐：valid_items的ITEM_ID是utf8字符集，measurement_data的item_id默认可能是utf8mb4，字符集不一致会导致索引无法匹配，需统一字符集：

ALTER TABLE measurement_data MODIFY COLUMN item_id varchar(64) CHARACTER SET utf8 NOT NULL;

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