MySQL透明页压缩 TPC 批量取消与磁盘碎片优化实战案例

引言：透明页压缩带来的挑战

MySQL的透明页压缩（Transparent Page Compression，简称TPC）是InnoDB提供的一种数据压缩技术，它可以在页面级别对数据进行压缩，从而减少磁盘空间占用。然而，在生产环境中，我们经常发现TPC会带来一些副作用：

1. 磁盘碎片严重：频繁的压缩和解压操作导致文件系统碎片增加
2. 性能波动：压缩/解压消耗CPU资源，影响查询性能
3. 空间回收困难：即使删除数据，压缩页可能无法完全释放空间

本文将通过一个实际案例，详细介绍如何安全、高效地批量取消TPC，并优化由此产生的磁盘碎片问题。

一、透明页压缩原理与问题分析

1.1 TPC工作原理

-- 创建使用TPC的表CREATE TABLE tpc_table (    id INT PRIMARY KEY,    data VARCHAR(2000)) COMPRESSION='zlib'  -- 启用透明页压缩  KEY_BLOCK_SIZE=8;    -- 指定压缩页大小

TPC在写入时压缩数据页，读取时解压。每个压缩页都附带一个"洞"（hole），通过fallocate()系统调用创建，实现空间节省。

1.2 常见问题症状

-- 检查表空间碎片情况SELECT     TABLE_SCHEMA,    TABLE_NAME,    DATA_LENGTH,    INDEX_LENGTH,    DATA_FREE,    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS fragmentation_percentFROM information_schema.TABLES WHERE DATA_FREE > 1024 * 1024 * 100  -- 大于100MB的碎片ORDER BY DATA_FREE DESCLIMIT 10;

高碎片化会导致：

• 磁盘I/O效率下降
• 备份恢复时间增长
• 磁盘空间虚高

二、实战案例：批量取消TPC压缩

2.1 环境准备与风险评估

案例背景：

• MySQL 8.0.28，InnoDB引擎
• 数据库大小：2TB，其中1.5TB使用TPC
• 磁盘：NVMe SSD，但碎片率超过40%

风险评估清单：

# 1. 检查当前TPC使用情况SELECT     COUNT(*) as tpc_tables,    SUM(DATA_LENGTH/1024/1024/1024) as tpc_size_gbFROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';# 2. 检查InnoDB状态SHOW ENGINE INNODB STATUSG# 3. 监控磁盘空间df -h /var/lib/mysqlls -lh /var/lib/mysql/*.ibd | sort -k5 -h -r | head -20

2.2 批量取消TPC方案设计

方案选择对比：

2.3 分步实施：中小型表在线取消

步骤1：生成批量取消脚本

-- 生成取消压缩的SQL语句SELECT     CONCAT(        'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',        'COMPRESSION="None", ',        'KEY_BLOCK_SIZE=0;'    ) as alter_sql,    ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) as size_mbFROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%'    AND (DATA_LENGTH + INDEX_LENGTH) < 1024 * 1024 * 1024  -- 小于1GB的表ORDER BY size_mb ASC;-- 生成进度监控脚本SELECT     TABLE_SCHEMA,    TABLE_NAME,    'SELECT "正在处理: ' || TABLE_SCHEMA || '.' || TABLE_NAME || '" as status; ' ||    'ALTER TABLE `' || TABLE_SCHEMA || '`.`' || TABLE_NAME || '` COMPRESSION="None", KEY_BLOCK_SIZE=0;' ||    'OPTIMIZE TABLE `' || TABLE_SCHEMA || '`.`' || TABLE_NAME || '`;' as full_processFROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';

步骤2：使用pt-online-schema-change平滑执行

#!/bin/bash# 批量取消TPC的自动化脚本DB_HOST="localhost"DB_USER="admin"DB_PASS="your_password"CHUNK_SIZE="100k"MAX_LOAD="Threads_running=50"# 获取所有TPC表mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -N -e "SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%'    AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')" > tpc_tables.txt# 逐表处理while read table; do    echo "处理表: $table"        pt-online-schema-change         --host=${DB_HOST}         --user=${DB_USER}         --password=${DB_PASS}         --alter="COMPRESSION='None', KEY_BLOCK_SIZE=0"         --chunk-size=${CHUNK_SIZE}         --max-load=${MAX_LOAD}         --execute         D=${table%.*},t=${table#*.}        # 记录日志    echo "$(date): 已处理 $table" >> tpc_remove.log        # 暂停60秒，避免对主库影响过大    sleep 60done < tpc_tables.txt

2.4 大型表的特殊处理方案

对于超过100GB的大型表，我们采用表空间传输方案：

-- 1. 创建目标表结构（无压缩）CREATE TABLE orders_new LIKE orders;ALTER TABLE orders_new COMPRESSION='None', KEY_BLOCK_SIZE=0;-- 2. 丢弃目标表空间ALTER TABLE orders_new DISCARD TABLESPACE;-- 3. 使用Percona工具复制表空间文件# 在操作系统层面执行sudo innobackupex --compress --export /backup/orders/sudo cp /backup/orders/orders.ibd /var/lib/mysql/mydb/orders_new.ibdsudo cp /backup/orders/orders.cfg /var/lib/mysql/mydb/orders_new.cfg-- 4. 导入表空间ALTER TABLE orders_new IMPORT TABLESPACE;-- 5. 验证数据一致性CHECK TABLE orders_new EXTENDED;-- 6. 原子切换（在维护窗口进行）RENAME TABLE orders TO orders_old, orders_new TO orders;-- 7. 清理旧表（确认业务正常后）DROP TABLE orders_old;

三、磁盘碎片优化与空间回收

3.1 碎片检测与评估

# 使用filefrag检查物理碎片sudo filefrag /var/lib/mysql/mydb/*.ibd | grep "extents found"# MySQL内部碎片统计SELECT     TABLE_NAME,    ENGINE,    TABLE_ROWS,    DATA_LENGTH,    INDEX_LENGTH,    DATA_FREE,    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS frag_ratioFROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb'    AND DATA_FREE > 1024 * 1024 * 10  -- 10MB以上碎片ORDER BY frag_ratio DESC;

3.2 优化策略组合拳

策略1：OPTIMIZE TABLE（需要停机时间）

-- 针对碎片率超过30%的表SET SESSION old_alter_table=1;  -- 使用旧算法，减少内存使用SELECT     CONCAT('OPTIMIZE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') as optimize_cmdFROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb'    AND ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) > 30    AND TABLE_ROWS > 1000000;

策略2：分批重建索引（在线操作）

-- 针对索引碎片SELECT     TABLE_SCHEMA,    TABLE_NAME,    INDEX_NAME,    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS index_size_mbFROM mysql.innodb_index_stats WHERE STAT_NAME = 'size'    AND DATABASE_NAME = 'mydb'ORDER BY STAT_VALUE DESCLIMIT 20;-- 分批重建大索引ALTER TABLE large_table DROP KEY idx_large, ADD KEY idx_large(column1, column2);-- 使用ALGORITHM=INPLACE, LOCK=NONE在线重建

策略3：使用innodb_defragment在线整理

-- 启用InnoDB碎片整理SET GLOBAL innodb_defragment=1;SET GLOBAL innodb_defragment_n_pages=7;SET GLOBAL innodb_defragment_stats_accuracy=0;-- 监控整理进度SELECT * FROM information_schema.INNODB_DEFRAG;

3.3 自动化维护脚本

#!/usr/bin/env python3"""MySQL TPC取消与碎片整理自动化脚本"""import pymysqlimport subprocessimport loggingfrom datetime import datetimeclass MySQLTPCOptimizer:    def __init__(self, host, user, password):        self.conn = pymysql.connect(            host=host,            user=user,            password=password,            charset='utf8mb4'        )        self.logger = self.setup_logger()            def setup_logger(self):        logging.basicConfig(            level=logging.INFO,            format='%(asctime)s - %(levelname)s - %(message)s',            handlers=[                logging.FileHandler('mysql_tpc_optimization.log'),                logging.StreamHandler()            ]        )        return logging.getLogger(__name__)        def get_tpc_tables(self, min_size_mb=100):        """获取使用TPC的表"""        sql = """        SELECT             TABLE_SCHEMA,            TABLE_NAME,            ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as size_mb,            CREATE_OPTIONS        FROM information_schema.TABLES         WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%'            AND (DATA_LENGTH + INDEX_LENGTH) > %s * 1024 * 1024        ORDER BY size_mb DESC        """                with self.conn.cursor() as cursor:            cursor.execute(sql, (min_size_mb,))            return cursor.fetchall()        def estimate_operation_time(self, table_size_mb):        """估算操作时间（经验公式）"""        # 导出导入：约 50 MB/s        # 在线ALTER：约 20 MB/s        export_time = table_size_mb / 50        alter_time = table_size_mb / 20                return {            'export_import': export_time * 2,  # 导出+导入            'online_alter': alter_time,            'recommended': 'export_import' if table_size_mb > 10240 else 'online_alter'        }        def batch_remove_tpc(self, batch_size=5):        """批量取消TPC"""        tables = self.get_tpc_tables()                for i in range(0, len(tables), batch_size):            batch = tables[i:i+batch_size]            self.logger.info(f"处理批次 {i//batch_size + 1}: {len(batch)}张表")                        for schema, table, size_mb, _ in batch:                try:                    self.logger.info(f"开始处理 {schema}.{table} ({size_mb}MB)")                                        # 根据大小选择策略                    if size_mb > 10240:  # 大于10GB                        self.handle_large_table(schema, table)                    else:                        self.handle_medium_table(schema, table)                                            self.logger.info(f"完成处理 {schema}.{table}")                                    except Exception as e:                    self.logger.error(f"处理 {schema}.{table} 失败: {str(e)}")                    continue        def optimize_fragmentation(self, frag_threshold=20):        """优化碎片化严重的表"""        sql = """        SELECT             TABLE_SCHEMA,            TABLE_NAME,            ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS frag_ratio        FROM information_schema.TABLES         WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema')            AND DATA_FREE > 50 * 1024 * 1024  -- 大于50MB            AND ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) > %s        ORDER BY frag_ratio DESC        """                with self.conn.cursor() as cursor:            cursor.execute(sql, (frag_threshold,))            fragmented_tables = cursor.fetchall()                        for schema, table, frag_ratio in fragmented_tables:                self.logger.info(f"优化碎片表 {schema}.{table} (碎片率: {frag_ratio}%)")                                # 使用OPTIMIZE TABLE                optimize_sql = f"OPTIMIZE TABLE `{schema}`.`{table}`"                cursor.execute(optimize_sql)                result = cursor.fetchone()                                self.logger.info(f"优化结果: {result}")if __name__ == "__main__":    optimizer = MySQLTPCOptimizer(        host="localhost",        user="admin",        password="your_password"    )        # 执行TPC取消    optimizer.batch_remove_tpc(batch_size=3)        # 执行碎片整理    optimizer.optimize_fragmentation(frag_threshold=25)

四、监控与验证

4.1 监控指标设计

-- 监控视图：TPC取消进度CREATE VIEW tpc_removal_progress ASSELECT     'before' as period,    COUNT(*) as table_count,    SUM(DATA_LENGTH + INDEX_LENGTH) as total_sizeFROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%'UNION ALLSELECT     'after',    COUNT(*),    SUM(DATA_LENGTH + INDEX_LENGTH)FROM information_schema.TABLES WHERE CREATE_OPTIONS NOT LIKE '%COMPRESSION=%'     OR CREATE_OPTIONS IS NULL;-- 磁盘空间监控CREATE VIEW disk_usage_trend ASSELECT     DATE(create_time) as date,    SUM(CASE WHEN CREATE_OPTIONS LIKE '%COMPRESSION=%' THEN 1 ELSE 0 END) as tpc_tables,    SUM(CASE WHEN CREATE_OPTIONS LIKE '%COMPRESSION=%' THEN DATA_LENGTH + INDEX_LENGTH ELSE 0 END) / 1024 / 1024 / 1024 as tpc_size_gb,    AVG(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100 as avg_frag_percentFROM information_schema.TABLES     CROSS JOIN (SELECT NOW() as create_time) as tWHERE TABLE_SCHEMA = 'mydb'GROUP BY DATE(create_time);

4.2 性能对比测试

-- 测试查询性能变化SELECT     'before_optimization' as phase,    AVG(query_time) as avg_query_time,    MAX(query_time) as max_query_time,    COUNT(*) as query_countFROM mysql.slow_log WHERE db = 'mydb'    AND start_time < '2024-01-15'UNION ALLSELECT     'after_optimization',    AVG(query_time),    MAX(query_time),    COUNT(*)FROM mysql.slow_log WHERE db = 'mydb'    AND start_time >= '2024-01-15';-- I/O性能监控SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

五、经验总结与最佳实践

5.1 关键经验总结

1. 分批处理：不要一次性处理所有表，按大小分批次
2. 监控先行：执行前建立完整监控基线
3. 回滚预案：随时准备停止或回滚
4. 业务影响评估：与业务团队充分沟通时间窗口

5.2 TPC使用建议

适合使用TPC的场景：

• 只读或读多写少的表
• SSD存储成本敏感的环境
• 数据归档表

不适合使用TPC的场景：

• 高频更新的OLTP表
• 内存充足，追求极致性能
• 已经使用其他压缩方案（如InnoDB表压缩）

5.3 长期维护策略

-- 定期碎片检查任务CREATE EVENT check_fragmentationON SCHEDULE EVERY 1 WEEKSTARTS CURRENT_TIMESTAMPDOBEGIN    -- 记录碎片状态    INSERT INTO frag_monitor_history    SELECT NOW(), TABLE_SCHEMA, TABLE_NAME,           ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2)    FROM information_schema.TABLES     WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys')        AND DATA_FREE > 100 * 1024 * 1024;        -- 自动优化高碎片表    CALL auto_optimize_fragmented_tables(30); -- 30%阈值END;

六、附录：常用命令速查

# 1. 检查表压缩状态mysql -e "SHOW TABLE STATUS WHERE Comment LIKE '%Compressed%'G"# 2. 检查文件系统碎片sudo filefrag -v /var/lib/mysql/dbname/*.ibd | grep "extent"# 3. 快速估算表大小SELECT     table_name AS `Table`,    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`FROM information_schema.TABLESWHERE table_schema = "your_database"ORDER BY (data_length + index_length) DESC;# 4. 监控ALTER进度（MySQL 8.0+）SELECT * FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

总结