PolarDB：使用說明

行列自動分流

前往叢集詳情頁面，在資料庫連接地區，修改叢集地址的行存/列存自動引流選項為開啟。

行列手動分流

前往叢集詳情頁面，在資料庫連接地區，建立一個自訂地址，服務節點應僅包含列存索引唯讀節點。

手動添加

列存索引為您提供全面的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)