问题:
在 MySQL 中,如果表中删除大量数据或者进行了重建等操作,但是表的数据和索引文件并未减小,这是因为在删除数据时,会造成碎片空间。这种碎片空间会加大存储代价,同时也会降低表的扫描效率。
解决:根据数据表引擎不同,使用不同的命令解决。
InnoDB引擎:【alter table “tableName”】
【alter table】 命令可以对表进行多种操作,其中包括对表进行重建和复制等操作。通过重建表可以清除表中的空间碎片,并将数据整理到连续的磁盘块中,从而释放表空间。
MyISAM引擎:【optimize table “tableName”】
【optimize table】命令,会对表进行优化和整理,包括重建索引、去除碎片等操作,从而释放表空间。该命令的执行需要消耗一定的时间和系统资源,因此建议在低峰期进行操作。
以下是基于php的清理脚本,可以直接不分内容后执行使用:
//清理空间时间较长,设置程序执行最大时间
set_time_limit(0);
//设置程序执行内存
ini_set('memory_limit', '-1');
//查询指定数据库中含有碎片空间的,表名,空间大小,引擎,数据大小,每条数据大小,数据条数
$d_sql = "select table_name, data_free, engine, Data_length, Avg_row_length, table_rows
from information_schema.tables
where table_schema not in ('information_schema', 'mysql') and data_free > 0 and table_schema = '数据库名称' ";
$db_list = DB::select($d_sql);
//循环清理每个表的碎片空间
foreach ($db_list as $key => $value) {
//实际存在的碎片空间
$garbage = round(($value['Data_length'] - ($value['Avg_row_length'] * $value['table_rows']))/1024/1024, 2);
//var_dump('数据表 ' . $value['table_name'] . ' 碎片空间 ' . $garbage . ' M');
//根据引擎不同,执行相应的清理语句
if ($value['engine'] == 'InnoDB') {
$t_sql = "alter table ".$value['table_name']." engine=InnoDB";
} else if ($value['engine'] == 'MyISAM') {
$t_sql = "optimize table " .$value['table_name'];
}
$res = DB::select($t_sql);
}