SQL 优化极简法则,还有谁不会?

Intel RealSense D435 深度相机详解 Intel RealSense D435 深度相机是 Intel 推出的一款基于深度感知技术的相机,广泛应用于机器人、无人机、增强现实(AR)、虚拟现实(VR)、3D扫描、智能监控等领域。D435 是 RealSense 系列中较为高端的深度相机之一,结合了深度传感和 RGB 摄像技术,为用户提供高精度的深度数据和高质量的图像捕捉能力。 阅读详情

点击上方“芋道源码”,选择“设为星标”

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 8:55 更新文章,每天掉亿点点头发...

源码精品专栏

 

来源:blog.youkuaiyun.com/horses/article/details/105695431

  • 法则一:只返回需要的结果

  • 法则二:确保查询使用了正确的索引

  • 法则三:尽量避免使用子查询

  • 法则四:不要使用OFFSET实现分页

  • 法则五:了解SQL子句的逻辑执行顺序

  • 总结

SQL本身并不难学,编写查询语句也很容易,但是想要编写出能够高效运行的查询语句却有一定的技巧。

查询优化是一个复杂的工程,涉及从硬件到参数配置,不同数据库的解析器,优化器实现,SQL语句的执行顺序,索引和统计信息的采集等,甚至应用程序和系统的整体架构。几个关键法则,可以帮助我们编写高效的SQL查询;尤其是对于初学者而言,这些法则至少可以避免我们写出性能很差的查询语句。

以下法则适用于各种关系型数据库,包括但不限于:MySQL,Oracle,SQL Server,PostgreSQL以及SQLite等。如果觉得文章有用,欢迎评论????,点赞????,推荐????

法则一:只返回需要的结果

通常来说,OLTP系统每次只需要从大量数据中返回很少的几条记录;指定查询条件可以帮助我们通过索引返回结果,而不是全表扫描。在情况下使用索引时的性能更好,因为索引(B-树,B +树,B *树)执行的是二进制搜索,具有对数时间复杂度,而不是线性时间复杂度。以下是MySQL聚簇索引的示意图:

聚集索引

表述,假设每个索引分支可以存储100个记录,100万(1003)条记录只需要3层B-树即可完成索引。通过索引查找数据时需要读取3次索引数据(每次磁盘IO读取完整的查询结果。

相反,如果采用全表扫描,需要执行的磁盘IO次数可能高出几个数量级。当数据量增加到1亿(1004)时,B-树索引只需要再增加1次索引IO即可;而全表扫描则需要再增加几个数量级的IO。

同理,我们应该避免使用SELECT * FROM,因为它表示查询表中的所有分区。这种写法通常导致数据库需要读取更多的数据,同时网络也需要传输更多的数据,从而导致性能的下降。

B关于B-树索引的原理以及利用索引优化各种查询条件,连接查询,排序和分组以及DML语句的介绍,可以参考:https://tonydong.blog.youkuaiyun.com/article/details/104020721

法则二:确保查询使用了正确的索引

如果,这些适当的索引,即使指定了查询条件也不会通过索引查找数据。因此,我们首先需要确保创建了相应的索引。一般来说,以下需要创建索引:

  • 经常出现在WHERE条件中的分割建立索引可以避免全表扫描;

  • 将ORDER BY排序的细分加入到索引中,可以避免额外的排序操作;

  • 多表连接查询的关联范围建立索引,可以提高连接查询的性能;

  • 将GROUP BY分组操作细分加入到索引中,可以利用索引完成分组。

即使创建了合适的索引,如果SQL语句写的有问题,数据库也不会使用索引。导致索引失效的常见问题包括:

  • 在WHERE子句中对索引分段进行表达式运算或使用函数都会导致索引重置,这种情况还包括分段的数据类型不匹配,例如串联和整体进行比较;

  • 对于大型文本数据的模糊匹配,应该考虑数据库提供的全文检索功能,甚至专门的全文搜索引擎(Elasticsearch等);

  • 如果WHERE条件中的分段上创建了索引,则设置为NOT NULL;不是所有数据库使用IS [NOT] NULL判断时都可以利用索引。

执行计划(执行计划,也叫查询计划或解释计划)是数据库执行SQL语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。性能不够理想,我们首先应该查看它的执行计划,通过执行计划(EXPLAIN)确保查询使用了正确的索引。

各种关于各种主流数据库中执行计划的查看和解释,可以参考:https://tonydong.blog.youkuaiyun.com/article/details/103579177 https://blog.youkuaiyun.com/horses/article/details/ 106905110

法则三:尽量避免使用子查询

以MySQL首先,以下查询返回月薪大于部门平均月薪的员工信息:

EXPLAIN ANALYZE
 SELECT emp_id, emp_name
   FROM employee e
   WHERE salary > (
     SELECT AVG(salary)
       FROM employee
       WHERE dept_id = e.dept_id);
-> Filter: (e.salary > (select #2))  (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: avg(employee.salary)  (actual time=0.147..0.149 rows=1 loops=25)
            -> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id)  (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)

从执行计划可以修剪,MySQL中采用的是类似Nested Loop Join实现方式;子查询循环了25次,而实际上可以通过一次扫描计算并保存每个部门的平均月薪。以下语句替换子查询替换为等价的JOIN语句,实现了子查询的展开(子查询Unnest):

EXPLAIN ANALYZE
 SELECT e.emp_id, e.emp_name
   FROM employee e
   JOIN (SELECT dept_id, AVG(salary) AS dept_average
           FROM employee
          GROUP BY dept_id) t
     ON e.dept_id = t.dept_id
  WHERE e.salary > t.dept_average;
-> Nested loop inner join  (actual time=0.722..2.354 rows=6 loops=1)
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)
    -> Filter: (e.salary > t.dept_average)  (actual time=0.068..0.076 rows=0 loops=25)
        -> Index lookup on t using <auto_key0> (dept_id=e.dept_id)  (actual time=0.011..0.015 rows=1 loops=25)
            -> Materialize  (actual time=0.048..0.057 rows=1 loops=25)
                -> Group aggregate: avg(employee.salary)  (actual time=0.228..0.510 rows=5 loops=1)
                    -> Index scan on employee using idx_emp_dept  (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)

改写之后的查询利用了物化(Materialization)技术,将子查询的结果生成一个内存临时表;然后与员工表进行连接。通过实际执行时间可以研磨这种方式替换。

以上示例在Oracle和SQL Server中会自动执行子查询展开,两种写法效果相同;在PostgreSQL中与MySQL类似,第一个语句使用嵌套循环联接,改写为JOIN之后使用哈希联接实现,性能更好。

另外,对于IN和EXISTS子查询也可以进行类似的替代。由于不同数据库的优化器能力有所差异,我们应该避免使用子查询,考虑使用JOIN进行重组。

法则四:不要使用OFFSET实现分页

分页查询的原理就是先跳过指定的行数,再返回Top-N记录。分页查询的示意图如下:

分页查询

数据库一般支持FETCH / LIMIT以及OFFSET实现Top-N排行榜和分页查询。当表中的数据量很大时,这种方式的分页查询可能会导致性能问题。

-- MySQL
SELECT *
  FROM large_table
 ORDER BY id
 LIMIT 10 OFFSET N;

以上查询传递OFFSET的增加,速度会越来越慢;因为甚至我们只需要返回10条记录,数据库仍然需要访问并且过滤掉N(大约1000000)行记录,甚至通过索引也会涉及某些的扫描操作。

对于以上分页查询,更好的方法是记住上一次获取到的最大id,然后在下一次查询中作为条件提交:

-- MySQL
SELECT *
  FROM large_table
 WHERE id > last_id
 ORDER BY id
 LIMIT 10;

如果id基线上存在索引,这种分页查询的方式可以基本不受数据量的影响。

Top关于Top-N排行榜和分页查询的详细介绍,可以参考:https://tonydong.blog.youkuaiyun.com/article/details/108729112

法则五:了解SQL子句的逻辑执行顺序

以下是SQL中各个子句的语法顺序,前面括号内的数字代表了它们的逻辑执行顺序:

(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1)  FROM t1 JOIN t2
(2)    ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5)HAVING having_condition
(7) UNION [ALL]
   ...
(8) ORDER BY col1 ASC,col2 DESC
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

也就是说,SQL并不是按照编写的顺序先执行SELECT,然后再从子句执行。从逻辑上讲,SQL语句的执行顺序如下:

  1. 首先,FROM和JOIN是SQL语句执行的第一步。它们的逻辑结果是一个笛卡尔积,决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序,实际上数据库在获取表中的数据之前会使用ON和WHERE过滤条件进行优化访问;

  2. 基本,应用ON条件对上一步的结果进行过滤并生成新的数据集;

  3. 然后,执行WHERE子句对上一步的数据集再次进行过滤。WHERE和ON在大多数情况下的效果相同,但外部连接查询有所区别,我们将在替代示例中进行;

  4. 接着,基于GROUP BY子句指定的表达式进行分组;同时,对于每个分组计算聚合函数agg_func的结果。通过GROUP BY处理之后,数据集的结构就发生了变化,只保留了分组分段和聚合函数的结果;

  5. 如果存在GROUP BY子句,可以利用HAVING针对分组后的结果递增进行过滤,通常是针对聚合函数的结果进行过滤;

  6. 接下来,SELECT可以指定要返回的列;如果指定了DISTINCT关键字,需要对结果集进行去重操作。另外将会被指定为AS的拆分生成别名;

  7. 如果还有集合操作符(UNION,INTERSECT,EXCEPT)和其他的SELECT语句,执行该查询和合并两个结果集。对于集合操作中的多个SELECT语句,数据库通常可以支持并发执行;

  8. 如果存在GROUP BY子句或DISTINCT关键字,只能使用分组和聚合函数进行排序;否则,可以使用FROM和JOIN表中的任何细分;

  9. 最后,OFFSET和FETCH(LIMIT,TOP)限定了最终返回的行数。

了解SQL逻辑执行顺序可以帮助我们进行SQL优化。例如,在子句中执行子句之前,因此我们应该尝试使用WHERE进行数据过滤,避免无谓的操作;除非业务需要针对聚合函数的结果进行过滤。

另外,理解SQL的逻辑执行顺序还可以帮助我们避免一些常见的错误,例如以下语句:

-- 错误示例
SELECT emp_name AS empname
  FROM employee
 WHERE empname ='张飞';

该语句的错误在于WHERE条件中引用了列别名;从上面的逻辑顺序可以研磨,执行WHERE条件时还没有执行SELECT子句,也就没有生成拆分的别名。

另外一个需要注意的操作就是GROUP BY,例如:

-- GROUP BY 错误示例
SELECT dept_id, emp_name, AVG(salary)
  FROM employee
 GROUP BY dept_id;

由于经过GROUP BY处理之后结果集只保留了分组细分和聚合函数的结果,示例中的emp_name完全已经不存在;从业务逻辑上来说,按照部门分组统计之后再显示有人的姓名没有意义。需要同时显示员工信息和所在部门的汇总,可以使用窗口函数。

BY如果使用了GROUP BY分组,之后的SELECT,ORDER BY等只能引用分组或聚合函数;否则,可以引用FROM和JOIN表中的任何分支。

还有一些逻辑问题可能不会直接导致查询出错,但是会返回不正确的结果;例如外连接查询中的ON和WHERE条件。以下是一个左外连接查询的示例:

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id)
 WHERE e.emp_name ='张飞';
emp_name|dept_name|
--------|---------|
张飞     |行政管理部|

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='张飞');
emp_name|dept_name|
--------|---------|
刘备     |   [NULL]|
关羽     |   [NULL]|
张飞     |行政管理部|
诸葛亮   |   [NULL]|
...

第一个查询在ON子句中指定了连接的条件,同时通过WHERE子句发现了“张飞”的信息。

第二个查询将所有的过滤条件都放在ON子句中,结果返回了所有的员工信息。这是因为左外连接会返回左表中的全部数据,甚至ON子句中指定了员工姓名也不会强制;而WHERE条件在逻辑上是对连接操作之后的结果进行过滤。

总结

SQL优化本质上是了解优化器的工作原理,并创建合适的索引和正确的语句;同时,当优化器不够智能的时候,手动让它智能。



欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:

已在知识星球更新源码解析如下:

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 20 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

兄弟,艿一口,点个赞!????

OSG 核心详解 OpenSceneGraph (OSG) 的核心架构、场景图管理以及几何体的绘制 阅读详情

相关推荐

802.1AS时间同步机制深度解析:从Sync报文到多域冗余设计

本文深度解析了802.1AS时间同步机制,从Sync与Follow_Up报文的核心交互原理入手,详细阐述了其如何通过精确的时钟频率比测量、链路延迟计算和驻留时间补偿来实现微秒乃至纳秒级同步。文章进一步探讨了802.1AS-2020版本引入的多时钟域冗余设计,为构建高可靠的车载TSN网络提供了关键见解和实战配置指南。

pytorch8learner的博客 464

Spring中的Bean是线程安全的吗?

Spring容器中的bean可以分为5个范围: (1)singleton:默认,每个容器中只有一个bean的实例 (2)prototype:为每一个bean请求提供一个实例 一般来说下面几种作用域,在开发的时候一般都不会用,99.99%的时候都是用singleton单例作用域 (3)request:为每一个网络请求创建一个实例,在请求完成以后,bean会失效并被垃圾回收器回收 (4)session:与request范围类似,确保每个session中有一个bean的实例,在session过期后,be

赵广陆 8231

华为防火墙双ISP出口实战:如何用策略路由实现电信联通流量自动切换(附配置截图)

本文详细解析了华为防火墙在双ISP环境下的智能选路技术,通过策略路由与健康检测的深度应用,实现电信联通流量的自动切换。文章包含实战配置截图,帮助企业解决切换延迟、路径劣化等核心挑战,提升业务连续性。

backprop5master的博客 646

牛逼!有人用漫画带你了解 Linux 内核长啥样

点击上方“芋道源码”,选择“设为星标”做积极的人,而不是积极废人!源码精品专栏原创 | Java 2020 超神之路,很肝~中文详细注释的开源项目RP...

芋艿V 558

ChatGPT高效制作PPT:从内容生成到设计优化全流程

人工智能技术正在改变传统办公文档的创作方式,其中自然语言处理(NLP)与生成式AI的结合为内容创作带来了革命性突破。以ChatGPT为代表的大语言模型(LLM)能够理解复杂指令并生成结构化内容,其核心原理是通过海量数据训练获得的语义理解和文本生成能力。在办公自动化场景中,这种技术特别适合解决PPT制作中的两大痛点:内容创作耗时和逻辑架构困难。通过精心设计的提示词工程,AI可以快速输出包含核心要点、数据支撑的完整内容框架,甚至直接生成Markdown格式的代码以便快速转换为PPT。对于商业计划、项目汇报等高频

weixin_42613018的博客 496

图解基站机房:AAU、RRU与BBU之间的线缆连接与规格选择

本文以图解方式详解了5G基站机房中AAU、RRU与BBU三大核心设备之间的线缆连接方案与规格选择。重点阐述了电力线(如2×10 mm²直流电缆)、数据光缆(如野战光缆与波分复用)及射频馈线的功能、选型标准与实战布线法则,为通信工程部署与维护提供清晰指导。

weixin_29306011的博客 506

spring中的bean是线程安全的吗?

spring本身并没有针对bean做线程安全的处理,所以: 1.如果bean是无状态的,那么bean则是线程安全的。 2.如果bean是有状态的,那么bean则不是线程安全的。 另外bean是不是线程安全的,跟bean的作用域没有关系,bean的作用域只是表示bean的生命周期范围,对于任何生命周期的bean都是一个对象,这个对象是不是线程安全的,还是得看这个bean对象本身。

lcb1424556301的博客 588

别再死记硬背了!一张图搞懂无刷电机半桥与全桥驱动的核心差异(附避坑指南)

本文深入解析无刷直流电机的半桥与全桥驱动方式的核心差异,提供直观对比与选型指南。半桥驱动结构简单、成本低,适合低要求场景;全桥驱动性能优越但复杂,适用于高精度控制。文章还包含实战避坑指南,帮助工程师优化驱动方案选择与应用。

weixin_29290947的博客 324

String类型转换为Long类型

//返回Long包装类型 Long.ValueOf("String") //返回long基本数据类型 Long.parseLong("String")

Gblfy_Blog 3563

基于simulink 的不间断电源ups的项目实例

UPS系统的核心组件包括整流器、逆变器、电池管理系统和控制系统。- **Three-Phase Controlled Rectifier**:设置触发角为0度,用于控制整流器的输出电压。- **Three-Phase Inverter**:设置逆变器的输出频率为60Hz,用于将直流电转换为交流电。- **Control System**:编写MATLAB函数,用于监测市电状态并控制各模块的工作模式。- **Step**:设置阶跃时间,例如0.05秒,阶跃前后的值分别为0和1,用于模拟市电中断。

xiaoheshang_123的博客 712

Centos 7 Linux 安装单机 Hadoop(HDFS):下载、安装、配置、测试

本文详细介绍了在 CentOS 7 系统上安装与配置单机 Hadoop(HDFS)的完整流程,涵盖 JDK 环境准备、Hadoop 安装包下载与部署、核心配置文件(core-site.xml、hdfs-site.xml 等)修改、SSH 免密登录设置、HDFS 格式化及服务启动,并通过命令行与 Web 界面验证 HDFS 和 YARN 功能,适合 Hadoop 初学者快速搭建本地测试环境。

寒水馨 · 轻量级技术文档 · hanshuixin.org 3239

MethodArgumentTypeMismatchException: Failed to convert value of type

版本 spring boot : 1.5.10.RELEASE 1.请求方法 @RequestMapping(value = "find1user", method = RequestMethod.GET) public Result find1user() { r...

chengqi5964的博客 9226

【OpenCV】第二十四章: 图像特征匹配:暴力特征匹配算法、FLANN特征匹配算法

参数match就是匹配器的匹配结果;参数crossCheck表示交叉检查,意思就是用第一组中的每个描述子和第二组的所有描述子进行匹配完后,再用第二组中的每个描述子和第一组的所有描述子进行匹配,这两步做完后,两步同时找到的相同的匹配对儿就是有效的匹配。方法一:用匹配器的match方法,进行特征匹配:match = bf.match(des1, des2),就是对两幅图的描述子进行计算,返回匹配的结果。匹配的时候是计算两个描述子之间的相似度,然后返回相似度最高的一对儿匹配点,也就是最终的匹配结果。

因为热爱所以坚持 5062

SQL优化极简法则,还有谁不会?

点击上方☝码猿技术专栏轻松关注!及时获取有趣有料的技术文章作者:不剪发的Tony老师编辑:陶家龙出处:http://002ii.cn/YknfGUSQL 本身并不难学,编写查询语句也很...

码猿技术专栏 371

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

ssm框架搭建

这个是一个在idea上成功跑起来的ssm框架项目,保存在这里方便以后使用直接下载

转载:SQL 优化极简法则,还有谁不会?

转载自不剪发的Tony老师的SQL 优化极简法则 文章目录 查询优化是一个复杂的工程,涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等,甚至应用程序和系统的整体架构。本文介绍几个关键法则,可以帮助我们编写高效的 SQL 查询;尤其是对于初学者而言,这些法则至少可以避免我们写出性能很差的查询语句。 以下法则适用于各种关系型数据库,包括但不限于:MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。如果觉得文章有用,欢

JustDoSelf的博客 293

STM32F407中文手册

STM32f407中文手册 本参考手册面向应用开发人员,提供有关使用 STM32F405xx/07xx、STM32F415xx/17xx、 STM32F42xxx 和 STM32F43xxx 微控制器存储器与外设的完整信息。STM32F405xx/07xx、STM32F415xx/17xx、STM32F42xxx 和 STM32F43xxx 构成一个微 控制器系列,各产品具有不同的存储器大小、封装和外设。有关订购信息以及器件的机械与电气特性,请参见数据手册。有关 ARM Cortex™-M4F 内核的信息,请参见《Cortex™-M4F 技术参考手册》。

上一篇: 关于 Redis 缓存的三个一定要知道的问题哟!
下一篇: 如何让 Mybatis 自动生成代码,提升开发效率
公众号-芋道源码
博客等级 码龄10年 1639粉丝 377原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值