做网站就找喇叭人,北京加盟网站建设,网站代理游戏,wordpress静态生成页面其实我们之前所讲的回表#xff0c;就是两个索引树同时使用#xff0c;先在二级索引树中搜索到对应的主键值#xff0c;然后在再去主键索引树中查询完整的记录。但是我今天的问题是#xff0c;两个不同的二级索引树#xff0c;会同时生效吗#xff1f;理论上来说#xf… 其实我们之前所讲的回表就是两个索引树同时使用先在二级索引树中搜索到对应的主键值然后在再去主键索引树中查询完整的记录。但是我今天的问题是两个不同的二级索引树会同时生效吗理论上来说应该是可以同时生效的不然这个 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)
) ENGINEInnoDB AUTO_INCREMENT100001 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;这个表里边有 username 和 address 两个索引注意是两个索引每个索引中有一个字段这不是联合索引。现在我的查询 SQL 如下select * from user where username1 or address1;搜索条件有两个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 username1 union all select * from user where address1 and username!1不过这种写法很明显有点笨拙。所以从 MySQL5.0 开始在查询中可以自动使用多个索引进行扫描并将结果进行合并也就是我们前面所说的索引合并index_merge。3. 三种情况索引合并这种算法有三个变种我们分别来看。3.1 union这是求两个索引的并集。我们来看如下 SQLselect * from user where username like 1% or address like 1%;这个 SQL 在执行的过程中就会涉及到两个索引需要去两棵索引树中进行搜索再对搜索结果求并集我们来看一下该 SQL 的执行计划可以看到这个执行计划中已经发生了索引合并看 type 、key、Extra。那么是不是只要是两个索引查询就总会发送索引合并呢我们再来看一个栗子select * from user where usernamea or address1;大家看一下只是搜索条件变了一下而已这里就没用索引合并了而变成了全表扫描这是为什么呢这就引出来索引合并的一个条件即每个索引对应的搜索条件搜到的主键必须是有序的如果搜到的主键是无序的抱歉索引合并用不了。在二级索引中数据按照二级索引的顺序进行排序结构类似下面这样username主键a20b30c9c10c18d1d5当 username 相同的时候主键是有序的当 username 不同的时候就不能保证主键有序了如果获取到的主键无序就无法实现索引合并了。这又引出来一个问题为什么获取到的主键有序才能发生索引合并呢因为只有当主键是有序的将来去重union、sort-union亦或者求交集intersect效率都要高一些。从 MySQL5.0 开始索引合并默认是开启的当然你也可以选择关闭关闭 union 索引合并方式如下SET optimizer_switch index_merge_unionoff;关闭之后再来看执行计划大家看到依然发生了索引合并但是这次不是 union而是 sort_union 了那我们接下来就来看下什么是 sort_union。3.2 sort_unionsort_union 基本上和 union 一样只是多了一个排序的能力。因为前面我们说如果获取到无序的主键就不会发生索引合并可能最终会直接上全表扫描。因此 MySQL 里边又搞了一个 sort_union就是先在 username 索引树和 address 索引树中同时进行搜索分别拿到主键值之后先进行排序排序完了再进行去重然后回表拿完整的数据。和 union 相比主要是多了加粗的那一步。那我们继续关闭 sort_union如下SET optimizer_switch index_merge_sort_unionoff;关闭之后再去看执行计划如下此时就没有索引合并了直接全表扫描。3.3 intersect这个是求两个索引的交集。例如如下 SQLselect * 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 的前提首先去二级索引 S 上去搜索找到第一条满足条件的记录由于二级索引的叶子结点保存的是主键值此时拿到主键值之后先不要急着回表。接下来去二级索引 T 上去搜索找到第一条满足条件的记录并且拿到对应的主键值。比较第一步和第二步搜索拿到的主键值3.1 如果主键值不相等则舍弃值小的主键留下大的主键下一次在 S 上搜索的时候就拿着这个大的主键和 S 上搜索出来的主键进行比较。3.2 如果主键值相等则说明这个主键是满足搜索条件的那就拿着这个主键回表。重复前三步直到各自索引中没有满足条件的记录为止。这就是所谓的交叉获取主键。好啦这就是索引合并的三种情况。4. 小结很多小伙伴可能会说既然有索引合并是不是我索引就可以随便建立了nonono索引合并是一种不得已而为之的办法如果发生了索引合并大概率是你设计的索引不太合理导致的所以我们应该去琢磨该如何优化索引。参考资料https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html《MySQL 是怎么运行的》《高性能 MySQL》https://www.modb.pro/db/29619
