mysql - SQL 查询优化——真的没有什么可以改进的了吗？

Question

我有以下查询。我从 mysql 慢查询日志中挑选出来的:

SELECT AVG(item.duration) AS dur 
FROM `item` 
INNER JOIN item_step ON item_step.item_id = item.id 
WHERE
item_step.number = '2' AND 
(IS_OK(item_step.result) OR item_step.result2 IN ("R1", "R2")) AND 
item.time >= '2015-03-01 07:00:00' AND 
item.time < '2015-05-01 07:00:00';

像往常一样，我尝试使用解释来检查它:

+----+-------------+-----------+------+----------------------------+---------+---------+------------------+--------+----------+-------------+
| id | select_type | table     | type | possible_keys              | key     | key_len | ref              | rows   | filtered | Extra       |
+----+-------------+-----------+------+----------------------------+---------+---------+------------------+--------+----------+-------------+
|  1 | SIMPLE      | item      | ALL  | PRIMARY,time               | NULL    | NULL    | NULL             | 790464 |    38.74 | Using where |
|  1 | SIMPLE      | item_step | ref  | number,item_id,result2_idx | item_id | 4       | debug_db.item.id |      1 |   100.00 | Using where |
+----+-------------+-----------+------+----------------------------+---------+---------+------------------+--------+----------+-------------+

在 id 和 time 上向表 item 添加索引没有任何结果。实际上 time 列有一个索引，表使用外键连接并有一个索引..

我不知道在这里做什么。真的不可能优化此查询以避免使用 join_type = ALL 吗？

Answer 1

由于您似乎已经拥有从 item_step.item_id 到 item.item_id 的 FK，因此您唯一可以改进的选择是关注用于过滤的部分出记录。

稍微重新格式化您的查询，我们有:

SELECT AVG(item.duration) AS dur 
  FROM `item` 
  INNER JOIN item_step 
     ON item_step.item_id = item.id 
    AND item_step.number = '2' 
    AND (IS_OK(item_step.result) OR item_step.result2 IN ("R1", "R2"))
  WHERE item.time >= '2015-03-01 07:00:00'
    AND item.time < '2015-05-01 07:00:00';

• 首先要注意的是 IS_OK(item_step.result)。我不知道这个函数背后是什么，但我很确定它会阻止优化器有效地使用该字段的任何索引。如果公式可以直接写在查询中，我建议这样做。 (例如 IN (1, 4, 9) 或 IN (SELECT OK FROM result_values) 等...)

• 根据字段名称，我假设我们首先要将 item_id 列表减少到最小值，然后使用该减少的列表处理 item_step 表。为此，您首先需要在 time 字段上建立索引。我假设 item_id 字段自动包含在索引中，因为它是 PK 字段，但我不是 MySQL 专家，它也可能取决于您的存储引擎。无论如何，在 MSSQL 中它就是这样工作的，YMMV。

• 然后要做的第二件事是将此 item_id 列表转到 item_step 表并减少那里的记录数。为此，您需要 item_id, number, result2, result 的复合索引。如果您设法将 IS_OK() 函数“内联”写入查询中，您可能想尝试交换最后两个字段……您需要测试的内容。

根据我在这里和那里阅读的内容，MySQL 不支持像 MSSQL 那样的索引上的 INCLUDE 之类的东西。一种解决方法是在 item 上的 time, duration 上创建一个“覆盖”索引。这样，一切都可以直接从索引完成，但在向 item 表添加数据时需要更多的磁盘空间和 CPU 要求。

简而言之:

• 在 time, duration 的 item 上添加索引
• 在 item_id、number、result2、result 的 item_step 上添加索引
• 看看您是否可以内联 IS_OK() 函数。