Q如何根据解释计划优化MySQL查询

查看查询的EXPLAIN计划,如何确定最佳化的位置?

我很感激首先要检查的事情之一是是否使用了好的索引,但除此之外,我还有些困惑。通过过去的试验和错误,我有时发现连接的顺序可以是一个很好的改进来源,但是如何才能从执行计划中确定它呢?

虽然我很想对如何优化查询有一个很好的一般理解(建议阅读,非常感谢!)我也意识到,具体的案例比抽象的讨论更容易。因为我现在正用这张照片把我的头撞在墙上,所以非常感谢您的想法:

id select_type table type possible_keys key_len ref rows额外1个简单的s const primary,l,p,f4 primary 2 const 1使用临时1个简单的q ref primary,s s 2 const 204,使用索引1个简单的v ref primary,n,q q 5 const,db.q.qid 6,使用where;使用index;distinct1个简单的r1 ref primary,l l 154 const,db.v.vid 447,使用索引;不同的1个简单的w eq_ref primary,w primary 5 const,db.r.rid,const 1,使用where;distinct1个简单的r2 eq_ref primary,l primary 156 const,db.w.rid,const 1,使用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的定义是:

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
2012-04-13 22:37:43  eggyal

A回答

  • 1

    这取决于您的目的和查询是什么。

    通常,explain中每一行有一个Using where,您需要使用一个索引(possible keyskeys列)。这些是你的过滤器,包括在哪里和在哪里。让它说Using index更好。这意味着有一个覆盖索引,mysql可以从索引中检索数据,而不是访问表数据中的行。

    不存在Using where,并且返回大量行的行应该被查看。这些是表中所有行的返回值。我不知道你的问题是什么,所以我不知道在这里是否应该惊慌。尝试筛选结果集以减小大小并提高性能。

    一般来说,你应该尽量避免看到Using filesortUsing temporary,尽管只有在你没有预料到的情况下,这些才是不好的。

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

    Using temporary引用派生表时可能不正确,因为它们没有索引。似乎您已经显式地创建了一个带有索引的临时表,所以在这里,它还不错。有时,您只能选择使用派生表,因此Using temporary

    2012-04-13 22:57:33  Marcus Adams