Mysql慢查询日志以及优化

小鱼儿 2022-05-01 12:02 282阅读 0赞

### 慢查询日志设置 ###

当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。

1、临时开启慢查询日志（如果需要长时间开启，则需要更改mysql配置文件）

set global slow\_query\_log = on;

注：如果想关闭慢查询日志，只需要执行 set global slow\_query\_log = off; 即可

2、临时设置慢查询时间临界点  查询时间高于这个临界点的都会被记录到慢查询日志中（如果需要长时间开启，则需要更改mysql配置文件）。

set long\_query\_time = 1;

现在起，所有执行时间超过1秒的sql都将被记录到慢查询文件中（我这里就是 /data/mysql/mysql-slow.log）。

3、设置慢查询存储的方式

set globle log\_output = file;

说明: 可以看到,我这里设置为了file,就是说我的慢查询日志是通过file体现的,默认是none,我们可以设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow\_log表中

4、查询慢查询日志的开启状态和慢查询日志储存的位置

show variables like '%quer%';

参数说明:

slow\_query\_log : 是否已经开启慢查询

slow\_query\_log\_file : 慢查询日志文件路径

long\_query\_time :  超过多少秒的查询就写入日志

log\_queries\_not\_using\_indexes 如果值设置为ON，则会记录所有没有利用索引的查询(性能优化时开启此项,平时不要开启)

5、使用慢查询日志示例

cat -n  /data/mysql/mysql-slow.log

从慢查询日志中，我们可以看到每一条查询时间高于1s钟的sql语句，并可以看到执行的时间是多少。

6、永久设置慢查询日志开启，以及设置慢查询日志时间临界点

linux中，mysql配置文件一般默认在 /etc/my.cnf

更改对应参数即可。

### 对慢查询日志进行分析 ###

我们通过查看慢查询日志可以发现，很乱，数据量大的时候，可能一天会产生几个G的日志，根本没有办法去清晰明了的分析。所以，这里，我们采用工具进行分析。

1、使用mysqldumpslow进行分析【第一种方式】

mysqldumpslow是mysql安装后就自带的工具，用于分析慢查询日志，下面是一个简单的例子：

mysqldumpslow -t 10  /data/mysql/mysql-slow.log  \#显示出慢查询日志中最慢的10条sql

2、使用pt-query-digest工具进行分析

pt-query-digest不是mysql自带的，如果想使用pt-query-digest进行慢查询日志的分析，则需要自己安装pt-query-digest。pt-query-digest工具相较于mysqldumpslow功能多一点。

（1）安装

yum install perl-DBI

yum install perl-DBD-MySQL

yum install perl-Time-HiRes

yum install perl-IO-Socket-SSL

wget percona.com/get/pt-query-digest

chmod u+x pt-query-digest

mv pt-query-digest  /usr/bin/

（2）查看具体参数作用

pt-query-digest --help

（3）使用

pt-query-digest  /data/mysql/mysql-slow.log

查询出来的结果分为三部分

第一部分：

显示出了日志的时间范围，以及总的sql数量和不同的sql数量。

第二部分：

显示出统计信息。

第三部分：

每一个sql具体的分析

pct是percent的简写，表示占的百分比

cout是占总sql个数的百分比，exec time 是占总执行时间的百分比，lock time 表示占总的锁表时间的百分比。

（4）如何通过pt-query-digest 慢查询日志发现有问题的sql

1）查询次数多且每次查询占用时间长的sql

通常为pt-query-digest分析的前几个查询

2）IO消耗大的sql

注意pt-query-digest分析中的Rows examine项

3）为命中索引的sql

注意pt-query-digest分析中Rows examine（扫描行数） 和 Rows sent （发送行数）的对比 ，如果扫描行数远远大于发送行数，则说明索引命中率并不高。

###     mysql优化 ###

1、使用explain查询sql的执行计划

explain select comic\_id,name,pen\_name,cover,last\_verify\_time from comic;

参数分析：

table：表示属于哪张数据表

type：最重要的参数，表示连接使用了何种类型。从最好到最差的连接类型为const，eq\_reg，ref，range，index和ALL。

possible\_keys：显示可能应用在这张表中的索引。如果为null，则表示没有可能的索引。

key：实际使用的索引。如果为null，则表示没有使用索引。

key\_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好。

ref：表示索引的哪一列被使用了，如果可能的话，是一个常数。

rows：Mysql认为必须检查的用来返回请求数据的行数。

2、count() 和 Max() 的优化方法

（1）优化前，是没有为last\_update\_time字段建立索引的情况，查询最大的时间戳

（2）优化后，是为last\_update\_time字段建立索引的情况，查询最大的时间戳

create index update\_time on comic(last\_update\_time)；

对比，可以看到，在没有为字段建立索引的情况下，查询时间是11秒多，建立索引之后，查询时间变成0秒了。

所以总结就是，如果经常用于count和max操作的字段，可以为其添加索引。

还有，值得注意的地方是：count() 计算时，count（\*）会将这一列中的null值但也算进去，而count（comic\_id）则不会将null算进去。

3、子查询的优化

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，如果有，是可能会出现重复数据的。所以如果存在一对多关系，则应该使用distinct进行限制。

例如：

select t.id from t where t.id in (select k.kid from k);

优化成：

select distinct t.id from t join k on t.id = k.kid;

4、group by 的优化

5、limit的优化

6、选择合适的列建立索引

7、选择合适的数据类型

8、数据库表的范式化优化

9、数据库表的反范式优化

10、数据库表的垂直拆分

11、数据库表的水平拆分

12、数据库系统配置优化

13、Mysql配置文件优化

14、第三方配置工具使用

15、服务器硬件优化  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2plcnJ5RHphbg_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2plcnJ5RHphbg_size_16_color_FFFFFF_t_70]: /images/20220119/2a46b263afbf484581ac46b9241911ed.png

Mysql慢查询日志以及优化

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL慢查询优化、日志收集定位排查、慢查询sql分析

相关 MySQL慢查询：慢SQL定位、日志分析与优化方案

相关 mysql慢日志查询

相关 mysql中查询缓存优化以及慢查询

相关 mysql 满查询管理平台_MySQL 之慢查询优化及慢日志管理

相关 mysql慢查询日志脚本_Mysql慢查询日志脚本

相关 mysql 慢日志flush_time_Mysql 慢查询和慢查询日志分析

相关 MySQL慢查询日志分析

相关 Mysql慢查询日志以及优化

相关 MySQL高级性能优化---慢日志查询