This topic describes how to execute DDL statements to dynamically create and delete an IMCI after a table is created.

Prerequisites

After you add a read-only column store node and configure the cluster endpoint, you can connect to the cluster by using the cluster endpoint and execute the statement to create and manage an IMCI.

  • Read-only column store nodes are added. For more information, see Add a read-only column store node.
  • The cluster endpoint is configured. Manual and automatic request distribution solutions can be used to distribute requests to row store and column store nodes. You can select a Request distribution method based on your business and configure the cluster endpoint.
  • The database cluster is connected by using the cluster endpoint. For more information, see Connect to a cluster.

Create an IMCI

  • Syntax:
    • Add the COMMENT 'COLUMNAR=1' field to the ALTER TABLE statement to create an IMCI that is valid for the entire table.
    • Add the COMMENT 'COLUMNAR=1' field to the ALTER TABLE ... MODIFY COLUMN ... statement to create an IMCI valid for a specified column.
    Note
    • For PolarDB for MySQL 8.0.1.1.25 and later, IMCIs support the BLOB and TEXT data types.
    • IMCIs do not support data types such as ENUM, SET, BIT, or Geo.
  • Examples:
    CREATE TABLE t5(
      col1 INT,
      col2 DATETIME,
      col3 VARCHAR(200)
    ) ENGINE InnoDB;
    
    -- Create an IMCI valid for a table
    ALTER TABLE t5 COMMENT 'COLUMNAR=1';
    
    -- Create an IMCI valid for specified columns
    ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1',
                   MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1';

Delete an IMCI

  • Syntax:
    • Add the COMMENT 'COLUMNAR=0' field to the ALTER TABLE statement to delete an IMCI that is valid for the entire table.

    • Add the COMMENT 'COLUMNAR=0' field to the ALTER TABLE ... MODIFY COLUMN ... statement to delete an IMCI that is valid for a specified column.
  • Examples:
    -- Create an IMCI valid for specified columns
    CREATE TABLE t6(
      col1 INT COMMENT 'COLUMNAR=1',
      col2 DATETIME COMMENT 'COLUMNAR=1',
      col3 VARCHAR(200)
    ) ENGINE InnoDB;
    
    -- Delete an IMCI valid for specified columns
    ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0',
                   MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';
    
    
    -- Create an IMCI valid for a table
    CREATE TABLE t7(
      col1 INT,
      col2 DATETIME,
      col3 VARCHAR(200)
    ) ENGINE InnoDB COMMENT 'COLUMNAR=1';
    
    -- Delete an IMCI valid for a table
    ALTER TABLE t7 COMMENT 'COLUMNAR=0';

Modify IMCI definition

  • Syntax:
    • Add the COMMENT 'COLUMNAR=1' field to the ALTER TABLE ... MODIFY COLUMN ... statement to add a column to the IMCI.
    • Add the COMMENT 'COLUMNAR=0' field to the ALTER TABLE ... MODIFY COLUMN ... statement to delete a column for which the IMCI is valid.
    Note
    • For PolarDB for MySQL 8.0.1.1.25 and later, IMCIs support the BLOB and TEXT data types.
    • IMCIs do not support data types such as ENUM, SET, BIT, or Geo.
  • Examples:
    CREATE TABLE t8(
      col1 INT COMMENT 'COLUMNAR=1',
      col2 DATETIME COMMENT 'COLUMNAR=1',
      col3 VARCHAR(200)
    ) ENGINE InnoDB;
    
    -- Add a column for which the IMCI is valid.
    ALTER TABLE t8 MODIFY COLUMN col3 VARCHAR(200) COMMENT 'COLUMNAR=1';
    
    -- Delete a column for which the IMCI is valid.
    ALTER TABLE t8 MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Create an IMCI valid for multiple columns

Tables that contain multiple columns are often involved in the OLAP service. You can use the COMMENT field to simplify the process to create an IMCI valid for a table that contains multiple columns. By default, the IMCI is valid for all columns of supported data types in the table. You can also specify only a few columns for which the IMCI is not valid.
Note
  • For PolarDB for MySQL 8.0.1.1.25 and later, IMCIs support the BLOB and TEXT data types.
  • IMCIs do not support data types such as ENUM, SET, BIT, or Geo.

For example, if you execute the following statement to create a table:

CREATE TABLE t9(
  col1 INT, col2 INT, col3 INT,
  col4 DATETIME, col5 TIMESTAMP,
  col6 CHAR(100), col7 VARCHAR(200),
  col8 TEXT, col9 BLOB
) ENGINE InnoDB;

You can execute the following statement to create an IMCI valid for the table:

ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

A similar result is returned:

SHOW CREATE TABLE t9 FULL\G
*************************** 1. row ***************************
      Table: t9
Create Table: CREATE TABLE `t9` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col6` char(100) DEFAULT NULL,
  `col7` varchar(200) DEFAULT NULL COMMENT 'COLUMNAR=0',
  `col8` text,
  `col9` blob,
  COLUMNAR INDEX  (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col8`,`col9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'COLUMNAR=1'

In the preceding example, the IMCI is not valid for the col7 column.

However, due to the InnoDB Online DDL implementation, the ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0'; statement in the preceding example is implemented in online rebuild mode, resulting in poor performance. You can try the following method:

-- Modify the COMMENT field for the column for which the IMCI is not valid.
ALTER TABLE t9 MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

-- Modify the COMMENT field for the table to create the IMCI which is valid for the table.
ALTER TABLE t9 COMMENT 'COLUMNAR=1';

Create an IMCI when adding columns

When you execute the ALTER TABLE ADD COLUMN statement to add columns, you can add the COMMENT 'COLUMNAR=1' field to create an IMCI valid for the columns.
Note
  • For PolarDB for MySQL 8.0.1.1.25 and later, IMCIs support the BLOB and TEXT data types.
  • IMCIs do not support data types such as ENUM, SET, BIT, or Geo.

For example, you can execute the following statement to create a table, and create an IMCI which is valid for the col1 and col2 columns:

CREATE TABLE t10(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

You can execute the following statement to add the col4 column for which the IMCI is also valid to the t10 table:

ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

It is no longer an INSTANT DDL statement because it involves changes to the IMCI. This DDL statement deletes the old IMCI when adding the column, and creates a new IMCI that is valid for the col1, col2, and col4 columns.

View the statuses of indexes

After the IMCI feature is enabled, OLAP query requests are distributed to the read-only column store node instead of the primary node. This isolates OLAP and OLTP computing resources. Due to this isolation, the online DDL statements for creating or modifying an IMCI are optimized as asynchronous DDL statements. The following logic is used: After the metadata of tables and IMCIs is modified on the primary node, the modifications are synchronized to the read-only column store node by using Redo logs. The read-only column store node starts background threads to concurrently build IMCIs after the data dictionary modifications take effect.

The asynchronous DDL logic means that IMCIs can be queried only after they are built, although DDL statements are submitted and data dictionary modifications take effect. If you perform an OLAP query immediately after the DDL statement is executed, the read-only row store node is still used. If you perform an OLAP query after the IMCI is built, the read-only column store node is used.

You can first execute the INFORMATION_SCHEMA.IMCI_INDEXES statement on a read-only column store node to check whether the IMCI is created.

For example, if you execute the following statement to create a table:

CREATE TABLE t11(
  col1 INT, col2 DATETIME, col3 VARCHAR(200)
) ENGINE InnoDB;

You can execute the following DDL statement to create an IMCI:

ALTER TABLE t11 COMMENT 'COLUMNAR=1';

This DDL statement is similar to an INSTANT DDL in effect and executed quickly on the primary node. However, if you immediately execute the following statement to perform a query:

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME = 't11';

If the STATE field in the result is RECOVERING instead of COMMITTED, the IMCI is still being created.

+--------+-----------+----------+--------+---------+------+----------+--------+
|TABLE_ID|SCHEMA_NAME|TABLE_NAME|NUM_COLS|PACK_SIZE|ROW_ID|STATE     |MEM_SIZE|
+--------+-----------+----------+--------+---------+------+----------+--------+
|    xxxx| test      | t11      |       3|    65536|     0|RECOVERING|    0   |
+--------+-----------+----------+--------+---------+------+----------+--------+

If the STATE field is COMMITTED, the IMCI has been created. If you perform an OLAP query now, the read-only column store node is used.