MySQL45讲 第十讲 MySQL为什么有时候会选错索引?

文章目录

  • MySQL45讲 第十讲 MySQL为什么有时候会选错索引?
    • 一、问题引入
    • 二、案例分析
      • 案例一
      • 案例二
    • 三、索引选择异常的处理方法
      • 1. 使用`force index`强行选择索引
      • 2. 修改语句引导优化器
      • 3. 新建或删除索引
    • 四、索引统计更新机制及解决索引统计不准确问题
    • 五、总结与思考

MySQL45讲 第十讲 MySQL为什么有时候会选错索引?

一、问题引入

在 MySQL 中,索引选择由优化器负责,其目标是以最小代价执行语句,但有时会选错索引,导致执行速度变慢。

二、案例分析

案例一

  1. 建表与数据插入
    • 创建表t,包含idab字段,分别建立主键索引和ab字段的普通索引。
    • 使用存储过程插入 10 万行数据,取值按整数递增。
  2. 初始查询情况
    • 执行select * from t where a between 10000 and 20000;,优化器选择索引a,扫描行数 10001 行,执行情况符合预期。
  3. 数据变更后的问题
    • session A开启事务,session B删除数据后重新插入 10 万行数据。
    • 此时session B再次执行上述查询,未选择索引a,而是走了全表扫描,扫描 10 万行,执行时间 40 毫秒。而使用force index(a)强制使用索引a时,扫描 10001 行,执行 21 毫秒。
  4. 原因分析
    • MySQL 通过索引的 “区分度”(基数)估算扫描行数,基数越大区分度越好,但采样统计方法导致基数不准。
    • 优化器预估使用索引a时每次从索引a取值后要回主键索引查整行数据,计算代价后认为直接扫描主键索引更快,然而实际执行时间表明此选择并非最优。

案例二

  1. 查询语句
    • select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
  2. 预期与实际索引选择
    • 从条件分析使用索引a扫描行数少应更快,但优化器选择了索引b,扫描行数预估为 50198 行。
  3. 原因分析
    • 优化器认为使用索引b可避免排序(b本身有序),即使扫描行数多也判定代价更小。

三、索引选择异常的处理方法

1. 使用force index强行选择索引

  • 优点:能直接指定索引,使优化器不再评估其他索引执行代价。
  • 缺点:写法不优美,索引改名时语句需修改,可能不兼容其他数据库,且变更及时性差,通常在问题出现后才修改 SQL 语句添加,测试发布过程不够敏捷。

2. 修改语句引导优化器

  • 例如将order by b limit 1改为order by b,a limit 1,使扫描行数成为影响决策的主要条件,诱导优化器选择索引a。但这种修改需根据数据特征和语句逻辑,不具备通用性。

3. 新建或删除索引

  • 新建更合适索引或删除误用索引,但在某些案例中可能难以找到合适的新增索引方法,也可能出现删除不必要索引后优化器重新选择正确索引的情况。

四、索引统计更新机制及解决索引统计不准确问题

  1. 索引统计方式
    • MySQL 采用采样统计方法,InnoDB 默认选择N个数据页统计不同值取平均值后乘以索引页面数得到基数。当变更数据行数超过1/M时自动触发重新统计。
    • 可通过设置innodb_stats_persistent参数选择统计信息存储方式,on表示持久化存储,默认N = 20M = 10off表示只存储在内存中,默认N = 8M = 16
  2. 解决索引统计不准确问题
    • 使用analyze table命令重新统计索引信息,可解决因索引统计不准确导致的优化器选错索引问题,但优化器还会综合其他因素判断。

五、总结与思考

  1. 优化器可能因索引统计信息不准确或其他因素选错索引,对于索引统计问题可用analyze table解决,对于其他误判情况可采用force index、修改语句或调整索引等方法。

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

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

openEuler的aarch64操作系统上安装k3s

1、需要安装docker容器引擎(省略) 2、安装ks3命令 curl -sfL https://rancher-mirror.rancher.cn/k3s/k3s-install.sh | INSTALL_K3S_MIRRORcn INSTALL_K3S_SKIP_SELINUX_RPMtrue INSTALL_K3S_SELINUX_WARNtrue sh -s -- --docker 其中&#xff1a…

Synchronized锁、锁的四种状态、锁的升级(偏向锁,轻量级锁,重量级锁)

目录 1. Synchronized锁 1.1 介绍 1.2 三种应用方式★ 1.2.1 synchronized同步方法 1.2.2 synchronized 同步静态方法 1.2.3 synchronized 同步代码块 1.3 Synchronized锁底层原理 1.3.1 简答 1.3.2 详述 1. Monitor对象 2. Monitor与对象锁关联时 具体的流程&#…

【网络】数据链路层

目录 以太网 以太网的帧格式 MSS 交换机 MTU对UDP的影响 ARP协议 数据链路层是软件层的最底层协议,它的下面就是物理层,那么下面我们就来介绍一下它负责在网络通信中完成什么工作 我们前面说的IP协议是解决如何进行跨网络转发的,也就是…

零基础‘自外网到内网’渗透过程详细记录(cc123靶场)——下

细节较多,篇幅较大,分为上/下两部分发布在两篇文章内 另一部分详见下面文章 零基础‘自外网到内网’渗透过程详细记录(cc123靶场)——上https://blog.csdn.net/weixin_62808713/article/details/143572185 八、第二层数据库服务器权限获取 猜到新闻资…

13-鸿蒙开发中的综合实战:华为登录界面

大家好,欢迎来到鸿蒙开发系列教程!今天,我们将通过一个综合实战项目来实现一个华为登录界面。这个项目将涵盖输入框组件、按钮组件、文本组件和布局容器的使用,帮助你更好地理解和应用这些组件。无论你是初学者还是有一定经验的开…

告别复杂协作:Adobe XD的简化替代方案

Adobe XD是一款集成UI/UX设计和原型创建功能的设计平台。它允许用户进行网页、移动应用的设计,以及原型的绘制,并且能够将静态设计转化为动态的交互原型。尽管Adobe XD提供了这些功能,但它依赖于第三方插件,且插件库有限&#xff…

ctfshow web文件上传 web166-170

1.web166 通过源码上传发现只能传zip,尝试一下图片上传也不行 把随便一张图片打包成zip文件,上传后发现有一个下载的地方,猜测是文件上传,尝试zip伪协议发现失败,打包php文件也失败了,不知为什么,&#x…

二开CS—上线流量特征shellcode生成修改模板修改反编译打包

前言 免杀几乎讲的差不多了,今天讲个CS的二次开发。我们原生态的CS特征肯定都是被提取完的了,包括它的流量特征,而我们要做的就是把它的流量特征给打乱,还可以修改生成的后门,使其生成即免杀。 实验环境 CS4.4&…

7.《双指针篇》---⑦三数之和(中等偏难)

题目传送门 方法一:双指针 1.新建一个顺序表用来返回结果。并排序数组。 2.for循环 i 从第一个数组元素遍历到倒数第三个数。 3.如果遍历过程中有值大于0的则break; 4.定义左右指针,以及target。int left i 1, right n - 1; int target -nums[i];…

Muse-Ant-Desgin-Vue 改造成 Vite+Vue3

后台地址:https://www.creative-tim.com/product/muse-vue-ant-design-dashboard?refantdv-official 一、配置 ViteAntDesginVue 配置ViteAntDesginVue ViteAntDesginVue配置:https://blog.csdn.net/qq_17523181/article/details/143241626 安装vue-ro…

实习作假:阿里健康实习做了RABC中台,还优化了短信发送流程

最近有二本同学说:“大拿老师,能帮忙看下简历吗?” 如果是从面试官的角度来看,这个同学的实习简历是很虚假的。 但是我们一直强调的是:校招的实习简历是不能出现明显的虚假。 首先,你去公司做事情&#…

疯狂Java讲义-Java基础类库

Java基础类库 本章思维导图 5-0Java基础类库.png 用户互动 使用Scanner获取键盘输入 Scanner主要提供了两个方法来扫描输入 hasNextXxx(); 是否还有下一个输入项,其中Xxx可以是int、long等代表基本数据类型的字符串。 nextXxx(); 获取下一个输入项。Xxx的含义与前一…

[前端] 为网站侧边栏添加搜索引擎模块

前言 最近想给我的个人网站侧边栏添加一个搜索引擎模块,可以引导用户帮助本站SEO优化(让用户可以通过点击搜索按钮完成一次对本人网站的搜索,从而实现对网站的搜索引擎优化)。 最开始,我只是想实现一个简单的百度搜索…

汇聚全球前沿科技产品,北京智能科技产业展览会·世亚智博会

在北京这座古老而又充满现代气息的城市中,一场科技与创新的盛宴正悄然上演——北京智能科技产业展览会(简称:世亚智博会),作为全球前沿科技的汇聚地,不仅展示了人工智能、5G通信、虚拟现实等尖端技术的最新…

JAVA基础:数组 (习题笔记)

一,编码题 1,数组查找操作:定义一个长度为10 的一维字符串数组,在每一个元素存放一个单词;然后运行时从命令行输入一个单词,程序判断数组是否包含有这个单词,包含这个单词就打印出“Yes”&…

猎板PCB2到10层数的科技进阶与应用解析

1. 单层板(Single-sided PCB) 定义:单层板是最基本的PCB类型,导线只出现在其中一面,因此被称为单面板。限制:由于只有一面可以布线,设计线路上有许多限制,不适合复杂电路。应用&…

2025年山东省考报名流程图解

2025年山东公务员考试备考开始 为大家整理了从笔试到录用的全部流程,希望可以帮助到你们!参考2024年山东省考公告整理,请以最新公告为准! 一、阅读公告和职位表 二、职位查询 三、网上报名 四、确认缴费 五、网上打印准考证 六、参…

修改elementUI等UI组件样式的5种方法总结,哪些情况需要使用/deep/, :deep()等方式来穿透方法大全

文章目录 方法 1:全局修改样式示例:修改 `ElMessage` 的背景色和字体颜色方法 2:修改特定类型的 `ElMessage` 样式-全局-不需要穿透示例:修改 `ElMessage` 成功类型的样式方法 3:通过 Scoped CSS 在组件内部修改-局部-不需要穿透方法 4:使用 JavaScript 动态修改样式-不需…

pandas——对齐运算+函数应用

引言:对齐运算是数据清洗的重要过程,可以按索引对齐进行运算,如果没对齐的位置则补NaN,最后也可以填充NaN 一、Series的对齐运算 1.Series 按行、索引对齐 import pandas as pds1 pd.Series(range(10, 20), indexrange(10)) s2…

# Ubuntu 达人九步养成记(1)

Ubuntu 达人九步养成记(1) 目录: 一、ubuntu基本安装 二、设置语言环境 三、设置服务器镜像源 四、在启动栏添加终端图标 五、使用apt更新和升级系统软件 六、使用apt安装软件 七、使用apt删除软件以及apt-get 八、deb格式及谷歌浏览…