对于上千万甚至上亿的数据，如何正确的删除？

迷南。 2023-10-10 09:19 6阅读 0赞

【原创】 波波说运维 2019-06-22 00:01:00

# 概述 #

当我们的系统进入海量数据时代后，很多过去看起来轻松简单的事情就变得比较复杂。此时，就需要我们采取一些独特的技术和技巧，来避免因此带来的一些问题。

如果一个数据表要进行删除，而数据表对应的数据量很大，对应空间多，此时要进行数据表drop需要面对一些困难。

**首先是系统内存资源和CPU资源的使用峰值。集中进行drop操作，系统会进行数据表结构的回收，对应数据分区和数据块的回收。当数据表很大的时候，这个过程自然很长，对CPU来说消耗时间和数量都很大。同时，进行drop table的过程中，会将数据表转移到临时段进行处理，这个过程对临时段空间的消耗也是巨大的。**

**第二个是一个重要问题，就是对整体前端应用的影响。如果贸然进行drop操作，给业务系统造成影响，进而带来影响。**如果借用业务系统窗口期进行删除操作，又不能保证窗口期够用。

下面介绍一下对于海量数据表的标准删除方案。

--------------------

# 分步分阶段大表删除 #

总的指导原则是先删除数据，不影响数据一致性的要求，之后分阶段分步骤的进行空间回收。避免一次性drop对系统造成过大的压力。

步骤：

**1、首先使用带reuse storage子句的truncate table，将数据删除。**

reuse storage子句的作用是单纯降低数据段data segment的高水位线，对分配的空间不进行回收。这样truncate操作不涉及到空间回收，速度是可以接受的。

**2、分若干次数，使用deallocate unused keep XXX的方法，将分配的空间回收。**

因为keep后面可以加入维持空间数量，所以可以分若干个窗口期进行回收。

--------------------

# 实验演示 #

**1、准备实验环境。**

SQL> select * from v$version;

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][0f078d1911ec4dd8af5e72afc4154c3b]

**2、准备20万的数据**

远谈不上海量，这里主要做实例演示。

SQL> create table m as select * from dba_tables;
    SQL> insert into m select * from m;
    SQL> /
    SQL> /
    SQL> /
    SQL> /
    SQL> select count(*) from m;

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][02d4b14d96684546ada7d1de31c00b06]

**3、分析数据表M的相关参数信息**

此时，我们分析作为一个data segment，数据表M的相关参数信息。

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and owner='SCOTT';

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][6c9cfa1af67e437d8b337032815c78ee]

通过dba\_segments视图可以知道，数据表M共分配在81个分区上，共包括10240个数据块。空间占有为80M左右。

**4、删除实验**

首先使用truncate table。

SQL> truncate table m reuse storage;
    Executed in 0.52 seconds (假数据)

reuse storage子句下，空间是不进行回收的，所以相对速度较快。下面是一个对比同类型规模数据表实验。

SQL> create table md as select * from m;
    SQL> select count(*) from md;
    SQL> truncate table md;
    Executed in 0.901 seconds (假数据)

相同的数据结构和数据量，使用的时间要超过reuse storage的方式。当然，带有reuse storage的语句下空间也是不回收的。

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][9d0bfcdf2ef34907bee0eb76b7b1ccc1]

SQL> exec dbms_stats.gather_table_stats('SCOTT','M',cascade => true);
    SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SCOTT';

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][0c79e7d013294a8c8e963c66365c7c76]

这里可以发现，虽然数据被删除了，但是空间没有回收。在dba\_segments上，依然显示81个数据区的空间分配。

**5、分阶段进行空间回收**

让回收工作在受控范围下进行。

5.1、第一次空间回收

SQL> alter table m deallocate unused keep 20M; --控制在20M
    SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and owner='SCOTT';

可见，分区为36个，将空间近似降低到21M左右。此后，可以在不同的窗口期中，根据自身情况不断进行回收空间工作。

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][9525de15672649d9915e745c229a9ecf]

5.2、多次空间回收

SQL> alter table m deallocate unused keep 15M;
    SQL> alter table m deallocate unused keep 10M;
    SQL> alter table m deallocate unused keep 3M;
    SQL> alter table m deallocate unused keep 100k;
    SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and owner='SCOTT';

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][fa4c0f5124d74f0c920a5ea3f4250808]

正常每个区间是64KB，100K需要2个分区。

5.3、尝试回收到1k

SQL> alter table m deallocate unused keep 1k;
    SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and owner='SCOTT';

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][0df8a199c6a54083b129a496cdaa2144]

这里虽然没有回收到1k，但也是已经到一个分区八个数据块的程度。最后就直接drop table即可。

5.4、删表

SQL> drop table m;

![面试官：对于上千万甚至上亿的数据，如何正确的删除？][5a35e082649a4f42af927b48e951b61c]

--------------------

# 总结 #

**1、在写一句SQL的时候，一定要注意操作数据集合、对象的范围。对于可能存在的海量数据访问，一定要实现有准备，事后有监控。这样才能保证系统可用性以及上线成功率；**

**2、海量数据处理的一个方法就是分割，根据业务的限制和要求，在时间上进行分割。处理原则是避开业务系统繁忙时间段，尽量将高负载工作进行划分实现；**

**3、drop数据表的方法。一般来说，进行drop数据表是不能中间终止的。drop数据表时，oracle首先把数据段转化为临时段对象，之后开始不断的进行空间回收。即使这个过程中间停止（强制终止），再次启动的时候smon进行恢复，也会进行回收删除操作。所以，对数据表进行直接drop的时候，切记三思。**

转载于:https://www.cnblogs.com/lcword/p/11553125.html

[0f078d1911ec4dd8af5e72afc4154c3b]: http://p3.pstatp.com/large/pgc-image/0f078d1911ec4dd8af5e72afc4154c3b
[02d4b14d96684546ada7d1de31c00b06]: http://p3.pstatp.com/large/pgc-image/02d4b14d96684546ada7d1de31c00b06
[6c9cfa1af67e437d8b337032815c78ee]: http://p1.pstatp.com/large/pgc-image/6c9cfa1af67e437d8b337032815c78ee
[9d0bfcdf2ef34907bee0eb76b7b1ccc1]: http://p9.pstatp.com/large/pgc-image/9d0bfcdf2ef34907bee0eb76b7b1ccc1
[0c79e7d013294a8c8e963c66365c7c76]: http://p3.pstatp.com/large/pgc-image/0c79e7d013294a8c8e963c66365c7c76
[9525de15672649d9915e745c229a9ecf]: http://p9.pstatp.com/large/pgc-image/9525de15672649d9915e745c229a9ecf
[fa4c0f5124d74f0c920a5ea3f4250808]: http://p1.pstatp.com/large/pgc-image/fa4c0f5124d74f0c920a5ea3f4250808
[0df8a199c6a54083b129a496cdaa2144]: http://p3.pstatp.com/large/pgc-image/0df8a199c6a54083b129a496cdaa2144
[5a35e082649a4f42af927b48e951b61c]: http://p1.pstatp.com/large/pgc-image/5a35e082649a4f42af927b48e951b61c