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

查看查询的EXPLAIN计划中，如何确定哪些地方可以进行最佳优化？

我很欣赏首先要检查的事情之一是是否使用了好的索引，但除此之外我有点困惑。通过过去的尝试和错误，我有时发现执行连接的顺序可以是一个很好的改进来源，但是如何通过查看执行计划来确定这一点呢？

虽然我非常希望对如何优化查询有一个良好的总体了解（建议阅读，非常感谢！），但我也意识到讨论具体案例通常比抽象讨论更容易。由于我目前正在用这个把头撞在墙上，因此非常感谢您的想法：

id   select_type   table   type     possible_keys    key       key_len   ref                    rows   Extra
 1   SIMPLE        S       const    PRIMARY,l,p,f4   PRIMARY         2   const                     1   Using temporary
 1   SIMPLE        Q       ref      PRIMARY,S        S               2   const                   204   Using index
 1   SIMPLE        V       ref      PRIMARY,n,Q      Q               5   const,db.Q.QID            6   Using where; Using index; Distinct
 1   SIMPLE        R1      ref      PRIMARY,L        L             154   const,db.V.VID          447   Using index; Distinct
 1   SIMPLE        W       eq_ref   PRIMARY,w        PRIMARY         5   const,db.R.RID,const      1   Using where; Distinct
 1   SIMPLE        R2      eq_ref   PRIMARY,L        PRIMARY       156   const,db.W.RID,const      1   Using where; Distinct
  

我对执行计划最后一行的解释是否正确：

• 因为它在主键上完全匹配，所以只有一行R2需要按输出行获取；
• 但是，此类输出行随后会根据适用于的某些条件进行过滤R2?

如果是这样，我的问题在于最后一步中发生的过滤。如果条件导致不进行过滤（例如WHERE `Col_1_to_3` IN (1,2,3)），查询运行速度极快（~50ms）；但是，如果条件限制所选行（WHERE `Col_1_to_3` IN (1,2)），查询需要相当长的时间（~5s）。如果限制是单个匹配 (WHERE `Col_1_to_3` IN (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 is:

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

取决于您要做什么以及查询是什么。

一般来说，对于 EXPLAIN 中的每一行都有Using where，您需要使用索引（possible keys and keys柱子）。这些是您的过滤器，包括 WHERE 和 ON。话虽这么说Using index甚至更好。这意味着存在覆盖索引，MySQL 可以直接从索引检索数据，而不是访问表数据中的行。

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

您通常应该尽量避免看到Using filesort or Using temporary，尽管只有在你没有预料到的情况下这些才是糟糕的。

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

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