复合索引中前导列对sql查询的影响

最新推荐文章于 2023-02-03 23:41:38 发布
最新推荐文章于 2023-02-03 23:41:38 发布
阅读量151 收藏
点赞数
文章标签： 数据库

[oracle@ora10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 21 14:35:56 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/tiger
Connected.

SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

在三个列上创建复合索引
SQL> create index emp_index on emp(EMPNO,ENAME,DEPTNO);
Index created.

SQL> set autotrace on explain;

通过下面的测试来分析sql查询在不同谓词的情况下是否会使用索引

测试1
SQL> select * from emp where EMPNO=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7900)


测试2
SQL> select * from emp where EMPNO=7788 and ENAME='SCOTT';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='SCOTT')
   2 - access("EMPNO"=7788)


测试3
SQL> select * from emp where EMPNO=7566 and ENAME='MANAGER' and DEPTNO=20;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='MANAGER' AND "DEPTNO"=20)
   2 - access("EMPNO"=7566)


测试4
SQL> select * from emp where ENAME='BLAKE';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='BLAKE')


 测试5
SQL> select * from emp where ENAME='ADAMS' and DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='ADAMS' AND "DEPTNO"=20)
   
 测试6  
 SQL> select * from emp where DEPTNO=30 and ENAME='TURNER';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ENAME"='TURNER' AND "DEPTNO"=30)


测试7
SQL> select * from emp where DEPTNO=10 and EMPNO=7934;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=10)
   2 - access("EMPNO"=7934)

测试8   
SQL> select * from emp where EMPNO=7521 or EMPNO=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

Execution Plan
----------------------------------------------------------
Plan hash value: 339106968
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |    74 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP       |     2 |    74 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_INDEX |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPNO"=7521 OR "EMPNO"=7839)

测试9
SQL> select * from emp where EMPNO in(7844,7902);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 339106968
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |    74 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP       |     2 |    74 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_INDEX |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPNO"=7844 OR "EMPNO"=7902)

结论：有上述测试可以看出，如果sql查询的谓词中没有复合索引中的前导列（EMPNO），则查询不走索引。可见创建复合索引时前导列的选择至关重要。
   
   

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

转载于:http://blog.itpub.net/25373498/viewspace-1306086/

cuixiuyin9014
关注
SQLServer的复合索引学习.docx
01-16
4. 如果一个复合索引的前导列（即索引定义中的第一列）是最常用于查询的列，那么这个索引将更加有效。例如，如果`fariqi`列比`neibuyonghu`列更常被用作查询条件，那么`fariqi`应该放在复合索引的前面。 5. 在同一列...
MySQL复合索引探究
minihuabei的博客
06-17 1917
mysql索引
组合索引中列的顺序问题
Robo's Blog【海阔凭鱼跃,天高任鸟飞】
10-28 2944
组合索引中列的顺序对执行计划的影响比较大。下面先简单介绍组合索引的适用场景，后面再根据一个具体的SQL说说组合索引中列的顺序对DM7执行计划的影响。 1、使用组合索引查询的场景 兹有 Index (A,B,C) ——组合索引多字段是有序的，并且是个完整的BTree 索引。 下面条件可以用该组合索引查询： A>5 A=5 AND B>6 A=5 AND B=6 AND C=7 A=5 AND B IN (2,3) AND C>5 下面条件将不能用该组合索引查询： B&.
mysql 前导列_MySQL 各个索引的使用详解
weixin_33327330的博客
02-07 342
一. 慢查询日志1.1 MySQL的日志类型日志用于记录数据库的运行情况，以及用户对数据库执行的各类操作。当数据库发生故障时，可以根据日志分析和解决问题，从而对数据库进行恢复。1.2 认识慢查询日志慢查询日志用于记录MySQL数据库中响应时间超过指定阈值的语句。慢查询日志通常也被称之为慢日志，因为它不仅仅只针对SELECT语句，像INSERT、UPDATE、DELETE等语句，只要响应时间超过所设...
MySQL-explain-extra字段详解
最新发布
浩然的博客
02-03 1668
MySQL的explain中extra分析
mysql 前导列_组合索引如何选择前导列的几点考虑
weixin_33669611的博客
01-28 599
? 选择组合索引的前导列，必须根据具体的业务(SQL)写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的， 本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更?选择组合索引的前导列，必须根据具体的业务(SQL)写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没...
浅谈SQL Server中索引的使用.pdf
09-19
在需要同时查询多个列时，可以考虑建立复合索引，通常将查询中引用最频繁的列作为复合索引的前导列，有助于形成覆盖查询，从而提高查询效率。 此外，索引的唯一性也是一个重要的考虑因素。如果一个索引键的所有值都...
sql.rar_提高SQL查询效率的30种方法
09-21
复合索引适用于多个字段的联合查询。 2. **避免全表扫描**：尽量避免在where子句中使用不等式或不包含索引的函数，这会导致全表扫描。 3. **优化JOIN操作**：减少JOIN的数量，合理设计JOIN条件，并确保JOIN的列有...
sqlserver 索引的一些总结
09-11
联合索引是多个列的组合，复合索引则根据列的顺序决定其效率，首列具有高选择性时效果最佳。 9. 索引失效 当查询条件使用了函数、计算表达式或索引列的非前导部分时，可能无法利用索引，导致全表扫描。 在进行...
MySQL索引失效的几种情况汇总
12-14
在MySQL中，单列索引不会存储`NULL`值，而复合索引则不存储所有列都是`NULL`的值。这是因为`NULL`值在比较操作中具有特殊性，不适用于大多数计算，这使得`NULL`值无法参与索引的构建过程。因此，当查询条件为`IS ...
什么复合索引前导列？
文艺地工作着
06-20 7468
在oracle中，我们经常以为建立了索引，sql查询的时候就会如我们所希望的那样使用索引，事实上，oracle只会在一定条件下使用索引，这里我们总结数第一点：oracle会在条件中包含了前导列时使用索引，即查询条件中必须使用索引中的第一个列，请看下面的例子SQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------BONUS  
mysql的索引 优化 前导列
qq_37609701的博客
05-04 3221
--组合索引之--前导列----------------------------------------------------------------1）对于where子句中出现的列要想索引生效，会有一些限制，这就和前导列有关。2）所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,...
高效的SQL【Composite Indexes（最佳前导列的选择）】
12-21 661
Composite Indexes（最佳前导列的选择）   一、前导列是否有序对composite indexes的影响大   1、创建表t1(x,y)  x有序列，y随意列 doudou@TEST> begin   2  for i in 1.. 1000000 loop   3  insert into t1 values (i,to_char(dbms_random.rando
mysql组合索引前导列
住进时光里
05-11 1533
什么是组合索引前导列？ CREATE TABLE `test_user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `age` int(3) DEFAULT NULL, `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `update_date` times
使用索引的误区之一：没有使用复合索引的前导列导致查询不使用索引
lunar的小铺
11-24 3129
使用索引的误区之一：没有使用复合索引的前导列导致查询不使用索引在oracle中，我们经常以为建立了索引，sql查询的时候就会如我们所希望的那样使用索引，事实上，oracle只会在一定条件下使用索引，这里我们总结数第一点：oracle会在条件中包含了前导列时使用索引，即查询条件中必须使用索引中的第一个列，请看下面的例子SQL> select * from tab; TNAME    
【Oracle index】组合索引如何选择前导列的几点考虑
mjj291268154的专栏
08-22 3630
选择组合索引的前导列，必须根据具体的业务（SQL）写法和列的数据分布不同而不同，很多书或网上都说，前导列要选择高选择性的，但是，脱离具体的业务，这些是没有意义的，本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉，实际应用中，有更多复杂的情况需要具体分析。 1.都是等值条件的列，谁做前导列都一样 DROP TABLE t; CREATE TABLE t 
模糊查询%在最前面，能否或者怎么使用索引
Hpsyche的博客
10-07 6267
前言 众所周知，在索引时，如果模糊查询的%置于最前面，索引会失效。 但之前面试时，面试官问了我一个问题：%前置的情况，什么情况下会使用到索引？ 借此博客，说明下。 正文 解决方式一 反转模糊查询的字段，但是注意，对于"%keywork%"的索引，此方法是无效的。 如 select * from student where name like '%三'; 我们可以改造为： select * fro...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值