列存索引创建指南

本文介绍如何根据业务场景为目标表创建查询性能更优的列存索引（CCI）。

适用场景

重要

列存索引是基于日志节点（CDC）链路异步构建而成，可以保证数据的查询一致性，但由于主实例向列存只读实例同步数据存在秒级延迟，因此对于实时性要求极高的场景，不建议使用CCI。

1. 混合负载场景或需要给复杂查询加速的场景。

说明

列存索引可以有效提升复杂查询（AP）的性能。

1. 冷数据归档场景。

说明

因为列存的读节点（CN节点）可以通过存储在OSS中的列存索引数据来获取元数据，所以您可以把冷数据使用列存索引技术转储至OSS，以降低存储成本。更多信息，请参见冷数据归档（TTL）。

1. 历史快照保存和查询场景。

说明

列存索引可以看作不会过期的历史数据副本，可以保存历史快照，并提供历史快照查询，常用于审计和备份业务。

1. ETL场景。

说明

列存索引可以看作是主实例的数据副本，您可以连接列存只读实例，使用其中数据提供数据提取、转换、加载（Extract Transform Load，ETL）服务，并将数据库的数据转储到其他数据系统。

分区表

常用分区类型介绍

列存索引的分区是一种数据库设计技术中的概念，主要用于大型表的数据分片或分割，以提高查询性能和管理大量数据的效率。列存索引的分区策略语法如下：

PARTITION BY
HASH({column_name | partition_func(column_name)})
| KEY(column_list)
| RANGE({column_name | partition_func(column_name)})
| RANGE COLUMNS(column_list)
| LIST({column_name | partition_func(column_name)})
| LIST COLUMNS(column_list)} }

说明

• 范围分区（RANGE）：根据某一列的取值范围划分数据。例如：包含历史销售记录的表，根据销售年份进行范围分区，不同年份的数据会被存储在不同的分区中。

• 列表分区（LIST）：根据列值是否属于某个预定义列表划分数据，常用于SAAS场景。例如：用户数据表可以根据用户所在国家进行列表分区，不同国家的用户数据会被分别存储在对应的分区中。

• 哈希分区（HASH/KEY）：根据列值的哈希值划分数据，可以保证数据均匀分布。适用于无法预测的数据分布情况，或者需要均匀分布数据的情况。

• 更多信息，请参见分区类型。

设置分区数的原则

分区数理论上与表的数量以及列存只读实例的规格有关。如果不明确指定分区数，则默认分区数为16。然而，通常不建议使用默认的分区数。一般遵循的原则是：列存索引的分区数的建议值为计算节点的个数*计算节点核数。考虑到未来数据增长的潜力，最终确定的分区数也可以高于这个建议值。

说明

多表关联操作时，为了减少需要数据重新分布的数据量，建议同一个实例中涉及的表，分区数保持一致。

选择分区策略的原则

• 因为列存索引主要是为了提升AP类查询的性能，且该类查询大多是聚合和关联查询，所以为了发挥列存并行扫描和查询优势，建议采用哈希分区。

• 查询条件有明确的时间含义，这种情况下建议将日期、时间类型的字段作为二级分区，除了该场景，其他场景如非必要请不要为列存索引创建二级分区。更多信息，请参见二级分区。

• 没有明确的范围查询，不建议使用范围分区；没有基于某个预定义列表值的查询，不建议使用列表分区；即便查询具备上述特征，也应优先考虑依赖行存来满足上述查询。

• 业务上依托CCI实现冷数据归档，建议使用时间列进行范围分区。

选择分区键的原则

• 选择值分布均匀的字段作为分区键，例如交易ID、设备ID、用户ID或者自增列作为分区键。

说明

尽量不要选择日期、时间和时间戳类型的字段作为分区键，写入时容易发生倾斜影响写入性能，且多数查询通常是限定了日期或者时间段，如：查询最近一天或者一个月的数据，可能会导致要查询的数据只存在于一个节点上，无法充分利用分布式数据库中所有节点的处理能力。这种情况下可以考虑将日期、时间类型的字段建议作为二级分区来考虑。

• 尽可能将需要JOIN和GROUP BY的字段作为分区键，可以有效减少数据重分布。例如，需要按照顾客维度查看历史订单信息，可以选择顾客ID作为分区键。

• 尽可能选择频繁出现在非范围查询条件中的字段作为分区键，从而实现按分区键进行数据裁剪。

• 每张表只能选择一个分区键，一个分区键可以包含一个或多个字段。分区键的字段越少就越在复杂的查询场景中具备通用性。

重要

• 在创建表时，如果没有指定分区键，系统会将主键作为分区键；对于没有显式定义主键的表，系统会将隐式主键作为分区键。

• 创建列存索引后，可以通过执行check columnar partition db_name.tbl_name命令来查看各个分区的数据量，以此判断所选分区键是否合适，是否存在数据倾斜的情况。

排序键

排序键介绍

列存索引的排序键定义了数据在索引文件中的排序方式，即数据按照该列有序存储。每个列数据块的元数据包含了该列数据块中所有数据的最小值和最大值等信息。在查询数据时，通常需要遍历指定列的所有列数据块。启用Pruner功能后，会根据查询条件与元数据信息将所有列数据块分为三类：相关、可能相关和不相关。在读取数据时，仅考虑相关和可能相关的列数据块。由于列数据块可以有不同的排列顺序，形成不同的组合，Pruner功能也会产生不同的过滤效果。因此，您可以改变查询条件来调整列数据块的排列顺序，以进一步提高查询性能。

[MISSING IMAGE: image, image ]

选择排序键的原则

• 数据表被频繁使用范围查询的场景下，建议使用该范围条件的列作为排序键。

• 使用分页查询的场景下，建议使用ORDER BY列作为排序键

• 其他场景下，建议使用分区键作为排序键。

字典编码列

字典编码列介绍

字典编码可以将字符串的比较转换为数字的比较，从而提升字符串列的GROUP BY、FILTER等查询的性能，并提升数据的压缩比，进一步降低存储成本。在PolarDB-X中，可以在创建列存索引时对指定字段进行字典编码，即为这些字段的值构建字典映射。示例：

# 显示指定字典列
DICTIONARY_COLUMNS='col1,col2';
# 显式指定字典列创建列存索引
CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16
dictionary_columns='order_id,seller_id';

选择字典编码列的原则

• 建议将基数较小的字符列设置为字典编码列，例如该列代表性别、地区等具备有限种类的分类信息。

• 不建议将所有的字符列都设置为字典编码列，因为这样做会带来额外的编码、解码开销。

说明

对于列基数较小的字符列使用字典编码不仅可以实现数据压缩，减少存储空间的占用，还能优化查询性能。然而，在分布式数据库查询中，会涉及到字典的解析和合并操作，这会引入额外的开销。因此，在查询过程中，基于字典的查询默认是关闭的。如果需要启用，需要将ENABLE_COLUMNAR_SLICE_DICT参数设置为TRUE。

常见问题

1. 在集群变配，是否会对分区数产生影响？

答：不会产生影响。

1. 列存索引是否支持修改分区键、排序键、分区数、字典编码列？

答：不支持。如有需要，请您删除并重建该列存索引。

1. 创建列存索引，需要购买列存只读实例吗？

答：可以直接在主实例上创建列存索引，如果要查询列存索引数据建议购买列存只读实例。