MariaDB 10.3半连接在大表与IN查询场景下性能极慢问题求助

1. 对比执行计划，定位核心差异 #

分别在两个数据库上执行EXPLAIN SELECT ... IN (1000个值)，重点关注以下字段：

• type：检查MariaDB是否走了ALL全表扫描，而MySQL 5.5用了range或ref这类高效索引扫描类型
• key：确认MariaDB是否命中了预期的索引，若未命中，大概率是优化器选择了错误的执行计划

2. 更新表统计信息 #

MariaDB的查询优化器依赖准确的表统计信息，过时的统计信息会导致执行计划偏差：

ANALYZE TABLE your_large_table;

更新完成后重新执行IN查询，观察性能是否改善

3. 调整IN查询相关配置参数 #

MariaDB 10.3部分默认参数与MySQL 5.5不同，可针对性调整：

• 开启in_to_exists优化，让优化器将长IN列表转换为EXISTS子查询：

  SET optimizer_switch='in_to_exists=on';
  -- 全局生效需执行：SET GLOBAL optimizer_switch='in_to_exists=on';
  

• 调大临时表阈值，避免内存临时表转磁盘临时表：

  SET GLOBAL tmp_table_size=256M;
  SET GLOBAL max_heap_table_size=256M;
  

4. 改写IN查询为JOIN形式 #

长IN列表容易触发优化器瓶颈，改用临时表JOIN的方式更稳定：

-- 创建内存临时表存储1000个值
CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO temp_ids VALUES (1),(2),...,(1000); -- 批量插入所有IN值

-- 通过JOIN替代IN查询
SELECT t.* FROM your_large_table t JOIN temp_ids ti ON t.id=ti.id;

5. 对齐存储引擎配置 #

确认两个数据库的表存储引擎一致（均为InnoDB），并调整MariaDB的InnoDB参数：

• 调大innodb_buffer_pool_size至服务器内存的50%-70%，减少磁盘IO：

  SET GLOBAL innodb_buffer_pool_size=8G; -- 示例值，根据实际内存调整
  

• 对比innodb_flush_log_at_trx_commit等IO相关参数，与MySQL 5.5配置对齐，避免不必要的性能损耗

6. 修复版本特性或bug #

MariaDB 10.3的新优化可能存在特定场景的问题：

• 升级到MariaDB 10.3的最新小版本，修复已知的查询优化器bug
• 若无法升级，可临时禁用可能导致问题的新特性，比如：

  SET optimizer_switch='semijoin=off';
  

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