主页 / dba / 问题 / 102814
Accepted
LefterisL
Asked: 2015-05-30 09:03:54 +0800 CST

MySQL JOIN 两个表并获得最新结果

  • 772

我想加入两个表并在一个表中从这两个表中的每一个中获取最新结果。我也有点担心速度，因为表格增长得有点快。每天接近 60-70k 条记录。稍后我将进行分区，但这是另一个问题。现在我有一个包含devices信息的主表。

+--------+-----------+---------+
|     id |    Name   | type    |
+--------+-----------+---------+
|      1 | Oh        | A       |
|      2 | This      | A       |
|      3 | Is        | B       |
|      4 | Hard      | A       |
+--------+-----------+---------+

根据类型，他们在不同的表中有一些数据类型 A 是

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  noise  |
+--------+-----------+------------------+---------+---------+
|      1 | 1         | 2012-10-23 07:50 | foo     |   10    |
|      2 | 1         | 2012-10-23 16:59 | bar     |   12    |
|      3 | 2         | 2012-10-23 15:11 | bar     |   0     |
|      4 | 4         | 2012-10-23 23:23 | foo     |   25    |
+--------+-----------+------------------+---------+---------+

B型是

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |
+--------+-----------+------------------+---------+---------+
|      1 | 3         | 2012-10-23 04:50 | foo     |  1000   |
|      2 | 3         | 2012-10-23 05:59 | bar     |  450    |
|      3 | 3         | 2012-10-23 09:11 | bar     |  980    |
|      4 | 3         | 2012-10-23 10:23 | foo     |   0     |
+--------+-----------+------------------+---------+---------+

我一直在努力寻找一个查询，最终得到这样的结果

+--------+-----------+------------------+---------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |   noise |
+--------+-----------+------------------+---------+---------+---------+
|      1 | 1         | 2012-10-23 16:59 | bar     |  12     |         |
|      2 | 2         | 2012-10-23 15:11 | bar     |  0      |         |
|      3 | 3         | 2012-10-23 10:23 | foo     |         |    0    |
|      4 | 4         | 2012-10-23 23:23 | foo     |  25     |         |
+--------+-----------+------------------+---------+---------+---------+

使用下面的查询不好，因为我得到两列stats_time

SELECT devices.id AS id, A.stats_time , B.stats_time
FROM devices 
LEFT JOIN A ON devices.id = A.device_id 
LEFT JOIN B ON devices.id = B.device_id 
GROUP BY devices.id

在我最终为设备类型使用不同的表之前，我曾经通过以下方式获得结果，但最终变得非常缓慢

SELECT *
FROM (
    SELECT *
    FROM A
    ORDER BY stats_time DESC, id ASC
) AS d
RIGHT JOIN devices ON A.device_id = devices.id
GROUP BY devices.id
mysql performance

2 个回答

  1. 我在上面的评论中指出，这似乎是一种超类型-子类型关系的情况，但是，由于更改数据库结构可能是超出此问题范围的决定，因此我将专注于提供解决方案你现在的情况。

    然后，经过几次编辑，我决定包含一些DDL陈述和我对您的数据库结构的假设的简要描述，希望通过这种方式，我的答案及其涉及的查询将更容易理解。

    了解情况和假设DDL

    正如我所理解的那样，type_a可能type_b是两种不同的类型，reading或者measurement您正在为每种类型收集device.

    这样，虽然我不完全确定type_a.type_a_idand列的含义，但我假设它们在每个相应的表中type_b.type_b_id都是某种sequential_number或row_number或。record_identifier同样，列type_a.stats_time和type_b.stats_time是device发布某种reading.

    我还假设type_a.device_id并且type_b.device_id是引用device表的外键，而表又将列device.device_id用作某种sequential_number或row_number或record_identifier您已定义为主键的列。

    在描述了我对事态的理解之后，请注意我并不是在暗示这是最佳结构（因为我自然不熟悉真实场景，它可能缺乏规范化、完整性等），我是只是根据您提供的数据样本和查询做出一些假设，以便为您的具体情况提供可能的解决方案。所以，这里是推测DDL：

    CREATE TABLE device
(
    device_id INT      NOT NULL AUTO_INCREMENT,
    name      CHAR(30) NOT NULL,
    type      CHAR(1)  NOT NULL,
    PRIMARY KEY (device_id),
    UNIQUE INDEX uix_name (name) 
);

CREATE TABLE type_a
(
    type_a_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL ,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    noise      INT      NOT NULL,
   PRIMARY KEY (type_a_id),
   CONSTRAINT FOREIGN KEY fk_type_a_device (device_id) 
   REFERENCES device(device_id),
   UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

CREATE TABLE type_b
(
    type_b_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    `signal`   INT      NOT NULL,
    PRIMARY KEY (type_b_id),
    CONSTRAINT FOREIGN KEY fk_type_b_device (device_id) 
    REFERENCES device(device_id),
    UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

-- Some ‘device’ INSERTS...

INSERT INTO device (name, type) VALUES ('First device', 'A');
INSERT INTO device (name, type) VALUES ('Second device', 'A');

-- ... And then, some ‘type_a’ and ‘type_b’ INSERTS 
-- in order to have some sample data for retrieving.

INSERT INTO type_a (device_id, stats_time, `status`, noise) 
VALUES (1, STR_TO_DATE('06/01/2015 08:10:01 AM', '%c/%e/%Y %r'), 'Foo', 123);

INSERT INTO type_a (device_id, stats_time, `status`, noise) 
VALUES (1, STR_TO_DATE('04/04/2015 03:07:34 PM', '%c/%e/%Y %r'), 'Bar', 456);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`) 
VALUES (2, STR_TO_DATE('03/04/2015 02:08:15 PM', '%c/%e/%Y %r'), 'Boo', 789);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`) 
VALUES (2, STR_TO_DATE('05/07/2015 04:03:12 PM', '%c/%e/%Y %r'), 'Far', 852);

    初步提案

    然后，这是我提交的第一个查询，在保留原始想法的同时，已重新格式化并适应了DDL后来添加的结构：

    SELECT DE.device_id,
       COALESCE(TA.type_a_id, TB.type_b_id)   AS type_id,
       DE.name,
       DE.type,    
       COALESCE(TA.stats_time, TB.stats_time) AS stats_time,
       COALESCE(TA.status, TB.status)         AS `status`,
       COALESCE(TA.noise, 0)                  AS noise,
       COALESCE(TB.signal, 0)                 AS `signal`
  FROM device            DE
  LEFT OUTER JOIN type_a TA
    ON TA.device_id    = DE.device_id
  LEFT OUTER JOIN type_b TB
    ON TB.device_id    = DE.device_id
ORDER BY stats_time DESC;

    如您所见，我正在使用COALESCE()函数，仅当列type_a.stats_time包含NULL值时，才会“打印”列的值type_b.stats_time，并以类似的方式处理type_a.statusandtype_b.status列。

    后续提案

    第一种方法

    现在，重新阅读您的问题并经过一些评论互动后，我知道您真正想要的是获得一个仅包含两行的结果集——一行包含与最新值对应的列，另一行包含与最新值type_a.stats_time相关的列type_b.stats_time——。因此，我建议您使用以下查询，该查询也从上述DDL建议中检索数据：

    (SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,      
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id  = DE.device_id
  WHERE TA.stats_time = (SELECT MAX(stats_time) 
                           FROM type_a))

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,      
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id  = DE.device_id
  WHERE TB.stats_time = (SELECT MAX(stats_time) 
                           FROM type_b))

ORDER BY stats_time DESC;

    请注意UNION运算符的使用，它的目的是将（基于最新列值，通过WHERE 子句中的子查询中的MAX()函数获得）的最新行与最新行组合在一个结果集中行中（同样，基于最新的列值，也通过 WHERE 子查询中的子查询中的 MAX() 函数获得）。type_astats_timetype_bstats_time

    第二种方法

    您还可以尝试使用此备用查询，它使用ORDER BY和LIMIT子句根据stats_time每个相应 SELECT 语句中的列对每个组合结果集进行排序。

    (SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,      
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id = DE.device_id
  ORDER BY TA.stats_time DESC LIMIT 1)

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,      
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id = DE.device_id
  ORDER BY TB.stats_time DESC LIMIT 1)

ORDER BY stats_time DESC;

    一旦您比较了所有建议方法的性能，就很容易定义哪一种最适合您的需求。此外，如果其中一个解决了您的问题，那么您可以将其设置为VIEW，这样将来的数据检索将更容易获得。

    关于查询的速度性能方面，您可以通过特别注意 apropiate 列中的索引定义来开始细化这些问题，例如type_a.stats_time和type_b.stats_time。

    • 3
  2. Best Answer

    我认为它分为两个步骤：

    1. 为每个设备构建仅包含最新信号（或噪声）的表
    2. JOIN或UNION两张桌子。

    步骤 1 是groupwise max的变体：

    SELECT  device_id, stats_time, status, noise -- The desired columns
    FROM  ( SELECT  @prev := '' ) init
    JOIN  ( SELECT
                device_id != @prev AS first, -- `device_id` is the 'GROUP BY'
                @prev := device_id,          -- the 'GROUP BY'
                device_id, stats_time, status, noise -- Also the desired columns
            FROM  TableA -- The table
            ORDER BY  device_id  DESC, -- The 'GROUP BY'
                      stats_time DESC  -- to get latest
      ) x
    WHERE  first;

    这可能对性能有益：

    INDEX(device_id, stats_time)

    TableB和同上signal。手动运行它们，看看我是否正确。

    您的示例没有显示两者signal和都noise存在的情况device_id。我会假设情况确实如此，因此UNION：

    第2步：

    SELECT device_id, stats_time, status, signal, noise
    FROM
    ( SELECT device_id, stats_time, status, signal, '' AS noise
        ... (the rest of the signal query)
    )
    UNION ALL
    ( SELECT device_id, stats_time, status, '' AS signal, noise
        ... (the rest of the noise query)
    );
    • 0

相关问题