Mysql数据库优化-索引下推IPC

ICP
索引下推的含义
范围查找为啥不行？
搞定MySQL

生猛干货
带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

Pre
MySQL - 索引优化案例实操 中 关于 【Case 3 : like KK% 一般情况都会走索引】 ，我们来详细聊一聊

ICP
我们说like KK% 一般情况下会走索引，这是为何呢？

结合B+索引树， like KK% 能保证有序吗？

Explain plan

id

包含一组数字，表示查询中执行select子句或操作表的顺序 id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

• SIMPLE：查询中不包含子查询或者UNION
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY:在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY
• DERIVED:在FROM列表中包含的子查询被标记为：DERIVED（衍生）
• UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED

type 列 （性能从高到低）

const: 表中值有且只有一行匹配,或者利用where 查询某个常量的值,主键或唯一索引查询是效率最高的方式

system:system是const类型的特例，当查询的表只有一行的情况下， 使用system
ref: 非唯一索引查找,返回匹配某个单独值的所有行

eq_ref: 常出现在join查询,唯一索引或主键索引查找,对每个索引键,表中只有一条记录与之匹配

ref_or_null:类似于ref 增加了null值的查询

index_merge: 索引合并
range: 索引范围素描(between , > , <)

index: 索引扫描

all: 全表扫描,性能最低

possible_keys:

可能会被使用到的索引

key:

实际被使用到的索引

key_len:

实际使用索引的最大长度 (字节)

ref列

列出哪些列被用于索引查找

rows列

预估扫描行数

extra列

using index: 使用了覆盖索引(直接通过索引获取数据,不访问表)
using where: 表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。
Using Index Condition: 在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”,常见使用order by 或者 group by 查找
using temporary: 使用了临时表,表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

其实MySQL对like KK% 这种情况使用到了索引下推优化 Index Condition Pushdown，ICP

1 索引下推的含义

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，

SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 

这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。（server层处理）

2 索引下推的优点

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，

过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数

使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
 

 版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
 原文链接：https://blog.csdn.net/yangshangwei/article/details/107754192