Varobj

2020-10-30

如何快速定位重复的脏数据



有个业务表,因为偷懒没有加上业务的唯一索引,导致一次主从同步延迟事故(从库加锁不释放)而写入一些重复的脏数据,为了删除脏数据,首先要筛选出多余的一部分,保留一条最新的记录,或者一条最老的记录。

1. 确定脏数据

部分数据表结构如下:

+---------+------------------+---------------------+----------------------+-------+
| id      | advertiser_id    | stat_datetime       | inventory            | cost  |
+---------+------------------+---------------------+----------------------+-------+
| 5737835 | 164******08      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 8.24  |
| 5738523 | 164******08      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 14.06 |
+---------+------------------+---------------------+----------------------+-------+

其中advertiser_idstat_datetimeinventory 三个维度是唯一的,但表中这两条记录维度一致,id 分别是 57378355738523 ,但是金额不同,导致18:00时间段数据变多。

统计总量数据

select count(*),count(distinct advertiser_id, stat_datetime, inventory)
    from dy_report_hourly
    where stat_datetime between '2020-10-29' and '2020-10-29 23:00:00';

结果:

+----------+---------------------------------------------------------+
| count(*) | count(distinct advertiser_id, stat_datetime, inventory) |
+----------+---------------------------------------------------------+
| 17428    | 16742                                                   |
+----------+---------------------------------------------------------+
1 row in set
Time: 0.083s

通过对比昨日总数据和 distinct 三个维度数据比较,发现总数据多了 686 条记录。

2. 筛选脏数据

通过not in语句筛选出需要删除的脏数据,三个维度都相同的,使用 max(id) 只保留最新的记录,删除此维度下其他数据,如果想要保留最早的记录,改成 min(id) 即可。

select count(*)
from dy_report_hourly
where stat_datetime between '2020-10-29' and '2020-10-29 23:00:00'
  and id not in (
    select max(id)
    from dy_report_hourly
    where stat_datetime between '2020-10-29' and '2020-10-29 23:00:00'
    group by advertiser_id, stat_datetime, inventory
);

结果也是 686 条记录,这些都是需要删除的

+----------+
| count(*) |
+----------+
| 686      |
+----------+

再次验证下结果的正确性

select id, advertiser_id, stat_datetime, inventory, count(*)
from dy_report_hourly
where stat_datetime between '2020-10-29' and '2020-10-29 23:00:00'
  and id not in (select max(id)
                 from dy_report_hourly
                 where stat_datetime between '2020-10-29' and '2020-10-29 23:00:00'
                 group by advertiser_id, stat_datetime, inventory)
group by advertiser_id, stat_datetime, inventory
limit 2;

列出前 2 条要删除的数据

+---------+------------------+---------------------+----------------------+----------+
| id      | advertiser_id    | stat_datetime       | inventory            | count(*) |
+---------+------------------+---------------------+----------------------+----------+
| 5737835 | 164******08      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 1        |
| 5737908 | 165******76      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 1        |
+---------+------------------+---------------------+----------------------+----------+

然后取一组维度信息,再次回表查询所有当前维度的记录。

select id, advertiser_id, stat_datetime, inventory, cost
from dy_report_hourly
where advertiser_id = '164******08'
  and stat_datetime = '2020-10-29 18:00:00'
  and inventory = 'INVENTORY_UNION_SLOT';

结果如下,共 2 条,需要保留最新记录,也就是id=5737835的记录需要删除,和上面查询出来的结果一致。

+---------+------------------+---------------------+----------------------+-------+
| id      | advertiser_id    | stat_datetime       | inventory            | cost  |
+---------+------------------+---------------------+----------------------+-------+
| 5737835 | 164******08      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 8.24  |
| 5738523 | 164******08      | 2020-10-29 18:00:00 | INVENTORY_UNION_SLOT | 14.06 |
+---------+------------------+---------------------+----------------------+-------+

然后就是机械地重复动作,筛选出需要删除的 id, 然后删除数据

3. 总结

  1. 不能偷懒,需要业务加唯一索引的地方果断加上
  2. 不要怕报错,网络、需求 到处充满不确定性因素,怎么可能没有错误,有错误及时发现才能改正。例如这次,如果有唯一键冲突报错,那就能及时(昨天下午五点半左右)主从延迟的时候,及时发现问题。而不是等到昨天晚上七点左右,发现数据不变化了才知道。然后第二天数据监控才发现脏数据的产生。
  3. 主从如何解决更新-查询的延迟问题?
  4. not inmax(id) & group by 只适合要筛选的数据范围比较小的情况。如果要筛选全表范围,且表大于五百万,那么这种方法会特别耗时,不推荐使用,这种情况可以选择使用布隆过滤器来实现脏数据筛选