MariaDB中SELECT与JOIN索引使用不一致引发性能差异排查

• 更新表统计信息
  本地同步数据后，表统计信息可能过时，导致优化器错误判断索引成本。执行以下命令更新统计信息：

  ANALYZE TABLE title_list;
  

  执行后重新用EXPLAIN查看查询计划，确认是否使用title索引。

• 对比并对齐配置参数
  生产与本地的MariaDB配置差异是常见原因，重点检查以下参数：

  • 连接/排序缓冲：join_buffer_size、sort_buffer_size
  • 优化器开关：optimizer_switch
  • 查询缓存：query_cache_type（若生产开启，本地也需保持一致）
    分别在生产和本地执行SHOW VARIABLES LIKE '参数名';获取配置，将本地配置调整为与生产完全一致后重启服务。

• 验证字符集与排序规则一致性
  字符集或排序规则不匹配会导致索引无法被正确识别。执行SHOW CREATE TABLE title_list;对比生产与本地的表定义，确保CHARSET和COLLATE参数完全相同。若不一致，ALTER表调整：

  ALTER TABLE title_list CONVERT TO CHARACTER SET [生产字符集] COLLATE [生产排序规则];
  

• 核对索引定义细节
  即使同步了表结构，索引可能存在细微差异（如字段顺序、排序方向）。执行SHOW INDEX FROM title_list;对比两边的索引详情，确保title索引的字段、类型、排序方向完全一致。若有差异，删除本地索引后重新创建：

  DROP INDEX idx_title ON title_list;
  CREATE INDEX idx_title ON title_list(title [排序方向]);
  

• 排查查询语句的隐式问题
  确认本地执行的查询与生产完全一致：

  • 检查字段名大小写（Linux系统大小写敏感，Windows不敏感，若生产是Linux，本地查询需保持字段名大小写一致）
  • 避免隐式类型转换：比如title字段为字符串类型，查询时不要用数字值匹配（如WHERE title = 123会导致索引失效）

• 通过优化器追踪定位决策原因
  开启优化器追踪，查看优化器选择全表扫描的具体原因：

  SET optimizer_trace="enabled=on";
  -- 执行你的目标查询
  SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
  SET optimizer_trace="enabled=off";
  

  重点分析cost_evaluation部分的成本计算数据，对比生产环境的追踪结果，找到优化器决策差异的根源。

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