我重构了我们从另一家公司继承的应用程序的慢速部分,以使用内部联接而不是子查询,例如:
WHERE id IN (SELECT id FROM ...)
重构后的查询运行速度提高了大约 100 倍。 (约 50 秒到约 0.3 秒)我预计会有所改善,但谁能解释为什么它如此激烈? where 子句中使用的列都已编入索引。 SQL 是否每行执行一次 where 子句中的查询?
更新 - 解释结果:
不同之处在于“where id in ()”查询的第二部分 -
2 DEPENDENT SUBQUERY submission_tags ref st_tag_id st_tag_id 4 const 2966 Using where
vs 1 个带有连接的索引行:
SIMPLE s eq_ref PRIMARY PRIMARY 4 newsladder_production.st.submission_id 1 Using index
“相关子查询”(即 where 条件取决于从包含查询的行获得的值的查询)将为每一行执行一次。一个不相关的子查询(其中 where 条件独立于包含的查询)将在开始时执行一次。 SQL 引擎会自动进行这种区分。
但是,是的,解释计划会给你一些肮脏的细节。
您为每一行运行一次子查询,而连接发生在索引上。
EXPLAIN
表示 DEPENDENT SUBQUERY
,这是此行为的最清晰指示。
在每个版本上运行解释计划,它会告诉你原因。
在针对数据集运行查询之前,它们通过查询优化器,优化器尝试以一种可以尽快从结果集中删除尽可能多的元组(行)的方式来组织查询。通常,当您使用子查询(尤其是坏的子查询)时,在外部查询开始运行之前,无法从结果集中删除元组。
如果没有看到查询,很难说原始查询有什么不好,但我的猜测是优化器无法做得更好。运行“解释”将向您展示用于检索数据的优化器方法。
查看每个查询的查询计划。
Where in 和 Join 通常可以使用相同的执行计划来实现,因此通常它们之间的更改速度为零。
优化器做得不是很好。通常它们可以毫无区别地进行转换,优化器可以做到这一点。
这个问题有点笼统,所以这里有一个笼统的答案:
基本上,当 MySQL 有大量行要排序时,查询需要更长的时间。
做这个:
对每个查询(JOIN'ed 查询,然后是 Subqueried 查询)运行 EXPLAIN,然后在此处发布结果。
我认为看到 MySQL 对这些查询的解释的差异对每个人来说都是一次学习经历。
where 子查询必须为每个返回的行运行 1 个查询。内连接只需要运行 1 个查询。
通常它的结果是优化器无法确定子查询可以作为连接执行,在这种情况下,它为表中的每条记录执行子查询,而不是将子查询中的表与您正在查询的表连接起来。一些更“企业”的数据库在这方面做得更好,但有时他们仍然会错过它。
使用子查询,您必须为每个结果重新执行第二个 SELECT,并且每次执行通常返回 1 行。
通过连接,第二个 SELECT 返回更多行,但您只需执行一次。优点是现在您可以加入结果,而加入关系是数据库应该擅长的。例如,也许优化器现在可以发现如何更好地利用索引。
它与其说是子查询不如说是 IN 子句,尽管连接至少是 Oracle 的 SQL 引擎的基础并且运行得非常快。
子查询可能正在执行“全表扫描”。换句话说,不使用索引并返回主查询中的 Where 需要过滤掉的太多行。
当然只是一个没有细节的猜测,但这是常见的情况。
摘自参考手册 (14.2.10.11 Rewriting Subqueries as Joins):
LEFT [OUTER] JOIN 可以比等效的子查询更快,因为服务器可能能够更好地优化它——这一事实并非仅针对 MySQL 服务器。
所以子查询可能比 LEFT [OUTER] JOINS 慢。
不定期副业成功案例分享
DEPENDENT SUBQUERY
与“相关子查询”的含义完全相同。