查看查询的EXPLAIN
计划,如何确定在哪里可以最好地进行优化?
我很欣赏首先要检查的一件事是是否使用了好的索引,但除此之外,我有点困惑。 通过过去的反复试验,我有时发现执行连接的顺序可能是一个很好的改进来源,但是如何通过查看执行计划来确定这一点呢?
虽然我非常想对如何优化查询有一个很好的总体了解(建议阅读非常感谢!),但我也意识到讨论具体案例通常比抽象讨论更容易。 由于我目前正在用这个用头撞墙,因此您的想法将不胜感激:
ID select_type表类型 possible_keys 键key_len引用行 额外 1 简单 S 常量 初级,l,p,f4 主要 2 常量 1 使用临时 1 简单 Q 参考 初级,S S 2 常量 204 使用索引 1 简单 V 参考初级,n,Q Q 5 常量,db.Q.QID 6 使用地点;使用索引;不同 1 简单 R1 参考初级,L L 154 常量,db.V.VID 447 使用索引;不同 1 个简单 W eq_ref初级,W 小学 5 常量,db。R.RID,常量 1 使用位置;不同 1 个简单的 R2 eq_ref初级,L 初级 156 常量,DB。W.RID,常量 1 使用位置;不同
我是否正确地将执行计划的最后一行解释如下:
- 由于它在其主键上完全匹配,因此每个输出行只需要获取一行
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
取决于你要做什么以及查询是什么。
通常,对于 EXPLAIN 中每行都有Using where
,您需要使用索引(possible keys
和 keys
列)来获取它。这些是您的筛选器,包括 WHERE 和 ON。说Using index
更好。这意味着有一个覆盖索引,MySQL可以直接从索引中检索数据,而不是访问表数据中的行。
没有Using where
的行,并且它返回了大量的行,应该查看。这些是表中所有行的返回值。我不知道你的查询是什么,所以我不知道是否应该在这里惊慌。尝试筛选结果集以减小大小并提高性能。
您通常应该尽量避免看到Using filesort
或Using temporary
,尽管这些只有在您不期望它们时才是坏事。
文件排序通常与 ORDER 子句一起出现。您通常希望MySQL使用覆盖索引(Using index
),以便从服务器按顺序返回行。如果不是,那么MySQL必须在之后使用filesort对它们进行排序。
Using temporary
引用派生表时可能很糟糕,因为它们没有索引。您似乎已经显式创建了一个带有索引的临时表,所以在这里,它还不错。有时,您唯一的选择是使用派生表,因此Using temporary
.