PolarDB:Implement CCI and TTL-based archiving for the same table

Supported versions

• Only databases in AUTO mode support the creation and use of a CCI.

• The instance version must be polardb-2.5.0_5.4.20-20250328 or later.

  Note

  • For information about the instance version naming rules, see Release notes.

  • For information about how to view the version of an instance, see View and update the version of an instance.

Prerequisites

An archive table is a special type of CCI used to store archived data. By default, only one CCI is allowed per table. Therefore, PolarDB-X provides the MAX_CCI_COUNT parameter to control the number of CCIs that can be added to a single table. For example:

SET GLOBAL MAX_CCI_COUNT = 2;

Example

1. Set MAX_CCI_COUNT to 2 to allow the creation of two CCIs for a single table.

   SET GLOBAL MAX_CCI_COUNT = 2;

2. Implement CCI and TTL-based archiving using one of the following methods.

   • Method 1: Create a regular CCI table first, then add a TTL archive table.

     -- Create a table
     CREATE TABLE ttl_tbl (
       `a` int(11) NOT NULL AUTO_INCREMENT,
       `c` int(11) NOT NULL,
       `b` timestamp(3),
       PRIMARY KEY(`a`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by KEY(`c`) partitions 4;
     -- Create a CCI
     CREATE CLUSTERED COLUMNAR INDEX `cci_c` ON ttl_tbl (`c`) partition by KEY(`c`) partitions 4;
     
     -- Change the table to a TTL table
     ALTER TABLE ttl_tbl
     MODIFY TTL
     SET
     TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00'
     TTL_CLEANUP='ON';
     -- Create an archive table for ttl_tbl
     CREATE TABLE ttl_tbl_arc LIKE ttl_tbl engine=Columnar archive_mode='ttl';

   • Method 2: Create a TTL archive table first, then add CCI.

     -- Create a TTL table
     CREATE TABLE `ttl_tbl` (
       `a` int(11) NOT NULL AUTO_INCREMENT,
       `c` int(11) NOT NULL,
       `b` timestamp(3),
       PRIMARY KEY(a)
     ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION( TTL_ENABLE = 'OFF', TTL_CLEANUP='ON', TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *' TIMEZONE '+08:00', ARCHIVE_TYPE = '', ARCHIVE_TABLE_NAME = '', ARCHIVE_TABLE_PRE_ALLOCATE = 2, ARCHIVE_TABLE_POST_ALLOCATE = 2 )
     PARTITION BY KEY(`a`)
     PARTITIONS 2;
     -- Create an archive table for ttl_tbl
     CREATE TABLE ttl_tbl_arc LIKE ttl_tbl engine=Columnar archive_mode='ttl';
     -- Create a standard CCI
     CREATE COLUMNAR CLUSTERED INDEX cci_c ON ttl_tbl(c) PARTITION BY KEY(c) PARTITIONS 4;

3. View the table schema.

   SHOW CREATE TABLE ttl_tbl;

   Sample result:

   CREATE TABLE `ttl_tbl` (
   	`a` int(11) NOT NULL AUTO_INCREMENT,
   	`c` int(11) NOT NULL,
   	`b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
   	PRIMARY KEY (`a`),
   	CLUSTERED COLUMNAR INDEX `arctmp_ttl_tbl_arc` (`b`)
   		PARTITION BY RANGE(UNIX_TIMESTAMP(`b`))
   		(PARTITION p20240625 VALUES LESS THAN (1719244800) ENGINE = Columnar,
   		 PARTITION p20240626 VALUES LESS THAN (1719331200) ENGINE = Columnar,
   		 PARTITION p20240627 VALUES LESS THAN (1719417600) ENGINE = Columnar,
   		 PARTITION p20240628 VALUES LESS THAN (1719504000) ENGINE = Columnar,
   		 PARTITION p20240629 VALUES LESS THAN (1719590400) ENGINE = Columnar,
   		 PARTITION p20240630 VALUES LESS THAN (1719676800) ENGINE = Columnar,
   		 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = Columnar),
   	CLUSTERED COLUMNAR INDEX `cci_c` (`c`)
   		PARTITION BY KEY(`c`)
   		PARTITIONS 4
   ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION ( TTL_ENABLE = 'OFF', TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *', TTL_CLEANUP = 'ON', TTL_PART_INTERVAL = INTERVAL(1, DAY), ARCHIVE_TYPE = 'ROW', ARCHIVE_TABLE_NAME = 'ttl_t1_with_cci_arc', ARCHIVE_TABLE_PRE_ALLOCATE = 2, ARCHIVE_TABLE_POST_ALLOCATE = 2 )
   PARTITION BY KEY(`a`)
   PARTITIONS 2

4. Insert test data.

   INSERT INTO ttl_tbl (c, b) VALUES
   (1, '2024-06-25 08:00:00'), -- expired 3 days ago
   (2, '2024-06-26 09:00:00'), -- expired 2 days ago (boundary case)
   (3, '2024-06-27 10:00:00'), -- not expired (1 day old)
   (4, '2024-06-28 09:00:00'); -- not expired (current time)

5. Manually clean up expired data.

   ALTER TABLE ttl_tbl CLEANUP EXPIRED DATA;

6. View online table data.

   SELECT * FROM ttl_tbl FORCE INDEX(PRIMARY);

   Sample result:

   +---+---+-------------------------+
   | a | c | b                       |
   +---+---+-------------------------+
   | 4 | 4 | 2024-06-28 09:00:00.000 |
   +---+---+-------------------------+

   Note

   Expired data has been cleaned up. By default, data older than three time units is deleted.

7. View standard CCI data.

   SELECT * FROM ttl_tbl FORCE INDEX(cci_c);

   Sample result:

   +---+---+-------------------------+
   | a | c | b                       |
   +---+---+---+---------------------+
   | 4 | 4 | 2024-06-28 09:00:00.000 |
   +---+---+-------------------------+

   Note

   Expired data in the columnstore index cci_c has been physically deleted.

8. View archived CCI data.

   SELECT * FROM ttl_tbl_arc;

   Sample result:

   +---+---+-------------------------+
   | a | c | b                       |
   +---+---+-------------------------+
   | 1 | 1 | 2024-06-25 08:00:00.000 |
   | 2 | 2 | 2024-06-26 09:00:00.000 |
   | 3 | 3 | 2024-06-27 10:00:00.000 |
   | 4 | 4 | 2024-06-28 09:00:00.000 |
   +---+---+-------------------------+

   Note

   Both archived data and pre-archived online data are included.