• 热门搜索 热门搜索
菜单

您现在的位置是:博客 > 文章详情文章详情

sql Service 海量数据查询,如何提高查询效率--数据库分区

原创
时间2023/09/04 05:33:17 发布 预览数量302
分类: sql server 标签: Sql Server

问题:有一个张销售表, 每天会插入数万条销售数据,随着数据的增加, 查询越来越慢,加上各种筛选条件,查询速度就更慢了,例如数据库分区可以有效解决这个问题。下面就来看一下如何实现sql service 数据库分区。

假设:有一张销售表,里面有两百万条数据(这个还算少了, 怎么也得千万级以上),查询条件, 往年的数据按照年份来查询, 当年的数据按照季度来查询。

根据假设, 我们先弄出一个测试环境吧。

1、初始化环境

sql Service 海量数据查询,如何提高查询效率--数据库分区

首先我创建了一个数据库, 名字叫 FenQuDemo , 建了一张销售表, 名字叫 FF_SellTable,然后往表里插入了两百万行数据

sql Service 海量数据查询,如何提高查询效率--数据库分区

我们右键查看FF_SellTable的属性, 应该是下图标注的那样

sql Service 海量数据查询,如何提高查询效率--数据库分区

2、创建分区

数据库FenQuDemo 右键 -> 属性 ->文件

sql Service 海量数据查询,如何提高查询效率--数据库分区

新建5个文件组,对应5个数据库文件,Y2017存放2017年的数据,Q1,Q2,Q3,Q4存放4个季度的数据,这里我们将文件都放在了同一个文件夹,如果条件允许,放在不同的磁盘上会增加读写效率。

3、建立分区函数

建立分区函数就是在当前数据库中创建一个函数,该函数可根据指定列的值将表或索引的各行映射到分区。 使用 CREATE PARTITION FUNCTION 是创建已分区表或索引的第一步

CREATE PARTITION FUNCTION [FenQuDemoFunc](DATETIME) 
AS RANGE RIGHT 
FOR VALUES 
(N'2018-01-01T00:00:00',N'2018-04-01T00:00:00', N'2018-07-01T00:00:00',N'2018-10-01T00:00:00',N'2019-01-01T00:00:00');

具体语法和参数请看:使用CREATE PARTITION FUNCTION 建立分区函数,以及参数介绍

:如果运行上面的sql语句报错, 错误信息为:“消息 7736,级别 16,状态 1,第 1 行只能在 SQL Server Enterprise Edition 中创建分区函数。只有 SQL Server Enterprise Edition 支持分区。”这是因为你安装的sql不是企业版,数据库分区是企业版才有的功能, 这里是企业版数据库的安装地址,可以去下载安装:SQL Server Management Studio(2012,简体中文企业版)

4、建立分区方案

在当前数据库中创建一个将已分区表或已分区索引的分区映射到文件组的方案。 已分区表或已分区索引的分区的个数和域在分区函数中确定

CREATE PARTITION SCHEME [FenQuDemoGroup] AS PARTITION [FenQuDemoFunc] TO ([year2017], [Q1],[Q2],[Q3],[Q4])

具体语法和参数请看:使用 CREATE PARTITION SCHEME 建立分区方案,以及参数介绍

建好的分区函数和分区方案如下:分区方案和分区函数下会多出一个文件

sql Service 海量数据查询,如何提高查询效率--数据库分区

5、建立分区索引

CREATE CLUSTERED INDEX [ClusteredIndex_CreateDate] ON [dbo].[FF_SellTable]
(
[f_createTime]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FenQuDemoGroup]([f_createTime])

参数:

ClusteredIndex_CreateDate 索引名称

FF_SellTable 表名(要对那张表进行分区)

f_createTime 列名(要根据哪一列进行分区)

FenQuDemoGroup 分区方案名称

这样表分区就完成了
sql Service 海量数据查询,如何提高查询效率--数据库分区

接下来我们就可以对数据库进行分区查询了,

select $PARTITION.FenQuDemoFunc(f_createTime) as 分区编号,count(ID) as 记录数 from FF_SellTable group by $PARTITION.FenQuDemoFunc(f_createTime)

运行结果如下:
sql Service 海量数据查询,如何提高查询效率--数据库分区

查询具体的分区语法为:

SELECT TOP 100 * from  FF_SellTable WHERE $PARTITION.FenQuDemoFunc(f_createTime) = 5

至于参数具体意思,以及其他的语法,可以参考: $PARTITION (Transact-SQL)

6、分区新增和合并

现在如果是2019年,需要将2018年的四个季度(Q1,Q2,Q3,Q4)合并成一个新的文件组 year2018,而之前的Q1,Q2,Q3,Q4存放2019年四个季度的数据。

6.1、新建2018文件组

分区新增和合并

6.2、合并分区

先将所有季度文件组都合并,这样2019年数据之前都在2017文件组

ALTER PARTITION FUNCTION FenQuDemoFunc() MERGE RANGE (N'2018-01-01T00:00:00');
ALTER PARTITION FUNCTION FenQuDemoFunc() MERGE RANGE (N'2018-04-01T00:00:00');  
ALTER PARTITION FUNCTION FenQuDemoFunc() MERGE RANGE (N'2018-07-01T00:00:00');  
ALTER PARTITION FUNCTION FenQuDemoFunc() MERGE RANGE (N'2018-10-01T00:00:00');

可以在分区方案上查看创建SQL语句,这时的分区方案已经更改为:

CREATE PARTITION SCHEME [FenQuDemoGroup] AS PARTITION [FenQuDemoFunc] TO ([Y2017], [PRIMARY])

6.3、分区新增

首先将2018年的数据放在year2018文件组

--选择文件组
ALTER PARTITION SCHEME FenQuDemoGroup  
NEXT USED [year2018] ;

--修改分区函数  
ALTER PARTITION FUNCTION FenQuDemoFunc()  
SPLIT RANGE (N'2018-01-01T00:00:00.000') ;

同理将2019年的数据分别放在2019年的各个季度中

ALTER PARTITION FUNCTION FenQuDemoFunc()  MERGE RANGE (N'2018-01-01T00:00:00');

ALTER PARTITION SCHEME FenQuDemoGroup  NEXT USED [Q1];
ALTER PARTITION FUNCTION FenQuDemoFunc()  SPLIT RANGE (N'2018-01-01T00:00:00.000');

ALTER PARTITION SCHEME FenQuDemoGroup  NEXT USED [Q2]; 
ALTER PARTITION FUNCTION FenQuDemoFunc()  SPLIT RANGE (N'2018-04-01T00:00:00.000');

ALTER PARTITION SCHEME FenQuDemoGroup  NEXT USED [Q3];
ALTER PARTITION FUNCTION FenQuDemoFunc()  SPLIT RANGE (N'2018-07-01T00:00:00.000');

ALTER PARTITION SCHEME FenQuDemoGroup  NEXT USED [Q4];
ALTER PARTITION FUNCTION FenQuDemoFunc()  SPLIT RANGE (N'2018-10-01T00:00:00.000');

7、创建分区方案

CREATE PARTITION FUNCTION [FenQuDemoFunc](datetime) 
AS RANGE RIGHT 
FOR VALUES (
        N'2017-01-01T00:00:00.000',     
        N'2018-01-01T00:00:00.000', 
        N'2019-04-01T00:00:00.000', 
        N'2019-07-01T00:00:00.000', 
        N'2019-10-01T00:00:00.000'
)

8、创建分区函数

CREATE PARTITION SCHEME [FenQuDemoGroup] AS PARTITION [FenQuDemoFunc] TO ([year2017], [year2018], [Q1], [Q2], [Q3], [Q4])

上述语法,参数可以参考:ALTER PARTITION FUNCTION (Transact-SQL)

除了合并分区还有一个其他的办法, 就是现将分区转换为普通表,然后在进行分区,步骤为:先删除表中的索引,然后在重新创建索引,创建索引如下

CREATE CLUSTERED INDEX [index_sellTable]  ON dbo.FF_SellTable(f_createTime) ON [Primary]

创建完之后刷新一下, 查看表的属性:

sql Service 海量数据查询,如何提高查询效率--数据库分区

版权声明:本文为Converts的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://www.converts.cn/article/2501.html

暂无评论

暂无评论

目录

推荐阅读

  • 移除Linux系统启动时的等待时间

    一、前言 VMware 虚拟机中安装了很多Linux 系统,每次启动的时候, 都卡在系统选择的界面上,除非手动选择, 否则要等很久才会进入系统,如下图所示: ![移除Linux系统启动时的等待时间](/ArticleFile/2024-06-24/e3ab02212cc946bd9c23159895177fb3.png '移除Linux系统启动时的等待时间') 二、关闭系统等待的事件 我们

  • GitLab 私有化部署

    一、GitLab 概述 git作为目前最流行的代码管理工具,已经成为了程序员必备的技能。虽然目前有 github(对国人不太友好) 、gitee 两个比较大的代码托管平台。但是出于各种原因,搭建自己的私有代码平台也成了一部分开发人员的选择。针对这种需求,这里为大家介绍的是其中的佼佼者gitlab,也是我们公司内部正在使用的一个代码管理平台。 GitLab是一个基于Git的开源代码管理平台,它

  • 云服务器购买按量付费实例,并搭建私有网络图解

    一、前言 最近在自学 k8s 集群化部署, 奈何云服务器包年包月太贵了, 学习成本飙升。好在各大云服务商支持按量计费 , 接下来就带大家一起过一下购买流程,并且使用私有网络。 二、私有网络 在购买云服务器之前,先了解下什么是私有网络。官网给出的解释是:私有网络(Virtual Private Cloud,VPC)是一块在云服务器上自定义的逻辑隔离网络空间,可以使云服务器, 云数据库资源构建逻

  • Docker 部署FastTunnel,实现内网穿透

    一、前言 最近在学习搭建Elasticsearch集群,但是发现云服务(2核4G)资源根本就不够用,部署上去就直接宕机了。想着服务器资源太贵, 家里刚好有一台64G内存的闲置电脑。不如做一个内网穿透,可以远程访问。工作学习两不误。其实目前市面上已经有很多穿透工具了,比如向日葵~~ ,但是奈何带宽太小了,免费的才1M~~~ 二、什么是 FastTunnel FastTunnel 是用.net

  • IP地址和子网掩码的关系

    一、概述 IP地址(Internet Protocol Address)和子网掩码(Subnet Mask)是计算机网络中两个核心的概念,它们共同工作以确保数据能够正确地在复杂的网络环境中传输到目标设备。 二、IP地址 `IP地址`是IP协议提供的一种统一的地址格式,它为互联网上的每一个网络和每一台主机分配一个逻辑地址,以此来屏蔽物理地址的差异。它是一个32位的二进制数,但是在习惯上,我们

  • 修改Docker默认的存储驱动程序目录

    一、前言 今天使用jenkins对程序进行更新的时候,发现更新失败。经排查是因为系统磁盘满了,然而通过堡垒机看了下,系统空间还有很多,于是看了下系统分区: ![修改Docker默认的存储驱动程序目录](/ArticleFile/2024-06-06/444ede8a1b1b42058db18690fa995952.png '修改Docker默认的存储驱动程序目录') ![修改Docker默认

  • Windows 安装git的详细安装步骤 ,以及TortoiseGit 图形化工具

    一、前言 `Git` 是一个开源的分布式版本控制系统,用于有效、高速地处理从很小到非常大的项目版本管理。Git 与常用的版本控制工具 CVS, Subversion 等不同,它采用了分布式版本库的方式,不需要服务器端软件支持。 二、安装Git 1、Git 下载地址 [Git 下载地址](https://git-scm.com/downloads "Git 下载地址") ![Windows

  • 免费的 GIF 录屏工具下载

    一、前言 在写博客或者文档的时候,经常需要附上一些gif动图,可以让快速展示操作流程。下面`小C`就介绍几款我经常用到的几款免费的gif工具。 二、免费的Gif工具 1、GifCam GifCam是一款免费绿色软件,它能够录制屏幕上的动态内容,并将其保存为GIF动画图像。用户可以调整录制区域、帧速率等参数,以满足不同的录制需求。除了连续录制外,GifCam还支持单帧录制功能。用户可以手

  • 对比MySQL不同版本下表结构和数据存储的差异

    一、前言 在开始之前, 我们需要先了解一下 MySQL 的存储引擎有哪些。 `MySQL`常用的存储引擎有 InnoDB 和 MyISAM,而且两者都有各自的特点,适用于不同的场景。下面是对这两种存储引擎的简要对比: - InnoDB存储引擎 - 支持事务处理 - 支持行级锁 - 支持外键约束 - 可以根据事务日志实现数据恢复 - MyISAM存储引擎 - MyISAM 的读取性能

  • Linux系统定时任务 -- crontab命令

    一、前言 有时候需要在 Linux 系统中定时执行一些任务,我们就需要使用 `crontab` 命令创建定时任务。crontab 是“cron table”的缩写,其功能是管理定时计划任务。在Linux系统中的crond是一个定时计划任务服务,用户只要能够按照正确的格式(分、时、日、月、星期、命令)写入到配置文件中,那么就会按照预定的周期时间自动地执行下去,而crontab命令则是用于配置的工具

加载中