mysql数据量过大，选择分表或分区

1. 水平分表：是对某张表同结构拆分多个表存储数据，对程序逻辑处理稍微麻烦，尤其项目后期优化或改造。例如order 分order_2022 order_2023

　　如果使用分表，可以用merge分表方式。 　　缺点：（总表（MERGE表）必须使用MRG_MyISAM存储引擎，子表必须使用MyISAM存储引擎，受到存储引擎的限制）

2.分区：同一张表 分散存储不同磁盘上，来减轻单表数据量访问的压力。变动小，逻辑相对清晰，交给mysql处理。更适用于历史数据很少查

　　可以选择KEY分区、范围分区、List（预定义列表）； 缺点：1必须唯一，2删除分区（drop）会删除该分区的数据，要特别小心

 

1. 针对本次项目做个记录，我选择 范围分区(create_time时间段 每年分区一次)

　　ALTER TABLE `racoon_order` PARTITION BY RANGE (year(create_time))
   (
        PARTITION p1 VALUES LESS THAN (2021) ,
        PARTITION p2 VALUES LESS THAN (2022) ,
        PARTITION p3 VALUES LESS THAN (2023) ,
        PARTITION p4 VALUES LESS THAN (2024) ,
        PARTITION p5 VALUES LESS THAN (2025) ,
        PARTITION p6 VALUES LESS THAN (2026),
        PARTITION p7 VALUES LESS THAN (2027),
        PARTITION p8 VALUES LESS THAN (2028),
        PARTITION pm VALUES LESS THAN MAXVALUE
    );
提示： 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

注意：使用range分区时表结构要么没有主键，要么分区字段必须包含主键。可以使用PRIMARY KEY (id,xxx) 来将多个字段作为主键。这个字段加入到主键中做为复合主键是否适合

2. 添加过程

每3个月自动添加分区。

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_partition_mytest`$$

CREATE PROCEDURE `add_partition_mytest`()
BEGIN
START TRANSACTION;

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name='mytest' ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 3 MONTH))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
SET @s1=CONCAT('ALTER TABLE mytest ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 输出查看增加分区语句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 提交 */
COMMIT ;
END$$

DELIMITER ;

其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"。
默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。
因为mysql一遇到分号，它就要自动执行。
即，在语句RETURN '';时，mysql解释器就要执行了。
这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

 

七、添加事件
每3个月自动添加分区。

DELIMITER ||
CREATE EVENT add_event_mytest
ON SCHEDULE
EVERY 3 month STARTS '2022-03-10 03:00:00'
DO
BEGIN
CALL add_partition_mytest();
END ||
DELIMITER ;
查询mysql事件调度器是否开启

/* 查看定时器开关情况 */
show VARIABLES like 'event_scheduler';
/* 开启定时器开关 */
set GLOBAL event_scheduler = 1;

八、查看分区情况
SELECT partition_name, partition_ordinal_position, TABLE_Rows, table_schema
FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name = 'tl_alarm_data'
ORDER BY partition_ordinal_position DESC