迎面走来了你的面试官,身穿格子衫,挺着啤酒肚,发际线重大后移的中年男子。
手拿泡着枸杞的保温杯,胳膊夹着 MacBook,MacBook 上还贴着公司标语:“加班使我高兴”。
面试官: 看你简历上用过 MySQL,问你几个简略的问题吧。什么是聚簇索引和非聚簇索引?
这个问题难不住我啊。来之前我看一下一灯 MySQL 八股文。
我: 举个例子:有这么一张用户表
CREATE TABLE `user` (
`id` int COMMENT '主键 ID',
`name` varchar(10) COMMENT '姓名',
`age` int COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COMMENT='用户表';
用户表中存储了这些数据:
id | nane | age |
---|---|---|
1 | 一灯 | 18 |
2 | 张三 | 22 |
3 | 李四 | 21 |
4 | 王二 | 19 |
5 | 麻子 | 20 |
那么在索引中,这些数据是怎么存储的呢?
MySQL 的 InnoDB 引擎中索引应用的 B + 树结构。
别问为什么根节点存储了(1,4)两个元素,左子节点又存储了(1,2,3)三个元素,上面带有三个叶子节点,叶子节点之间又用有序链表相连?
问就是 B + 树的个性,不理解的能够翻一下上期的文章。
如上图所示,叶子节点中存储了全副元素的索引,就是 聚簇索引 。
个别主键索引就是 聚簇索引,如果表中没有主键,MySQL 也会默认建设一个暗藏主键做主键索引。
什么是非聚簇索引?
假如咱们在 age(年龄)字段上建一个一般索引,age 字段下面的索引存储构造就是上面这样:
叶子节点中只存储了以后索引字段和主键 ID,这样的存储构造就是非聚簇索引。
面试官: 那什么是联结索引呢?
我: 有多个字段组成的索引就是联结索引。
面试官:【晕】建联结索引有什么益处?它跟在单个字段上建索引有什么区别?
我: 假如有这么一条查问语句。
select * from user where age = 18 and name = '张三';
如果咱们在 age 和 name 字段上别离建两个索引,这个查问语句只会用到其中一个索引。
然而咱们在 age 和 name 字段建一个联结索引(age,name),它的存储构造就变成这样了。
如果只在 age 下面建索引,会先查问 age 下面非聚簇索引,有三条 age=18 的记录,主键 ID 别离是 1、4、5,而后再用这三个 ID 去查问主键 ID 的聚簇索引。
如果在 age 和 name 下面建联结索引,会先查问 age 和 name 下面的非聚簇索引,匹配到一条记录,主键 ID 是 1,而后再用这个 ID 去查问主键 ID 的聚簇索引。
由此能够得出,联结索引的长处:大大减少扫描行数。
面试官: 你再说一下什么是最左匹配准则?
我: 最左匹配准则是指在建设联结索引的时候,遵循最左优先,以最右边的为终点任何间断的索引都能匹配上。
当咱们在 (age,name) 上建设联结索引的时候,where 条件中只有 age 能够用到索引,同时有 age 和 name 也能够用到索引。然而只有 name 的时候是无奈用到索引的。
为什么会呈现这种状况呢?
看下面的图,就了解了,(age,name)的联结索引,是先依照 age 排序,age 相等的行再依照 name 排序。如果 where 条件只有一个 name,当然无奈用到索引。
面试官: 什么是笼罩索引和回表查问?
我: 这个就更简略了,下面曾经提到这个知识点了。
当咱们在 age 上建索引的时候,查问 SQL 是这样的时候:
select id from user where age = 18;
就会用到笼罩索引,因为 ID 字段咱们应用 age 索引的时候曾经查出来,不须要再二次回表查问了。
然而当查问 SQL 是这样的时候:
select * from user where age = 18;
想要查问所有字段,就须要二次回表查问。因为咱们第一次用 age 索引的时候只查出来了主键 ID,还须要再用主键 ID 回表查问出所有字段。
面试官: 再问一个,你晓得什么是索引下推吗?
这么冷门的问题,你都问的进去,真的要面试造火箭啊!
我: 索引下推(Index Condition Pushdown)是 MySQL5.6 引入的一个优化索引的个性。
举例:
在 (age,name) 下面建联结索引,并且查问 SQL 是这样的时候:
select * from user where age = 18 and name = '张三';
如果没有索引下推,会先匹配出 age = 18 的三条记录,再用 ID 回表查问,筛选出 name = ‘ 张三 ’ 的记录。
如果应用索引下推,会先匹配出 age = 18 的三条记录,再筛选出 name = ‘ 张三 ’ 的一条记录,最初再用 ID 回表查问。
由此得出,索引下推的长处:缩小了回表的扫描行数。
面试官: 小伙子,八股文背的挺溜啊。我给你出个实战题,看你有没有筹备。上面这个查问 SQL 该怎么建联结索引?
select a from table where b = 1 and c = 2;
成心刁难我?你认为实战题就不能背八股文了吗?
我: 方才在讲联结索引的时候曾经说了这个知识点了,where 条件有 b 和 c 的等值查问,联结索引就建成(b,c),因为 select 前面有 a,咱们就建设 (b,c,a) 的联结索引,并且能够用到笼罩索引,查问速度更快。
面试官: 小伙子,有点货色。一会儿就给你发 offer,今天就来下班,薪资 double。
文章继续更新,能够微信搜一搜「一灯架构」第一工夫浏览更多技术干货。