TiDB使用 TTL (Time to Live) 定期删除过期数据

一、TTL功能概述

1.1 什么是TTL

TTL（Time to Live，生存时间）是TiDB 6.1版本引入的一项重要功能，它提供了自动化的数据生命周期管理机制。通过为表设置TTL属性，TiDB可以自动识别并清理过期的数据，无需手动编写复杂的删除脚本或定时任务。

TTL功能的核心思想是：为数据设定一个有效期，超过这个期限的数据将被系统自动清理。这种机制既简化了数据管理，又能有效控制存储成本。

1.2 TTL与传统删除方式的对比

特性	传统定期删除	TTL自动删除
实现方式	自定义脚本或存储过程	数据库内置功能
对业务影响	可能影响在线业务	最小化对在线业务的影响
维护成本	需要维护定时任务	配置后自动执行
删除精度	取决于脚本逻辑	基于表中的时间字段
资源消耗	可能导致资源竞争	系统自动调度，资源可控

二、TTL应用场景

2.1 典型应用场景

1. 会话数据管理：自动清理过期的用户会话记录、验证码、登录令牌等

   CREATE TABLE session_tokens (
       token_id VARCHAR(64) PRIMARY KEY,
       user_id INT NOT NULL,
       created_at TIMESTAMP NOT NULL DEFAULT NOW(),
       data JSON
   ) TTL = `created_at` + INTERVAL 7 DAY;
   

2. 日志和审计数据：按保留策略自动清理系统日志、操作记录

   CREATE TABLE audit_logs (
       id BIGINT AUTO_INCREMENT PRIMARY KEY,
       operation VARCHAR(32) NOT NULL,
       operator_id INT NOT NULL,
       operation_time TIMESTAMP NOT NULL DEFAULT NOW(),
       details TEXT
   ) TTL = `operation_time` + INTERVAL 90 DAY;
   

3. 时序数据管理：物联网设备数据、监控指标等时序数据的自动老化

   CREATE TABLE device_metrics (
       device_id INT NOT NULL,
       metric_time TIMESTAMP NOT NULL,
       temperature FLOAT,
       humidity FLOAT,
       PRIMARY KEY (device_id, metric_time)
   ) TTL = `metric_time` + INTERVAL 6 MONTH;
   

4. 电商订单数据：按业务规则自动归档或清理历史订单

   CREATE TABLE order_details (
       order_id BIGINT NOT NULL,
       product_id INT NOT NULL,
       created_at TIMESTAMP NOT NULL,
       quantity INT NOT NULL,
       price DECIMAL(10,2) NOT NULL,
       PRIMARY KEY (order_id, product_id)
   ) TTL = `created_at` + INTERVAL 3 YEAR;
   

2.2 TTL的业务价值

• 降低存储成本：自动清理过期数据，减少存储空间占用
• 提升查询性能：减少历史数据量，提高查询效率
• 简化运维工作：无需编写和维护复杂的数据清理脚本
• 合规要求支持：帮助满足数据保留政策和隐私法规要求

三、TTL详细配置与使用

3.1 创建带TTL属性的表

创建表时可以直接指定TTL属性：

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(32) NOT NULL,
    event_time TIMESTAMP NOT NULL DEFAULT NOW(),
    payload JSON
) TTL = `event_time` + INTERVAL 30 DAY;

TTL表达式的一般格式为：时间列 + 时间间隔

支持的时间间隔单位包括：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

3.2 为现有表添加或修改TTL

可以通过ALTER TABLE语句为已有表添加TTL属性：

-- 添加TTL属性
ALTER TABLE user_logs TTL = `log_time` + INTERVAL 60 DAY;

-- 修改TTL时间间隔
ALTER TABLE user_logs TTL = `log_time` + INTERVAL 90 DAY;

-- 修改TTL使用的时间列
ALTER TABLE user_logs TTL = `updated_at` + INTERVAL 60 DAY;

3.3 禁用和启用TTL

可以临时禁用表的TTL功能，而不删除TTL配置：

-- 禁用TTL
ALTER TABLE user_logs TTL_ENABLE = 'OFF';

-- 重新启用TTL
ALTER TABLE user_logs TTL_ENABLE = 'ON';

3.4 调整TTL任务执行频率

TTL清理任务默认每小时执行一次，可以通过以下方式调整执行间隔：

-- 设置为每天执行一次
ALTER TABLE user_logs TTL_JOB_INTERVAL = '24h';

-- 设置为每30分钟执行一次
ALTER TABLE user_logs TTL_JOB_INTERVAL = '30m';

支持的时间单位：

• h（小时）
• m（分钟）
• s（秒）

3.5 查看表的TTL配置

可以通过INFORMATION_SCHEMA查看表的TTL配置：

SELECT * FROM information_schema.tidb_ttl_table_status WHERE table_schema = 'your_database' AND table_name = 'your_table';

四、TTL工作原理与实现细节

4.1 TTL任务执行机制

1. 任务调度：TiDB后台会定期检查所有启用了TTL的表
2. 数据识别：根据TTL表达式计算过期时间点，识别需要删除的数据
3. 批量删除：以小批次方式删除数据，避免对系统造成冲击
4. 资源控制：TTL任务会自动控制删除速率，避免影响在线业务

4.2 TTL任务状态监控

TiDB提供了系统表来监控TTL任务的执行状态：

-- 查看TTL表状态
SELECT * FROM mysql.tidb_ttl_table_status;

-- 查看TTL任务历史
SELECT * FROM mysql.tidb_ttl_job_history;

关键监控指标：

• last_job_start_time：上次任务开始时间
• last_job_finish_time：上次任务完成时间
• last_job_ttl_expire：上次任务的过期时间点
• last_job_summary：上次任务执行摘要（如删除行数）

4.3 TTL性能优化

为了提高TTL任务的执行效率，建议：

1. 为TTL时间列创建索引：加速过期数据的查找

   ALTER TABLE events ADD INDEX idx_event_time (event_time);
   

2. 合理设置任务间隔：根据数据量和业务特点调整执行频率

   ALTER TABLE events TTL_JOB_INTERVAL = '12h';
   

3. 避免高峰期执行：可以临时禁用TTL，在业务低峰期再启用

   -- 业务高峰期
   ALTER TABLE events TTL_ENABLE = 'OFF';
   
   -- 业务低峰期
   ALTER TABLE events TTL_ENABLE = 'ON';
   

五、使用限制与注意事项

5.1 功能限制

• TTL不支持临时表（TEMPORARY TABLE）
• 带有外键约束的主表不能使用TTL
• TTL表达式只能使用表中的时间类型列（DATE、DATETIME、TIMESTAMP）
• 分区表支持TTL，但TTL表达式不能使用分区键

5.2 执行保证

• TTL不保证过期数据会立即被删除，实际删除时间取决于任务调度和系统负载
• 在极端情况下（如系统负载过高），TTL任务可能会被延迟执行
• TTL任务执行时会自动限制资源使用，以避免影响在线业务

5.3 版本兼容性

• TTL功能需要TiDB 6.1.0或更高版本
• 从低版本升级到支持TTL的版本后，需要重新配置TTL属性

六、最佳实践案例

6.1 电商平台订单数据管理

某电商平台使用TTL管理订单数据：

-- 订单主表（长期保留）
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    order_time TIMESTAMP NOT NULL DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(16) NOT NULL
) TTL = `order_time` + INTERVAL 7 YEAR;

-- 订单详情表（保留时间较短）
CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    item_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id, item_id)
) TTL = `created_at` + INTERVAL 3 YEAR;

-- 订单状态变更历史（保留时间更短）
CREATE TABLE order_status_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    old_status VARCHAR(16),
    new_status VARCHAR(16) NOT NULL,
    change_time TIMESTAMP NOT NULL DEFAULT NOW(),
    operator VARCHAR(32)
) TTL = `change_time` + INTERVAL 1 YEAR;

6.2 物联网平台数据管理

某物联网平台使用TTL管理设备数据：

-- 设备基础信息（长期保留）
CREATE TABLE devices (
    device_id VARCHAR(64) PRIMARY KEY,
    device_type VARCHAR(32) NOT NULL,
    register_time TIMESTAMP NOT NULL DEFAULT NOW(),
    last_active_time TIMESTAMP,
    firmware_version VARCHAR(32)
);

-- 设备状态数据（保留30天）
CREATE TABLE device_status (
    device_id VARCHAR(64) NOT NULL,
    status_time TIMESTAMP NOT NULL,
    online_status BOOLEAN NOT NULL,
    battery_level INT,
    signal_strength INT,
    PRIMARY KEY (device_id, status_time)
) TTL = `status_time` + INTERVAL 30 DAY;

-- 设备详细指标数据（保留7天）
CREATE TABLE device_metrics (
    device_id VARCHAR(64) NOT NULL,
    collect_time TIMESTAMP NOT NULL,
    temperature FLOAT,
    humidity FLOAT,
    pressure FLOAT,
    PRIMARY KEY (device_id, collect_time)
) TTL = `collect_time` + INTERVAL 7 DAY;

参考资料

• TiDB官方文档：TTL (Time to Live) (opens new window)
• TiDB 6.1 Release Notes (opens new window)