未分区的表, 相当于只有一个分区, 只能存储在一个 FileGroup 中; 对表进行分区后, 每一个分区都存储在一个 FileGroup, 或分布式存储在不同的 FileGroup 中. 对表进行分区的过程, 实际上是将逻辑上完整的一个表, 按照特定的字段拆分成多个分区, 分散到相同或不同的 FileGroup 中, 每一个部分叫做表的一个分区(Partition), 一个分区实际上是一个独立的, 内部的物理表. 也就是说, 分区表在逻辑上是一个表, 而在物理上是多个完全独立的表.
一, 分区对性能的提升
分区对性能的提升, 主要体现: 系统的物理硬盘分担 IO, 把资源的争用局限在特定的分区中.
1, 分摊 IO
对表分区后, 把表数据分散到不同的文件组中, 每个文件组可以存放在不同的物理硬盘上, 这就使得数据的查询可以分摊到不同的物理硬盘上, 以并发方式调用各个硬盘的 IO 资源, 也就是说, 把数据分配给多个物理硬盘, 这些物理硬盘同时读写数据, 成倍提升数据读写的速度.
当查找的数据位于某一个分区内时, 那么分区使得对数据的操作被局限在一个分区中, 而不需要从整个数据集中进行读写, 从而减少读写数据所需要的 IO 次数.
2, 降低锁粒度
当表分区后, 加锁的粒度从表级别降低到分区级别, 这使得对一个分区执行更新操作, 同时不会影响另一个分区的读取操作. 因此, 分区可以降低并发查询系统产生死锁和阻塞的概率, 增加数据操作的并发度, 从整体上提高系统的性能.
二, 调整分区
分区能够提升性能, 就在于把数据分散, 如果数据不能分散到不同的物理硬盘, 那么对性能的提升将大打折扣, 因此, 在对表进行分区时, 应尽量使得每个分区存储的数据相同. 分区大小增加的速度跟文件组所在的硬盘空闲空间有关, 空闲空间越大, SQL Server 分配给该空间的数据量越大, 导致该分区的大小增加越快. 为了使每个分区的数量尽量均衡, 应使得每个文件组的大小保持相同, 不同的物理硬盘容量也尽量保持相同.
另外, 数据访问的频次是不同的, 有些数据可能被经常访问, 而有些数据很少被访问. 根据数据被访问的频次, 可以把常用的数据切换到性能最好的文件组中, 也就是说, 该文件组中的文件创建在性能最好的硬盘上, 把不常用的数据切换到性能较差的文件组中, 这样, 常用数据的访问会以最快的速度读写. 对于那些归档的数据, 可以使用分区切换到临时表, 再转移到备用的数据库服务器中.
还有, 要合理选择分区列, 尽量把可能同时修改的数据分散到不同的分区中, 减少资源争用, 使互斥的操作在不同的分区同时进行, 增加系统执行查询的并发度.
调整分区, 不仅以来业务逻辑, 还要对分区的统计数据有所了解.
1, 查看分区
通过系统视图: sys.partitions 查看分区对象 (Table 或 index) 的基本信息, 在该视图中, 如果 index_id=0, 表示基础表 (Underlying Table) 是堆 (Heap) 结构, 堆表是没有创建的聚集索引的存储结构; 如果 index_id=1, 表示基础表是平衡树 (B-Tree) 结构, 在堆表上创建聚集索引, 可以把堆表结构转换为平衡树 (B-Tree) 结构; 如果 index_id>1, 表示索引是非聚集索引. 由于基础表要么是堆表结构, 要么是 B-Tree 结构, 因此, index_id 只会是 0, 或者 1, 不可能同时存在 1 和 0.
data_compression 字段表示每个分区的数据压缩类型, rows 字段表示每个分区的近似的数据总行数. 通过统计 rows 字段, 能够快速统计基础表近似的总的数据行数量, 设置条件 index<=1, 表示只统计聚集索引或堆表的总数据行数量.
- select sum(rows) as ApproximateTotalRows
- from sys.partitions
- where object_id=object_id('xx.yyy','U')
- and index_id<=1
2, 统计每个分区占用的存储空间
在做分区时, 应尽量保证每个分区的数据行总量均匀分布, 每个分区占用的存储空间均匀分布, 避免单个分区过大, 系统视图 sys.dm_db_partition_stats 能够查看每个分区所占用的 Pages 数量.
- select ps.partition_id,
- ps.object_id,
- ps.index_id,
- ps.partition_number,
- ps.in_row_data_page_count,
- ps.in_row_used_page_count,
- ps.in_row_reserved_page_count,
- ps.lob_used_page_count,
- ps.lob_reserved_page_count,
- ps.row_overflow_used_page_count,
- ps.row_overflow_reserved_page_count,
- ps.used_page_count,
- ps.reserved_page_count,
- ps.row_count
- from sys.dm_db_partition_stats ps
- where ps.object_id=object_id('xx.xx','U')
3, 统计每个分区对象的占用的总的 Pages 数量
- select
- ps.object_id,
- ps.index_id,
- sum(ps.in_row_data_page_count) as data_pages,
- sum(ps.used_page_count) as used_pages,
- sum(ps.reserved_page_count) as reserved_pages
- from sys.dm_db_partition_stats ps
- where ps.object_id=object_id('xx.xx','U')
- group by ps.object_id,ps.index_id
4, 查看每个分区的分配单元(Allocation Unit)
SQL Server 为每个分区分配了一个分配单元(allcotion unit), 用于为该分区分配存储空间, 通过系统内部视图: sys.system_internals_allocation_units, 能够查看该 alloction unit 分配的 Page 类型等信息.
- select *
- from sys.system_internals_allocation_units
- where container_id=72057621135294464 --partition id
参考文档:
sys.dm_db_partition_stats (Transact-SQL)
来源: https://www.cnblogs.com/ljhdo/p/10098835.html