前言

mysql 随着记录数的不断插入，当表记录数达到几千万的时候查询开始变慢许多，本次记录库表按月分区，前端通过时间限制来提高查询效率。

提示：以下是本篇文章正文内容，作者水平有限，下面案例仅供参考

一、数据量比较小时可以直接在原表建立分区方案

提示：将需要用来的分区的字段添加到主键

#  PRIMARY KEY (`id`,`request_time`)

ALTER TABLE tb_interface_call_log PARTITION BY RANGE COLUMNS(request_time)
(PARTITION P202206 VALUES LESS THAN ( 2022-07-01 ) ENGINE = InnoDB,
 PARTITION P202207 VALUES LESS THAN ( 2022-08-01 ) ENGINE = InnoDB,
 PARTITION P202208 VALUES LESS THAN ( 2022-09-01 ) ENGINE = InnoDB,
 PARTITION P202209 VALUES LESS THAN ( 2022-10-01 ) ENGINE = InnoDB,
 PARTITION P202210 VALUES LESS THAN ( 2022-11-01 ) ENGINE = InnoDB,
 PARTITION P202211 VALUES LESS THAN ( 2022-12-01 ) ENGINE = InnoDB,
 PARTITION P202212 VALUES LESS THAN ( 2023-01-01 ) ENGINE = InnoDB,
 PARTITION p202301 VALUES LESS THAN ( 2023-02-01 ) ENGINE = InnoDB,
 PARTITION p202302 VALUES LESS THAN ( 2023-03-01 ) ENGINE = InnoDB,
 PARTITION p202303 VALUES LESS THAN ( 2023-04-01 ) ENGINE = InnoDB,
 PARTITION p202304 VALUES LESS THAN ( 2023-05-01 ) ENGINE = InnoDB,
 PARTITION p202305 VALUES LESS THAN ( 2023-06-01 ) ENGINE = InnoDB,
 PARTITION p202306 VALUES LESS THAN ( 2023-07-01 ) ENGINE = InnoDB);

二、表数据量已经很大，已超过千万方案

1.根据原表创建一张分区新表

SQL如下（示例）：

CREATE TABLE `tb_interface_call_log_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `call_method_type` varchar(10),
  `call_method_uri` varchar(50),
  `call_result` int(1),
  `create_time` datetime(6) NOT NULL,
  `request_time` datetime(6) NOT NULL,
  `request_client_ip` varchar(20),
   PRIMARY KEY (`id`,`request_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PARTITION BY RANGE COLUMNS(request_time) (
      PARTITION P202206 VALUES LESS THAN ( 2022-07-01 ) ENGINE = InnoDB,
      PARTITION P202207 VALUES LESS THAN ( 2022-08-01 ) ENGINE = InnoDB,
      PARTITION P202208 VALUES LESS THAN ( 2022-09-01 ) ENGINE = InnoDB,
      PARTITION P202209 VALUES LESS THAN ( 2022-10-01 ) ENGINE = InnoDB,
      PARTITION P202210 VALUES LESS THAN ( 2022-11-01 ) ENGINE = InnoDB,
      PARTITION P202211 VALUES LESS THAN ( 2022-12-01 ) ENGINE = InnoDB,
      PARTITION P202212 VALUES LESS THAN ( 2023-01-01 ) ENGINE = InnoDB,
      PARTITION p202301 VALUES LESS THAN ( 2023-02-01 ) ENGINE = InnoDB,
      PARTITION p202302 VALUES LESS THAN ( 2023-03-01 ) ENGINE = InnoDB,
      PARTITION p202303 VALUES LESS THAN ( 2023-04-01 ) ENGINE = InnoDB,
      PARTITION p202304 VALUES LESS THAN ( 2023-05-01 ) ENGINE = InnoDB,
      PARTITION p202305 VALUES LESS THAN ( 2023-06-01 ) ENGINE = InnoDB,
      PARTITION p202306 VALUES LESS THAN ( 2023-07-01 ) ENGINE = InnoDB,
      PARTITION p202307 VALUES LESS THAN ( 2023-08-01 ) ENGINE = InnoDB,
      PARTITION p202308 VALUES LESS THAN ( 2023-09-01 ) ENGINE = InnoDB,
      PARTITION p202309 VALUES LESS THAN ( 2023-10-01 ) ENGINE = InnoDB,
      PARTITION p202310 VALUES LESS THAN ( 2023-11-01 ) ENGINE = InnoDB,
      PARTITION p202311 VALUES LESS THAN ( 2023-12-01 ) ENGINE = InnoDB
    );

2.将数据导入到新表

SQL如下（示例）：

`提示：给新表分区字段加一个索引插入很快许多`
#可以采用其他效率更高的工具，这个只是示列
INSERT INTO tb_interface_call_log_new 
SELECT * FROM tb_interface_call_log_new where request_time BETWEEN  2022-06-15 00:00:01  and  2022-07-04 00:00:00 ; 

3.修改表名

SQL如下（示例）：

#修改表名
ALTER TABLE tb_interface_call_log RENAME tb_interface_call_log_bak20220705;
ALTER TABLE tb_interface_call_log_new RENAME tb_interface_call_log;

结束