《MySQL学习》 MySQL优化器选择如何选择索引
一.优化器的选择逻辑
建表语句
CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `a` (`a`),KEY `b` (`b`)
) ENGINE=InnoDB;
往表中插入10W条数据
delimiter ;;
create procedure idata()
begindeclare i int;set i=1;while(i<=100000)doinsert into t (`a`,`b`) values(i, i);set i=i+1;end while;
end;;
delimiter ;
call idata();
接着执行SQL语句
select * from t where a between 10000 and 20000;
由于a上有普通索引,索引优化器肯定会选择使用a索引,与explain一致

但是如果此时有另外一个事务开启了一致性视图,如下所示

session A在 session B 之前开启了一致性视图,并且没有提交,那么 此时的 undo log 不能被清理,虽然此时 session B 做了删除操作,但数据不会被真正的删除。因此,在session B 再次插入10W条数据后 此时 undo log 保存了 20W的版本信息,当前数据页的数据页无法被覆盖,只能用另外的数据页来存储数据
而此时的session B 的分析结果将会出现扫描 10W行的情况,走了全表扫描,并没有使用到索引 a
导致此现象产生的原因 是由于受一致性视图的影响,导致计算索引的区分度出现了偏差,预估了错误的扫描行锁,而索引a 非主键索引,还需要回表进行一次查询操作,多一次IO操作的代价使MySQL的优化器觉得不如走全表扫描
当发现MySQL出现明细的统计数据行数出现异常后,我们可以执行以下命令重新统计索引信息,解决采样导致的扫描行数出错的问题
analyze table t
二.如何解决MySQL选错索引
基于上述的建表语句与数据,当我们执行下面的查询语句时,
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
预期时扫描1001行,但实际上MySQL扫描了50191行,远远超出我们的预期,这是由于 oder by 和 limitd的影响:
- 因为有 order by b,优化器认为走索引 b 可以避免排序;
- 又有 limit 1,优化器认为只要找到了 1 条满足条件的记录,索引 b 的遍历就可以提前终止,虽然可能要遍历 50001 条记录,但是优化器认为这是值得冒险的事,所以决定了走索引 b;

强制使用索引
使用 force index(a) 语句后,强制使用索引 a,这时候发现扫描的行数只有1000了,符合我们的预期,MySQL不得不作出正确的选择
explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1

修改SQL语句
如果我们能让MySQL判断出,使用索引b的代价比索引a大,那么MySQL就能选择到正确的索引
所以,我们可以
- 干扰limit 判断
explain select * from ( select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100) tt limit 1
最初的SQL语句因为b不用排序,又有limit 1,从5w里只要找到一条就可以返回了,如果选择a,因为要排序,就要扫完1000条,然后才能排序,这成本明显太大,所以选择了b。但如果是limit 100,选择b,虽然不用排序,但找到第一条记录后,还要向后查询,看后面有没有满足条件的100个记录,从5w中找100个的成本就大于从1000找100个的成本了,所以选择a。其实limit 20就会选择a了

-
干扰order by判断
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1要求按照b,a排序,无论选择b索引还是a索引,都只需要再将另外一个字段排序(个人认为索引b已经对b排好序,再对a排序;索引a已经对a排好序,再对b排序成(b,a)。这两者数据库引擎按照同样的排序算法去排序,前者成本较小,但是数据库引擎并不能感知得了),所以扫描行数成了影响决策的主要条件。

相关文章:
《MySQL学习》 MySQL优化器选择如何选择索引
一.优化器的选择逻辑 建表语句 CREATE TABLE t (id int(11) NOT NULL AUTO_INCREMENT,a int(11) DEFAULT NULL,b int(11) DEFAULT NULL,PRIMARY KEY (id),KEY a (a),KEY b (b) ) ENGINEInnoDB;往表中插入10W条数据 delimiter ;; create procedure idata() begindeclare i in…...
uniapp 悬浮窗(应用内、无需授权) Ba-FloatWindow2
简介(下载地址) Ba-FloatWindow2 是一款应用内并且无需授权的悬浮窗插件。支持多种拖动;自定义位置、大小;支持动态修改。 支持自动定义起始位置支持自定义悬浮窗大小支持贴边显示支持多种拖动方效果:不可拖动、任意…...
MMKV与mmap:全方位解析
概述 MMKV 是基于 mmap 内存映射的移动端通用 key-value 组件,底层序列化/反序列化使用 protobuf 实现,性能高,稳定性强。从 2015 年中至今,在 iOS 微信上使用已有近 3 年,其性能和稳定性经过了时间的验证。近期已移植…...
【信息系统项目管理师】项目管理十大知识领域记忆敲出(整体范围进度)
【信息系统项目管理师】项目管理十大知识领域记忆敲出(整体范围进度) 【信息系统项目管理师】项目管理十大知识领域记忆敲出(整体范围进度)【信息系统项目管理师】项目管理十大知识领域记忆敲出(整体范围进度ÿ…...
一起学 pixijs(3):Sprite
大家好,我是前端西瓜哥。今天来学习 pixijs 的 Sprite。 Sprite pixijs 的 Sprite 类用于将一些纹理(Texture)渲染到屏幕上。 Sprite 直译为 “精灵”,是游戏开发中常见的术语,就是将一个角色的多个动作放到一个图片…...
深入讲解Kubernetes架构-垃圾收集
垃圾收集(Garbage Collection)是 Kubernetes 用于清理集群资源的各种机制的统称。 垃圾收集允许系统清理如下资源:终止的 Pod已完成的 Job不再存在属主引用的对象未使用的容器和容器镜像动态制备的、StorageClass 回收策略为 Delete 的 PV 卷…...
Flink03: 集群安装部署
Flink支持多种安装部署方式 StandaloneON YARNMesos、Kubernetes、AWS… 这些安装方式我们主要讲一下standalone和on yarn。 如果是一个独立环境的话,可能会用到standalone集群模式。 在生产环境下一般还是用on yarn 这种模式比较多,因为这样可以综合利…...
OCR项目实战(一):手写汉语拼音识别(Pytorch版)
✨写在前面:强烈推荐给大家一个优秀的人工智能学习网站,内容包括人工智能基础、机器学习、深度学习神经网络等,详细介绍各部分概念及实战教程,非常适合人工智能领域初学者及研究者学习。➡️点击跳转到网站。 📝OCR专栏…...
【js】export default也在影响项目性能呢
这里写目录标题介绍先说结论分析解决介绍 无意间看到一个关于export与exprot default对比的话题, 于是对二者关于性能方面,有了想法,二者的区别,仅仅是在于写法吗? 于是,有了下面的测试。 先说结论 太长…...
《软件安全》 彭国军 阅读总结
对于本书,小编本意是对其讲述的内容,分点进行笔记的整理,后来学习以后,发现,这本书应该不算是一本技术提升类的书籍,更像是一本领域拓展和知识科普类书籍,所讲知识广泛,但是较少实践…...
深入讲解Kubernetes架构-节点与控制面之间的通信
本文列举控制面节点(确切说是 API 服务器)和 Kubernetes 集群之间的通信路径。 目的是为了让用户能够自定义他们的安装,以实现对网络配置的加固, 使得集群能够在不可信的网络上(或者在一个云服务商完全公开的 IP 上&am…...
120个IT冷知识,看完就不愁做选择题了
目录 IT冷知识 01-10 1.冰淇淋馅料 2.蠕虫起源 3.Linux和红帽子 4."间谍软件"诞生 5.游戏主机的灵魂 6.Linux之父 7.NetBSD的口号 8.安卓起源 9.不是第七代的 Win 7 10.域名金字塔 11~20 11.神奇魔盒 12. 第一个Ubuntu 正式版本 13.巾帼英雄 14.密码…...
Java之动态规划之机器人移动
目录 0.动态规划问题 一.不同路径 1.题目描述 2.问题分析 3.代码实现 二.不同路径 II 1.题目描述 2.问题分析 3.代码实现 三.机器人双向走路 1.题目描述 2.问题分析 3.代码实现 0.动态规划问题 动态规划(Dynamic Programming)算法的核心思想是:将大问题划分为小问…...
seata源码-全局事务提交 服务端源码
前面的博客中,我们介绍了,发起全局事务时,是如何进行全局事务提交的,这篇博客,主要记录,在seata分布式事务中,全局事务提交的时候,服务端是如何进行处理的 发起全局事务提交操作 事…...
C++ 模板
文章目录一、泛型编程二、 函数模板三、类模板一、泛型编程 泛型编程:编写与类型无关的通用代码,代码复用的一种方法 在 C 中,我们可以通过函数重载实现通用的交换函数 Swap ,但是有一些缺点 重载函数只有类型不同,…...
JWT安全漏洞以及常见攻击方式
前言 随着web应用的日渐复杂化,某些场景下,仅使用Cookie、Session等常见的身份鉴别方式无法满足业务的需要,JWT也就应运而生,JWT可以有效的解决分布式场景下的身份鉴别问题,并且会规避掉一些安全问题,如CO…...
华为OD机试题 - 最小施肥机能效(JavaScript)
最近更新的博客 华为OD机试题 - 任务总执行时长(JavaScript) 华为OD机试题 - 开放日活动(JavaScript) 华为OD机试 - 最近的点 | 备考思路,刷题要点,答疑 【新解法】 华为OD机试题 - 最小步骤数(JavaScript) 华为OD机试题 - 任务混部(JavaScript) 华为OD机试题 - N 进…...
Python(1)变量的命名规则
目录 1.变量的命名原则 3.内置函数尽量不要做变量 4.删除变量和垃圾回收机制 5.结语 参考资料 1.变量的命名原则 ①由英文字母、_(下划线)、或中文开头 ②变量名称只能由英文字母、数字、下画线或中文字所组成。 ③英文字母大小写不相同 实例: 爱_aiA1 print(…...
Shiro1.9学习笔记
文章目录一、Shiro概述1、Shiro简介1.1 介绍1.2 Shiro特点2、Shiro与SpringSecurity的对比3、Shiro基本功能4、Shiro原理4.1 Shiro 架构(外部)4.2 shiro架构(内部)二、Shiro基本使用1、环境准备2、登录认证2.1 登录认证概念2.2 登录认证基本流程2.3 登录认证实例2.4 身份认证源…...
2.5|iot|嵌入式Linux系统开发与应用|第4章:Linux外壳shell脚本程序编程
1.shell基础 Shell是Linux操作系统内核的外壳,它为用户提供使用操作系统的命令接口。 用户在提示符下输入的每个命令都由shell先解释然后发给Linux内核,所以Linux中的命令通称为shell命令。 通常我们使用shell来使用Linux操作系统。Linux系统的shell是…...
【大模型RAG】拍照搜题技术架构速览:三层管道、两级检索、兜底大模型
摘要 拍照搜题系统采用“三层管道(多模态 OCR → 语义检索 → 答案渲染)、两级检索(倒排 BM25 向量 HNSW)并以大语言模型兜底”的整体框架: 多模态 OCR 层 将题目图片经过超分、去噪、倾斜校正后,分别用…...
调用支付宝接口响应40004 SYSTEM_ERROR问题排查
在对接支付宝API的时候,遇到了一些问题,记录一下排查过程。 Body:{"datadigital_fincloud_generalsaas_face_certify_initialize_response":{"msg":"Business Failed","code":"40004","sub_msg…...
Oracle查询表空间大小
1 查询数据库中所有的表空间以及表空间所占空间的大小 SELECTtablespace_name,sum( bytes ) / 1024 / 1024 FROMdba_data_files GROUP BYtablespace_name; 2 Oracle查询表空间大小及每个表所占空间的大小 SELECTtablespace_name,file_id,file_name,round( bytes / ( 1024 …...
使用分级同态加密防御梯度泄漏
抽象 联邦学习 (FL) 支持跨分布式客户端进行协作模型训练,而无需共享原始数据,这使其成为在互联和自动驾驶汽车 (CAV) 等领域保护隐私的机器学习的一种很有前途的方法。然而,最近的研究表明&…...
质量体系的重要
质量体系是为确保产品、服务或过程质量满足规定要求,由相互关联的要素构成的有机整体。其核心内容可归纳为以下五个方面: 🏛️ 一、组织架构与职责 质量体系明确组织内各部门、岗位的职责与权限,形成层级清晰的管理网络…...
unix/linux,sudo,其发展历程详细时间线、由来、历史背景
sudo 的诞生和演化,本身就是一部 Unix/Linux 系统管理哲学变迁的微缩史。来,让我们拨开时间的迷雾,一同探寻 sudo 那波澜壮阔(也颇为实用主义)的发展历程。 历史背景:su的时代与困境 ( 20 世纪 70 年代 - 80 年代初) 在 sudo 出现之前,Unix 系统管理员和需要特权操作的…...
Rust 异步编程
Rust 异步编程 引言 Rust 是一种系统编程语言,以其高性能、安全性以及零成本抽象而著称。在多核处理器成为主流的今天,异步编程成为了一种提高应用性能、优化资源利用的有效手段。本文将深入探讨 Rust 异步编程的核心概念、常用库以及最佳实践。 异步编程基础 什么是异步…...
【JavaSE】绘图与事件入门学习笔记
-Java绘图坐标体系 坐标体系-介绍 坐标原点位于左上角,以像素为单位。 在Java坐标系中,第一个是x坐标,表示当前位置为水平方向,距离坐标原点x个像素;第二个是y坐标,表示当前位置为垂直方向,距离坐标原点y个像素。 坐标体系-像素 …...
OpenPrompt 和直接对提示词的嵌入向量进行训练有什么区别
OpenPrompt 和直接对提示词的嵌入向量进行训练有什么区别 直接训练提示词嵌入向量的核心区别 您提到的代码: prompt_embedding = initial_embedding.clone().requires_grad_(True) optimizer = torch.optim.Adam([prompt_embedding...
【HTTP三个基础问题】
面试官您好!HTTP是超文本传输协议,是互联网上客户端和服务器之间传输超文本数据(比如文字、图片、音频、视频等)的核心协议,当前互联网应用最广泛的版本是HTTP1.1,它基于经典的C/S模型,也就是客…...
