Mysql索引合并

例如有如下表emps，表结构如下：

mysql> desc emps;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| emp_no     | int           | NO   | MUL | NULL    |                |
| birth_date | date          | NO   |     | NULL    |                |
| first_name | varchar(14)   | NO   |     | NULL    |                |
| last_name  | varchar(16)   | NO   |     | NULL    |                |
| gender     | enum('m','f') | NO   | MUL | NULL    |                |
| hire_date  | date          | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

在字段first_name和last_name上各有一个单独的索引

mysql> show indexes from emps;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emps  |          0 | PRIMARY  |            1 | id          | A         |      299150 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | fn_ck    |            1 | first_name  | A         |        1251 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emps  |          1 | ln_ck    |            1 | last_name   | A         |        1618 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

执行查询：select * from emps where first_name=‘Sumant’ and last_name=‘Peac’;
会使用first_name上的索引呢？还是last_name上的索引呢？或者两个索引都用到，又或者两个索引都用不到。

mysql> desc format=tree select * from emps where first_name='Sumant' and last_name='Peac';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((emps.last_name = 'Peac') and (emps.first_name = 'Sumant'))  (cost=5.51 rows=1)
    -> Intersect rows sorted by row ID  (cost=5.51 rows=1)
        -> Index range scan on emps using ln_ck over (last_name = 'Peac')  (cost=2.54 rows=183)
        -> Index range scan on emps using fn_ck over (first_name = 'Sumant')  (cost=2.86 rows=249)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从上面的执行计划看到两个索引都使用到了。

在MySQL中，如果where条件中的两个或多个字段上有单独的索引，会使用一种叫做索引合并的技术，使sql能使用到多个单独的索引，以提高查询效率。

索引合并有三种算法:Index Merge Intersection，Index Merge Union，Index Merge Sort-Union。Sort-Union和Union区别为Sort-Union返回行之前会根据row id进行排序。

索引合并功能可以通过优化器参数打开和关闭。

mysql> SELECT @@optimizer_switch\G  
index_merge={on|off}  
index_merge_intersection={on|off}  
index_merge_sort_union={on|off}  
index_merge_union={on|off} 

索引合并的原理：

1.扫描last_name的索引树，获取满足条件的(last_name,primary key)
2.扫描first_name的索引树，获取满足条件的(last_name,primary key)
3.1、2步骤的两个结果集根据primary key取交集
4.按照第3步中的结果扫描主键树，获取满足条件的行返回给客户端