## 模块 2: 使用 Time to Live (TTL)

在本模块中，参与者将创建 3 张表，其中前 2 张表配置了 TTL 属性，第 3 张表没有配置 TTL 属性。之后，使用 tiup bench 工具每隔 1 秒钟向 3 个表中各插入 1 行随机数据。关于 TiUP bench 组件的使用请参考：<https://docs.pingcap.com/zh/tidb/stable/tiup-bench/>，参与者会观察 3 张表中的数据是否会根据配置的生命周期自动删除，以及如何在线开启或者关闭 TTL 功能，最后，将测试 FLASHBACK TABLE 功能对于表的 TTL 属性的影响。

### 步骤 1. 用 2 个终端连接到 TiUP 控制机器

+ 通过点击以下链接打开 2 个 SSH 连接到你的实验虚拟机：
  + [终端 1](ssh://monitor1-ssh#1)
  + [终端 2](ssh://monitor1-ssh#2)

+ 现在你将有 2 个活动的终端会话。

### 步骤 2. 创建表

+ 在[终端 1](ssh://monitor1-ssh#1) 上，连接到 TiDB 服务器实例。

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上，创建 `test` 下的表 `tbl_ttl_1`，`tbl_ttl_2` 和 `tbl_ttl_3`，并了解创建表所用的 DDL 语句。

  ```sql
  DROP TABLE IF EXISTS test.tbl_ttl_1;
  CREATE TABLE test.tbl_ttl_1 (
  `ID` bigint PRIMARY KEY AUTO_RANDOM,
  `pState` varchar (100) NOT NULL,
  `cState` tinyint (4) DEFAULT NULL,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  `txt` varchar(64) DEFAULT 'TTLTEST',
  KEY `pcStateIndex` (`pState`, `cState`)
  ) PRE_SPLIT_REGIONS=4, TTL = `created_at` + INTERVAL 1 MINUTE;

  DROP TABLE IF EXISTS test.tbl_ttl_2;
  CREATE TABLE test.tbl_ttl_2 (
  `ID` bigint PRIMARY KEY AUTO_RANDOM,
  `pState` varchar (100) NOT NULL,
  `cState` tinyint (4) DEFAULT NULL,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  `expire_at` DATETIME AS( IF (cState=1, created_at + INTERVAL 1 MINUTE, created_at + INTERVAL 2 DAY) ),
  `txt` varchar(64) DEFAULT 'TTLTEST',
  KEY `pcStateIndex` (`pState`, `cState`)
  ) PRE_SPLIT_REGIONS=4, TTL = `expire_at` + INTERVAL 0 MINUTE;

  DROP TABLE IF EXISTS test.tbl_ttl_3;
  CREATE TABLE test.tbl_ttl_3 (
  `ID` bigint PRIMARY KEY AUTO_RANDOM,
  `pState` varchar (100) NOT NULL,
  `cState` tinyint (4) DEFAULT NULL,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  `txt` varchar(64) DEFAULT 'TTLTEST',
  KEY `pcStateIndex` (`pState`, `cState`)
  ) PRE_SPLIT_REGIONS=4;
  ```

  ```output
  mysql> CREATE TABLE test.tbl_ttl_1 (
      -> `ID` bigint PRIMARY KEY AUTO_RANDOM,
      -> `pState` varchar (100) NOT NULL,
      -> `cState` tinyint (4) DEFAULT NULL,
      -> `created_at` DATETIME,
      -> `updated_at` DATETIME,
      -> `txt` varchar(64) DEFAULT 'TTLTEST',
      -> KEY `pcStateIndex` (`pState`, `cState`)
      -> ) PRE_SPLIT_REGIONS=4, TTL = `created_at` + INTERVAL 1 MINUTE;
  Query OK, 0 rows affected, 2 warnings (0.07 sec)

  mysql> 
  mysql> DROP TABLE IF EXISTS test.tbl_ttl_2;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> CREATE TABLE test.tbl_ttl_2 (
      -> `ID` bigint PRIMARY KEY AUTO_RANDOM,
      -> `pState` varchar (100) NOT NULL,
      -> `cState` tinyint (4) DEFAULT NULL,
      -> `created_at` DATETIME,
      -> `updated_at` DATETIME,
      -> `expire_at` DATETIME AS( IF (cState=1, created_at + INTERVAL 1 MINUTE, created_at + INTERVAL 2 DAY) ),
      -> `txt` varchar(64) DEFAULT 'TTLTEST',
      -> KEY `pcStateIndex` (`pState`, `cState`)
      -> ) PRE_SPLIT_REGIONS=4, TTL = `expire_at` + INTERVAL 0 MINUTE;
  Query OK, 0 rows affected, 2 warnings (0.07 sec)

  mysql> 
  mysql> DROP TABLE IF EXISTS test.tbl_ttl_3;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> CREATE TABLE test.tbl_ttl_3 (
      -> `ID` bigint PRIMARY KEY AUTO_RANDOM,
      -> `pState` varchar (100) NOT NULL,
      -> `cState` tinyint (4) DEFAULT NULL,
      -> `created_at` DATETIME,
      -> `updated_at` DATETIME,
      -> `txt` varchar(64) DEFAULT 'TTLTEST',
      -> KEY `pcStateIndex` (`pState`, `cState`)
      -> ) PRE_SPLIT_REGIONS=4;
  Query OK, 0 rows affected, 2 warnings (0.06 sec)
  ```

+ 通过创建表的 DDL 语句可以了解到：
  + 表 `test.tbl_ttl_1` 中数据过期时间为 `created_at` 列的时间值加 1 分钟。
  + 表 `test.tbl_ttl_2` 中的数据当列 `cState` 的值为 `1` 时数据过期时间为 `created_at` 列的时间值加 1 分钟，否则为 `created_at` 列的时间值加 2 天。
  + 表 `test.tbl_ttl_3` 没有设置 TTL 属性。

+ 在[终端 2](ssh://monitor1-ssh#2) 上，使用 tiup bench 工具向表 `test.tbl_ttl_1`，`test.tbl_ttl_2` 和 `test.tbl_ttl_3` 中各插入 1,000 行随机数据。关于 TiUP bench 组件的使用请参考：<https://docs.pingcap.com/zh/tidb/stable/tiup-bench/>。

  ```bash
  cat > tbl_ttl_insert_1.sql <<EOF
  INSERT INTO test.tbl_ttl_1 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  INSERT INTO test.tbl_ttl_2(pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  INSERT INTO test.tbl_ttl_3 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  EOF
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 1000 --threads 1 --query-files tbl_ttl_insert_1.sql
  ```

  ```output
  [ec2-user@ip-10-90-4-53 ~]$ cat > tbl_ttl_insert_1.sql <<EOF
  > INSERT INTO test.tbl_ttl_1 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > INSERT INTO test.tbl_ttl_2(pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > INSERT INTO test.tbl_ttl_3 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > EOF
  [ec2-user@ip-10-90-4-53 ~]$ export PATH="$PATH:/home/ec2-user/.tiup/bin"
  [ec2-user@ip-10-90-4-53 ~]$ tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 1000 --threads 1 --query-files tbl_ttl_insert_1.sql
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.183 -P 4000 --count 1000 --threads 1 --query-files tbl_ttl_insert_1.sql
  Finished
  [Summary] TBL_TTL_INSERT_1: 0.01s
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 查看 3 张表的数据量， 对于表 `test.tbl_ttl_2` 只查看列 `cstate` 值为 `1` 的数据行数， 也就是过期时间为 1 分钟的数据。注意：输出结果可能有所不同。

  ```sql
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |     1000 |
  +----------+
  1 row in set (0.00 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      505 |
  +----------+
  1 row in set (0.01 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |     1000 |
  +----------+
  1 row in set (0.00 sec)
  ```

### 步骤 3. 设置表的 TTL 属性

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。`mysql.tidb_ttl_table_status` 表中包含了所有 TTL 表的上一次执行与正在执行的 TTL 任务的信息。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 112
                parent_table_id: 112
                table_statistics: NULL
                    last_job_id: NULL
            last_job_start_time: NULL
            last_job_finish_time: NULL
            last_job_ttl_expire: NULL
                last_job_summary: NULL
                  current_job_id: 1ab86f3817964e41a0ef90209a57b23a
            current_job_owner_id: 84823e1f-e42a-4dc8-bc6e-e4fdaf2ba114
          current_job_owner_addr: NULL
      current_job_owner_hb_time: 2025-09-07 10:36:40
          current_job_start_time: 2025-09-07 10:33:14
          current_job_ttl_expire: 2025-09-07 10:33:14
              current_job_state: NULL
              current_job_status: running
  current_job_status_update_time: 2025-09-07 10:33:14
  1 row in set (0.02 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上，第一次执行时可能只输出 1 张表的信息，反复执行几次后会出现 2 张表的信息。注意：因为表 `test.tbl_ttl_3` 没有配置 TTL 属性，所以目前是不会出现在输出结果中的。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_1
                        table_id: 110
                parent_table_id: 110
                table_statistics: NULL
                    last_job_id: NULL
            last_job_start_time: NULL
            last_job_finish_time: NULL
            last_job_ttl_expire: NULL
                last_job_summary: NULL
                  current_job_id: 9e46c3c8dc3c4be8992a9d4cb856363a
            current_job_owner_id: 84823e1f-e42a-4dc8-bc6e-e4fdaf2ba114
          current_job_owner_addr: NULL
      current_job_owner_hb_time: 2025-09-07 10:37:20
          current_job_start_time: 2025-09-07 10:37:18
          current_job_ttl_expire: 2025-09-07 10:36:18
              current_job_state: NULL
              current_job_status: running
  current_job_status_update_time: 2025-09-07 10:37:18
  *************************** 2. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 112
                parent_table_id: 112
                table_statistics: NULL
                    last_job_id: NULL
            last_job_start_time: NULL
            last_job_finish_time: NULL
            last_job_ttl_expire: NULL
                last_job_summary: NULL
                  current_job_id: 1ab86f3817964e41a0ef90209a57b23a
            current_job_owner_id: 84823e1f-e42a-4dc8-bc6e-e4fdaf2ba114
          current_job_owner_addr: NULL
      current_job_owner_hb_time: 2025-09-07 10:37:20
          current_job_start_time: 2025-09-07 10:33:14
          current_job_ttl_expire: 2025-09-07 10:33:14
              current_job_state: NULL
              current_job_status: running
  current_job_status_update_time: 2025-09-07 10:33:14
  2 rows in set (0.02 sec)
  ```

  + 在[终端 1](ssh://monitor1-ssh#1) 上，检查表 `test.tbl_ttl_1` 和 表 `test.tbl_ttl_2` 的建表语句，发现 TTL 属性中 `TTL_JOB_INTERVAL` 的值默认为 `1h`， 这表示 TTL 的后台清理任务为每 1 小时执行一次。

  ```sql
  SHOW CREATE TABLE test.tbl_ttl_1\G
  SHOW CREATE TABLE test.tbl_ttl_2\G
  ```

  ```output
  mysql> SHOW CREATE TABLE test.tbl_ttl_1\G
  *************************** 1. row ***************************
        Table: tbl_ttl_1
  Create Table: CREATE TABLE `tbl_ttl_1` (
    `ID` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    `pState` varchar(100) NOT NULL,
    `cState` tinyint DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `txt` varchar(64) DEFAULT 'TTLTEST',
    PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */,
    KEY `pcStateIndex` (`pState`,`cState`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */ /*T! PRE_SPLIT_REGIONS=4 */ /*T![ttl] TTL=`created_at` + INTERVAL 1 MINUTE */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='1h' */
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE test.tbl_ttl_2\G
  *************************** 1. row ***************************
        Table: tbl_ttl_2
  Create Table: CREATE TABLE `tbl_ttl_2` (
    `ID` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    `pState` varchar(100) NOT NULL,
    `cState` tinyint DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `expire_at` datetime GENERATED ALWAYS AS (if(`cState` = 1, date_add(`created_at`, interval 1 minute), date_add(`created_at`, interval 2 day))) VIRTUAL,
    `txt` varchar(64) DEFAULT 'TTLTEST',
    PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */,
    KEY `pcStateIndex` (`pState`,`cState`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */ /*T! PRE_SPLIT_REGIONS=4 */ /*T![ttl] TTL=`expire_at` + INTERVAL 0 MINUTE */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='1h' */
  1 row in set (0.00 sec)
  ```

  + 在[终端 1](ssh://monitor1-ssh#1) 上，通过下面的语句将 TTL 后台清理任务设置为每 1 分钟执行一次。表 `test.tbl_ttl_3` 的命令会失败，原因是其并没有开启 TTL 配置。

  ```sql
  ALTER TABLE test.tbl_ttl_1 TTL_JOB_INTERVAL = '1m';
  ALTER TABLE test.tbl_ttl_2 TTL_JOB_INTERVAL = '1m';
  ALTER TABLE test.tbl_ttl_3 TTL_JOB_INTERVAL = '1m';
  ```

  ```output
  mysql> ALTER TABLE test.tbl_ttl_1 TTL_JOB_INTERVAL = '1m';
  Query OK, 0 rows affected (0.06 sec)

  mysql> ALTER TABLE test.tbl_ttl_2 TTL_JOB_INTERVAL = '1m';
  Query OK, 0 rows affected (0.06 sec)

  mysql> ALTER TABLE test.tbl_ttl_3 TTL_JOB_INTERVAL = '1m';
  ERROR 8150 (HY000): Cannot set TTL_JOB_INTERVAL on a table without TTL config
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 查看 3 张表的数据量， 对于表 `test.tbl_ttl_2` 只查看列 `cstate` 值为 `1` 的数据行数， 也就是过期时间为 `created_at` 列的值加 1 分钟。请注意:由于 TTL 后台执行时间不同，输出结果可能与以下结果有所不同，但可以看到表 `test.tbl_ttl_1` 和 `test.tbl_ttl_2` 中的数据已经开始被 TTL 清理。

  ```sql
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |      699 |
  +----------+
  1 row in set (0.00 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      289 |
  +----------+
  1 row in set (0.00 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |     1000 |
  +----------+
  1 row in set (0.00 sec)
  ```

+ 如果间隔 1 分钟以后再次执行相同的查询，会发现表的数据量再次减少，说明 TTL 任务正在执行。请注意:由于 TTL 后台执行时间不同，输出结果可能与以下结果有所不同，但可以看到表 `test.tbl_ttl_1` 和 `test.tbl_ttl_2` 中的数据已经开始被 TTL 清理。

  ```sql
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |      510 |
  +----------+
  1 row in set (0.01 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      191 |
  +----------+
  1 row in set (0.00 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |     1000 |
  +----------+
  1 row in set (0.00 sec)
  ```

### 步骤 4. 为表添加 TTL 属性

+ 在[终端 1](ssh://monitor1-ssh#1) 上，为表 `test.tbl_ttl_3` 配置 TTL 属性，数据过期时间为 `created_at` 列的值加 1 分钟， TTL 后台清理任务设置为每 1 分钟执行一次。

  ```sql
  ALTER TABLE test.tbl_ttl_3 TTL = `created_at` + INTERVAL 1 MINUTE;
  ALTER TABLE test.tbl_ttl_3 TTL_JOB_INTERVAL = '1m';
  ```

  ```output
  mysql> ALTER TABLE test.tbl_ttl_3 TTL = `created_at` + INTERVAL 1 MINUTE;
  Query OK, 0 rows affected (0.06 sec)
  mysql> ALTER TABLE test.tbl_ttl_3 TTL_JOB_INTERVAL = '1m';
  Query OK, 0 rows affected (0.52 sec)
  ```

### 步骤 5. 周期性向表中插入数据

+ 在[终端 2](ssh://monitor1-ssh#2) 上，使用 tiup bench 工具向表 `tbl_ttl_1`，`tbl_ttl_2` 和 `tbl_ttl_3` 周期性插入 1 行随机数据，每个周期间隔 1 秒，共执行 3600 个周期。

  ```bash
  cat > tbl_ttl_insert_2.sql <<EOF
  INSERT INTO test.tbl_ttl_1 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  INSERT INTO test.tbl_ttl_2(pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  INSERT INTO test.tbl_ttl_3 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  SELECT SLEEP(1);
  EOF
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 3600 --threads 1 --query-files tbl_ttl_insert_2.sql
  ```

  ```output
  [ec2-user@ip-10-90-4-151 ~]$ cat > tbl_ttl_insert_2.sql <<EOF
  > INSERT INTO test.tbl_ttl_1 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > INSERT INTO test.tbl_ttl_2(pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > INSERT INTO test.tbl_ttl_3 (pState, cState, created_at, updated_at) VALUES (ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 2), NOW(), NOW());
  > SELECT SLEEP(1);
  > EOF
  [ec2-user@ip-10-90-4-151 ~]$ export PATH="$PATH:/home/ec2-user/.tiup/bin"
  [ec2-user@ip-10-90-4-151 ~]$ tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 3600 --threads 1 --query-files tbl_ttl_insert_2.sql
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.2.64 -P 4000 --count 3600 --threads 1 --query-files tbl_ttl_insert_2.sql
  [Current] TBL_TTL_INSERT_2: 1.01s
  [Current] TBL_TTL_INSERT_2: 1.01s
  [Current] TBL_TTL_INSERT_2: 1.01s
  [Current] TBL_TTL_INSERT_2: 1.01s
  ...
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 查看 3 张表的数据量， 对于表 `test.tbl_ttl_2` 只查看列 `cstate` 值为 `1` 的数据行数， 也就是过期时间为 `created_at` 列的值加 1 分钟。可以发现表的数据量开始上升。

  ```sql
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |      220 |
  +----------+
  1 row in set (0.01 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      118 |
  +----------+
  1 row in set (0.00 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      220 |
  +----------+
  1 row in set (0.00 sec)
  ```

### 步骤 6. 关闭与打开 TiDB 的 TTL 属性

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 将全局变量 `tidb_ttl_job_enable` 设置为 `OFF`。

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  SET GLOBAL tidb_ttl_job_enable='OFF';
  SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  ```

  ```output
  mysql> SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | tidb_ttl_job_enable | ON    |
  +---------------------+-------+
  1 row in set (0.00 sec)

  mysql> SET GLOBAL tidb_ttl_job_enable='OFF';
  Query OK, 0 rows affected (0.02 sec)

  mysql> SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | tidb_ttl_job_enable | OFF   |
  +---------------------+-------+
  1 row in set (0.00 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。 发现 3 个表上都没有 TTL 任务在运行。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_1
                        table_id: 110
                parent_table_id: 110
                table_statistics: NULL
                    last_job_id: 85188517dd1342cb980eab52b9a77475
            last_job_start_time: 2025-09-07 10:51:01
            last_job_finish_time: 2025-09-07 10:53:10
            last_job_ttl_expire: 2025-09-07 10:50:01
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":16,"scheduled_scan_task":16,"finished_scan_task":16}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 2. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 112
                parent_table_id: 112
                table_statistics: NULL
                    last_job_id: a25399e2b753437b8987c2ea5c1a6369
            last_job_start_time: 2025-09-07 10:53:02
            last_job_finish_time: 2025-09-07 10:55:19
            last_job_ttl_expire: 2025-09-07 10:53:02
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":0,"scheduled_scan_task":0,"finished_scan_task":0,"scan_task_err":"tidb_ttl_job_enable turned off"}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 3. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_3
                        table_id: 114
                parent_table_id: 114
                table_statistics: NULL
                    last_job_id: 22b7914f44ca4243b09e97e18cbb80d0
            last_job_start_time: 2025-09-07 10:47:57
            last_job_finish_time: 2025-09-07 10:51:10
            last_job_ttl_expire: 2025-09-07 10:46:57
                last_job_summary: {"total_rows":1000,"success_rows":1000,"error_rows":0,"total_scan_task":16,"scheduled_scan_task":16,"finished_scan_task":16}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  3 rows in set (0.02 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 将全局变量 `tidb_ttl_job_enable` 设置为 `ON`。

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  SET GLOBAL tidb_ttl_job_enable='ON';
  SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  ```

  ```output
  mysql> SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | tidb_ttl_job_enable | OFF   |
  +---------------------+-------+
  1 row in set (0.00 sec)

  mysql> SET GLOBAL tidb_ttl_job_enable='ON';
  Query OK, 0 rows affected (0.01 sec)

  mysql> SHOW GLOBAL VARIABLES LIKE 'tidb_ttl_job_enable';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | tidb_ttl_job_enable | ON    |
  +---------------------+-------+
  1 row in set (0.00 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。 反复执行几次，会发现 TTL 任务重新在表上开始运行了。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_1
                        table_id: 110
                parent_table_id: 110
                table_statistics: NULL
                    last_job_id: 85188517dd1342cb980eab52b9a77475
            last_job_start_time: 2025-09-07 10:51:01
            last_job_finish_time: 2025-09-07 10:53:10
            last_job_ttl_expire: 2025-09-07 10:50:01
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":16,"scheduled_scan_task":16,"finished_scan_task":16}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 2. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 112
                parent_table_id: 112
                table_statistics: NULL
                    last_job_id: a25399e2b753437b8987c2ea5c1a6369
            last_job_start_time: 2025-09-07 10:53:02
            last_job_finish_time: 2025-09-07 10:55:19
            last_job_ttl_expire: 2025-09-07 10:53:02
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":0,"scheduled_scan_task":0,"finished_scan_task":0,"scan_task_err":"tidb_ttl_job_enable turned off"}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 3. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_3
                        table_id: 114
                parent_table_id: 114
                table_statistics: NULL
                    last_job_id: 22b7914f44ca4243b09e97e18cbb80d0
            last_job_start_time: 2025-09-07 10:47:57
            last_job_finish_time: 2025-09-07 10:51:10
            last_job_ttl_expire: 2025-09-07 10:46:57
                last_job_summary: {"total_rows":1000,"success_rows":1000,"error_rows":0,"total_scan_task":16,"scheduled_scan_task":16,"finished_scan_task":16}
                  current_job_id: 2722bcbe1fe544839ccaa6c1d31d7e10
            current_job_owner_id: 84823e1f-e42a-4dc8-bc6e-e4fdaf2ba114
          current_job_owner_addr: NULL
      current_job_owner_hb_time: 2025-09-07 11:02:50
          current_job_start_time: 2025-09-07 11:01:18
          current_job_ttl_expire: 2025-09-07 11:00:18
              current_job_state: NULL
              current_job_status: running
  current_job_status_update_time: 2025-09-07 11:01:18
  3 rows in set (0.02 sec)
  ```

### 步骤 7. 关闭与打开表的 TTL 属性

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 将表 `test.tbl_ttl_2` 和表 `test.tbl_ttl_3` 的 TTL_ENABLE 属性设置为 `OFF`， 并删除表 `test.ttbl_ttl_1` 的所有 TTL 属性。

  ```sql
  ALTER TABLE test.tbl_ttl_2 TTL_ENABLE = 'OFF';
  ALTER TABLE test.tbl_ttl_3 TTL_ENABLE = 'OFF';
  ALTER TABLE test.tbl_ttl_1 REMOVE TTL;
  ```

  ```output
  mysql> ALTER TABLE test.tbl_ttl_2 TTL_ENABLE = 'OFF';
  Query OK, 0 rows affected (0.06 sec)

  mysql> ALTER TABLE test.tbl_ttl_3 TTL_ENABLE = 'OFF';
  Query OK, 0 rows affected (0.06 sec)

  mysql> ALTER TABLE test.tbl_ttl_1 REMOVE TTL;
  Query OK, 0 rows affected (0.07 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。 反复执行多次后，会发现结果为空。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_1
                        table_id: 116
                parent_table_id: 116
                table_statistics: NULL
                    last_job_id: 2b6d5038ff0547f3a3fc757c3249fdce
            last_job_start_time: 2025-09-07 06:21:27
            last_job_finish_time: 2025-09-07 06:22:28
            last_job_ttl_expire: 2025-09-07 06:20:27
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":0,"scheduled_scan_task":0,"finished_scan_task":0,"scan_task_err":"TTL table has been removed or the TTL on this table has been stopped"}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 2. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 118
                parent_table_id: 118
                table_statistics: NULL
                    last_job_id: f37814e4203846d0b6529041dd3457d3
            last_job_start_time: 2025-09-07 05:56:43
            last_job_finish_time: 2025-09-07 06:00:09
            last_job_ttl_expire: 2025-09-07 05:56:43
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":16,"scheduled_scan_task":16,"finished_scan_task":16}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 3. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_3
                        table_id: 120
                parent_table_id: 120
                table_statistics: NULL
                    last_job_id: 2daa646b1eed4c49a101a4d3df1515a1
            last_job_start_time: 2025-09-07 06:19:03
            last_job_finish_time: 2025-09-07 06:21:19
            last_job_ttl_expire: 2025-09-07 06:18:03
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":0,"scheduled_scan_task":0,"finished_scan_task":0,"scan_task_err":"TTL table has been removed or the TTL on this table has been stopped"}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  3 rows in set (0.02 sec)
  ```

+ 3～5 分钟后，查询结果为空。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  Empty set (0.02 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 将表 `test.tbl_ttl_1`，表 `test.tbl_ttl_2`和表 `test.tbl_ttl_3` 的 TTL_ENABLE 属性设置为 `ON`。 发现表 `test.tbl_ttl_1` 的设置命令会失败，原因是该表的 TTL 属性已经被全部删除了。

  ```sql
  ALTER TABLE test.tbl_ttl_2 TTL_ENABLE = 'ON';
  ALTER TABLE test.tbl_ttl_3 TTL_ENABLE = 'ON';
  ALTER TABLE test.tbl_ttl_1 TTL_ENABLE = 'ON';
  ```

  ```output
  mysql> ALTER TABLE test.tbl_ttl_2 TTL_ENABLE = 'ON';
  Query OK, 0 rows affected (0.51 sec)

  mysql> ALTER TABLE test.tbl_ttl_3 TTL_ENABLE = 'ON';
  Query OK, 0 rows affected (0.51 sec)

  mysql> ALTER TABLE test.tbl_ttl_1 TTL_ENABLE = 'ON';
  ERROR 8150 (HY000): Cannot set TTL_ENABLE on a table without TTL config
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。 反复执行几次后，发现 TTL 任务重新在表 `test.tbl_ttl_2` 和表 `test.tbl_ttl_3` 上开始运行了。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_2
                        table_id: 118
                parent_table_id: 118
                table_statistics: NULL
                    last_job_id: aef75a31eb844b70862d37abe1445837
            last_job_start_time: 2025-09-07 06:46:34
            last_job_finish_time: 2025-09-07 06:46:39
            last_job_ttl_expire: 2025-09-07 06:46:34
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  *************************** 2. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_3
                        table_id: 120
                parent_table_id: 120
                table_statistics: NULL
                    last_job_id: c3b0bda2789c4c2580e8a4a1c29b3f46
            last_job_start_time: 2025-09-07 06:45:55
            last_job_finish_time: 2025-09-07 06:46:09
            last_job_ttl_expire: 2025-09-07 06:44:55
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
                  current_job_id: be5877fc6b724882aef1ebd289f29545
            current_job_owner_id: 52b4aed5-367c-45d9-b421-29b9772061d1
          current_job_owner_addr: NULL
      current_job_owner_hb_time: 2025-09-07 06:46:59
          current_job_start_time: 2025-09-07 06:46:55
          current_job_ttl_expire: 2025-09-07 06:45:55
              current_job_state: NULL
              current_job_status: running
  current_job_status_update_time: 2025-09-07 06:46:55
  2 rows in set (0.02 sec)
  ```

### 步骤 8. 查看 FLASHBACK TABLE 对于表 TTL 属性的影响

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 首先，删除表 `test.tbl_ttl_2`，运行。接着执行对表 `test.tbl_ttl_1`, 表 `test.tbl_ttl_2` 和表 `test.tbl_ttl_3` 数据量查询的命令，从结果中可以发现表 `test.tbl_ttl_2` 已经不存在了。

  ```sql
  DROP TABLE test.tbl_ttl_2;
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> DROP TABLE test.tbl_ttl_2;
  Query OK, 0 rows affected (0.51 sec)
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |     3581 |
  +----------+
  1 row in set (0.01 sec)

  ERROR 1146 (42S02): Table 'test.tbl_ttl_2' doesn't exist
  +----------+
  | COUNT(*) |
  +----------+
  |      172 |
  +----------+
  1 row in set (0.01 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 对于刚刚删除的表 `test.tbl_ttl_2` 执行 FLASHBACK TABLE 命令。

  ```sql
  FLASHBACK TABLE test.tbl_ttl_2;
  ```

  ```output
  mysql> FLASHBACK TABLE test.tbl_ttl_2;
  Query OK, 0 rows affected (0.52 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 查看 3 张表的数据量， 对于表 `test.tbl_ttl_2` 只查看列 `cstate` 值为 `1` 的数据行数， 也就是过期时间为 `created_at` 列的值加 1 分钟。可以发现表 `test.tbl_ttl_2` 已经恢复。

  ```sql
  SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM test.tbl_ttl_1;SELECT COUNT(*) FROM test.tbl_ttl_2 WHERE cState=1;SELECT COUNT(*) FROM test.tbl_ttl_3;
  +----------+
  | COUNT(*) |
  +----------+
  |     3581 |
  +----------+
  1 row in set (0.01 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |      101 |
  +----------+
  1 row in set (0.01 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |       54 |
  +----------+
  1 row in set (0.01 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 查看表 `test.tbl_ttl_2` 的定义，发现在 FLASHBACK TABLE 后 TTL 属性中 TTL_ENABLE 值为 'OFF'，即该表的 TTL 属性被自动关闭。

  ```sql
  SHOW CREATE TABLE test.tbl_ttl_2\G
  ```

  ```output
  mysql> SHOW CREATE TABLE test.tbl_ttl_2\G
  *************************** 1. row ***************************
        Table: tbl_ttl_2
  Create Table: CREATE TABLE `tbl_ttl_2` (
    `ID` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
    `pState` varchar(100) NOT NULL,
    `cState` tinyint DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `expire_at` datetime GENERATED ALWAYS AS (if(`cState` = 1, date_add(`created_at`, interval 1 minute), date_add(`created_at`, interval 2 day))) VIRTUAL,
    `txt` varchar(64) DEFAULT 'TTLTEST',
    PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */,
    KEY `pcStateIndex` (`pState`,`cState`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30001 */ /*T! PRE_SPLIT_REGIONS=4 */ /*T![ttl] TTL=`expire_at` + INTERVAL 0 MINUTE */ /*T![ttl] TTL_ENABLE='OFF' */ /*T![ttl] TTL_JOB_INTERVAL='1m' */
  1 row in set (0.01 sec)
  ```

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 通过系统表 `information_schema.tables` 和 `mysql.tidb_ttl_table_status`，来查看当前 TTL 任务的信息。 反复执行几次后，发现只有表 `test.tbl_ttl_3` 上的 TTL 任务在运行。注意：可能需要 3～5 分钟才会输出期望的结果，请耐心等待，或者直接进行下面的操作也不会影响接下来的步骤。

  ```sql
  SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  ```

  ```output
  mysql> SELECT A.TABLE_SCHEMA, A.TABLE_NAME, B.* FROM information_schema.tables A, mysql.tidb_ttl_table_status B WHERE A.tidb_table_ID=B.table_id\G
  *************************** 1. row ***************************
                    TABLE_SCHEMA: test
                      TABLE_NAME: tbl_ttl_3
                        table_id: 120
                parent_table_id: 120
                table_statistics: NULL
                    last_job_id: e0b4af3e300841758dfa3f470dafad18
            last_job_start_time: 2025-09-07 06:54:55
            last_job_finish_time: 2025-09-07 06:55:09
            last_job_ttl_expire: 2025-09-07 06:53:55
                last_job_summary: {"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
                  current_job_id: NULL
            current_job_owner_id: NULL
          current_job_owner_addr: NULL
      current_job_owner_hb_time: NULL
          current_job_start_time: NULL
          current_job_ttl_expire: NULL
              current_job_state: NULL
              current_job_status: NULL
  current_job_status_update_time: NULL
  1 row in set (0.02 sec)
  ```

### 步骤 9. 清理数据

+ 在[终端 1](ssh://monitor1-ssh#1) 上， 删除 `test` 下的表 `tbl_ttl_1`，`tbl_ttl_2` 和 `tbl_ttl_3`。

  ```sql
  DROP TABLE test.tbl_ttl_1;
  DROP TABLE test.tbl_ttl_2;
  DROP TABLE test.tbl_ttl_3;
  ```

  ```output
  mysql> DROP TABLE test.tbl_ttl_1;
  Query OK, 0 rows affected (0.52 sec)

  mysql> DROP TABLE test.tbl_ttl_2;
  Query OK, 0 rows affected (0.52 sec)

  mysql> DROP TABLE test.tbl_ttl_3;
  Query OK, 0 rows affected (0.51 sec)
  ```

+ 本模块到此结束。