改进性能或重新设计“每个组最多n次”的mysql查询

I'm using MySQL5 and I currently have a query that gets me the info I need but I feel like it could be improved in terms of performance.

我正在使用MySQL5，目前我有一个查询，可以得到我需要的信息，但我觉得它可以在性能方面得到改进。

Here's the query I built (roughly following this guide) :

下面是我构建的查询(大致遵循本指南):

SELECT d.*, dc.date_change, dc.cwd, h.name as hub
FROM livedata_dom AS d
      LEFT JOIN ( SELECT dc1.*
        FROM livedata_domcabling as dc1
        LEFT JOIN livedata_domcabling AS dc2
        ON dc1.dom_id = dc2.dom_id AND dc1.date_change < dc2.date_change
        WHERE dc2.dom_id IS NULL
        ORDER BY dc1.date_change desc) AS dc ON (d.id = dc.dom_id)
      LEFT JOIN livedata_hub AS h ON (d.id = dc.dom_id AND dc.hub_id = h.id)
WHERE d.cluster = 'localhost'
GROUP BY d.id;

EDIT: Using ORDER BY + GROUP BY to avoid getting multiple dom entries in case 'domcabling' has an entry with null date_change and another one with a date for the same 'dom'.

编辑:使用ORDER BY + GROUP BY避免获得多个dom条目，以防“domcabling”的条目具有null date_change，而另一个条目具有相同的“dom”的日期。

I feel like I'm killing a mouse with a bazooka. This query takes more than 3 seconds with only about 5k entries in 'livedata_dom' and 'livedata_domcabling'. Also, EXPLAIN tells me that 2 filesorts are used:

我觉得我在用火箭筒杀死一只老鼠。这个查询需要超过3秒，在“livedata_dom”和“livedata_domcabling”中只有大约5k个条目。另外，解释告诉我2个文件共享:

+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys               | key                         | key_len | ref             | rows | Extra                                        |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-----------------+------+----------------------------------------------+
|  1 | PRIMARY     | d          | ALL    | NULL                        | NULL                        | NULL    | NULL            |    3 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                        | NULL                        | NULL    | NULL            |    3 |                                              |
|  1 | PRIMARY     | h          | eq_ref | PRIMARY                     | PRIMARY                     | 4       | dc.hub_id       |    1 |                                              |
|  2 | DERIVED     | dc1        | ALL    | NULL                        | NULL                        | NULL    | NULL            |    4 | Using filesort                               |
|  2 | DERIVED     | dc2        | ref    | livedata_domcabling_dc592d9 | livedata_domcabling_dc592d9 | 4       | live.dc1.dom_id |    2 | Using where; Not exists                      |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-----------------+------+----------------------------------------------+

How could I change this query to make it more efficient?

如何更改此查询以使其更有效?

Using the dummy data (provided below), this is the expected result:

使用虚拟数据(提供如下)，这是预期结果:

+-----+-------+---------+--------+----------+------------+-----------+---------------------+------+-----------+
| id  | mb_id | prod_id | string | position | name       | cluster   | date_change         | cwd  | hub       |
+-----+-------+---------+--------+----------+------------+-----------+---------------------+------+-----------+
| 249 | 47    | 47      |     47 |       47 | SuperDOM47 | localhost | NULL                | NULL | NULL      |
| 250 | 48    | 48      |     48 |       48 | SuperDOM48 | localhost | 2014-04-16 05:23:00 | 32A  | megahub01 |
| 251 | 49    | 49      |     49 |       49 | SuperDOM49 | localhost | NULL                | 22B  | megahub01 |
+-----+-------+---------+--------+----------+------------+-----------+---------------------+------+-----------+

Basically I need 1 row for every 'dom' entry, with

基本上，每个“dom”条目都需要一行

the 'domcabling' record with the highest date_change
- if record does not exist, I need null fields
- 如果记录不存在，我需要空字段
- ONE entry may have a null date_change field per dom (null datetime field considered older than any other datetime)
- 一个条目可能每个dom都有一个null date_change字段(null datetime字段被认为比其他任何datetime字段都要古老)
如果记录不存在，则“domcabling”记录中最高的date_change，我需要null字段一个条目可能有一个null date_change字段/ dom (null datetime字段比任何其他datetime都要老)
the name of the 'hub', when a 'domcabling' entry is found, null otherwise
当找到“domcabling”条目时，“hub”的名称为空

CREATE TABLE + dummy INSERT for the 3 tables:

为3个表创建表格+虚拟插入:

livedata_dom (about 5000 entries)

livedata_dom(约5000条)

CREATE TABLE `livedata_dom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mb_id` varchar(12) NOT NULL,
  `prod_id` varchar(8) NOT NULL,
  `string` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `cluster` varchar(9) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mb_id` (`mb_id`),
  UNIQUE KEY `prod_id` (`prod_id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `livedata_domgood_string_7bff074107b0e5a0_uniq` (`string`,`position`,`cluster`)
) ENGINE=InnoDB AUTO_INCREMENT=5485 DEFAULT CHARSET=latin1;

INSERT INTO `livedata_dom` VALUES (251,'49','49',49,49,'SuperDOM49','localhost'),(250,'48','48',48,48,'SuperDOM48','localhost'),(249,'47','47',47,47,'SuperDOM47','localhost');

livedata_domcabling (about 10000 entries and growing slowly)

livedata_domcabling(大约10000个条目并且增长缓慢)

CREATE TABLE `livedata_domcabling` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dom_id` int(11) NOT NULL,
  `hub_id` int(11) NOT NULL,
  `cwd` varchar(3) NOT NULL,
  `date_change` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `livedata_domcabling_dc592d9` (`dom_id`),
  KEY `livedata_domcabling_4366aa6e` (`hub_id`),
  CONSTRAINT `dom_id_refs_id_73e89ce0c50bf0a6` FOREIGN KEY (`dom_id`) REFERENCES `livedata_dom` (`id`),
  CONSTRAINT `hub_id_refs_id_179c89d8bfd74cdf` FOREIGN KEY (`hub_id`) REFERENCES `livedata_hub` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5397 DEFAULT CHARSET=latin1;

INSERT INTO `livedata_domcabling` VALUES (1,251,1,'22B',NULL),(2,250,1,'33A',NULL),(6,250,1,'32A','2014-04-16 05:23:00'),(5,250,1,'22B','2013-05-22 00:00:00');

livedata_hub (about 100 entries)

livedata_hub(约100条)

CREATE TABLE `livedata_hub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(14) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=latin;

INSERT INTO `livedata_hub` VALUES (1,'megahub01');

2 个解决方案

#1

Try this rewriting (tested in SQL-Fiddle:

尝试这种重写(在SQL-Fiddle测试:

SELECT 
    d.*, dc.date_change, dc.cwd, h.name as hub
FROM 
    livedata_dom AS d
  LEFT JOIN 
    livedata_domcabling as dc
        ON dc.id =
           ( SELECT id
             FROM livedata_domcabling AS dcc
             WHERE dcc.dom_id = d.id 
             ORDER BY date_change DESC 
               LIMIT 1
          ) 
  LEFT JOIN 
    livedata_hub AS h 
        ON dc.hub_id = h.id
  WHERE 
     d.cluster = 'localhost' ;

And index on (dom_id, date_change) would help efficiency.

而索引(dom_id, date_change)将有助于提高效率。

I'm not sure about the selectivity of d.cluster = 'localhost' (how many rows of the livedata_dom table match this condiiton?) but adding an index on (cluster) might help as well.

我不确定d的选择性。cluster = 'localhost' (livedata_dom表中有多少行符合这个条件?)但在(cluster)上添加索引也可能有帮助。

#2

set @rn := 0, @dom_id := 0;
select d.*, dc.date_change, dc.cwd, h.name as hub
from
    livedata_dom d
    left join (
        select
            hub_id, date_change, cwd, dom_id,
            if(@dom_id = dom_id, @rn := @rn + 1, @rn := 1) as rn,
            @dom_id := dom_id as dm_id
        from
            livedata_domcabling
        order by dom_id, date_change desc
    ) dc on d.id = dc.dom_id
    left join
    livedata_hub h on h.id = dc.hub_id
where rn = 1 or rn is null
order by dom_id

The data you posted does not have the dom_id 249. And the #250 has one null date so it comes first. So your result does not reflect what I understand form your question.

您发布的数据没有dom_id 249。而#250有一个空日期，所以它首先出现。所以你的结果并不能反映我对你问题的理解。

#1