时间戳

首页 > 解决方案 > 使用多个 JOINS、子查询和 MIN MAX 优化 Select 查询

mysql - 使用多个 JOINS、子查询和 MIN MAX 优化 Select 查询

问题描述

我正在尝试优化以下查询，它运行良好但非常缓慢。子查询多次遍历整个表

挑战在于，在 3 种情况下，我需要从值不为空的 JOINS 中获取最低值，在 1 种情况下，JOIN 应该获得最高行值。我用过 MIN 和 Max

有没有更好的方法可以用来创建这个查询？

以下是值不为空的最低值列。我正在使用带有 JOIN 的 MIN() 子查询

j4.owner_perception, 
j6.tl_perception,

以下是最高值列。我正在使用带有 JOIN 的 MAX() 子查询

j2.tl_perception, 
j2.owner_perception

查询

EXPLAIN SELECT 
  s.ticket_number, 
  s.request_id,
  j4.owner_perception, 
  j6.tl_perception,
  j2.tl_perception, 
  j2.owner_perception 
FROM 
  survey 
  LEFT JOIN survey AS s ON survey.id = s.id 
  LEFT JOIN (
    SELECT 
      request_id, 
      MIN(id) AS minumumownerid 
    FROM 
      history_gt 
    WHERE 
      owner_perception != "" 
    GROUP BY 
      request_id
  ) AS j3 ON s.request_id = j3.request_id 
  LEFT JOIN history_gt AS j4 ON j4.id = j3.minumumownerid 
  LEFT JOIN (
    SELECT 
      request_id, 
      MIN(id) AS minumumtlid 
    FROM 
      history_gt 
    WHERE 
      tl_perception != "" 
    GROUP BY 
      request_id
  ) AS j5 ON s.request_id = j5.request_id 
  LEFT JOIN history_gt AS j6 ON j6.id = j5.minumumtlid 
  LEFT JOIN (
    SELECT 
      request_id, 
      MAX(id) AS maximumid 
    FROM 
      history_gt 
    GROUP BY 
      request_id
  ) AS j1 ON s.request_id = j1.request_id 
  LEFT JOIN history_gt AS j2 ON j2.id = j1.maximumid 
GROUP BY 
  s.request_id 
ORDER BY 
  s.id ASC 
LIMIT 
  50

这是解释细节（我不明白解释:)

+------+--------------+-------------+---------+-------------------+-------------+----------+--------------------+----------+----------------------------------------------+--+
|  id  | select_type  |   table     |  type   |  possible_keys    |    key      | key_len  |        ref         |  rows    |                    Extra                     |  |
+------+--------------+-------------+---------+-------------------+-------------+----------+--------------------+----------+----------------------------------------------+--+
|   1  | PRIMARY      | survey      | index   | NULL              | request_id  |     767  | NULL               |     476  | Using index; Using temporary; Using filesort |  |
|   1  | PRIMARY      | s           | eq_ref  | PRIMARY           | PRIMARY     |       4  | qo.survey.id       |       1  |                                              |  |
|   1  | PRIMARY      | <derived2>  | ref     | key0              | key0        |     153  | qo.s.request_id    |    1060  | Using where                                  |  |
|   1  | PRIMARY      | j4          | eq_ref  | PRIMARY           | PRIMARY     |       4  | j3.minumumownerid  |       1  | Using where                                  |  |
|   1  | PRIMARY      | <derived3>  | ref     | key0              | key0        |     153  | qo.s.request_id    |    2121  | Using where                                  |  |
|   1  | PRIMARY      | j6          | eq_ref  | PRIMARY           | PRIMARY     |       4  | j5.minumumtlid     |       1  | Using where                                  |  |
|   1  | PRIMARY      | <derived4>  | ref     | key0              | key0        |     153  | qo.s.request_id    |     530  | Using where                                  |  |
|   1  | PRIMARY      | j2          | eq_ref  | PRIMARY           | PRIMARY     |       4  | j1.maximumid       |       1  | Using where                                  |  |
|   4  | DERIVED      | history_gt  | range   | NULL              | request_id  |     152  | NULL               |  252406  | Using index for group-by                     |  |
|   3  | DERIVED      | history_gt  | index   | NULL              | request_id  |     152  | NULL               | 1009620  | Using where                                  |  |
|   2  | DERIVED      | history_gt  | index   | owner_perception  | request_id  |     152  | NULL               | 1009620  | Using where                                  |  |
+------+--------------+-------------+---------+-------------------+-------------+----------+--------------------+----------+----------------------------------------------+--+

标签： mysqlindexingquery-optimization

解决方案

是的，您的原件很笨重，并且经过了 3 次历史记录表。我的建议是对历史记录中的所有记录进行一次条件查询。请注意我的“PQ”（预查询）按请求 id 分组，并根据 !=“” 资格的 CASE WHEN 执行相应的最小值/最大值。如果不适用，则返回 null 作为结果被忽略。然后我应用 coalesce() 来防止空值从该结果集中返回。现在，我将您的所有聚合都放在了 1 个结果集中。

现在可用，我将您的冗余调查删除到调查 S 连接，并直接从调查转到预查询结果。现在，我可以将预查询的连接返回到它们各自的最小/最大 ID 的历史记录。

SELECT 
      s.ticket_number, 
      s.request_id,
      j4.owner_perception, 
      j6.tl_perception,
      j2.tl_perception, 
      j2.owner_perception 
    FROM 
      survey s
         left join
         (SELECT 
                request_id, 
                coalesce( min( case when owner_perception != ""
                   then id else null end ), 0 ) minOwnerID,
                coalesce( min( case when tl_perception != ""
                   then id else null end ), 0 ) minTLID,
                MAX(id) AS maxID
             FROM 
                history_gt 
             GROUP BY 
                request_id ) PQ
            on s.request_id = PQ.request_id
             LEFT JOIN history_gt AS j4 
                ON PQ.minOwnerID = j4.id
             LEFT JOIN history_gt AS j6 
                ON PQ.minTLID = j6.id
             LEFT JOIN history_gt AS j2 
                ON PQ.maxID = j2.id
   GROUP BY 
      s.request_id 
   ORDER BY 
      s.id ASC 
   LIMIT 
      50

推荐阅读