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

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

最近开始在大量的应用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 .;

CREATE COLUMNAR INDEX ON ;

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

DROP COLUMNAR INDEX ON .;

DROP COLUMNAR INDEX ON ;

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

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