改进这个 MySQL 查询 - 选择作为子查询 [英] Improving this MySQL Query - Select as sub-query

问题描述

我有这个查询

  SELECT  
   shot.hole AS hole,
   shot.id AS id,
   (SELECT s.id FROM shot AS s 
      WHERE s.hole = shot.hole AND s.shot_number > shot.shot_number AND shot.round_id = s.round_id 
       ORDER BY s.shot_number ASC LIMIT 1) AS next_shot_id,
   shot.distance AS distance_remaining,
   shot.type AS hit_type,
   shot.area AS onto
  FROM shot 
  JOIN course ON shot.course_id = course.id
  JOIN round ON shot.round_id = round.id
  WHERE round.uID = 78

这会在大约 0.7 秒内返回 900~ 行.这是好的，但需要更多这样的行

This returns 900~ rows in around 0.7 seconds. This is OK-ish, but there are more lines like this required

(SELECT s.id FROM shot AS s 
 WHERE s.hole = shot.hole AND s.shot_number > shot.shot_number AND shot.round_id = s.round_id 
 ORDER BY s.shot_number ASC LIMIT 1) AS next_shot_id,

例如

   (SELECT s.id FROM shot AS s 
    WHERE s.hole = shot.hole AND s.shot_number < shot.shot_number AND shot.round_id = s.round_id 
    ORDER BY s.shot_number ASC LIMIT 1) AS past_shot_id,

添加它会将加载时间增加到 10 秒，这太长了，页面通常根本不加载或 MySQL 只是锁定并使用 show processlist 显示查询只是坐在那里发送数据.

Adding this increases the load time to 10s of seconds which is far too long and the page often doesn't load at all or MySQL just locks up and using show processlist shows that the query is just sat there sending data.

删除那些子查询中的 ORDER BY s.shot_number ASC 子句将查询时间减少到 0.05 秒，这要好得多.但是需要 ORDER BY 以确保返回下一行或上一行(镜头)，而不是任何旧的随机行.

Removing the ORDER BY s.shot_number ASC clause in those sub queries reduces the query time down to 0.05 seconds which is much much better. But the ORDER BY is required to ensure that the next or past row (shot) is returned, rather than any old random row.

我如何改进此查询以使其运行速度更快并返回相同的结果.也许我的方法获取下一行和过去行是次优的，我需要寻找一种不同的方式来返回下一行和上一行 ID?

How can I improve this query to make it run faster and return the same results. Perhaps my approach for obtaining the next and past rows is sub optimal and I need to look at a different way of returning those next and previous row IDs?

编辑 - 附加背景信息

在我的测试域(一个子域)上查询正常.但是当转移到实时域时，问题就开始了.由于这些新的缓慢查询，几乎没有任何变化，但整个站点都停止了.重点说明:

The query was fine on my testing domain, a subdomain. But when moved to the live domain the issues started. Hardly anything was changed yet the whole site came to halt because of these new slow queries. Key notes:

• 不同的域
• /var/www 中的不同文件夹
• 同一个数据库
• 相同的数据库凭据
• 相同的代码
• 添加索引以试图修复 - 这没有帮助

这些是否会影响加载时间?

Could any of these affected the load time?

推荐答案

为了扩展 Strawberry 的答案，对预查询"进行额外的左连接以获取所有上一个/下一个 ID，然后加入以获取任何内容您需要的详细信息.

To expand on Strawberry's answer, doing additional left-join for a "pre-query" to get all the prior / next IDs, then join out to get whatever details you need.

select
      Shot.ID,
      Shot.Hole,
      Shot.Distance as Distance_Remaining,
      Shot.Type as Hit_Type,
      Shot.Area as Onto
      PriorShot.Hole as PriorHole,
      PriorShot.Distance as PriorDistanceRemain,
      NextShot.Hole as NextHole,
      NextShot.Distance as NextDistanceRemain
   from
      ( SELECT 
              shot.id, 
              MIN(nextshot.id) as NextShotID,
              MAX(priorshot.id) as PriorShotID
           FROM 
              round 
                 JOIN shot 
                    on round.id = shot.round_id
                    LEFT JOIN shot nextshot
                       ON shot.round_id = nextshot.round_id
                       AND shot.hole = nextshot.hole
                       AND shot.shot_number < nextshot.shot_number
                    LEFT JOIN shot priorshot
                       ON shot.round_id = priorshot.round_id
                       AND shot.hole = priorshot.hole
                       AND shot.shot_number > priorshot.shot_number
           WHERE
              round.uID = 78
           GROUP BY 
              shot.id ) AllShots
         JOIN Shot
            on AllShots.id = Shot.ID
            LEFT JOIN shot PriorShot
               on AllShots.PriorShotID = PriorShot.ID
            LEFT JOIN shot NextShot
               on AllShots.NextShotID = NextShot.ID

内部查询仅获取round.uID = 78 的那些，然后您可以根据需要加入下一个/前一个.我没有将连接添加到课程和圆桌会议，因为没有显示结果列，但可以轻松添加.

The inner query gets only those for round.uID = 78, then you can join to the next / prior as needed. I did not add the joins to the course and round tables as no result columns were presented, but could easily be added.

这篇关于改进这个 MySQL 查询 - 选择作为子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！