Java教程

关于不等于是否可与走索引的问题

本文主要是介绍关于不等于是否可与走索引的问题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

以前在学oracle的时候,不等于不能走索引似乎是一条铁律

SQL> select a,count(*) from unequal_oracle group by a;

         A   COUNT(*)
---------- ----------
         1       2048
         2          1
create index idx_unequal on unequal_oracle(a);
--a=2时,索引的过滤性是比较好的,这里我们取a<>1


--用不等于的查询条件时,oracle的执行计划
SQL>  select * from unequal_oracle where a<>1;
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    20 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| UNEQUAL_ORACLE |     1 |    20 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"<>1)   

实测oracle的堆表上的索引,查询条件为<> 时,走的是全表扫描,不能走到索引

在绝大部分关系型数据中的索引都是B+树索引,索引寻迹原理是一样的,所以在学mysql优化时,很容易把oracle的优化逻辑移植过来

选择率、数据倾斜、索引失效条件、索引寻迹原理、组合索引等等,原理都差不多。

但是索引失效条件中,“查询条件为<>时,无法使用索引”,这条在mysql中同样适用吗?

--innodb表
mysql>  select a,count(*) from unequal_mysql group by a;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |     2048 |
|    2 |        1 |
+------+----------+
create index idx_unequal on unequal_mysql(a);


mysql> explain  select * from unequal_mysql where a<>1;
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | unequal_mysql | NULL       | range | idx_unequal   | idx_unequal | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

查询条件为<>时,mysql可以走索引

因为mysql的表是聚簇索引,相当于oracle的索引组织表(IOT表),而不是oracle的堆表

我们在oracle中创建IOT表,看看执行计划情况

--在oracle中创建IOT表必须带关键字ORGANIZATION INDEX,且必须有主键
SQL>  create table unequal_iot(a number,b varchar2(10),pk number,primary key (pk))    ORGANIZATION INDEX ;
--创造数据后数据如下
SQL> select a,count(*) from unequal_iot group by a;

         A   COUNT(*)
---------- ----------
         1       1024
         2          1
--IOT表上创建索引
create index idx_unequal_iot on unequal_iot(a);

--执行计划
SQL> select * from unequal_iot where a<>1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2102446683

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |    33 |     3   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| SYS_IOT_TOP_87613 |     1 |    33 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"<>1)

这个执行计划看上去访问了索引,实际上这个索引并不是我们在a列上创建的索引。它是表上的簇,也就是主键,也是索引结构,但是叶节点为数据块。

所以这里的INDEX FAST FULL SCAN相当于全表扫描。因为本身如果要扫描出所有的数据块,oracle肯定不会去从主键的root节点开始,从上往下的扫描主键上的所有块。因为B+数的叶节点间本身也是指针连接的,oracle只需要横向扫描所有叶节点就行了,这种横向扫描就是INDEX FAST FULL SCAN。

select * from unequal_iot where a<>1这个sql不走a列的索引,而走簇,也可能是因为oracle认为回表代价较高。所有为了排除这个想法,我们用索引覆盖扫描再做一次实验

--只查a列,从原理看,走a列的索引效率是比较高的
SQL>  select a from unequal_iot where a<>1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2102446683

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| SYS_IOT_TOP_87613 |     1 |    13 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

但是oracle还是走了簇,没有走索引

 

所以,对于关于不等于是否可与走索引的问题,我们基本上可以得出这样的结论

1.oracle库上的表(堆表和IOT表),查询条件为不等于时,不可以走索引

2.mysql的表,查询条件为不等于时,可以走索引

 

那么为什么会造成这样的差异呢?

从表的数据结构上看都是IOT或者聚簇索引结构,从索引结构上看都是B+树索引。在数据结构和访问方式上没有看出什么区别。

来看看<>时,索引的访问路径。

 <>时,优化器不知道数据到底放在索引的哪个位置,只能通过索引全部扫描的方式去访问。索引全部扫描实际上不一定会比全表扫描更优。

oracle在访问索引是一般使用单块读,在访问全表时一般会使用多块读,多块读可以一次放磁盘中的多个块到内存中,多块读可以减少IO次数,对于oracle来说全索引扫描的代价不一定比全表扫描低。无论是堆表还是IOT表,在建表时数据地址一般会连续,可以满足多块读的条件。

mysql没有多块读的概念,有类似多块读的预读的概念。mysql在连续读取多个page时,预读读取的是整个extent,无论是触发机制还是读取块的范围,跟多块读还是有区别的。但从功能上看预读没有多块读更高效,更像是多块读的简易版。

在<>时,无论是访问全表还是访问索引,效率都是挺差的,mysql和oracle根据各自的数据库特性选择执行计划

 

 

这篇关于关于不等于是否可与走索引的问题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!