Mysql查询Order By太慢

Question

我知道以前有人问过这种问题，但没有一个让我找到解决方案。

这是我的查询:

SELECT p.photoid, c.comment, p.path, p.smallfile, p.bigfile, c.userid, c.dateposted, u.username, c.likephoto 
  FROM bs_photocomments c, photos p, user u 
  WHERE c.active = 1 
    AND u.userid = c.userid 
    AND p.photoid = c.photoid 
    AND c.id = (SELECT ID FROM bs_photocomments WHERE photoid = p.photoid ORDER BY ID DESC LIMIT 1) 
  ORDER BY c.id DESC LIMIT 2

关于最后一个 AND 的一点解释:

基本上，我正在尝试获取 20 张独特照片的最新评论。例如，如果一张照片有 5 条评论，我只想显示最后一条。

ORDER BY c.id 是 killer 。只有 18 万条评论和 10 万张照片。如果我删除 ORDER BY 查询会立即返回，但显然不是我想要的结果。

索引如下:

+------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bs_photocomments |          0 | PRIMARY            |            1 | id          | A         |      183269 |     NULL | NULL   |      | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx1 |            1 | photoid     | A         |      183269 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx2 |            1 | active      | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx3 |            1 | dateposted  | A         |      183269 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx4 |            1 | userid      | A         |        4953 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx5 |            1 | puserid     | A         |        4072 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx6 |            1 | id          | A         |      183269 |     NULL | NULL   |      | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx6 |            2 | photoid     | A         |      183269 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx7 |            1 | photoid     | A         |      183269 |     NULL | NULL   | YES  | BTREE      |         |
| bs_photocomments |          1 | photocomments_idx7 |            2 | userid      | A         |      183269 |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| photos |          0 | PRIMARY      |            1 | photoid      | A         |       57736 |     NULL | NULL   |      | BTREE      |         |
| photos |          1 | photos_idx1  |            1 | userid       | A         |        5773 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx2  |            1 | dateuploaded | A         |       57736 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx3  |            1 | camera       | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx4  |            1 | focallength  | A         |         308 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx5  |            1 | category     | A         |          96 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx6  |            1 | active       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photo_idx7   |            1 | aperture     | A         |         354 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx8  |            1 | lenstype     | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx9  |            1 | film         | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| photos |          1 | photos_idx10 |            1 | originalfile | A         |       57736 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

有什么想法吗？谢谢!

Answer 1

此查询将返回(最多)20 张不同照片的单个“最新”评论(“recent-cy”由 bs_photocomments 表中的升序 ID 值标识)。返回的 20 张照片将是评论最“最近”的照片，并从最“最近”的评论开始排序。 (注意:只考虑 bs_photocomments 中具有 active=1 的行，bs_photocomments 中的所有其他行都将被忽略，就好像它们甚至不在表中一样。)

SELECT p.photoid
     , c.comment
     , p.path
     , p.smallfile
     , p.bigfile
     , c.userid
     , c.dateposted
     , u.username
     , c.likephoto
  JOIN ( 
         SELECT n.photoid
              , MAX(n.id) AS max_id
           FROM bs_photocomments n
          WHERE n.active = 1 
          GROUP BY n.photoid
          ORDER BY max_id DESC
          LIMIT 20
       ) m
  JOIN bs_photocomments c
    ON c.id = m.max_id
  JOIN photos p
    ON p.photoid = c.photoid
  JOIN user u
    ON u.userid = c.userid
 ORDER BY c.id DESC
 LIMIT 20

“技巧”是使用内联 View 来获取每个 photoid 的“最新”评论(内联 View 的别名是 m)。完成后，只需从 bs_photocomments 获取行，以及从用户和照片获取相关行即可。

我相信原始查询中真正的性能“ killer ”是连接谓词中的相关子查询。就对 bs_photocomments 表中的每一行执行该查询，甚至对 photos 表中的每一行运行该查询而言，这将非常昂贵。

---

为了获得最佳性能，您需要“覆盖索引”，而不是每个单独列上的(无用的)索引。至少，您需要 bs_photocomments 上的索引以 photoid 作为前导索引，后跟 id。包含active列将意味着可以“使用索引”满足内联 View 查询，而无需访问任何数据页。

... ON bs_photocomments (photoid, id, active)

有了这个索引，MySQL 应该能够相当有效地获得每个 photoid 的最大 id。同样，有一个索引

... ON bs_photocomments (id, comment, userid, dateposted, likephoto)

将意味着对 bs_photocomments 表 (c) 的外部查询也可以“使用索引”得到满足。 (MySQL 可能能够满足索引中的 ORDER BY，而不需要排序操作。(您需要检查 EXPLAIN 输出以查看 Extra 中是否出现“Using filesort”专栏。)

此外，这些覆盖索引可能会略微提高查询的性能。 (如果在不访问基础表中的页面的情况下从索引满足查询，则 EXPLAIN 输出将显示“使用索引”。)

  ON photos (photoid, path, smallfile, bigfile)

  ON user (userid, username)

每个单独列上的大多数单独索引都是无用的；这些索引中的任何一个都不太可能被使用，因此它们所做的只是消耗资源。