情况

我的 SQL 查询包含两部分。第一个很简单:

SELECT * FROM `clients` WHERE id IN (...)

这将返回有关具有特定 ID 的客户端的所有信息

第二个查询要大得多，它应该为之前的查询准备 ID 列表(为了更好的可读性我省略了几个 UNION，你可以在最后看到完整的查询):

SELECT client_id
  FROM `contact_persons`
  WHERE id IN (
              SELECT owner_id
                FROM `contacts`
                WHERE contact_info LIKE '%keyword%' AND company_or_person = 'person'
  )
UNION
SELECT owner_id
  FROM `contacts`
  WHERE contact_info LIKE '%keyword%' AND company_or_person = 'company'
UNION
SELECT id
  FROM `clients`
  WHERE client_name LIKE '%keyword%'

这些查询中的每一个都需要不到 0.5 秒来单独执行。但是，当我将第二个查询放入第一个查询时，这两个查询都需要一分多钟才能完成，此时 CPU 负载跃升至 100%。

我尝试了什么

• 分别运行此查询的两个部分。结果:一切都足够快(不到 0.5 秒)
• 将 SELECT * ... 更改为 SELECT id ...(尽管这会使整个查询变得无用)。结果:什么都没有改变
• 使用几个不同的关键字以避免缓存
• 使内部(大的)返回 0 行。结果:什么都没有改变。完整的查询仍然非常慢

看起来如果我单独运行查询的这些部分并将 ID 列表存储在一个 php 变量中它会工作正常，但即使对于我这个初学者来说也是如此。

完成查询

SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
SELECT client_id as found
  FROM `contact_persons`
  WHERE id IN (
              SELECT owner_id
                FROM `contacts`
                WHERE contact_info LIKE '%keyword%' AND company_or_person = 'person'
 )
UNION
SELECT owner_id as found
  FROM `contacts`
  WHERE contact_info LIKE '%keyword%' AND company_or_person = 'company'
UNION
SELECT id as found
  FROM `clients`
  WHERE client_name LIKE '%keyword%'
UNION
SELECT client_id as found
  FROM `cargo`
  WHERE cargo_name LIKE '%keyword%'
UNION
SELECT page_id as found
  FROM `comments`
  WHERE message LIKE '%keyword%' AND page_type = 'client' AND deleted = 0
UNION
SELECT client_id as found
  FROM `contact_persons`
  WHERE person_name LIKE '%keyword%')

我的数据库非常小 (160 Kb)，特别是 clients 表只有 160 行和 5 列。我尝试了所有我能想出的办法，但仍然无法解决问题。

更新 1.解释

在 EXPLAIN 的结果中我得到

更新 2. 这与 UNION 无关

我刚刚运行了这个最小的查询，其中我用 UNION 切断了每个部分。结果:花费了几乎相同的时间 - 执行 57 秒

SELECT SQL_NO_CACHE id FROM `clients` WHERE id IN (
 SELECT client_id
   FROM `contact_persons`
   WHERE id IN (
               SELECT owner_id
                 FROM `contacts`
                 WHERE contact_info LIKE '%keyword%'
             )
)

更新3.解决方案

正如所建议的那样，我尝试用 INNER JOIN 替换其中一个 IN 并且成功了，~60 秒现在变成了~0.4s

所以代替

SELECT .. WHERE .. IN (SELECT .. WHERE .. IN (SELECT ..))

我是这样写的

SELECT .. AS t1 INNER JOIN (SELECT .. WHERE .. IN (SELECT ..)) AS t2 WHERE ti.id = t2.id

对于人类来说，这几乎是一回事，但看起来它不适用于 MySQL 服务器。

如果有人好奇，我会在此处发布最终查询的 EXPLAIN 输出:

:

最佳答案

您的查询可能太复杂，查询优化器提出了错误的优化。正如@huhushow 建议的那样，针对您的查询运行 EXPLAIN 可能会显示这一点。

您可以尝试多种方法。

• 更简单的方法是单独运行查询，并以编程方式累积所有 ID，然后使用已删除重复的 ID 列表构建最后一个查询。这就是你觉得“不对劲”的地方。事实上，随着更多 ID 的检索，这种方法可能会变得笨拙。

• 类似的解决方案是将查询的结果累积到一个临时表中，使用 INSERT IGNORE 和 found 上的唯一索引来确保重复数据删除。然后，您将在客户表和临时表之间运行 JOIN。

• 一种完全不同的方法是为 SQL 查询优化器提供更多内存和时间限制，以鼓励它找到更快的查询计划。这里的困难在于干预 mysqld 的参数(您可能没有被授权这样做)，而且稍后更改查询可能会导致优化器再次失败；换句话说，这个解决方案不一定稳定。

• 另一种方法是将外部 SELECT 分布在 UNION 内部，从而将 UNION 引出；即，而不是做

  SELECT a.* WHERE id IN ( select1 UNION select2 UNION select3 )
  

  由于 id 的生成位置和使用位置之间的“分离度”太多，因此不容易优化，您会先做

  SELECT a.* WHERE id IN ( select1 )
  UNION
  SELECT a.* WHERE id IN ( select2 )
  UNION
  SELECT a.* WHERE id IN ( select3 )
  

  然后，依次合并 IN 并将其转换为 JOIN。例如第一组:

  SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
      SELECT client_id as found
      FROM `contact_persons`
      WHERE id IN (
        SELECT owner_id
          FROM `contacts`
          WHERE contact_info LIKE '%keyword%' AND company_or_person
          = 'person' )
  

  将成为第一个:

  SELECT * FROM `clients` WHERE deleted = 0 AND id IN (
      SELECT cp.client_id as found
      FROM `contact_persons` AS cp
      JOIN `contacts` AS c ON (cp.id = c.owner_id)
          WHERE c.contact_info LIKE '%keyword%' AND c.company_or_person
          = 'person' )
  )
  

  最后是优化器更容易的形式:

  SELECT [DISTINCT] clients.* FROM `clients`
      JOIN `contact_persons` AS cp ON (clients.id = cp.client_id)
      JOIN `contacts` AS c ON (cp.id = c.owner_id)
           WHERE clients.deleted = 0 
           AND c.contact_info LIKE '%keyword%'
           AND c.company_or_person = 'person'
  

  作为最后一个阶段，您将添加索引:例如，在上面的例子中，您根据 company_or_person 从 contacts 中选择 owner_id (这是一个 = 标准)和 contact_info。因此你可以

  CREATE INDEX contacts_ndx_1 ON contacts(company_or_person, contact_info, owner_id)
  

  这还允许完整地执行所有子查询，并查看其中是否有一个特别减慢速度。

关于MySQL 将 SELECT 加载 CPU 100% 一分钟，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34250799/