MySQL 回表,覆盖索引,索引下推
作者:向着百万年薪努力的小 发布时间:2024-01-21 12:56:59
回表
在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下
字面意思,找到索引,回到表中找数据
解释一下就是:
先通过索引扫描出数据所在的行,再通过行主键ID 取出数据。
举个例子说明:
SELECT * FROM INNODB_USER
WHERE AGE = 18 AND USER_NAME LIKE '模糊查%';
假如age
和user_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;
分析一下以上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;
开启索引下推优化后再分析一下以上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
猜你喜欢
- SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`COLUMNS` WHERE
- set 是一个无序的元素集合,支持并、交、差及对称差等数学运算, 但由于 set 不记录元素位置,因此不支持索引、分片等类序列的操作。初始化
- 在写Python的时候经常会遇到时间格式的问题,首先就是最近用到的时间戳(timestamp)和时间字符串之间的转换。所谓时间戳,就是从 1
- 以住做B/S的系统都是以IE浏览器为主,基本上忽略其他的浏览器,这次决定来个大兼容,但在实现背景渐变上就是个 * 烦。本想用图片来实现的,但要
- 目录爬虫Python验证码识别 1、批量下载验证码图片2、识别代码看看效果 3、折腾降噪、去干扰爬虫Python验证码识
- 如何用SA-FileUp上传多个文件?表单处理: <%@&nbs
- 用python输出汉字字库问题1:假设我们知道汉字编码范围是0x4E00到0x9FA5,怎么从十六进制的编码转成人类可读的字呢?问题2:怎么
- 阅读上一篇教程:WEB2.0网页制作标准教程(10)自适应高度布局初步搭建起来,我开始填充里面的内容。首先是定义logo图片:样式表:#lo
- tomorrow是我最近在用的一个爬虫利器,该模块属于第三方的一个模块,使用起来非常的方便,只需要用其中的threads方法作为装饰器去修饰
- 什么是pyecharts?pyecharts 是一个用于生成 Echarts 图表的类库。echarts是百度开源的一个数据可视化 JS 库
- 目录1.横向合并1.1 concatenate方法1.2 hstack方法1.3 column_stack方法2.纵向合并2.1 conca
- 如下所示:# coding = GBKa =[1,2,3,4,5]sum=0b = len(a)print("这个数组的长度为:&
- 现在我们已经很熟悉Django的MTV模式了。模板(template)负责如何去展示数据,而视图(view)负责筛选出正确的数据。因此通常来
- 引言目前Python2和Python3存在版本上的不兼容性,这里将列举dict中的问题之一。下面话不多说,来看看详细的介绍:1. Pytho
- 用ASP实现搜索引擎的功能是一件很方便的事,可是,如何实现类似3721的智能搜索呢?比如,当在搜索条件框内输入“中国人民”时,自动从中提取“
- 前言虽然本文讲的是Python,但其实它也适用于所有的编程语言。因为这里面蕴含着编程之魂。所以本文标题没有显著的使用Python关键词。当然
- 1.利用装饰器在视图中拦截未登录的url@login_required(login_url='/user/login/')d
- 废话不多说了,关键代码如下所示:<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 T
- 执行环境会负责管理代码执行过程中使用的内存,编写JavaScript程序时,所需内存的分配以及无用内存的回收完全实现自动管理。原理:找出那些
- Web性能优化最佳实践中最重要的一条是减少HTTP请求,它也是YSlow中比重最大的一条规则。减少HTTP请求的方案主要有合并JavaScr