回表概念
定义:在使用非聚簇索引查询数据时,根据主键id到聚簇索引上查询数据的过程称为回表
优化:如果回表的次数变多,那查询的性能会变的差,需要优化
过程图解:
在非聚族索引上面根据索引过滤掉一部分数据,然后拿到主键id,再根据主键id到聚族索引查找数据的过程
避免回表
覆盖索引
- 无需回表,查询需要返回的列信息直接从索引树获得,不再访问物理行数据
索引下堆
-
索引下推(Index Condition Pushdown,简称 ICP),是 mysql 5.6 版本的新特性,它能减少回表查询次数,提高查询效率
-
需要回表,存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据 where 其他条件进行过滤
-
mysql 架构图
-
mysql 服务层 负责 sql语法 解析、生成执行计划等,并调用存储引擎层执行数据的存储和检索
-
索引下推是指将部分上层(服务层)负责的事情(如where查询),交给了下层(引擎层)去处理
-
无 ICP 查询过程
- 存储引擎读取索引
- 根据索引中的主键值,定位并读取完整的行记录(回表)
- 存储引擎把记录交给服务层去检测该记录是否满足where条件
-
ICP 查询过程
- 存储引擎读取索引
- 判断where条件部分能否用索引中的列来做检查
- 条件不满足,处理下一行记录(丢弃,减少回表次数)
- 条件满足,用索引中的主键去定位并读取完整的行记录(回表)
- 存储引擎把记录交给服务层,服务层检测该记录是否满足WHERE条件的其余部分
mysql 配置索引下堆
- ICP开启状态查询:
SHOW VARIABLES LIKE '%optimizer_switch%';
- 状态开启与关闭:
SET optimizer_switch="index_condition_pushdown=off/on";