MySQL将CPU 100%加载一分钟，选择选择

Question

情况

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

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

这一个将返回所有关于具有特定IDs的客户端的信息。

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

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切断了每个部分。结果:几乎相同的时间-- 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.解决办法

正如我所建议的那样，我试图用IN中的一个替换INNER JOIN，这就成功了，~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输出：

​

Answer 1

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

有几种方法你可以尝试。

• 更简单的方法是分别运行查询，并以编程方式累积所有ID，然后使用ID的重复列表构建最后一个查询，这是您觉得“不对”的地方。事实上，由于检索了更多的ID，这种方法可能会变得很尴尬。
• 类似的解决方案是使用INSERT IGNORE和found上的唯一索引将查询的结果累积到一个临时表中，以确保去重复。然后在客户端表和临时表之间运行一个连接。
• 一种完全不同的方法是为SQL查询优化器提供更多的内存和时间限制，以鼓励它找到更快的查询计划。这里的困难在于，一方面干预mysqld的参数(您可能没有被授权这样做)，另一方面，稍后更改查询可能导致优化器再次失败；换句话说，这个解决方案不一定是稳定的。
• 另一种方法是将外部选择分散到工会内部，从而使工会退出；也就是说，不要去做。 选择.*其中id IN ( select1 UNION select2 UNION select3 ) 这并不容易优化，因为在生成id的地方和使用它的地方之间存在太多的“分离度”，所以您首先要做的是 选择.*其中id IN ( select1 )联合选择.*其中id IN ( select2 )联合选择.*其中id IN ( select3 ) 然后，依次将in合并为一个联接。例如，第一组： 选择* FROM clients，其中删除=0，id IN (选择client_id，如从contact\_persons中找到id IN (从contacts选择owner_id contact_info，如‘%关键字%’和company_or_person = 'person‘) 将成为第一位： 选择*从clients中删除=0和id IN (选择cp.client_id作为cp从contact\_persons中找到，将contacts作为c (cp.id = c.owner_id)连接，其中c.contact_info喜欢‘%关键字%’和c.company_or_person = 'person‘)) 最后是一个在优化器上更容易使用的表单： 选择不同的客户端。*从clients连接contact\_persons作为cp ON (clients.id = cp.client_id)将contacts作为c+ ON (cp.id = c.owner_id)，其中clients.deleted =0，c.contact_info，例如‘%关键字%’和c.company_or_person = 'person‘。 作为最后一个阶段，您将添加索引:例如，在上面的例子中，您将基于owner_id (这是一个=条件)和contact_info从contacts中选择company_or_person。因此，你可以 创建联系人索引contacts_ndx_1 (company_or_person、contact_info、owner_id) 这还允许全面执行所有子查询，并查看其中是否有一个特别慢下来的子查询。

Answer 2

首先检查查询的解释查询计划。像这样

EXPLAIN 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%')

这可以找出查询的哪一部分是慢的。mysql官方手册对理解解释结果有很大帮助。