Delete删除表数据时对性能的影响分析，delete删除

Delete删除表数据时对性能的影响分析，delete删除表数据

上一篇博客中提到，当我们用delete删除数据时，因为无法降低高水位，对表做全表扫描时会带来数据库性能问题，而truncate可以大大改善这一状况，下面来看测试
SQL> conn zlm/zlm Connected.
--创建表之前先记录下剩余表空间大小 SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ ZLM                                     6        128   51380224       6272            6
--创建一个100W行的大表 SQL> create table test1(int number);
Table created.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';
TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ------------ ----------- ------------------ TEST1                                   0          0            0           0 27-SEP-14
注意，创建完表结构而未插入数据时，表的高水位是0，并没有为该表分配任何数据块
SQL> declare
  2  i number := 0;   3  begin   4  for i in 1 .. 1000000 loop   5  insert into test1 values (i);   6  end loop;   7  commit;   8  end;   9  /
PL/SQL procedure successfully completed.
SQL> select count(*) from test1;
  COUNT(*) ----------    1000000
--查看高水位状况 SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS ----------- ------------ ---------- ---------- ----------           6          130   13631488       1664         28
SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'TEST%';
  BLOCK_ID  EXTENT_ID      BYTES     BLOCKS ---------- ---------- ---------- ----------        128          0      65536          8        136          1      65536          8        144          2      65536          8        152          3      65536          8        160          4      65536          8        168          5      65536          8        176          6      65536          8        184          7      65536          8        192          8      65536          8        200          9      65536          8        208         10      65536          8        216         11      65536          8        224         12      65536          8        232         13      65536          8        240         14      65536          8        248         15      65536          8        256         16    1048576        128        384         17    1048576        128        512         18    1048576        128        640         19    1048576        128        768         20    1048576        128        896         21    1048576        128       1024         22    1048576        128       1152         23    1048576        128       1280         24    1048576        128       1408         25    1048576        128       1536         26    1048576        128       1664         27    1048576        128
SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ ZLM                                     6       1792   37748736       4608            6
SQL> set autot trace SQL> select count(*) from test1;

Execution Plan ---------------------------------------------------------- Plan hash value: 3896847026
-------------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |       |     1 |            |          | |   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 | --------------------------------------------------------------------

Statistics ----------------------------------------------------------           0  recursive calls           1  db block gets        1599  consistent gets           0  physical reads           0  redo size         526  bytes sent via SQL*Net to client         519  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed
SQL> delete from test1;

1000000 rows deleted.

Execution Plan ---------------------------------------------------------- Plan hash value: 2642947686
-------------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------- |   0 | DELETE STATEMENT   |       |     1 |     2   (0)| 00:00:01 | |   1 |  DELETE            | TEST1 |       |            |          | |   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 | --------------------------------------------------------------------

Statistics ----------------------------------------------------------         814  recursive calls     1038983  db block gets        1953  consistent gets           8  physical reads   245334988  redo size         847  bytes sent via SQL*Net to client         769  bytes received via SQL*Net from client           3  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)     1000000  rows processed
用delete删除100W行数据的大表TEST1时，产生了大量的redo（2亿多），另外还有很多一致性读（1953），读取了100多W个数据块，814次递归调用，可以看到，用delete删除表记录，对数据库的性能消耗是很大的，尤其是当delete大量行的时候

SQL> set autot off SQL> select count(*) from test1;
  COUNT(*) ----------          0
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS ----------- ------------ ---------- ---------- ----------           6          130   13631488       1664         28
--用delete删除数据后查看执行计划 SQL> set autot trace SQL> select count(*) from test1;

Execution Plan ---------------------------------------------------------- Plan hash value: 3896847026
-------------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |       |     1 |            |          | |   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 | --------------------------------------------------------------------

Statistics ----------------------------------------------------------           0  recursive calls           1  db block gets        1599  consistent gets           0  physical reads           0  redo size         525  bytes sent via SQL*Net to client         519  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed
此时虽然表中已经没有一条记录了，但是由于TEST1表并没有索引，走的是全表扫描，全表扫描就是要从hearder_block的值一致扫描到blocks得值，即从block 130一致到block 1664，可以看到，即使去访问一个空表，也要消耗1599次逻辑读，这就是高水位没有下降的缘故，之前说过，delete操作并不会降低表的高水位，带来的副作用就是访问该表时带来的性能下降（产生大量逻辑读）
--truncate表后再次查看统计信息 SQL> truncate table test1;
Table truncated.
SQL> set autot trace SQL> select count(*) from test1;

Execution Plan ---------------------------------------------------------- Plan hash value: 3896847026
-------------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |       |     1 |            |          | |   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 | --------------------------------------------------------------------

Statistics ----------------------------------------------------------           2  recursive calls           1  db block gets           7  consistent gets           0  physical reads          96  redo size         525  bytes sent via SQL*Net to client         519  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed
此时再去全表扫描TEST1表，只有7次一致性读了，大大降低了需要扫描的数据库块，只有1个，
--查看此时TEST1表的表信息 SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';
TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ------------ ----------- ------------------ TEST1                                   0          0            0           0 27-SEP-14
--似乎不太准确，重新收集一下统计信息 SQL> analyze table test1 compute statistics;

Table analyzed.
--再次查看 SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';
TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ------------ ----------- ------------------ TEST1                                   0          0            8           0 27-SEP-14
可以看到，truncate已经把高水位降低到8了，而且是8个空块（EMPTY_BLOCKS），表示高水位以下未使用的空间，即第一个EXTENT为TEST1表分配的BLOCK空间，并且是最低值了，这与我们刚才是创建的只有表结构的空表不一样，同样是没有行数据的表，刚才创建表结构时的高水位为0。因此可以这么说，truncate降低高水位的作用也是有限的，还是剩余了1个extent的blocks的高水位，并没有完全消除。如果默认1个extent的block要大于8，那么高水位也要超过8
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS ----------- ------------ ---------- ---------- ----------           6          130      65536          8          1
dba_segment视图也可以反映这一高水位的情况，如果对表做全表扫描，就是从130开始，扫描8个数据块，而并非之前的从130扫描到1664了
--查看剩余表空间容量 SQL> select * from dba_free_space where tablespace_name='ZLM';
TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ ZLM                                     6        136   51314688       6264            6
truncate表数据后，ZLM剩余表空间又变大了，但是要注意，又多了8个block的消耗，建表之前查看的BLOCK_ID值是128
总结：
delete删除数据会读取大量的数据块，并产生大量的redo，对数据库产生性能影响，尤其是对大表操作时，删除大量的行数据时，而truncate虽然可以有效降低高水位，但其也有一定的局限性： 1. 并不能完全消除空表的高水位，仍然会有一定的空间浪费（8K） 2. 无法用于非清空全表数据的删除的场景

不是不可以使用，而是delete操作会写入日志，导致删除大量数据时性能低下，如果是要清空整表的话，建议用truncate，该操作不记录日志，但无法回滚，删掉后想恢复数据是很麻烦的，所以比较危险，
如果不是删除整表，还是要用delete的。
如果你是想删除大部分数据只留下一小部分的话，可以把那一小部分转移到一个临时的表，然后对原表truncate，再把转移的那部分数据复制回来。
 

SQL:
DELETE FROM 表名称 WHERE 列名称 = 值
eg：
Person:
LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Fred Zhongshan 23 Nanjing

删除某行
"Fred Wilson" 会被删除：
DELETE FROM Person WHERE LastName = 'Wilson'

结果:
LastNameFirstNameAddressCity
Gates Bill Xuanwumen 10 Beijing

删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name
或者：DELETE * FROM table_name
参考资料：www.w3school.com.cn/sql/sql_delete.asp