MySQL的左连接只有最后一排十分缓慢

问题描述：

堡垒

|id|lat|lon|

fort_sightings

|id|fort_id|team|

fort_raids

|id|fort_id|raid_level|

我需要获取所有从forts行，然后选择从fort_sightings和fort_raids的最新信息，如果任何一个查询。可能有多行fort_id具有相同的值，所以我需要最新的信息。

目前，我有这个，这可能不是最漂亮的

SELECT 
    * 
FROM 
    forts c 

LEFT JOIN fort_sightings o ON o.id = (
    SELECT 
     id 
    FROM 
     fort_sightings 
    WHERE 
     fort_id = c.id 
    ORDER BY 
     id DESC 
    LIMIT 1 
) 

LEFT JOIN fort_raids r ON r.id = (
    SELECT 
     id 
    FROM 
     fort_raids 
    WHERE 
     fort_id = c.id 
    ORDER BY 
     id DESC 
    LIMIT 1 
)

但它是痛苦的缓慢，查询接管10秒。 forts只有〜350行，所以它真的不应该花这么长时间。我相信它来自JOIN中的所有SELECT查询，但我不知道任何替代方案。

EXPLAIN

每一个问题与慢查询涉及包含EXPLAIN'的'输出。请看看如何获得'EXPLAIN'的输出并将其发布在这里。 – Mjh

子查询总是很慢，在这种情况下，如果我没有弄错，你正在为每个o.id和r.id做一个子查询。总是找到一种避免子查询的方法。 – Goose

为了找到它为什么这么慢，使用'explain'后面跟着这个查询（在mysql命令行中），它会告诉你使用了哪些索引以及读取了多少记录。你可以改变你的代码来使用索引或添加索引，如果它真的需要的话 – Tamar

答

这是您的查询：

SELECT * 
FROM forts c LEFT JOIN 
    fort_sightings o 
    ON o.id = (SELECT fs2.id 
       FROM fort_sightings fs2 
       WHERE fs.fort_id = c.id 
       ORDER BY fs2.id DESC 
       LIMIT 1 
       ) LEFT JOIN 
    fort_raids r 
    ON r.id = (SELECT fr2.id 
       FROM fort_raids fr2 
       WHERE fr2.fort_id = c.id 
       ORDER BY fr2.id DESC 
       LIMIT 1 
       );

我觉得很奇怪的是结构化的。但是，看看，如果这个工程：

fort_sightings(fort_id, id)
fort_raids(fort_id, id)

的fort_id在索引的第一个键是很重要的。

如果这不起作用，那么您可能需要修改查询。

第一个关键？我不知道这很重要。我会尝试切换键盘，然后运行您的查询，谢谢！ – kopa

我有两个主键'id'和'fort_id'，其中'id'使用'AUTO INCREMENT'。如果我尝试切换它们，那么'fort_id'是关键字1，那么我得到这个：'1075 - 不正确的表格定义;只能有一个自动列，并且必须将其定义为密钥“ – kopa

您不需要两个主键。你需要我在答案中描述的复合索引。 –

答

除了来自Gordon的索引建议，您正在做一个相关的子查询，这意味着对于堡垒表中的每个记录，您都将重新运行查询以查看和搜索。我想稍微改变拉所有的MAX（）每堡垒从每个再加入...像

SELECT 
     c.id, 
     c.lat, 
     c.lon, 
     fs2.team, 
     fr2.raid_level 
    FROM 
     forts c 
     LEFT JOIN 
     (select fs.fort_id, max(fs.id) as MaxSightID 
       from fort_sightings fs 
       group by fs.fort_id) S 
      on c.id = s.fort_id 
      LEFT JOIN fort_sightings fs2 
       on s.MaxSightID = fs2.id 
     LEFT JOIN 
     (select fr.fort_id, max(fr.id) as MaxRaidID 
      from fort_raids fs 
      group by fr.fort_id) R 
      on c.ID = r.fort_id 
      LEFT JOIN fort_raids fr2 
      on r.MaxRaidID = fr2.id

的第二部分左联接回到原来的RAID和瞄准表拉相应的团队，如果发现任何此类问题，请在最终结果中找到突袭级别。

答

如果您需要最有效的查询，您应该添加列latest_fort_sighting_idlatest_fort_raid_id表forts。MySQL没有强大的功能，如物化视图或哈希加入像PostgreSQL，我们需要手动处理它们。不要忘记使用事务进行更新。

如果限制范围为forts，或者只能使用LEFT JOIN运行优化查询。

select - SQL join: selecting the last records in a one-to-many relationship - Stack Overflow

SELECT forts.*, fs1.team, fr1.raid_level FROM forts 
LEFT JOIN fort_sightings fs1 ON fs1.fort_id = forts.id 
LEFT JOIN fort_sightings fs2 ON fs2.fort_id = forts.id AND fs1.id < fs2.id 
LEFT JOIN fort_raids fr1 ON fr1.fort_id = forts.id 
LEFT JOIN fort_raids fr2 ON fr2.fort_id = forts.id AND fr1.id < fr2.id 
WHERE fs2.id IS NULL AND fr2.id IS NULL AND forts.id > 5 ORDER BY forts.id LIMIT 5;

MySQL的左连接只有最后一排十分缓慢

相关推荐