mysql数据库按日期简单分区

前段时间学着创建了一个mysql数据库，数据近千万之后性能明显下降，找到了一些简单有效的提速方法，首先是创建索引，其次就是分区表，这里记录一下分区。

一、表格格式

我用的字段名是“日期时间”，类型为datetime。

二、创建分区

看了很多教程都建议以"p+yyyymmyy"命名分区，且因为使用less than，所以创建的分区名日期要比括号里的日期小一天。这样才能保证分区名的日期与分区内的数据日期对应，创建时应该要查看当前数据库内数据的所有日期，每天创建一个分区，不常用的老数据也可以都放到最前面的一个分区里，但是注意命名：

-- 字段名应该是datetime类型，或者date
ALTER TABLE 表名 PARTITION BY RANGE COLUMNS(字段名) (
    PARTITION p20240220 VALUES LESS THAN ('2024-02-21'),
    PARTITION p20240221 VALUES LESS THAN ('2024-02-22'),
    PARTITION p20240222 VALUES LESS THAN ('2024-02-23'),
    PARTITION p20240223 VALUES LESS THAN ('2024-02-24'),
    PARTITION p20240224 VALUES LESS THAN ('2024-02-25'),
    PARTITION p20240225 VALUES LESS THAN ('2024-02-26'),
    PARTITION p20240226 VALUES LESS THAN ('2024-02-27'),
    PARTITION p20240227 VALUES LESS THAN ('2024-02-28'),
    PARTITION p20240228 VALUES LESS THAN ('2024-02-29'),
    PARTITION p20240229 VALUES LESS THAN ('2024-03-01')
);

三、新增分区

后期要添加分区可以使用ADD PARTITION ：

ALTER TABLE 表名 ADD PARTITION (
	PARTITION p20240301 VALUES LESS THAN ('2024-03-02'),
	PARTITION p20240302 VALUES LESS THAN ('2024-03-03'),
	PARTITION p20240303 VALUES LESS THAN ('2024-03-04'),
	PARTITION p20240304 VALUES LESS THAN ('2024-03-05'),
	PARTITION p20240305 VALUES LESS THAN ('2024-03-06')
);

四、查看所有分区

SELECT table_name, partition_name, table_rows 
FROM information_schema.partitions 
WHERE table_schema = '数据库名' AND table_name = '表名';

五、验证分区是否生效

因为使用日期分区，取数据的时候必须加上日期条件，分区才会生效，不加日期就会扫描全表。
验证的方法是随便选个日期，查看执行结果，如果只查询了日期对应的分区就说明生效了。

EXPLAIN 
SELECT * 
FROM 表名
WHERE 日期时间 BETWEEN'2023-12-07' AND'2023-12-10'

-- 随便选个日期，查看执行结果，如果只查询了日期对应的分区就说明生效了。

这里可以看到只查询了选择的日期对应的分区，说明分区生效。