SQL Server 2012:性能列存储索引与B树

时间:2022-08-27 10:59:26

One of the benefits of a columnstore-index is that the data of a single column is stored "next to each other on disk". This stands for better compression and faster read times.

columnstore-index的一个好处是,单个列的数据“彼此相邻地存储在磁盘上”。这代表更好的压缩和更快的读取时间。

However; when using a B-tree (a regular non-clustered index), aren't the leafs of that tree the data itself? So when I make an index on column A, won't all the fields of column A appear sorted on disk in parallel to the table? This would mean the data could be compressed and could be read fast.

然而;当使用B树(常规的非聚集索引)时,数据本身不是那棵树的叶子吗?因此,当我在列A上创建索引时,列A的所有字段是否都不会与表并行排列在磁盘上?这意味着数据可以被压缩并且可以快速读取。

So in short: when exactly is a columnstore-index more performant than a B-tree on a single column?

简而言之:当一个列存储索引确实比单个列上的B树更高效时?

Thanks in advance!

提前致谢!

1 个解决方案

#1


1  

So when I make an index on column A, won't all the fields of column A appear sorted on disk in parallel to the table?

因此,当我在列A上创建索引时,列A的所有字段是否都不会与表并行排列在磁盘上?

Yes, column A will be sorted. But all other columns won't. A column store index stores all columns in sorted order (in chunks of 2^20 rows).

是的,将对A列进行排序。但所有其他专栏都不会。列存储索引按排序顺序存储所有列(以2 ^ 20行的块为单位)。

#1


1  

So when I make an index on column A, won't all the fields of column A appear sorted on disk in parallel to the table?

因此,当我在列A上创建索引时,列A的所有字段是否都不会与表并行排列在磁盘上?

Yes, column A will be sorted. But all other columns won't. A column store index stores all columns in sorted order (in chunks of 2^20 rows).

是的,将对A列进行排序。但所有其他专栏都不会。列存储索引按排序顺序存储所有列(以2 ^ 20行的块为单位)。