共计 2689 个字符,预计需要花费 7 分钟才能阅读完成。
前言
索引下推 (ICP) 是针对 MySQL 应用索引从表中检索数据行的状况的优化。
- 在没有索引下推的状况下,MySQL 通过存储引擎遍历索引来定位表中的数据行并将它们返回给 MySQl 服务器,服务器再进行 WHERE 条件的判断,确认是否将数据行退出后果集。
- 开启索引下推,且 WHERE 条件局部能够仅应用索引中的列来评估,这时 MySQL 服务器会将这部分 WHERE 条件下推到存储引擎,接着存储引擎应用索引条目评估推送的索引条件,仅当满足该条件时才从表中进行读取。
索引下推能够缩小存储引擎拜访数据表的次数以及 MySQL 服务器拜访存储引擎的次数。
注释
看完下面这段官网文档的解释,你当初是不是这样的😂
是的话,那就对了,毫无疑问下面这段话了解起来相当吃力,但请不要灰心,我将用最通俗易懂的语言来带你理解索引下推。
在此之前我先像你介绍两个敌人👬
- 最左前缀准则
- 回表
最左前缀准则
MySQL 在建设联结索引时会遵循最左前缀准则,比方当初 User 表建设了联结索引(id,name,age)依据最左前缀准则只有在 SQL 的条件局部命中 (id)、(id,name) 或者 (id, name, age) 时能力应用到这个联结索引。
能应用该索引的状况如下:
SELECT * FROM USER WHERE id = 1
SELECT * FROM USER WHERE id = 1 and name = ‘zhangsan’
SELECT * FROM USER WHERE id = 1 and name = ‘zhangsan’ and age = 18
不能应用该索引的状况如下:
SELECT * FROM USER WHERE name = ‘zhangsan’
SELECT * FROM USER WHERE age = 18
SELECT * FROM USER WHERE name = ‘zhangsan’ and age = 18
对于联结索引 mysql 会始终向右匹配直到遇到范畴查问 (>、<、between、like) 就进行匹配。
回表
MySQL 在 InnoDB 引擎下反对两种索引
汇集索引:索引里(B+ 树的叶子结点上)存储的是数据行(实在的数据)
一般索引:索引里(B+ 树的叶子结点上)存储的是主键
这里着重说一下汇集索引,官网文档有以下形容
- 在有主键的表,InnoDB 将主键作为汇集索引
- 没有主键的表,InnoDB 应用第一个惟一索引作为汇集索引
- 即没有主键也没有惟一索引时,MySQL 将生成一个暗藏的 6 字节大小的 row ID 字段作为汇集索引
MySQL 通过一般索引没法一次性将数据拿全的状况下,通过一般索引获取主键值,再通过主键值到汇集索引中定位到记录,这个过程就叫回表。能够通过建设笼罩索引来缩小回表,比方当初要通过身份证号查姓名,那就建设身份证号和姓名的联结索引(id,name),当查问时能够通过这个索引间接拿到姓名 name 得值,不再须要去汇集索引里查找了,这就是笼罩索引。
索引下推
首先创立一个用户表
CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT 0,
`class` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_two` (`name`,`age`)
) ENGINE=InnoDB;
// 这张表减少一个复合索引
(`name`,`age`)
给表插入数据
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 21, '1');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 22, '2');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 23, '3');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 24, '4');
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 25, '5');
查问插入的数据如下
接下来 explain 上面这个 SQL
explain select * from student where name like 'peng%' and age = 23;
能够看到 Extra 字段显示为 USING INDEX CONDITION,这就表明这个 SQL 应用了索引下推,咱们剖析下下面这个 SQL 语句:
在 MySQL5.6 之前,只能从 name 字段中找出符合条件的行而后开始 回表,到汇集索引上找出数据行,再对 age 字段进行比照,把符合条件的数据退出到后果集中。
在 MySQL5.6 引入了索引下推优化,在索引的遍历过程中,对索引中蕴含字段先做判断,这里对 age 字段进行判断。间接将 age 字段不满足的数据行排除,从而 缩小回表 的次数。
问答区
- 问题 1 当复合索引列为(name,age,address)时 以下 SQL 能应用索引吗?
select * from student where name like 'peng%' and age = 23;
能够,遇到 like 会中断后续元素的匹配,但只能应用 name 这个字段,mysql 会始终向右匹配直到遇到范畴查问(>、<、between、like)就进行匹配。范畴列能够用到索引,然而范畴列前面的列无奈用到索引。即索引最多用于一个范畴列,因而如果查问条件中有两个范畴列则无奈全用到索引。
- 问题 2 索引下推只能存在联结索引里吗?
是的,非联结索引无奈应用索引下推。
- 问题 3 索引下推在哪些状况下无奈应用?
下推条件遇到子查问
下推条件遇到函数
非 InnoDB 表和 MyISAM 表
- 问题 4 索引下推如何开启和敞开?
// 索引下推默认是开启的
set optimizer_switch='index_condition_pushdown=off'; // 敞开
set optimizer_switch='index_condition_pushdown=on'; // 开启
总结
索引下推在非主键索引上的优化,能够无效缩小回表的次数,大大晋升了查问的效率,在平时工作中能够依据业务状况通过优化索引来达到应用索引下推,进步业务吞吐量。
❤️ 谢谢反对
以上便是本次分享的全部内容,心愿对你有所帮忙 ^_^
喜爱的话别忘了 分享、点赞、珍藏 三连哦~。
欢送关注公众号 程序员巴士,一辆乏味、有范儿、有温度的程序员巴士,涉猎大厂面经、程序员生存、实战教程、技术前沿等内容,关注我,交个敌人。