数据库表碎片率定时优化策略

数据库表碎片率定时优化策略

1、在每台RDS实例上创建库、表、存储过程函数

• 创建库——dbadminplat

create DATABASE dbadminplat;

•  创建表——table_optimizelog

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