tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Basic Settings

    Last updated: 2023-12-12 14:37:21
    Download PDF
      This document describes operations related to Column Store Index (CSI).

      Prerequisites

      You have submitted a ticket to upgrade the kernel minor version to v3.2.1 of TDSQL-C for MySQL 8.0.
      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 Type field.
      3. In the pop-up window, change the instance type and then click Check.
      If the instance type changes from Line Store to Mix Store, the CSI feature is enabled.
      If the instance type changes from Mix Store to Line Store, the CSI feature 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] [(col1, col2,...)] ON table_name;
      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: 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.
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy