列存索引常见问题

如何使用PolarDB MySQL版的列存索引功能？

如何查看列存索引状态？

如何确认SQL使用了列存索引/如何查看列存索引的SQL执行计划？

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

如何解决SQL语句的执行计划不使用列索引/查询不走列存/加了列存节点后SQL执行计划没有变化/列索引是否支持某条SQL/“行存/列存自动引流”功能不生效的问题？

新增列存节点后，需要为SQL语句所查询的表增加列存索引，且SQL语句的查询代价超过一定阈值，该SQL语句才会使用列索引进行查询。另外，SQL语句需要被转发到列存节点，才可以使用列存索引进行查询加速。一般来说，如果一条SQL语句无法使用列存索引进行查询，可以按照以下步骤进行问题排查：

1. 确认SQL是否被转发到列存节点。

   通过SQL洞察功能，可以确认SQL是否被转发到了列存节点。

   如果使用了集群地址并且开启了“行存/列存自动引流”功能，则数据库代理会自动将预估查询阈值超过imci_ap_threshold的SQL转发到列存节点。您也可以在SQL语句的SELECT字样前增加/*FORCE_IMCI_NODES*/强制指定将SQL转发到列存节点。示例如下：

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

   更多信息请参见配置行列自动分流。

   说明

   创建一个新的endpoint直接连接列存节点，可以保证SQL语句一定会被转发到列存节点上执行。

2. SQL的查询代价是否高于一定阈值。

   在列存节点上，优化器会对SQL进行代价预估，如果预估代价高于设定的阈值cost_threshold_for_imci，则使用列存索引进行查询，否则使用原有的行索引进行查询。

   在确认SQL被转发到列存节点后，如果通过EXPLAIN查看执行计划依然没有使用列存索引，可以通过比较预估的执行代价与预设的阈值，判断是否是预估代价过小而没有使用列存索引。您可以通过查询Last_query_cost变量获取“上一条SQL的预估执行代价”：

   EXPLAIN SELECT * FROM t1;
   SHOW STATUS LIKE 'Last_query_cost';

   如果SQL预估的执行代价小于预设的cost_threshold_for_imci，可以考虑调整cost_threshold_for_imci 。例如，使用Hint调整单条SQL的预设阈值：

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. SQL所需的列是否被列索引完全覆盖。

   可以通过内置的存储过程dbms_imci.check_columnar_index()检查SQL语句中的表是否已创建列索引，示例如下：

   CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

   如果SQL没有完全被列索引覆盖，调用该存储过程会返回没有被覆盖的表和列；如果已经被列索引完全覆盖，调用该存储过程会返回空结果集。

4. 是否有不支持的SQL特性。

   通过查看使用限制列表，确认某个SQL特性是否支持列存索引。

如果上述检查都没问题，则该SQL一般都应该会使用列存索引进行查询。

列存节点是否能使用行索引？

列存只读节点可以看作普通的只读节点上新增了列索引的功能，因此列存节点上既可以像普通只读节点一样使用行索引，也可以使用列存索引，优化器根据cost_threshold_for_imci的阈值做索引选择。

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;

如何为SQL增加合适的列存索引？

一条SQL语句中使用的所有列都需要被列存索引覆盖，这条语句才能使用列存索引进行查询。如果SQL语句中涉及到的列没有被列存索引覆盖，可以通过CREATE TABLE或者ALTER TABLE语句增加列索引。PolarDB MySQL版提供一系列的内置存储过程以辅助此操作。

dbms_imci.columnar_advise('<query_string>');

列存节点的CPU使用率高/内存使用率高/如何配置列存节点的CPU和内存监控？

• 列存索引的默认配置是单条SQL并发执行，并且在执行时可以用满所有CPU。当存在多条SQL同时执行时，数据库内部的调度器会对SQL进行调度，动态降低各个SQL的CPU上限以及内存使用上限。因此，相对于其他节点，列存节点的平均CPU使用率和内存都相对较高。您可以通过调整imci_max_dop参数，控制单条SQL的最大并发度（单条SQL最多使用多少核的CPU）。

• 一般建议CPU的监控阈值配置为CPU使用率的70%，内存监控阈值配置为内存使用率的80%。

• PolarDB MySQL版支持同一个集群内不同节点的规格异构，可以单独为列存节点升级/降低配置。一般建议列存节点的规格至少在8核16 GB及以上。

PolarDB MySQL版 5.6/5.7版本是否支持列存索引？

PolarDB MySQL版5.6/5.7版本不支持使用列存索引功能。PolarDB MySQL版8.0版本支持使用列存索引功能。

列存索引IMCI的使用限制是什么/列存索引是否支持全文索引/使用列存索引的查询是否兼容MySQL？

列存索引的使用方式完全兼容MySQL，但是一些不常见的查询特性，比如某些时空类型的表达式、全文索引以及某些形式的关联子查询，目前尚未完全支持，使用了这些查询特性的SQL语句无法使用列索引（会默认使用行索引进行查询）。有关列索引的详细使用限制请参见使用限制。

INSERT INTO SELECT/CREATE TABLE AS SELECT语句是否可以使用列存索引？

列存索引只能在单独的只读节点上查询，而INSERT/CREATE语句只能在主节点（RW节点）上执行，因此如果需要使用列存索引进行INSERT INTO SELECT/CREATE TABLE AS SELECT语句的查询，需要使用列存索引的ETL功能。更多详细信息请参见使用列索引加速ETL。

列存索引功能是否收费/普通只读节点是否支持列存索引？

列存索引功能不单独收费，但是使用列存索引需要单独增加一个只读节点并且在这个只读节点上打开列存索引。这个新增的只读节点，以及后续为某些表新增的列存索引所带来的额外存储，会带来额外的费用。

普通只读节点不支持列存索引。

列存索引会新增多少额外存储空间？

列存索引数据按列组织，因此压缩比更高。相比行存一般有3~10倍的压缩比，会额外增加10%~30%左右的空间。

如何查看列存索引占用的存储空间容量？

• 集群版本为PolarDB MySQL版8.0.1.1.32版本及之前的版本时，您可以通过information_schema中的系统表imci_columns来查看列索引所在的表占用的存储空间容量和列压缩比。如查看test库中含有列存索引的test表占用的存储空间容量和压缩比，SQL语句如下：

  SELECT
    SCHEMA_NAME, TABLE_NAME,
    SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
    SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
    SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE,
    SUM(RAW_DATA_SIZE) / SUM(FILE_SIZE) AS COMPRESS
  FROM
    information_schema.imci_columns
  WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

• 集群版本为PolarDB MySQL版8.0.1.1.33版本及之后的版本时，您可以通过information_schema中的系统表imci_data_files查看存储空间容量，通过系统表imci_columns查看列压缩比。如查看test库中含有列存索引的test表占用的存储空间容量和列压缩比，SQL语句如下：

  • 查看含有列存索引的表test占用的存储空间容量。SQL语句如下：

    SELECT
        SCHEMA_NAME, TABLE_NAME,
        SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
        SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
        SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • 查看列压缩比的SQL语句如下：

    SELECT
        SCHEMA_NAME, TABLE_NAME,
         SUM(RAW_DATA_SIZE) / SUM(CMP_DATA_SIZE) AS COMPRESS_RATIO
    FROM
        INFORMATION_SCHEMA.IMCI_COLUMNS
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

上述SQL语句中对应的参数说明如下：

增加列存索引后INSTANT DDL不生效是什么原因？

• PolarDB MySQL版8.0.1.1.42之前，以及8.0.2.2.23之前的版本，对于创建了表级列存索引的表上执行的加列操作，不再适用于秒级加列逻辑，原因在于涉及到列存索引结构的变更与索引数据重建。

• PolarDB MySQL版8.0.1.1.42及以上，以及8.0.2.2.23及以上的版本，已支持在创建了表级列存索引的表上使用INSTANT DDL。该功能与旧版的重建模式不兼容，即需要将参数imci_enable_add_column_instant_ddl的值设置为OFF。且需要保证表上有主键。

增加列存索引后AlTER TABLE加减列时间变长是什么原因？

一般来说执行加减列变更操作时，都会涉及到表数据重建。当原表上包含列存索引时，表数据重建的同时也需要重建列存索引的数据，而列存索引数据重建的过程需要写入Redo log，通常列存索引会覆盖比较多的列，从而列存索引数据重建Redo log与原表数据大小成正比。相比没有列存索引的表重建过程中会增加IO数据量，从而表现为执行时间会变长。

增加列存索引是否会影响写入性能？

增加列存索引对写入性能的影响基本在5%以内，使用Sysbench测试集测试oltp_insert workload，增加列存索引后写入性能下降约为3%。

列存索引支持哪些事务隔离级别？

列存索引支持READ_COMMITTED和REPEATABLE_READ两种事务隔离级别。