用例:

我有桌子,可以说:“制造商”

manuf_code  manuf_display_name  record_status  record_timestamp

----------  -------------------  ------------  ----------------
M000001      Sam                      N        2017-09-13 12:13:16
M000002      JII                      N        2017-09-13 15:13:15
M000002      JII                      U        2017-09-13 17:16:35
M000003      Sun                      N        2017-09-13 18:54:16
M000004      NG-Graphics              N        2017-09-13 19:13:15
M000004      NG-Graphics              U        2017-09-14 20:16:50
M000004      NG-Graphics              U        2017-09-14 09:13:25
M000005      HewNett                  N        2017-09-15 10:24:19
M000006      HewNett                  N        2017-09-15 10:24:19
M000007      HewNett                  N        2017-09-15 10:24:19
M000007      HewNett                  U        2017-09-15 15:10:16
M000007      HewNett                  U        2017-09-17 21:35:19
M000007      HewNett                  U        2017-09-17 21:37:26



当用户创建新的制造商时,详细信息位于表中,record_status为“ N”。
当用户更新现有制造商时,该制造商ID的行将用record_status更新为“ U”


现在,每个制造商可以拥有大约7-10百万个这样的条目:


状态为“ N”的单个条目
状态为“ U”的多个条目


要求:我需要获取每个制造商的最新条目。

我的查询:

SELECT m.manuf_code
     , m.manuf_display_name
     , m.record_timestamp
     , m.record_status
  FROM manufacturers m
  JOIN
     ( SELECT manuf_code
           , MAX(record_timestamp) AS maxdate
        FROM manufacturers
           WHERE record_status = 'N' OR record_status = 'U'
         GROUP
          BY manuf_code) mn
    ON m.manuf_code = mn.manuf_code
   AND m.record_timestamp = mn.maxdate


我更喜欢Join子查询,因为前者查询速度更快,可获取700万条数据。

但是,我需要更快地完成这项工作,因为在获取了这么多数据之后,我什至不得不用新的record_status插入同一表中的相同数据。

请提出建议。

编辑:

CREATE TABLE `manufacturers` (
  `manuf_code` varchar(20) NOT NULL,
  `record_status` varchar(1) NOT NULL,
  `manuf_display_name` varchar(50) NOT NULL,
  `record_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`manuf_code`, `record_update_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


说明:

新条目将具有状态->'N'
现有条目的更新将具有状态->'U'
而已。查询应该是最新的。

针对需求的另一种情况是,我们获取每条记录的所有最新条目,并将状态设置为“ L”,然后再次插入

最佳答案

首先解决了紧迫的问题,然后讨论了替代设计:

分组最大

这是一个“成组最大”问题。对于数百万的行表,典型的查询速度很慢,所有查询都涉及全表扫描。要对此进行改进,请参见http://mysql.rjweb.org/doc.php/groupwise_max

历史与当前

另一种方法是保留2个表:


History动作;这是您目前拥有的。它大多是INSERTed进入的。
每个项目的Current状态。这将是微不足道的。主要是UPDATEd。或者更好的是,使用INSERT...ON DUPLICATE KEY UPDATE...,以便可以插入新项目而无需额外的语句。


您说“用户创建/更新时...”。这是如何进行的?我希望他们不要发布SQL语句。我建议您考虑一些子例程(在客户端代码中)或存储过程(在MySQL中)。这样,您可以向用户隐藏两个表等的详细信息。

批量上传

你说大量的插入/更新/等都提供了吗?将其加载到临时表(CREATE TEMPORARY或您TRUNCATE并永久使用). Then write a relatively small number of SQL statements to combine the data to put into Current and shovel (mostly intact) into History`的永久表)中。

10-08 13:22
查看更多