自动化执行表重组

ALTER TABLE test.customer     SHRINK SPACE FULL;
ALTER TABLE test.lineitem     SHRINK SPACE FULL;
ALTER TABLE test.nation       SHRINK SPACE FULL;
ALTER TABLE test.orders       SHRINK SPACE FULL;
ALTER TABLE test.part         SHRINK SPACE FULL;
ALTER TABLE test.partsupp     SHRINK SPACE FULL;
ALTER TABLE test.region       SHRINK SPACE FULL;
ALTER TABLE test.supplier     SHRINK SPACE FULL;

-- 创建表收缩执行日志表
DROP TABLE IF EXISTS table_shrink_log;
CREATE TABLE table_shrink_log(
   log_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
   exec_time DATETIME DEFAULT NOW() COMMENT '执行时间',
   target_table VARCHAR(128) NOT NULL COMMENT '待收缩表名（库.表）',
   cost_ms INT COMMENT '执行耗时(毫秒)',
   exec_status TINYINT COMMENT '0成功 1失败',
   err_msg VARCHAR(1000) COMMENT '错误详情'
) ENGINE=EXPRESS COMPRESS(5,5) DEFAULT CHARSET utf8;

DROP PROCEDURE IF EXISTS proc_shrink_tpch_table;
DELIMITER //
CREATE PROCEDURE proc_shrink_tpch_table()
BEGIN
   -- 1. test.customer
   SET @tab = 'test.customer';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.customer SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 2. test.lineitem
   SET @tab = 'test.lineitem';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.lineitem SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 3. test.nation
   SET @tab = 'test.nation';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.nation SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 4. test.orders
   SET @tab = 'test.orders';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.orders SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 5. test.part
   SET @tab = 'test.part';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.part SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 6. test.partsupp
   SET @tab = 'test.partsupp';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.partsupp SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;

   -- 7. test.supplier
   SET @tab = 'test.supplier';
   SET @v_start = NOW();
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION
       BEGIN
           SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
           INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
           VALUES(@tab, @v_cost, 1, '执行SHRINK SPACE FULL异常');
       END;
       ALTER TABLE test.supplier SHRINK SPACE FULL;
       SET @v_cost = TIMESTAMPDIFF(MICROSECOND, @v_start, NOW()) / 1000;
       INSERT INTO table_shrink_log(target_table,cost_ms,exec_status,err_msg)
       VALUES(@tab, @v_cost, 0, '');
   END;
END //
DELIMITER ;

-- 定时收缩事件
DROP EVENT IF EXISTS event_shrink_table_MINUTE;
DELIMITER //
CREATE EVENT event_shrink_table_MINUTE
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
DO
BEGIN
   CALL proc_shrink_tpch_table();
END //
DELIMITER ;

SELECT * FROM table_shrink_log ORDER BY exec_time DESC;