SQL SERVER 表分区测试备忘
最近遇到一个反馈说系统查询很慢,经常发生超时的问题,因系统上线比较久,部分数据表数据量比较大,常用的表少则1千多万,多的超过1亿条数据,且都是常用的表。目前想到的方案有:
- 分库:报表查询用一个库,操作用一个库。这样的改动对于系统来说改动比较小,但基本无法解决超时的问题;
- 分表:对于报表的改动比较大,是一个备选方案;
- 表分区:看到有文章介绍说表分区可解决性能问题,因表分区不需要对业务代码做任何改变,基于此特性我做了个验证。
关于表分区的操作过程可参考:SQL Server表分区
如果需要调整表分区方案可参考:SQL修改表分区方案(高效)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 增加分区文件 ALTER PARTITION SCHEME fa_main NEXT USED [db06] -- 新增分区表,加入新的分区规则,执行后它会自动将数据迁移到新的分区中 ALTER PARTITION FUNCTION fn_main() SPLIT RANGE(50000000)
本次我在本机上做新建一个测试库,并创建了 5千万条数据,分5个表分区文件测试,每个文件1千万条数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
用如下代码验证数据分布:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- fn_main 为分区函数 SELECT COUNT(1) 数量, $partition.fn_main(prohid) 分区号 FROM dbo.pro_main GROUP BY $partition.fn_main(prohid)
数据分布都符合预期。下面我将表分区和不做表分区对比着查询,看下实际性能如何。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 释放缓存 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%'
通过查询分析对比,他们的速度几乎一致,有做过分区的甚至比未做分区的查询要慢。但加上了查询的分区号后表分区的查询速度立马体现出来了。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT $partition.fn_main(prohid) fc, * FROM dbo.pro_main WHERE barcodeid LIKE '%3752c%' AND $partition.fn_main(prohid) BETWEEN 2 AND 3
结论:
- 有文章介绍的说表分区查询速度快是有前提的,即限定在某个分区的查询速度会快;
- 在应对大体量数据的时候,表分区在某些场景下可能有用,但它不是万能的。