用例:
我有桌子,可以说:“制造商”
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`的永久表)中。