如何优化基于解释计划

Question

的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)），则查询需要相当长的时间（〜5秒）。如果限制为单一匹配（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的定义是：

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必须使用filesort命令它们。

Using temporary由于没有索引而引用派生表时可能会很糟糕。看起来你已经明确创建了一个带有索引的临时表，所以在这里，这并不坏。有时，你唯一的选择是使用派生表，因此Using temporary。