MySQL从大表(千万级)中删除符合条件的记录的可用方法。

01-17 17:25 字数 1459 阅读 468 已编辑

我有一个日志表 user_log,大概有6000万数据。表中存在大量无用数据。比如 status = 2 的数据是垃圾数据(status加了索引),永远都不会被查询或修改,所以我想把这部分记录删除掉,节省点表空间。这部分无用数据大概是2000万条。

如果我直接 delete from user_log where status = 2 那操作一定会失败。

所以刚开始刚开始我的代码是这么写的。

delete from user_log where status = 2 order by id limit 1000;

写个循环,每次删除1000条数据,直到全部删除,看起来好像没什么问题。

但是实际跑脚本的过程中发现越往后执行越慢,到最后1次循环需要大概10-20秒,照这么跑下去想删除完2000万条数据,岂不是要好几周,因为为了保险起见,我只在晚上执行它。

为什么这么慢,通过explain SQL发现,我的删除语句并没有走索引。

那么怎么让删除走索引呢,最后经过摸索,通过一种取巧的方式实现了需求。代码如下

// 伪代码
$minId = "SELECT min(id) as min_id from user_log WHERE status = 2";
$maxId = "SELECT max(id) as max_id from user_log WHERE status = 2";

for ($i = 0; $i < $batchNum; $i++) {
    $startId = ($i * $batchSize) + $minId;
    $endId = $startId + $batchSize - 1;
    $idArray = range($startId, $endId);
    $ids = implode(",", $idArray);

    $sql = "DELETE FROM user_log WHERE id IN ($ids) and status = 2";
    try {
        $deletedRows = $db->createCommand($sql)->execute();
        if ($deletedRows > 0) $totalDeletedRows += $deletedRows;
        printf("删除行数: %d | batch:(%d/%d),%d / {$totalCount}\r", $deletedRows, $i + 1, $batchNum, ($i + 1) * $batchSize);
    } catch (Exception $e) {
        echo "异常: " . $e->getMessage() . PHP_EOL;
    }
}

// 优化表,谨慎使用
// alter table user_log engine = INNODB

解释下上面的代码,思路就是,使用 delete in 删除记录,但是 in里不能嵌套子查询,如果嵌套了子查询,最终还是无法走索引,所以只能在代码中给它生成 id,然后直接放到in中。这样每次扫描的行数就是1000,实际运行速度对比以前也有了质的提升,6000万的表扫一遍只要400s左右。

优化后

上图是我多次运行脚本后的结果,实际删除2000万条数据也是差不多的时间。

0人点赞>
关注 收藏 改进 举报
2 条评论
排序方式 时间 投票
kittyfamous

你真是天才,哈哈

Up骚年
哈哈,没办法的办法。
请登录后发表评论
站长 @ 十七度
文章
368
粉丝
23
喜欢
180
收藏
28
排名 : 1
访问 : 87.81万
私信