mysql分区入门

创建分区

ALTER TABLE test
PARTITION by RANGE COLUMNS(time)
(	
	PARTITION p01 VALUES less than ('2021-12-18 22:16:17'),
	PARTITION p02 VALUES less than ('2021-12-18 22:36:17'),
	PARTITION p03 VALUES less than ('2021-12-18 22:46:17'),
	PARTITION p04 VALUES less than (MAXVALUE)
)

查看test表格的所有分区

select PARTITION_NAME,TABLE_ROWS,PARTITION_DESCRIPTION 
from information_schema.PARTITIONS
WHERE TABLE_NAME ='test'

新增分区，删除分区，合并分区

-- range分区,分区字段必须是整型或者转换为整型
-- 按照字段的区间划分数据的归属，典型的就是按照时间维度的月份分区
ALTER TABLE `table_name` ADD PARTITION(PARTITION p20190107 VALUES LESS THAN (20190107) ENGINE = InnoDB);

-- List分区，分区字段必须是整型或者转换为整型
-- 按照某个字段上的规则，不同的数据离散地分布在不同的区中
ALTER TABLE `table_name` ADD PARTITION(PARTITION p20190107 VALUES IN (20190107) ENGINE = InnoDB);

-- 删除分区
ALTER TABLE `table_name` DROP PARTITION p20190107;

-- 合并分区
ALTER TABLE `table_name` REORGANIZE PARTITION p1,p2 INTO (PARTITION p4 VALUES less than (20190105) );

查询库的内存

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
	sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema 
ORDER BY
	sum( data_length ) DESC,
	sum( index_length ) DESC;

每日定时增加分区

• 声明procedure

DELIMITER $$

USE `Hydropower_Sensor_2021`$$

DROP PROCEDURE IF EXISTS `pro_add_test_patrtition`$$

CREATE PROCEDURE `pro_add_test_patrtition`()
BEGIN
/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
-- 开启事务
    START TRANSACTION;
/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    SELECT STR_TO_DATE(max(PARTITION_DESCRIPTION),'\'%Y-%m-%d %H:%i:%s\'') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name='test' ;
    SET @Max_date= DATE_ADD(@P12_Name, INTERVAL 1 DAY);
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
		SET @P12_Name = date_format(@Max_date, 'p%Y%m%d');  
    SET @s1=CONCAT('ALTER TABLE test ADD PARTITION (PARTITION ',@P12_Name,' VALUES LESS THAN (\'',@Max_date,'\'))');
    /* 输出查看增加分区语句*/
    SELECT @s1;
    PREPARE stmt2 FROM @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
/* 提交 */
    COMMIT ;
 END$$

DELIMITER ;


call pro_add_test_patrtition()

• 设置自动