MySQL表空间碎片怎么查？一文教你清理磁盘碎片_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