SQL SERVER 表分区测试备忘

　　最近遇到一个反馈说系统查询很慢，经常发生超时的问题，因系统上线比较久，部分数据表数据量比较大，常用的表少则1千多万，多的超过1亿条数据，且都是常用的表。目前想到的方案有：

1. 分库：报表查询用一个库，操作用一个库。这样的改动对于系统来说改动比较小，但基本无法解决超时的问题；
2. 分表：对于报表的改动比较大，是一个备选方案；
3. 表分区：看到有文章介绍说表分区可解决性能问题，因表分区不需要对业务代码做任何改变，基于此特性我做了个验证。

　　关于表分区的操作过程可参考：SQL Server表分区

　　如果需要调整表分区方案可参考：SQL修改表分区方案（高效） 

-- 增加分区文件
ALTER PARTITION SCHEME fa_main NEXT USED [db06]

-- 新增分区表，加入新的分区规则，执行后它会自动将数据迁移到新的分区中
ALTER PARTITION FUNCTION fn_main() SPLIT RANGE(50000000)

 

　　本次我在本机上做新建一个测试库，并创建了 5千万条数据，分5个表分区文件测试，每个文件1千万条数据。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[pro_main](
    [prohid] [BIGINT] IDENTITY(1,1) NOT NULL,
    [barcodeid] [VARCHAR](50) NOT NULL,
    [createdate] [DATETIME] NOT NULL,
    [sortid] [INT] NOT NULL,
    [userid] [INT] NOT NULL,
    [realname] [VARCHAR](50) NULL,
 CONSTRAINT [PK_pro_main] PRIMARY KEY CLUSTERED 
(
    [prohid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
GO


DECLARE @i BIGINT;
SET @i = 1;
WHILE (@i <= 5000000)
BEGIN

INSERT INTO dbo.pro_main
(
    barcodeid,
    createdate,
    sortid,
    userid,
    realname
)
    VALUES
    (NEWID(), GETDATE(), @i,3421, 'test'+ CAST(@i AS VARCHAR(10)));
    SET @i = @i + 1;
END;
GO

　　用如下代码验证数据分布：

-- fn_main 为分区函数
SELECT COUNT(1) 数量, $partition.fn_main(prohid) 分区号 
FROM dbo.pro_main
GROUP BY $partition.fn_main(prohid)

　　数据分布都符合预期。下面我将表分区和不做表分区对比着查询，看下实际性能如何。

-- 释放缓存
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE

-- 表分区的
SELECT $partition.fn_main(prohid) fc, * 
FROM dbo.pro_main WHERE barcodeid LIKE '%3752c%'

-- 未做表分区的
SELECT * 
FROM dbo.pro_main01 WHERE barcodeid LIKE '%3752c%'

　　通过查询分析对比，他们的速度几乎一致，有做过分区的甚至比未做分区的查询要慢。但加上了查询的分区号后表分区的查询速度立马体现出来了。

SELECT $partition.fn_main(prohid) fc, * 
FROM dbo.pro_main WHERE barcodeid LIKE '%3752c%' 
    AND $partition.fn_main(prohid) BETWEEN 2 AND 3

　　

　　结论：

1. 有文章介绍的说表分区查询速度快是有前提的，即限定在某个分区的查询速度会快；
2. 在应对大体量数据的时候，表分区在某些场景下可能有用，但它不是万能的。