MySQL慢SQL优化方案汇总

 a31da70afc294501ba5d6e07af20bd59.jpeg

⛰️个人主页:     蒾酒

🔥系列专栏《mysql经验总结》

🌊山高路远,行路漫漫,终有归途


目录

写在前面

优化思路

避免查询不必要的列

分页优化

索引优化

JOIN优化

排序优化

UNION 优化

写在最后


写在前面

本文介绍了MySQL常见的优化慢sql的手段,坚持看完相信对你有帮助。

同时欢迎订阅MySQL系列专栏,持续分享MySQL的使用经验。

优化思路

慢sql的优化无非是从两个方向着手

  • SQL语句本身的优化
  • 据库设计的优化

下面进行渐进式的分享一些常见优化手段

避免查询不必要的列

查询应该精准的查出需要的列,对于select * 的写法要避免,因为所有字段查出来不仅sql查询执行慢,若是直接返回给前端,大量的数据也会影响网络传输效率。

分页优化

对于数据量特别大,这时分页会比较深,查询扫描的数据量会比较大效率自然低,我们就需要进行分页优化

假设我们有一个包含大量订单记录的订单表,其中每个订单都有一个唯一的不包含业务逻辑的主键,并且我们想要查询最近一个月的订单并按照订单id从小到大进行分页显示某页。

假设出现深分页的sql如下:

select * from orders where order_date >= date_sub(now(), interval 1 month)
 order by id limit 100000, 10;

执行此SQL时需要先扫描到100000行,然后再去取10行,但是随着扫描的记录数越多,SQL的性能就会越差,因为扫描的记录越多,MySQL需要扫描越多的数据来定位到具体的多少行,这样耗费大量的 IO 成本和时间成本。

对于解决该深分页问题通常有两种方法

1.延迟关联

先通过 where 条件提取出主键,在将该表与原数据表关联,通过主键 id 提取数据行,而不是通过原来的二级索引提取数据行

优化后sql:

select o.*
from (
    select id
    from orders
    where order_date >= date_sub(now(), interval 1 month)
    order by id
    limit 100000, 10
) as sub
join orders as o on sub.id = o.id;

优化后SQL中的子查询只取主键id,可避免通过二级索引中的主键去回表查询,这样性能会快一些。

2.id偏移量

偏移量就是找到 limit 第一个参数对应的主键值,根据这个主键值再去过滤并 limit,这种方法又称为基于游标的分页。基于游标的分页的前提是需要保证主键或排序列的连续性、唯一性。

优化后sql:

select *
from orders
where id >= (select id from orders order by id limit 100000, 1)
order by id
limit 10;

这种方法相对于原来直接使用偏移量和限制结果数量的方式,可以在大数据集上提供更稳定和一致的性能,因为它不需要扫描和跳过大量的行。

索引优化

通过合理的设计和使用索引,能够有效优化sql性能,这也是我们使用最多的手段。

下面介绍一下如何进行索引优化:

使用覆盖索引

InnoDB使用二级索引查询数据时会回表,但是如果索引的叶节点中已经包含要查询的字段,那它没有必要再回表查询了,这就叫覆盖索引,还有一个简单的理解查询列都是索引列。

示例:

select product_name, price
from products
where category_id = 1;
create index idx_category_id on products (category_id, product_name, price);

避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询,可以使用 union 或者子查询来替代,因为早期的MySQL版本使用 or 查询可能会导致索引失效,高版本引入了索引合并,解决了这个问题,不过建议大家在实际使用中还是规范写法,能不用就少用。

避免使用 != 或者 <>操作符

SQL中,不等于操作符会导致查询引擎放弃查询索引,引起全表扫描,即使比较的字段上有索引。解决方法:通过把不等于操作符改成 or,可以使用索引,避免全表扫描

id <>'aaa'
id >'aaa'or id<'aaa

适当使用前缀索引

适当地使用前缀索引,可以降低索引的空间占用,提高索引的查询效率。比如,邮箱的后缀都是固定的“@xxx.com”,那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

create index idx_email_prefix on users (email(6)); -- 假设后缀长度为6

需要注意的是,前缀索引也存在缺点,MySQL无法利用前缀索引做 order by和 group by 操作,也无法作为覆盖索引。

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算,否则可能会导致存储引擎无法正确使用索引,从而影响了查询的效率。

select order_id
from orders
where total_amount / 2 > 100

正确使用联合索引

使用联合索引的时候,注意最左匹配原则。 

JOIN优化

优化子查询

尽量使用 Join 语句来替代子査询,因为子査询是嵌套查询,而嵌套查询会新创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,同时对于返回结果集比较大的子查询,其对查询性能的影响更大 

小表驱动大表

关联查询的时候要拿小表去驱动大表,因为关联的时候,MySQL内部会遍历驱动表,再去连接被驱动表

select name from小表 left join 大表;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询,因为多张表的连表查询性能很低,所有可以适当的增加冗余字段,以减少多张表的关联查询,这是以空间换时间的优化策略。

避免使用 JOIN 关联太多的表

《阿里巴巴 Java 开发手册》规定不要 join 超过三张表,第一join 太多降低査询的速度,第二 join 的buffer 会占用更多的内存。

排序优化

利用索引扫描做排序

MYSQL有两种方式生成有序结果:一是对结果集进行排序的操作,二是按照索引顺序扫描得出的结果,索引是排好序的数据结构,自然是有序的。
但是如果索引不能覆盖查询所需列(覆盖索引),就会每扫描一条记录回表查询一次(逐个获取),这个读操作是随机 IO,通常会比顺序全表扫描还慢,有时会直接放弃使用索引转为全表扫描。因此,在设计索引时,尽可能使用同一个索引既满足排序又用于查找行。

#索引为 a,b,c
select b,c from test where a like 'aa%' order by b,c;

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果做排序。

UNION 优化

条件下推

MySQL处理 union 的策略是先创建临时表,然后将各个查询结果填充到临时表中最后再来做查询,很多优化策略在 union 查询中都会失效,因为它无法利用索引。所以需要将 where、limit 等子句下推到 union 的各个子查询中,以便优化器可以充分利用这些条件进行优化。

此外,除非确实需要服务器去重,一定要使用 union all,如果不加 all 关键字,MySQL 会给临时表加上distinct 选项,这会导致对整个临时表做唯一性检查,代价很高。

写在最后

MySQL优化慢SQL的6种方式到这里就结束了,本文介绍了常见慢sql优化的有效方式。任何问题评论区或私信讨论,欢迎指正。

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

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Netty学习——实战篇3 BIO与NIO零拷贝 和 Netty入门实战

1 BIO拷贝 BIOServer.java public class BIOServer {public static void main(String[] args) throws Exception{ServerSocket serverSocket new ServerSocket(8000);while(true){Socket socket serverSocket.accept();DataInputStream inputStream new DataInputStream(so…

使用美化方法设计嵌入的子窗体(三)

使用美化方法设计嵌入的子窗体 分析效果图的实现 效果图&#xff1a; 新建 Windows 窗体 新窗体命名&#xff1a;FrmAddProduct.cs修改窗体的 Text 属性&#xff1a;新增商品修改窗体的位置&#xff1a;StartPosition&#xff1a;CenterScreen窗体的无边框设计&#xff1a…

拼多多容器文件修改自动上传

拼多多开放平台php环境是官方的linux容器&#xff0c;不能自己搭建ftp上传文件&#xff0c;每每有文件更新都挺麻烦。 有些功能测试不想每次都打包全部代码上去重新发布一次程序生成新的容器&#xff0c;那样太过麻烦和效率低。 一开始搞了一个php的文件管理工具上去&#xf…

电压比较器LM339介绍和仿真

电压比较器LM339介绍和仿真 &#x1f4d1;LM339相关特性 工作电源电压范围宽&#xff0c;单电源、双电源均可工作&#xff0c;单电源&#xff1a; 2&#xff5e;36V&#xff0c;双电源&#xff1a;1&#xff5e;18V&#xff1b;消耗电流小&#xff0c; Icc1.3mA&#xff1b;输…

《Kubernets证书篇:基于Kylin V10+ARM架构CPU修改K8S 1.26.15版本证书时间限制》

一、背景 Kubernetes 默认的证书有效期只有1年&#xff0c;因此需要每年手动更新一次节点上面的证书&#xff0c;特别麻烦而且更新过程中可能会出现问题&#xff0c;因此我们要对 Kubernetes 的 SSL 证书有效期进行修改&#xff0c;这里将证书的时间限制修改为100年。 环境信息…

【日常记录】【JS】styled-components库的原理,模板字符串调用函数

文章目录 1、引言2、模板字符串调用函数3、实现 1、引言 在react 中&#xff0c;styled-components 是最流行的 css in js 模式的库 2、模板字符串调用函数 let stu {name: 呆呆狗,age: 30,address: 中国}let str fn你好${stu.name}今年${stu.age}岁,来自${stu.address}这样会…

MySql 安装,小白也可以学会成功安装的保姆级教程

MySql 安装 文章目录 MySql 安装1.Mysql下载1.1 访问下载链接1.2 选择合适版本1.3 下载安装包 2.MySql安装3.安装成功检测验证3.1 mysql自带控制台验证3.2 win系统控制台进入验证 4. mysql 配置path5. navicat 连接 mysql 1.Mysql下载 1.1 访问下载链接 MySQL Downloads 这里…

计算机网络----第十六天

OSPF基础 RIP的缺陷&#xff1a; 最大16跳不可达&#xff1b; 收敛速度慢&#xff1b; 协议会产生路由自环 每发一次路由更新&#xff0c;就将自己的全部路由信息发送出去&#xff1b; OSPF&#xff1a; 含义&#xff1a;ospf&#xff08;最短路径优先&#xff09;&…

【Github】一个用于Active Directory的自助密码更改工具

在众多企业的日常运营中&#xff0c;Active Directory&#xff08;AD&#xff09;扮演着核心角色&#xff0c;负责管理和维护员工账户。然而&#xff0c;密码重置作为IT支持团队的常规工作之一&#xff0c;往往既耗时又繁琐。虽然一些商业解决方案和通过Windows服务器上RDS服务…

航芯通用MCU技术常见问题 | F4专题

日常工作中&#xff0c;我们的销售或技术工程师经常会收到来自用户的问题&#xff0c;其中一些问题是比较常见的&#xff0c;所以为满足日常用户对航芯产品使用及服务的了解&#xff0c;航芯特此推出“通用MCU技术常见问题”专题&#xff0c;分为F0专题及F4专题&#xff0c;欢迎…

内网穿透是什么意思?快解析如何实现内网穿透

在家里或者公司&#xff0c;我们常常会使用路由器来连接网络&#xff0c;以便我们能够上网学习和工作&#xff0c;但有时候使用起来真的不方便。有的时候我们在外面&#xff0c;想访问家里或者公司内部的设备&#xff0c;就会碰到一个问题&#xff1a;我们无法直接通过公网IP访…

【LeetCode: 3117. 划分数组得到最小的值之和 + 动态规划】

&#x1f680; 算法题 &#x1f680; &#x1f332; 算法刷题专栏 | 面试必备算法 | 面试高频算法 &#x1f340; &#x1f332; 越难的东西,越要努力坚持&#xff0c;因为它具有很高的价值&#xff0c;算法就是这样✨ &#x1f332; 作者简介&#xff1a;硕风和炜&#xff0c;…

【C语言】<结构体>C中的自定义类型之struct

&#xff1c;结构体&#xff1e; 1. 结构体类型的声明1.1 结构体回顾1.1.1 结构体的声明1.1.2 结构体变量的创建和初始化 1.2 结构体的特殊声明1.3 结构体的自引用 2. 结构体内存对齐2.1 对齐规则2.2 为什么存在内存对齐&#xff1f;2.3 修改默认对齐数 3. 结构体传参4. 结构体…

SD-WAN提升企业网络体验

在现代企业中&#xff0c;网络体验已成为提升工作效率与业务质量的关键因素。SD-WAN技术的出现&#xff0c;以其独特的优势&#xff0c;为企业提供了优化网络连接、加速数据传输、提升服务质量和应用访问体验&#xff0c;以及增强网络稳定性的解决方案。接下来&#xff0c;我们…

Vue3(四):Pinia

一、Pinia介绍 Pinia是一个专门为Vue.js设计的状态管理库&#xff0c;它提供了一种简单和直观的方式来管理应用程序的状态。在使用Pinia时&#xff0c;可以轻松地创建定义状态的存储&#xff0c;然后将其与Vue组件绑定&#xff0c;使它们能够使用该状态。和上一个博客提到的Vu…

外网如何访问内网数据库?

在当今信息时代&#xff0c;随着互联网的快速发展&#xff0c;很多企业和个人都面临着外网访问内网数据库的需求。外网访问内网数据库可以实现远程操作&#xff0c;方便用户在任何地点使用移动设备进行数据管理和查询。本文将介绍一种名为【天联】的组网产品&#xff0c;它是一…

Sublime Text下载,安装,安装插件管理器,下载汉化插件

SublimeTest官网 © Sublime Text中文网 下载安装 一路点击安装即可 安装插件管理器 管理器官网安装 - 包控制 (packagecontrol.io) 手动安装将3 位置点击网址下载 再打开SublimeTest 点击 选择第一个Browse Packages..... 将会跳转到文件夹中 进入上一个文件夹 在进入…

使用剧本批量部署rsync服务端实战

目录 1、实战部署 编写剧本 执行剧本测试&#xff01;&#xff01;&#xff01; 2、部署方式对比 1、实战部署 编写剧本 执行剧本测试&#xff01;&#xff01;&#xff01; 2、部署方式对比 ansible模块实战-部署rsync服务端-CSDN博客 ansible临时命令和playbook区别 …

UE5 C++ TimeLine 时间轴练习

一. Actor引入头文件 #include "Components/TimelineComponent.h" 声明CurveFloat 和 TimelineComponent UPROPERTY(EditAnywhere,BlueprintReadWrite,Category "MyCurve")UCurveFloat* MyCurveFloat;UPROPERTY(EditAnywhere, BlueprintReadWrite, Cate…

北漂程序员整理:2024年阿里云服务器租用优惠价格表

阿里云服务器租用价格表2024年最新&#xff0c;云服务器ECS经济型e实例2核2G、3M固定带宽99元一年&#xff0c;轻量应用服务器2核2G3M带宽轻量服务器一年61元&#xff0c;ECS u1服务器2核4G5M固定带宽199元一年&#xff0c;2核4G4M带宽轻量服务器一年165元12个月&#xff0c;2核…