order by before group by 的 MySQL 查询性能改进

Question

下面是我用来获取每个 serverID 的最新记录的查询，不幸的是，这个查询确实需要无穷无尽的处理。根据下面的 stackoverflow 问题，它应该是一个非常快速的解决方案。有什么方法可以加快此查询的速度，还是我必须将其拆分？ (首先获取所有 serverID，然后获取每个服务器的最后一条记录) Retrieving the last record in each group

SELECT s1.performance, s1.playersOnline, s1.serverID, s.name, m.modpack, m.color
    FROM stats_server s1 
    LEFT JOIN stats_server s2
        ON (s1.serverID = s2.serverID AND s1.id < s2.id)
    INNER JOIN server s
        ON s1.serverID=s.id
    INNER JOIN modpack m
        ON s.modpack=m.id
    WHERE s2.id IS NULL
    ORDER BY m.id
15 rows in set (34.73 sec)

解释:

+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

示例输出:

+-------------+---------------+----------+---------------+-------------------------+--------+
| performance | playersOnline | serverID | name          | modpack                 | color  |
+-------------+---------------+----------+---------------+-------------------------+--------+
|          99 |            18 |       15 | hub           | Lobby                   | AAAAAA |
|          98 |            12 |       10 | horizons      | Horizons                | AA00AA |
|          97 |             6 |       11 | m_lobby       | Monster                 | AA0000 |
|          99 |             1 |       12 | m_north       | Monster                 | AA0000 |
|          86 |            10 |       13 | m_south       | Monster                 | AA0000 |
|          87 |            17 |       14 | m_east        | Monster                 | AA0000 |
|          98 |            10 |       16 | m_west        | Monster                 | AA0000 |
|          84 |             7 |        5 | tppi          | Test Pack Please Ignore | 55FFFF |
|          95 |            15 |        6 | agrarian_plus | Agrarian Skies          | 00AA00 |
|          98 |            23 |        7 | agrarian2     | Agrarian Skies          | 00AA00 |
|          74 |            18 |        9 | agrarian      | Agrarian Skies          | 00AA00 |
|          97 |            37 |       17 | agrarian3     | Agrarian Skies          | 00AA00 |
|          99 |            17 |        3 | bteam_pvp     | Attack of the B-Team    | FFAA00 |
|          73 |            44 |        8 | bteam_pve     | Attack of the B-Team    | FFAA00 |
|          93 |            11 |        4 | crackpack     | Crackpack               | EFEFEF |
+-------------+---------------+----------+---------------+-------------------------+--------+
15 rows in set (38.49 sec)

示例数据:

http://www.mediafire.com/download/n0blj1io0c503ig/mym_bridge.sql.bz2

Answer 1

编辑

好的，我解决了。以下是显示您的原始慢速查询的扩展行:

这是一个使用 MAX() 和 GROUP BY 的快速查询，它给出了相同的结果。请亲自尝试。

SELECT       s1.id 
            ,s1.performance
            ,s1.playersOnline
            ,s1.serverID 
            ,s.name 
            ,m.modpack
            ,m.color
FROM        stats_server s1
JOIN        (
    SELECT      MAX(id) as 'id'
    FROM        stats_server 
    GROUP BY    serverID
            ) AS s2
ON          s1.id = s2.id
JOIN        server s
ON          s1.serverID = s.id
JOIN        modpack m 
ON          s.modpack = m.id
ORDER BY    m.id