mysql - 如何快速从关系 mysql 表中选择 max(timestamp)

Question

我们正在开发一个工单系统，我们希望在仪表板上显示工单及其最新状态。我们有两张 table 。第一个表用于工单本身，第二个表用于个人编辑。

系统已经在运行，但仪表板的性能非常糟糕(约 1300 张票需要 6 秒)。起初，我们使用了一个为每张票选择“where timestamp = (select max(Timestamp))”的语句。在第二步中，我们创建了一个 View ，其中仅包含每张工单的最新时间戳，但我们无法将正确的状态也包含到该 View 中。

所以主要的问题可能是，我们无法构建一个表，在其中为每张工单选择最新的 ins_date 以及最新状态。

简化后的数据库如下所示:

CREATE TABLE `ticket` (
  `id` int(10) NOT NULL,
  `betreff` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ticket_relation` (
  `id` int(11) NOT NULL,
  `ticket` int(10) NOT NULL,
  `info` varchar(10000) DEFAULT NULL,
  `status` int(1) NOT NULL DEFAULT '0',
  `ins_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `ins_user` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `ticket` (`id`, `betreff`) VALUES
(1, 'Technische Frage'),
(2, 'Ticket 2'),
(3, 'Weitere Fragen');

INSERT INTO `ticket_relation` (`id`, `ticket`, `info`, `status`, `ins_date`, `ins_user`) VALUES
(1, 1, 'Betreff 1', 0, '2019-05-28 11:02:18', 123),
(2, 1, 'Betreff 2', 3, '2019-05-28 12:07:36', 123),
(3, 2, 'Betreff 3', 0, '2019-05-29 06:49:32', 123),
(4, 3, 'Betreff 4', 1, '2019-05-29 07:44:07', 123),
(5, 2, 'Betreff 5', 1, '2019-05-29 07:49:32', 123),
(6, 2, 'Betreff 6', 3, '2019-05-29 08:49:32', 123),
(7, 3, 'Betreff 7', 2, '2019-05-29 09:49:32', 123),
(8, 2, 'Betreff 8', 1, '2019-05-29 10:49:32', 123),
(9, 3, 'Betreff 9', 2, '2019-05-29 11:49:32', 123),
(10, 3, 'Betreff 10', 3, '2019-05-29 12:49:32', 123);

我创建了一个 SQL Fiddle:http://sqlfiddle.com/#!9/a873b6/3前三个语句是无法正常工作或运行速度太慢的尝试。最后一个是我认为的关键，但我不明白，为什么这会导致状态错误。

尝试为每张票创建具有最新 ins_date 和状态的表:

SELECT
  ticket, status, MAX(ins_date) as max_date 
FROM 
  ticket_relation 
GROUP BY 
  ticket
ORDER BY 
  ins_date DESC;

此查询获取每张票的正确(最新)ins_date，但不是最新状态:

+--------+--------+----------------------+
| ticket | status | max_date             |
+--------+--------+----------------------+
| 3      | 1      | 2019-05-29T12:49:32Z |
+--------+--------+----------------------+
| 2      | 0      | 2019-05-29T10:49:32Z |
+--------+--------+----------------------+
| 1      | 0      | 2019-05-28T12:07:36Z |
+--------+--------+----------------------+

预期的输出是这样的:

+--------+--------+----------------------+
| ticket | status | max_date             |
+--------+--------+----------------------+
| 3      | 3      | 2019-05-29T12:49:32Z |
+--------+--------+----------------------+
| 2      | 1      | 2019-05-29T10:49:32Z |
+--------+--------+----------------------+
| 1      | 3      | 2019-05-28T12:07:36Z |
+--------+--------+----------------------+

有没有一种有效的方法来为票表中的每张票选择最新的时间戳和状态？

Answer 1

其他方法是认为过滤而不是 GROUPing..

查询

SELECT 
   ticket_relation_1.ticket
 , ticket_relation_1.status 
 , ticket_relation_1.ins_date
FROM 
 ticket_relation AS ticket_relation_1
LEFT JOIN
 ticket_relation AS ticket_relation_2
ON
   ticket_relation_1.ticket = ticket_relation_2.ticket
 AND
   ticket_relation_1.ins_date < ticket_relation_2.ins_date
 WHERE 
  ticket_relation_2.id IS NULL
ORDER BY 
 ticket_relation_1.id DESC

结果

| ticket | status | ins_date            |
| ------ | ------ | ------------------- |
| 3      | 3      | 2019-05-29 12:49:32 |
| 2      | 1      | 2019-05-29 10:49:32 |
| 1      | 3      | 2019-05-28 12:07:36 |

参见 demo

此查询需要索引 KEY(ticket, ins_date, id) 以获得最佳性能..