SQL查询太慢？实战讲解YashanDB SQL调优思路

本文是 Meetup 第十期 “调优实战专场” 的第二篇技术文章，上一篇《高效查询秘诀，解码 YashanDB 优化器分组查询优化手段》中，我们揭秘了 YashanDB 分组查询优化秘诀，本文将通过一个案例，助你快速上手 YashanDB 慢日志功能，精准定位 “慢 SQL” 后进行优化。
在这里插入图片描述

前言

说起 SQL 调优，不少人认为是个高难度的事情，让人望而却步。但其实在日常的运维过程中，慢 SQL 却是一个常见的问题，这时大家或多或少会用到一些常用的调优手段，比如加索引、调整执行计划等。

接下来，我们将通过三大环节详细讲解如何发现、分析和优化 “慢 SQL”，具体包括利用慢日志定位问题、分析执行计划并可通过 hint 手段人为调整优化、以及有效搜集统计信息优化执行计划等。这个通用思路可以在遇到性能问题时，提供一种可行的解决方案，帮助大家尝试着手解决问题。

如何找到慢 SQL

首先，SQL 调优最重要的是如何找到慢 SQL。

从业务层的表现来看，比如应用反应慢、接口获取数据超时、应用夯住等等。但若业务反馈说某条 SQL 很慢，能帮忙优化一下吗？我们该怎么确定这条 SQL 一定就是那条 “慢 SQL” 呢？

在 YashanDB 中，有多种方式可以辅助我们准确定位慢 SQL，以下是一些常用方式：

  • YCM 监控平台
  • AWR 报告
  • 系统性能视图，比如 V S Q L ， V SQL，V SQL，VSESSION 等
  • 慢日志 slow.log

本次主要介绍怎么使用慢日志功能来找到符合条件的慢 SQL。

01 slow log 的配置

主要通过两个基本参数控制，可以找到慢 SQL。

  • ENABLE_SLOW_LOG：是否开启慢日志，默认关闭。（即时生效，不建议长期开启，用于复现性能问题时抓取识别慢 SQL。）
  • SLOW_LOG_TIME_THRESHOLD ：时间阈值，单位毫秒。（代表超过这个时间阈值的 SQL，认为是慢 SQL，会记录在 slow.log 里面

执行下列命令开启慢日志：

ALTER SYSTEM SET ENABLE_SLOW_LOG = TRUE;

在这里插入图片描述
慢日志相关的参数介绍如下：

  • SLOW_LOG_FILE_NAME：慢日志文件名，使用默认即可。
  • SLOW_LOG_FILE_PATH：慢日志文件路径，使用默认即可。
  • SLOW_LOG_OUTPUT：慢日志输出方式，默认是 FILE，也可以设置成 TABLE，慢 SQL 信息会输出到系统表 SYS.SLOW_LOG$，建议使用默认的 FILE 即可。
  • SLOW_LOG_SQL_MAX_LEN：慢 SQL 打印的 SQL 长度，最长也是 2000，使用默认即可。

按照如上配置设置完成之后，执行时间超过 1s 的 SQL 就会被记录在 slow log 中。

测试完成之后，需要将 slow log 关闭：

ALTER SYSTEM SET ENABLE_SLOW_LOG = FALSE;

既然找到了慢 SQL，接下来需要针对该 SQL 进行优化，一般会去查看该 SQL 的执行计划情况。

执行计划的生成和调整

01 如何查看执行计划

查看执行计划通常有两种手段，通过 explain+SQL 或是通过 autotrace。

方式 1_：_通过 explain + SQL

比如：

explain select object_name from test limit 10;

方式 2_：_autotrace

各操作类别如下：

  • set autotrace on explain：输出语句的执行结果和执行计划
  • set autotrace on statistics：输出语句的执行结果和 SQL 执行统计信息
  • set autotrace traceonly：只输出语句的执行计划和执行统计信息
  • set autotrace on：输出语句的执行结果、执行计划和执行统计信息

注：输出统计信息需要设置参数 statistics_level=all（alter system set statistics_level=all;)

通过表格对比各个操作类别，更能直观感受其区别：
在这里插入图片描述
以下通过一个完整的测试用例，查看 explain 和 autotrace 各类别打印出来的具体信息。

  1. 创建测试表和索引
# 创建测试表
create table test as select * from dba_objects;

# 创建索引，为后面测试hint修改执行计划做准备
create index idx_obj_name on test (object_name);

在这里插入图片描述

  1. 使用方式 1 explain 查看执行计划
explain select object_name from test limit 10;

在这里插入图片描述
explain 的执行计划可以看到输出的表格信息，这个表格所展示的就是 SQL 的执行计划，后面会进行解读。

  1. 使用方式 2 的 set autotrace on explain
set autotrace on explain 
select object_name from test limit 10;

可以看到输出了执行结果和执行计划：
在这里插入图片描述

  1. 使用方式 2 的 set autotrace on statistics
(alter system set statistics_level=all;)
set autotrace on statistics 
select object_name from test limit 10;

可以看到输出了执行结果和统计信息：
在这里插入图片描述
5. 使用方式 2 的 set autotrace traceonly

set autotrace traceonly
select object_name from test limit 10;

可以看到输出了执行计划和统计信息：
在这里插入图片描述

  1. 使用方式 2 的 set autotrace on
set autotrace on
select object_name from test limit 10;

可以看到输出了执行结果、执行计划以及统计信息：
在这里插入图片描述
7. 关闭 autotrace 和调整 statistics_level
当测试完毕后，需要将相关设置还原。

#关闭autotrace
set autotrace off

 测试完成，调整statistics_level为默认的typical
alter system set statistics_level=typical;

通过上面这个例子，相信大家对 explain 和 autotrace 的使用有了一个全面的了解。不同的使用方式会有不同的效果，大家根据实际情况选择合适的方法使用即可。

02 执行计划解读

还是上面这个例子，我们来看看执行计划里都有哪些信息，各代表什么含义。
在这里插入图片描述
执行计划中各信息介绍如下：

  • Id：执行步骤的唯一标识，并不是执行顺序。
  • Operation type：执行算子，前面的空格标示计划的层次关系。
  • INDEX FAST FULL SCAN：代表扫描方式是索引快速全扫描
  • WINDOW：limit
  • SELECT STATEMENT：代表是 SELECT 查询
  • Name：对象名
  • Owner：对象所属用户
  • Rows：优化器根据统计信息和特定算法计算出来的行数预估值。图中显示的 100000 行其实不是真实的数据行数，为什么呢？咱们先留个悬念。
  • Cost (% CPU)：优化器根据算子和硬件等信息，算出的一个参考值，Cost 值越大，表示该层计划占用的资源越大。
  • Partition info：分区相关信息，因为这张表未分区，所以没有该部分相关信息。

如果在某些极端场景导致优化器生成的执行计划并不是最优的，YashanDB 也提供可以手动修改执行计划的手段。

03 通过 hint 调整执行计划

一般情况下，优化器是可以做出最优的执行效果。如果我们的统计信息未收集或者收集失效，这时候优化器可能无法根据错误的统计信息做出最优的执行计划，需要我们手动调整。这时可以选择通过 hint 去调整执行计划。

hint 是一种特殊的注释，其格式和位置固定，作用是影响优化器对执行计划的选择，但非强制。

通过 hint 填写的内容可以分为以下三种：

  • leading：改变表的 join 顺序；
  • use_nl/use_hash/use_merge 等：改变表的连接类型；
  • index/full 等：改变访问路径。

通过添加 hint /+full(test)/ 调整 SQL 使用全表扫描

explain select /*+full(test)*/ object_name from test limit 10;

在这里插入图片描述

可以看到，通过 hint，将原本的索引快速全扫描（INDEX FAST FULL SCAN）调整为了全表扫描（TABLE ACCESS FULL）。

注：这里只是为了验证 hint 确实可以改变执行计划。在此用例中，全表扫描并不是最优的。*

统计信息的收集

在前面执行计划的解读中，TEST 表的预估行数是 100000，这其实是因为没有收集统计信息，系统给的默认值为 100000 行。下面我们来看看怎么收集统计信息。

01 查看统计信息是否有效

统计信息相关参数介绍如下：

  • DBA_TAB_STATISTICS：表统计信息
  • DBA_TAB_COL_STATISTICS：列统计信息
  • DBA_IND_STATISTICS：索引的统计信息
  • DBA_PART_COL_STATISTICS：分区列的统计信息

比如前面所使用的 test 表，并未收集过统计信息，通过视图查出来信息如下：

select table_name,last_analyzed,stale_stats from dba_tab_statistics where owner='SKYE' and table_name='TEST';
 # SKYE 是 TEST表的所属用户

在这里插入图片描述
LAST_ANALYZED 是指上一次该对象统计信息的收集时间，为空代表从未收集过。

02 统计信息收集方式

在 YashanDB 中，可以从全库、按用户、按表，3 个维度进行统计信息收集，来应对不同的场景。

情况 1：全库收集

exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER', 1, 48,  'FOR ALL COLUMNS SIZE AUTO', 'ALL', TRUE, FALSE);

情况 2：按用户收集

exec DBMS_STATS.GATHER_SCHEMA_STATS('SKYE',1,TRUE,'FOR ALL COLUMNS SIZE AUTO',48,'ALL',TRUE);

情况 3：按表收集

exec DBMS_STATS.GATHER_TABLE_STATS('SKYE', 'TEST','', 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 32, 'AUTO', TRUE);

注：一般大库、大表建议使用按表收集统计信息，因为收集统计信息也会消耗系统性能。这里的 1 代表采样率为 1，表数据量比较大的情况下可以使用 0.2 即可。这里的 32 代表并行度，如果系统 CPU 等资源充足，可以适当调大该值加快统计信息速度。

收集完 test 表统计信息之后，视图查询如下：
在这里插入图片描述
可以看到，统计信息收集完成之后，视图会记录该对象的收集情况，在执行计划的 Rows 估算中，能更加真实的预估行数，Cost 也会随之降低。

总结

很多时候 SQL 突然变慢，可能是执行计划改变了；而执行计划突然改变很可能是统计信息失效了，导致优化器不能根据真实的统计信息选择出最优的执行计划。从以往经验来看，可以先查看涉及到的表、分区和索引相关的统计信息是否是正常有效的。

工欲善其事，必先利其器。不论是 AWR、系统视图还是 slow log 工具，大家能够找到适合自己的某一种方法，熟练掌握即可。本文主要介绍的是通过慢日志功能进行 SQL 调优，感兴趣的同学也可以到 YashanDB 官网的文档中心参考官方文档，试试通过 AWR、系统视图等方式，找到慢 SQL 进行分析。

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

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

相关文章

爱堡集团数智掘金—共绘上市蓝图

爱堡集团数智掘金—共绘上市蓝图

（本台记者报）2024年5月26日爱堡集团在浙江省杭州市上城区瑞莱克斯大酒店隆重召开规模达500人的盛会。这场聚焦智慧与创新的会议，旨在加速爱堡集团的数智化转型进程，并为其上市之路绘制蓝图，吸引了众多行业领袖和媒体的…
阅读更多...
统信UOS桌面操作系统1070安装vmware workstation 17

统信UOS桌面操作系统1070安装vmware workstation 17

原文连接：统信UOS桌面操作系统1070安装vmware workstation 17 Hello，大家好啊！今天给大家带来一篇在统信UOS桌面操作系统1070上安装VMware Workstation 17.5.2的文章。VMware Workstation是一款强大的虚拟化软件，可以让您在一台计…
阅读更多...
未来工厂新篇章：大型工厂3D可视化技术引领工业新潮流

未来工厂新篇章：大型工厂3D可视化技术引领工业新潮流

在科技日新月异的今天，大型工厂不再是我们印象中机器轰鸣、尘土飞扬的钢铁丛林，而是变成了智慧与效率并存的现代化生产中心。这一切的改变，都离不开一项革命性的技术——3D可视化。 一、何为大型工厂3D可视化？ 大型工厂3D可视化&…
阅读更多...
vue3 setup 使用 beforeRouteEnter 组件内路由守卫

vue3 setup 使用 beforeRouteEnter 组件内路由守卫

vue3 setup 使用 beforeRouteEnter 组件内路由守卫 setup 中只有onBeforeRouteLeave、onBeforeRouteUpdate两个钩子函数&#xff0c; 没有beforeRouteEnter对应的钩子函数&#xff0c;所以无法在setup中直接使用 <script setup> onBeforeRouteLeave((to, from) > {// …
阅读更多...
pytorch使用tensorboardX面板自动生成模型结构图和各类可视化图像

pytorch使用tensorboardX面板自动生成模型结构图和各类可视化图像

总结&#xff1a; 在原本代码中额外添加如下几行即可实现查看模型结构&#xff1a; from tensorboardX import SummaryWriter # 用于进行可视化# 1. 来用tensorflow进行可视化with SummaryWriter("./log", comment"sample_model_visualization") as sw: …
阅读更多...
【echarts】 渐变色格状横条图

【echarts】 渐变色格状横条图

案例来源 https://www.makeapie.cn/echarts_content/xutsGwXHGt.html 效果 源码 let list [{name: 中和,value: 6}, {name: 西园,value: 1}, {name: 肖家河,value: 0}, {name: 石羊,value: 8}, {name: 合作,value: 0},{name: 桂溪,value: 6},{name: 芳草街,value: 1} ]; le…
阅读更多...
Navicat使用ssh隧道连接mysql数据库

Navicat使用ssh隧道连接mysql数据库

转载请标明出处&#xff1a;http://blog.csdn.net/donkor_/article/details/139352748 文章目录 前言新建连接MySql,填写ssh隧道信息方式1&#xff1a;使用密码方式连接方式二&#xff1a;使用密钥方式连接 填写常规信息总结 前言 使用ssh隧道连接数据库&#xff0c;方便本机…
阅读更多...
PS系统教程06

PS系统教程06

图片裁剪-详细版 首先勾选图层-单机裁剪工具-删除裁剪像素 背景颜色是和左边工作区颜色保持一致的。 确定选择 单机两下工作区中的√按下回车键 缩小裁剪 当你缩小裁剪之后再想扩大&#xff0c;那么扩大的部分就是背景颜色 不勾选删除裁剪像素效果&#xff08;裁剪完单机一…
阅读更多...
Element ui 快速入门（基础知识点）

Element ui 快速入门（基础知识点）

element ui官网 前言&#xff1a; 在当今时代&#xff0c;我们在编写计算机程序时&#xff0c;不仅仅是写几个增删改查的简单功能&#xff0c;为了满足广大用户对页面美观的需求&#xff0c;为了让程序员们写一些功能更简便&#xff0c;提高团队协作效率&#xff0c;所以eleme…
阅读更多...
看到大厂工时爆料，我沉默了。。

看到大厂工时爆料，我沉默了。。

大厂工时爆料 今天逛脉脉的时候&#xff0c;看到一篇名为「一人一句&#xff0c;大厂工时爆料」的帖子&#xff1a; 点开之后&#xff0c;我沉默了 ... 出来爆料的基本上都是 10 小时。 好奇心之下&#xff0c;我搜索了一下去年很热的排行榜&#xff1a; 2023 年最新互联网公司…
阅读更多...
开发小技巧

开发小技巧

1.根据JSON生成实体类 打开网站&#xff1a;在线JSON校验格式化工具&#xff08;Be JSON&#xff09; 选中JSON转JAVA实体类&#xff0c;在文本框中输入要转实体类的JSON&#xff0c; 在下边可以输入类名、包名&#xff0c;然后点击下载即可 2、IDEA中复制类的全路径&#xf…
阅读更多...
Java—— StringBuilder 和 StringBuffer

Java—— StringBuilder 和 StringBuffer

1.介绍 由于String的不可更改特性&#xff0c;为了方便字符串的修改&#xff0c;Java中又提供了StringBuilder和Stringbuffer类&#xff0c;这两个类大部分功能是相同的&#xff0c;以下为常用方法&#xff1a; public static void main(String[] args) {StringBuilder sb1 n…
阅读更多...
electron初学

electron初学

最近有一个开发桌面端的业务&#xff0c;考虑到跨平台就使用了electron。 引用官网&#xff1a;Electron是一个使用 JavaScript、HTML 和 CSS 构建桌面应用程序的框架。 嵌入 Chromium 和 Node.js 到 二进制的 Electron 允许您保持一个 JavaScript 代码代码库并创建 在Windows…
阅读更多...
[代码复现]Self-Attentive Sequential Recommendation(ing)

[代码复现]Self-Attentive Sequential Recommendation(ing)

参考代码&#xff1a;SASRec.pytorch 可参考资料&#xff1a;SASRec代码解析 前言&#xff1a;文中有疑问的地方用?表示了。可以通过ctrlF搜索’?。 环境 conda create -n SASRec python3.9 pip install torch torchvision因为我是mac运行的&#xff0c;所以device是mps 下面…
阅读更多...
MATLAB函数模块光显示zeros/poles怎么办？

MATLAB函数模块光显示zeros/poles怎么办？

出现下面这种图了怎么办&#xff1f;是做错了吗&#xff1f; 这种图就是它显示不完整了&#xff0c;把它拉大点就可以完全显示了。
阅读更多...
编辑任何场景! 3DitScene：通过语言引导的解耦 Gaussian Splatting开源来袭！

编辑任何场景! 3DitScene：通过语言引导的解耦 Gaussian Splatting开源来袭！

文章&#xff1a;https://arxiv.org/pdf/2405.18424 项目&#xff1a;https://zqh0253.github.io/3DitScene/ huggingface:https://huggingface.co/spaces/qihang/3Dit-Scene 场景图像编辑在娱乐、摄影和广告设计中至关重要。现有方法仅专注于2D个体对象或3D全局场景编辑&…
阅读更多...
harbor -- docker私有仓库安装配置

harbor -- docker私有仓库安装配置

1 安装docker-compose $ curl -L "https://get.daocloud.io/docker/compose/releases/download/v1.25.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose $ chmod x /usr/local/bin/docker-compose 2 安装配置harbor $ wget https://g…
阅读更多...
Windows11 wsl2编译Android14 使用ASfP Debug windows上启动的模拟器

Windows11 wsl2编译Android14 使用ASfP Debug windows上启动的模拟器

wsl2的安装和配置 安装&#xff1a; 直接百度搜索最新的wsl2安装教程即可&#xff0c;官网&#xff1a;https://learn.microsoft.com/zh-cn/windows/wsl/install 1. 启用适用于 Linux 的 Windows 子系统(以管理员身份打开 PowerShell 并运行) Enable-WindowsOptionalFeature…
阅读更多...
网络安全岗秋招面试题及面试经验分享

网络安全岗秋招面试题及面试经验分享

Hello&#xff0c;各位小伙伴&#xff0c;我作为一名网络安全工程师曾经在秋招中斩获&#x1f51f;个offer&#x1f33c;&#xff0c;并在国内知名互联网公司任职过的职场老油条&#xff0c;希望可以将我的面试的网络安全大厂面试题和好运分享给大家~ 转眼2024年秋招又快到了金…
阅读更多...
【学习】软件测试中如何进行Web网页兼容性测试

【学习】软件测试中如何进行Web网页兼容性测试

在数字时代&#xff0c;Web网页作为信息传递和交流的重要平台&#xff0c;其稳定性和用户体验至关重要。如同一位匠人细致打磨他的工艺品&#xff0c;开发者亦需精心测试网页的兼容性&#xff0c;确保其在各种设备和浏览器上的表现无懈可击。今天&#xff0c;我们就来探讨如何对…
阅读更多...
最新文章

Copyright @ 2022~2023