复合索引是如何工作的？

biti_rainy

最初由 ahlu 发布
[B]谢谢！ [/B]

我这里说的范围扫描  确切地说应该说 范围查询

因为范围查询是从一个入口点进去找到 叶子，然后从叶子之间的指针逐个找下去，如果碰巧该范围中有一段特别多的数据那将 搜索大量的数据

而如果是符合索引的两个字段条件都是等于并且满足条件的结果集比较小，那它是直接从 索引的枝上定位到相关叶子，则问题不是很大。


这是因为 a,b 两个字段的索引的key是存储在一起的，不存在先过滤一遍a再过滤一遍b的问题。也就是说，如果是符合索引，我们假定存在数字 a = 1000, b为 1..1000000 的记录。总记录条数为100万条，b是唯一的。若a做前导列的索引(a,b)。 实际上，如果查询总是同时存在 a,b  的等于条件，则效果就相当于 1000*100万 + 1..100万  这样的100万个值的单个字段做索引。

我再重复一遍我上面做过的实验，希望你仔细看一看！


SQL> desc t
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(18)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

SQL> update t set  owner = 'sys';

481216 rows updated.

SQL> commit;

Commit complete.

SQL>  update t set  OBJECT_ID = rownum;

481216 rows updated.

SQL> commit;

Commit complete.

SQL> create index t_ind12 on t(owner,object_id);

Index created.

SQL> set autotrace traceonly
SQL> select * from t where  owner = 'sys' and object_id = 12345;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_IND12' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         5  consistent gets
          2  physical reads
          0  redo size
       1156  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> create index  t_ind21 on t(object_id,owner);

Index created.

SQL>  select * from t where  owner = 'sys' and object_id = 12345;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_IND21' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         5  consistent gets
          2  physical reads
          0  redo size
       1156  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

在下面这个例子中 对于前导列a字段来说，恰好 'sys' 满足 > 'a' 这个条件，但是一开始你可能并不清楚会有这么多的相同的满足 >'a' 的值，这不过是举个例子，比如a换做数字类型，恰好大量数字a  = 100，你查询一个 a between 99 and 100,你可能并不会认为大量记录在里面,但是实际上却还不如全表扫描。

SQL>  select * from t where owner > 'a';

481216 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_IND12' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      70913  consistent gets
       1245  physical reads
          0  redo size
   29217583  bytes sent via SQL*Net to client
     353390  bytes received via SQL*Net from client
      32083  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     481216  rows processed

ahlu

就想你看书一样，当你要看大多数页时，还不如直接去翻书，看目录反而慢。
在问个问题，每次取索引快时，使取出满足条件的一个块后直接去访问表，还是等取出所有满足条件的索引块后再去访问表？有什么办法使表中数据能顺序排列，这样当取出一个索引块后也能对应一个表块，如果无序状态下，可能一个索引块中的rowid会对应多个表块，并且其他索引块也会访问相同的表块，这可能造成极大的浪费，可能也是大量逻辑读产生的原因！

adamyang

最初由 ahlu 发布
[B]就想你看书一样，当你要看大多数页时，还不如直接去翻书，看目录反而慢。
在问个问题，每次取索引快时，使取出满足条件的一个块后直接去访问表，还是等取出所有满足条件的索引块后再去访问表？有什么办法使表中数据能顺序排列，这样当取出一个索引块后也能对应一个表块，如果无序状态下，可能一个索引块中的rowid会对应多个表块，并且其他索引块也会访问相同的表块，这可能造成极大的浪费，可能也是大量逻辑读产生的原因！ [/B]

尽管觉得用书的目录做比喻有些欠妥
（用书的索引更恰当些吧^--^）
但道理是一样的，在选择性底的列上建立索引意义不大，
在对表中很多数据检索时，索引还不如全表。
在良好表分析的前提下CBO可以决定一部分这种选择了：）

biti_rainy

最初由 ahlu 发布
[B]就想你看书一样，当你要看大多数页时，还不如直接去翻书，看目录反而慢。
在问个问题，每次取索引快时，使取出满足条件的一个块后直接去访问表，还是等取出所有满足条件的索引块后再去访问表？有什么办法使表中数据能顺序排列，这样当取出一个索引块后也能对应一个表块，如果无序状态下，可能一个索引块中的rowid会对应多个表块，并且其他索引块也会访问相同的表块，这可能造成极大的浪费，可能也是大量逻辑读产生的原因！ [/B]

你这个话 是正确的，但是和你 前面的问题是两码事


索引本就不适合查询大量数据，这是基本原则问题

至于表数据和索引顺序保持一致,这个问题讨论过多次了，可以参考
http://blog.itpub.net/post/330/2970



如果非要保持数据顺序的问题，可以参考 IOT 表(为什么不广泛使用，肯定是有原因的，自己多去琢磨吧)

ahlu

但他重建起来很费事，我觉得iot很适合dss

ahlu

在问个问题，解析sql-〉建立和执行执行计划-〉读取结果集-〉显示结果集，哪个最费时间，他们各占时间的百分比是多少？

biti_rainy

一般都是消耗在查询读取这个环节，如果返回记录特多，达到数百万以上，可能在网络传输一层也要消耗一些时间，不过由于这段时间和 读取记录过程是重叠的，所以不是简单的叠加。

BTW :  具体情况下你完全可以测试，怎么能用 百分比 来一概而论呢？？？？？
select 1 from  dual
与
select * from  big_table  难道能用个百分比来做统一结论？？？？

ahlu

而无所谓是不是前导列，但我又在想在rule下，oracle没有统计资料，他怎么会知道哪个列范围大？

biti_rainy

最初由 ahlu 发布
[B]而无所谓是不是前导列，但我又在想在rule下，oracle没有统计资料，他怎么会知道哪个列范围大？ [/B]

where 条件中哪个列 范围大，也就是说选择性的差异吧


前导列 是针对具体某个索引而言， where 条件中各列到底是针对多个索引而言？ 如果不是，对于一个索引的话，哪里存在 哪个列的选择性的问题？对于一个复合索引来说，只要符合索引的列都存在于where条件中，那就不存在什么哪个列先过滤再比对另一个列的问题，我做了2次实验不都说明了这个问题么？


rule下，oracle根本不会考虑选择性问题！有着既定的不变的规则，你有疑问，请做实验证明之，实验有问题不可理解再探讨行不行呢，就这样本就没有理清头绪的讨论，不过是兜圈子而已。

ahlu

sql的运行过程就象你到图书馆去借书，当你和图书管理员说你要鲁迅的全部书籍（SELECT * FROM PUB WHERE NAME='鲁迅'）,图书管理员会很快想出执行计划也就是访问路径，但找到书会很多，时间是浪费在搬运书的过程中，如果你和图书管理员说你要1980年清华出版社出版的以sk开头的书籍，这时有多种选择路径，图书管理员可能要盘算一下了，最后选择查索引，只找出了几本书，这时时间是花费在选择路径上，还有个问题，如果你要以s开头的书，可能只有几本，但也可能会很多，这时一个刚上任的图书管理员回去机械的查索引，运气好只有几本书，会很快但运气不好很多书还不如直接到书架上去查（这就是基于rule的缺点），一个工作多年的管理员，会知道大概查询书有多少，如果他估计会很多，他会直接去查书架（这就是基于cost，收集表的统计信息），问题是这个工作多年的管理员因某些原因很久没有上班了，图书馆又进了很多书，或又转移了很多书，下次查询时他很能会选择错误的路径，这就是基于cost的不稳定性和需要定时维护的缺点，管理员需要不断的去了解书的库存状况，当图书馆很大时会很费时会影响工作