MySQL杂记2020.10.22
分区表自动增加/删除分区MySQL查询表碎片
分区表自动增加/删除分区
增加分区的存储过程
DELIMITER $$
CREATE DEFINER=`root
`@`%`
PROCEDURE `proc_create_paratition
`(IN `tableName
` VARCHAR(128) CHARSET utf8
,
IN `timeColName
` VARCHAR(128) CHARSET utf8
)
BEGIN
DECLARE p_id
int;
DECLARE nextDate
date;
DECLARE lastDate
date;
DECLARE p_name
varchar(16);
SELECT COUNT(partition_name
) INTO p_id
FROM INFORMATION_SCHEMA
.partitions
WHERE TABLE_NAME
=tableName
;
IF p_id
=0 THEN
SELECT DATE_ADD
(CURDATE
()-DAY(CURDATE
())+1,INTERVAL 1 MONTH) INTO nextDate
FROM DUAL
;
SET p_name
=DATE_FORMAT
(CURDATE
(),'%Y%m');
SET @v_add=CONCAT
('ALTER table ',tableName
,' PARTITION by range('
, timeColName
,')(partition '
, CONCAT
('p',p_name
)
,' values less than '
, nextDate
,'))');
ELSE
SELECT STR_TO_DATE
(replace(max(partition_description
),'''',''),'%Y-%m-%d') des
INTO lastDate
FROM INFORMATION_SCHEMA
.partitions
WHERE TABLE_NAME
=tableName
;
SELECT DATE_ADD
(lastDate
,INTERVAL 1 MONTH) INTO nextDate
FROM DUAL
;
SET p_name
=DATE_FORMAT
(lastDate
,'%Y%m');
SET @v_add=CONCAT
('alter table ',tableName
,' add partition (partition '
, CONCAT
('p',p_name
)
,' values less than ("'
,nextDate
,'"))');
END IF;
PREPARE stmt
from @v_add;
EXECUTE stmt
;
DEALLOCATE PREPARE stmt
;
END $$
DELIMITER;
删除分区的存储过程
DELIMITER $$
CREATE DEFINER=`root
`@`%`
PROCEDURE `proc_drop_paratition
`(IN `tableName
` VARCHAR(128) CHARSET utf8
)
BEGIN
DECLARE p_id
int;
DECLARE p_name
varchar(16);
SELECT COUNT(partition_name
) into p_id
FROM INFORMATION_SCHEMA
.PARTITIONS
WHERE TABLE_NAME
=tableName
;
IF p_id
> 2 THEN
SELECT PARTITION_NAME
into p_name
FROM INFORMATION_SCHEMA
.PARTITIONS
WHERE TABLE_NAME
=tableName
and TIMESTAMPDIFF
(MONTH,replace(partition_description
,'''',''), DATE_FORMAT
(now(), '%Y-%m-%d')) > 1
ORDER BY partition_ordinal_position
LIMIT 1;
IF p_name
is not null THEN
SET @v_add=CONCAT
('alter table ',tableName
,' drop partition ',p_name
);
PREPARE stmt
from @v_add;
EXECUTE stmt
;
DEALLOCATE PREPARE stmt
;
END IF;
END IF;
END $$
DELIMITER;
剩下的就是JOB的创建和调用了，这里暂时省略
MySQL查询表碎片
select table_schema
,concat
('alter table ',table_name
, ' engine=Innodb ;'),(data_length
+index_length
)/1024/1024 length
,engine,data_free
,table_rows
from information_schema
.tables where table_schema
not in ('information_schema','mysql','performance_schema') and data_free
!=0 group by table_name
order by table_rows
asc;
