使用说明

行列自动分流

前往集群详情页面，在数据库连接区域，修改集群地址的行存/列存自动引流选项为开启。

行列手动分流

前往集群详情页面，在数据库连接区域，新建一个自定义地址，服务节点应仅包含列存索引只读节点。

手动添加

列存索引为您提供全面的DDL语句，以支持您为业务表添加或删除列存索引。您可以根据实际需求进行选择：

-- 为某一列添加列存索引
CREATE TABLE <table_name>(
  <column_name_1> INT COMMENT 'COLUMNAR=1',
  <column_name_2> VARCHAR(100)
) ENGINE InnoDB;

-- 为全表添加列存索引
CREATE TABLE <table_name>(
  <column_name_1> INT,
  <column_name_2> VARCHAR(100)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

-- 为指定列添加列存索引
ALTER TABLE <table_name> MODIFY COLUMN <column_name_1> INT COMMENT 'COLUMNAR=1';
               
-- 为整个表添加列存索引
ALTER TABLE <table_name> COMMENT 'COLUMNAR=1';

-- 为整个表添加列存索引
CREATE COLUMNAR INDEX ON <db_name>.<table_name>;

-- 为整个库添加列存索引
CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;

自动添加

列存索引提供的自动无感提速（AutoIndex）功能，能够根据您的慢SQL自动创建列存索引，从而显著提升慢SQL的执行速度，无需您深入理解每一条慢SQL进行调优。随着应用负载的变化，自动列存索引提速功能会持续监控并调整列存索引策略，以确保PolarDB集群维持在最佳性能状态。

您只需在PolarDB集群详情页面，开启自动列存索引提速功能即可体验该功能带来的效果：

列存执行计划（横向树形式）

+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
| ID | Operator                   | Name     | E-Rows | E-Cost | Extra Info                                                                  |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
|  1 | Select Statement           |          |        |        | IMCI Execution Plan (max_dop = 4, max_query_mem = 858993459)                |
|  2 | └─Sort                     |          |        |        | Sort Key: revenue DESC,o_orderdate ASC                                      |
|  3 |   └─Hash Groupby           |          |        |        | Group Key: (lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY) |
|  4 |     └─Hash Join            |          |        |        | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY                          |
|  5 |       ├─Hash Join          |          |        |        | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY                            |
|  6 |       │ ├─Table Scan       | customer |        |        | Cond: (C_MKTSEGMENT = "BUILDING")                                           |
|  7 |       │ └─Table Scan       | orders   |        |        | Cond: (O_ORDERDATE < 03/24/1995)                                            |
|  8 |       └─Table Scan         | lineitem |        |        | Cond: (L_SHIPDATE > 03/24/1995)                                             |
+----+----------------------------+----------+--------+--------+-----------------------------------------------------------------------------+
8 rows in set (0.01 sec)

行存执行计划

+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys      | key        | key_len | ref                         | rows   | filtered | Extra                                        |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | PRIMARY            | NULL       | NULL    | NULL                        | 147630 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | orders   | NULL       | ref  | PRIMARY,ORDERS_FK1 | ORDERS_FK1 | 4       | tpch100g.customer.C_CUSTKEY |     14 |    33.33 | Using where                                  |
|  1 | SIMPLE      | lineitem | NULL       | ref  | PRIMARY            | PRIMARY    | 4       | tpch100g.orders.O_ORDERKEY  |      4 |    33.33 | Using where                                  |
+----+-------------+----------+------------+------+--------------------+------------+---------+-----------------------------+--------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)