< 返回新闻公共列表
Polar for Mysql 列存索引常用方法
发布时间：2023-06-26 12:00:26
添加列存索引创建表：
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;
●并行DDLhttps://help.leiyu.cn/document_detail/193259.html?spm=a2c4g.172533.0.i1●列存索引排序https://help.leiyu.cn/document_detail/602366.html?spm=a2c4g.607775.0.0.7d9418a8RmlFbt
