none
咨询一个sqlserver垮分区排序的问题 RRS feed

  • 问题

  •  案例相关sql如下:

    CREATE PARTITION FUNCTION [pf_1](datetime) AS RANGE left FOR VALUES (N'2020-05-01T00:00:00.000', N'2020-06-01T00:00:00.000', N'2020-07-01T00:00:00.000')
    GO  

    CREATE PARTITION SCHEME [ps_1] AS PARTITION [pf_1] all TO ([primary])
    GO

    -------------

    CREATE TABLE t_p(id int identity,phone varchar(20),add_money decimal(18,5),insert_time datetime)
    create clustered index i_insert_time on t_p(insert_time asc) on [ps_1](insert_time) --注意,此处的索引为asc升序

    ---------------
    --插入5万条数据到两个分区里面去
    declare @phone bigint=13589890000
    declare @insert_time datetime='2020-6-30 16:00:00'

    while @phone>13589840000
    begin

    insert t_p(phone,add_money,insert_time)values(@phone,RIGHT(@phone,2),@insert_time)

    set @phone=@phone-1
    set @insert_time=DATEADD(SECOND,1,@insert_time)
    end

    ----------------

    select COUNT(*) from t_p
    --总计50000

    select MIN(insert_time),MAX(insert_time) from t_p 
    --最小最大时间如右侧,2020-06-30 16:00:00.000 2020-07-01 05:53:19.000

    ---现象如下:
    select top 50 phone,add_money from t_p
    where insert_time>='2020-6-30' and insert_time<'2020-7-2'
    order by insert_time desc
    --通过执行计划可以看出,性能良好,虽然垮了两个分区获取数据,但是没有额外的排序操作,实际扫描的数据也只有50条

    --执行下列sql,将聚集索引排序asc修改为desc

    drop   index i_insert_time on t_p
    create clustered index i_insert_time on t_p(insert_time desc) on [ps_1](insert_time)

    --再次执行上面的select语句

    select top 50 phone,add_money from t_p
    where insert_time>='2020-6-30' and insert_time<'2020-7-2'
    order by insert_time desc
    --通过执行计划可以看出,性能不对了,有额外的排序操作,实际扫描的数据不再是50条,而是非常大的数据量了

    不知为何?

     
    2020年7月9日 9:30

全部回复

  • 你好,

    因为聚集索引的排列顺序是会影响到查询的方向的,也就会对扫描的数据量有所不同。

    根据你的语句,可以看到如下不同:

    1. 当聚集索引为默认(升序)时:

    看红线部分,查找条件和where语句一致:where insert_time>='2020-6-30' and insert_time<'2020-7-2'

    2.当聚集索引为降序时:

    看红线部分,查找条件和where语句顺序相反:先 insert_time<'2020-7-2' 再 insert_time>='2020-6-30' 

    这有一篇文章希望能对你有用:索引排序顺序设计指南


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年7月10日 6:30
  • 没有想明白,哈哈!

    关键是如果不夸区(当聚集为降序时),比如:

    select top 50 phone,add_money from t_p
    where insert_time>='2020-6-30' and insert_time<'2020-6-30 23:59:00'
    order by insert_time desc

    发现也不会有额外的消耗,但是只要跨区,就会消耗很大!

    2020年7月13日 2:45