MySql 一条普通的查询语句 你知道如何优雅的使用 前缀索引、索引下推优化查询速度吗?

在码农的世界里,优美的应用体验,来源于程序员对细节的处理以及自我要求的境界,年轻人也是忙忙碌碌的码农中一员,每天、每周,都会留下一些脚印,就是这些创作的内容,有一种执着,就是不知为什么,如果你迷茫,不妨来瞅瞅码农的轨迹。

在这里,有一张存有10多万条用户数据的表,你知道接下来会发生什么吗 ?这是建表语句中的一小部分

CREATE TABLE `t_user`(
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMIT '主键ID',
	`id_card` VARCHAR(32) DEFAULT NULL COMMIT '用户身份证',
	`u_name` VARCHAR(32) DEFAULT NULL COMMIT '用户姓名',
	`u_age` INT(11) DEFAULT NULL COMMIT '用户年龄',
	`u_male` TINYINT(1) DEFAULT NULL ,
	PRIMARY KEY(`id`) ,
	KEY `id_card`(`id_card`) ,
	KEY `name_age`(`u_name` , `u_age`)
) ENGINE = INNODB

如上所示 我们创建了(用户年龄与姓名)的联合索引,如果现在有一个高频请求,要根据用户的姓名查询他的年龄,这个联合索引就有意了。它可以在这个高频请求上用到 覆盖索引,不再需要回表查整行记录,减少语句的执行时 间。


索引字段的维护是有代价的,所以在建立冗余索引来支持覆盖索引时就需要权衡业务的需求,不能盲目来。

1 前缀索引

如果为每一种查询都设计一个索引,索引也就太多了,如我们再多一个根据用户身份证号来查看他的姓名或者是地址,这个查询需求在业务中出现的概率不高,每次查询时让它走全表扫描也是可以的,但这也太low了,数据量一上百万级别,人眼感觉的慢。

所以可以使用 索引的“最左前缀” 来定位记录。

在这里插入图片描述

如上图所示,索引项是按照索引定义里面出现的字段顺序排序的,当需要查到所有名字是“张三”的人时,可以快速定位到 ID为1001的位置,然后向后遍历得到所有需要的结果。

当你的查询是模糊查询时,也能够用上图这个索引,查找到第一个符合条件的记录是 ID为1001的位置,然 后向后遍历,直到不满足条件为止。

select * from t_user where name like '张 %'

这样看来,只要是查询的条件满足满足联合索引的最左前缀,就可以利用索引来加速检索,这个最左前缀可以是联合索引的最左 N 个字段,可称作是前缀索引。

基于联合索引的使用,在建立联合索引的时候,如何安排索引内的字段顺序便是一个效率的关键点,一个参考的原则就是

索引的复用能力: 当有了 (a,b) 这个联合索引后,一般就不需要单独在 a 上建立索引了,如果通过调 整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

如在这里我们创建的 ‘name_age’ ,从空间的角度来看,name 字段是比 age 字段大的 ,所以我们创建一个(name,age) 的联合索引和一个 (age) 的单字段索 引。

2 索引下推

如在这里 t_user 表的 (name,age) 的联合索引,当执行如下查询筛选时

select * from t_user where name like '张 %' and age=20

首先会使用(name,age) 的联合索引的 前缀索引规则找到第一个满足条件的ID为1001,然后一个个回表到主键索引上找出数据行,如下图所示:
在这里插入图片描述
在这个过程 InnoDB 并不会去看 联合索引表age 的值,只是按顺序把“name 第一个字是’张’的记录一条条取出来回表,因此查询出来三条数据, 需要回表 4 次。


MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过 程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数,如下图所示

在这里插入图片描述
在这个过种中InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 20,对于 不等于 20 的记录,直接判断并跳过,在查询中,只需要对 ID-1001、ID-1011 这两条记 录回表取数据判断,查询三条数据,就只需要回表 2 次。


完毕

不局限于思维,不局限语言限制,才是编程的最高境界。

以小编的性格,肯定是要录制一套视频的,随后会上传

有兴趣 你可以关注一下 西瓜视频 — 早起的年轻人

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页