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行的块为单位)。