列存索引常见问题

本文汇总了PolarDB MySQL版列存索引相关的常见问题。

说明

关于列存索引的使用问题请进钉钉群咨询,群号:27520023189。

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

如果需要使用列存索引功能进行查询加速,需要执行以下操作:

  1. PolarDB MySQL版集群中增加一个只读列存节点(增加只读节点时打开列存索引开关)。增加带有列存索引的只读节点的步骤,请参见添加只读列存节点

  2. 为某些需要加速访问的表增加列索引。具体的方式是使用CREATE TABLE语句或者ALTER TABLE语句在表的COMMENT字段中增加COLUMNAR=1的字段,列索引就绪后,优化器根据查询代价,自动选择是否使用列索引进行查询。为某些表增加列索引的语法,请参见建表时创建列存索引的DDL语法

  3. SQL需要被转发到列存节点,并且查询代价高于一定阈值,优化器会自动选择使用列索引进行查询。关于SQL语句的自动引流和手动引流,请参见配置集群地址实现行存和列存分流

如何查看列存索引状态?

使用ALTER TABLE语句为现有表动态添加列索引后,列索引的构建是在只读列存节点异步完成的,通过连接开启行存/列存分流的集群地址或者直连列存节点,可以查询INFORMATION_SCHEMA.IMCI_INDEXES表以获得列索引的构建状态,只有COMMITTED状态的列索引才可以用于查询。对于构建中的列存索引可以查询INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS来获取索引构建进度信息。更多信息请参见查看索引状态

说明

使用DMS登录数据库时,默认连接的是集群主地址。如果您需要连接集群地址或直连列存节点,请参考以下内容:

  • 连接集群地址。

    登录据管理DMS 5.0,在新增实例页面,录入方式选择连接串地址,并录入集群地址。详情请参见云数据库录入

  • 直连列存节点。

    首先,您需要为目标列存节点新增自定义集群地址,且该自定义集群地址只包含目标列存节点。再登录数据管理DMS 5.0,在新增实例页面,录入方式选择连接串地址,并录入该只读列存节点的自定义集群地址。详情请参见云数据库录入

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

通过EXPLAIN语句可以查看SQL语句的执行计划,如果执行计划中存在IMCI Execution Plan,则说明此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中的操作具有一一对应的关系。例如,CTableScan算子表示对某张表进行扫描,HashJoin 算子对应SQL语句中的JOIN部分,HashGroupby算子对应SQL语句中的GROUP BY部分,但是也有一些算子(例如Sequence)是查询优化过程中生成的,与原SQL中的语句没有对应关系。

如何解决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的阈值做索引选择。

您可以使用Hint设置单条SQL的查询阈值,强制使用列存索引:

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

同样也可以强制某条SQL不使用列存索引:

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()存储过程可以获得某个SQL语句所需的DDL语句,按照这个DDL语句构建列存索引,可以保证该SQL语句完全被列存索引覆盖。更多信息请参见获取创建列存索引的DDL语句

dbms_imci.columnar_advise('<query_string>');

使用dbms_imci.columnar_advise_begin()dbms_imci.columnar_advise_end()以及dbms_imci.columnar_advise()接口,可以获得一批SQL语句所需的DDL语句。更多信息请参见批量获取创建列存索引的DDL语句

列存节点的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语句中对应的参数说明如下:

参数

说明

SCHEMA_NAME

库名。

TABLE_NAME

表名。

EXTENT_SIZE

EXTENT的大小。单位:Byte。

TOTAL_EXTENT_COUNT

总EXTENT数。

USED_EXTENT_COUNT

已使用的EXTENT数。

FREE_EXTENT_COUNT

空闲EXTENT数。

RAW_DATA_SIZE

压缩前的列数据大小。单位:Byte。

FILE_SIZE

压缩后的列数据大小。单位:Byte。

说明

该参数适用于PolarDB MySQL版8.0.1.1.33之前的版本。

CMP_DATA_SIZE

压缩后的列数据大小。单位:Byte。

说明

该参数适用于PolarDB MySQL版8.0.1.1.33及之后的版本。

增加列存索引后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两种事务隔离级别。

说明
  • 对于REPEATABLE_READ事务隔离级别,在使用列存索引时,需要使用仅包含只读列存节点的自定义连接地址。

  • 集群版本为PolarDB MySQL版8.0.1.1.40及以上,以及8.0.2.2.21及以上的版本,对于某些生态工具在查询会话中隐式地将事务隔离级别设置成不支持的场景(如Metabase BI工具会设置为READ_UNCOMMITTED),可以通过SET imci_ignore_unsupported_isolation_level=ON来强制使用READ_COMMITTED,或者在ODBC/JDBC的连接串选项部分加上SESSION变量设置,例如Metabase可以在连接串中添加session Variables=imci_ignore_unsupported_isolation_level='ON'