复合索引性能问题初探
- - CSDN博客推荐文章在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性. 何为前缀性，该书阐述为排除skip scan index的情况，约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字段值越多，可选性越强，定位记录越少，查询效率越高. 即查询返回记录少的列应该放在复合索引的前面.
复合索引性能问题初探
标签：
复合
索引
性能
| 发表时间：2014-04-22 19:26 | 作者：zh309601335
出处：http://blog.csdn.net
在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性。何为前缀性，该书阐述为排除skip scan index的情况，约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字段值越多，可选性越强，定位记录越少，查询效率越高。即查询返回记录少的列应该放在复合索引的前面。
而在《收货不止oracle》一书，5.2.1.9章节里也介绍了复合索引的两个特点：①在等值查询的情况下，复合索引的列无论那一列在前，性能都一样。②复合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才是高效的。
根据上述对复合索引两位作者的不同见解之处，我们通过测试，辨别下实事的真相。测试环境是11.2.0.3单实例，oracle linux 5.4
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
109971
SQL> select count(distinct object_type) from t;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
45
SQL> select count(distinct object_id) from t;
COUNT(DISTINCTOBJECT_ID)
------------------------
109971
SQL> create index ind_t_obj_id on t(object_id,object_type);
Index created.
SQL> create index ind_t_obj_ty on t(object_type,object_id);
Index created.
SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id=5585 and object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=5585)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 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(t,ind_t_obj_id) */ * from T where object_id=5585 and object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=5585 AND "OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 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(t,ind_t_obj_ty) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 912 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 9 | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。
而在《收货不止oracle》一书，5.2.1.9章节里也介绍了复合索引的两个特点：①在等值查询的情况下，复合索引的列无论那一列在前，性能都一样。②复合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才是高效的。
根据上述对复合索引两位作者的不同见解之处，我们通过测试，辨别下实事的真相。测试环境是11.2.0.3单实例，oracle linux 5.4
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
109971
SQL> select count(distinct object_type) from t;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
45
SQL> select count(distinct object_id) from t;
COUNT(DISTINCTOBJECT_ID)
------------------------
109971
SQL> create index ind_t_obj_id on t(object_id,object_type);
Index created.
SQL> create index ind_t_obj_ty on t(object_type,object_id);
Index created.
SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id=5585 and object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=5585)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 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(t,ind_t_obj_id) */ * from T where object_id=5585 and object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=5585 AND "OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 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(t,ind_t_obj_ty) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 912 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 9 | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。
作者：zh309601335 发表于2014-4-22 11:26:53 原文链接
阅读：0 评论：0 查看评论
相关 [复合 索引 性能] 推荐：
复合索引的前导列如何选择？
- - CSDN博客数据库推荐文章 在表上建复合索引是常见的事情，那索引中键值的顺序是什么呢. 通过下面的实验可以得出三个结论：. --制造实验数据，并收集统计信息. 如果是等值查询，那字段无先后之分，结合着索引的存储结构，索引存储的是键值和rowid，复合索引就是存储两个键值，如果是等值查询，可以看作是一个值，跟单个字段索引查询一样.
mongodb索引讲解与性能调优
- - haohtml's blogmongodb索引规则基本上与传统的关系库一样，大部分优化MySQL/Oracle/SQLite索引的技巧也适用于mongodb. 当查询中用到某些条件时，可以对该键建立索引，以提高查询速度. 如果数据量很多且查询多于更新时，可以用索引提高查询的速度. a) 查询索引：. 查询索引很简单，比如说需要查询mailaccess数据库中的Mail collection上的索引时：.
《高性能 MySQL》 — 第五章 创建高性能的索引
- - 码蜂笔记索引的使用：现在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行. 对于有多列的索引，MySQL 只能高效地使用索引的最左前缀列. MySQL 的唯一限制和主键限制都是通过索引实现. MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准. B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相等，适合范围查询.
SQL Server 查询性能优化——堆表、碎片与索引（一）
- - 博客园_首页 SQL Server在堆表中查询数据时，是不知道到底有多少数据行符合你所指定的查找条件，它将根据指定的查询条件把数据表的全部数据都查找一遍. 如果有可采用的索引，SQL Server只需要在索引层级查找每个索引分页的数据，再抓出所需要的少量数据分页即可. 访问数据表内数以万计的数据分页与只访问少数索引的分页两者间的差异，让索引变成效能调校的最佳工具.
数据库查询性能优化之利器—索引（一）
- - 博客园_首页 数据库查询性能优化之利器—索引（一）. 最近在做基于Android的公交查询系统的过程中，遇到一个很棘手的问题：换乘算法效率低. 在直达查询和一次换乘查询的时候，问题体现的还不是很明显，能够在1s之内查询出乘车方案，而当进行二次查询的时候，基本要等一两分钟才能查询出换乘方案，这对于公交查询系统是绝对无法容忍的.
不同SSD盘组合搜索引擎单机性能测试
- - 搜索技术博客－淘宝测试不同SSD盘组合策略HA3引擎性能，寻找到较好的SSD盘组合策略. a) IO调度策略：deadline（sudo echo deadline > /sys/block/sdb/queue/scheduler）. b) 预读策略：关闭系统预读（sudo blockdev –setra 0 /dev/sdb1）.
Linux常用性能调优工具索引
- - 系统技术非业余研究原创文章，转载请注明： 转载自 系统技术非业余研究. Linux常用性能调优工具索引. 前段时间看到brendangregg的 Linux Performance Analysis and Tools PPT里面提到Linux常用性能调优工具, 见下图：. 其中提到了的工具，大部分我之前有推荐过或者在实践的案例里面使用过,这里方便大家索引下：.
SQL Server 查询性能优化——创建索引原则（一）
- - 博客园_首页索引是提高查询性能的一个重要工具，索引就是把查询语句所需要的少量数据添加到索引分页中，这样访问数据时只要访问少数索引的分页就可以. 但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好. 索引建少了，用WHERE子句找数据效率低，不利于查找数据. 索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER除了要更新数据表本身，还要连带地立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间.
电商搜索引擎的架构设计和性能优化
- - SegmentFault 最新的文章「 OneAPM 技术公开课」由应用性能管理第一品牌. OneAPM 发起，内容面向 IT 开发和运维人员. 云集技术牛人、知名架构师、实践专家共同探讨技术热点. 本文系「OneAPM 技术公开课」第一期演讲嘉宾前当当网高级架构师吴英昊的演讲整理：. 首先，非常感谢 OneAPM 技术公开课举办的这次活动.