MySQL慢SQL优化方案汇总

news/2024/5/24 12:13:49/ 标签: mysql, sql, 数据库

 a31da70afc294501ba5d6e07af20bd59.jpeg

⛰️个人主页:     蒾酒

🔥系列专栏sql>mysql经验总结》

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


目录

写在前面

优化思路

避免查询不必要的列

分页优化

索引优化

JOIN优化

排序优化

UNION 优化

写在最后


写在前面

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

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

优化思路

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

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

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

避免查询不必要的列

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

分页优化

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

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

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

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:

sql">select o.*
from (select idfrom orderswhere order_date >= date_sub(now(), interval 1 month)order by idlimit 100000, 10
) as sub
join orders as o on sub.id = o.id;

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

2.id偏移量

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

优化后sql:

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

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

索引优化

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

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

使用覆盖索引

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

示例:

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

避免使用or查询

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

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

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

sql">id <>'aaa'
sql">id >'aaa'or id<'aaa

适当使用前缀索引

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

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

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

避免列上函数运算

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

sql">select order_id
from orders
where total_amount / 2 > 100

正确使用联合索引

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

JOIN优化

优化子查询

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

小表驱动大表

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

sql">select name from小表 left join 大表;

适当增加冗余字段

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

避免使用 JOIN 关联太多的表

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

排序优化

利用索引扫描做排序

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

sql">#索引为 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优化的有效方式。任何问题评论区或私信讨论,欢迎指正。


http://www.ppmy.cn/news/1424599.html

相关文章

【LeetCode热题100】【贪心算法】跳跃游戏

题目链接&#xff1a;55. 跳跃游戏 - 力扣&#xff08;LeetCode&#xff09; 数组的元素表示可以跳的最大长度&#xff0c;要判断能不能跳到最后 不断更新可以跳到的最远距离&#xff0c;如果当前的位置大于可跳最远距离&#xff0c;说明不行 class Solution { public:bool …

[python3] 字符串匹配的`多模式匹配`算法

在Python 3中&#xff0c;你可以使用第三方库ahocorasick来实现Aho-Corasick算法。Aho-Corasick算法是一种用于字符串匹配的多模式匹配算法&#xff0c;可以高效地在一个文本中搜索多个关键词。 首先&#xff0c;你需要安装ahocorasick库。你可以使用pip来进行安装&#xff1a…

「GO基础」在Windows上配置VS Code GO语言开发环境

&#x1f49d;&#x1f49d;&#x1f49d;欢迎莅临我的博客&#xff0c;很高兴能够在这里和您见面&#xff01;希望您在这里可以感受到一份轻松愉快的氛围&#xff0c;不仅可以获得有趣的内容和知识&#xff0c;也可以畅所欲言、分享您的想法和见解。 推荐:「stormsha的主页」…

# [USACO3.2] 魔板 Magic Squares

[USACO3.2] 魔板 Magic Squares 题目背景 在成功地发明了魔方之后&#xff0c;鲁比克先生发明了它的二维版本&#xff0c;称作魔板。这是一张有 8 8 8 个大小相同的格子的魔板&#xff1a; 1 2 3 4 1\quad2\quad3\quad4 1234 8 7 6 5 8\quad7\quad6\quad5 8765 题目描述 我…

Qt日志使用

QsLog使用 这篇讲qt的日志还是比较好的&#xff0c;可以在自己的函数里面配置这个日志框架实现自己所需的功能。 我接触的项目里面&#xff0c;假如有个函数功能执行错误了&#xff0c;我希望可以快速定位到这个错误&#xff0c;这个时候就需要到了日志&#xff0c;我咨询了有经…

华为OD-C卷-靠谱的车[100分]C语言-100%

题目描述 程序员小明打了一辆出租车去上班。出于职业敏感,他注意到这辆出租车的计费表有点问题,总是偏大。 出租车司机解释说他不喜欢数字4,所以改装了计费表,任何数字位置遇到数字4就直接跳过,其余功能都正常。 比如: 23再多一块钱就变为25;39再多一块钱变为50;399…

校园综合服务平台V3.9.2 源码修复大部分已知BUG

校园综合服务平台&#xff0c;版本更新至V3.9.1 &#xff0c;源码功能强大&#xff0c;ui 精美&#xff0c; 功能包含但不限于校园跑腿&#xff0c;外卖&#xff0c;组局&#xff0c;圈子&#xff0c;商城&#xff0c;抽奖&#xff0c;投票&#xff0c;团购&#xff0c;二手市场…

自然语言处理NLP:文本预处理Text Pre-Processing

大家好&#xff0c;自然语言处理(NLP)是计算机科学领域与人工智能领域中的一个重要方向&#xff0c;其研究能实现人与计算机之间用自然语言进行有效通信的各种理论和方法。本文将介绍文本预处理的本质、原理、应用等内容&#xff0c;助力自然语言处理和模型的生成使用。 1.文本…

ES6的模块化

ES6模块化是JavaScript的一种组织代码的方式&#xff0c;它允许开发者将代码分割成多个独立的部分&#xff08;模块&#xff09;&#xff0c;每个模块有自己的作用域和接口&#xff0c;模块之间可以通过导入&#xff08;import&#xff09;和导出&#xff08;export&#xff09…

AI讲师人工智能讲师大模型培训讲师叶梓:突破大型语言模型推理效率的创新方法

大型语言模型&#xff08;LLM&#xff09;在自然语言处理&#xff08;NLP&#xff09;任务中展现出了前所未有的能力&#xff0c;但它们对计算资源的巨大需求限制了其在资源受限环境中的应用。SparQ Attention算法提出了一种创新的方法&#xff0c;通过减少注意力机制中的内存带…

C++初阶学习第一弹——C++入门(上)

前言&#xff1a; 很高兴&#xff0c;从今天开始&#xff0c;我们就要步入C的学习了&#xff0c;在这之前我们已经对C语言有了不错的了解&#xff0c;对数据结构也有了一些自己的认识&#xff0c;今天开始&#xff0c;我们就进入这个新的主题的学习——C 目录 一、C的发展即其特…

AI大模型之路 第二篇: Word2Vec介绍

你好&#xff0c;我是郭震 今天我来总结大模型第二篇&#xff0c;word2vec&#xff0c;它是大模型的根基&#xff0c;一切NLP都会用到它。 Word2Vec Word2Vec 是一种流行的自然语言处理&#xff08;NLP&#xff09;工具&#xff0c;它通过将词汇表中的每个单词转换成一个独特的…

探索人工智能绘图的奇妙世界

探索人工智能绘图的奇妙世界 人工智能绘图的基本原理机器之美&#xff1a;AI绘图作品AI绘图对艺术创作的影响未来展望与挑战图书推荐&#x1f449;AI绘画教程&#xff1a;Midjourney使用方法与技巧从入门到精通内容简介获取方式&#x1f449;搜索之道&#xff1a;信息素养与终身…

电脑桌面便签软件哪个好?好用的电脑桌面便签

电脑作为我们日常工作的重要工具&#xff0c;承载着大量的任务和项目。当工作任务繁重时&#xff0c;如何在电脑桌面上高效管理这些任务就显得尤为重要。这时&#xff0c;选择一款优秀的桌面便签软件&#xff0c;无疑会给我们带来极大的便利。 一款好的桌面便签软件&#xff0…

Bilstm双向长短期神经网络多输入单输出回归分析

目录 背影 摘要 LSTM的基本定义 LSTM实现的步骤 BILSTM神经网络 Bilstm双向长短期神经网络多输入单输出回归分析 完整代码: Bilstm双向长短期神经网络多输入单输出回归分析.zip资源-CSDN文库 https://download.csdn.net/download/abc991835105/89087121 效果图 结果分析 展望 …

NSA发布《在数据支柱中推进零信任成熟度》报告

4月9日&#xff0c;美国国家安全局&#xff08;NSA&#xff09;发布了题为《在数据支柱中推进零信任成熟度》的报告&#xff0c;旨在于数据安全层面提供指导&#xff0c;以增强数据整体安全性并保护静态和传输中的数据。(如下图&#xff09; 一、主要内容 报告中的建议侧重于将…

flutter知识点---三棵树

在Flutter开发领域中&#xff0c;提到“三棵树”这个概念&#xff0c;通常是指构成Flutter UI构建体系的三个核心组件树&#xff1a;Widget树、Element树和RenderObject树。这三棵树相互关联、协同工作&#xff0c;共同构成了Flutter灵活、高效、高性能的UI渲染机制。下面分别对…

C#值传递和引用传递,ref和out关键字,装箱和拆箱

C#值传递和引用传递 1.值传递和引用传递 值传递&#xff1a;值传递时&#xff0c;系统首先为被调用方法的形参分配内存空间&#xff0c;并将实参的值按位置一一对应复制给形参&#xff0c;被调用方法中形参得任何改变都不会影响到相应的实参。 引用传递时&#xff1a;系统不是…

机器视觉各类光源特点

1. 环形光源 提供不同照射角度和颜色组合突出物体的三维信息高密度LED阵列&#xff0c;高亮度多种紧凑设计&#xff0c;节省安装空间解决对角照射阴影问题可选配漫射板导光&#xff0c;光线均匀扩散 2. 背光源系列 高密度LED阵列面提供高强度背光照明突出物体的外形轮廓特征…

# 从浅入深 学习 SpringCloud 微服务架构(三)注册中心 Eureka(1)

从浅入深 学习 SpringCloud 微服务架构&#xff08;三&#xff09;注册中心 Eureka&#xff08;1&#xff09; 段子手168 1、微服务的注册中心 注册中心可以说是微服务架构中的”通讯录”&#xff0c;它记录了服务和服务地址的映射关系。 在分布式架构中服务会注册到这里&am…