数据库表碎片率定时优化策略
1、在每台RDS实例上创建库、表、存储过程函数
create DATABASE dbadminplat;
CREATE TABLE `dbadminplat.table_optimizelog` (
`nID` bigint(20) NOT NULL AUTO_INCREMENT,
`table_catalog` varchar(512) DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`table_name` varchar(64) DEFAULT NULL,
`engine` varchar(64) DEFAULT NULL,
`TABLE_ROWS` bigint(20) NOT NULL DEFAULT '0',
`DATA_LENGTH` bigint(20) NOT NULL DEFAULT '0',
`INDEX_LENGTH` bigint(20) NOT NULL DEFAULT '0',
`DATA_FREE` bigint(20) NOT NULL DEFAULT '0',
`total_size` bigint(20) NOT NULL DEFAULT '0',
`frag_percent` decimal(10,2) NOT NULL DEFAULT '0.00',
`dtCreateTime` datetime DEFAULT CURRENT_TIMESTAMP,
`isOptimized` tinyint(4) NOT NULL DEFAULT '0',
`dtOptimizeStartTime` datetime DEFAULT NULL,
`dtOptimizeEndTime` datetime DEFAULT NULL,
PRIMARY KEY (`nID`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8;
-
创建存储过程函数(无参数PROCEDURE类型)——p_dba_optimizetable
BEGIN
DECLARE vLastOptimizeDate DATETIME;
DECLARE vnTheID BIGINT;
DECLARE vTableSchema VARCHAR(64);
DECLARE vTableName VARCHAR(64);
DECLARE i INT DEFAULT 1;
DECLARE nRows INT DEFAULT 0;
DECLARE vdtStartTime DATETIME;
DECLARE vdtEndTime DATETIME;
DECLARE vdtCreateTime DATETIME;
DECLARE vcSQL VARCHAR(1000);
DECLARE nDays INT DEFAULT (30);
-- 找出未优化的表数据的最近创建时间：
SELECT MAX(dtCreateTime) into vdtCreateTime FROM table_OptimizeLog WHERE isOptimized = 0;
-- 不存在未优化的表，则找出最近优化表的时间：
IF (vdtCreateTime IS NULL) THEN
SELECT MAX(dtCreateTime) into vLastOptimizeDate FROM table_OptimizeLog WHERE isOptimized = 1;
END IF;
IF (vdtCreateTime IS NULL) AND (vLastOptimizeDate IS NULL) THEN
SET vLastOptimizeDate = DATE_ADD(NOW(), interval -nDays day);
END IF;
-- 最近优化表的时间为30天之前，才开始下一轮优化：
IF (DATEDIFF(NOW(),vLastOptimizeDate) >= nDays) THEN
insert into table_OptimizeLog(table_catalog,TABLE_SCHEMA,table_name,engine, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH,DATA_FREE,total_size,frag_percent)
select table_catalog,TABLE_SCHEMA,table_name, engine, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH,DATA_FREE
, DATA_LENGTH + INDEX_LENGTH + DATA_FREE total_size
, DATA_FREE/(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) frag_percent
from information_schema.`TABLES`
where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema') AND DATA_FREE/(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) >= 0.2
order by total_size desc;
-- 再次获取未优化的表数据的最近创建时间：
SELECT MAX(dtCreateTime) into vdtCreateTime FROM table_OptimizeLog WHERE isOptimized = 0;
END IF;
-- SELECT vLastOptimizeDate, vdtCreateTime;
-- 只有存在未执行优化的表，才开始本轮优化：
IF (vdtCreateTime IS NOT NULL) THEN
DROP TABLE IF EXISTS tt;
CREATE TEMPORARY TABLE tt(
nID BIGINT AUTO_INCREMENT PRIMARY KEY,
nTheID BIGINT,
TABLE_SCHEMA VARCHAR(64),
table_name VARCHAR(64)
);
INSERT INTO tt(nTheID, TABLE_SCHEMA, table_name)
SELECT nID, TABLE_SCHEMA, table_name FROM table_OptimizeLog WHERE isOptimized = 0 AND dtCreateTime = vdtCreateTime ORDER BY total_size desc;
SELECT MAX(nID) into nRows FROM tt;
SELECT nRows, i;
LAB: WHILE i <= nRows DO
/* UPDATE table_OptimizeLog
SET dtOptimizeStartTime = NOW(), isOptimized = -1
WHERE nID = vnTheID;*/
-- 如果距离早上8点相差不到30分钟，则退出:
IF (TIMESTAMPDIFF(MINUTE,CURRENT_TIMESTAMP(),CONCAT(CURRENT_DATE(),' 08:00:00'))) < 30 THEN
-- SELECT 1;
LEAVE lab;
END IF;
SELECT nTheID, TABLE_SCHEMA, table_name INTO vnTheID, vTableSchema, vTableName
FROM tt
WHERE nID = i;
SET vdtStartTime = NOW();
SELECT vnTheID, vTableSchema, vTableName;
SET @vcSQL = CONCAT('OPTIMIZE LOCAL TABLE `',vTableSchema, '`.`', vTableName,'`;');
PREPARE vcSQL FROM @vcSQL;
EXECUTE vcSQL;
DEALLOCATE PREPARE vcSQL;
SET vdtEndTime = NOW();
UPDATE table_OptimizeLog
SET dtOptimizeStartTime = vdtStartTime, dtOptimizeEndTime = vdtEndTime, isOptimized = 1
WHERE nID = vnTheID;
SET i = i + 1;
END WHILE LAB;
END IF;
END
2、在Linux服务器上创建SQL脚本及执行语句
SQL脚本——/home/sunli/table/table.sql
call dbadminplat.p_dba_optimizetable;
shell脚本——/home/sunli/table/table.sh
#!/bin/bash
echo "RDS-xxx-表优化开始时间:`date`"
/bin/mysql -h xxx.xxx.xxx.xxx -u root -P 3306 -p 'password' < /home/sunli/table/table.sql
echo "RDS-xxx-表优化结束时间:`date`"
创建执行计划（每天凌晨6点执行）crontab -e
00 06 * * * /bin/bash /home/sunli/table/table.sh >> /home/sunli/table/table.log 2>&1
