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

àì夳堔傛蜴生んèń 2023-10-07 15:49 49阅读 0赞

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

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

在这里，有一张存有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

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

--------------------

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

#### 1 前缀索引 ####

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center]

如上图所示，索引项是按照索引定义里面出现的字段顺序排序的，当需要查到所有名字是“张三”的人时，可以快速定位到 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，然后一个个回表到主键索引上找出数据行，如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]  
在这个过程 InnoDB 并不会去看 联合索引表age 的值，只是按顺序把“name 第一个字是’张’的记录一条条取出来回表，因此查询出来三条数据， 需要回表 4 次。

--------------------

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

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

--------------------

完毕

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

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

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

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[Link 5]: https://blog.csdn.net/zl18603543572/article/details/114435742
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20210307125714354.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210307131417418.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210307131854849.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[Link 6]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center