如何基于EXPLAIN计划优化MySQL查询

Question

从查询EXPLAIN计划来看，如何确定最能在哪里进行优化？

我很欣赏要检查的第一件事是是否使用了良好的索引，但是除此之外，我有些困惑。通过过去的反复试验，我有时发现执行连接的顺序可以很好地改进，但是从执行计划中如何确定呢？

尽管我非常想对如何优化查询有一个很好的一般理解（建议多加赞赏！），但我也意识到，讨论具体案例通常比抽象讨论要容易得多。由于我目前正在用这个把头撞在墙上，因此您的想法将不胜感激：

id select_type表的类型possible_keys键key_len参考行额外
 1 SIMPLE S const PRIMARY，l，p，f4 PRIMARY 2 const 1使用临时
 1 SIMPLE Q ref PRIMARY，SS 2 const 204使用索引
 1 SIMPLE V ref PRIMARY，n，QQ 5 const，db.Q.QID 6使用位置; 使用索引；不同
 1 SIMPLE R1 ref PRIMARY，LL 154 const，db.V.VID 447使用索引; 不同
 1 SIMPLE W eq_ref PRIMARY，w PRIMARY 5 const，db.R.RID，const 1使用位置; 不同
 1 SIMPLE R2 eq_ref PRIMARY，L PRIMARY 156 const，db.W.RID，const 1使用位置; 不同

我对执行计划的最后一行的解释是否正确，如下所示：

• 由于它在主键上完全匹配，因此R2每个输出行只需要获取一行；
• 但是，然后根据适用于R2？的某些条件过滤此类输出行。

如果是这样，我的问题就在于在最后一步中进行的过滤。如果条件导致没有过滤（例如WHERECol_1_to_3IN (1,2,3)），则查询将非常快地运行（〜50ms）；但是，如果条件限制了选择的行（WHERECol_1_to_3IN (1,2)），则查询将花费更长的时间（〜5s）。如果限制为单个匹配（WHERECol_1_to_3IN (1)），则优化程序会建议使用完全不同的执行计划（执行计划略优于5s，但仍比50ms差很多）。似乎没有一个更好的索引可以在该表上使用（假设已经完全使用主键为每个结果返回一行？）。

一个人应该如何解释所有这些信息？我猜对了吗，因为要在要连接的最终表上进行这种输出过滤，所以与早先连接表并更快地过滤此类行相比，浪费了大量的精力？如果是这样，如何确定执行计划中的什么时候R2应该加入？

虽然我拒绝在此处完整地包含查询和架构（因为我真的很可能知道要查找的内容，而不仅仅是被告知答案），但我知道有必要推进讨论：

SELECT DISTINCT
    `Q`.`QID`
FROM
    `S`
    NATURAL JOIN `Q`
    NATURAL JOIN `V`
    NATURAL JOIN `R` AS `R1`
    NATURAL JOIN `W`

    JOIN `R` AS `R2` ON (
            `R2`.`SID` = `S`.`SID`
        AND `R2`.`RID` = `R1`.`RID`
        AND `R2`.`VID` = `S`.`V_id`
        AND `R2`.`Col_1_to_3` IN (1,2) -- this is where performance suffers!
    )

WHERE
    AND `S`.`SID` = @x
    AND `W`.`WID` = @y
;

该表的定义R是：

CREATE TABLE `R` (
  `SID` smallint(6) unsigned NOT NULL,
  `RID` smallint(6) unsigned NOT NULL,
  `VID` varchar(50) NOT NULL DEFAULT '',
  `Col_1_to_3` smallint(1) DEFAULT NULL,
  `T` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`SID`,`RID`,`VID`),
  KEY `L` (`SID`,`VID`,`Col_1_to_3`),
  CONSTRAINT `R_f1` FOREIGN KEY (`SID`) REFERENCES `S` (`SID`),
  CONSTRAINT `R_f2` FOREIGN KEY (`SID`, `VID`) REFERENCES `V` (`SID`, `VID`),
  CONSTRAINT `R_f3` FOREIGN KEY (`SID`, `VID`, `Col_1_to_3`) REFERENCES `L` (`SID`, `VID`, `LID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

取决于您要查询的内容和查询的内容。

通常，对于EXPLAIN中每个具有的行Using where，您都需要使用索引（possible keys和keys列）来使它。这些是您的过滤器，包括WHERE和ON。说得Using index更好。这意味着有一个覆盖索引，MySQL可以直接从索引中检索数据，而不必访问表数据中的行。

Using where应该查看没有的行，它返回大量行。这些是表中所有行的返回值。我不知道您的查询是什么，所以我不知道是否在这里被提醒。尝试过滤结果集以减小大小并提高性能。

通常，您应该尽量避免看到Using filesort或Using temporary，尽管只有在不期望它们的情况下它们才是不好的。

Filesort通常与ORDER子句一起出现。通常，您希望MySQL使用覆盖索引（Using index），以便已按顺序从服务器返回行。如果不是，则MySQL必须在以后使用文件排序对其进行排序。

Using temporary当它引用派生表时可能会很糟糕，因为它们没有索引。看来您已经用索引明确创建了一个临时表，所以在这里还不错。有时，您唯一的选择是使用派生表，因此Using temporary。