Polar for Mysql 列存索引常用方法

添加列存索引
创建表：

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

修改表：

查看最后执行SQL

-- 查看最后执行SQL 阈值
SHOW STATUS LIKE 'Last_query_cost';
-- 查询cost阈值
SHOW VARIABLES LIKE 'imci_ap_threshold';

查看表是否列存索引

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%by_wechat_message%';

检查执行SQL 字段缺少列存索引

CALL dbms_imci.check_columnar_index("
    SELECT 
            domain, subject_id AS subjectId, 
            SUM(question_num) AS questionNum , 
            SUM(tk_question_num) AS tkQuestionNum 
    FROM `ask_question_statistic` 
    WHERE dt >= 20230501 AND dt <= 20230531 
    GROUP BY domain, subject_id
");

强制执行列存查询

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */
        domain, subject_id AS subjectId, 
        SUM(question_num) AS questionNum , 
        SUM(tk_question_num) AS tkQuestionNum 
FROM `ask_question_statistic` 
WHERE dt >= 20230501 AND dt <= 20230531

设置并列索引 =16
-- 后台控制

innodb_polar_parallel_ddl_threads = 16;

-- 查看是否开启并列查询

SHOW VARIABLES LIKE "innodb_polar_parallel_ddl_threads"

查看列存索引创建状态

-- 表中查看索引的状态信息；
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES;
-- 表中查看索引的写入速度；
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEX_STATS;
-- 参见查看DDL执行速度和进度
SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

●并行DDL
https://help.aliyun.com/document_detail/193259.html?spm=a2c4g.172533.0.i1
●列存索引排序
https://help.aliyun.com/document_detail/602366.html?spm=a2c4g.607775.0.0.7d9418a8RmlFbt