tencent cloud

TDSQL-C for MySQL

DocumentaçãoTDSQL-C for MySQL

Basic Settings

Baixar
Modo Foco
Tamanho da Fonte
Última atualização: 2026-09-01 14:34:02
Traduzido por IA
This document describes operations related to Column Store Index (CSI).
Note:
For production systems, enabling and creating column-format storage is a production change and must follow the production change process. To reduce change risks, TDSQL-C for MySQL provides verification capabilities such as traffic replay, which you can use to evaluate the effectiveness of column-format storage before deciding whether to enable and create columnar storage indexes in production systems.

Prerequisites

The kernel version of TDSQL-C for MySQL 8.0 is 3.1.20.001 or later.
Note:
For read-only instances that meet the version requirements, the CSI feature can be enabled only on those with four or more CPU cores.

Enabling or Disabling CSI

1. On the cluster list page, go to the instance details page according to the view mode.
Tab View
List View
1. Log in to the TDSQL-C for MySQL console, and click the target cluster in the cluster list on the left to go to the cluster management page.
2. On the Cluster Details tab, click Details of the target instance next to the instance ID to go the instance details page.
1. Log in to the TDSQL-C for MySQL console, find the cluster whose character set needs modification in the cluster list, and click the Cluster ID to go to the cluster management page.
2. On the cluster management page, select the Instance List tab. Locate the read-write instance or read-only instance for which you intend to enable or disable CSI, and click the instance ID to access the instance details page.
2. Click the icon for edition next to the Instance Mode field.

3. In the pop-up window, select the operation time, check During the operation, there will be a second-level interruption. Please ensure your business has a reconnection mechanism, and click OK.
Operation Time
Execute immediately: Immediately execute the switch of the instance mode.
During maintenance time: Execute within the instance maintenance time you have set. To modify the maintenance time, see Modifying Instance Maintenance Window.
Note:
The instance mode is changed from Line Store to Mix Store, indicating that Column Storage Index (CSI) is enabled.
The instance mode is changed from Mix Store to Line Store, indicating that CSI is disabled.

Column-format Storage Configuration

To make better use of columnar storage indexes for queries, you can refer to Parameter Configuration and Monitoring Items for column-format storage configuration.

Creating and Deleting CSI

CREATE TABLE t (c1 INT, c2 INT, c3 INT);
INSERT INTO t VALUES (1, 2, 3);
Note:
DDL statements need to be executed on the RW instance.
Column-format storage indexes can be created in two ways:
Use the column-format storage comment syntax.
Use the COLUMNSTORE keyword.
We recommend using the column-format storage comment syntax for the following reasons:
COLUMNSTORE is a newly added keyword, so existing toolchains and ecosystems may have compatibility issues.
By default, column-format storage comments create columnar storage indexes on all fields. When fields change, the indexes are automatically synchronized, reducing maintenance costs.
If you use the COLUMNSTORE keyword to create columnar storage indexes, you must manually maintain the field list and synchronize the index definition when fields change.

Column-format Storage Comment Syntax (Recommended)

Include columnar storage indexes when creating a table:
-- Column-format storage comment (recommended, full index)
CREATE TABLE t (c1 INT, c2 INT, c3 INT) COMMENT='COLUMNAR=1';
Create columnar storage indexes using a separate statement:
-- Column-format storage comment (recommended, full index)
ALTER TABLE t COMMENT 'COLUMNAR=1';
You can run SHOW CREATE TABLE table_name FULL to check whether a table has column-format storage. Note that columnar storage indexes use the new COLUMNSTORE keyword, which is not printed by default to avoid affecting tool compatibility. You can use FULL to control the printing.
Columnar storage indexes created using column-format storage comments:
-- SHOW CREATE TABLE t FULL
CREATE TABLE `t` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  COLUMNSTORE KEY `_default_csi_` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='COLUMNAR=1'
Delete indexs:
-- Column-format storage comment (recommended)
ALTER TABLE t COMMENT 'COLUMNAR=0';

Column-format Storage Keyword Syntax

Note:
The tool ecosystem may not be compatible with the column-format storage keyword COLUMNSTORE. Additionally, columnar storage index fields are fixed and are not automatically synchronized when new fields are added. Therefore, using it is not recommended.
Include columnar storage indexes when creating a table:
-- Column-format storage keyword (fixed-field index csi (c1,c2,c3))
CREATE TABLE t (c1 INT, c2 INT, c3 INT, COLUMNSTORE INDEX csi);
Create columnar storage indexes using a separate statement:
-- Column-format storage keyword (fixed-field index csi (c1,c2,c3))
ALTER TABLE t ADD COLUMNSTORE INDEX csi;
Delete indexs:
-- Index name created by the column-format storage keyword
ALTER TABLE t DROP KEY `csi`;

Execute SQL

EXPLAIN prints Csi Scan for table access. You can connect through the database proxy, and SQL that meets the threshold and compatibility checks is automatically executed on column-format storage nodes. You can also directly connect to a column-format storage read-only (RO) node to execute SQL.
-- Test setting for small data volumes. For production systems, be sure to use meaningful values.
-- SET columnstore_cost_threshold = 0;
-- explain analyze ...
-- explain format=tree ...
select max(col1) from t1 where col2 < col3;
The following is the printed execution plan:
| physical_plan -> Ungrouped Aggregate: max(#0)
   -> Projection: col1 (rows=1)
     -> Projection: #11 (rows=1)
       -> Projection: NULL #6 NULL #5 NULL #4 NULL #3 NULL #2 NULL #1 NULL #0 NULL (rows=1)
         -> Projection: NULL #2 NULL #1 NULL #0 NULL (rows=1)
           -> Csi Scan Table: test.t1 Projections: col2 col3 col1 (rows=1)

Renaming Column Store Indexes

After the CSI feature is enabled, the following command can be used to rename column store indexes:
ALTER TABLE table_name RENAME index old_index_name to new_index_name;

Using HINT Statements for Column Store Indexes

1. Enforce statements to use row store indexes or column store indexes.
Enforce statements to use row store indexes.
SELECT a FROM t IGNORE INDEX (csi);
Enforce statements to use column store indexes.
SELECT a FROM t FORCE INDEX (csi);
2. Use HINT statements for parallel CSI-based queries.
SELECT /*+PARALLEL(2)*/ a FROM t FORCE INDEX (csi);

Example of Creating a Table and Column Store Index 

CREATE TABLE t (a int, columnstore index csi (a));
INSERT INTO t VALUES (0), (1), (2);
SHOW CREATE TABLE t; 
SHOW INDEX FROM t;
Execution result:
MySQL [test]> CREATE TABLE t (a int, columnstore index csi (a));
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> INSERT INTO t VALUES (0), (1), (2);
Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0 
MySQL [test]> SHOW CREATE TABLE t; 
+-------+---------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (   `a` int DEFAULT NULL,   COLUMNSTORE KEY `csi` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------+
MySQL [test]> SHOW INDEX FROM t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type  | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+---------------+---------+------------+
| t     |          1 | csi      |            1 | a           | NULL      |           1 |     NULL |   NULL | YES  | COLUMNSTORE |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

Usage of HINT

1. Enforce statements to use column store indexes.
SELECT a FROM t FORCE INDEX (csi);
EXPLAIN FORMAT=TREE SELECT a FROM t FORCE INDEX (csi);
Execution result:
MySQL [test]> SELECT a FROM t FORCE INDEX (csi);
+------+
| a    |
+------+
|    0 |
|    1 | 
|    2 |
+------+
3 rows in set (0.00 sec)
MySQL [test]> EXPLAIN FORMAT=TREE SELECT a FROM t FORCE INDEX (csi); 
+---------------------------------------------------------------+
| EXPLAIN                                                       |  
+---------------------------------------------------------------+
| -> COLUMNSTORE Index scan on t using csi  (cost=1.30 rows=3)  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
2. Enforce statements to avoid using column store indexes (using row store indexes).
SELECT a FROM t IGNORE INDEX (csi);
EXPLAIN FORMAT=TREE SELECT a FROM t IGNORE INDEX (csi);
Execution result:
MySQL [test]> SELECT a FROM t IGNORE INDEX (csi);
+------+
| a    |
+------+
|    0 |
|    1 | 
|    2 |
+------+
3 rows in set (0.00 sec)
MySQL [test]> EXPLAIN FORMAT=TREE SELECT a FROM t IGNORE INDEX (csi);
+-----------------------------------------+
| EXPLAIN                                 |
+-----------------------------------------+
| -> Table scan on t  (cost=0.55 rows=3)  |
+-----------------------------------------+
1 row in set (0.00 sec)

Viewing Creation Status of Column Store Indexes

show create table TABLE
Note:
By default, the COLUMNSTORE prefix is not displayed. It will be displayed only when columnstore_display_in_show_create is set to 1.
show index from TABLE
explain format=tree
Note:
Once CSI is enabled, explain format=tree can be used to check the status of column store index creation. The statement checks if the execution plan operator has the COLUMNSTORE prefix to determine whether the operator uses column store indexes for query execution. The COLUMNSTORE prefix is not displayed by default. It will be displayed only when format is set to tree.
Próximo: Service Level Agreement

Ajuda e Suporte

Esta página foi útil?

comentários

Search
    Enter keywords to search documentation