MySQL从大表(千万级)中删除符合条件的记录的可用方法。
22-01-17 17:25
字数 1459
阅读 2788
已编辑
我有一个日志表 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人点赞>
请登录后发表评论
相关推荐
文章归档
2024-11
1 篇
2024-06
1 篇
2024-05
2 篇
2024-04
2 篇
2024-03
2 篇
展开剩余 68 条
2024-01
1 篇
2023-10
1 篇
2023-09
1 篇
2023-08
1 篇
2023-06
1 篇
2023-04
1 篇
2022-12
2 篇
2022-06
1 篇
2022-04
4 篇
2022-03
3 篇
2022-01
6 篇
2021-12
2 篇
2021-11
2 篇
2021-10
2 篇
2021-09
1 篇
2021-08
2 篇
2021-07
4 篇
2021-06
1 篇
2021-05
3 篇
2021-04
3 篇
2021-01
2 篇
2020-11
1 篇
2020-10
3 篇
2020-09
2 篇
2020-08
1 篇
2020-07
5 篇
2020-06
5 篇
2020-05
1 篇
2020-04
1 篇
2020-03
2 篇
2020-02
3 篇
2020-01
1 篇
2019-11
5 篇
2019-10
10 篇
2019-09
12 篇
2019-08
17 篇
2019-07
8 篇
2019-05
3 篇
2019-04
8 篇
2019-03
7 篇
2019-02
8 篇
2019-01
5 篇
2018-12
7 篇
2018-11
8 篇
2018-10
4 篇
2018-09
7 篇
2018-08
12 篇
2018-07
9 篇
2018-06
6 篇
2018-05
11 篇
2018-04
18 篇
2018-03
1 篇
2018-02
2 篇
2018-01
10 篇
2017-12
14 篇
2017-11
44 篇
2017-10
13 篇
2017-09
4 篇
2017-08
12 篇
2017-07
5 篇
2017-06
4 篇
2017-05
2 篇
2017-04
3 篇
2017-03
9 篇
2017-02
3 篇
2017-01
2 篇
2016-12
10 篇
2016-11
4 篇
最新文章
最受欢迎
11-07 19:00
06-26 11:51
05-17 17:08
05-17 10:59
04-11 17:05
13 评论
11 评论
10 评论
你真是天才,哈哈