几个必须掌握的SQL优化技巧(二):如何定位低效率执行SQL

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。

1 前言


在应用的开发过程中,由于开发初期的数据量一般都比较小,所以开发过程中一般都比较注重功能上的实现,但是当完成了一个应用或者系统之后,随着生产数据量的急剧增长,那么之前的很多sql语句的写法就会显现出一定的性能问题,对生产的影响也会越来越大,这些不恰当的sql语句就会成为整个系统性能的瓶颈,为了追求系统的极致性能,必须要对它们进行优化。


这篇文章开始后面的几篇文章将结合自己平时工作和学习中的知识记录下,当面对一个有sql性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题、解决问题。


2 正文


上一篇文章:几个必须掌握的SQL优化技巧(一):查看SQL语句的执行频率介绍了如何通过两个命令来查询SQL的执行频率。这两个命令分别是:


show[session|global] status
复制代码



show global status like 'Innodb_rows_%'
复制代码


今天这篇文章将介绍如何定位到低效率的执行SQL。


通常可以通过以下两种方式来定位到执行效率比较低的SQL语句。


1、慢查询日志:慢查询指的是我们自己可以设定一定的标准,如果执行sql语句的效率低于这个标准,那么就算慢查询并被记录在慢查询日志中,这个功能需要开启才能用。通过慢查询日志定位那些执行效率较低的SQL语句,用


log-slow-queries[=file_name]
复制代码


选项启动时,mysqld写一个包含所以执行时间超过long_query_time秒的sql语句的日志文件。


在MySQL的配置文件my.cnf中写上:


# 慢查询的定义时间,这里是2秒
long_query_time = 2
# 慢查询日志的位置
log-slow-queries = /var/lib/mysql/mysql-slow.log
复制代码


long_query_time是指执行超过多久的SQL会被日志记录下来,这里是2 秒。


log-slow-queries设置把日志写在那里,上面则表示慢查询日志会写到文件/var/lib/mysql/mysql-slow.log中,当为空的时候,系统会给慢查询日志赋予主机名,并加上slow.log。如果设置了参数log-long-format ,那么所有没有使用索引的查询也将被记录。


除了上面的通过修改配置文件 的方式开启慢日志查看还可以通过mysql数据库的方式开始慢查询日志:


set global slow_query_log=ON
set global long_query_time = 3600
set global log_querise_not_using_indexes =ON
复制代码


这是一个非常有用的日志。它对于性能的影响不大(假设所有查询都很快),并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用)。


2、show processlist命令:慢查询日志在查询结束后才记录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以通过使用show processlist命令查看当前mysql在进行的线程,包括线程的状态、是否锁表等,可以实时地查看sql的执行情况,同时对一些锁表操作进行优化。


b7fce0d7839d4544848658f6fd9c62e8~tplv-k3u1fbpfcp-zoom-in-crop-mark_1304_0_0_0.webp.jpg


可以看到id=13的表示正在执行刚刚的show processlist。


通过show processlist命令查询结果的字段的含义分别是:


1、id:用户登录mysql时,系统分配的“Connection_id”,可以使用函数connection_id()函数进行查看;


2、User:显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句;


3、Host:显示这个语句是从哪个ip的哪个端口上发出的,可以同来跟踪出现问题语句的用户;


4、db:显示这个进行当前链接的数据;


5、command:显示当前链接执行的命令,一般取值为sleep、query、connect等;


6、time:显示这个状态持续的时间,单位是秒;


7、state:显示当前连接的sql语句的状态,很重要的列,state描述的是语句执行的某一个状态,一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成;


8、info:显示这个sql语句,是判断问题语句的一个重要依据


3 总结


通过上述的两种方式:慢查询日志和show processlist命令,可以定位到执行效率比较低的sql语句,后续可以针对这些执行效率比较低的sql语句进行sql优化,这个将在后面进行介绍。


后面将会继续分享关于sql优化的其他方法、步骤。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
21天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
36 0
|
5月前
|
SQL Oracle 关系型数据库
sql优化技巧
sql优化技巧
40 0
|
8月前
|
SQL 关系型数据库 MySQL
详解MySQL慢SQL定位、分析
1.概述 解决慢SQL的问题无非3步: 定位慢SQL 分析慢SQL 优化慢SQL 本文将按顺序介绍前两步该怎么做,第三步将会在后续的文章中详细讨论。
392 0
|
SQL 关系型数据库 MySQL
MySQL慢查询日志:如何定位执行慢的sql语句
MySQL慢查询日志:如何定位执行慢的sql语句
341 0
MySQL慢查询日志:如何定位执行慢的sql语句
|
SQL 存储 缓存
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
|
SQL Java 数据库连接
springboot-maven项目+jpa 运行过程中执行resources下sql脚本文件-ClassPathResource和ScriptUtils.executeSqlScript的使用
springboot-maven项目+jpa 运行过程中执行resources下sql脚本文件-ClassPathResource和ScriptUtils.executeSqlScript的使用
428 0
springboot-maven项目+jpa 运行过程中执行resources下sql脚本文件-ClassPathResource和ScriptUtils.executeSqlScript的使用
|
SQL IDE 开发工具
Python脚本执行hive SQL命令
Python脚本执行hive SQL命令
|
SQL Java 关系型数据库
java执行自定义sql时报错 error in your SQL syntax;
java执行自定义sql时报错 error in your SQL syntax;
139 0
java执行自定义sql时报错 error in your SQL syntax;
|
SQL 数据库
慢SQL定位
慢SQL定位
62 0
|
SQL 存储 关系型数据库
如何定位慢查询SQL以及优化
如何定位慢查询SQL以及优化
350 0
如何定位慢查询SQL以及优化