大数据表查询优化 - 表分区

快两年没写过业务代码了……
今天帮一个研发团队优化了一下数据库表的查询性能。使用的是表分区。
简单记录了一下步骤，方便直接用：

1. 使用 Trade_Date 新建表分区，从 4 月建立到 2021-6，每月一个单独的分区：

   ALTER TABLE tbl_original_data 
   PARTITION BY RANGE(unix_timestamp(Trade_Date))(
     partition p0 values less than (unix_timestamp(‘2020-05-01 00:00:00‘)),
     partition p1 values less than (unix_timestamp(‘2020-06-01 00:00:00‘)),
     partition p2 values less than (unix_timestamp(‘2020-07-01 00:00:00‘)),
     partition p3 values less than (unix_timestamp(‘2020-08-01 00:00:00‘)),
     partition p4 values less than (unix_timestamp(‘2020-09-01 00:00:00‘)),
     partition p5 values less than (unix_timestamp(‘2020-10-01 00:00:00‘)),
     partition p6 values less than (unix_timestamp(‘2020-11-01 00:00:00‘)),
     partition p7 values less than (unix_timestamp(‘2020-12-01 00:00:00‘)),
     partition p8 values less than (unix_timestamp(‘2021-01-01 00:00:00‘)),
     partition p9 values less than (unix_timestamp(‘2021-02-01 00:00:00‘)),
     partition p10 values less than (unix_timestamp(‘2021-03-01 00:00:00‘)),
     partition p11 values less than (unix_timestamp(‘2021-04-01 00:00:00‘)),
     partition p12 values less than (unix_timestamp(‘2021-05-01 00:00:00‘)),
     partition p13 values less than (unix_timestamp(‘2021-06-01 00:00:00‘)),
     partition p14 values less than (unix_timestamp(‘2021-07-01 00:00:00‘))
   );
   

2. 到明年6月以后，使用以下语句添加新分区：

   ALTER TABLE tbl_original_data
   ADD PARTITION (PARTITION p15 values less than (unix_timestamp(‘2021-08-01 00:00:00‘)))
   

3. 因为现有查询已经带上 Trade_Date 字段条件，所以不用修改查询和程序。

参考文章：
MySQL数据库表分区功能详解
MySQL对数据表已有表进行分区表
Mysql 分区表-分区操作

大数据表查询优化 - 表分区

原文：https://www.cnblogs.com/zgynhqf/p/13750800.html