关于java:查询中有没有可能多个索引一起用呢

其实咱们之前所讲的回表，就是两个索引树同时应用，先在二级索引树中搜寻到对应的主键值，而后在再去主键索引树中查问残缺的记录。

然而我明天的问题是，两个不同的二级索引树，会同时失效吗？实践上来说，应该是能够同时失效的，不然这个 MySQL 也太笨了。不过依据松哥日常开发教训，这种事件最好可能防止，如果产生了同时搜寻两棵索引树的事件，大略是你的索引设计有问题，此时就要去检查一下索引的设计是否正当。

加粗的是实践经验，然而对于两个索引同时失效的知识点，咱们还是要懂，一起来看下。

1. 索引合并

例如我有如下一张表构造：

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`),
  KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这个表里边有 username 和 address 两个索引，留神是两个索引，每个索引中有一个字段，这不是联结索引。

当初我的查问 SQL 如下：

select * from user where username='1' or address='1';

搜寻条件有两个，username 和 address，这是两个索引，分属于两棵不同的索引树。那么它在搜寻的时候会两棵索引树都去搜寻吗？还是只搜寻一颗索引树，再用另一个搜寻条件过滤第一棵树搜寻进去的后果？

咱们来看下数据库执行打算：

大抵上瞥一眼这个执行打算，大家也能猜出来，这里其实两个索引都用到了，在这个执行打算中有几个新面孔：

• type 为 index_merge。
• Extra 为 Using union(username,address); Using where。

这个 type 中的 index_merge 就是索引合并。

2. 旧版玩法

当然这个 index_merge 并不是一开始就有的，这是从 MySQL5.0 开始引入的货色。尽管大家当初根本山不会再用到 MySQL5.0 之前的版本了，然而我这里还是说一下，加深大家对 MySQL 的了解。在 MySQL5.0 之前，对于咱们下面给出的查问 SQL，是不会走索引的，会全表扫描。在那个年代，如果你想实现下面这个查问，然而又想走索引，你的 SQL 得这样写：

select * from user where username='1' union all select * from user where address='1' and username!='1'

不过这种写法很显著有点蠢笨。

所以，从 MySQL5.0 开始，在查问中能够主动应用多个索引进行扫描，并将后果进行合并，也就是咱们后面所说的索引合并（index_merge）。

3. 三种状况

索引合并这种算法有三个变种，咱们别离来看。

3.1 union

这是求两个索引的并集。

咱们来看如下 SQL：

select * from user where username like '1%' or address like '1%';

这个 SQL 在执行的过程中就会波及到两个索引，须要去两棵索引树中进行搜寻，再对搜寻后果求并集，咱们来看一下该 SQL 的执行打算：

能够看到，这个执行打算中曾经产生了索引合并（看 type、key、Extra）。

那么是不是只有是两个索引查问就总会发送索引合并呢？咱们再来看一个栗子：

select * from user where username>'a' or address='1';

大家看一下，只是搜寻条件变了一下而已，这里就没用索引合并了，而变成了全表扫描，这是为什么呢？这就引出来索引合并的一个条件，即：每个索引对应的搜寻条件，搜到的主键必须是有序的，如果搜到的主键是无序的，道歉，索引合并用不了。在二级索引中，数据依照二级索引的程序进行排序，构造相似上面这样：

当 username 雷同的时候，主键是有序的，当 username 不同的时候，就不能保障主键有序了，如果获取到的主键无序，就无奈实现索引合并了。

这又引出来一个问题，为什么获取到的主键有序能力产生索引合并呢？因为只有当主键是有序的，未来去重（union、sort-union）亦或者求交加（intersect），效率都要高一些。

从 MySQL5.0 开始，索引合并默认是开启的，当然你也能够抉择敞开，敞开 union 索引合并形式如下：

SET optimizer_switch = 'index_merge_union=off';

敞开之后再来看执行打算：

大家看到，仍然产生了索引合并，然而这次不是 union，而是 sort_union 了，那咱们接下来就来看下什么是 sort_union。

3.2 sort_union

sort_union 基本上和 union 一样，只是多了一个排序的能力。

因为后面咱们说，如果获取到无序的主键，就不会产生索引合并，可能最终会间接上全表扫描。因而 MySQL 里边又搞了一个 sort_union，就是先在 username 索引树和 address 索引树中同时进行搜寻，别离拿到主键值之后先进行排序，排序完了再进行去重，而后回表拿残缺的数据。

和 union 相比次要是多了加粗的那一步。

那咱们持续，敞开 sort_union，如下：

SET optimizer_switch = 'index_merge_sort_union=off';

敞开之后，再去看执行打算，如下：

此时就没有索引合并了，间接全表扫描。

3.3 intersect

这个是求两个索引的交加。

例如如下 SQL：

select * from user where username like '1%' and address like '1%';

这个 SQL 在执行的过程中就有可能呈现求交加的状况。当然这并非相对的，具体还要看优化器优化后的状况。

松哥尝试了很久，没法复现一个例子进去，次要是我的模仿数据不太对味。如果小伙伴们有现成的 Using intersect 例子欢送留言分享（执行打算 Extra 中会呈现 Using intersect 的）。

然而我把这个原理这里和大家分享下，咱们来看如下一张图：

假如有二级索引 S 和二级索引 T，当初穿插获取主键（这里有一点须要留神，如果咱们是独自在 S 和 T 上搜寻，且 S 上搜寻条件是 username like '1%'，T 上的搜寻条件是 address like '1%'，那么在搜寻的过程中，各自拿到的主键 id 是有序的，这也是 intersect 的前提）：

1. 首先去二级索引 S 下来搜寻，找到第一条满足条件的记录，因为二级索引的叶子结点保留的是主键值，此时拿到主键值之后，先不要急着回表。
2. 接下来去二级索引 T 下来搜寻，找到第一条满足条件的记录，并且拿到对应的主键值。
3. 比拟第一步和第二步搜寻拿到的主键值：
   3.1 如果主键值不相等，则舍弃值小的主键，留下大的主键，下一次在 S 上搜寻的时候，就拿着这个大的主键和 S 上搜寻进去的主键进行比拟。
   3.2 如果主键值相等，则阐明这个主键是满足搜寻条件的，那就拿着这个主键回表。
4. 反复前三步，直到各自索引中没有满足条件的记录为止。

这就是所谓的 穿插获取主键。

好啦，这就是索引合并的三种状况。

4. 小结

很多小伙伴可能会说，既然有索引合并，是不是我索引就能够轻易建设了？nonono！索引合并是一种不得已而为之的方法，如果产生了索引合并，大概率是你设计的索引不太正当导致的，所以咱们应该去推敲该如何优化索引。

参考资料：

• https://dev.mysql.com/doc/ref…
• 《MySQL 是怎么运行的》
• 《高性能 MySQL》
• https://www.modb.pro/db/29619