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

MySQL之查询性能优化(十)

查询性能优化

MySQL查询优化器的局限性

松散索引扫描

由于历史原因,MySQL并不支持松散索引扫描,也就无法按照不连续的方式扫描一个索引。通常,MySQL的索引扫描需要先定义一个起点和终点,即使需要的数据只是这段索引中很少数的几个,MySQL仍需要扫描这段儿索引中的每一个条目。下面我们通过一个示例说明这点。假设我们有如下索引(a,b),有下面的查询:

mysql>SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;

因为索引的前导字段是列a,但是在查询中只指定了字段b,MySQL无法使用这个索引,从而只能通过全表扫描找到匹配的行,在这里插入图片描述
如图所示。了解索引的物理结构的话,不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构(不是存储引擎的API)使得可以先扫描a列的第一个值对应的b列的范围,然后再跳到a列不同第二个不同值扫描对应的b列的范围。在这里插入图片描述
如图所示展示了如果由MySQL来实现这个过程会怎样。注意到,这时就无须再使用WHERE子句过滤,因为松散索引扫描已经跳过了所有不需要的记录。上面是一个简单的例子,除了松散索引扫描,新增一个合适的索引当然也可以优化上述查询。但对于某些场景,增加索引是没用的,例如,对于第一个索引列是范围条件,第二个索引列是等值条件的查询,靠增加索引就无法解决问题。
MySQL5.0之后的版本,在某些特殊的场景下是可以使用松散索引扫描的,例如,在一个分组查询中需要找到分组的最大值和最小值:

mysql> EXPLAIN SELECT actor_id, MAX(film_id)-> FROM sakila.film_actor-> GROUP BY actor_id\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: range
possible_keys: PRIMARY,idx_fk_film_idkey: PRIMARYkey_len: 2ref: NULLrows: 201filtered: 100.00Extra: Using index for group-by

在EXPLAIN中的Extra字段显示"Using index for group-by",表示这里将使用松散索引扫描,不过如果MySQL能写上"loose index probe",相信会更好理解。在MySQL很好地支持松散索引扫描之前,一个简单的绕过问题的办法就是给前面的列加上可能的常数值。在MySQL5.6之后的版本,关于松散索引扫描的一些限制将会通过"索引下推(index condition pushdown)"的方式解决

最大值和最小值优化

对于MIN()和MAX()查询,MySQL的优化做得并不好。这里有一个例子:

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE';

因为在first_name字段上并没有索引,因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描,那么理论上,当MySQL读到的第一个满足条件的记录的时候,就是我们需要找到的最小值了,因为主键是严格按照actor_id字段的大小顺序排列的。但是MySQL这时只会做全表扫描,我们可以通过查看SHOW STATUS的全表扫描计数器来验证这一点。一个曲线的优化办法是移除MIN(),然后使用LIMIT来讲查询重写如下:

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)-> WHERE first_name = 'PENELOPE' LIMIT 1;
+----------+
| actor_id |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

这个策略可以让MySQL扫描尽可能少的记录数。如果你是一个完美主义者,可能会说这个SQL已经无法表达她的本意了。一般我们通过SQL告诉服务器我们需要什么数据,由服务器来决定如何最优地获取数据,不过在这个案例中,我们其实是告诉MySQL如何去获取我们需要的数据,通过SQL并不能一眼就看出我们其实是想要一个最小值。确实如此,有时候为了获得更高的性能,我们不得不放弃一些原则

在同一个表上查询和更新

MySQL不允许对同一个张表同时进行查询和更新。这其实并不是优化器的限制,如果清楚MySQL是如何执行查询,就可以避免这种情况。下面是一个无法运行的SQL,虽然这是一个符合标准的SQL语句。这个SQL语句尝试将两个表中相似行的数量记录到字段cnt中:

mysql> UPDATE tbl AS outer_tbl-> SET cnt = (-> SELECT COUNT(*) FROM tbl AS inner_tbl-> WHERE inner_tbl.type = outer_tbl.type-> );
ERROR 1093(HY000):You can't specify target table 'outer_tbl' for update in FROM clause

可以通过使用生成表的形式来绕过上面的限制,因为MySQL只会把这个表当作一个临时表来处理。实际上,这执行了两个查询:一个是子查询中的SELECT语句,另一个是多表关联UPDATE,只是关联的表是一个临时表。子查询会在UPDATE语句打开表之前就完成。所以下面的查询将会正常执行:

mysql> UPDATE tbl-> INNER JOIN (-> SELECT type, count(*) AS cnt-> FROM tbl-> GROUP BY type-> ) AS der USING(type)-> SET tbl.cnt = der.cnt;

相关文章:

MySQL之查询性能优化(十)

查询性能优化 MySQL查询优化器的局限性 松散索引扫描 由于历史原因,MySQL并不支持松散索引扫描,也就无法按照不连续的方式扫描一个索引。通常,MySQL的索引扫描需要先定义一个起点和终点,即使需要的数据只是这段索引中很少数的几…...

短视频矩阵源码----如何做正规开发规则分享:

一、什么是SaaS化服务技术开发? (短视频矩阵系统是源头开发的应该分为3个端口---- 总后台控制端、总代理端口,总商户后台) SaaS是软件即服务(Software as a Service)的缩写。它是一种通过互联网提供软件应…...

4. JavaScript 循环与迭代

JavaScript 中提供了这些循环语句&#xff1a; for 语句do … while 语句while 语句label 语句 跳出多级循环 var num 0; outPoint: for (var i 0; i < 10; i) {for (var j 0; j < 10; j) {if (i 5 && j 5) {break outPoint; // 在 i 5&#xff0c;j 5 …...

智能视频监控平台LntonCVS视频融合共享平台保障露营安全解决方案

在当今社会&#xff0c;都市生活的快节奏和压力使得越来越多的人渴望逃离城市的喧嚣&#xff0c;寻求一种短暂的慢生活体验。他们向往在壮丽的山河之间或宁静的乡村中露营&#xff0c;享受大自然的宁静与美好。随着露营活动的普及&#xff0c;露营地的场景也变得更加丰富多样&a…...

python如何画函数图像

通过图像可以直观地学习函数变化、分布等规律&#xff0c;在学习函数、概率分布等方面效果显著。下面我们尝试用Python的2D绘图库matplotlib来绘制函数图像。 下面我们来实现一个简单的函数&#xff1a; 首先&#xff0c;调用matplotlib库和numpy库 import matplotlib.pyplot …...

zeppelin(kylin的可视化界面安装)(从头到尾安装)

zeppelin(kylin的可视化界面安装) 1、zeppelin安装前的准备工作&#xff1a; 1、虚拟机安装配置好jdk&#xff0c; 2、虚拟机安装配置好Hadoop&#xff0c; 3、虚拟机安装配置好Hive 4、虚拟机安装配置了hbase 5、安装了kylin&#xff08;麒麟&#xff09; 6、下载了zeppelin…...

python词云生成库-wordcloud

内容目录 一、模块介绍二、WordCloud常用的方法1. generate(self, text)2. generate_from_frequencies(frequencies)3. fit_words(frequencies)4. generate_from_text(text) 三、进阶技巧1. 设置蒙版2. 设置过滤词 WordCloud 是一个用于生成词云的 Python 库&#xff0c;它可以…...

鸿蒙开发接口数据管理:【@ohos.data.rdb (关系型数据库)】

关系型数据库 关系型数据库&#xff08;Relational Database&#xff0c;RDB&#xff09;是一种基于关系模型来管理数据的数据库。关系型数据库基于SQLite组件提供了一套完整的对本地数据库进行管理的机制&#xff0c;对外提供了一系列的增、删、改、查等接口&#xff0c;也可…...

Java返回前端Bigdecimal类型数据时“0E-8“及小数点多余0的问题

目录 问题描述: 解决方法: 重要代码&#xff1a; 问题描述: 项目中oracle数据库需要转换为mysql&#xff0c;Oracle中的表字段定义为number(36,16)类型的工具自动转换为mysql的decimal(36,16)。在Oracle数据库中&#xff0c;number(36,16)类型的字段&#xff0c;使用BigDeci…...

标题:深入探索Linux中的`ausyscall`

标题&#xff1a;深入探索Linux中的ausyscall&#xff08;注意&#xff1a;ausyscall并非Linux内核标准命令&#xff0c;但我们可以探讨类似的概念&#xff09; 在Linux系统中&#xff0c;系统调用&#xff08;syscall&#xff09;是用户空间程序与内核空间进行交互的一种重要…...

CorelDRAW2024发布更新啦!设计师们的得力助手

在数字化的今天&#xff0c;视觉设计已经成为我们生活中不可或缺的一部分。从手机界面到广告海报&#xff0c;从网页布局到包装设计&#xff0c;每一个细节都离不开设计师们的专业与创意。然而&#xff0c;面对日益增长的设计需求和不断提升的审美标准&#xff0c;许多设计师开…...

SpringMVC日期格式处理 分页条件查询

实现日期格式处理&#xff1a; springmvc能实现String类型和基本数据类型及包装类的自动格式转换&#xff0c;但是不能识别String和 日期类格式的自动转换。 实现方式&#xff1a; 1是在实体类上加上注解DateTimeFormat&#xff0c;识别String格式为“yyyy-MM-dd” 2使用自定义…...

蓝桥云课第12届强者挑战赛

第一题&#xff1a;字符串加法 其实本质上就是一个高精度问题&#xff0c;可以使用同余定理的推论 &#xff08;ab&#xff09;%n((a%n)(b%n))%n; #include <iostream> using namespace std; const int mod1e97; int main() {string a,b;cin>>a>>b;ab;int …...

LabVIEW储油罐监控系统

LabVIEW储油罐监控系统 介绍了基于LabVIEW的储油罐监控系统的设计与实施。系统通过集成传感器技术和虚拟仪器技术&#xff0c;实现对储油罐内液位和温度的实时监控&#xff0c;提高了油罐监管的数字化和智能化水平&#xff0c;有效增强了油库安全管理的能力。 项目背景 随着…...

局域网、城域网、广域网的ip

一、 广域网ip&#xff1a; 全球共享同一个广域网&#xff0c;所以广域网也被称为公网&#xff0c;所以广域网的ip也称为公网ip&#xff0c;全球公网ip必须是都是唯一的&#xff0c;不能冲突。 二、城域网、局域网ip&#xff1a; 可以有无数个局域网、城域网&#xff0c;虽然在…...

【全开源】Java共享茶室棋牌室无人系统支持微信小程序+微信公众号

打造智能化休闲新体验 一、引言&#xff1a;智能化休闲时代的来临 随着科技的飞速发展&#xff0c;智能化、无人化服务逐渐渗透到我们生活的各个领域。在休闲娱乐行业&#xff0c;共享茶室棋牌室无人系统源码的出现&#xff0c;不仅革新了传统的休闲方式&#xff0c;更为消费…...

echarts数据更新没反应解决方案

数据处理逻辑问题&#xff1a; 确保data数组在传入函数时确实发生了变化&#xff0c;并且这些变化对于生成newData1和newData2是有效的。您可以增加一些日志输出来验证处理后的数据是否如预期那样被更新了。 ECharts实例未正确更新&#xff1a; 虽然使用了myChart.setOption…...

RK3588+FPGA+算能BM1684X:高性能AI边缘计算盒子,应用于视频分析、图像视觉等

搭载RK3588&#xff08;四核 A76四核 A55&#xff09;&#xff0c;CPU主频高达 2.4GHz &#xff0c;提供1MB L2 Cache 和 3MB L3 &#xff0c;Cache提供更强的 CPU运算能力&#xff0c;具备6T AI算力&#xff0c;可扩展至38T算力。 产品规格 系统主控CPURK3588&#xff0c;四核…...

Mysql学习(三)——SQL通用语法之DML

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 DML添加数据修改数据删除数据 总结 DML DML用来对数据库中表的数据记录进行增删改操作。 添加数据 -- 给指定字段添加数据 insert into 表名(字段1&#xff0c;字…...

java static 如何理解

在Java中&#xff0c;static关键字是一个重要的概念&#xff0c;它用于定义类的静态成员&#xff0c;包括静态变量&#xff08;也称作类变量&#xff09;、静态方法和静态代码块。static关键字的主要作用是创建独立于对象的成员&#xff0c;这些成员属于类本身&#xff0c;而不…...

【Python】 -- 趣味代码 - 小恐龙游戏

文章目录 文章目录 00 小恐龙游戏程序设计框架代码结构和功能游戏流程总结01 小恐龙游戏程序设计02 百度网盘地址00 小恐龙游戏程序设计框架 这段代码是一个基于 Pygame 的简易跑酷游戏的完整实现,玩家控制一个角色(龙)躲避障碍物(仙人掌和乌鸦)。以下是代码的详细介绍:…...

零门槛NAS搭建:WinNAS如何让普通电脑秒变私有云?

一、核心优势&#xff1a;专为Windows用户设计的极简NAS WinNAS由深圳耘想存储科技开发&#xff0c;是一款收费低廉但功能全面的Windows NAS工具&#xff0c;主打“无学习成本部署” 。与其他NAS软件相比&#xff0c;其优势在于&#xff1a; 无需硬件改造&#xff1a;将任意W…...

unix/linux,sudo,其发展历程详细时间线、由来、历史背景

sudo 的诞生和演化,本身就是一部 Unix/Linux 系统管理哲学变迁的微缩史。来,让我们拨开时间的迷雾,一同探寻 sudo 那波澜壮阔(也颇为实用主义)的发展历程。 历史背景:su的时代与困境 ( 20 世纪 70 年代 - 80 年代初) 在 sudo 出现之前,Unix 系统管理员和需要特权操作的…...

【C语言练习】080. 使用C语言实现简单的数据库操作

080. 使用C语言实现简单的数据库操作 080. 使用C语言实现简单的数据库操作使用原生APIODBC接口第三方库ORM框架文件模拟1. 安装SQLite2. 示例代码:使用SQLite创建数据库、表和插入数据3. 编译和运行4. 示例运行输出:5. 注意事项6. 总结080. 使用C语言实现简单的数据库操作 在…...

深度学习习题2

1.如果增加神经网络的宽度&#xff0c;精确度会增加到一个特定阈值后&#xff0c;便开始降低。造成这一现象的可能原因是什么&#xff1f; A、即使增加卷积核的数量&#xff0c;只有少部分的核会被用作预测 B、当卷积核数量增加时&#xff0c;神经网络的预测能力会降低 C、当卷…...

使用Matplotlib创建炫酷的3D散点图:数据可视化的新维度

文章目录 基础实现代码代码解析进阶技巧1. 自定义点的大小和颜色2. 添加图例和样式美化3. 真实数据应用示例实用技巧与注意事项完整示例(带样式)应用场景在数据科学和可视化领域,三维图形能为我们提供更丰富的数据洞察。本文将手把手教你如何使用Python的Matplotlib库创建引…...

打手机检测算法AI智能分析网关V4守护公共/工业/医疗等多场景安全应用

一、方案背景​ 在现代生产与生活场景中&#xff0c;如工厂高危作业区、医院手术室、公共场景等&#xff0c;人员违规打手机的行为潜藏着巨大风险。传统依靠人工巡查的监管方式&#xff0c;存在效率低、覆盖面不足、判断主观性强等问题&#xff0c;难以满足对人员打手机行为精…...

Vite中定义@软链接

在webpack中可以直接通过符号表示src路径&#xff0c;但是vite中默认不可以。 如何实现&#xff1a; vite中提供了resolve.alias&#xff1a;通过别名在指向一个具体的路径 在vite.config.js中 import { join } from pathexport default defineConfig({plugins: [vue()],//…...

【从零开始学习JVM | 第四篇】类加载器和双亲委派机制(高频面试题)

前言&#xff1a; 双亲委派机制对于面试这块来说非常重要&#xff0c;在实际开发中也是经常遇见需要打破双亲委派的需求&#xff0c;今天我们一起来探索一下什么是双亲委派机制&#xff0c;在此之前我们先介绍一下类的加载器。 目录 ​编辑 前言&#xff1a; 类加载器 1. …...

论文阅读笔记——Muffin: Testing Deep Learning Libraries via Neural Architecture Fuzzing

Muffin 论文 现有方法 CRADLE 和 LEMON&#xff0c;依赖模型推理阶段输出进行差分测试&#xff0c;但在训练阶段是不可行的&#xff0c;因为训练阶段直到最后才有固定输出&#xff0c;中间过程是不断变化的。API 库覆盖低&#xff0c;因为各个 API 都是在各种具体场景下使用。…...