例如有如下表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步中的结果扫描主键树，获取满足条件的行返回给客户端
