MySQL 加入时间太长

Question

我需要一些帮助来优化 MySQL 查询或表

当我运行此查询时，它会在 .01 秒内返回 650 条记录:

  select mm, name, display, year
  from tbl d
  where active = 1 and tbl2_id = 'val' and lvl_id = 9
  order by mm;

当我运行此查询时，它会在 15 秒内返回相同的记录:

  select d.mm, d.name, d.display, d.year, a.year year2
  from tbl d left join tbl a on d.mm = a.mm and a.tbl2_id = 'val2'
  where d.active = 1 and d.tbl2_id = 'val' and d.lvl_id = 9
  order by d.mm;

当我这样运行它时，它也需要超过 15 秒:

  select mm, name, display, year, 
      (select a.year from tbl a where a.mm = mm and a.tbl2_id = 'val2') year2
  from tbl 
  where active = 1 and tbl2_id = 'val' and lvl_id = 9
  order by mm;

表中有mm的多条记录。我需要获取 tbl2_id = 'val' 的所有记录，如果 tbl2_id = 'val2' 的那个 mm 有一条记录，我需要 val2 记录中的“year”值。 tbl 目前有 13k 条记录，并且任何给定的 mm 的记录都不超过 10 条，所以我认为这个查询不应该超过 15s。我有索引 mm、active、tbl2_id 和 lvl_id。

我在 MSSQL 中几乎没有延迟地完成了类似的事情。

Answer 1

您可以首先在 (tbl2_id, lvl_id, active) 和 (tbl2_id, mm) 表上引入复合索引。这可能会加快您的所有三个查询。

每当您在 WHERE 子句中使用多个字段时，考虑复合索引是有意义的。如果只存在单列索引，则查询可以仅使用其中一个索引进行搜索，同时不得不求助于较慢的扫描来搜索剩余的子集。 MySQL 和 MSSQL 在这方面的区别可能是 MSSQL 根据数据的基数更好地猜测使用这三个中的哪一个(最好使用的是留下最小子集进行扫描的那个)，尽管如果不详细检查这两个查询计划就很难说。