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

2024-02-29 14:58
文章标签 sql 实现 数据库 分区 server 千万级 database

本文主要是介绍千万级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.  
  48. FILEGROUP [AirAvCache FG6] 
  49. (NAME = 'AirAvCache FG6', 
  50. FILENAME = 
  51. 'D:\Data\FG6\AirAvCache FG6.ndf', 
  52. SIZE = 5MB, 
  53. MAXSIZE=500, 
  54. FILEGROWTH=1 ), 
  55.  
  56.  
  57. FILEGROUP [AirAvCache FG7] 
  58. (NAME = 'AirAvCache FG7', 
  59. FILENAME = 
  60. 'D:\Data\FG7\AirAvCache FG7.ndf', 
  61. SIZE = 5MB, 
  62. MAXSIZE=500, 
  63. FILEGROWTH=1 ), 
  64.  
  65. FILEGROUP [AirAvCache FG8] 
  66. (NAME = 'AirAvCache FG8', 
  67. FILENAME = 
  68. 'D:\Data\FG8\AirAvCache FG8.ndf', 
  69. SIZE = 5MB, 
  70. MAXSIZE=500, 
  71. FILEGROWTH=1 ), 
  72.  
  73. FILEGROUP [AirAvCache FG9] 
  74. (NAME = 'AirAvCache FG9', 
  75. FILENAME = 
  76. 'D:\Data\FG9\AirAvCache FG9.ndf', 
  77. SIZE = 5MB, 
  78. MAXSIZE=500, 
  79. FILEGROWTH=1 ), 
  80.  
  81. FILEGROUP [AirAvCache FG10] 
  82. (NAME = 'AirAvCache FG10', 
  83. FILENAME = 
  84. 'D:\Data\FG10\AirAvCache FG10.ndf', 
  85. SIZE = 5MB, 
  86. MAXSIZE=500, 
  87. FILEGROWTH=1 ), 
  88.  
  89. FILEGROUP [AirAvCache FG11] 
  90. (NAME = 'AirAvCache FG11', 
  91. FILENAME = 
  92. 'D:\Data\FG11\AirAvCache FG11.ndf', 
  93. SIZE = 5MB, 
  94. MAXSIZE=500, 
  95. FILEGROWTH=1 ), 
  96.  
  97. FILEGROUP [AirAvCache FG12] 
  98. (NAME = 'AirAvCache FG12', 
  99. FILENAME = 
  100. 'D:\Data\FG12\AirAvCache FG12.ndf', 
  101. SIZE = 5MB, 
  102. MAXSIZE=500, 
  103. FILEGROWTH=1 ) 
 
 创建好后如图：
 
 
 
 打开FG1 文件夹 看到多了AirAvCacheFG1.ndf 文件
 
 
 
 创建分区函数
 
 
  1. USE AirAvCache 
  2. GO 
  3.  
  4. -- 创建函数  
  5.  
  6. CREATE PARTITION FUNCTION [AirAvCache Partition Range](DATETIME) 
  7. AS RANGE LEFT FOR VALUES ('2010-09-01','2010-10-01','2010-11-01',
  8. '2010-12-01','2011-01-01','2011-02-01','2011-03-01','2011-04-01',
  9. '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。
                            
                        

                    

                    
                    

                        
                            
                        
                    

                

                

                    相关文章
                

                    

                        

                            

                                

                                    
                                        详谈redis跟数据库的数据同步问题
                                    
                                

                            

                            

                                

                                    详谈redis跟数据库的数据同步问题
                                

                                

                                    《详谈redis跟数据库的数据同步问题》文章讨论了在Redis和数据库数据一致性问题上的解决方案,主要比较了先更新Redis缓存再更新数据库和先更新数据库再更新Redis缓存两种方案,文章指出,删除R...																									目录一、Redis 数据库数据一致性的解决方案1.1、更新Redis缓存、删除Redis缓存的区别二
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        oracle数据库索引失效的问题及解决
                                    
                                

                            

                            

                                

                                    oracle数据库索引失效的问题及解决
                                

                                

                                    《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引...																									目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        python使用watchdog实现文件资源监控
                                    
                                

                            

                            

                                

                                    python使用watchdog实现文件资源监控
                                

                                

                                    《python使用watchdog实现文件资源监控》watchdog支持跨平台文件资源监控,可以检测指定文件夹下文件及文件夹变动,下面我们来看看Python如何使用watchdog实现文件资源监控吧...																									python文件监控库watchdogs简介随着Python在各种应用领域中的广泛使用，其生态环境也
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        el-select下拉选择缓存的实现
                                    
                                

                            

                            

                                

                                    el-select下拉选择缓存的实现
                                

                                

                                    《el-select下拉选择缓存的实现》本文主要介绍了在使用el-select实现下拉选择缓存时遇到的问题及解决方案,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的...																									目录项目场景：问题描述解决方案：项目场景：从左侧列表中选取字段填入右侧下拉多选框，用户可以对右侧
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Mysql 中的多表连接和连接类型详解
                                    
                                

                            

                            

                                

                                    Mysql 中的多表连接和连接类型详解
                                

                                

                                    《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据...																									目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Python pyinstaller实现图形化打包工具
                                    
                                

                            

                            

                                

                                    Python pyinstaller实现图形化打包工具
                                

                                

                                    《Pythonpyinstaller实现图形化打包工具》：本文主要介绍一个使用PythonPYQT5制作的关于pyinstaller打包工具,代替传统的cmd黑窗口模式打包页面,实现更快捷方便的...																									目录1.简介2.运行效果3.相关源码1.简介一个使用python PYQT5制作的关于pyinstall
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        使用Python实现大文件切片上传及断点续传的方法
                                    
                                

                            

                            

                                

                                    使用Python实现大文件切片上传及断点续传的方法
                                

                                

                                    《使用Python实现大文件切片上传及断点续传的方法》本文介绍了使用Python实现大文件切片上传及断点续传的方法,包括功能模块划分（获取上传文件接口状态、临时文件夹状态信息、切片上传、切片合并）、整...																									目录概要整体架构流程技术细节获取上传文件状态接口获取临时文件夹状态信息接口切片上传功能文件合并功能小
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        python实现自动登录12306自动抢票功能
                                    
                                

                            

                            

                                

                                    python实现自动登录12306自动抢票功能
                                

                                

                                    《python实现自动登录12306自动抢票功能》随着互联网技术的发展,越来越多的人选择通过网络平台购票,特别是在中国,12306作为官方火车票预订平台,承担了巨大的访问量,对于热门线路或者节假日出行...																									目录一、遇到的问题？二、改进三、进阶–展望总结一、遇到的问题？1.url-正确的表头：就是首先ur
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        C#实现文件读写到SQLite数据库
                                    
                                

                            

                            

                                

                                    C#实现文件读写到SQLite数据库
                                

                                

                                    《C#实现文件读写到SQLite数据库》这篇文章主要为大家详细介绍了使用C#将文件读写到SQLite数据库的几种方法,文中的示例代码讲解详细,感兴趣的小伙伴可以参考一下...																									目录1. 使用 BLOB 存储文件2. 存储文件路径3. 分块存储文件《文件读写到SQLite数据库China编程的方法》博客中，介绍了文
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Redis主从复制实现原理分析
                                    
                                

                            

                            

                                

                                    Redis主从复制实现原理分析
                                

                                

                                    《Redis主从复制实现原理分析》Redis主从复制通过Sync和CommandPropagate阶段实现数据同步,2.8版本后引入Psync指令,根据复制偏移量进行全量或部分同步,优化了数据传输效率...																									目录Redis主DodMIK从复制实现原理实现原理Psync: 2.8版本后总结Redis主从复制实
                                

                                

                                    阅读更多...
                                

                            

                        

                    

            

        

    


    


    

        

            Copyright China编程   23002807@qq.com
        

        

            沪ICP备2023033072号-2