关于组合索引的第二列为in时是否可用走索引的问题

组合索引的第二列为in时是否可用走索引？当时面试直接把我问蒙了。一般来说in会等价改写成or，然后走索引。比如

where a in (1,2)

等价于

where a=1 or a=2

在oracle中肯定会这样去改写，所以该走索引的还是会走索引。但是这个条件放在组合索引的第二列呢？

首先等价改写同样成立，比如

where a in (1,2) and b=0

等价于

where (a=1 or a=2) and b=0

优化器是否会这样做呢？这只有测试了才知道

 

oracle测试

 --单列索引
 create index idx_tlzl on tlzl(a);

  1* select * from tlzl where a in (1,2)
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     2 |    40 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TLZL     |     2 |    40 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_TLZL |     4 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("A"=1 OR "A"=2)



--复合索引
create index idx_tlzl2 on tlzl(b,a);


--仅指定第二列的条件时，oracle选择走全表扫描
--其实oracle还有索引跳跃扫描的方案，因为b列只有1个值，全表扫描的效率还是要高一些
SQL> select * from tlzl where a in (1,2);


Execution Plan
----------------------------------------------------------
Plan hash value: 925964718

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    40 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TLZL |     2 |    40 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("A"=1 OR "A"=2)
--oracle做了等价改写，in改写成or



--第一列和第二列都有条件时，可以走索引，索引范围扫描
--且做了等价改写
SQL> select * from tlzl where a in (1,2) and b='a';


Execution Plan
----------------------------------------------------------
Plan hash value: 3195115994

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TLZL2 |     2 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access("B"='a' AND ("A"=1 OR "A"=2))

 

mysql测试

--单列索引
mysql> create index idx_tlzl on tlzl(a);


--单列索引时，条件为in时，mysql可以走索引，走索引范围扫描range
mysql> explain select * from tlzl where a in (1,2);
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tlzl  | NULL       | range | idx_tlzl      | idx_tlzl | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.06 sec)


--符合索引
mysql> create  index idx_tlzl2 on tlzl(b,a);

--符合索引只有第二列的条件
--mysql走了索引，但是是index，表示索引全扫描
mysql>  explain select * from tlzl where a in (1,2);
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tlzl  | NULL       | index | NULL          | idx_tlzl2 | 38      | NULL | 1000 |    20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)


--符合索引有第一列和第二列的条件
--mysql可以走索引，索引范围扫描
mysql>  explain select * from tlzl where a in (1,2) and  b='a';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tlzl  | NULL       | range | idx_tlzl2     | idx_tlzl2 | 38      | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)   

 

 

总结：

1.无论是理论上还是实际上，组合索引的第二列为in是可以走索引的

2.有些细枝末节确实不太容易提前测试到，但是要相信自己的逻辑推理判断