none
千万级别数据 RRS feed

  • 问题

  • 千万级别数据,单表查询,时间列上加聚集索引,查看了执行计划是聚集索引扫描,返回数据要十几多秒,如果优化?
    2011年10月20日 11:16

答案

  • 你这个语句可以稍微重写一下:

    Select CellID as a,Count(1) as b 

    from GBMessage where EventID = 337903624 and 

    timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59' and CellID != 0

    group by CellID

    把cellid=0的记录直接剔除掉,而不是等到groupby之后再剔除掉,性能会高。

    然后你可以基于CellID、EventID以及timestamp做索引。

    因为你这张表的字符串列非常多,因此一页上存放的记录数目就相对少很多,所以你做很多常规的查询涉及的IO都会非常大,性能都不会太好。

    所以尽量把经常作为where条件查询的列单独拿出来做一个索引效率就会高很多。


    Visit my tech blog: www.imkevinyang.com
    • 已标记为答案 znajax 2011年10月21日 14:45
    2011年10月21日 5:48

全部回复

  • 或許你可以把你的TABLE SCHEMA和所使用的SELECT語法貼上來看看。
    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年10月20日 12:25
  • create table GBMessage
    (
    id int,
    dtime datetime,
    eventid int
    )
    select * 
    from GBMessage
    where dtime>'2011-09-14 00:00:00' and dtime<'2011-09-16 23:00:00' and eventid=23
    dtime 上是聚集索引,是时间戳精度到毫秒,数据量很大,一天就有500多万条
    2011年10月20日 13:15
  • Hi, znajax

    数据库查询优化的原则大同小异,我觉得有两个很重要的原则是你需要考虑的

    1. 不要返回不需要的细节数据

    是不是真的有必要一次查询中返回上百万条数据?

    即使是导出报表,一般人也不会一次性导出这么多数据,是否可以考虑加分页?

    如果是需要对大数据做数据分析,那么是否可以考虑每周汇总一次数据,基于汇总之后的数据做分析,或者使用SSAS

    2. 能使用小数据类型的情况下不使用大数据类型

    在你的场景中,时间记录是否有必要使用8个字节的datetime类型,用4个字节的smalldatetime(精确到秒)是否能满足你的业务需求?又比如,eventid是否必须用int,用smallint是否可以满足需求?

    数据量大的情况下,使用小数据类型可以使sql server的页容纳的数据更多,一次查询涉及的IO就越少。性能提升是非常明显的。


    Visit my tech blog: www.imkevinyang.com
    • 已建议为答案 Wison-Ho 2011年10月21日 2:18
    2011年10月20日 16:33
  • 試試看下列語法有沒有比較快。

    1.只select需要的欄位

    2.把eventid的篩選條件移到dtime前面。

    select col1,col2
    from GBMessage
    where eventid=23 and dtime>'2011-09-14 00:00:00' and dtime<'2011-09-16 23:00:00'
    


     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年10月20日 22:55
  • where条件的顺序一般是无关紧要的,sql server优化器会自动优化的。例如你在最后面添加1=0的条件,那么前面的条件就直接忽略了。
    你用到了聚簇索引列但加了eventid的判断条件,所以肯定是做表查找或者聚簇索引查找(执行计划显示的应该是Clustered Index Seek),因为sql server会根据聚簇索引列定位到数据的范围,然后对每行数据的eventid做比较才能找到目标记录。
    所以数据量上如果本来就那么大,那数据量上基本没法做什么优化的。只能说让查找的效率提高一些,就是我上面所说的一些优化思路了。

    Visit my tech blog: www.imkevinyang.com
    2011年10月21日 2:16
  • 另外,从你设计的表名来看,你实际应用中的表应该不只那么几列吧?应该还有一些字符串列吧

    如果那样的话,那可优化的空间也会不一样的。例如你可以启用sql server页级压缩来让数据页更紧凑一些,这样IO效率会高很多。


    Visit my tech blog: www.imkevinyang.com
    2011年10月21日 2:19
  • sql server2005可以页级压缩吗?还有就是现在单表数据是17193909行,就对单表查询返回了354:

    如下:

    Select distinct tab1.CellID as [index], tab2.b as [value] 

    from GBMessage as tab1 left join ( 

    Select CellID as a,Count(1) as b 

    from GBMessage where EventID = 337903624 and 

    timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59' 

    group by CellID) as tab2 on tab1.CellID = tab2.a 

    where  timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59'  and tab1.CellID!=0 

    ORDER BY tab1.CellID

    时间是11秒,问是否可以有优化的空间了?

    2011年10月21日 4:32
  • sql server2005可以页级压缩吗?还有就是现在单表数据是17193909行,就对单表查询返回了354:

    如下:

     

    Select distinct tab1.CellID as [index], tab2.b as [value] 

    from GBMessage as tab1 left join ( 

    Select CellID as a,Count(1) as b 

    from GBMessage where EventID = 337903624 and 

    timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59' 

    group by CellID) as tab2 on tab1.CellID = tab2.a 

    where  timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59'  and tab1.CellID!=0 

    ORDER BY tab1.CellID

    时间是11秒,问是否可以有优化的空间了?

     

    SQL Server 2008才提供压缩的功能的

    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月21日 5:16
  • 这是表结构
    CREATE TABLE [dbo].[GBMessage](
    [Index] [bigint] NOT NULL,
    [TimeStamp] [datetime] NULL,
    [CurTLLI] [bigint] NULL,
    [NewTLLI] [bigint] NULL,
    [IMSI] [varchar](50) NULL,
    [TMSI] [varchar](50) NULL,
    [MSISDN] [varchar](50) NULL,
    [TrainID] [varchar](50) NULL,
    [TrainNO] [varchar](50) NULL,
    [CellID] [int] NOT NULL CONSTRAINT [DF_GBMessage_CellID]  DEFAULT ((0)),
    [KMPose] [bigint] NULL,
    [Speed] [decimal](18, 2) NULL,
    [GPSLong] [varchar](50) NULL,
    [GPSLat] [varchar](50) NULL,
    [Direction] [varchar](10) NULL,
    [InterfaceType] [varchar](50) NULL,
    [MessageID] [varchar](50) NULL,
    [UmMessageID] [varchar](50) NULL,
    [KeyWord] [varchar](100) NULL,
    [CaseID] [varchar](50) NULL,
    [CaseValue] [varchar](100) NULL,
    [EventID] [int] NOT NULL CONSTRAINT [DF_GBMessage_EventID]  DEFAULT ((0)),
    [Length] [int] NOT NULL CONSTRAINT [DF_GBMessage_Length]  DEFAULT ((0)),
    [DataContent] [varbinary](2000) NULL,
    [Defult1] [varchar](100) NULL
    )
    分区:一天一个区
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day1
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day2
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day3
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day4
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day5
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day6
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day7
    ...................
    ALTER DATABASE [TemplateServer] ADD FILEGROUP day365
    CREATE PARTITION FUNCTION [CPF](datetime)
     AS RANGE LEFT FOR VALUES (N'2011-01-02 00:00:00', N'2011-01-03 00:00:00', 
     N'2011-01-04 00:00:00', N'2011-01-05 00:00:00', N'2011-01-06 00:00:00', 
     N'2011-01-07 00:00:00', N'2011-01-08 00:00:00', N'2011-01-09 00:00:00', 
     N'2011-01-10 00:00:00', N'2011-01-11 00:00:00', N'2011-01-12 00:00:00', 
     N'2011-01-13 00:00:00', N'2011-01-14 00:00:00', N'2011-01-15 00:00:00', ...........'2011-12-31 00:00:00');
    2011年10月21日 5:22
  • 你这个语句可以稍微重写一下:

    Select CellID as a,Count(1) as b 

    from GBMessage where EventID = 337903624 and 

    timestamp>='2011-09-15 00:00:00' and timestamp<= '2011-09-15 17:59:59' and CellID != 0

    group by CellID

    把cellid=0的记录直接剔除掉,而不是等到groupby之后再剔除掉,性能会高。

    然后你可以基于CellID、EventID以及timestamp做索引。

    因为你这张表的字符串列非常多,因此一页上存放的记录数目就相对少很多,所以你做很多常规的查询涉及的IO都会非常大,性能都不会太好。

    所以尽量把经常作为where条件查询的列单独拿出来做一个索引效率就会高很多。


    Visit my tech blog: www.imkevinyang.com
    • 已标记为答案 znajax 2011年10月21日 14:45
    2011年10月21日 5:48