按时间范围分表
按时间点分表
MySQL示例:
-- 创建按月分表
CREATE TABLE orders_202301 LIKE orders_template;
CREATE TABLE orders_202302 LIKE orders_template;
-- 插入数据时根据时间路由
INSERT INTO orders_202301 SELECT * FROM new_orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
分区表方案:
-- 创建按范围分区的表
CREATE TABLE orders (
id INT,
order_date DATETIME,
customer_id INT,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)*100 + MONTH(order_date)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
Java Spring示例:
public String determineTableName(LocalDate orderDate) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
return "orders_" + orderDate.format(formatter);
}
@Repository
public class OrderRepository {
public void save(Order order) {
String tableName = determineTableName(order.getOrderDate().toLocalDate());
String sql = "INSERT INTO " + tableName + " (id, order_date, customer_id, amount) VALUES (?, ?, ?, ?)";
// 执行SQL
}
}
-- 跨月查询示例
SELECT * FROM orders_202301
UNION ALL
SELECT * FROM orders_202302
WHERE customer_id = 12345;
// 每月初创建下个月的表
@Scheduled(cron = "0 0 0 1 * ?")
public void createNextMonthTable() {
LocalDate nextMonth = LocalDate.now().plusMonths(1);
String tableName = "orders_" + nextMonth.format(DateTimeFormatter.ofPattern("yyyyMM"));
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS " + tableName + " LIKE orders_template");
}
// 将3个月前的数据归档到历史表
@Scheduled(cron = "0 0 1 * * ?")
public void archiveOldData() {
LocalDate archiveDate = LocalDate.now().minusMonths(3);
String sourceTable = "orders_" + archiveDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
String archiveTable = "orders_archive_" + archiveDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS " + archiveTable + " LIKE orders_template");
jdbcTemplate.execute("INSERT INTO " + archiveTable + " SELECT * FROM " + sourceTable);
jdbcTemplate.execute("DROP TABLE " + sourceTable);
}
使用分库分表中间件:
使用时序数据库:如InfluxDB、TimescaleDB等专门处理时间序列数据的数据库
冷热数据分离:
您需要根据具体的业务场景、数据量和查询模式选择最适合的分表策略。