MYSQL 索引下推 45讲

刘老师群里,看到一位小友 问<MYSQL 45讲>林晓斌的回答
大意是一个组合索引 (a,b,c) 条件 a > 5 and a <10 and b='123', 这样的情况下是如何?
林老师给的回答是 A>5  ,然后下推B='123'

小友 问 "为什么不是先 进行范围查询,然后在索引下推 b='123'?"
然后就没有然后了....


说真的,不是我有意踩林老师, 我只是说<MYSQL 45 讲>吃个半饱, 大脑半醒半睡,好比晚上2点睡,早上被8点闹钟催醒. 上午在公司里梦游状态样.

极客这种课程,视乎给人感觉不全面,不细致.相对于等同价格的书来说,性价比太低了.

以前买了一本ORACLE ACE写的一本MYSQL入门的书.书中把BINLOG CACHE 归类于共享内存. 
高鹏(八怪)说BINLOG CAHCE是线程的内存. 
ACE 看来就是个荣誉技术编辑&总编. 

MYSQL 产生大量数据的过程

我们做个实验,用上面链接的表和数据!

添加个组合索引

KEY `idx_age_income_education` (`age`,`income_year`,`top_education`)

我们还是先讲下索引下推是什么鬼?
在很早很早以前 MYSQL 分为一阴一阳两面.  SERVER层负责阳的一面,引擎层负责阴的一面.

在这里我们记住一点就是服务层server负责过虑结果集, 只要执行计划有WHERE字眼,说明服务层执行了过滤操作, 另外ROW+FILER % 也可以窥爱一下.

引擎层返回服务层要的数据! 一个SQL有多个WHERE 条件,我们看哪个条件能命中引擎层的二级索引. 我们就把这个条件传给引擎层.引擎层通过这个条件筛选数据,然后返回,服务层再用剩余的条件,进一步筛选过滤(FILTER)记录,积累到NET_BUF满后就发生给客户.

引擎层一般会预读,大约是100条件记录,然后一条,一条给服务层,服务层判断一条记录,再问引擎要一条.

上面一般过程,不必牢记! 重点是 为什么不把服务层过滤条件,全拿到引擎层做呢?  其实都是内存操作,在引擎层还是服务层差距不大.

那为什么要ICP呢?  所以重点是索引, 是服务层把更多的条件,下推到索引上.是引擎上的二级索引.

通过索引过滤掉更多不符合条件的记录. 这样减少去读聚集索引!

一般二级索引都被内存缓存,聚集索引相对较大,不易缓存在内存里.读聚集索引可能要发生IO操作. 能通过ICP优化,能更多减少不必要的IO操作!

MYSQL 专业叫法是 读聚集索引, ORACLE 叫法是 回表!  回表和读聚集索引功能是类似的, 回表操作是直接从索引获得物理ID,直接定位到表具体行.而MYSQL读二级索引获得逻辑ID,还要通过主键聚集索引,根节点,分支节点,再到页节点,多了两次IO操作. 每个逻辑ID都要多两次IO操作. 比回表多了很多次IO操作.再说MYSQL是16K一个页,ORACLE是8K一个页. 优化思路是一样的,实现细节是有区别的. 算法一样,数据结构不一样. 作为MYSQL DBA. 如果还有OCP,COM,ACE头衔,自然不能说"回表",太LOW!

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

  • 存储引擎读取索引记录；

  • 根据索引中的主键值，定位并读取完整的行记录；

  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

  • 存储引擎读取索引记录（不是完整的行记录）；

  • 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

  • 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

  • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

我们还可以看一下执行计划，
看到Extra一列里Using index condition，这就是用到了索引下推。

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法；

  • 只能用于InnoDB和 MyISAM存储引擎及其分区表；

  • 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

我们使用下面SQL 看下执行计划 根据上面说只要EXTAR using index condition 使用索引条件 这英文取得让人误会. 为啥不多加个单词"using index pushdown condition "

select * from dba_test.personal_identity_info where  age > 35  and income_year > 10000 and   income_year < 20000  and top_education='大学' ; 
-- NO ICP key_len=10 rows=75 filtered=8.28 Extra=Using where 

select * from dba_test.personal_identity_info where  age >= 35 and age <= 65; 
-- ICP  key_len=1 rows=206 filtered=100 Extra=Using index condition  

select * from dba_test.personal_identity_info where  age > 35 and age < 65; 
-- ICP  key_len=1 rows=196 filtered=100 Extra=Using index condition 

select * from dba_test.personal_identity_info where  age > 35 and age < 65 and top_education='大学'; 
-- NO ICP key_len=10 rows=75 filtered=19.6 Extra=Using where 

select * from dba_test.personal_identity_info where  age > 35 and age < 65  and income_year > 10000 ; 
-- ICP  key_len=1 rows=196 filtered=33.33 Extra=Using index condition

select * from dba_test.personal_identity_info where  age >= 35 and age <= 65  and income_year > 10000; 
-- ICP  key_len=6 rows=206 filtered=33.33 Extra=Using index condition

select * from dba_test.personal_identity_info where  age = 35  and income_year > 10000 and  income_year < 20000  and top_education='大学' ; 
-- ICP  key_len=6 rows=1 filtered=7.50 Extra=Using index condition

select * from dba_test.personal_identity_info where  income_year > 10000 and  income_year < 20000  and top_education='大学' ;
 -- NO ICP key_len=10 rows=75 filtered=11.11 Extra=Using where

从上面八种情况,或许可以推导出,只要WHERE条件命中了组合索引第一个字段.

它一定会走索引! 其它条件命中组合索引其它字段,也能走索引.

ICP条件1:WHERE条件命中索引第一个字段.

ICP条件2:WHERE其它条件能命中组合索引其它字段,不过不能有等值查询

select * from dba_test.personal_identity_info where  age >= 35 and age <= 65 and top_education='大学';
-- NO ICP key_len=10 rows=75 filtered=20.60 Extra=Using where 

select * from dba_test.personal_identity_info where  age between 35 and 65 and top_education='大学';
-- NO ICP key_len=10 rows=75 filtered=20.60 Extra=Using where
另外两个情况下,还是其它WHERE条件命中组合索引且等值 ICP就失效
我的MYSQL 是 8.0.24. 索引下推是开启的
select @@optimizer_switch;
/*
index_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
*/

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";
我们还可以explain format=tree 看的更清楚

explain FORMAT=tree select * from dba_test.personal_identity_info where age > 35 and age < 65 and top_education='大学' and income_year > 10000;
/*
-> Filter: ((personal_identity_info.age > 35) and (personal_identity_info.age < 65) and (personal_identity_info.income_year > 10000))  (cost=7.24 rows=5)
    -> Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education='大学')  (cost=7.24 rows=75)
*/    

explain FORMAT=tree select * from dba_test.personal_identity_info where  age >= 35 and age <= 65 and top_education='大学' and income_year > 10000;
/*
-> Filter: ((personal_identity_info.age >= 35) and (personal_identity_info.age <= 65) and (personal_identity_info.income_year > 10000))  (cost=7.26 rows=5)
    -> Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education='大学')  (cost=7.26 rows=75)
*/

explain FORMAT=tree   select * from dba_test.personal_identity_info where  age > 35  and income_year > 10000 and   income_year < 20000  and top_education='大学' ;
/*
-> Filter: ((personal_identity_info.age > 35) and (personal_identity_info.income_year > 10000) and (personal_identity_info.income_year < 20000))  (cost=7.37 rows=6)
    -> Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education='大学')  (cost=7.37 rows=75)
*/


explain FORMAT=tree   select * from dba_test.personal_identity_info where  age = 35  and income_year > 10000 and  income_year < 20000  and top_education='大学' ;
/*
-> Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age = 35) and (personal_identity_info.income_year > 10000) and (personal_identity_info.income_year < 20000) and (personal_identity_info.top_education = '大学'))  (cost=0.71 rows=1)
*/

explain FORMAT=tree   select * from dba_test.personal_identity_info where  age > 35 and age < 65; 
/*
-> Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age > 35) and (personal_identity_info.age < 65))  (cost=88.46 rows=196)
*/
explain FORMAT=tree  select * from dba_test.personal_identity_info where  age = 35  and income_year > 10000 ;
/*
-> Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age = 35) and (personal_identity_info.income_year > 10000))  (cost=3.86 rows=8)
*/

前三个没有下推,后三个下推了,从中可推导出,ICP可以推进多个条件.

另外 推导出

ICP条件3:WHER条件命中组合索引第一个字段且是等值也生效.

看起来条件2和条件3有点冲突,其实不冲突!

一般来说,命中索引的只有一个WHER条件.

这个经验来自ORACLE,MYSQL通过EXPLAIN FORMAT=TREE是看不出来的.

这样只能跟踪源码才可知,跟踪源码是件很累的事情,成本高收益低!

以上胡说八道

 

此刘老师,不是那个刘老师! 那个刘老师太那个了,200号人捐款4.2万.

说是他自己用个脚本换来的,然后捐给武汉.自己独占了荣誉.

也没感谢大家捐款,也没在公号列出感谢名单.培训也就是培训脚本

如何使用! 说白了就是PPT宣传你的脚本有多么多么厉害.

online脚本套用ORACLE官方脚本SQLHC.

好像 搞得大家200号人 没有良心没有善心,就冲着你的牛X脚本来的?

还搞个PDF污蔑我. 只能忽悠没有脑子的小年轻!

脚本有鸟用,谁敢把来历不明的脚本,用在生产环境中?

8千行再套用个SQLHC,我没有精力去分析代码,

早就扔在上上家公司的办公电脑里!

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/695973.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Leetcode 力扣114. 二叉树展开为链表 (抖音号：708231408)

Leetcode 力扣114. 二叉树展开为链表 (抖音号：708231408)

给你二叉树的根结点 root &#xff0c;请你将它展开为一个单链表&#xff1a; 展开后的单链表应该同样使用 TreeNode &#xff0c;其中 right 子指针指向链表中下一个结点&#xff0c;而左子指针始终为 null 。展开后的单链表应该与二叉树 先序遍历 顺序相同。 示例 1&#xf…
阅读更多...
欢乐打地鼠小游戏html源码

欢乐打地鼠小游戏html源码

这是一款简单的js欢乐打地鼠游戏&#xff0c;挺好玩的&#xff0c;老鼠出来用鼠标点击锤它&#xff0c;击中老鼠获得一积分。 欢乐打地鼠小游戏html源码
阅读更多...
不同数据库背后的数据存储方案

不同数据库背后的数据存储方案

在大数据和AI时代&#xff0c;数据库成为各类应用不可或缺的重要组成部分。而数据库中的数据依赖存储引擎进行管理&#xff0c;包括数据的存储、查询、更新和删除等。因此&#xff0c;在设计系统时&#xff0c;选择正确的数据库存储引擎方案变得尤为重要。这篇文章将以关系型、…
阅读更多...
滑动窗口算法：巧妙玩转数据的窗外世界

滑动窗口算法：巧妙玩转数据的窗外世界

✨✨✨学习的道路很枯燥&#xff0c;希望我们能并肩走下来! 文章目录 目录 文章目录 前言 一 滑动窗口是什么&#xff1f; 二 相关题目解析 1. 长度最小的子数组 &#x1f973;题目解析 &#x1f973;算法原理 ✏️思路1 暴力枚举出所有子数组之和 ✏️思路2 滑动窗…
阅读更多...
LabVIEW调用DLL时需注意的问题

LabVIEW调用DLL时需注意的问题

在LabVIEW中调用DLL&#xff08;动态链接库&#xff09;是实现与外部代码集成的一种强大方式&#xff0c;但也存在一些常见的陷阱和复杂性。本文将从参数传递、数据类型匹配、内存管理、线程安全、调试和错误处理等多个角度详细介绍LabVIEW调用DLL时需要注意的问题&#xff0c;…
阅读更多...
有趣的数学 为什么绝对值和模都用两个竖线表示？

有趣的数学 为什么绝对值和模都用两个竖线表示？

绝对值和模都可以使用两个竖线表示&#xff0c;是因为它们在数学概念上有相似的性质&#xff0c;不过是应用场景不同。 绝对值&#xff08;Absolute Value&#xff09;&#xff1a; 绝对值是一个实数的非负值。它表示一个数在数轴上距离原点的距离。例如&#xff0c; 和 。 模&…
阅读更多...
Hadoop3：MapReduce源码解读之Map阶段的TextInputFormat切片机制（3）

Hadoop3：MapReduce源码解读之Map阶段的TextInputFormat切片机制（3）

Job那块的断点代码截图省略&#xff0c;直接进入切片逻辑 参考&#xff1a;Hadoop3&#xff1a;MapReduce源码解读之Map阶段的Job任务提交流程&#xff08;1&#xff09; 5、TextInputFormat源码解析 类的继承关系 它的内容比较少 重写了两个父类的方法 这里关心一下泛型参数…
阅读更多...
基于Java+SpringBoot制作一个软考助手答题小程序

基于Java+SpringBoot制作一个软考助手答题小程序

基于Java+SpringBoot制作一个软考小助手考试答题小程序。其中系统前端功能包括注册登录、公告通知、考试答题、视频课程、考试记录、题库、题目评论、错题统计、我的收藏和用户信息管理模块；系统后台功能包括用户管理、题库管理、答题管理、学习视频管理以及系统管理模块。 摘…
阅读更多...
WINUI——Behavior（行为）小结

WINUI——Behavior（行为）小结

前言 在使用MVVM进行WINUI或WPF开发时&#xff0c;Command在某些时候并不能满足逻辑与UI分离的要求。这时肯定就需要其它技术的支持&#xff0c;Behavior就是一种。在WPF中是有Behavior直接支持的&#xff0c;转到WINUI后&#xff0c;相对有一些麻烦&#xff0c;于是在此记录之…
阅读更多...
RainBond 制作应用并上架【以ElasticSearch为例】

RainBond 制作应用并上架【以ElasticSearch为例】

文章目录 安装 ElasticSearch 集群第 1 步：添加组件第 2 步：查看组件第 3 步：访问组件制作 ElasticSearch 组件准备工作ElasticSearch 集群原理尝试 Helm 安装 ES 集群RainBond 制作 ES 思路源代码Dockerfiledocker-entrypoint.shelasticsearch.yml制作组件第 1 步：添加组件…
阅读更多...
搭建RocketMQ主从异步集群

搭建RocketMQ主从异步集群

搭建RocketMQ主从异步集群 1、RocketMQ集群模式 为了追求更好的性能&#xff0c;RocketMQ的最佳实践方式都是在集群模式下完成的。RocketMQ官方提供了三种集群搭建方式&#xff1a; 2主2从异步通信方式&#xff1a;使用异步方式进行主从之间的数据复制。吞吐量大&#xff0c;…
阅读更多...
通过 AI Edge Torch 生成式 API 在设备上使用自定义大语言模型

通过 AI Edge Torch 生成式 API 在设备上使用自定义大语言模型

作者 / 首席工程师 Cormac Brick&#xff0c;软件工程师 Haoliang Zhang 我们很高兴地发布 AI Edge Torch 生成式 API&#xff0c;它能将开发者用 PyTorch 编写的高性能大语言模型 (LLM) 部署至 TensorFlow Lite (TFLite) 运行时&#xff0c;从而无缝地将新的设备端生成式 AI 模…
阅读更多...
[大模型]Gemma-2B-Instruct FastApi 部署调用

[大模型]Gemma-2B-Instruct FastApi 部署调用

环境准备 在 平台中租赁一个 3090 等 24G 显存的显卡机器&#xff0c;如下图所示镜像选择 PyTorch-->2.1.0-->3.10(ubuntu22.04)-->12.1。 接下来打开刚刚租用服务器的 JupyterLab&#xff0c;并且打开其中的终端开始环境配置、模型下载和运行演示。 pip 换源加速下载…
阅读更多...
[qt] qt程序打包以及docker镜像打包

[qt] qt程序打包以及docker镜像打包

目录 一 环境准备: 1.1 qt环境 1.2 linuxdeplouqt打包工具 二 qt包发布: 2.1 搜索链接库 2.2 应用程序APP打包 2.3 发布 三 docker镜像包发布 3.1 环境准备 3.2 镜像生产脚本 3.3 加载镜像并运行docker容器 一 环境准备: qt环境linuxdeployqt打包工具docker环境 1…
阅读更多...
Python学习打卡：day01

Python学习打卡：day01

day1 笔记来源于&#xff1a;黑马程序员python教程&#xff0c;8天python从入门到精通&#xff0c;学python看这套就够了 1、Python 软件&#xff08;PyCharm&#xff09; 安装&#xff1a;在 Linux 环境下安装 Pycharm 插件&#xff1a;汉化、翻译 设置字体大小 常用快捷…
阅读更多...
【MySQL】（基础篇五） —— 排序检索数据

【MySQL】（基础篇五） —— 排序检索数据

排序检索数据 本章将讲授如何使用SELECT语句的ORDER BY子句&#xff0c;根据需要排序检索出的数据。 排序数据 还是使用上一节中的例子,查询employees表中的last_name字段 SELECT last_name FROM employees;输出结果&#xff1a; 发现其输出并没有特定的顺序。其实&#xf…
阅读更多...
【Linux】进程3——PID/PPID，父进程，子进程

【Linux】进程3——PID/PPID，父进程，子进程

在讲父子进程之前&#xff0c;我们接着上面那篇继续讲 1.查看进程 mycode.c makefile 我们在zs_108直接编译mycode.c&#xff0c;直接运行&#xff0c;然后我们转换另一个账号来查看这个进程 我们可以通过ps指令来查看进程 我们就会好奇了&#xff0c;第二行是什么&#xff…
阅读更多...
牛客热题：矩阵的最小路径和

牛客热题：矩阵的最小路径和

&#x1f4df;作者主页&#xff1a;慢热的陕西人 &#x1f334;专栏链接&#xff1a;力扣刷题日记 &#x1f4e3;欢迎各位大佬&#x1f44d;点赞&#x1f525;关注&#x1f693;收藏&#xff0c;&#x1f349;留言 文章目录 牛客热题&#xff1a;矩阵的最小路径和题目链接方法一…
阅读更多...
[数据集][目标检测]变电站火灾检测电力场景烟雾明火检测数据集VOC+YOLO格式140张2类别真实场景非PS合成

[数据集][目标检测]变电站火灾检测电力场景烟雾明火检测数据集VOC+YOLO格式140张2类别真实场景非PS合成

数据集格式&#xff1a;Pascal VOC格式YOLO格式(不包含分割路径的txt文件&#xff0c;仅仅包含jpg图片以及对应的VOC格式xml文件和yolo格式txt文件) 图片数量(jpg文件个数)&#xff1a;140 标注数量(xml文件个数)&#xff1a;140 标注数量(txt文件个数)&#xff1a;140 标注类别…
阅读更多...
模型 SCAMPER创新法则

模型 SCAMPER创新法则

说明&#xff1a;系列文章 分享 模型&#xff0c;了解更多&#x1f449; 模型_思维模型目录。激发创新的七步思维法。 1 SCAMPER创新法则的应用 1.1 SCAMPER应用之改进自行车设计 一家自行车制造商希望改进其自行车设计&#xff0c;以吸引更多的消费者并提高市场份额。他们决…
阅读更多...
最新文章

Copyright @ 2022~2023