前言

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;

结束