5 回答

• 1

  这是一个非常有趣的查询 . 在优化过程中，您可能会发现并了解有关MySQL如何工作的许多新信息 . 我不确定我是否会有时间详细编写所有内容，但我可以逐步更新 .

  为什么它很慢

  基本上有两种情况：快速和慢速 .

  在快速方案中，您正在以某种预定义的顺序在表上行走，并且可能同时快速从id为其他表中的每一行获取一些数据 . 在这种情况下，只要LIMIT子句指定了足够的行，就会停止行走 . 订单来自哪里？从您在表上的b树索引或子查询中的结果集的顺序 .

  在缓慢的情况下，您没有预定义的顺序，并且MySQL必须隐式地将所有数据放入临时表中，对某些字段上的表进行排序并从LIMIT子句返回n行 . 如果您放入该临时表的任何字段是TEXT类型（不是VARCHAR），MySQL甚至不会尝试将该表保留在RAM中并刷新并在磁盘上对其进行排序（因此需要额外的IO处理） .

  首先要解决的问题

  在许多情况下，您无法构建允许您遵循其顺序的索引（例如，当您从不同的表中对ORDER BY列进行排序时），因此在这种情况下的经验法则是最小化MySQL将放置的数据在临时表中 . 你怎么能这样做？您只选择子查询中行的标识符，并在获得ID之后，将ids连接到表本身和其他表以获取内容 . 那就是你用订单制作一张小 table ，然后使用快速方案 . （这通常与SQL略有矛盾，但SQL的每种风格都有自己的方法来优化查询方式） .

  巧合的是，你的 SELECT -- everything is ok here 看起来很有趣，因为它是第一个不合适的地方 .

  SELECT p.*
      , u.name user_name, u.status user_status
      , c.name city_name, t.name town_name, d.name dist_name
      , pm.meta_name, pm.meta_email, pm.meta_phone
      , (SELECT concat("{", 
          '"id":"', pc.id, '",', 
          '"content":"', replace(pc.content, '"', '\\"'), '",', 
          '"date":"', pc.date, '",', 
          '"user_id":"', pcu.id, '",', 
          '"user_name":"', pcu.name, '"}"') last_comment_json 
          FROM post_comments pc 
          LEFT JOIN users pcu ON (pcu.id = pc.user_id) 
          WHERE pc.post_id = p.id
          ORDER BY pc.id DESC LIMIT 1) AS last_comment
  FROM (
      SELECT id
      FROM posts p
      WHERE p.status = 'published'
      ORDER BY 
          (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
              THEN +p.reputation ELSE NULL END) DESC, 
          p.id DESC
      LIMIT 0,10
  ) ids
  JOIN posts p ON ids.id = p.id  -- mind the join for the p data
  LEFT JOIN users u ON (u.id = p.user_id)
  LEFT JOIN citys c ON (c.id = p.city_id)
  LEFT JOIN towns t ON (t.id = p.town_id)
  LEFT JOIN dists d ON (d.id = p.dist_id)
  LEFT JOIN post_metas pm ON (pm.post_id = p.id)
  ;
  

  这是第一步，但即使是现在你也可以看到你不需要为你不需要的行制作这些无用的LEFT JOINS和json序列化 . （我跳过 GROUP BY p.id ，因为我没有看到哪个LEFT JOIN可能导致多行，你不进行任何聚合） .

  还没写：

  • 索引

  • 重新制定CASE子句（使用UNION ALL）

  • 可能强制索引

  回复于 2024-05-03T18:07:44+08:00
• 0

  这是你的问题：

  • "ORDER BY expression"：必须为表中的每一行计算表达式，然后对整个表进行排序，然后结果通过LIMIT .

  • 没有索引使用："ORDER BY col"当"col"是索引的一部分时，可以通过按顺序遍历索引来消除排序 . 使用LIMIT时非常有效 . 但是，它在这里不起作用 .

  有一些方法摆脱这种混乱，但你需要告诉你有多少不同级别的“声誉”（如3，或像“很多”）以及它们如何统计分布（例如，1名声望为100的用户和其余的都是零，或均匀分布） .

  EDIT

  嗯，没有关于“声誉”的统计分布或其可能的 Value 范围的信息 . 在这种情况下，让我们采取直截了当的方法：

  让我们添加一个“repdate”列，其中包含：

  repdate = p.created_at + INTERVAL p.reputation DAY
  

  这相当于将他们拥有的每个声望点的一天转移到未来 . 然后他们会相应地排序 . 如果p.created_at不是DATETIME，请调整味道 .

  现在，我们可以简单地“ORDER BY repdate DESC”并使用索引，它会很快 .

  回复于 2024-05-03T18:07:44+08:00
• 0

  也许带有列的索引： id ， reputation ， created_at 可能有助于加快一点，如果你还没有尝试，那将是最简单的解决方案 . DBMS不必读取如此多的数据，计算偏移量，限制受影响的记录 .

  回复于 2024-05-03T18:07:44+08:00
• 0

  select * 
  from (
    SELECT -- everything is ok here
    , CASE 
        WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
          THEN + p.reputation ELSE NULL END order_col
    FROM posts AS p
    WHERE p.status = 'published' -- the only thing for filter
    LIMIT 0,10 -- limit provided as well
  ) a
  ORDER BY 
      a.order_col desc
      ,a.id DESC
  

  回复于 2024-05-03T18:07:44+08:00
• 1

  • Inflate-deflate - LEFT JOIN 膨胀行数， GROUP BY 然后收缩 . 膨胀的行数很昂贵 . 相反，在执行任何 JOINing 之前，请专注于获取所需行的ID . 幸运的话，你可以摆脱 GROUP BY .

  • WP模式 - 这是一个EAV模式，在性能和扩展方面很糟糕 .

  • 你有什么指数？有关如何改进元表的信息，请参见this .

  • 复杂 ORDER BY . 这导致在排序和执行 LIMIT 之前收集所有行（在过滤之后） . 如果可能，重新考虑 ORDER BY 条款 .

  在完成我的建议之后，请启动另一个问题以继续完善 . 请务必包含 EXPLAIN SELECT ... 和 SHOW CREATE TABLE .

  回复于 2024-05-03T18:07:44+08:00