# 1. 创建分区表
CREATE TABLE fgedudb.fgedu_orders (
id INT PRIMARY KEY,
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(20)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);

# 2. 配置存储介质
# 修改tikv.toml配置文件
[storage]
# 热数据存储路径
data-dir = “/tidb/fgdata/hot”

# 冷数据存储配置
[storage.cold-data]
enable = true
# 冷数据存储路径
data-dir = “/tidb/fgdata/cold”
# 冷数据迁移阈值（天数）
migration-threshold-days = 90

# 1. 手动迁移冷数据
# 创建冷数据表
CREATE TABLE fgedudb.fgedu_orders_cold LIKE fgedudb.fgedu_orders;

# 迁移冷数据
INSERT INTO fgedudb.fgedu_orders_cold SELECT * FROM fgedudb.fgedu_orders WHERE order_date < '2023-01-01'; # 删除原表中的冷数据 DELETE FROM fgedudb.fgedu_orders WHERE order_date < '2023-01-01'; # 2. 配置自动迁移 # 修改tidb.toml配置文件 [hot-cold-data] enable = true # 热数据保留天数 hot-data-retention-days = 90 # 冷数据存储路径 cold-data-path = "/tidb/fgdata/cold"

# 查看表大小
SELECT table_schema, table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = ‘fgedudb’
ORDER BY data_length + index_length DESC;

# 查看分区大小
SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = ‘fgedudb’ AND table_name = ‘fgedu_orders’;

# 监控存储使用
# 在Grafana中查看TiKV -> Storage面板

# 调优数据迁移策略
# 修改tikv.toml配置文件
[storage.cold-data]
migration-threshold-days = 180 # 调整迁移阈值
migration-batch-size = 1000 # 调整批处理大小

# 1. 问题描述：订单表数据量过大，影响查询性能
风哥提示：
# 2. 解决方案：
# 1. 创建分区表
CREATE TABLE fgedudb.fgedu_orders (
id INT PRIMARY KEY,
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(20)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

# 2. 迁移历史数据到冷存储
# 创建冷数据表
CREATE TABLE fgedudb.fgedu_orders_cold LIKE fgedudb.fgedu_orders;

# 迁移2022年数据
INSERT INTO fgedudb.fgedu_orders_cold SELECT * FROM fgedudb.fgedu_orders WHERE order_date < '2023-01-01'; DELETE FROM fgedudb.fgedu_orders WHERE order_date < '2023-01-01'; # 3. 效果：订单表大小显著减少，查询性能提升

# 1. 问题描述：存储成本过高

# 2. 解决方案：
# 1. 分析存储使用情况
SELECT table_schema, table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = ‘fgedudb’
ORDER BY data_length + index_length DESC;

# 2. 实施冷热数据分离
# 配置冷数据存储
# 修改tikv.toml配置文件
[storage.cold-data]
enable = true
data-dir = “/tidb/fgdata/cold”
migration-threshold-days = 90

# 3. 验证存储成本
# 计算存储成本节约
# 假设热存储成本为0.1元/GB/月，冷存储成本为0.03元/GB/月
# 冷数据量为1000GB
# 月节约成本：1000 * (0.1 – 0.03) = 70元

# 4. 效果：存储成本显著降低

# 1. 问题描述：查询性能下降

# 2. 分析原因：表数据量过大，热数据和冷数据混在一起

# 3. 解决方案：
# 1. 实施冷热数据分离
# 创建分区表
CREATE TABLE fgedudb.fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50),
created_at DATETIME,
last_login DATETIME
) PARTITION BY RANGE (YEAR(last_login)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);

# 2. 迁移不活跃用户数据到冷存储
INSERT INTO fgedudb.fgedu_users_cold SELECT * FROM fgedudb.fgedu_users WHERE last_login < '2023-01-01'; DELETE FROM fgedudb.fgedu_users WHERE last_login < '2023-01-01'; # 3. 效果：查询性能显著提升，特别是针对活跃用户的查询

更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 冷热数据分离最佳实践

• 根据业务特点定义冷热数据：不同业务的冷热数据定义不同
• 选择合适的存储介质：根据性能需求和成本预算选择存储介质
• 建立数据生命周期管理：定期评估数据热度，调整存储策略
• 监控数据访问模式：根据实际访问情况调整分层策略
• 结合分区表和存储分层：通过分区表实现逻辑分离，通过存储分层实现物理分离

5.2 常见问题与解决方案

• 数据迁移影响业务：在业务低峰期执行数据迁移
• 冷数据访问性能差：为冷数据创建合适的索引，或使用缓存
• 存储介质选择困难：根据数据访问模式和成本预算综合考虑
• 数据一致性问题：确保数据迁移过程中的数据一致性

学习交流加群风哥QQ113257174

5.3 未来发展趋势

• 智能冷热数据识别：利用AI技术自动识别冷热数据
• 自动数据迁移：根据访问模式自动进行数据迁移
• 云存储集成：更好地与云存储服务集成
• 多级存储分层：支持更多级别的存储分层
• 存储成本优化：通过智能算法优化存储成本

from tidb视频:www.itpux.com