知方可补不足~SQL为大数据引入分区表

2017-01-09 09:02 出处:360java.com 作者:360java  阅读()
回到目录 一些概念 分区表在oracle和sqlserver中都上存在的,当数据表的数据量过大时,上千万,上亿的数据,在进行数据查询时需要显得比较慢,性能很差,这时是时候引入分区表了,

回到目录

一些概念

分区表在oracle和sqlserver中都上存在的,当数据表的数据量过大时,上千万,上亿的数据,在进行数据查询时需要显得比较慢,性能很差,这时是时候引入分区表了,分区表顾名思义,就是把物理表用一些文件NDF进行分块存储,以缓减IO的压力,因为当你的SQL文件过大的,这其实对系统的IO影响是最大的,这种分区表我感觉类似于数据的分片(mongodb),它将有效的利用服务器的CPU多核资源,并行去处理你的请求,所以在大数据情况下,分区表是很好的一种选择!

我们通常也把电脑的磁盘分成若干的区,其中一种考虑也是为了性能,安全等

sql的分区表于若干的文件组组成,它们可以被理解成依照某个条件(分区函数)来进行划分的文件块,当你进行curd操作时,SQL会把它同时响应到对应的块上去,文件组里至少包含一个文件,当然可以是多个,它们也可以在不同的磁盘上。

说干就干

下面来一步一步的实现一下分区表的建立逻辑

--建立两个文件组
ALTER DATABASE Test ADD FILEGROUP BEFORE2000
ALTER DATABASE Test ADD FILEGROUP AFTER2000
ALTER DATABASE Test ADD FILEGROUP AFTER2010

--创建文件
ALTER DATABASE Test ADD FILE
(Name=N'Before2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Before2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP Before2000
ALTER DATABASE Test ADD FILE
(Name=N'After2000',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2000.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2000
ALTER DATABASE Test ADD FILE
(Name=N'After2010',filename='c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\After2010.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) 
TO FILEGROUP After2010

上面代码在磁盘上建立了两个文件组,用来存储2000年以前,2000-2010年,以2010年以后的数据,而ndf是分区表文件的类型,一个分区表文件组可以由多个ndf文件构成

--编写分区函数
CREATE PARTITION FUNCTION RangeTime (DATETIME) AS RANGE LEFT FOR VALUES ('2000-01-01','2010-01-01')
--编写分区方案,  分区方案也就是将分区函数与文件组进行一个关联
CREATE PARTITION SCHEME RangeSchema_CreateTime
AS PARTITION RangeTime
TO (BEFORE2000,AFTER2000,AFTER2010)

上面分区函数说明以哪里依据进行分区,而分区方案是将它与分区文件组进行接合,或者和数据表进行打通,以后我们用的时候,直接用分区函数(表名)即可。

--创建分区表,先建表,再设主键,否则会出错
CREATE TABLE Order
    (
      ID VARCHAR(50) ,
      UserId VARCHAR(50) ,
      CreateTime DATETIME
    )
ON  RangeSchema_CreateTime(CreateTime)

上面代码建立一张表,并进行分区的配置,注意,在建立表后,再建立主键,我们填充一些数据就可以测试了,下面介绍几个常用的命令 

返回2001-2-2日这条数据会被分配到哪个分区了

--测试某个对象放在哪个分区里
SELECT  $PARTITION.RangeTime('2001-2-2')

知方可补不足~SQL为大数据引入分区表

查看指定分区内的数据

--查看某个分区表里存放的数据
SELECT  *
FROM    shop
WHERE   $PARTITION.RangeTime(CreateTime) = 1

知方可补不足~SQL为大数据引入分区表

查看某个分区里,记录的个数

--查看某个分区表的个数
SELECT  $PARTITION.RangeTime(CreateTime) AS 分区编号 ,
        COUNT(id) AS 记录数
FROM    shop
GROUP BY $PARTITION.RangeTime(CreateTime)  

知方可补不足~SQL为大数据引入分区表

当数据量达到一定指数时,我们必须要进行调整,而选择哪种方法是靠技术决策人的,我们每个开发人员都应该把自己当成是技术的决策人,对自己的代码和自己的人生负责!

感谢您的阅读!

回到目录

 

分享到:
本文标签: 方可, 不足, 数据, 引入, 分区表

相关文章

发表评论(共条评论)愿您的每句评论,都能给大家的生活添色彩,带来共鸣,带来思索,带来快乐。

Copyright (C) 360java 360java.com, All Rights Reserved.

苏ICP备16022210号