针对mysql delete删除表数据后占用空间不变小的问题

时间:2022-06-14 11:07:52

开发环境

  • Yii1版本
  • MySQL
  • PHP5.6.27

前言

物流规则匹配日志表记录订单匹配规则相关日志信息,方便管理员维护和查阅不匹配的订单,四个月时间,该日志表数据就有174G,当前,这么大的数据量,不仅对数据库造成了很大的负载压力,同时查询等维护也缓慢,所以采取将日志记录移出到文件进行存储。但是短期内,还需要数据库中的部分日志记录,故而有了下面的删除记录、优化表操作。

日志表大小一览

表本身有六七百万条数据,从六七百万删到五百多万,发现数据占用空间大小一点也没变,如下图所示。网上查到需要释放删除了的数据占用的空间、也就是优化表或碎片整理,使用到的命令是:OPTIMIZE TABLE tableName

针对mysql delete删除表数据后占用空间不变小的问题

问题出现原因

在删除sql语句中,写法如下:DELETE FROM ueb_logistics_rule_logs WHERE type=0 LIMIT 100; 凡是这样,delete带有where条件的,都不是真删除,只是MySQL给记录加了个删除标识,自然这样操作后表数据占有空间也不会变小了

注意:DELETE FROM ueb_logistics_rule_logs; 这条sql语句执行后,就清空了表数据,占有空间就变为0了

针对mysql delete删除表数据后占用空间不变小的问题

解决方法

主要就是执行下面三条sql语句(轮询删除delete,避免一次性删除数据太多造成MySQL负载崩溃,另外数据量大的时候需要等待网站访问流量小的时候执行)

  • DELETE FROM ueb_logistics_rule_logs WHERE type=0 LIMIT 100;
  • OPTIMIZE TABLE ueb_logistics_rule_logs;

源码

基于yii1版本框架,命令行下执行,可以加入定时脚本任务里,自动执行

<?php

/**
* 定时清理相关表数据
* Class Logisticstable
*/
class LogisticstableCommand extends CConsoleCommand
{
public $before15Days;
public $oneMonthAgo;
public $halfYearAgo; /**
* 清理表数据
*
* windows环境:
* >yiic.bat Logisticstable Execute
* >yiic.bat Logisticstable Execute 10
* linux环境:
* >./yiic Logisticstable Execute
* >./yiic Logisticstable Execute 10
*
* @param int $limit 清理的条数
*/
public function actionExecute($limit = 100)
{
$sTime = microtime(true);
$this->initDaytime();
$dbList = $this->tableData(); $cacheKey = 'command:logisticstable:%s:%s';
foreach ($dbList as $db=>$tableList){
foreach ($tableList as $tableInfo){
$tableName = $tableInfo['tableName'];
$cacheKeyTmp = sprintf($cacheKey, $db, $tableName);
$isRunning = Redis::getCache($cacheKeyTmp);
if (!empty($isRunning)){
//当前表正在清理中……
continue;
}
Redis::setCache($cacheKeyTmp, 1, 36000);
//轮询删除表数据
while(!$this->myDeleteData($db, $tableName, $tableInfo['whereStr'], $limit)){
sleep(3);
}
//优化表空间
$this->myOptimizeTable($db, $tableName);
Redis::deleteCache($cacheKeyTmp);
sleep(10);
}
} echo '共计耗时:'. (microtime(true)-$sTime).' s'.PHP_EOL;
exit(0);
} /**
* 删除表数据
* @param $db
* @param $tableName
* @param $where
* @param int $limit
* @return bool
*/
protected function myDeleteData($db, $tableName, $where, $limit = 1000)
{
//连接数据库
$connection = Yii::app()->$db; $findSql = "SELECT * FROM `".$tableName."` WHERE $where ";
$arrRow = $connection->createCommand($findSql)->queryRow();
if (empty($arrRow)){
//没有要删除的数据了
return true;
} //删除
$deleteSql = "DELETE FROM `".$tableName."` WHERE $where LIMIT $limit ";
$connection->createCommand($deleteSql)->execute();
return false;
} /**
* 释放被删除数据占用的空间
* @param $db
* @param $tableName
* @return mixed
*/
protected function myOptimizeTable($db, $tableName)
{
//连接数据库
$connection = Yii::app()->$db; //优化表空间
$optimizeSql = "OPTIMIZE TABLE `".$tableName."`";
return $connection->createCommand($optimizeSql)->execute();
} /**
* 初始化时间
*/
protected function initDaytime()
{
$this->before15Days = date('Y-m-d H:i:s', strtotime('-15 day'));
$this->oneMonthAgo = date('Y-m-d H:i:s', strtotime('-1 month'));
$this->halfYearAgo = date('Y-m-d H:i:s', strtotime('-6 month'));
} /**
* 相关表数据
*/
protected function tableData()
{
return [
'db_logistics'=>[
[
//check_task_is_run 保留最新15天
'tableName'=>'check_task_is_run',
'whereStr'=> "create_time < '{$this->before15Days}'"
],
[
//check_task_run_get_logistict_cost 30天
'tableName'=>'check_task_run_get_logistict_cost',
'whereStr'=> "create_time < '{$this->oneMonthAgo}'"
],
[
//ueb_logistics_rule_opration_log 半年
'tableName'=>'ueb_logistics_rule_opration_log',
'whereStr'=> "create_time < '{$this->before15Days}'"
],
]
];
}
}

总结

惭愧,今天才碰到这类问题,MySQL delete表数据,之前一直的感觉就是直接删除掉了,数据所占的空间也自然会释放,但是今天才发现,delete sql语句后加where条件删除的数据MySQL会自动加个delete标识而不会真的删掉。学的太浅了,还需多多努力!

参考资料