【AICon】探索RAG 技术在实际应用中遇到的挑战及应对策略!AICon精华内容已上线73%>>> 了解详情
写点什么

SQL 性能第 4 篇:其他注意事项

  • 2019-11-15
  • 本文字数:2779 字

    阅读完需:约 9 分钟

SQL性能第4篇:其他注意事项

今天我们简要概述我们尚未讨论的 SQL 优化的其他几个方面。以及对我们 SQL 性能和优化的四个部分文章进行总结。希望对大家在 SQL 性能优化方面有所帮助。


今天的文章总结了我们对 SQL 性能和优化的四个部分。 第一部分介绍并解释了关系优化;第二部分回顾了查询分析,介绍了单表访问路径的制定方法;第三部分介绍了多表 SQL 优化……今天我们简要概述了我们尚未讨论的 SQL 优化的其他几个方面。

1 使用索引避免排序

为了满足特定类型的 SQL 请求,DBMS 可能需要对数据进行排序。但是排序的成本太高,应该尽可能避免。关系优化过程理解排序的开销,并将其转化为优化决策。


也就是说,可以做一些事情来帮助优化器。dba 可以通过在需要排序的列上创建索引来设计索引,从而避免排序。当索引存在时,关系优化器将尽量使用这些索引来避免排序。当指定下列子句时,可能会发生排序:


  • DISTINCT:当指定了这个子句时,DBMS 要求结果数据的每一列都按顺序排列,以便从结果集中删除重复的行。

  • UNION:这个操作要求对每个选择列表中的列进行排序,因为结果集不能有重复的行。如果 DBMS 支持 INTERSECT 和 EXCEPT,那么同样的考虑也适用于这些操作。

  • GROUP BY:当指定这个子句时,DBMS 要求按照指定的列对数据进行排序,以便聚合数据。

  • ORDER BY:当指定这个子句时,DBMS 将确保按照指定的列对结果集进行排序。


考虑以下 SQL 语句:


SELECT   last_name, first_name, middle_initial, empno, positionFROM     employeeWHERE    position in ('MANAGER', 'DIRECTOR', 'VICE PRESIDENT')ORDER BY last_name;
复制代码


如果 last_name 列上存在索引,查询可以使用该索引并避免排序。使用索引来避免排序可以抵消排序所需的额外 CPU 成本和索引访问所需的额外 I/O 成本。当然,如果无论如何都要使用索引,这个选择是显而易见的。使用索引实际上是否比扫描数据和排序快取决于几个因素,包括符合条件的行数、排序的速度和索引的特性(例如集群)。


此外,在编写指定 UNION 操作的查询代码时,要注意检查应用程序的需求。如果没有重复的行,或者不关心结果集中是否存在重复,可以指定 UNION ALL,以避免重复删除的排序。

2 为什么没有选择索引?

有时会出现这样的情况,你认为优化器应该选择索引,但是它没有选择。任何原因都可能导致优化器避免使用索引。


请参考以下建议选择索引的方法:


  • 查询是否指定了搜索参数?如果没有谓词使用搜索参数,优化器就不能使用索引来满足查询。

  • 你加入了大量的表?某些 DBMS 中的优化器在加入大量表时可能会产生不可预测的查询计划结果。

  • 统计当前?如果插入、更新和/或删除了大量数据,则应该重新捕获数据库统计信息,以确保优化器拥有最新的信息,可以根据这些信息制定查询计划。

  • 你正在访问一个小表吗?对于非常小的表,简单地访问所有数据并进行排序可能比使用索引更有效。

  • 你正在使用存储过程吗?有时候 DBMS 提供了一些选项,这样一个存储过程在编译后就不会为后续的执行重新制定查询计划。你可能需要重新编译或优化存储过程,以利用最新的统计数据、新索引或任何其他相关的数据库更改。

  • 是否需要其他谓词?不同的 WHERE 子句可能使优化器能够考虑不同的索引。


有些表可能根本没有定义任何索引。每个表必须至少有一个索引,这是一个神话。下面是一些在表上不定义索引的情况:


  • 当所有访问都检索表的每一行时。因为每次想要使用该表时都会检索每一行,所以索引(如果使用)只会添加额外的 I/O,并且会降低而不是提高性能。虽然不是很常见,但你可能会在组织中遇到这样的表。

  • 一个只有几页数据、没有主键或惟一性要求的非常小的表不需要索引。小型表(可能有 20 到 30 页左右)可能不需要索引,因为简单地读取所有页面已经非常有效。

  • 当性能无关紧要且表仅很少被访问时。但是,在现实世界中,你什么时候有过这种需求呢?


除了这些情况,你很可能希望在每个表上构建一个或多个索引,并尝试让 DBMS 使用这些索引。

3 散列访问

一些数据库系统还支持散列。哈希是一种算法,它将一个键(一个或多个列)转换成一个小数字,通常是磁盘上的存储位置。哈希函数返回的值称为哈希值、哈希和或简单的哈希。键值由哈希算法(也称为哈希函数、哈希例程或随机化器)处理并转换为存储位置。当插入数据时,哈希算法告诉 DBMS 在哪里物理存储数据;当关键字访问数据时,算法会精确地告诉 DBMS 在哪里可以找到数据。


散列甚至比直接索引查找更有效,因为它通常导致更少的 I/O。不需要遍历多个索引页(从根到非叶页,再到叶页,然后到数据),散列将键转换为磁盘上的特定位置。在最佳情况下,这将导致一个 I/O。如果哈希算法为多个键生成相同的位置,就会发生哈希冲突,这需要额外的 I/O。


哈希主要用于为少量数据优化随机 I/O,比如查找代码表值或根据其主键值访问一行。然而,散列在关系系统中并不常见。


如果存在散列,并且当散列存在时,优化器将在制定访问路径时考虑使用任何散列结构。

4 并行访问

关系优化器可以选择并行运行查询。当 DBMS 调用查询并行性时,将调用多个并发任务来访问数据。DBMS 支持三种基本类型的并行:


  • I/O 并行性允许为单个查询启动并发 I/O 流。运行并行 I/O 任务可以显著提高 I/O 绑定查询的性能。将查询的数据访问分解为并行执行的并发 I/O 流可以减少查询的总运行时间。

  • CPU 并行性支持在查询中处理 CPU 的多任务处理。经常调用 CPU 并行性还会调用 I/O 并行性,以使每个 CPU 引擎能够使用自己的 I/O 流。CPU 并行性将查询分解为多个较小的查询,这些查询可以在多个处理器上并发执行。CPU 并行性可以进一步减少查询的运行时间。

  • 最后,DBMS 可以部署系统并行性来进一步增强并行查询操作。系统并行性允许一个查询被分解,并在多个 DBMS 实例之间运行。允许单个查询利用多个 DBMS 实例的处理能力可以进一步减少复杂查询的总体运行时间。


并行不是万能的。通常,优化器将必须制定多个访问路径(一个用于并行,一个用于单流),以便在无法使用适当的系统资源进行并行处理时拥有回退方法。也就是说,使优化器能够为 I/O 和/或 CPU 绑定的 SQL 构建并行访问路径是有意义的。

总结

该系列文章旨在提供与 DBMS 无关的 SQL 性能和优化介绍。当然,还有大量额外的细节需要理解和消化,才能使主题正确。在这些细节中,最重要的是你正在使用的 DBMS 的确切特性和支持。


确保使用正确的索引和访问方法制定正确的查询计划是一个耗时的过程,但是可以通过提高性能的形式获得巨大的收益。dba 应该训练他们的应用程序开发人员理解关系优化并创建最优 SQL。应用程序开发人员有责任编写高效的 SQL 和程序逻辑。


然而,DBA 是关系数据库性能的哨兵。当出现性能问题时,DBA 必须寻找问题的原因并提出解决方法。此外,DBA 应该进行设计评审,在将次优访问路径和程序迁移到生产状态之前,找出并调优低效的 SQL。


本文转载自公众号 360 云计算(ID:hulktalk)。


原文链接:


https://mp.weixin.qq.com/s/SyZWIZVqPm7lsC6eTrppNw


2019-11-15 14:33589

评论

发布
暂无评论
发现更多内容

办事利器推荐 | 社区征文

工程师日月

工具软件 6月月更 初夏征文

GTID详解

乌龟哥哥

6月月更

远程办公三部曲 - 如何提高沟通效率| 社区征文

耳东@Erdong

沟通 远程办公 6月月更 初夏征文

C#入门系列(十八) -- 类的属性、索引、事件和this关键字

陈言必行

C# 6月月更

模块四

Geek_2ce415

InfoQ 极客传媒15周年庆征文|云原生运维排障的关键要点

穿过生命散发芬芳

云原生 6月月更 InfoQ极客传媒15周年庆

流计算中的死锁

Damon

6月月更

如何构建、部署运行Flink程序

百思不得小赵

flink 部署 6月月更

BOM核心——window对象之Golbal

大熊G

JavaScript 前端 6月月更

别再说你不知道分布式事务了

牧小农

【Spring 学习笔记(十二)】Spring AOP 切入点表达式

倔强的牛角

Java spring spring aop Java EE 6月月更

测试需要做单元测试吗?

老张

软件测试 单元测试

前端食堂技术周刊第 41 期:TC 39 会议、IE 退役、React Labs、Storybook 组件百科全书

童欧巴

前端 React IE TC39

彻底搞懂 select/poll/epoll,就这篇了!

C++后台开发

网络编程 linux开发 epoll select C++开发

金融之外,区块链还适合什么场景?

TinTinLand

区块链+ 科技 web3

Web3 游戏:现状与未来

TinTinLand

区块链 科技 web3

Linux开发_Linux下进程编程

DS小龙哥

6月月更

Docker进阶(一):docker -v目录挂载

No Silver Bullet

Docker 6月月更

算法系列之动态规划

坚果

6月月更

五八同城(58.com)研发效能组织和团队建设之路

laofo

互联网 DevOps 研发效能 持续交付 工程效率

手把手教你在CentOS环境安装Docker

迷彩

Docker 架构 运维 6月月更 InfoQ极客传媒15周年庆

Mysql源码阅读 -- Windows10编译运行MySQL源码

c++ MySQL 源码学习

颠覆Web2 社交媒体,Liberty 计划在波卡上找到了归宿

One Block Community

区块链 科技

SeekTiger迎多重利好,旗下生态NFT、DAO VC平台将陆续上线

鳄鱼视界

spring4.1.8扩展实战之七:控制bean(BeanPostProcessor接口)

程序员欣宸

Java spring SpringFramework 6月月更

【前端】前后端交互重点Ajaxの介绍及实战

孤寒者

json ajax tornado 6月月更

创建视图——基于函数的视图 Django

海拥(haiyong.site)

Python django 6月月更

SDN系统方法 | 7. 叶棘网络

俞凡

架构 网络 sdn SDN系统方法

SeekTiger的崛起,旗下生态NFT、DAO VC平台将陆续上线

股市老人

Feature Store Meetup V3回顾|华为商城&第四范式&众安保险特征平台建设实践

星策开源社区

华为商城 OpenMLDB Feature Store MLOps 特征工程

关于 Angular SSR 应用在渲染中止时如何避免内存泄漏问题的一些尝试

Jerry Wang

typescript angular SSR Spartacus 6月月更

SQL性能第4篇:其他注意事项_文化 & 方法_360云计算_InfoQ精选文章