• 热门搜索 热门搜索

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

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

原创
时间2023/09/04 05:33:17 发布 预览数量217
分类: 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

暂无评论

暂无评论

目录

推荐阅读

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

    ## 一、前言 最近在学习搭建**Elasticsearch**集群,但是发现云服务(2核4G)资源根本就不够用,部署上去就直接宕机了。想着服务器资源太贵, 家里刚好有一台**64G**内存的闲置电脑。不如做一个**内网穿透**,可以远程访问。工作学习两不误。其实目前市面上已经有很多穿透工具了,比如向日葵~~ ,但是奈何带宽太小了,免费的才1M~~~ ## 二、什么是 FastTunnel **FastTunnel** 是用.net core开发的一款跨平台内网穿透工具,它可以实现将内网服务暴露到公网供自己或任何人访问。 与其他穿透工具不同的是:FastTunnel项目致力于打造一个易于扩

  • 解决:“The configured user limit (128) on the number of inotify instances has been reached....” 错误

    ## 一、场景再现 在linux系统上运行着10个微服务,突然发现某个服务挂了。遇到这种情况,一般就使用命令`docker start` 就可以解决。 然而发现命令无法使服务起来。根据跟踪容器日志发现是出现了异常,如下所示: ```shell Unhandled exception. System.IO.IOException: The configured user limit (128) on the number of inotify instances has been reached, or the per-process limit on the number of open fi

  • Docker 安装mysql

    ## 一、创建mysql 容器 ```shell docker run \ -d \# 后台运行 --restart=always \#总是跟随docker启动 --privileged=true\#获取宿主机root权限 -p 13306:3306 --name mysql \# 容器与主机映射端口为,主机13306,容器3306 -v /mysql/log:/var/log/mysql \# 容器运行后的名称 -v /mysql/data:/var/lib/mysql \# mysql 目录挂载 -v /etc/localtime:/etc/localtime:ro \#让容器的时钟与宿主

  • validate service connection: CRI v1 runtime API is not implemented for endpoint \"unix:///var/run/containerd/containerd.sock\": rpc error: code = Unimplemented desc = unknown service runtime.v1.RuntimeService

    ## 一、问题 安装k8s集群, Node节点加入主节点的时候(`kubeadm join...`),报错,报错信息如下: ```shell [root@node1 ~]# kubeadm join k8s-master:6443 --token 4nm8cy.jgxw8go95c1uqt6c --discovery-token-ca-cert-hash sha256:f1c08bce4ebeb8deb531b950e644cca399efc40e1a9ac99df21b7b38a31a6c02 [preflight] Running pre-flight checks

  • k8s 安装ingress,并解决拉取镜像失败的问题

    ## 一、前言 Service 是将运行在一组 Pods 上的应用程序公开为网络服务的抽象方法,但是Service 只能在内网间访问(NodePort方式用的较少), 那么外网的路由请求如何发送到 Service 上呢? k8s 为我们提供了 Ingress 网关服务。 ## 二、什么是Ingress ? Ingress 公开从集群外部到集群内服务的 HTTP 和 HTTPS 路由。 流量路由由 Ingress 资源上定义的规则控制。下面是一个将所有流量都发送到同一 Service 的简单 Ingress 示例: ![k8s 安装ingress,并解决拉取镜像失败的问题](/Article

  • ssh 免密登录

    ## 一、前言 SSH(Secure Shell)免密登录是一种安全便捷的远程登录方式,允许用户在不输入密码的情况下连接到远程Linux服务器。它通过密钥认证来实现登录,这种方法可以提高工作效率,同时加强系统的安全性。 ## 二、秘钥的存放位置 一般秘钥都存放在用户的根目录下 `~/.ssh`,如下图所示 ![ssh 免密登录](/ArticleFile/2024-01-09/802cf3e23bde4298bf4accd9929878c6.png 'ssh 免密登录') .ssh 目录下一般会有两个文件 `id_rsa:私钥` 、 `id_rsa.pub:公钥`。 ![ssh 免密

  • k8s http: server gave HTTP response to HTTPS client

    ## 一、问题 k8s 在拉取私有仓库镜像的时候报`http: server gave HTTP response to HTTPS client`错,网络上的答案都是千篇一律的,根本就没有抓住问题的根源。下面就有我来剖析一下问题的原因。 ```shell Type Reason Age From Message ---- ------ ---- ---- ------- Normal Schedule

  • org.jenkinsci.plugins.scriptsecurity.scripts.UnapprovedUsageException: script not yet approved for use

    ## 一、报错信息 jenkins 中编写pipeline脚本的时候,执行构建,报如下错误: ```shell Started by user admin org.jenkinsci.plugins.scriptsecurity.scripts.UnapprovedUsageException: script not yet approved for use at org.jenkinsci.plugins.scriptsecurity.scripts.ScriptApproval.using(ScriptApproval.java:676) at org.jenkinsci.plugin

  • kubeadm init:failed to pull image registry.k8s.io/pause:3.6

    ## 一、错误现象 在安装 **K8s 1.28.0** 的时候,**kubeadm init...** 执行失败,错误信息如下: ```shell [kubelet-check] Initial timeout of 40s passed. Unfortunately, an error has occurred: timed out waiting for the condition This error is likely caused by: - The kubelet is not running - The kubelet is

  • .net core 通过环境变量加载配置文件(Development、Staging、Production)

    ## 一、前言 在**.net core 2.1** 之前,每次更新程序, 都需要手动更改数据库连接字符串。如果不小心把测试库发布上去了, 问题就大了。不过好在.NET Core 2.1及以上版本增加了支持根据环境变量加载配置文件。简单点理解就是, **代码中有三个配置文件(开发,测试,生产),程序发布之后, 更具服务器的环境变量, 自动加载相应的配置文件,不用在每次手动的切换了**。很是方便。 ## 二、实现 要想实现此功能,我们需要设置对应配置文件的后缀,以便系统会自动识别。 **举个例子:** 假设现在有一个叫 `Database.json` 的数据库配置文件,我们只需要再创建两个配

加载中