千万级SQL Server数据库表分区的实现

2024-02-29 14:58

本文主要是介绍千万级SQL Server数据库表分区的实现,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

千万级SQL Server数据库表分区的实现

2012-12-04 14:48 by swarb, ... 阅读, ... 评论, 收藏, 编辑

一般在千万级的数据压力下,分区是一种比较好的提升性能方法。本文将介绍SQL Server数据库表分区的实现。

AD:

最近使用SQL SERVER一个的缓存,数据量一天100w的速度增长,同时接受客户查询,速度由于数据量越来越大越来越慢,这里感谢  KillKill 和 邀约, 最近读了一套书不错,感兴趣的同学可以读读<<活法>>

回顾下经常使用的索引

一 .聚集索引

聚集索引的页级别包含了索引键,还包含数据页,因此,关于 除了键值以外聚集索引的叶级别还存放了什么的答案就是一切,也就是说,每行的所有字段都在叶级别种。
另一种说话是:数据本身也是聚集索引的一部分,聚集索引基于键值保持表中的数据有序。

SQL SERVER 中,所有的聚集索引都是唯一的,如果在创建聚集索引时没有指定UNIQUE 关键字,SQL SERVER 会在需要时通过往记录中添加一个唯一标识符(Uniqueifier)在内部保证索引的唯一性,该唯一标识符是一个4字节的值,作为附加在聚集索引键的字段添加到数据中,只有那些声明为索引键字段并拥有重复值的行才会被添加。

二 .非聚集索引

对于非聚集索引,叶级别不包含全部的数据。除了键值以外,每个叶级别(树的最低层)中的索引行包含了一个书签(bookmark),告诉SQL Server 可以在哪里找到与索引键相应的数据行。一个书签课能有两种格式。如果表上存在聚集索引,书签就是相应的数据行的聚集索引键。如果表是堆(heap)结构 ,就是没有聚集索引的情况下 ,书签就是一个行标识符 row identifier,rid ,以 文件号 页号 槽号 的格式来定位实际的行。

非聚集索引的存在与否并不影响数据分页的组织,因此每张表上并不像聚集索引那样只局限于拥有一个非聚集索引,SQL  Server 2005  每张表能够包含249 个非聚集索引 SQL Server 2008 每张表能够包含999 个非聚集索引 ,但是实际上所用到的比这个数要少的多。

三 .包含索引

索引键字段数量限制是16个,总共900个字节大小 ,包含性列只在叶级别中出现而且不以任何方式控制索引行的排序。它们的目的是使叶级别能够包含更多的信息从而更大地发挥覆盖索引(Covering index)的索引调优能力.覆盖索引是一种非聚集索引,在其叶级别就可以找到满足查询的全部信息,这样sql server就根本没有必要访问数据分页了,在一些情况下 sql serer 会悄悄的为索引添加一个包含性列。这可能发生在索引建立于分区表 也就是我今天是发的博客 O(∩_∩)O (partitioned table )上没有指定 on filegroup  或者 no partition_scheme  的情况下。

一 .SQL SERVER 表分区介绍:

SQL Server  引入的表分区技术,让用户能够把数据分散存放到不同的物理磁盘中,提高这些磁盘的并行处理性能以优化查询性能……

二 .SQL SERVER 数据库表分区由三个步骤来完成:

1.创建分区函数

2.创建分区架构

3.对表进行分区

基于缓存更新机制,我使用时间来进行分区,这里大家根据业务的要求使用合适的字段来作为分区

创建数据库分区文件数量,这里存储一年的数据分成十二个分区,需要现在D盘建立好Data 的文件夹 里面包含Primary 文件夹和 FG1 FG2 FG3 FG4............

 
  1. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AirAvCache')
  2. DROP DATABASE [AirAvCache]
  3. GO
  4. CREATE DATABASE [AirAvCache]
  5. ON PRIMARY
  6. (NAME='Data Partition DB Primary FG',
  7. FILENAME=
  8. 'D:\Data\Primary\AirAvCache Primary FG.mdf',
  9. SIZE=5,
  10. MAXSIZE=500,
  11. FILEGROWTH=1 ),
  12. FILEGROUP [AirAvCache FG1]
  13. (NAME = 'AirAvCache FG1',
  14. FILENAME =
  15. 'D:\Data\FG1\AirAvCache FG1.ndf',
  16. SIZE = 5MB,
  17. MAXSIZE=500,
  18. FILEGROWTH=1 ),
  19. FILEGROUP [AirAvCache FG2]
  20. (NAME = 'AirAvCache FG2',
  21. FILENAME =
  22. 'D:\Data\FG2\AirAvCache FG2.ndf',
  23. SIZE = 5MB,
  24. MAXSIZE=500,
  25. FILEGROWTH=1 ),
  26. FILEGROUP [AirAvCache FG3]
  27. (NAME = 'AirAvCache FG3',
  28. FILENAME =
  29. 'D:\Data\FG3\AirAvCache FG3.ndf',
  30. SIZE = 5MB,
  31. MAXSIZE=500,
  32. FILEGROWTH=1 ),
  33. FILEGROUP [AirAvCache FG4]
  34. (NAME = 'AirAvCache FG4',
  35. FILENAME =
  36. 'D:\Data\FG4\AirAvCache FG4.ndf',
  37. SIZE = 5MB,
  38. MAXSIZE=500,
  39. FILEGROWTH=1 ),
  40. FILEGROUP [AirAvCache FG5]
  41. (NAME = 'AirAvCache FG5',
  42. FILENAME =
  43. 'D:\Data\FG5\AirAvCache FG5.ndf',
  44. SIZE = 5MB,
  45. MAXSIZE=500,
  46. FILEGROWTH=1 ),
  47. FILEGROUP [AirAvCache FG6]
  48. (NAME = 'AirAvCache FG6',
  49. FILENAME =
  50. 'D:\Data\FG6\AirAvCache FG6.ndf',
  51. SIZE = 5MB,
  52. MAXSIZE=500,
  53. FILEGROWTH=1 ),
  54. FILEGROUP [AirAvCache FG7]
  55. (NAME = 'AirAvCache FG7',
  56. FILENAME =
  57. 'D:\Data\FG7\AirAvCache FG7.ndf',
  58. SIZE = 5MB,
  59. MAXSIZE=500,
  60. FILEGROWTH=1 ),
  61. FILEGROUP [AirAvCache FG8]
  62. (NAME = 'AirAvCache FG8',
  63. FILENAME =
  64. 'D:\Data\FG8\AirAvCache FG8.ndf',
  65. SIZE = 5MB,
  66. MAXSIZE=500,
  67. FILEGROWTH=1 ),
  68. FILEGROUP [AirAvCache FG9]
  69. (NAME = 'AirAvCache FG9',
  70. FILENAME =
  71. 'D:\Data\FG9\AirAvCache FG9.ndf',
  72. SIZE = 5MB,
  73. MAXSIZE=500,
  74. FILEGROWTH=1 ),
  75. FILEGROUP [AirAvCache FG10]
  76. (NAME = 'AirAvCache FG10',
  77. FILENAME =
  78. 'D:\Data\FG10\AirAvCache FG10.ndf',
  79. SIZE = 5MB,
  80. MAXSIZE=500,
  81. FILEGROWTH=1 ),
  82. FILEGROUP [AirAvCache FG11]
  83. (NAME = 'AirAvCache FG11',
  84. FILENAME =
  85. 'D:\Data\FG11\AirAvCache FG11.ndf',
  86. SIZE = 5MB,
  87. MAXSIZE=500,
  88. FILEGROWTH=1 ),
  89. FILEGROUP [AirAvCache FG12]
  90. (NAME = 'AirAvCache FG12',
  91. FILENAME =
  92. 'D:\Data\FG12\AirAvCache FG12.ndf',
  93. SIZE = 5MB,
  94. MAXSIZE=500,
  95. FILEGROWTH=1 )

创建好后如图:

打开FG1 文件夹 看到多了AirAvCacheFG1.ndf 文件

创建分区函数

 
  1. USE AirAvCache
  2. GO
  3. -- 创建函数
  4. CREATE PARTITION FUNCTION [AirAvCache Partition Range](DATETIME)
  5. AS RANGE LEFT FOR VALUES ('2010-09-01','2010-10-01','2010-11-01',
  6. '2010-12-01','2011-01-01','2011-02-01','2011-03-01','2011-04-01',
  7. '2011-05-01','2011-06-01','2010-07-01');

创建分区架构

 
  1. CREATE PARTITION SCHEME [AirAvCache Partition Scheme]
  2. AS PARTITION [AirAvCache Partition Range]
  3. TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],
  4. [AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);

创建一个使用AirAvCache Partitiion Scheme 架构的表

 
  1. CREATE TABLE [dbo].[AvCache](
  2. [CityPair] [varchar](6) NOT NULL,
  3. [FlightNo] [varchar](10) NULL,
  4. [FlightDate] [datetime] NOT NULL,
  5. [CacheTime] [datetime] NOT NULL DEFAULT (getdate()),
  6. [AVNote] [varchar](300) NULL
  7. ) ON [AirAvCache Partition Scheme] (FlightDate);
  8. --注意这里使用[AirAvCache Partition Scheme]架构,根据FlightDate 分区

查询分区情况

 
  1. -- 查看使用情况
  2. SELECT *, $PARTITION.[AirAvCache Partition Range](FlightDate)
  3. FROM dbo.AVCache

可以看到9 月和 10 月已经分开了。

原文标题:SQL SERVER 表分区

 

这篇关于千万级SQL Server数据库表分区的实现的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/759189

相关文章

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

python生成随机唯一id的几种实现方法

《python生成随机唯一id的几种实现方法》在Python中生成随机唯一ID有多种方法,根据不同的需求场景可以选择最适合的方案,文中通过示例代码介绍的非常详细,需要的朋友们下面随着小编来一起学习学习... 目录方法 1:使用 UUID 模块(推荐)方法 2:使用 Secrets 模块(安全敏感场景)方法

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案(适合跨库实时查询)1. 方案概述2. 环境要求3.

Spring StateMachine实现状态机使用示例详解

《SpringStateMachine实现状态机使用示例详解》本文介绍SpringStateMachine实现状态机的步骤,包括依赖导入、枚举定义、状态转移规则配置、上下文管理及服务调用示例,重点解... 目录什么是状态机使用示例什么是状态机状态机是计算机科学中的​​核心建模工具​​,用于描述对象在其生命

Spring Boot 结合 WxJava 实现文章上传微信公众号草稿箱与群发

《SpringBoot结合WxJava实现文章上传微信公众号草稿箱与群发》本文将详细介绍如何使用SpringBoot框架结合WxJava开发工具包,实现文章上传到微信公众号草稿箱以及群发功能,... 目录一、项目环境准备1.1 开发环境1.2 微信公众号准备二、Spring Boot 项目搭建2.1 创建

IntelliJ IDEA2025创建SpringBoot项目的实现步骤

《IntelliJIDEA2025创建SpringBoot项目的实现步骤》本文主要介绍了IntelliJIDEA2025创建SpringBoot项目的实现步骤,文中通过示例代码介绍的非常详细,对大家... 目录一、创建 Spring Boot 项目1. 新建项目2. 基础配置3. 选择依赖4. 生成项目5.

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步:打开官网下载对应文件第二步:程序安装配置第三部:安装工具SQL Server Manageme