本文主要是介绍mysql（四）索引下推，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
目录
数据准备：
标准案列：
问题1：索引下推如何开启和关闭？（MySQL5.6以后的版本）
问题2：索引下推在哪些情况下无法使用？
2.1下推条件遇到子查询
2.2下推条件遇到函数
2.3非InnoDB表和MyISAM表
注意事项：
1、索引下推只能存在联合索引里
2、范围列可以用到索引，但是范围列后面的列无法用到索引
3、不要使用SELECT * FROM
4、减少子查询、范围等查询、慎用函数。
索引下推（Using index condition）为什么要单独写、因为这边还涉及到了数据在索引中的存放方式和规则。
数据准备：
我们借用在mysql（三）的创建的数据、来继续演示。
建表脚本
CREATE TABLE user_info( id INT NOT NULL COMMENT '主键', name varchar(50) NOT NULL COMMENT '名称',en_name varchar(50) NOT NULL COMMENT '英文名称',age INT NOT NULL COMMENT '年龄',status INT NOT NULL COMMENT '0 草稿 1 上架 2 下架',description varchar(100) NOT NULL COMMENT '描述',PRIMARY KEY (`id`));#创建组合索引
ALTER TABLE `user_info` ADD INDEX index_name_age_status (`name`,`age`,`status`);
造数据函数
CREATE DEFINER=`root`@`localhost` PROCEDURE `123`()
BEGIN#Routine body goes here...declare i int default 1;while(i<1000)doinsert into user_info values(i,CONCAT("yzh",FLOOR(RAND() * 100)),CONCAT("YZH",FLOOR(RAND() * 100)),FLOOR(RAND() * 100),1,FLOOR(RAND() * 1000));set i=i+1;end while;
END数据造好了、我们再插入3条指定的模拟数据、方便我们进行演示。
INSERT INTO `user_info` ( `id`, `name`, `en_name`, `age`, `status`, `description` )
VALUES( 1001, '张三', 'Zhang1', 29, 1, '123' ),( 1002, '张三', 'Zhang2', 19, 1, '123' ),( 1003, '张三', 'Zhang2', 39, 1, '123' ),( 1004, '张三', 'Zhang2', 40, 1, '123' );上面三条数据年龄个位都是9。现在我们要写一条查询用户姓名是"张三"且年龄个位数为"9" 的查询语句。
标准案列：
select * from user_info where name = "张三" and age like "%9";
命中索引： index_name_age_status
Extra : Using index condition 索引下推。
但是有人疑问了、不是说%在前索引会失效吗？ 为什么还是命中索引了。因为我用的是mysql5.8。MySQL5.6添加的索引下推。
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。
那么问题来了、我们都是高版本的如何验证模拟不支持索引下推呢
问题1：索引下推如何开启和关闭？（MySQL5.6以后的版本）
// 索引下推默认是开启的
set optimizer_switch='index_condition_pushdown=off'; // 关闭
set optimizer_switch='index_condition_pushdown=on'; // 开启我们先执行关闭语句：
然后再执行
select * from user_info where name = "张三" and age like "%9";
命中索引： index_name_age_status
Extra : Using where 回表查询。
这是为什么呢、我们先看图
看到图片和下面的话、我们会发现索引下推比回表少了一个1004、我们再看1004主键的数据
( 1004, '张三', 'Zhang2', 40, 1, '123' );结合上面的我们可以知道、索引下推的时候再索引里面帮我们进行了一次%9的筛选、1004的数据不符合就没有返回进行回表查询。
- 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
- 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
- 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
- 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数
问题2：索引下推在哪些情况下无法使用？
2.1下推条件遇到子查询
EXPLAIN SELECT*
FROMuser_info
WHERENAME = "张三" AND age IN ( SELECT age FROM USER WHERE NAME = "张三" );
我们可以看到查询语句还是命中了（有点翻车）、但是子查询走的全表扫描。所有不建议这样使用。
2.2下推条件遇到函数
EXPLAIN SELECT*
FROMuser_info
WHERENAME = "张三" AND age LIKE "%9"
ORDER BYage DESC;
我们看到是回表、不是索引下推。
2.3非InnoDB表和MyISAM表
这个就不演示了、有兴趣的伙计可以自己改索引方式进行尝试。
注意事项：
1、索引下推只能存在联合索引里
2、范围列可以用到索引，但是范围列后面的列无法用到索引
3、不要使用SELECT * FROM
4、减少子查询、范围等查询、慎用函数。
版权声明：转载请附上文章地址DJyzh的博客_CSDN博客-java基础,框架,java高级领域博主
这篇关于mysql（四）索引下推的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
