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

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

以前在学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根据各自的数据库特性选择执行计划

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