索引失效之Or连接条件

用or分割开的条件， 如果or前的条件和后面的列中有一个没有索引，那么涉及的索引都不会被用到。

DB2：

db2expln -d test -statement "select * from employee where id=987 or  name='lisy'" -terminal -graph -opids
Optimizer Plan:

                        Rows   
                      Operato
                        (ID)   
                        Cost   
                              
                      1.99999 
                      RETURN  
                       ( 1)   
                      40.6023 
                        |     
                      1.99999 
                       FETCH  
                       (--)   
                      40.6023 
                     /       \
             1.99999           200003  
             RIDSCN           Table:   
              ( 3)            DB2INST1 
             27.0743          EMPLOYEE 
            /       \
        1               1    
      SORT            SORT   
      ( 4)            ( 6)   
     13.5372         13.5372 
       |               |    
       1               1    
     IXSCAN          IXSCAN 
      ( 5)            ( 7)  
     13.537          13.537 
      |               |         
    200003             4        
 Index:         Index:          
 DB2INST1       DB2INST1        
 EMPLOYEE_IDX1  EMPLOYEE_INDEX2 
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "drop index employee_index2"
DB20000I  The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2expln -d test -statement "select * from employee where id=987 or  name='lisy'" -terminal -graph -opids
Optimizer Plan:

    Rows   
  Operator 
    (ID)   
    Cost   
          
  1.99999 
  RETURN  
   ( 1)   
  1247.08 
    |     
  1.99999 
  TBSCAN  
   ( 2)   
  1247.08 
    |     
  200003  
 Table:   
 DB2INST1 
 EMPLOYEE
 
 [db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create index employee_idx2 on employee(name)"
DB20000I  The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "drop index employee_idx1"
DB20000I  The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2expln -d test -statement "select * from employee where id=987 or  name='lisy'" -terminal -graph -opids

End of section

Optimizer Plan:

    Rows   
  Operator 
    (ID)   
    Cost   
          
  1.99999 
  RETURN  
   ( 1)   
  1247.08 
    |     
  1.99999 
  TBSCAN  
   ( 2)   
  1247.08 
    |     
  200003  
 Table:   
 DB2INST1 
 EMPLOYEE
 
 MYSQL；
 mysql>  explain select * from employee where id=987 or  name='lisy';
+----+-------------+----------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table    | partitions | type        | possible_keys                 | key                           | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+----------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | employee | NULL       | index_merge | employee_idx1,employee_index2 | employee_idx1,employee_index2 | 5,43    | NULL |    2 |   100.00 | Using union(employee_idx1,employee_index2); Using where |
+----+-------------+----------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> drop index employee_index2 on employee;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  explain select * from employee where id=987 or  name='lisy';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | employee_idx1 | NULL | NULL    | NULL | 199799 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index employee_idx2 on employee(name);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index employee_idx1  on employee;               
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  explain select * from employee where id=987 or  name='lisy';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | employee_idx2 | NULL | NULL    | NULL | 199799 |    19.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)