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

Question

查看查询的 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

Am I correct in interpreting the final row of the execution plan as follows:

• as it is fully matched on its primary key, only one row of R2 need be fetched per output row;
• however, such output rows are then filtered based on some criteria that applies to R2?

If so, my problem lies in the filtering which occurs in that final step. If the condition results in no filtering (e.g. WHERE `Col_1_to_3` IN (1,2,3)), the query runs extremely quickly (~50ms); however, if the condition restricts the rows selected (WHERE `Col_1_to_3` IN (1,2)), the query takes considerably longer (~5s). If the restriction is to a single match (WHERE `Col_1_to_3` IN (1)), the optimiser suggests an altogether different execution plan (which performs marginally better than 5s, but still a lot worse than 50ms). It doesn't seem as though there's a better index that can be used on that table (given it's already fully using the primary key to return one row per result?).

How should one interpret all this information? Am I right in guessing that, because such output filtering is taking place on the final table to be joined, considerable effort is wasted versus joining the table earlier and filtering such rows sooner? If so, how does one determine when in the execution plan R2 ought to be joined?

Whilst I resisted including the query & schema in full here (as I would really likely to know what to look for, not merely be told the answer), I understand it's necessary to advance the discussion:

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 且返回大量行的行。这些是表中所有行的返回值。我不知道你的查询是什么，所以我不知道是否要在这里 panic 。尝试过滤结果集以减小大小并提高性能。

您通常应该尽量避免看到 Using filesort 或 Using temporary，尽管只有在您不希望看到它们时它们才不好。

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

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