SQL优化之慢查询和explain以及性能分析

时间:2022-03-27 17:10:57

性能优化的思路

  • 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句

  • 使用explain去查看该sql的执行计划

  • 使用show profile去查看该sql执行时的性能问题

MySQL性能优化之慢查询

数据库的查询速度是影响项目性能的重要因素,除了添加缓存中间件外,对于查询本身的优化带来的性能也是不容小觑

要想优化查询SQL,就应该先找打需要被优化的SQL语句,MySQL提供了这么一个功能可以帮助我们快速定位带查询慢的SQL

MySQL的慢查询日志功能:默认关闭,需要手动开启

查看是否开启慢查询日志功能:show variables like '%slow_query%';

SQL优化之慢查询和explain以及性能分析

SQL优化之慢查询和explain以及性能分析

  • slow_query_log :是否开启慢查询日志,ON 为开启,OFF 为关闭,如果为关闭可以开启

  • slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_-slow.log

下面我们开启慢查询日志功能:

长期有效(修改 :/etc/my.cnf配置文件如下 记得重启mysql生效)

  • [mysqld]

  • slow_query_log = ON

  • slow_query_log_file = /var/log/mysql/slow.log

  • long_query_time = 1

临时开启,重启后失效:(测试用)

  • set global slow_query_log = ON; (开启)

  • set global long_query_time = 1; (便于测试,设置阀值,后面我改的是配置文件为0.1)

SQL优化之慢查询和explain以及性能分析   SQL优化之慢查询和explain以及性能分析

我们显示查询慢查询的阀值,也就是sql执行时间超过该值就会被记录,单位秒,我们可以看到是10秒

我们已经开启了慢查询日志功能,并设置相应的阀值,下面我们来测试一下:

  SQL优化之慢查询和explain以及性能分析     SQL优化之慢查询和explain以及性能分析

SQL优化之慢查询和explain以及性能分析可以看到已经生效了,但是中途其实还是出了点小问题,比如在设置阀值的时候失效了,没有设置上,在比如慢查询日志没有写入文件;

上面的日志重要部分解析:

  • Query_time:这条sql执行的时间

  • Lock_time:在Mysql服务器阶段等待表锁时间

  • Rows_sent:查询返回的行数

  • Rows_examined:查询检查的行数,越长查询的时间越长

  • 最后一排就是我们查询的sql语句信息

MySQL性能优化之查看执行计划:explain

(若有侵权,请联系删除)

在上面的慢查询中,我们已经将查询时间超过阀值的sql语句过滤了出来,下面我们来具体分析分析是哪里出了问题

MySQL性能分析语句show profile

Query Profile是MySQL自带的一种Query诊断分析工具,可以完整的显示一条sql执行的各方面的详细信息,默认关闭;

查看是否开启Profile功能:

  • select @@profiling;

  • show variables like '%profil%';

SQL优化之慢查询和explain以及性能分析   SQL优化之慢查询和explain以及性能分析

可以看到 profiling为 OFF,profiling为0,表示关闭状态,下面我们将其开启:set profiling=1; (1:开 / 0:关)

  SQL优化之慢查询和explain以及性能分析

再次查看是否打开性能分析工具:select@@profiling; (之前为0)

  SQL优化之慢查询和explain以及性能分析

下面我们开始准备测试:

  SQL优化之慢查询和explain以及性能分析

  • 第一个被圈中的是我们要执行的测试sql语句

  • 第二个被圈中的是:以列表形式显示最近发送到服务器上执行的语句的资源使用情况

  • 第三个被圈中的是:我们刚刚发送到服务器上执行的语句

SQL优化之慢查询和explain以及性能分析下面我们就来看看这条语句的一些信息: show profile for query 4;

这个4就是上面 show profiles中的Query_ID 我们的测试sql为4

  SQL优化之慢查询和explain以及性能分析

  见名知意,对于这条简单的sql而言,可见在刚开始建立连接时和最后返回数据时,所占的时间比列多很大

  所以,有连接池和只返回我们需要的数据即可这么些说法;

   合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。对需要优化的语句进行针对性的优化