前言
在使用mysql时,往往用一张表来存放数据,不同的业务可能产生的数据量也不同,有的业务可能一天需要插入几万条数据,也有的则更多,即使量很小日积月累数据库表的数据堆积也会越来越多,需要人工去删除数据,当数据达到百万或千万级别时,mysql查询速度明显下降,性能也随之下降,解决该问题的办法有很多,比如添加索引、优化SQL,但这都只是治标不治本的做法,无法从根本上提高Mysql的性能和查询速度。
什么是表分区?
mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。
表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。
创建测试表
CREATE TABLE `dcdlqx` (
`u_time` int(11) NOT NULL COMMENT '采集时间',
`zh_order` tinyint(4) NOT NULL COMMENT '道岔方向',
KEY `index_utime` (`u_time`) USING BTREE
) ENGINE=MyISAM CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='道岔电流曲线';
手动进行分区
对自动分区的表必须是在该表有手动分区的前提之下才能进行; 对以下代码稍做解释,less than 的意思是小于的意思,所以p20230919这个分区里面存放的数据都是比2023年09月19日之前的数据,以此类推,p20230920存放的是09月20日的数据。
命令说明#单条增加分区
ALTER TABLE dcdlqx ADD PARTITION (PARTITION p20230919 VALUES LESS THAN(UNIX_TIMESTAMP('2023-09-19')));
命令说明#删除分区命令
ALTER TABLE dcdlqx DROP PARTITION p20230919;
命令说明#查看表分区
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='dcdlqx';
PARTITION_NAME PARTITION_DESCRIPTION
p20230918 1695052800
p20230919 1695139200
分区维护#自动按天分表函数
说明:该函数可以按天新增分区,删除历史分区(保留指定天数的分区,超出部分自动删除)
DROP PROCEDURE IF EXISTS proc_partiontable_byday;
DELIMITER $$
CREATE PROCEDURE `proc_partiontable_byday`(
IN tablename VARCHAR (50), -- 表名称
IN keepday INT, -- 分区保留天数
OUT msg VARCHAR (100)) -- 输出日志 @msg
BEGIN
DECLARE pMinValue INT DEFAULT UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL keepday DAY));-- 在这时间之前的分区需要删除
DECLARE ptoday VARCHAR(9) DEFAULT CONCAT('p',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY), '%Y%m%d'));-- 今天分区名称
DECLARE ptomorrow VARCHAR(9) DEFAULT CONCAT('p',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 DAY), '%Y%m%d'));-- 明天分区名称
DECLARE pQMinValue INT DEFAULT UNIX_TIMESTAMP();-- 查得最早分区时间
DECLARE pQMinName VARCHAR(9) DEFAULT '';-- 查得最早分区名称
DECLARE pQtoday VARCHAR(9) DEFAULT '';-- 查得今日分区
DECLARE pQtomorrow VARCHAR(9) DEFAULT '';-- 查得明日分区
SELECT
MIN(PARTITION_DESCRIPTION),
MAX(CASE PARTITION_ORDINAL_POSITION WHEN 1 THEN PARTITION_NAME ELSE '' END),
MAX(CASE PARTITION_NAME WHEN ptoday THEN PARTITION_NAME ELSE '' END),
MAX(CASE PARTITION_NAME WHEN ptomorrow THEN PARTITION_NAME ELSE '' END )
INTO pQMinValue,pQMinName,pQtoday,pQtomorrow
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME=tablename;
SET msg = CONCAT('分区维护:',tablename,'[');
-- 创建今日分区
IF pQtoday = ptoday THEN
SET msg=CONCAT(msg,'已存在:',ptoday);
ELSE
SET @psql = CONCAT('ALTER TABLE ',tablename ,' ADD PARTITION(PARTITION ',ptoday, ' VALUES LESS THAN(',UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 1 DAY)), '));');
PREPARE stmt from @psql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET msg=CONCAT(msg,'已创建:',ptoday);
END IF;
-- 创建明日分区
IF pQtomorrow = ptomorrow THEN
SET msg=CONCAT(msg,'已存在:',ptomorrow);
ELSE
SET @psql = CONCAT('ALTER TABLE ',tablename ,' ADD PARTITION(PARTITION ',ptomorrow, ' VALUES LESS THAN(',UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 2 DAY)), '));');
PREPARE stmt from @psql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET msg=CONCAT(msg,'已创建:',ptomorrow);
END IF;
-- 删除历史分区
IF pQMinValue < pMinValue THEN
SET @psql = CONCAT('ALTER TABLE ', tablename, ' DROP PARTITION ',pQMinName,';');
PREPARE stmt from @psql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET msg=CONCAT(msg,'删除分区:',pQMinName);
END IF;
SET msg=CONCAT(msg,']');
END
$$
DELIMITER;
分区维护#自动按天分表事件
DROP EVENT Event_partiontable_byday;
SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE EVENT IF NOT EXISTS Event_partiontable_byday
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP -- 每1天执行一次检查分区
ON COMPLETION PRESERVE
DO
BEGIN
CALL proc_partiontable_byday('dcdlqx',3,@msg);
SELECT @msg;
END
$$
DELIMITER ;