MySQL 回表,覆盖索引,索引下推

作者:向着百万年薪努力的小 时间:2024-01-21 12:56:59 

回表

在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下

字面意思,找到索引,回到表中找数据

解释一下就是:

先通过索引扫描出数据所在的行,再通过行主键ID 取出数据。

举个例子说明:

SELECT * FROM INNODB_USER
WHERE AGE = 18 AND USER_NAME LIKE '模糊查%';

假如ageuser_name两个字段是个联合索引,我们通过age=18这个索引找到了二级索引树对应页所在的数据,但是由于user_name是模糊查询,导致了这个字段的索引失效,我们得到了二级索引的这一页中age=18的很多个数据(主键id),我们通过这些主键ID回到主键索引树里再查表里的数据,这个操作就是回表。

另外回表的产生也是需要一定条件的,如果一次索引查询就能获得所有的select 记录(也就是联合索引已经包含了你查的字段)就不需要回表,如果select 所需获得列中有其他的非索引列,就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

另外上面所说的不需要回表,其实还有另一个名词

覆盖索引

就是我们需要查询的数据都在二级索引树中,直接返回这种情况就叫做覆盖索引。

上面提到的联合索引、二级索引树、主键索引树这些名词,如果同学们还没有啥概念,请看我写的这一篇文章,详细介绍了MYSQL的索引
链接: MySQL索引详解及演进过程及面试题延伸

索引下推

索引下推(index condition pushdown )简称ICP,在Mysql5.6以后的版本上推出,用于优化回表查询;

在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 ;

在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,
然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 ;

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

举个栗子:

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(64) NOT NULL COMMENT '用户名 ',
 `age` int(8) NOT NULL COMMENT '年纪',
`address` varchar(255) DEFAULT NULL COMMENT '地址',
 `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除 默认否',
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';
-- 初始化数据
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李四', 22, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李五', 22, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李六', 23, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('张三', 24, '中国');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李期', 24, '中国');

用上面的语句建一个测试用的表

无索引下推: 查看索引下推的状态

show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switchindex_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

关闭索引下推:

索引下推是mysql 5.6优化查询回表的功能,在5.6之前都不支持索引下推,笔者用的8.0,则需要先关闭索引下推:
set optimizer_switch='index_condition_pushdown=off';

执行sql;

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

MySQL 回表,覆盖索引,索引下推

分析一下以上sql执行的过程:

  • 1. idx_name_age`组合索引 遵循最左匹配遇到非等值判断时匹配停止,name的范围查询则会使age这个条件就不会走索引;

  • 2. 会先在name索引上顺序找到 符合条件的name和id数据;

  • 3. 然后通过id在聚簇索引上回表找到对应的age数据,将结果存放在临时表中;

  • 4. 最后在临时表中通过age条件来筛选数据。

以上过程会扫描4条记录,回表4次。
extra = Using where:表示优化器需要通过索引回表查询数据。

有索引下推: 开启索引下推

set optimizer_switch='index_condition_pushdown=on';

执行sql:

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

MySQL 回表,覆盖索引,索引下推

开启索引下推优化后再分析一下以上sql执行的过程:

  • 1. 由于开启了索引下推会在idx_name_age索引上同时检索满足name和age的条件的数据的id;

  • 2. 再用id到聚簇索引上查询完整的数据。

以上过程会扫描4行数据,回表次数是2次。

extra = Using index condition 表示索引下推。

总结:

  • 索引下推功能是mysql 5.6推出优化回表的操作,只支持向上兼容,低版本是不支持的;

  • 索引下推优化的只是回表次数,扫描行数还是一样的。

来源:https://blog.csdn.net/weixin_44688973/article/details/125511360

标签:MySQL,回表,覆盖,索引,下推
0
投稿

猜你喜欢

  • 关于ASP中脚本执行顺序的讲解

    2008-11-04 12:02:00
  • Express无法通过req.body获取请求传递的数据解决方法

    2024-06-05 09:52:06
  • python第三方库pygame的使用详解

    2023-07-21 13:27:19
  • python opencv读mp4视频的实例

    2022-04-13 00:32:11
  • perl 采集入库脚本分享

    2023-09-13 08:45:40
  • Python绘制词云图之可视化神器pyecharts的方法

    2022-12-08 14:38:44
  • Python time库的时间时钟处理

    2022-12-18 23:04:23
  • Python分支语句与循环语句应用实例分析

    2022-12-02 03:35:31
  • Python基础教程之if判断,while循环,循环嵌套

    2023-11-05 12:56:07
  • 详解Python中命令行参数argparse的常用命令

    2022-06-06 15:59:30
  • python实现向ppt文件里插入新幻灯片页面的方法

    2021-09-01 15:58:22
  • python 执行函数的九种方法

    2021-03-10 18:12:08
  • vue-quill-editor实现图片上传功能

    2024-04-30 10:22:40
  • OpenCV搞定腾讯滑块验证码的实现代码

    2022-09-09 21:41:04
  • Django 如何实现文件上传下载

    2021-07-16 02:54:15
  • 基于 Serverless +企业微信打造 nCoV 疫情监控小助手

    2023-11-20 21:42:02
  • golang gorm开发架构及写插件示例

    2024-04-25 13:19:00
  • 把网页中的电话号码生成图片的ASP程序

    2008-11-07 16:12:00
  • mysql修改密码的三方法和忘记root密码的解决方法

    2024-01-24 18:45:12
  • 选项卡动态增删的效果(内嵌框架)

    2008-05-22 12:59:00
  • asp之家 网络编程 m.aspxhome.com