主页 / dba / 问题 / 18617
Accepted
artfulrobot
Asked: 2012-06-01 01:28:21 +0800 CST

了解复杂 SELECT 查询的优化

  • 772

我有一个包含基本联系信息的人员表。我有一堆标准可以从人员表中选择 Id 值（称为 pid 的字段）。

  1. 我想要匹配（（标准 1 或标准 2）和标准 3）的记录。

  2. 我希望条件语句将列添加到结果中。

  3. 我需要一种标准方法来组合查询，因为我正在制作“搜索构建器”；所以我不能优化每个组合。可以假设每个标准将返回零或一行。

EG1：我第一次尝试

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       (criterion3 SELECT) c3
 WHERE people.id=c3.pid
   AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

这太慢了——它从来没有在我允许的那一刻返回记录！

EG2：接下来我尝试了

SELECT people.*
  FROM people,
       (       (criterion1 SELECT) 
         UNION (criterion2 SELECT) ) c12
 WHERE people.id=c12.pid
   AND people.id IN (criterion3 SELECT)

这个在 0.6 秒内返回 2000+ 行，这是可以接受的。但是你不能从标准 3 添加任何列，也不能从 c1 和 c2 访问列，真的，因为一个会掩盖另一个。

EG3：出于兴趣，我将 c12 移到 WHERE 子句中：

SELECT people.*
  FROM people
 WHERE id IN (criterion3 SELECT)
   AND id IN ( (criterion1 SELECT) 
               UNION (criterion2 SELECT) )

但是，在我终止查询之前，这再次抛出了超过一分钟。

EG4：所以我回到了 EG2 代码，做了一件丑陋的事情：

SELECT * 
  FROM ( all the EG2 SQL ) src
       LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid

所以 c1 和 c2 查询被添加了两次！这将在 6 秒内返回（相同数量的）结果以及额外的字段。太慢了，真的，但至少它运行。而且真的很丑！

谁能给我任何指示来解释这一切？当他们都在做同样的事情时，为什么 EG3 比 EG1/EG4 快得多？为什么运行查询两次（EG4）比运行一次（EG1）要快？！

编辑：使用 MySQL 5.1，InnoDB 表。

mysql optimization

1 个回答

  1. Best Answer
    • 为什么查询之间的性能存在巨大差异？
      （假设您至少为子查询条件和连接条件中使用的列定义了索引）

    基本上是因为 MySQL 优化器不够聪明，无法确定所有这些查询都是等价的。因此，它可能会为不同的查询生成不同的执行计划。如果您没有正确定义FOREIGN KEY约束，优化器实际上可能是正确的，不能保证查询返回相同的结果。

    • 那么，如何提高性能并有一个标准的方式来编写这种类型的查询变体呢？

    影响 MySQL 查询性能的几件事：

    1. id IN (SELECT subquery)如果可以避免，请勿使用。它在大多数 MySQL 版本中都没有得到很好的优化（见下面的第 6 点）。如果可以，请使用连接。

    2. 如果这不会更改结果集，请替换UNION为（您可以在 EG3 查询中执行此操作）。UNION ALL

    3. 不要使用隐式连接（在子句中使用逗号，在FROM子句中使用连接条件WHERE）。使用显式JOIN语法。例如，您的 EG1 实际上与以下内容相同（这不是为了性能，而是为了一致性）：

      SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       JOIN (criterion3 SELECT) c3 ON people.id=c3.pid
 WHERE ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

    4. 上面的查询有一个OR与 2 个表相关的条件（以及它们与 的连接people，因此实际上是 3 个表）。这通常不是很好的性能。

    5. 您可以尝试使用EXISTS. 这将使您的查询更易于编写，并且也可能有助于提高性能：

      SELECT people.*
  FROM people
 WHERE EXISTS 
         (criterion3 SELECT modified with `people.id = some_table.pid`)
   AND ( EXISTS 
           (criterion1 SELECT modified with `people.id = some_table.pid`)
      OR EXISTS 
           (criterion2 SELECT modified with `people.id = some_table.pid`) 
       ) ;

    6. 如果您的主机允许，请尝试/测试 MariaDB（它是 MySQL fork 替代品），它在其最新版本中引入了查询执行方面的多项改进。优化器会更智能地识别等效查询，当然也更智能，因为它实现了一些影响查询将连接、子查询等的新算法。

    MySQL 5.6 在优化器方面也有一些改进，但它还没有作为稳定版本提供。

    • 3

相关问题