MySQL空间管理：查询、优化与碎片清理

1. 查询 MySQL 表空间和磁盘碎片

查询表空间使用情况

使用以下 SQL 语句可以查看数据库中各个表的表空间使用情况，包括数据大小、索引大小和空闲空间（碎片）：

SELECT 
    table_schema AS `Database`, 
    table_name AS `Table`, 
    ROUND(data_length / 1024 / 1024, 2) AS `Data Size (MB)`, 
    ROUND(index_length / 1024 / 1024, 2) AS `Index Size (MB)`, 
    ROUND(data_free / 1024 / 1024, 2) AS `Free Space (MB)`
FROM 
    information_schema.tables
WHERE 
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY 
    data_length + index_length DESC;

分析磁盘碎片

通过检查 data_free 列的值，可以判断表中是否存在碎片。如果 data_free 值较大，意味着表中存在未使用的空间，即磁盘碎片。

2. 优化表空间和清理磁盘碎片

使用 OPTIMIZE TABLE 命令可以优化表空间，清理磁盘碎片。这会重新组织表的数据并回收未使用的空间：

OPTIMIZE TABLE your_table_name;

如果想要对整个数据库中的所有表进行优化，可以使用如下 SQL 脚本：

SET @tables = NULL;
SELECT GROUP_CONCAT(table_name) INTO @tables
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' AND table_type = 'BASE TABLE';

SET @tables = CONCAT('OPTIMIZE TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 表空间和磁盘碎片分析

在数据库存在大量数据插入和删除操作时，表的碎片可能会逐渐增多。定期分析表空间和碎片是必要的。分析结果可以帮助确定哪些表需要优化。

可以根据 data_free 列的值来评估碎片情况，或者使用 SHOW TABLE STATUS 命令查看特定表的碎片和空间使用情况：

SHOW TABLE STATUS LIKE 'your_table_name';

4. 自动清理碎片

可以使用 innodb_file_per_table 选项来使每个表都有独立的表空间，从而减少表空间碎片的产生。确保在 MySQL 配置文件 (my.cnf 或 my.ini) 中启用该选项：

[mysqld]
innodb_file_per_table=1

5. 使用 Shell 脚本定期清理表空间和磁盘碎片

使用 Shell 脚本定期清理 MySQL 表空间和磁盘碎片的示例脚本。这个脚本会查找所有表并执行 OPTIMIZE TABLE 操作。

Shell 脚本

#!/bin/bash

# MySQL 登录信息
MYSQL_USER="mysql_user"
MYSQL_PASSWORD="mysql_password"
MYSQL_HOST="localhost"
MYSQL_DATABASE="database_name"

# 获取所有表名
TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -D$MYSQL_DATABASE -e "SHOW TABLES;" | awk '{ print $1}' | grep -v '^Tables')

# 对每个表执行 OPTIMIZE TABLE
for TABLE in $TABLES; do
    echo "Optimizing table: $TABLE"
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -D$MYSQL_DATABASE -e "OPTIMIZE TABLE $TABLE;"
done

echo "Table optimization complete."

exit 0

总结

定期分析和优化 MySQL 表空间，清理磁盘碎片，从而保持数据库的高效运行。Shell 脚本的自动化处理可以减少手动维护的负担，确保数据库始终处于最佳状态。