大数据量下的查找最新的几条数据的通用方法

xuexiaodong2009
博客专家认证
2012-09-04 09:20:14
加精
原文地址
http://blog.csdn.net/xuexiaodong2009/article/details/7938679
由于项目需要，需要获取一组数据的的最新一条数据，表结构如下：

[sql] view plaincopy
CREATE TABLE [dbo].[WUSU_SUOLITest_Table](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ReceiveTime] [datetime] NULL,
[GroupID] [bigint] NOT NULL,
[DataValue] [float] NULL,
[SensorCode] [char](10) NOT NULL,
)
在这个表上只有两种操作，插入和查询，没有删除和更新。而且同一种设备，随着id列的变大，ReceiveTime也随着变大。
每一个不同的SensorCode代表了一个设备，目前有50个设备，每30秒上报一次数据，ReceiveTime代表上报数据的时间，现在需要获取每一个设备最新一次的数据，
开始我使用如下的查询语句：

[sql] view plaincopy
select * from WUSU_SUOLITest_Table where id in (select max(id) from WUSU_SUOLITest_Table group by SensorCode )
在数据量比较小时，是没有问题的，但数据量特别大时，这种方式，目前一天的数据就超过了14万，有很大的延时，即使在id上有聚集索引，SensorCode上使用了分区，依然没有多大作用。时间主要花费到了group by上。
实在想不多到什么好的而解决方法，就只能在此表上创建一个触发器，每次插入数据时就把最新的数据放在了一个临时表，又有临时表最多只有50条数据，速度当然就很好了。

[sql] view plaincopy
create TRIGGER [dbo].[UpdateWUSU_LastOriginalDataSUOLI]
ON [dbo].[WUSU_SUOLITest_Table]
AFTER INSERT
AS
BEGIN
declare @SensorCode char(10), @DataValue float ,@ReceiveTime datetime ,@GroupID bigint

select @SensorCode=SensorCode,@DataValue=DataValue,@ReceiveTime=ReceiveTime,@GroupID=GroupID from inserted
update WUSU_LastOriginalData set DataValue=@DataValue,ReceiveTime=@ReceiveTime,GroupID=@GroupID
where SensorCode=@SensorCode
END
当然这是为了获取各种设备最新的一条数据，如果要获取最新的两条数据，最多也就是100条记录，一次类推，只需要把上边的触发器修改一下就可以。

但还有没有更好的方式，在不修改表结构的情况下？目前还没有想到。
...全文
5757 122 打赏 收藏 转发到动态 举报
写回复
用AI写文章
122 条回复
切换为时间正序
请发表友善的回复…
发表回复
ChangeMyself2012 2012-10-15
  • 打赏
  • 举报
 回复
好贴 学习下！
Barton 2012-10-15
  • 打赏
  • 举报
 回复
如果是我做，我也会选择跟100楼的做法，即insert正式表之后，同步更新临时表，这样临时表始终只保持有50多条记录。
Barton 2012-10-15
  • 打赏
  • 举报
 回复
CREATE TABLE [dbo].[WUSU_SUOLITest_Table](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ReceiveTime] [datetime] NULL,
[GroupID] [bigint] NOT NULL,
[DataValue] [float] NULL,
[SensorCode] [char](10) NOT NULL,
)

select abs(checksum(newid()))%10


declare @i int=1
while @i<=50
begin
insert into [WUSU_SUOLITest_Table](ReceiveTime,GroupID,DataValue,SensorCode)
select dateadd(second,number*10,getdate()),@i,abs(checksum(newid())),right('0000'+cast(@i as varchar(30)),4)
--,number
from master.dbo.spt_values
where type='p' and number>1
waitfor delay '00:00:01.200'
set @i=@i+1
end


select count(*) from [WUSU_SUOLITest_Table]--102300

alter table [WUSU_SUOLITest_Table]
add constraint PK_WUSU_SUOLITest_Table primary key (ID)


select top 100 * from [WUSU_SUOLITest_Table]
create index IX_WUSU_SUOLITest_Table_SensorCode on WUSU_SUOLITest_Table(SensorCode)

set statistics io on
set statistics time on
set showplan_text on
--set showplan_text off
;with cte_id
as
(
select max(id) as id
from WUSU_SUOLITest_Table
group by SensorCode
)

select * from WUSU_SUOLITest_Table a inner join cte_id b on a.ID=b.id
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1007]) WITH UNORDERED PREFETCH)
|--Stream Aggregate(GROUP BY:([ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode]) DEFINE:([Expr1005]=MAX([ASBU].[dbo].[WUSU_SUOLITest_Table].[ID])))
| |--Index Scan(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[IX_WUSU_SUOLITest_Table_SensorCode]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[PK_WUSU_SUOLITest_Table] AS [a]), SEEK:([a].[ID]=[Expr1005]) ORDERED FORWARD)

CPU 时间 = 31 毫秒，占用时间 = 40 毫秒。

;with cte
as
(
select ROW_NUMBER()over(partition by sensorcode order by id desc) as rownum,id
from WUSU_SUOLITest_Table
)
select * from cte where rownum=1
|--Parallelism(Gather Streams)
|--Filter(WHERE:([Expr1003]=(1)))
|--Sequence Project(DEFINE:([Expr1003]=row_number))
|--Segment
|--Sort(ORDER BY:([ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode] ASC, [ASBU].[dbo].[WUSU_SUOLITest_Table].[ID] DESC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode]))
|--Index Scan(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[IX_WUSU_SUOLITest_Table_SensorCode]))
CPU 时间 = 358 毫秒，占用时间 = 168 毫秒。

select *
from WUSU_SUOLITest_Table a
where not exists(select top 1 * from WUSU_SUOLITest_Table b where a.SensorCode=b.SensorCode and a.id<b.ID)

|--Parallelism(Gather Streams)
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([a].[ID], [a].[SensorCode], [Expr1004]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[PK_WUSU_SUOLITest_Table] AS [a]))
|--Index Seek(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[IX_WUSU_SUOLITest_Table_SensorCode] AS [b]), SEEK:([b].[SensorCode]=[ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode] as [a].[SensorCode] AND [b].[ID] > [ASBU].[dbo].[WUSU_SUOLITest_Table].[ID] as [a].[ID]) ORDERED FORWARD)
CPU 时间 = 670 毫秒，占用时间 = 230 毫秒。



select * from WUSU_SUOLITest_Table where id in (select max(id) from WUSU_SUOLITest_Table group by SensorCode)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006], [Expr1008]) WITH UNORDERED PREFETCH)
|--Sort(DISTINCT ORDER BY:([Expr1006] ASC))
| |--Stream Aggregate(GROUP BY:([ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode]) DEFINE:([Expr1006]=MAX([ASBU].[dbo].[WUSU_SUOLITest_Table].[ID])))
| |--Index Scan(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[IX_WUSU_SUOLITest_Table_SensorCode]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[PK_WUSU_SUOLITest_Table]), SEEK:([ASBU].[dbo].[WUSU_SUOLITest_Table].[ID]=[Expr1006]) ORDERED FORWARD)
CPU 时间 = 46 毫秒，占用时间 = 47 毫秒。

;with cte_id
as
(
select max(id) as id
from WUSU_SUOLITest_Table
group by SensorCode
)
select * from cte_id a cross apply (select top 1 * from WUSU_SUOLITest_Table b where a.id=b.ID order by b.id desc) b

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003], [Expr1007]) WITH UNORDERED PREFETCH)
|--Stream Aggregate(GROUP BY:([ASBU].[dbo].[WUSU_SUOLITest_Table].[SensorCode]) DEFINE:([Expr1003]=MAX([ASBU].[dbo].[WUSU_SUOLITest_Table].[ID])))
| |--Index Scan(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[IX_WUSU_SUOLITest_Table_SensorCode]), ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([ASBU].[dbo].[WUSU_SUOLITest_Table].[PK_WUSU_SUOLITest_Table] AS [b]), SEEK:([b].[ID]=[Expr1003]) ORDERED FORWARD)
CPU 时间 = 31 毫秒，占用时间 = 46 毫秒。


建议楼主多看看实际的执行计划，基本上第一，四，五种的是一样的。row_number为什么慢，主要是因为要按照SensorCode，ID desc进行排序.exists慢主要是因为利用nested loops的时候需要聚集扫描ID
看上面的执行计划，是第一种是最好的写法，


fang1229 2012-10-10
  • 打赏
  • 举报
 回复
我只想说，其实时间列要比ID列好用，
在时间列上做聚集索引，然后在SensorCode和时间列上做复合索引吧
wawa_yu 2012-09-25
  • 打赏
  • 举报
 回复
标记，好好研究下，静心学习!
mohong_lin 2012-09-24
  • 打赏
  • 举报
 回复
试下对ID列和SensorCode列建组合聚簇索引。再不行，做物理分表！
skyeg 2012-09-24
  • 打赏
  • 举报
 回复
[Quote=引用 42 楼 的回复:]
单独建表，保存最新数据...
[/Quote]
正解。只需要另建一张50行的表，每个设备占一行。在插入历史表的同时更新50行的输出表即可。
为了得到时间性能，只有以空间为代价了。否则，那么频繁地插入及筛选，又费时间又没好处。
lvsong1988 2012-09-24
  • 打赏
  • 举报
 回复
看懂一点点 还是先收藏好了 感觉以后能用得到
专注or全面 2012-09-24
  • 打赏
  • 举报
 回复
其实楼主的问题就出在group by 上，那为什么不能对不同类别的数据分开放在不同的表中呢？

这里1填十几万的数据量其实并不是什么大问题，没必要做什么特殊处理。
goodwalkman 2012-09-23
  • 打赏
  • 举报
 回复
很好，学习了。
Q2565093493 2012-09-23
  • 打赏
  • 举报
 回复
二分查找
Leap 2012-09-23
  • 打赏
  • 举报
 回复
如涉及侵权内容,您的资源将被移除* 请勿上传小说、mp3、图片等与技术无关的内容.一旦发现将被删除* 请勿在未经授权的情况下上传任何涉及著作权侵权的资源，除非该资源完全由您个人创作* 点击上传资源即表示您确认该资源不违反资源分享的使用条款，并且您拥有该资源的所有版权或者上传资源的授权
joker981924 2012-09-23
  • 打赏
  • 举报
 回复
鼓励下楼主，赞美分享
skyfine 2012-09-22
  • 打赏
  • 举报
 回复
楼主的处理方式很好呀.至少我觉得是这样的.
无为剑士 2012-09-21
  • 打赏
  • 举报
 回复
好方法……学习了
gq198718 2012-09-21
  • 打赏
  • 举报
 回复
厉害，先收藏起来
l4kangaxx 2012-09-20
  • 打赏
  • 举报
 回复
这贴内容不错，非常值得收藏
Macular 2012-09-20
  • 打赏
  • 举报
 回复
趁楼主还不散分，果断MARK
第四个火枪手 2012-09-19
  • 打赏
  • 举报
 回复
学习了，多些楼主，保存此贴。
hrbhitxw 2012-09-19
  • 打赏
  • 举报
 回复
楼主本身的做法可以，个人更习惯在往大表里插入之后，再通过程序更新临时表，不使用触发器
加载更多回复（79）
大数据量报表系统的改进方案
如果是行家，提起报表，你是否想起了JasperReports( iReport )、Birt、JFreeReport、水晶报表等？但是，在海量数据问题面前，这些工具都弱爆了。怎么说呢，这些报表工具，的确功能强大、支持可视化的“报表模板”制作，用它来制作公文文件、财务报表、统计汇总表等，非常实用。但是我讲的是一种大数据量的报表，可能不是一张word或者几千条的excel能够导出的，比如说订单报表、销
Oracle 大数据量update优化
oracle update 语句不支持象查新那样进行连接处理, 所以关联子表update 需要使用子查询,举个例子: 加入有一张员工考勤表, 一张员工打卡表,需要将打卡表的最早时间更新到员工表,那么应该这么写: update <员工考勤表 t1> set 最早打卡时间 = (select min(打卡时间) from <员工打卡表 t2> where t1.员工编号 =t2.员工编号 and t1.时间 = t2.时间) 这个语句在遍历update 员工考勤表时,...
数据量过大时数据库操作的处理
随着“金盾工程”建设的逐步深入和公安信息化的高速发展，公安计算机应用系统被广泛应用在各警种、各部门。与此同时，应用系统体系的核心、系统数据的存放地――数据库也随着实际应用而急剧膨胀，一些大规模的系统，如人口系统的数据甚至超过了1000万条，可谓海量。那么，如何实现快速地从这些超大容量的数据库中提取数据（查询）、分析、统计以及提取数据后进行数据分页已成为各地系统管理员和数
【图文详解】Unity存储游戏数据的几种方法
在Unity中，常用的数据存储方法包括PlayerPrefs、ScriptableObject、JSON、XML和数据库等。 PlayerPrefs是Unity自带的一种简单的键值存储系统，适合存储一些简单的游戏数据。ScriptableObject是一种Unity类，可用于创建可序列化的对象并存储数据。JSON和XML是轻量级的数据交换格式，可以通过读写文件的方式进行数据存储和传输。对于存储大量数据，使用数据库则是更好的选择。
Oracle--数据量增长过快策略
以下是转摘的资料，具有一定的代表性。前笔者在实际工作中，有幸接触到海量的数据处理问题，对其进行处理是一项艰巨而复杂的任务。原因有以下几个方面： 一、数据量过大，数据中什么情况都可能存在。如果说有10条数据，那么大不了每条去逐一检查，人为处理，如果有上百条数据，也可以考虑，如果数据上到千万级别，甚至过亿，那不是手工能解决的了，必须通过工具或者程序进行处理，尤其海量的数据中，什么情况都可能存在，
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章