数据库多索引合并查询

                            数据库多索引合并查询

     当数据库在处理带有where条件的查询时，在查询中有多个谓词，且每个谓词都
有索引时，对于oracle来说会选择过滤性最强的索引，而mysql使用了索引合并的技术
对两个索引进行合并，然后再查询.

创建以下测试数据，并分别在object_id,object_name两个列上创建索引   

SQL> select object_id,object_name from t1 where object_id<20 order by object_id;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
         1 C_OBJ#
         1 C_FILE#_BLOCK#
         1 I_TS#
         1 I_OBJ#
         1 I_FILE#_BLOCK#
         1 TAB$
         1 C_TS#
         1 CLU$
        10 test
        11 test
        12 test

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        13 test
        14 test
        15 test
        16 test
        17 test
        18 test
        19 test

18 rows selected.

SQL> create index ix_object_id on t1(object_id);

Index created.

SQL> create index ix_object_name on t1(object_name);

Index created.

SQL> select *
  2  from t1
  3  where object_id=1
  4  and object_name='test';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1509370966

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   115 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_OBJECT_ID |     8 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='test')
   2 - access("OBJECT_ID"=1)

从执行计划来看，oracle选择了IX_OBJECT_ID这个索引，而另一个where条件中的谓词使用过滤操作来实现.

 我们可以通过对每个where条件索引列去过滤，然后找出满足条件的rowid,再在rowid上做一个连接，那么
这样找出的rowid就是满足两个条件的，然后根据这个rowid去访问表，但是oracle没有这样的优化，手动写出
这种算法的sql如下.

SQL> select /*+ NO_QUERY_TRANSFORMATION */
  2  *
  3  from t1
  4  where rowid in
  5   (select x.id
  6     from (select rowid as id from t1 where object_id=1) x,(select rowid as id from t1 where object_name='test') y
  7     where x.id=y.id)
  8  ;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1542144853

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |   115 |   174K  (1)| 00:00:29 |
|*  1 |  FILTER                       |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL           | T1   | 91570 |    10M|   419   (1)| 00:00:01 |
|*  3 |   HASH JOIN SEMI              |      |     1 |    24 |     2   (0)| 00:00:01 |
|   4 |    VIEW                       |      |     1 |    12 |     1   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS BY USER ROWID| T1   |     1 |    17 |     1   (0)| 00:00:01 |
|   6 |    VIEW                       |      |     1 |    12 |     1   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY USER ROWID| T1   |     1 |    37 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM  (SELECT ROWID "ID" FROM "T1" "T1" WHERE
              ROWID=:B1 AND "OBJECT_NAME"='test') "Y", (SELECT ROWID "ID" FROM "T1" "T1"
              WHERE ROWID=:B2 AND "OBJECT_ID"=1) "X" WHERE "X"."ID"="Y"."ID"))
   3 - access("X"."ID"="Y"."ID")
   5 - filter("OBJECT_ID"=1)
   7 - filter("OBJECT_NAME"='test')

再看来看mysql的处理,mysql要想实现索引合并，需要打开insert_merge的提示开关.

mysql> show global variables like '%optimizer_switch%';
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                            |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select *
    -> from t1
    -> where object_id=1
    -> and object_name='test';
+----+-------------+-------+-------------+-----------------------------+-----------------------------+---------+------+------+------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys               | key                         | key_len | ref  | rows | Extra                                                                  |
+----+-------------+-------+-------------+-----------------------------+-----------------------------+---------+------+------+------------------------------------------------------------------------+
|  1 | SIMPLE      | t1    | index_merge | ix_object_id,ix_object_name | ix_object_id,ix_object_name | 5,303   | NULL |    4 | Using intersect(ix_object_id,ix_object_name); Using where; Using index |
+----+-------------+-------+-------------+-----------------------------+-----------------------------+---------+------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

从extra和key的两个栏位信息来看，mysql合并了两个索引，然后再做查询，row是4

 如果我们关闭了索引合并，index_merge=off.这样的情况下,mysql的行为就很类似于oracle了.

mysql> set optimizer_switch='index_merge=off,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select *
    -> from t1
    -> where object_id=1
    -> and object_name='test';
+----+-------------+-------+------+-----------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys               | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | ix_object_id,ix_object_name | ix_object_id | 5       | const |    8 | Using where |
+----+-------------+-------+------+-----------------------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

可以看到mysql只选择了一个索引ix_object_id,rows已经评估为8了.