MySQL表碎片整理

-- 基本用法
OPTIMIZE TABLE table_name;

-- 整理多个表
OPTIMIZE TABLE table1, table2, table3;

-- 实际示例
OPTIMIZE TABLE users, orders, products;

-- 方法1：简单重建
ALTER TABLE table_name ENGINE=InnoDB;

-- 方法2：带字符集重建（保持原字符集）
ALTER TABLE table_name ENGINE=InnoDB CHARACTER SET utf8mb4;

-- 实际示例
ALTER TABLE large_table ENGINE=InnoDB;

-- 使用 OPTIMIZE TABLE（推荐）
OPTIMIZE TABLE innodb_table;

-- 或者使用 ALTER TABLE
ALTER TABLE innodb_table FORCE;

-- 在线 DDL（MySQL 5.6+）
ALTER TABLE innodb_table ALGORITHM=INPLACE, LOCK=NONE;

-- OPTIMIZE TABLE
OPTIMIZE TABLE myisam_table;

-- 或者使用 REPAIR TABLE
REPAIR TABLE myisam_table;

-- 带选项的修复
REPAIR TABLE myisam_table QUICK;

-- 整理所有表的存储过程
DELIMITER $$
CREATE PROCEDURE optimize_all_tables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE()
        AND table_type = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('OPTIMIZE TABLE `', tbl_name, '`');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

-- 调用存储过程
CALL optimize_all_tables();

# 使用 mysqlcheck 工具
mysqlcheck -o database_name table_name
mysqlcheck -o database_name  # 整理整个数据库

# 整理所有数据库
mysqlcheck -o --all-databases

# 带连接参数
mysqlcheck -o -u username -p database_name

#!/bin/bash

# 数据库连接信息
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"

# 整理单个数据库
mysqlcheck -h $DB_HOST -u $DB_USER -p$DB_PASS -o $DB_NAME

# 或者整理特定表
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "OPTIMIZE TABLE table1, table2;" $DB_NAME

-- 查看表碎片情况
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE,
    ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100, 2) AS fragment_percent
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_FREE > 0
ORDER BY fragment_percent DESC;