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)do
        insert 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
	* 
FROM
	user_info 
WHERE
	NAME = "张三" 
	AND age IN ( SELECT age FROM USER WHERE NAME = "张三" );

我们可以看到查询语句还是命中了（有点翻车）、但是子查询走的全表扫描。所有不建议这样使用。

2.2下推条件遇到函数

EXPLAIN SELECT
	* 
FROM
	user_info 
WHERE
	NAME = "张三" 
	AND age LIKE "%9" 
ORDER BY
	age DESC;

我们看到是回表、不是索引下推。

2.3非InnoDB表和MyISAM表

        这个就不演示了、有兴趣的伙计可以自己改索引方式进行尝试。

注意事项：

        1、索引下推只能存在联合索引里

        2、范围列可以用到索引，但是范围列后面的列无法用到索引

        3、不要使用SELECT * FROM

        4、减少子查询、范围等查询、慎用函数。

 版权声明：转载请附上文章地址DJyzh的博客_CSDN博客-java基础,框架,java高级领域博主