前言
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;
结束
