原始sql:
select * from A
join B on A.aid = b.aid
...(some join)
limit 0,1000
它将在 1 秒内返回结果, 但是当使用子查询时,它变得非常慢(以分钟为单位):
select * from
(
select * from A
join B on A.aid = b.aid
...(some join)
) tmp
limit 0,1000
当我使用 explain 执行时,它有一行额外的行:
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: null
key_len: null
rows: 1504940
Extra: Using temporary; Using filesort
MySQL 版本:5.6
附言。我需要以子查询方式使用查询,因为它发生在另一个应用程序读取的视图中。
第一种方法是连接,速度要快得多。在第二秒内,将为每一行执行查询。不过,某些数据库将嵌套查询优化为联接。
联接与子查询
文章 MySQL 性能:内部联接与子选择
我发现在我的表上使用"虚拟表"而不是 ROW 子查询要快得多。行子查询似乎没有优化,其中"虚拟表"上的连接已优化。
以下是出于教育目的返回的查询和"解释"。
-- 使用 ROW 子查询进行查询
EXPLAIN
SELECT
*
FROM
region
WHERE
ROW (PDB,CHAIN) IN (
SELECT
region.PDB,
region.CHAIN
FROM
region LEFT JOIN split_domain USING (SUNID)
WHERE
split_domain.SUNID IS NULL
GROUP BY
PDB, CHAIN
HAVING
COUNT(*)>1
)
LIMIT
10
;
+----+--------------------+--------------+--------+---------------+---------+---------+------------------------+-------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+--------+---------------+---------+---------+------------------------+-------+--------------------------------------+
| 1 | PRIMARY | region | ALL | NULL | NULL | NULL | NULL | 57362 | Using where |
| 2 | DEPENDENT SUBQUERY | region | ALL | NULL | NULL | NULL | NULL | 57362 | Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | split_domain | eq_ref | PRIMARY | PRIMARY | 3 | scop_1_65.region.SUNID | 1 | Using where; Using index; Not exists |
+----+--------------------+--------------+--------+---------------+---------+---------+------------------------+-------+--------------------------------------+
3 rows in set (0.04 sec)
我无法从上述内容中获得任何结果(需要太长时间) - 也许限制条款没有发挥作用?
-- 使用联接的虚拟表进行查询
EXPLAIN
SELECT
*
FROM
region
INNER JOIN (
SELECT
region.PDB,
region.CHAIN
FROM
region LEFT JOIN split_domain USING (SUNID)
WHERE
split_domain.SUNID IS NULL
GROUP BY
PDB, CHAIN
HAVING
COUNT(*)>1
) AS x
ON
region.PDB = x.PDB
AND
region.CHAIN = x.CHAIN
LIMIT
10
;
+----+-------------+--------------+--------+---------------------+-----------+---------+------------------------+-------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+---------------------+-----------+---------+------------------------+-------+--------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8624 | |
| 1 | PRIMARY | region | ref | PDB,CHAIN,pdb_chain | pdb_chain | 5 | x.PDB,x.CHAIN | 1 | |
| 2 | DERIVED | region | ALL | NULL | NULL | NULL | NULL | 57362 | Using temporary; Using filesort |
| 2 | DERIVED | split_domain | eq_ref | PRIMARY | PRIMARY | 3 | scop_1_65.region.SUNID | 1 | Using where; Using index; Not exists |
+----+-------------+--------------+--------+---------------------+-----------+---------+------------------------+-------+--------------------------------------+
4 rows in set (1.02 sec)
以上返回... 大约 1 秒内 10 个结果 大约 1 秒内 100 个结果 大约 1.5 秒内获得 1000 个结果 约2秒内完成全套(20437)
前一个查询不会在 5 分钟内返回(即使限制为 10)。
我希望这对任何设计(或尝试优化)复杂子查询的人都很有用,并且数据的精确细节对于传达此处呈现的结果不是必需的。
IN ( SELECT ... )
因优化不佳而臭名昭著。 在最近的版本中,它正在变得更好。
FROM ( SELECT ... )
JOIN ( SELECT ... ) ON ...
曾经的性能很糟糕,因为它没有ON
索引。 在 5.6 中,优化器挠头并决定哪个索引可能是好的,然后创建这样的索引(参见EXPLAIN
中的"自动键")来提供帮助。
用SELECT ( ... ) LIMIT ...
包装查询(如示例中所示)需要完成内部查询,并生成一个可能很大的(1504940 行?)临时表。 所有这些都是在剥离 1000 行之前完成的。 当然,优化器可能能够识别该模式,但我认为"既然外部SELECT
没有添加任何有用的东西,为什么要这样做? 那么,优化器人员为什么要浪费时间优化这些。
另一种情况困扰着许多人,他们问"为什么当我删除ORDER BY
时它运行得这么快?
SELECT ...
ORDER BY ...
LIMIT ...
更令人困惑的是,某些变体的运行速度并不快。 这里的关键因素是是否有处理所有WHERE
和ORDER BY
的复合索引。