columnar table design, ORDER KEY, storage policy, PolarDB MySQL-PolarDB(PolarDB)-阿里云帮助中心

This topic describes how to create and manage columnar tables, including core operations such as setting order keys and converting table formats, as well as methods for analyzing execution plans to optimize query performance.

Create columnar tables

This section describes how to create a new columnar table, including basic creation, setting order keys to optimize performance, and understanding storage policies.

Create a basic table

Create a basic columnar table. You only need to specify ENGINE=XEngine and TABLE_FORMAT=COLUMN in the CREATE TABLE statement.

-- Create a columnar table to store sales records
CREATE TABLE sales_records (
  sale_id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  region VARCHAR(50),
  sale_date DATE,
  amount DECIMAL(10, 2)
) ENGINE=XEngine, TABLE_FORMAT=COLUMN;

Use order keys to optimize query performance

Pre-sorting data significantly improves the performance of specific queries. The order key (order key) is a core optimization method for columnar tables. It determines the physical storage order of data. A well-designed order key can improve the performance of filtering, aggregation, and sorting operations several times over.

Design recommendations

High-frequency filter columns: Use the columns most frequently used in WHERE conditions for range or equality queries as order keys, especially time columns (such as sale_date).
High-frequency grouping columns: Include dimension columns frequently used in GROUP BY in the order key.
High-cardinality dimension columns: Select columns with higher cardinality (number of unique values) as the prefix of the order key to improve data filtering efficiency.

Procedure: Specify the order key by using the ORDER KEY clause in the CREATE TABLE statement.

Important

The total length of all columns in the order_key must not exceed 3,072 bytes.

-- Create a columnar table with an order key
-- Queries frequently filter by date range and region, so use them as the order key
CREATE TABLE sales_records_sorted (
  sale_id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  region VARCHAR(50),
  sale_date DATE,
  amount DECIMAL(10, 2),
  -- Define an order key named idx_date_region
  ORDER KEY idx_date_region(sale_date, region)
) ENGINE=XEngine, TABLE_FORMAT=COLUMN;

Specify a storage policy (storage_policy) for hot-cold data tiering

storage_policy specifies the storage medium for data. It is designed to separate hot and cold data by storing infrequently accessed data on low-cost Object Storage Service (OSS).

HOT: Data is stored on high-performance PolarStore. This is the default setting.
COLD: Data is stored on low-cost OSS.

Note

The storage_policy=cold syntax is supported. However, in the current version, all data is temporarily stored on PolarStore regardless of whether you specify HOT or COLD.

-- Syntax example: Create a columnar table with COLD storage policy
CREATE TABLE archive_logs (
  log_id BIGINT PRIMARY KEY,
  log_time TIMESTAMP,
  message TEXT
) ENGINE=XEngine, TABLE_FORMAT=COLUMN, STORAGE_POLICY=COLD;

Create partitioned tables

The syntax for creating a columnar partitioned table is the same as that for a non-partitioned table. Specify ENGINE=XEngine and TABLE_FORMAT=COLUMN in the CREATE TABLE statement.

Note

Creating an XEngine partitioned table (archiving the partitioned table as a hybrid partitioned table) requires the following conditions:

MySQL 8.0.2 and the minor version is 8.0.2.2.34.1 or later. You must set the loose_polar_allow_create_hybrid_partition parameter to ON.
MySQL 8.0.2 and the minor version is earlier than 8.0.2.2.34.1. Upgrade to a later minor version.

-- Create a columnar partitioned table
CREATE TABLE t1(a1 INT, a2 INT, a3 INT, a4 INT) ENGINE=XEngine TABLE_FORMAT=COLUMN
PARTITION BY RANGE(a1)
(
  PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (40),
  PARTITION p3 VALUES LESS THAN (60),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- Create a columnar partitioned table with data stored on OSS
CREATE TABLE t2(a1 INT, a2 INT, a3 INT, a4 INT) ENGINE=XEngine TABLE_FORMAT=COLUMN STORAGE_POLICY=COLD
PARTITION BY RANGE(a1)
(
  PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (40),
  PARTITION p3 VALUES LESS THAN (60),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

Manage columnar tables

This section describes how to manage existing tables, including converting between row-based and columnar formats, modifying table structures, and changing partition formats.

Convert table storage formats (row-based and columnar)

Convert an existing row-based table to a columnar table for analytics acceleration, or convert a columnar table back to a row-based table.

// Convert a row-based table to an XEngine columnar table
ALTER TABLE sales_records ENGINE=XEngine, TABLE_FORMAT=COLUMN;

// Convert a columnar table to an XEngine row-based table
ALTER TABLE sales_records ENGINE=XEngine TABLE_FORMAT=ROW;

// Convert a columnar table to an InnoDB row-based table
ALTER TABLE sales_records ENGINE=innodb;

Modify table schema (columns and indexes)

Columnar tables support Online DDL. You can add or drop columns or indexes without blocking DML operations. The syntax is the same as for row-based tables.

-- Add a column online
ALTER TABLE sales_records ADD COLUMN salesperson_id INT;

-- Drop a column online
ALTER TABLE sales_records DROP COLUMN salesperson_id;

-- Add an index online
ALTER TABLE sales_records ADD INDEX idx_product(product_id);

-- Drop an index online
ALTER TABLE sales_records DROP INDEX idx_product;

Note

In the current version, the ADD COLUMN operation does not support Instant DDL, but supports Online DDL. This means adding a column requires a full table data copy, but DML operations are not blocked during DDL execution.

Modify order keys

Adjust or drop the order key of a columnar table based on changes in query patterns.

-- Add an order key to an existing columnar table
ALTER TABLE sales_records ADD ORDER KEY idx_region(region), ALGORITHM=INPLACE;

-- Modify the order key definition (drop and add)
ALTER TABLE sales_records_sorted DROP ORDER KEY idx_date_region, ADD ORDER KEY idx_product(product_id), ALGORITHM=INPLACE;

-- Drop an order key
ALTER TABLE sales_records_sorted DROP ORDER KEY idx_date_region, ALGORITHM=INPLACE;

Change partition storage formats

In a partitioned table, convert specific historical partitions to the columnar format for hybrid row-column storage. Keep recent active data partitions in the row-based format to ensure OLTP performance, and convert historical archive data partitions to the columnar format for analytics acceleration.

-- Step 1: Create an InnoDB partitioned table
CREATE TABLE historical_orders (
  order_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10, 2)
) ENGINE=InnoDB
PARTITION BY RANGE(TO_DAYS(order_date)) (
  PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
  PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01'))
);

-- Step 2: Convert the 2023 historical data partition to columnar format
ALTER TABLE historical_orders CHANGE PARTITION p2023 ENGINE=XEngine TABLE_FORMAT=COLUMN;

Analyze and optimize query performance

Use the EXPLAIN command to determine whether a query successfully uses the columnar engine and to understand its execution process. Columnar execution plans are displayed in a horizontal tree structure, which differs significantly from traditional row-based plan formats.

Example

The following example uses the TPC-H Q5 benchmark query to compare columnar and row-based execution plans.

Columnar execution plan example

+----+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------+
| ID | Operator                                  | Name     | Extra Info                                                                                         |
+----+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------+
|  1 | Select Statement                          |          | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited)                                      |
|  2 | └─Sort                                    |          | Sort Key: revenue DESC                                                                             |
|  3 |   └─Hash Groupby                          |          | Group Key: nation.n_name                                                                           |
|  4 |     └─Hash Join                           |          | Join Cond: (lineitem.l_suppkey, customer.c_nationkey) = (supplier.s_suppkey, supplier.s_nationkey) |
|  5 |       ├─Hash Join                         |          | Join Cond: orders.o_orderkey = lineitem.l_orderkey                                                 |
|  6 |       │ ├─Hash Join                       |          | Join Cond: (customer.c_nationkey, region.r_regionkey) = (nation.n_nationkey, nation.n_regionkey)   |
|  7 |       │ │ ├─Hash Join                     |          | Join Cond: orders.o_custkey = customer.c_custkey                                                   |
|  8 |       │ │ │ ├─Cartesian Product           |          |                                                                                                    |
|  9 |       │ │ │ │ ├─Table Scan                | region   | Cond: (r_name = "ASIA")                                                                            |
| 10 |       │ │ │ │ └─Table Scan                | orders   | Cond: ((o_orderdate >= 01/01/1994) AND (o_orderdate < 01/01/1995))                                 |
| 11 |       │ │ │ └─Table Scan                  | customer |                                                                                                    |
| 12 |       │ │ └─Table Scan                    | nation   |                                                                                                    |
| 13 |       │ └─Table Scan                      | lineitem |                                                                                                    |
| 14 |       └─Table Scan                        | supplier |                                                                                                    |
+----+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------+

How to read the plan:

Confirm columnar execution: The Extra Info column displays IMCI Execution Plan, which indicates that the query is processed by the columnar engine.
Check parallelism: max_dop = 32 indicates that the maximum number of parallel threads for this query within the columnar engine is 32.
Understand core operators:
- Table Scan: Represents a scan of a columnar table. The Cond section shows that WHERE conditions have been pushed down to the storage layer for execution, which is key to performance optimization.
- Hash Join/Hash Groupby: Efficient join and aggregation algorithms used in the columnar engine, suitable for processing large-scale data.
- Sort: Sort operation. Performance is significantly improved when the order key matches the ORDER BY columns.

Row-based execution plan comparison

+----+-------------+----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type   | possible_keys | key     | key_len | ref                       | rows    | filtered | Extra                                        |
+----+-------------+----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | region   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                      |       5 |    20.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | orders   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                      | 1500000 |    11.11 | Using where; Using join buffer (hash join)   |
|  1 | SIMPLE      | customer | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | tpch.orders.o_custkey     |       1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | nation   | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | tpch.customer.c_nationkey |       1 |    10.00 | Using where                                  |
|  1 | SIMPLE      | lineitem | NULL       | ref    | PRIMARY       | PRIMARY | 8       | tpch.orders.o_orderkey    |       2 |   100.00 | NULL                                         |
|  1 | SIMPLE      | supplier | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | tpch.lineitem.l_suppkey   |       1 |    10.00 | Using where                                  |
+----+-------------+----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+----------------------------------------------+

Row-based plans typically contain markers such as Using temporary; Using filesort, which indicate that temporary tables need to be created and sorting is performed on disk. These are the main performance bottlenecks in analytical queries. Columnar plans avoid these inefficient operations through in-memory parallel computation.