复合索引的前导列如何选择?

标签: 复合 索引 选择 | 发表时间:2013-09-18 17:51 | 作者:guogang83
出处:http://blog.csdn.net

     在表上建复合索引是常见的事情,那索引中键值的顺序是什么呢?通过下面的实验可以得出三个结论:

--制造实验数据,并收集统计信息

SQL> create table test as select * from dba_objects;
SQL> create index ind_id_owner on test(object_id,owner);
SQL> create index ind_owner_id on test(owner,object_id);
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);

SQL> select count(distinct owner) owner_count,  count(distinct object_id) id_count, count(*)  from test;

OWNER_COUNT   ID_COUNT   COUNT(*)
        -----------            ----------        ----------
         25                     50583      50584
SQL> set autotrace traceonly

1. 如果是等值查询,那字段无先后之分,结合着索引的存储结构,索引存储的是键值和rowid,复合索引就是存储两个键值,如果是等值查询,可以看作是一个值,跟单个字段索引查询一样。

SQL> select /*+index(test,ind_id_owner)*/* from test    where owner ='TEST' and object_id =52623;
执行计划
----------------------------------------------------------
Plan hash value: 1096520809
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID_OWNER |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=52623 AND "OWNER"='TEST')
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1199  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select /*+index(test,ind_owner_id)*/* from test  where owner ='TEST' and object_id =52623;
执行计划
----------------------------------------------------------
Plan hash value: 724495818
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OWNER_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST' AND "OBJECT_ID"=52623)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1199  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. 如果是一个是等值,一个是范围查询,等值的字段作为前导列好一些。同样结合索引的存储特性,如果是object_id作为前导列,那oracle 会扫描30000,test到50584,***的数据,丢弃owner !=‘TEST’的列。

    如果是owner作为前导列,那oracle 会扫描test,30000到test,50584的数据,没有丢弃。
SQL> select /*+index(test,ind_id_owner)*/* from test  where owner ='TEST' and object_id >=30000;
已选择148行。
执行计划
----------------------------------------------------------
Plan hash value: 1096520809
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1076 |    97K|    90   (2)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |  1076 |    97K|    90   (2)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_ID_OWNER |  1076 |       |    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=30000 AND "OWNER"='TEST' AND "OBJECT_ID" IS NOT NULL)
       filter("OWNER"='TEST')
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        114  consistent gets
          0  physical reads
          0  redo size
      17139  bytes sent via SQL*Net to client
        484  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        148  rows processed
SQL> select /*+index(test,ind_owner_id)*/* from test  where owner ='TEST' and object_id >=30000;
已选择148行。
执行计划
----------------------------------------------------------
Plan hash value: 724495818
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1076 |    97K|    36   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |  1076 |    97K|    36   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OWNER_ID |  1076 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST' AND "OBJECT_ID">=30000 AND "OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
      17139  bytes sent via SQL*Net to client
        484  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        148  rows processed

3. 如果两个条件都是范围查询,则前导列为,单个字段过滤后的数据量接近两个字段过滤后的数据量。以下面的列子来说,最终结果是830条数据,用 owner >='TEST'为1076条,用object_id >= 30000是21029条,这样会减少filter的操作,所以选择owner作为前导列。
SQL> select /*+index(test,ind_id_owner)*/*from test  where owner >='TEST' and object_id >= 30000;
已选择830行。
执行计划
----------------------------------------------------------
Plan hash value: 1096520809
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5377 |   488K|   156   (1)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |  5377 |   488K|   156   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_ID_OWNER |  5377 |       |    73   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=30000 AND "OWNER">='TEST' AND "OBJECT_ID" IS NOT NULL)
       filter("OWNER">='TEST')
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        228  consistent gets
          0  physical reads
          0  redo size
      86578  bytes sent via SQL*Net to client
        990  bytes received via SQL*Net from client
         57  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        830  rows processed
SQL> select /*+index(test,ind_owner_id)*/*from test  where owner >='TEST' and object_id >= 30000;
已选择830行。
执行计划
----------------------------------------------------------
Plan hash value: 724495818
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5377 |   488K|   188   (1)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST         |  5377 |   488K|   188   (1)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IND_OWNER_ID |  5377 |       |    36   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("OWNER">='TEST' AND "OBJECT_ID">=30000 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID">=30000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        164  consistent gets
          0  physical reads
          0  redo size
      86578  bytes sent via SQL*Net to client
        990  bytes received via SQL*Net from client
         57  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        830  rows processed
SQL> set autotrace off
SQL> select count(*) from test where owner >='TEST';
  COUNT(*)
----------
      1076
SQL> select count(*) from test where object_id >= 30000;
  COUNT(*)
----------
     21029

作者:guogang83 发表于2013-9-18 9:51:48 原文链接
阅读:100 评论:0 查看评论

相关 [复合 索引 选择] 推荐:

复合索引的前导列如何选择?

- - CSDN博客数据库推荐文章
     在表上建复合索引是常见的事情,那索引中键值的顺序是什么呢. 通过下面的实验可以得出三个结论:. --制造实验数据,并收集统计信息. 如果是等值查询,那字段无先后之分,结合着索引的存储结构,索引存储的是键值和rowid,复合索引就是存储两个键值,如果是等值查询,可以看作是一个值,跟单个字段索引查询一样.

mysql选择索引

- - CSDN博客数据库推荐文章
1、尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列编制索引. 最适合有索引的数据列是那些在where子句中数据列,在联结子句中出现的数据列,或者是在Group by 、Order by子句中出现的数据列. select 后的数据列最好不要用索引. 2、综合考虑各数据列的维度.

复合索引性能问题初探

- - CSDN博客推荐文章
在《品悟性能优化》一书,4.4.3章节里介绍了复合索引的两个特点:前缀性,可选性. 何为前缀性,该书阐述为排除skip scan index的情况,约束条件如果不包含复合索引的第一列,则该复合索引不会被用到;何为可选性,该书阐述为字段值越多,可选性越强,定位记录越少,查询效率越高. 即查询返回记录少的列应该放在复合索引的前面.

复合地板、竹地板、实木地板 应该如何选择?

- - 知乎每日精选
实木地板:就是整块木材,经过刨削,油漆而得到的地面铺装材料. 多层复合:也就是实木复合地板,常以胶合板为主体,表面贴一层木皮,再油漆. 强化复合地板:也叫强化木地板,标准名称是:浸渍纸层压木质地板,是以密度板为坯料,面上贴木纹纸,耐磨纸,背上贴平衡纸. 竹地板:就是将竹子,劈成竹条,刨去外皮,内皮(即竹青、竹黄)得到尺寸相同的竹片,然后通过胶粘剂,将竹片热压胶合成地板形状的坯料,最后刨削,油漆而得.

从性能的角度谈SQL Server聚集索引键的选择

- - CSDN博客数据库推荐文章
    在SQL Server中,数据是按页进行存放的. 而为表加上聚集索引后,SQL Server对于数据的查找就是按照聚集索引的列作为关键字进行了. 因此对于聚集索引的选择对性能的影响就变得十分重要了. 本文从旨在从性能的角度来谈聚集索引的选择,但这仅仅是从性能方面考虑. 对于有特殊业务要求的表,则需要按实际情况进行选择.

转载 选择

- bravusliu - caowumao的博客

CSS4 选择器

- iVane - 幸福收藏夹
CSS3 还没完全用上,CSS4 已经提上日程. 官方发布了 update to the working Selectors Level 4 spec,对选择器做了一些升级. 前端最大的优点就是技术更新快,可以经常学到新东西;最大的缺点也是技术更新快,要跟上潮流还真不是那么简单. 不过,这次更新有像“父选择器”这样让人兴奋的内容,让我们先睹为快,了解一下吧:.

JQuery 选择器

- - CSDN博客Web前端推荐文章
}

点击我

.    像上面这样把JavaSript代码和HTML代码混杂在一起的做法同样也非常不妥,因为它并没有将网页内容和行为分离,所以才有JQuery选择器的学习.

点击我

. //给class为demo的元素添加行为.

选择性闭嘴

- 蓓 - 土摩托日记
除了熟人之外,文青博客我追看的不多,总数不会超过10个,因为大多数这类博客的营养都欠奉. 一个是连岳,他的感情QA还是挺好看的,某些政论文字也还不错. 但这厮喜欢掺和科学的事儿,不止一次误导过读者. 就拿地震预报来说吧,他哪有资格评论. 看看这个报道,今天距离这则报道正好过去了两个月,可预报的地震仍然没有发生.

jsoup select 选择器

- - 编程语言 - ITeye博客
采用CSS或类似jquery 选择器(selector)语法来处理HTML文档中的数据. 利用方法: Element.select(String selector)和 Elements.select(String selector). Jsoup的元素支持类似CSS或(jquery)的选择器语法的查找匹配的元素,可实现功能强大且鲁棒性好的查询.