mysql - 表之间的内连接，耗时

Question

我认为我的 SQL 查询花费的时间太长，现在看起来大约需要 30 秒。我有两个表，Record 和 Mainrecord。我想获取有关列金额中包含 0 的所有 Mainrecords 的信息，并且在这种情况下获取具有相同 Id 且其自身金额中除 0 以外的内容的最新记录。我有大约 30,000 条主记录和大约 3,000,000 条记录。我想这可以通过某种方式进行优化，但我无法弄清楚。

SELECT * 
FROM (
    mainrecord
    INNER JOIN (
        SELECT tt.* 
        FROM record tt 
        INNER JOIN (
            SELECT Max(record.timestamp) AS maxtimestamp, id 
            FROM record 
            WHERE record.amount <> 0 
            GROUP BY record.id
        ) AS rec 
        ON tt.id = rec.id AND tt.timestamp = rec.maxtimestamp
    ) AS t
    ON mainrecord.id = t.id
)
WHERE amount = 0 

解释:

+----+-------------+------------+-------------+---------------+-----------+---------+---------------------+---------+----------+---------------------------------------+
| id | select_type |   table    |    type     | possible_keys |    key    | key_len |         ref         |  rows   | filtered |                 Extra                 |
+----+-------------+------------+-------------+---------------+-----------+---------+---------------------+---------+----------+---------------------------------------+
|  1 | PRIMARY     | Mainrecord | index_merge | Id,Amount     | Amount    |     768 | \N                  |    2850 |   100,00 | Using sort_union(Amount); Using where |
|  1 | PRIMARY     | <derived3> | ref         | key0          | key0      |     768 | db.Mainrecord.Id    |      53 |   100,00 | Using where                           |
|  1 | PRIMARY     | tt         | ref         | Id,timestamp  | timestamp |       9 | record.maxtimestamp |      1  |   100,00 | Using where                           |
|  3 | DERIVED     | Record     | index       | Amount        | Id        |     768 | \N                  | 3185669 |    50,03 | Using where                           |
+----+-------------+------------+-------------+---------------+-----------+---------+---------------------+---------+----------+---------------------------------------+

Answer 1

这个怎么办？从子查询中提取分组

更新:

SELECT * 
FROM mainrecord mr
     INNER JOIN (SELECT Max(record.timestamp) AS maxtimestamp, id 
        FROM   record 
        WHERE  record.amount <> 0 
        GROUP  BY record.id) AS max_r ON max_r.id=mr.id
    INNER JOIN record r ON mr.id = r.id
WHERE  mr.amount = 0 
  AND r.timestamp = max_r.maxtimestamp