数据库高效优化：架构、规范与SQL技巧
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

案例2　糟糕的结构设计带来的问题

1.案例说明

这是某公司后台的ERP系统，系统已经上线运行了10多年。随着时间的推移，累积的数据量越来越大。随着公司业务量的不断增加，数据库系统运行缓慢的问题日益凸显。为提高运行效率，公司计划有针对性地对部分大表进行数据清理。在DBA对某个大表进行清理时出现了问题。这个表本身有数百吉字节，按照指定的清理规则只需要根据主键字段范围（运算符为>=）选择出一定比例（不超过10%）的数据进行清理即可。但在实际使用中发现，该SQL是全表扫描，执行时间大大超出预期。DBA尝试使用强制指定索引方式清理数据，依然无效，整个SQL语句的执行效率达不到要求。为了避免影响正常业务运行，不得不将此次清理工作放在半夜进行，还需要协调库房等诸多单位进行配合，严重影响正常业务运行。

为了尽量减少对业务的影响，DBA求助笔者帮助协同分析。这套ERP系统是由第三方公司开发的，历史很久远，相关的数据字典等信息都已经找不到了，只能从纯数据库的角度进行分析。这是一个普通表（非分区表），按照主键字段的范围查询一批记录并进行清理。按照正常理解，执行索引范围扫描应该是效率较高的一种处理方式，但实际情况都是全表扫描。进一步分析发现，该表的主键是没有业务含义的，仅仅是自增长的数据，其来源是一个序列。但奇怪的是，这个主键字段的类型是变长文本类型，而不是通常的数字类型。当初定义该字段类型的依据，现在已经无从考证，但实验表明正是这个字段的类型“异常”，导致了错误的执行路径。

下面通过一个实验重现这个问题。

（1）数据准备

两个表的数据类型相似（只是ID字段类型不同），各插入了320万数据，ID字段范围为1~3200000。

create table t1 as select * from dba_objects where 1=0;
alter table t1 add id int primary key;
create table t2 as select * from dba_objects where 1=0;
alter table t2 add id varchar2(10) primary key;

insert into t1 
select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','test','','','',rownum 
from dual 
connect by rownum<=3200000;
insert into t2 
select 'test','test','test',rownum,rownum,'test',sysdate,sysdate,'test','test','','','',rownum 
from dual 
connect by rownum<=3200000;
commit;
execdbms_stats.gather_table_stats(ownname => 'hf',tabname => 't1',cascade =>true,estimate_percent => 100);
execdbms_stats.gather_table_stats(ownname => 'hf',tabname => 't2',cascade =>true,estimate_percent => 100);

（2）模拟场景

相关代码如下：

select * from t1 where id>= 3199990;
11 rows selected.
--------------------------------------------------------------------------------
| Id | Operation                | Name       |Rows |Bytes|Cost (%CPU)|  Time    |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |            | 11  | 693 |   4  (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| T1         | 11  | 693 |   4  (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN         |SYS_C0025294| 11  |     |   3  (0) | 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
6  consistent gets
0  physical reads

对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。

select * from t2 where id>= '3199990';
755565 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2417K|   149M|  8927   (2)| 00:01:48 |
|*  1 |  TABLE ACCESS FULL| T2   |  2417K|   149M|  8927   (2)| 00:01:48 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
82568  consistent gets
0  physical reads

对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。

（3）分析结论

·字符类型在索引中是“乱序”的，这是因为字符类型的排序方式与我们的预期不同。从“select * from t2 where id>= '3199990'”执行返回755 565条记录可见，不是直观上的10条记录。这也是当初在做表设计时，开发人员没有注意的问题。

·字符类型还导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（'1'...'32000000'）t排序。

select table_name,index_name,leaf_blocks,num_rows,clustering_factor
from user_indexes
where table_name in ('T1','T2');
TABLE_NAME         INDEX_NAME      LEAF_BLOCKS   NUM_ROWS    CLUSTERING_FACTOR
-------------- -------------- ---------------- ---------- ---------------------
T1               SYS_C0025294             6275    3200000                 31520
T2               SYS_C0025295            13271    3200000                632615

·在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。

（4）解决方法

具体的解决方法如下：

select * from t2 where id between '3199990' and '3200000';
--------------------------------------------------------------------------------
| Id  | Operation                 | Name         |Rows|Bytes |Cost(%CPU)| Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |   6|  390 |   5 (0)|00:00:01|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |   6|  390 |   5 (0)|00:00:01|
|*  2 |   INDEX RANGE SCAN        | SYS_C0025295 |   6|      |   3 (0)|00:00:01|
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
13  consistent gets
0  physical reads

将SQL语句由开放区间扫描（>=），修改为封闭区间（between xxx and max_value）。使得数据在索引局部顺序是“对的”。如果采用这种方式仍然走全表扫描，还可以进一步细化分段或者采用“逐条提取+批绑定”的方法。

2.给我们的启示

这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示：

·糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上加以杜绝，才是优化的根本。

·在设计初期能引入数据库审核，可以起到很好的作用。

本周热推：
Hadoop应用实战数据迷雾：洞察数据的价值与内涵重复数据删除技术：面向大数据管理的缩减技术文本数据挖掘：基于R语言Hadoop与大数据挖掘（第2版）
上一章目录下一章