- 数据库高效优化：架构、规范与SQL技巧
- 马立和 高振娇 韩锋
- 1134字
- 2020-08-27 05:47:11
7.2 字段
字段对象对于SQL语句的执行效率也有很大的影响。影响因素主要体现在两个方面——字段存储顺序和字段类型，下面分别说明。
1.字段存储顺序
字段存储顺序会影响访问性能。下面我们先观察一下行记录的存储结构。
其中，H表示记录头，L表示字段长度，D表示字段内容。
从上面的结构可见，数据库不知道一条记录中每个字段的偏移量。如果需要定位字段2，必须从字段1开始，接着根据字段1的长度来定位字段2。靠近记录开始的字段定位速度明显快于末尾的字段。因此，在做表设计时，将访问频繁的字段放在前面。
2.字段类型
如果说字段存储顺序对访问性能有一定影响，那么字段类型对访问性能就有着更显著的影响。常见的问题是：隐式数据类型转化；错误数据类型带来的成本估算异常。
下面通过两个示例分别说明，先举一个隐式数据类型转化的示例。
SQL> create table t1 (owner varchar2(30),object_name varchar2(128),object_id varchar2(100));
//表已创建
SQL> insert into t1(owner,object_name,object_id) select owner,object_name,object_id from dba_object
已创建 18869 行。
SQL> commit;
//提交完成
SQL> create index idx_t1_id on t1(object_id);
//索引已创建
SQL> set autotrace on
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 | 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 135 | 27 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("OBJECT_ID")=20)
/*
从上面输出可见，表T1的OBJECT_ID字段保存的是数字，测试中估计创建了文本类型，导致模拟选择错误字段的情况发生。当执行一个正常的查询时，由于类型不一致，优化器进行了隐式的数据类型转换，从Predicate Information中可以看出来，进行了一次TO_NUMBER操作。由于数据类型转换，整体执行计划走了全表扫描
*/
//下面我们看看正常情况下的执行计划
SQL> select * from t1 where object_id='20';
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 1 | 135 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 78 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"='20')
//从上面输出可见，引用了正确的数据类型后，走了索引的范围扫描
下面看一下因为数据类型异常导致的优化器估算异常的示例。
SQL> create table t_test(id number,v1 varchar2(20),n1 number,d1 date);
//表已创建
SQL> insert into t_test select rownum,
2 to_char(to_date('2001-01-01','yyyy-mm-dd') + (rownum-1),'yyyy-mm-dd'),
3 to_char(to_date('2001-01-01','yyyy-mm-dd') + (rownum-1),'yyyymmdd'),
4 to_date('2001-01-01','yyyy-mm-dd') + (rownum-1) from dual
5 connect by rownum<= (to_date('2010-12-31','yyyy-mm-dd') - to_date('2001-01-01','yyyy-mm-dd'));
//已创建 3651 行
SQL> exec dbms_stats.gather_table_stats('hf', 't_test');
//PL/SQL 过程已成功完成
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
//会话已更改
SQL> select * from t_test where rownum<10;
ID V1 N1 D1
---------- ------------------------------------- ----------- -------------------
685 20021116 20021116 2002-11-16 00:00:00
686 20021117 20021117 2002-11-17 00:00:00
687 20021118 20021118 2002-11-18 00:00:00
688 20021119 20021119 2002-11-19 00:00:00
689 20021120 20021120 2002-11-20 00:00:00
690 20021121 20021121 2002-11-21 00:00:00
691 20021122 20021122 2002-11-22 00:00:00
692 20021123 20021123 2002-11-23 00:00:00
693 20021124 20021124 2002-11-24 00:00:00
//已选择9行
//上面创建了一张测试表，包含3个字段，保存的信息都是“日期”。后面插入了10年的日期数据
SQL> select * from t_test
where d1 between to_date('2001-01-01','yyyy-mm-dd') and to_date('2002-01-01', 'yyyy-mm-dd');
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 366 | 10614 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST | 366 | 10614 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D1"<=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "D1">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
//上面测试中按日期类型字段进行范围扫描，优化器评估返回366条记录，这是十分精准的
SQL> select * from t_test where v1 between '20010101' and '20020101';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402 | 10854 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST | 402 | 10854 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"<='20020101' AND "V1">='20010101')
/*
如果使用文本字段进行类似的查询，优化器评估返回402条记录，这较上面测试存在一定偏差。为什么会造成这一现象？原因就是优化器针对文本的范围选择率的评估不如日期类型精准
*/
SQL> select * from t_test where n1 between 20010101 and 20020101;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402 | 10854 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST | 402 | 10854 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"<=20020101 AND "N1">=20010101)
//数字类型与文本类型类似