当前位置: 首页 > news >正文

《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一致

image-20230219101950969

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

img

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的影响:

  1. 因为有 order by b,优化器认为走索引 b 可以避免排序;
  2. 又有 limit 1,优化器认为只要找到了 1 条满足条件的记录,索引 b 的遍历就可以提前终止,虽然可能要遍历 50001 条记录,但是优化器认为这是值得冒险的事,所以决定了走索引 b;

image-20230219103307630

强制使用索引

使用 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

image-20230219103625183

修改SQL语句

如果我们能让MySQL判断出,使用索引b的代价比索引a大,那么MySQL就能选择到正确的索引

所以,我们可以

  1. 干扰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了

image-20230219104106101

  1. 干扰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)。这两者数据库引擎按照同样的排序算法去排序,前者成本较小,但是数据库引擎并不能感知得了),所以扫描行数成了影响决策的主要条件。

image-20230219104243931

相关文章:

《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

简介&#xff08;下载地址&#xff09; Ba-FloatWindow2 是一款应用内并且无需授权的悬浮窗插件。支持多种拖动&#xff1b;自定义位置、大小&#xff1b;支持动态修改。 支持自动定义起始位置支持自定义悬浮窗大小支持贴边显示支持多种拖动方效果&#xff1a;不可拖动、任意…...

MMKV与mmap:全方位解析

概述 MMKV 是基于 mmap 内存映射的移动端通用 key-value 组件&#xff0c;底层序列化/反序列化使用 protobuf 实现&#xff0c;性能高&#xff0c;稳定性强。从 2015 年中至今&#xff0c;在 iOS 微信上使用已有近 3 年&#xff0c;其性能和稳定性经过了时间的验证。近期已移植…...

【信息系统项目管理师】项目管理十大知识领域记忆敲出(整体范围进度)

【信息系统项目管理师】项目管理十大知识领域记忆敲出&#xff08;整体范围进度&#xff09; 【信息系统项目管理师】项目管理十大知识领域记忆敲出&#xff08;整体范围进度&#xff09;【信息系统项目管理师】项目管理十大知识领域记忆敲出&#xff08;整体范围进度&#xff…...

一起学 pixijs(3):Sprite

大家好&#xff0c;我是前端西瓜哥。今天来学习 pixijs 的 Sprite。 Sprite pixijs 的 Sprite 类用于将一些纹理&#xff08;Texture&#xff09;渲染到屏幕上。 Sprite 直译为 “精灵”&#xff0c;是游戏开发中常见的术语&#xff0c;就是将一个角色的多个动作放到一个图片…...

深入讲解Kubernetes架构-垃圾收集

垃圾收集&#xff08;Garbage Collection&#xff09;是 Kubernetes 用于清理集群资源的各种机制的统称。 垃圾收集允许系统清理如下资源&#xff1a;终止的 Pod已完成的 Job不再存在属主引用的对象未使用的容器和容器镜像动态制备的、StorageClass 回收策略为 Delete 的 PV 卷…...

Flink03: 集群安装部署

Flink支持多种安装部署方式 StandaloneON YARNMesos、Kubernetes、AWS… 这些安装方式我们主要讲一下standalone和on yarn。 如果是一个独立环境的话&#xff0c;可能会用到standalone集群模式。 在生产环境下一般还是用on yarn 这种模式比较多&#xff0c;因为这样可以综合利…...

OCR项目实战(一):手写汉语拼音识别(Pytorch版)

✨写在前面&#xff1a;强烈推荐给大家一个优秀的人工智能学习网站&#xff0c;内容包括人工智能基础、机器学习、深度学习神经网络等&#xff0c;详细介绍各部分概念及实战教程&#xff0c;非常适合人工智能领域初学者及研究者学习。➡️点击跳转到网站。 &#x1f4dd;OCR专栏…...

【js】export default也在影响项目性能呢

这里写目录标题介绍先说结论分析解决介绍 无意间看到一个关于export与exprot default对比的话题&#xff0c; 于是对二者关于性能方面&#xff0c;有了想法&#xff0c;二者的区别&#xff0c;仅仅是在于写法吗&#xff1f; 于是&#xff0c;有了下面的测试。 先说结论 太长…...

《软件安全》 彭国军 阅读总结

对于本书&#xff0c;小编本意是对其讲述的内容&#xff0c;分点进行笔记的整理&#xff0c;后来学习以后&#xff0c;发现&#xff0c;这本书应该不算是一本技术提升类的书籍&#xff0c;更像是一本领域拓展和知识科普类书籍&#xff0c;所讲知识广泛&#xff0c;但是较少实践…...

深入讲解Kubernetes架构-节点与控制面之间的通信

本文列举控制面节点&#xff08;确切说是 API 服务器&#xff09;和 Kubernetes 集群之间的通信路径。 目的是为了让用户能够自定义他们的安装&#xff0c;以实现对网络配置的加固&#xff0c; 使得集群能够在不可信的网络上&#xff08;或者在一个云服务商完全公开的 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源码-全局事务提交 服务端源码

前面的博客中&#xff0c;我们介绍了&#xff0c;发起全局事务时&#xff0c;是如何进行全局事务提交的&#xff0c;这篇博客&#xff0c;主要记录&#xff0c;在seata分布式事务中&#xff0c;全局事务提交的时候&#xff0c;服务端是如何进行处理的 发起全局事务提交操作 事…...

C++ 模板

文章目录一、泛型编程二、 函数模板三、类模板一、泛型编程 泛型编程&#xff1a;编写与类型无关的通用代码&#xff0c;代码复用的一种方法 在 C 中&#xff0c;我们可以通过函数重载实现通用的交换函数 Swap &#xff0c;但是有一些缺点 重载函数只有类型不同&#xff0c;…...

JWT安全漏洞以及常见攻击方式

前言 随着web应用的日渐复杂化&#xff0c;某些场景下&#xff0c;仅使用Cookie、Session等常见的身份鉴别方式无法满足业务的需要&#xff0c;JWT也就应运而生&#xff0c;JWT可以有效的解决分布式场景下的身份鉴别问题&#xff0c;并且会规避掉一些安全问题&#xff0c;如CO…...

华为OD机试题 - 最小施肥机能效(JavaScript)

最近更新的博客 华为OD机试题 - 任务总执行时长(JavaScript) 华为OD机试题 - 开放日活动(JavaScript) 华为OD机试 - 最近的点 | 备考思路,刷题要点,答疑 【新解法】 华为OD机试题 - 最小步骤数(JavaScript) 华为OD机试题 - 任务混部(JavaScript) 华为OD机试题 - N 进…...

Python(1)变量的命名规则

目录 1.变量的命名原则 3.内置函数尽量不要做变量 4.删除变量和垃圾回收机制 5.结语 参考资料 1.变量的命名原则 ①由英文字母、_(下划线)、或中文开头 ②变量名称只能由英文字母、数字、下画线或中文字所组成。 ③英文字母大小写不相同 实例&#xff1a; 爱_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操作系统内核的外壳&#xff0c;它为用户提供使用操作系统的命令接口。 用户在提示符下输入的每个命令都由shell先解释然后发给Linux内核&#xff0c;所以Linux中的命令通称为shell命令。 通常我们使用shell来使用Linux操作系统。Linux系统的shell是…...

golang循环变量捕获问题​​

在 Go 语言中&#xff0c;当在循环中启动协程&#xff08;goroutine&#xff09;时&#xff0c;如果在协程闭包中直接引用循环变量&#xff0c;可能会遇到一个常见的陷阱 - ​​循环变量捕获问题​​。让我详细解释一下&#xff1a; 问题背景 看这个代码片段&#xff1a; fo…...

日语学习-日语知识点小记-构建基础-JLPT-N4阶段(33):にする

日语学习-日语知识点小记-构建基础-JLPT-N4阶段(33):にする 1、前言(1)情况说明(2)工程师的信仰2、知识点(1) にする1,接续:名词+にする2,接续:疑问词+にする3,(A)は(B)にする。(2)復習:(1)复习句子(2)ために & ように(3)そう(4)にする3、…...

AtCoder 第409​场初级竞赛 A~E题解

A Conflict 【题目链接】 原题链接&#xff1a;A - Conflict 【考点】 枚举 【题目大意】 找到是否有两人都想要的物品。 【解析】 遍历两端字符串&#xff0c;只有在同时为 o 时输出 Yes 并结束程序&#xff0c;否则输出 No。 【难度】 GESP三级 【代码参考】 #i…...

电脑插入多块移动硬盘后经常出现卡顿和蓝屏

当电脑在插入多块移动硬盘后频繁出现卡顿和蓝屏问题时&#xff0c;可能涉及硬件资源冲突、驱动兼容性、供电不足或系统设置等多方面原因。以下是逐步排查和解决方案&#xff1a; 1. 检查电源供电问题 问题原因&#xff1a;多块移动硬盘同时运行可能导致USB接口供电不足&#x…...

Java 加密常用的各种算法及其选择

在数字化时代&#xff0c;数据安全至关重要&#xff0c;Java 作为广泛应用的编程语言&#xff0c;提供了丰富的加密算法来保障数据的保密性、完整性和真实性。了解这些常用加密算法及其适用场景&#xff0c;有助于开发者在不同的业务需求中做出正确的选择。​ 一、对称加密算法…...

[Java恶补day16] 238.除自身以外数组的乘积

给你一个整数数组 nums&#xff0c;返回 数组 answer &#xff0c;其中 answer[i] 等于 nums 中除 nums[i] 之外其余各元素的乘积 。 题目数据 保证 数组 nums之中任意元素的全部前缀元素和后缀的乘积都在 32 位 整数范围内。 请 不要使用除法&#xff0c;且在 O(n) 时间复杂度…...

如何理解 IP 数据报中的 TTL?

目录 前言理解 前言 面试灵魂一问&#xff1a;说说对 IP 数据报中 TTL 的理解&#xff1f;我们都知道&#xff0c;IP 数据报由首部和数据两部分组成&#xff0c;首部又分为两部分&#xff1a;固定部分和可变部分&#xff0c;共占 20 字节&#xff0c;而即将讨论的 TTL 就位于首…...

力扣-35.搜索插入位置

题目描述 给定一个排序数组和一个目标值&#xff0c;在数组中找到目标值&#xff0c;并返回其索引。如果目标值不存在于数组中&#xff0c;返回它将会被按顺序插入的位置。 请必须使用时间复杂度为 O(log n) 的算法。 class Solution {public int searchInsert(int[] nums, …...

Yolov8 目标检测蒸馏学习记录

yolov8系列模型蒸馏基本流程&#xff0c;代码下载&#xff1a;这里本人提交了一个demo:djdll/Yolov8_Distillation: Yolov8轻量化_蒸馏代码实现 在轻量化模型设计中&#xff0c;**知识蒸馏&#xff08;Knowledge Distillation&#xff09;**被广泛应用&#xff0c;作为提升模型…...

MySQL JOIN 表过多的优化思路

当 MySQL 查询涉及大量表 JOIN 时&#xff0c;性能会显著下降。以下是优化思路和简易实现方法&#xff1a; 一、核心优化思路 减少 JOIN 数量 数据冗余&#xff1a;添加必要的冗余字段&#xff08;如订单表直接存储用户名&#xff09;合并表&#xff1a;将频繁关联的小表合并成…...