13.2.10.10 优化子查询

开发正在进行中,因此长期没有优化技巧是可靠的。以下列 table 提供了一些您可能想玩的有趣技巧。另请参见第 8.2.2 节“优化子查询,派生 table 和视图引用”

  • 使用影响子查询中行数或 Sequences 的子查询子句。例如:
SELECT * FROM t1 WHERE t1.column1 IN
  (SELECT column1 FROM t2 ORDER BY column1);
SELECT * FROM t1 WHERE t1.column1 IN
  (SELECT DISTINCT column1 FROM t2);
SELECT * FROM t1 WHERE EXISTS
  (SELECT * FROM t2 LIMIT 1);
  • 用子查询替换联接。例如,尝试以下操作:
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
  SELECT column1 FROM t2);

代替这个:

SELECT DISTINCT t1.column1 FROM t1, t2
  WHERE t1.column1 = t2.column1;
  • 可以将某些子查询转换为联接,以与不支持子查询的旧版本 MySQL 兼容。但是,在某些情况下,将子查询转换为联接可能会提高性能。参见第 13.2.10.11 节,“将子查询重写为联接”

  • 将子句从外部移到内部子查询。例如,使用以下查询:

SELECT * FROM t1
  WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

代替此查询:

SELECT * FROM t1
  WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

对于另一个示例,请使用以下查询:

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

代替此查询:

SELECT (SELECT column1 FROM t1) + 5 FROM t2;
  • 使用行子查询而不是相关子查询。例如,使用以下查询:
SELECT * FROM t1
  WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

代替此查询:

SELECT * FROM t1
  WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
                AND t2.column2=t1.column2);
  • 使用NOT (a = ANY (...))而不是a <> ALL (...)

  • 使用x = ANY (table containing (1,2))而不是x=1 OR x=2

  • 使用= ANY而不是EXISTS

  • 对于始终返回一行的不相关子查询,IN总是比=慢。例如,使用以下查询:

SELECT * FROM t1
  WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);

代替此查询:

SELECT * FROM t1
  WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

这些技巧可能会导致程序运行得更快或更慢。使用BENCHMARK()函数之类的 MySQL 工具,您可以了解自己的情况会有什么帮助。参见第 12.15 节“信息功能”

MySQL 本身进行的一些优化是:

  • MySQL 仅执行一次不相关的子查询。使用EXPLAIN确保给定的子查询确实不相关。

  • MySQL 重写INALLANYSOME子查询,以尝试利用对子查询中的选择列 table 列构建索引的可能性。

  • MySQL 用索引查找功能替换了以下形式的子查询,该索引查询功能EXPLAIN描述为特殊的连接类型(unique_subqueryindex_subquery):

... IN (SELECT indexed_column FROM single_table ...)
  • MySQL 使用涉及MIN()MAX()的 table 达式来增强以下形式的 table 达式,除非涉及到NULL值或空集:
value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

例如,此WHERE子句:

WHERE 5 > ALL (SELECT x FROM t)

优化器可能会这样处理:

WHERE 5 > (SELECT MAX(x) FROM t)

另请参见MySQL 内部知识:MySQL 如何转换子查询