全网最专业PolarDB-MySQL 大SQL解决方案--之如何添加列式索引（一）

AustinDatabases

发布于 2026-03-12 19:26:58

最近开始在大量的应用PolarDB的IMCI的功能，多个业务线在并发，都在测试。不同的业务线测试的结果可能也不尽相同，使用的方法和方案也都有各自的特点。

我特别想把PolarDB的IMCI推广的更好，让更多的业务享受到PolarDB的插件化数据库的特性。这里我准备写几期帖子，把我研究的部分也都和大家一一展示，如果有同道中人，也可以反馈您的使用中的问题，我们一并和阿里云的老师进行沟通，相信他们会做出更好的功能并逐步完善他。

今天我们是IMCI的第二集，关于操作IMCI的语法，以及一些技巧。IMCI是PolarDB for MySQL的一个功能，原理是在POLARDB的数据库上增加节点，通过IMCI列式节点的方案，来解决需要列式需要解决的数据查询的问题。

这里我们需要先统一什么能做，什么不能做。

1 封装在事务内的，有写，有读的我们不建议，我们建议的是将难以优化，和复杂的SQL（select）语句单独的封装，否则您的SQL很难进入IMCI的节点，事务的逻辑性很可能导致您的SQL 还是走了主节点（行存）。

2 临时表不支持，在mysql中的临时表temporary table 是不支持走列式的，所以在使用polardb的时候，不要使用MYSQL的临时表

3 MySQL中的虚拟列尽量不要使用，如果要使用需要将 imci_enable_virtual_column 设置为on

4 IMCI使用仅仅加速 select语句，对于select for update select for share 的语句是无法支持的。

5 在撰写时，使用了mysql 的窗口函数中的 over() 使用了 unbounded preceding

SELECT
    time,
    subject,
    val,
    SUM(val) OVER (
        PARTITION BY subject
        ORDER BY time
        ROWS UNBOUNDED PRECEDING  --- window function 中的 frame 定义，IMCI 不支持
    ) AS running_total
FROM
    observations;

6 在查询充出现如下的语句撰写的方式都不可以走IMCI节点

1 select ..... from 表 group by (select ......) as 表名

2 select ..... from 表 where 条件 in (select * from .... where 条件)

3 子查询中出现窗口函数，或者子查询中出现了having

4 子查询包含union

5 不要使用加密压缩，JSON 字符类的一些函数（soundex,match load_file,timestamp）,spatial 函数，都不可以使用。

更详细的解释可以看这个页面

https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/limits-3?spm=a2c4g.11186623.help-menu-2249963.d_5_26_4_0.4e8e42cd5hN18l

2 添加列式索引的语法在使用 IMCI的功能，需要数据库版本的支持必须在8.01.1.30后的版本才可以支持

添加列式索引有两种方案

1 针对全表进行添加 2 针对单列进行添加

CREATE TABLE t1(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

CREATE TABLE t2(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

这里注意 create table like 如果原表包含列式索引，则目标表也包含列式索引。

查看表的建立列式索引的命令 show create table 表名 full;

Create Table: CREATE TABLE `t2` (
  `col1` int(11) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(200) DEFAULT NULL,
  COLUMNAR INDEX (`col1`,`col2`,`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='COLUMNAR=1'

全表添加列式索引通过创建表的语法

CREATE COLUMNAR INDEX ON <db_name>.<table_name>;

CREATE COLUMNAR INDEX ON <table_name>;

快速删除表中所有的列式索引

DROP COLUMNAR INDEX ON <db_name>.<table_name>;

DROP COLUMNAR INDEX ON <table_name>;

修改，添加，删除列式索引的语句

ALTER TABLE t5 COMMENT 'COLUMNAR=1'; 整个表添加列式

ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1', MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1'; 单独列多列添加列式。

删除列式索引

ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0', MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0'; 删除单列，多列列式索引。

ALTER TABLE t7 COMMENT 'COLUMNAR=0';

增加列时的同时增加列式索引

ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

这里注意PolarDB FOR MYSQL 8.0.1.1.42 以上，以及8.0.2.2.23及以上，都需要将 imci_enable_add_column_instant_ddl 关闭，且需要表上有主键，才可以使用 instant ddl

在大表创建索引时通过 SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES 来查看表的 state的状态，如果状态在RECOVERING时说明索引没有建立完毕，状态在commit 则说明索引创建完毕。

关注列式索引创建速度的可以关注系统表，IMCI_ASYNC_DDL_STATS

Create Table: CREATE TEMPORARY TABLE `IMCI_ASYNC_DDL_STATS` (
  `SCHEMA_NAME` varchar(193) NOT NULL DEFAULT '',     -- 库名
  `TABLE_NAME` varchar(193) NOT NULL DEFAULT '',      -- 表名
  `CREATED_AT` varchar(64) NOT NULL DEFAULT '',       -- 任务创建时间戳
  `STARTED_AT` varchar(64) NOT NULL DEFAULT '',       -- 任务开始执行时间戳
  `FINISHED_AT` varchar(64) NOT NULL DEFAULT '',      -- 任务开始结束时间戳
  `STATUS` varchar(128) NOT NULL DEFAULT '',          -- 状态
  `APPROXIMATE_ROWS` bigint(8) NOT NULL DEFAULT '0',  -- 预估基线数据行数
  `SCANNED_ROWS` varchar(128) NOT NULL DEFAULT '',    -- 已扫描行数及百分比，实际行数可大于预估行数
  `SCAN_SECOND` bigint(8) NOT NULL DEFAULT '0',       -- 扫描已执行秒数
  `SORT_ROUNDS` bigint(8) NOT NULL DEFAULT '0',       -- 排序轮次，仅适用于带排序键场景
  `SORT_SECOND` bigint(8) NOT NULL DEFAULT '0',       -- 排序已执行秒数，仅适用于带排序键场景
  `BUILD_ROWS` varchar(128) NOT NULL DEFAULT '',      -- 排序结束写入行数及百分比，仅适用于带排序键场景
  `BUILD_SECOND` bigint(8) NOT NULL DEFAULT '0',      -- 排序结束写入执行秒数，仅适用于带排序键场景
  `AVG_SPEED` int(4) NOT NULL DEFAULT '0',            -- 任务开始后的平均速度，单位行每秒
  `SPEED_LAST_SECOND` int(4) NOT NULL DEFAULT '0',    -- 前一秒速度，单位行每秒，适用于扫描阶段和排序写入阶段
  `ESTIMATE_SECOND` bigint(8) NOT NULL DEFAULT '0'    -- 预计剩余时间，单位秒，适用于扫描阶段和排序写入阶段
) ENGINE=MEMORY DEFAULT CHARSET=utf8

以上是IMCI使用中的注意事项，和如何创建IMCI索引的部分内容。

置顶

PostgreSQL Bitmap scan 和 Index scan 优化一个SQL ，结果非常不一样！

PostgreSQL 平时没事，怎么就一个SQL下去就不行了？，原来是老毛病了！

在测试浪潮 KaiwuDB-lite 后，留下几个大字 "你别挨骂了"

云数据库备份恢复验证，云数据库高端客户的需求说明

国产数据库大舞台，搞不搞兼容性之互相揭老底！

PostgreSQL 现世报，客户吐槽不如SQL SERVER 与国产数据库搞兼容性

阿里云 PolarDB for MySQL IMCI 绞杀SQL优化专家，My GOD！！

阿里云DTS 我冤枉，其实我很委屈--客户大爷们，咱们换个位置也理解一下吧！

SQLite 开发中的数据库开发规范 --如何提升业务系统性能避免基础BUG

把我的第一次给 “金仓” 数据库社区活动之我要作妖