Release Notes
Product Announcements
Basic Settings
This document describes operations related to Column Store Index (CSI).
Prerequisites
The kernel version of TDSQL-C for MySQL 8.0 is 3.1.14 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.
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.
Creating Column Store Indexes
After the CSI feature is enabled, the following commands can be used to create column store indexes:
1. Create indexes when creating the table.
CREATE TABLE table_name (col1, col2,... COLUMNSTORE INDEX [index_name] [(col1, col2,...)]);
2. Create indexes after the table is created.
CREATE COLUMNSTORE INDEX [index_name] ON table_name[(col1, col2,...)];ALTER TABLE table_name ADD COLUMNSTORE INDEX [index_name] [(col1, col2,...)];
Deleting Column Store Indexes
After the CSI feature is enabled, the following command can be used to delete column store indexes:
ALTER TABLE table_name DROP INDEX index_name;
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: 0MySQL [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.
Bantuan dan Dukungan
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan