列存索引
本文主要介绍列存索引的基础用法和进阶用法。
背景信息
索引是加速数据查询的重要方法。云原生多模数据库 Lindorm不仅支持高性能主键查询和基于搜索索引的高性能检索，也支持列存索引。列存索引可以增强宽表中海量数据的分析计算能力，主要适用于车联网与物联网的设备信息统计、电商领域的数据分析、物流行业的订单统计等场景。
前提条件
已开通列存索引功能。
重要列存索引功能目前处于邀测阶段，请联系Lindorm技术支持（钉钉号：s0s3eg3）开通该功能。
已开通计算引擎。具体操作，请参见开通与变配。
已开通文件引擎，且文件引擎的版本为4.0.0及以上版本。如需升级版本，请联系Lindorm技术支持（钉钉号：s0s3eg3)。
已开通宽表引擎，且宽表引擎的版本为2.5.0及以上版本。如需升级版本，请联系Lindorm技术支持（钉钉号：s0s3eg3)。
基础用法
假设示例表my_tbl的结构如下：
+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME | TYPE | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_tbl | pk0 | INT | true |
| my_tbl | pk1 | VARCHAR | true |
| my_tbl | pk2 | VARCHAR | true |
| my_tbl | col0 | INT | false |
| my_tbl | col1 | VARCHAR | false |
+------------+-------------+---------+----------------+创建列存索引
语法
CREATE INDEX index_name USING COLUMNAR
ON table_name(column_name(,..))
PARTITION BY ENUMERABLE (column_name(,...), bucket(bucket_num, column_name(,...)));参数 | 说明 |
index_name | 列存索引的名称，由大写字母、小写字母、数字、下划线（_）其中的一种或多种组成。 |
table_name | 宽表名称。 |
column_name(,...)) | 需要创建列存索引的字段列表，多个字段用英文逗号（,）分隔。目前支持创建列存索引的字段类型包括：TINYINT、SMALLINT、INTEGER、INT、BIGINT、LONG、FLOAT、DOUBLE、VARCHAR、BINARY、VARBINARY、BOOLEAN。 说明 该字段列表必须包含对应宽表的全部主键字段，若您需要为全部字段（主键和非主键）创建列存索引，可简写为 |
PARTITION BY ENUMERABLE(column_name(,...), bucket(bucket_num, column_name(,...))) | 指定索引数据按照枚举算法进行分区，从而提升查询过程中的检索能力。分区表达式包括普通分区表达式和bucket分区表达式，且普通分区表达式和bucket分区表达式中的字段均为宽表主键字段。
|
示例
以表my_tbl为例创建列存索引：
CREATE INDEX my_tbl_idx USING COLUMNAR
ON my_tbl(pk0, pk1, pk2, col0, col1)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0, pk1));查询列存索引
列存索引创建成功后，索引数据会持续构建，宽表作为主表会持续将表中的数据同步至列存索引表中。数据同步包括存量数据同步和增量数据同步，增量数据同步过程中，索引数据与主表的数据会存在延迟，延迟时间小于30分钟。您可以使用以下SQL命令查看列存索引的状态。
语法
SHOW INDEX FROM table_name;参数说明
table_name：宽表名称。
示例
SHOW INDEX FROM my_tbl;返回结果：
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+-----------------------+-----------+--------------------------------------------------+
| TABLE_SCHEMA | DATA_TABLE | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS | INDEX_TYPE | INDEX_COVERED | INDEX_COLUMN | INDEX_TTL | INDEX_DESCRIPTION |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+-----------------------+-----------+--------------------------------------------------+
| default | my_tbl | my_tbl_idx | ACTIVE | 2023-03-24 10:27:19.994 +0800 | COLUMNAR | NA | pk0,pk1,pk2,col0,col1 | | index table: |
| | | | | | | | | | __columnar_index_db__.default_my_tbl_my_tbl_idx; |
| | | | | | | | | | task id: 3b5bdbd5-2a38-47f3-8ef7-a2edf08dac51; |
| | | | | | | | | | partition by: [pk1,pk2,hash(128,pk0,pk1)]; |
| | | | | | | | | | attributes: [] |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+-----------------------+-----------+--------------------------------------------------+返回结果说明：
参数 | 说明 |
TABLE_SCHEMA | 宽表所在的数据库名称。 |
DATA_TABLE | 宽表的名称。 |
INDEX_NAME | 列存索引的名称。 |
INDEX_STATE | 列存索引的状态。取值如下：
|
INDEX_PROGRESS | 数据同步的进度。
|
INDEX_COLUMN | 列存索引对应的宽表字段。 |
INDEX_DESCRIPTION | 列存索引的详细信息，包括列存索引表的所在位置、同步任务ID、分区信息、用户属性等。 |
使用列存索引
创建列存索引可以增强宽表海量数据的分析计算能力，您可以在SELECT查询语句中指定Hint /*+ _use_ldps_ */，将查询请求路由至计算引擎执行并使用列存索引加速查询，从而提升大数据计算的效率。
示例一：大数据统计
SELECT /*+ _use_ldps_ */ COUNT(*), SUM(col0), MIN(col0), MAX(col0)
FROM my_tbl
GROUP BY pk1
WHERE col0 > 100 AND col0 < 200 OR col0 > 500;示例二：大数据排序
SELECT /*+ _use_ldps_ */ pk0 + col0,
FROM my_tbl
ORDER BY pk1
WHERE col0 > 100 AND col0 < 200 OR col0 > 500
LIMIT 100;示例三：大数据关联
如果您为多个宽表创建了列存索引，也可以将宽表间的数据进行关联。
SELECT /*+ _use_ldps_ */ *
FROM my_tbl0 JOIN my_tbl1
ON my_tbl0.pk0 = my_tbl1.pk0
AND my_tbl0.pk1 = my_tbl1.pk1
LIMIT 100;通过select /*+ _use_ldps_ */ webui()获取WebUI地址，可以查看SELECT语句的执行信息。
删除列存索引
语法
DROP INDEX index_name ON table_name;参数说明
index_name：列存索引的名称。
table_name：宽表的名称。
示例
DROP INDEX my_tbl_idx ON my_tbl;当您删除宽表时，对应的列存索引也会被删除。
进阶用法
假设示例表my_tbl的结构如下：
+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME | TYPE | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_tbl | pk0 | INT | true |
| my_tbl | pk1 | VARCHAR | true |
| my_tbl | pk2 | VARCHAR | true |
| my_tbl | col0 | INT | false |
| my_tbl | col1 | VARCHAR | false |
+------------+-------------+---------+----------------+自定义列存表
宽表列存索引数据基于列存表存储，在创建列存索引时，您可以自定义列存表的名称，示例如下：
CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(*)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0, pk1))
WITH (`lindorm_columnar.user.index.database` = 'my_index_db',
`lindorm_columnar.user.index.table` = 'my_index_tbl');参数说明
my_tbl_idx：列存索引的名称。
my_tbl：宽表的名称。
my_index_db：列存表所在的数据库名称。
my_index_tbl：列存表的名称。
您可以自定义列存表，以便于通过计算引擎访问Lindorm列存数据，构建后续数据生产计算链路。具体操作，请参见访问Lindorm列存。
复杂分区表达式
假设示例表my_ts_tbl的表结构如下：
+------------+-------------+---------+----------------+
| TABLE_NAME | COLUMN_NAME | TYPE | IS_PRIMARY_KEY |
+------------+-------------+---------+----------------+
| my_ts_tbl | id | INT | true |
| my_ts_tbl | ts | LONG | true |
| my_ts_tbl | col0 | VARCHAR | false |
| my_ts_tbl | col1 | INT | false |
+------------+-------------+---------+----------------+在创建列存索引时，若宽表数据的主键字段不能直接作为列存索引的普通分区表达式，可以在普通分区表达式中包含计算逻辑，示例如下。
对宽表所有字段创建列存索引，将列存索引数据按时间戳字段
ts按天分区：CREATE INDEX my_ts_idx USING COLUMNAR ON my_ts_tbl(*) PARTITION BY ENUMERABLE (substring(from_unixtime(ts), 0, 10), bucket(128, id));列存索引创建完成后，您可以在查询语句中指定过滤条件并查询列存索引中的数据。
SELECT /*+ _use_ldps_ */ COUNT(1) FROM my_ts_tbl WHERE substring(from_unixtime(ts), 0, 10) = '2020-06-06';列存索引数据基于列存表存储，定义的复杂分区表达式会映射到底层列存表形成一个衍生列，您可以自定义衍生列的名称，示例如下：
CREATE INDEX my_ts_idx USING COLUMNAR ON my_ts_tbl(*) PARTITION BY ENUMERABLE (substring(from_unixtime(ts), 0, 10) AS date, bucket(128, id));
仅为增量数据构建列存索引
如果您需要跳过宽表中的存量数据，只为增量数据构建列存索引，可以指定参数lindorm_columnar.user.syncer.skip.fullsync = 'true'，示例如下：
CREATE INDEX my_tbl_idx USING COLUMNAR ON my_tbl(*)
PARTITION BY ENUMERABLE (pk1, pk2, bucket(128, pk0, pk1))
WITH (`lindorm_columnar.user.syncer.skip.fullsync` = 'true');配置列存索引查询参数
列存索引支持在连接串中添加计算引擎配置项。具体配置项，请参见作业配置说明。
./lindorm-cli -url <jdbc url>?hiveconf:key=value?hiveconf:key=value -username <用户名> -password <密码>其中，jdbc url为控制台获取的Lindorm 宽表SQL地址。如何获取，请参见查看连接地址。
配置项会在首次启动计算引擎节点时生效。计算引擎节点启动后需要变更配置，请在SparkUI界面的Kyuubi Query Engine页签中，单击kill关闭原有计算引擎节点。
JSON字段展开映射
列存索引支持在数据同步后，将表中JSON类型的字段展开存储。在创建列存索引时，定义JSON字段到列存字段之间的映射关系。
创建包含JSON字段的表。
CREATE TABLE IF NOT EXISTS my_json_tbl(id BIGINT, col1 INTEGER, col2 DOUBLE, json_col JSON, PRIMARY KEY(id));写入数据。
假设json_col的结构如下：
{ "a": { "b": { "c": "hello,world", "d": 123 }, "e": false }, "f": 3.14 }创建列存索引时，基于
WITH关键字，指定展开JSON字段的映射规则。CREATE INDEX columnar_idx USING COLUMNAR ON my_json_tbl(*) PARTITION BY ENUMERABLE (id%16, bucket(16,id)) WITH ( `lindorm_columnar.user.syncer.lci.jsonMapping.json_col` = 'a.b.c VARCHAR, a.e BOOLEAN ,f DOUBLE');参数说明
lindorm_columnar.user.syncer.lci.jsonMapping.json_col：指定表中需要映射的JSON列，此处指定列json_col。
a.b.c VARCHAR, a.e BOOLEAN ,f DOUBLE：指定每一个展开字段，使用英文逗号（,）隔开。
字段名：展开字段对应的JSON路径，使用半角句号（.）隔开。例如
a.b.c。字段类型：支持的数据类型为BOOLEAN、BYTE、SHORT、INTEGER、LONG、FLOAT、DOUBLE和VARCHAR。
说明您可以通过
WITH关键字指定多个lindorm_columnar.user.syncer.lci.jsonMapping，为多个JSON字段创建映射。
常见问题
Q：创建列存索引后，是否会产生额外费用？
A：会。主要包括列存索引数据的存储费用，以及主表和列存索引之间数据同步实际使用的CU费用。
Q：分区表达式中是否可以包含非主键字段？
A：不可以。分区表达式中的字段必须全部为主键字段。
Q：bucket分区表达式中，是否可以包含复杂分区表达式？
A：不可以。bucket分区表达式中仅包括
bucket_num和bucket分区字段。Q：分区数目过大或过小会有什么影响？
A：分区数目过大，会导致元数据膨胀，从而影响查询效率，因此建议单分区数据量大于50 MB，bucket分区表达式中的
bucket_num小于1024。分区数目过小，会影响数据读写吞吐或造成数据倾斜，建议单分区数据量小于512 MB。Q：是否可以通过Lindorm计算引擎直接访问列存索引数据？
A：可以。您需要先自定义索引表的名称再通过计算引擎访问列存索引数据。具体操作，请参见访问Lindorm列存。
重要请谨慎执行列存索引表的修改操作，如需修改列存索引表，请联系Lindorm技术支持（钉钉号：s0s3eg3）。
Q：能否为同一个宽表创建多个列存索引？
A：不能。
Q：若已经对部分宽表字段创建列存索引，能否新增更多字段？
A：不能。
Q：宽表中数据因为TTL过期被清除或使用TRUNCATE命令被删除后，列存索引数据是否会被自动清除？
A：不会。