国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置：首页 > 互联网 > Delete删除表数据时对性能的影响分析

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

来源：程序员人生   发布时间：2014-10-03 08:00:01 阅读次数：4233次
上一篇博客中提到，当我们用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表的表信息
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------

上一篇 android程序启动时去掉一瞬间的系统背景界面

下一篇 GitHub入门教程

分享到:
------分隔线----------------------------
为码而活
为码而活
积分：4237
15粉丝
7关注
栏目热点

程序员人生，我编程，我富裕，记住wfuyu网，php教程，php学习，php手册，CMS模版制作

声明：本站大部分内容是作者原创，少部分收集于互联网供大家一起学习，原版权很多不明，如有侵权请联系本站，谢谢！

粤ICP备14040726号-1   2015-2020 程序员人生 版权所有

平平安安 备案网站
关闭
程序员人生