当前位置: 首页 > 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是…...

浅谈 React Hooks

React Hooks 是 React 16.8 引入的一组 API&#xff0c;用于在函数组件中使用 state 和其他 React 特性&#xff08;例如生命周期方法、context 等&#xff09;。Hooks 通过简洁的函数接口&#xff0c;解决了状态与 UI 的高度解耦&#xff0c;通过函数式编程范式实现更灵活 Rea…...

Chapter03-Authentication vulnerabilities

文章目录 1. 身份验证简介1.1 What is authentication1.2 difference between authentication and authorization1.3 身份验证机制失效的原因1.4 身份验证机制失效的影响 2. 基于登录功能的漏洞2.1 密码爆破2.2 用户名枚举2.3 有缺陷的暴力破解防护2.3.1 如果用户登录尝试失败次…...

linux之kylin系统nginx的安装

一、nginx的作用 1.可做高性能的web服务器 直接处理静态资源&#xff08;HTML/CSS/图片等&#xff09;&#xff0c;响应速度远超传统服务器类似apache支持高并发连接 2.反向代理服务器 隐藏后端服务器IP地址&#xff0c;提高安全性 3.负载均衡服务器 支持多种策略分发流量…...

Redis相关知识总结(缓存雪崩,缓存穿透,缓存击穿,Redis实现分布式锁,如何保持数据库和缓存一致)

文章目录 1.什么是Redis&#xff1f;2.为什么要使用redis作为mysql的缓存&#xff1f;3.什么是缓存雪崩、缓存穿透、缓存击穿&#xff1f;3.1缓存雪崩3.1.1 大量缓存同时过期3.1.2 Redis宕机 3.2 缓存击穿3.3 缓存穿透3.4 总结 4. 数据库和缓存如何保持一致性5. Redis实现分布式…...

在HarmonyOS ArkTS ArkUI-X 5.0及以上版本中,手势开发全攻略:

在 HarmonyOS 应用开发中&#xff0c;手势交互是连接用户与设备的核心纽带。ArkTS 框架提供了丰富的手势处理能力&#xff0c;既支持点击、长按、拖拽等基础单一手势的精细控制&#xff0c;也能通过多种绑定策略解决父子组件的手势竞争问题。本文将结合官方开发文档&#xff0c…...

Day131 | 灵神 | 回溯算法 | 子集型 子集

Day131 | 灵神 | 回溯算法 | 子集型 子集 78.子集 78. 子集 - 力扣&#xff08;LeetCode&#xff09; 思路&#xff1a; 笔者写过很多次这道题了&#xff0c;不想写题解了&#xff0c;大家看灵神讲解吧 回溯算法套路①子集型回溯【基础算法精讲 14】_哔哩哔哩_bilibili 完…...

解锁数据库简洁之道:FastAPI与SQLModel实战指南

在构建现代Web应用程序时&#xff0c;与数据库的交互无疑是核心环节。虽然传统的数据库操作方式&#xff08;如直接编写SQL语句与psycopg2交互&#xff09;赋予了我们精细的控制权&#xff0c;但在面对日益复杂的业务逻辑和快速迭代的需求时&#xff0c;这种方式的开发效率和可…...

postgresql|数据库|只读用户的创建和删除(备忘)

CREATE USER read_only WITH PASSWORD 密码 -- 连接到xxx数据库 \c xxx -- 授予对xxx数据库的只读权限 GRANT CONNECT ON DATABASE xxx TO read_only; GRANT USAGE ON SCHEMA public TO read_only; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; GRANT EXECUTE O…...

Python如何给视频添加音频和字幕

在Python中&#xff0c;给视频添加音频和字幕可以使用电影文件处理库MoviePy和字幕处理库Subtitles。下面将详细介绍如何使用这些库来实现视频的音频和字幕添加&#xff0c;包括必要的代码示例和详细解释。 环境准备 在开始之前&#xff0c;需要安装以下Python库&#xff1a;…...

SpringTask-03.入门案例

一.入门案例 启动类&#xff1a; package com.sky;import lombok.extern.slf4j.Slf4j; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.cache.annotation.EnableCach…...