聊聊索引和SQL优化

最新推荐文章于 2024-10-17 10:02:06 发布

ciqu9915

最新推荐文章于 2024-10-17 10:02:06 发布

阅读量90

点赞数

文章标签：数据库数据结构与算法

和一个朋友聊起索引和执行路径的问题，觉得很有意思，就把一点想法写下来，权当一个记录。

讨论是这样：有一个数据表，其中有字段为月份（使用数字类型）。根据业务的需要，很多搜索都是依据这个月份字段来进行的。于是从提高整体搜索性能角度，选择该列作为一个索引列。但是某个月份的数据是在一个统一的时间点被插入到系统中。所以，该表中所包含对应月份数据，是逐渐的增加的。开始的一段时间里，可能只有一个月份的数据。当进行使用月份作为搜索条件的SQL语句时，朋友发现执行路径并没有使用索引。

问题原理和解释

这个问题其实是比较好理解的。设置了索引，而执行路径没有选择索引路径。最直接的可能性就是Oracle优化器认为当前的数据分布情况下，使用全表扫描的成本更低。

这里面我们再说一下Oracle优化器。SQL语句本质上是一种描述性语句，本身不会决定获取数据的操作和方法。Oracle对于SQL语句的解析parse中，需要对该SQL生成执行计划，也就是确定这个SQL进行操作的方式。从SQL语句到执行计划的过程，其实就是Oracle优化器的主要工作。

Oracle优化器主要是两个类型，基于规则（Rule-Based Optimizer RBO）和基于成本(Cost-Based Optimizer CBO)。RBO是早期的一种优化器，是依据SQL语句本身书写的一些规则来确定执行计划。这种方法的优点是规则简单，我们通常指通过表结构、索引结构和执行SQL语句，就可以确定出执行计划。生活是复杂的，简单通常也就伴随着武断。一些时候，RBO生成的执行计划往往不是最好。例如：当我们在where条件中书写一个字段选择，并且字段为索引列。RBO会直接选择索引路径。但是这时候，走索引可能不是很好的选择。因为索引本身读取也需要额外的成本付出。

于是，CBO应运而生。CBO依据的是数据表和数据项的统计量，包括数据分布情况，取值分布等。根据这些数据当前的实际情况，CBO去生成执行计划。应该说，CBO是更科学、更贴近实际需要的优化器方法。从Oracle9i之后，CBO成为优化器默认的配置（感谢 lixin_2002 的指正），成为优化器发展的一个方向。

本质上说，RBO是一系列生成规则的集合，而CBO则是一系列参数控制公式的集合。对每个SQL语句，Oracle优化器都会生成多条执行计划，根据收集的统计数据和成本参数（作为系统参数的一部分）为每个执行计划试算出一个成本cost数值，依据最少cost的原则确定选择的执行计划。

回到朋友提到的案例。在最开始的时候，数据表中只有一个月份的信息，在月份列上建立了索引对象。当执行带月份的条件查询时，Oracle会生成两份执行计划：全表扫描计划FTS和索引计划。当全表数据都是一个月份的情况下，Oracle如果选择索引执行计划，意味着会搜索全部索引树，并且根据索引树中所有的rowid获取数据表。成本要远远大于直接进行FTS。

下面我们进行一个简单实验。

SQL> conn scott/tiger@orcl

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

//构建数据表

SQL> create table t as select rownum as ronum, object_id,object_name, 201101 as month from all_objects;

Table created

SQL> select month,count(*) from t group by month;

MONTH COUNT(*)

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

201101 40718

构建索引，并且收集统计量。

SQL> create index idx_t_month on t(month);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

首先，月份month列值有一种取值201101，上面建立索引。我们先查看搜索路径。

SQL> select * from t where month=201101;

已选择40718行。

已用时间: 00: 00: 00.98

执行计划

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 40718 | 1590K| 63 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 40718 | 1590K| 63 (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("MONTH"=201101)

统计信息

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

201 recursive calls

0 db block gets

2995 consistent gets

0 physical reads

0 redo size

1608149 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

40718 rows processed

执行计划上，可以方便看到执行路径为全表扫描，并没有选择索引路径。如果我们强制要求Oracle生成走索引的路径，可以使用hint。

SQL> select /*+ index(t idx_t_month) */ * from t where month=201101;

已选择40718行。

已用时间: 00: 00: 01.09

执行计划

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

Plan hash value: 3445114591

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

-----------

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

| 0 | SELECT STATEMENT | | 40718 | 1590K| 349 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 40718 | 1590K| 349 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | IDX_T_MONTH | 40718 | | 93 (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("MONTH"=201101)

统计信息

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

64 recursive calls

0 db block gets

5766 consistent gets

92 physical reads

0 redo size

2191670 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40718 rows processed

从执行计划和实际执行计时上看，强制走索引的执行计划明显占劣势。可见，Oracle优化器在做这个选择的时候，是选择成本较低的全表扫面执行计划。

所以，我们不难得出结论。我们确定某某列上需要加索引，是由于业务操作需求上，存在对该列访问需求。但是，这不意味着该索引一定会成为所有情况SQL的执行计划。索引路径只是作为Oracle可选的一种执行路径，实际执行情况还要看各种可选路径的成本估算值。也就是说，没有走索引，也不一定是坏事，重点在于是否满足性能需求，要区别对待。

索引方案的使用

那么，在什么情况下，索引路径才会执行呢？索引路径成本的组成=读取索引树中符合条件记录的rowid成本+据rowid成本获取数据表块记录成本。只有选择性比较好的时候，这种成本小于全表扫描的时候，Oracle会选择索引路径。

继续实验，我们修改一下数据表数据结构。

SQL> select month,count(*) from t group by month;

MONTH COUNT(*)

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

201103 1999

200112 499

201102 999

201101 40718

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

我们重新整理了一下数据分布情况，增加了数据的选择性（虽然数据取值稍偏）。我们进行搜索。

SQL> select * from t where month=201102;

已选择999行。

已用时间: 00: 00: 00.11

执行计划

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

Plan hash value: 3445114591

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

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

| 0 | SELECT STATEMENT | | 1022 | 39858 | 10 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1022 | 39858 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T_MONTH | 1022 | | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("MONTH"=201102)

统计信息

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

383 recursive calls

0 db block gets

209 consistent gets

8 physical reads

0 redo size

44074 bytes sent via SQL*Net to client

1111 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

999 rows processed

索引起效果了。Oracle针对这个查询，发现执行索引路径成本较低。反之，对一些SQL，Oracle同样会执行全表扫描。

SQL> select * from t where month=201101;

已选择40718行。

已用时间: 00: 00: 01.00

执行计划

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 40773 | 1552K| 73 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 40773 | 1552K| 73 (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("MONTH"=201101)

统计信息

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

1 recursive calls

0 db block gets

3011 consistent gets

29 physical reads

0 redo size

1608149 bytes sent via SQL*Net to client

30239 bytes received via SQL*Net from client

2716 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40718 rows processed

这种灵活性，也就是体现出CBO的优势，针对实际情况，执行灵活的执行计划。

最后，笔者感觉有必要说明一个原则，就是优化的原则。在相同的情况（软硬件、配置）下，全表获取一百条数据的成本要低于全表获取100万条数据的成本。从100行数据中获取10行数据成本要低于从100万行数据中获取10行数据。随着数据表的容量扩大，系统总会出现瓶颈。我们进行优化的原则就是随着系统容量增加，性能变化处于一个线性变化就可以了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-686787/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-686787/