mysql - 有很多左连接的慢查询

Question

当我在数据库中检查 SHOW PROCESSLIST; 时，我得到了以下查询。它大量使用 CPU(超过 100%)，需要 80 秒才能完成查询。我们有一个单独的数据库服务器(64GB RAM)。

INSERT INTO `search_tmp_598075de5c7e67_73335919` 
SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` 
  FROM (SELECT `search_index`.`entity_id`, (((0)) * 1) AS score  
        FROM `catalogsearch_fulltext_scope1` AS `search_index`
        LEFT JOIN `catalog_eav_attribute` AS `cea`
                    ON search_index.attribute_id = cea.attribute_id
        LEFT JOIN `catalog_category_product_index` AS `category_ids_index` 
                    ON search_index.entity_id = category_ids_index.product_id
        LEFT JOIN `review_entity_summary` AS `rating`
                    ON `rating`.`entity_pk_value`=`search_index`.entity_id
                   AND `rating`.entity_type = 1
                   AND `rating`.store_id  =  1
       WHERE (category_ids_index.category_id = 2299)
  ) AS `main_select`
 GROUP BY `entity_id`
 ORDER BY `relevance` DESC
 LIMIT 10000

为什么这个查询占用了我全部的 CPU 资源？

Answer 1

一些低效的地方:

• 外连接catalog_category_product_index 的记录中存在非空条件。这会将外部连接变成内部连接。使用 inner join 子句会更有效。

• 不需要嵌套查询:分组、排序和限制可以直接在内部查询上完成。

• (((0)) * 1) 只是说 0 的一种复杂方式，并取 MAX这显然仍然会为所有记录返回 0 的相关性。这不仅是一种输出 0 的低效方式，而且毫无意义。我假设您的真实查询有一些不太明显的计算，可能需要优化。

• 如果 catalog_eav_attribute.attribute_id 是唯一字段，则外部连接该表没有任何意义，因为该数据不会在任何地方使用

• 如果 review_entity_summary.entity_pk_value 是唯一的(至少当 entity_type = 1 和 store_id = 1 时)，那么再次有在外部连接该表时没有用，因为该数据未在任何地方使用

• 如果上述 2 个要点中的字段不唯一，但每个 search_index.entity_id 值返回的记录数不影响结果(因为它目前与模糊 (((0)) * 1) 值，它没有)，那么也不需要外部连接。

有了这些假设，select 部分可以简化为:

SELECT      search_index.entity_id, 
            MAX(((0)) * 1) AS relevance 
FROM        catalogsearch_fulltext_scope1 AS search_index 
INNER JOIN  catalog_category_product_index AS category_ids_index 
        ON  search_index.entity_id = category_ids_index.product_id 
WHERE       category_ids_index.category_id = 2299
GROUP BY    search_index.entity_id 
ORDER BY    relevance DESC 
LIMIT       10000

我仍然把 (((0)) * 1) 留在那里，但它真的没有意义。