name varchar(255) not null,
password varchar(255) ,
primary key (id),
engine=innodb
default character set=utf8 collate=utf8_general_ci
比如有这么两句sql
语句A: select id from user_table where name= '张三'
语句B: select password from user_table where name= '张三'
语句A: 因为 name索引树 的叶子结点上保存有 name和id的值 ,所以通过 name索引树 查找到id后,因此可以直接提供查询结果,不需要回表,也就是说,在这个查询里面,索引name 已经 “覆盖了” 我们的查询需求,我们称为 覆盖索引
语句B: name索引树 上 找到 name=‘张三’ 对应的主键id, 通过回表在主键索引树上找到满足条件的数据
因此我们可以得知,当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中(联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引。
例如上面的语句B是一个高频查询的语句,我们可以建立(name,password)的联合索引,这样,查询的时候就不需要再去回表操作了,可以提高查询效率。
所以关于上面的面试题我们就可以得出,使用联合索引就可以很好的回答面试官的问题(id,name,password)这样的联合索引就可以调用到覆盖索引,可以减少树的搜索次数,不再需要回表查整行记录,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
说到了联合索引我们就不得不说联合索引中最重要的匹配原则,最左匹配原则了
五、最左匹配原则
最左前缀匹配原则,是非常重要的原则,mysql会从左向右进行匹配。
例如我们定义了(name,password)两个联合索引字段,我们 使用 where name = '张三' and password = '2'
索引可以生效的,当我们是颠倒了他们的顺序 使用where password = '1' and name = '王五'
,索引同样也是可以生效的,在mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划,我们能尽量的利用到索引时的查询顺序效率最高,所以mysql查询优化器会最终以这种顺序(where name = '张三' and password = '2'
)进行查询执行,就类似 我们的 order by name,password
这样一种排序规则,先对张三的用户进行查询排序,在对password进行处理
比如我们要查询姓张的用户,我们的条件查询可以为 "where name like ‘张%’"
,但是不能是 where name like '%张%'
或者是 where name like '%张'
,因为索引可以用于查询条件字段为索引字段,根据字段值必须是最左若干个字符进行的模糊查询,也就是需要是 ‘张%’ 这样的添加才可以使用。
索引的复用能力。因为可以支持最左前缀,所以当已经有了(name,password)这个联合索引后,一般就不需要单独在name上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
如果既有联合查询,又有基于name,password各自的查询呢?查询条件里面只有password的语句,是无法使用(name,password)这个联合索引的,这时候你需要同时维护(name,password)、(password) 这两个索引。
创建索引时,我们也要考虑空间代价,使用较少的空间来创建索引
假设我们现在不需要通过name查询password了,需要通过name查询age或通过age查询name
- 1.(name,age)联合索引+age单字段索引
- 2.(age,name)联合索引+name单字段索引
name字段是比age字段大的,所以,选择第一种,索引占用空间较小的一个
六、索引下推
上面我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。那么如果那些不符合最左前缀的部分,会怎么样呢?
如果现在有一个需求:检索出表中“名字第一个字是张,而且没有删除的信息(is_del = 1)。SQL语句如下:
mysql> select * from t_user where name like ‘张%’ and is_del=1
在MySQL 5.6之前,只能从匹配的位置一个个回表。到主键索引上找出数据行,再对比字段值
在MySQL 5.6中 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数
根据(username,is_del)联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。过程如下图:
每一个虚线箭头表示回表一次
图一(无索引下推执行流程)
每一个虚线箭头表示回表一次
图二(索引下推执行流程)
图1跟图2的区别是,InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过。在我们的这个例子中,只需要对ID1、ID4这两条记录回表取数据判断,就只需要回表2次
mysql默认启用索引下推,我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制SET optimizer_switch = 'index_condition_pushdown=off';
我们也需要注意:
- innodb引擎的表,索引下推只能用于二级索引,因为innodb的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果
- 索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引
六、小结
今天的内容就到这里了,我们在上面描述了数据库索引的概念,包括了覆盖索引、联合索引、索引下推,那么下次如果有面试官问你刚开始的问题,相信大家可以好好的回(dui)答(ta)一下面试官了,在sql优化中,减少回表次数,或者直接使用覆盖索引是比较重要的,尽量少地访问资源也是数据库设计的重要原则之一,谢谢大家,加油~
小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数初中级前端工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Web前端开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助,可以添加下面V无偿领取!(备注:前端)
最后
喜欢的话别忘了关注、点赞哦~
开源分享:【大厂前端面试题解析+核心总结学习笔记+真实项目实战+最新讲解视频】
6371)**