Documentation Home
MySQL 5.7 参考手册

13.2.10.10 优化子查询

开发仍在进行中，因此从长远来看，没有任何优化技巧是可靠的。下面的列表提供了一些您可能想要尝试的有趣技巧。另见 第 8.2.2 节，“优化子查询、派生表和视图引用”。

  • 使用影响子查询中行的数量或顺序的子查询子句。例如： 

    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 (...)。

  • Use x = ANY (table containing (1,2)) rather than x=1 OR x=2.

  • Use = ANY rather than EXISTS.

  • For uncorrelated subqueries that always return one row, IN is always slower than =. For example, use this query: 

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

    Instead of this query: 

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

These tricks might cause programs to go faster or slower. Using MySQL facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. See Section 12.16, “Information Functions”.

Some optimizations that MySQL itself makes are:

  • MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.

  • MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

  • MySQL replaces subqueries of the following form with an index-lookup function, which EXPLAIN describes as a special join type (unique_subquery or index_subquery): 

    ... IN (SELECT indexed_column FROM single_table ...)

  • MIN()MySQL 使用涉及or 的表达式增强以下形式的表达式MAX()，除非 涉及NULL值或空集： 

    value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

    例如，这个WHERE条款： 

    WHERE 5 > ALL (SELECT x FROM t)

    优化器可能会这样处理： 

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

另请参阅 MySQL 内部结构：MySQL 如何转换子查询。