SQL Server 分区会在不更改文件组的情况下提高性能吗 [英] Will SQL Server Partitioning increase performance without changing filegroups

问题描述

场景我有一个 1000 万行的表.我将它分成 10 个分区，这导致每个分区有 100 万行，但我没有做任何其他事情(例如将分区移动到不同的文件组或主轴)

Scenario I have a 10 million row table. I partition it into 10 partitions, which results in 1 million rows per partition but I do not do anything else (like move the partitions to different file groups or spindles)

我会看到性能提升吗?这实际上类似于创建 10 个较小的表吗?如果我有执行键查找或扫描的查询，性能是否会提高，就好像它们是针对小得多的表进行操作一样?

Will I see a performance increase? Is this in effect like creating 10 smaller tables? If I have queries that perform key lookups or scans, will the performance increase as if they were operating against a much smaller table?

我试图了解分区与仅仅拥有一个索引良好的表有何不同，以及它可以用来提高性能的地方.

I'm trying to understand how partitioning is different from just having a well indexed table, and where it can be used to improve performance.

将旧数据(使用分区切换)从主表移到只读存档表是更好的方案吗?

Would a better scenario be to move the old data (using partition switching) out of the primary table to a read only archive table?

拥有一个包含 100 万行分区和 900 万行分区的表是否类似于(在性能方面)将 900 万行移动到另一个表而在原始表中只留下 100 万行?

Is having a table with a 1 million row partition and a 9 million row partition analagous (performance wise) to moving the 9 million rows to another table and leaving only 1 million rows in the original table?

推荐答案

分区不是性能特性，而是用于维护操作，例如在表之间移动数据和真正快速删除数据.将一个 10M 行的表划分为 10 个每个 1M 行的分区，不仅不会提高大多数查询的性能，而且可能会使很多查询变慢.

Partitioning is not a performance feature, is for maintenance operations like moving data between tables and dropping data real fast. Partitioning a 10M rows table into 10 partitions of 1M rows each not only that it won't increase most queries performance, will likely make quite a few of them slower.

任何查询都不能针对单个分区的较小行集进行操作，除非可以确定查询仅需要该分区中的行.但这可以通过正确选择表上的聚集索引或至少一个良好的覆盖非聚集索引来解决，并且总是更好.

No query can operate against a smaller set of rows of a single partition unless the query can be determined that it only needs row in that partition alone. But this can be solved, always, and much better, by properly choosing the clustered index on the table, or at least a good covering non-clustered index.

这篇关于SQL Server 分区会在不更改文件组的情况下提高性能吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！