您当前的位置:首页 > 电脑百科 > 数据库 > 百科

神奇的 SQL 之性能优化 → 让 SQL 飞起来

时间:2021-01-07 09:56:18  来源:  作者:

写在前面

在像 Web 服务这样需要快速响应的应用场景中,SQL 的性能直接决定了系统是否可以使用;特别在一些中小型应用中,SQL 性能更是决定服务能否快速响应的唯一标准

严格地优化查询性能时,必须要了解所使用数据库的功能特点,此外,查询速度慢并不只是因为 SQL 语句本身,还可能是因为内存分配不佳、文件结构不合理、刷脏页等其他原因

因此本文即将介绍的优化 SQL 的方法不能解决所有的性能问题,但是却能处理很多因 SQL 写法不合理而产生的性能问题

下文将尽量介绍一些不依赖具体数据库实现,使 SQL 执行速度更快、消耗内存更少的优化技巧,只需调整 SQL 语句就能实现的通用的优化Tip

环境准备

下文所讲的内容是从 SQL 层面展开的,而不是针对某种特性的数据库,也就是说,下文的内容基本上适用于任何关系型数据库

但是,关系型数据库那么多,逐一来演示示例了,显然不太现实;我们以常用的 MySQL 来进行示例的演示

MySQL 版本: 5.7.30-log ,存储引擎: InnoDB

准备两张表: tbl_customer 和 tbl_recharge_record

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

使用高效的查询

针对某一个查询,有时候会有多种 SQL 实现,例如 IN、EXISTS、连接之间的互相转换

从理论上来讲,得到相同结果的不同 SQL 语句应该有相同的性能,但遗憾的是,查询优化器生成的执行计划很大程度上要受到外部结构的影响

因此,如果想优化查询性能,必须知道如何写 SQL 语句才能使优化器生成更高效的执行计划

使用 EXISTS 代替 IN

关于 IN,相信大家都比较熟悉,使用方便,也容易理解;虽说 IN 使用方便,但它却存在性能瓶颈

如果 IN 的参数是 1,2,3 这样的数值列表,一般还不需要特别注意,但如果参数是子查询,那么就需要注意了

在大多时候, [NOT] IN 和 [NOT] EXISTS 返回的结果是相同的,但是两者用于子查询时,EXISTS 的速度会更快一些

假设我们要查询有充值记录的顾客信息,SQL 该怎么写?

相信大家第一时间想到的是 IN: SELECT * FROM tbl_customer WHERE ID IN (SELECT customer_id FROM tbl_recharge_record);

IN 使用起来确实简单,也非常好理解;我们来看下它的执行计划

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

我们再来看看 EXISTS 的执行计划:

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

可以看到,IN 的执行计划中新产生了一张临时表: <subquery2> ,这会导致效率变慢

通常来讲,EXISTS 比 IN 更快的原因有两个

1、如果连接列(customer_id)上建立了索引,那么查询 tbl_recharge_record 时可以通过索引查询,而不是全表查询

2、使用 EXISTS,一旦查到一行数据满足条件就会终止查询,不用像使用 IN 时一样进行扫描全表(NOT EXISTS 也一样)

当 IN 的参数是子查询时,数据库首先会执行子查询,然后将结果存储在一张临时表里(内联视图),然后扫描整个视图,很多情况下这种做法非常耗费资源

使用 EXISTS 的话,数据库不会生成临时表

但是从代码的可读性上来看,IN 要比 EXISTS 好,使用 IN 时的代码看起来更加一目了然,易于理解

因此,如果确信使用 IN 也能快速获取结果,就没有必要非得改成 EXISTS 了

其实有很多数据库也尝试着改善了 IN 的性能

Oracle 数据库中,如果我们在有索引的列上使用 IN, 也会先扫描索引

PostgreSQL 从版 本 7.4 起也改善了使用子查询作为 IN 谓词参数时的查询速度

说不定在未来的某一天,无论在哪个关系型数据库上,IN 都能具备与 EXISTS 一样的性能

关于 EXISTS,更多详情可查看:神奇的 SQL 之谓词 → 难理解的 EXISTS

使用连接代替 IN

其实在平时工作当中,更多的是用连接代替 IN 来改善查询性能,而非 EXISTS,不是说连接更好,而是 EXISTS 很难掌握

回到问题:查询有充值记录的顾客信息,如果用连接来实现,SQL 改如何写?

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

这种写法能充分利用索引;而且,因为没有了子查询,所以数据库也不会生成中间表;所以,查询效率是不错的

至于 JOIN 与 EXISTS 相比哪个性能更好,不太好说;如果没有索引,可能 EXISTS 会略胜一筹,有索引的话,两者差不多

避免排序

说到 SQL 的排序,我们第一时间想到的肯定是: ORDER BY ,通过它,我们可以按指定的某些列来顺序输出结果

但是,除了 ORDER BY 显示的排序,数据库内部还有很多运算在暗中进行排序;会进行排序的代表性的运算有下面这些

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

如果只在内存中进行排序,那么还好;但是如果因内存不足而需要在硬盘上排序,那么性能就会急剧下降

因此,尽量避免(或减少)无谓的排序,能够大大提高查询效率

灵活使用集合运算符的 ALL 可选项

SQL 中有 UNION 、 INTERSECT 、 EXCEPT 三个集合运算符,分表代表这集合运算的 并集、交集、差集

默认情况下,这些运算符会为了排除掉重复数据而进行排序

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

Using temporary 表示进行了排序或分组,显然这个 SQL 没有进行分组,而是进行了排序运算

如果我们不在乎结果中是否有重复数据,或者事先知道不会有重复数据,可以使用 UNION ALL 代替 UNION

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

可以看到,执行计划中没有排序运算了

对于 INTERSECT 和 EXCEPT 也是一样的,加上 ALL 可选项后就不会进行排序了

加上 ALL 可选项是一个非常有效的优化手段,但各个数据库对它的实现情况却是参差不齐,如下图所示

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

注意:Oracle 使用 MINUS 代替 EXCEPT ;MySQL 压根就没有实现 INTERSECT 和 EXCEPT 运算

使用 EXISTS 代替 DISTINCT

为了排除重复数据, DISTINCT 也会进行排序

还记得用连接代替 IN 的案例吗,如果不用 DISTINCT

SQL: SELECT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id = tc.id

那么查出来的结果会有很多重复记录,我们改进 SQL

SELECT DISTINCT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id = tc.id

会发现执行计划中有个 Using temporary ,表示用到了排序运算

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

我们使用 EXISTS 来进行优化

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

可以看到,已经规避了排序运算

在极值函数中使用索引

SQL 语言里有两个极值函数: MAX 和 MIN ,使用这两个函数时都会进行排序

例如: SELECT MAX(recharge_amount) FROM tbl_recharge_record

会进行全表扫描,并会进行隐式的排序,找出单笔充值最大的金额

但是如果参数字段上建有索引,则只需要扫描索引,不需要扫描整张表

例如: SELECT MAX(customer_id) FROM tbl_recharge_record;

会通过索引: idx_c_id 进行扫描,找出充值记录中最大的顾客ID

这种方法并不是去掉了排序这一过程,而是优化了排序前的查找速度,从而减弱排序对整体性能的影响

能写在 WHERE 子句里的条件不要写在 HAVING 子句里

我们来看两个 SQL 以及其执行结果

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

从结果上来看,两条 SQL 一样;但是从性能上来看,第二条语句写法效率更高,原因有两个

减少排序的数据量

GROUP BY 子句聚合时会进行排序,如果事先通过 WHERE 子句筛选出一部分行,就能够减轻排序的负担

有效利用索引

WHERE 子句的条件里可以使用索引

HAVING 子句是针对聚合后生成的视图进行筛选的,但是很多时候聚合后的视图都没有继承原表的索引结构

关于 HAVING,更多详情可查看:神奇的 SQL 之 HAVING → 容易被轻视的主角

在 GROUP BY 子句和 ORDER BY 子句中使用索引

一般来说,GROUP BY 子句和 ORDER BY 子句都会进行排序

如果 GROUP BY 和 ORDER BY 的列有索引,那么可以提高查询效率

特别是在一些数据库中,如果列上建立的是唯一索引,那么排序过程本身都会被省略掉

使用索引

使用索引是最常用的 SQL 优化手段,这个大家都知道,怕就怕大家不知道:明明有索引,为什么查询还是这么慢(为什么索引没用上)

关于索引未用到的情况,可查看:神奇的 SQL 之擦肩而过 → 真的用到索引了吗,本文就不做过多阐述了

总之就是:查询尽量往索引上靠,规避索引未用上的情况

减少临时表

在 SQL 中,子查询的结果会被看成一张新表(临时表),这张新表与原始表一样,可以通过 SQL 进行操作

但是,频繁使用临时表会带来两个问题

1、临时表相当于原表数据的一份备份,会耗费内存资源

2、很多时候(特别是聚合时),临时表没有继承原表的索引结构

因此,尽量减少临时表的使用也是提升性能的一个重要方法

灵活使用 HAVING 子句

对聚合结果指定筛选条件时,使用 HAVING 子句是基本原则

但是如果对 HAVING 不熟,我们往往找出替代它的方式来实现,就像这样

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

然而,对聚合结果指定筛选条件时不需要专门生成中间表,像下面这样使用 HAVING 子句就可以

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

HAVING 子句和聚合操作是同时执行的,所以比起生成临时表后再执行 WHERE 子句,效率会更高一些,而且代码看起来也更简洁

需要对多个字段使用 IN 谓词时,将它们汇总到一处

SQL-92 中加入了行与行比较的功能,这样一来,比较谓词 = 、< 、> 和 IN 谓词的参数就不再只是标量值了,而应是值列表了

我们来看一个示例,多个字段使用 IN 谓词

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

这段代码中用到了两个子查询,我们可以进行列汇总优化,把逻辑写在一起

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

这样一来,子查询不用考虑关联性,而且只执行一次就可以

还可以进一步简化,在 IN 中写多个字段的组合

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

简化后,不用担心连接字段时出现的类型转换问题,也不会对字段进行加工,因此可以使用索引

先进行连接再进行聚合

连接和聚合同时使用时,先进行连接操作可以避免产生中间表

合理地使用视图

视图是非常方便的工具,我们在日常工作中经常使用

但是,如果没有经过深入思考就定义复杂的视图,可能会带来巨大的性能问题

特别是视图的定义语句中包含以下运算的时候,SQL 会非常低效,执行速度也会变得非常慢

神奇的 SQL 之性能优化 → 让 SQL 飞起来

 

总结

文中虽然列举了几个要点,但其实优化的核心思想只有一个,那就是找出性能瓶颈所在,然后解决它

其实不只是数据库和 SQL,计算机世界里容易成为性能瓶颈的也是对硬盘,也就是文件系统的访问(因此可以通过增加内存,或者使用访问速度更快的硬盘等方法来提升性能)

不管是减少排序还是使用索引,亦或是避免临时表的使用,其本质都是为了减少对硬盘的访问

小结下文中的 Tips

1、参数是子查询时,使用 EXISTS 或者 JOIN 代替 IN

2、在 SQL 中,很多运算都会暗中进行排序,尽量规避这些运算

3、SQL 的书写,尽量往索引上靠,避免用不上索引的情况

4、尽量减少使用中间表



Tags:性能优化   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
对于一个新建连接,内核要发送多少个 SYN 连接请求才决定放弃。不应该大于255,默认值是5,对应于180秒左右时间。。(对于大负载而物理通信良好的网络而言,这个值偏高,可修改为2.这个值仅仅是针对对外的连接,对进来的连接,...【详细内容】
2021-12-08  Tags: 性能优化  点击:(23)  评论:(0)  加入收藏
App的性能优化,可以从多角度优化,包括App启动优化,App启动优化又分为main()函数执行前优化,main()函数执行后优化;包括界面卡顿优化,页面卡顿优化又包括CPU优化和GPU优化;包括安装包瘦...【详细内容】
2021-12-08  Tags: 性能优化  点击:(14)  评论:(0)  加入收藏
一、前言最近参加了几轮面试,发现很多5-7年工作经验的候选人在性能优化这一块,基本上只能说出传统的分析方式,例如ANR分析,是通过查看/data/anr/ 下的log,分析主线程堆栈、cpu、...【详细内容】
2021-06-17  Tags: 性能优化  点击:(149)  评论:(0)  加入收藏
MNN(Mobile Neural Network)是一个高性能、通用的深度学习框架,支持在移动端、PC端、服务端、嵌入式等各种设备上高效运行。MNN利用设备的GPU能力,全面充分“榨干”设备的GPU资源,来进行深度学习的高性能部署与训练。...【详细内容】
2021-04-20  Tags: 性能优化  点击:(228)  评论:(0)  加入收藏
一 题记最近公司项目添加新功能,上线后发现有些功能的列表查询时间很久。原因是新功能用到旧功能的接口,而这些旧接口的 SQL 查询语句关联5,6张表且编写不够规范,导致 MySQL 在...【详细内容】
2021-03-12  Tags: 性能优化  点击:(143)  评论:(0)  加入收藏
【摘要】日常生活中,我们会遇到各种各样的数据,小到公司通讯录,大到互联网用户行为分析。在进行数据分析处理的过程中,查询是必不可少的环节,如何更加高效地进行数据查询。点击:性...【详细内容】
2021-03-03  Tags: 性能优化  点击:(98)  评论:(0)  加入收藏
今天给大家分享的是MySQL性能优化,也是大数据开发指南MySQL的最后一部分。性能优化对于老刘来说,是必须掌握的一个手段,如何让自己变得更加优秀,这块内容还是好好看看!本篇内容相...【详细内容】
2021-02-07  Tags: 性能优化  点击:(162)  评论:(0)  加入收藏
说明:文章有点长,CPU性能主要观测点的理论知识搬砖堆砌得较多,主要是为了大家对CPU性能主要观测点有深入理解,这样才能在性能调优和排错的过程中把握方向,希望你能耐心读完。当...【详细内容】
2021-02-02  Tags: 性能优化  点击:(228)  评论:(0)  加入收藏
本文选自“字节跳动基础架构实践”系列文章。 “字节跳动基础架构实践”系列文章是由字节跳动基础架构部门各技术团队及专家倾力打造的技术干货内容,和大家分享团队在基础架...【详细内容】
2021-01-18  Tags: 性能优化  点击:(259)  评论:(0)  加入收藏
写在前面在像 Web 服务这样需要快速响应的应用场景中,SQL 的性能直接决定了系统是否可以使用;特别在一些中小型应用中,SQL 性能更是决定服务能否快速响应的唯一标准严格地优化...【详细内容】
2021-01-07  Tags: 性能优化  点击:(120)  评论:(0)  加入收藏
▌简易百科推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  快乐火车9d3    Tags:SQL   点击:(1)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  linux上的码农    Tags:sql   点击:(9)  评论:(0)  加入收藏
《开源精选》是我们分享Github、Gitee等开源社区中优质项目的栏目,包括技术、学习、实用与各种有趣的内容。本期推荐的HasorDB 是一个全功能数据库访问工具,提供对象映射、丰...【详细内容】
2021-12-22  GitHub精选    Tags:HasorDB   点击:(5)  评论:(0)  加入收藏
作者丨Rafal Grzegorczyk译者丨陈骏策划丨孙淑娟【51CTO.com原创稿件】您是否还在手动对数据库执行各种脚本?您是否还在浪费时间去验证数据库脚本的正确性?您是否还需要将...【详细内容】
2021-12-22    51CTO  Tags:Liquibase   点击:(3)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Bald    Tags:SQL   点击:(5)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  谣言止于独立思考    Tags:SQL基础   点击:(13)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  柠檬班软件测试    Tags:SQL   点击:(15)  评论:(0)  加入收藏
话说C是面向内存的编程语言。数据要能存得进去,取得出来,且要考虑效率。不管是顺序存储还是链式存储,其寻址方式总是很重要。顺序存储是连续存储。同质结构的数组通过其索引表...【详细内容】
2021-12-08  小智雅汇    Tags:数据存储   点击:(17)  评论:(0)  加入收藏
概述DBConvert Studio 是一款强大的跨数据库迁移和同步软件,可在不同数据库格式之间转换数据库结构和数据。它将成熟、稳定、久经考验的 DBConvert 和 DBSync 核心与改进的现...【详细内容】
2021-11-17  雪竹聊运维    Tags:数据库   点击:(26)  评论:(0)  加入收藏
一、前言 大家好,我是小诚,《从0到1-全面深刻理解MySQL系列》已经来到第四章,这一章节的主要从一条SQL执行的开始,由浅入深的解析SQL语句由客户端到服务器的完整执行流程,最...【详细内容】
2021-11-09  woaker    Tags:SQL   点击:(35)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条