mysql - 6.慢查询

上一节讲到了建表思想，那如果想要通过优化表结构 来提高执行sql的效率 已经没办法做到了。这个时候就要从另外找一个角度着手：根据sql语句的应用场景，根据表内数据量的变化来进行优化。

Q: 现在找到前进的方向了，既然有些sql语句执行时间长，那就对这些sql进行优化么！ 可是我怎么样才能知道哪些sql语句操作时间比较长呢？           

A : 1.每执行一个sql 就去看一次执行时间？                                                           那效率太低下了。     

      2. 自己写一个程序，设置一个阈值，每次执行sql语句判断是否超出阈值？      成本有点高，假如代码没写好，误差不好控制

      3.其实mysql给我们已经提供了过滤那些查询时间长的语句的功能，哎，专业的软件考虑的东西是比较全面！

 

一.设置慢查询日志

   注：在mysql8之前 ，全局变量的设置是在内存中，一旦系统重启，会恢复成配置文件(my.ini)中的配置

第一步.检查我们的mysql是否设置了慢查询日志记录开关 ----在mysql执行窗口

show variables like 'slow_query_log%'              ---------------------------dataDir地址 查询方式： show variables like ‘datadir’

第二步：日志输出的方式: FILE 文件格式输出,slow_query_log_file 生效 ， TABLE慢查询日志保存到 database :mysql  ---table: slow_log 中数据内容还是一样的 （建议生产上不要用table）

show VARIABLES like '%log_output%' 

第三步. 查看/设置 慢查询的阈值

show variables like 'long_query_time'    ------------------------------ Mysql5.1之前记录单位是s ，多慢才算是慢查询呢？

可以通过set global long_query_time=0.01 来将阈值缩小，不过这种方式设置是设置在内存中，而且你要新打开一个session窗口才能看到生效的内容

二.分析慢查询日志

下面我将window下(unix一样)执行了一些慢sql的日志内容放出来：

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe, Version: 5.6.23-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 190413 10:49:23
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    33
# Query_time: 0.014773  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use bicycle;
SET timestamp=1555123763;
set GLOBAL slow_query_log_file='windows_slow_log';
# Time: 190413 10:49:51
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    38
# Query_time: 0.013982  Lock_time: 0.000000 Rows_sent: 1000  Rows_examined: 22000
SET timestamp=1555123791;
SELECT * FROM `user_buyer` LIMIT 21000, 1000;

》》》》》》分析例子》》》》》》》》》
# Time: 190413 10:49:56
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    37
# Query_time: 0.971524  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 924672
SET timestamp=1555123796;
SELECT COUNT(1) FROM `user`;

分析：

Time：190413 10:49:56	执行这条语句的时间 2019-04-13 10:49:56
User@Host: root[root] @ localhost [127.0.0.1]  Id:    37	用户 @ 数据库地址  id
Query_time: 0.971524  Lock_time: 0.000000 Rows_sent: 1    Rows_examined: 924672	查询花费时间  -- 锁表时间 -- 输出行数 -- 检查的行数
SET timestamp=1555123796;	设置当前的时间戳
SELECT COUNT(1) FROM `user`;	执行的sql语句

   1.我们借助mysql提供给我们的工具 mysqldumpslow.pl    (在mysql的bin目录下面有，需要先 安装Perl 环境)

简单介绍以下这个工具 ：  使用方式就一种-命令：

mysqldumpslow.pl -s 排序规则 -t  查询多少天数据  慢查询日志文件地址
排序规则	c ：总次数   t：总时间   l:锁的时间  r：总数据行数  at/al/ar   a(avg)表示平均的意思
查询多少天数据	指定取前面几天作为结果输出
慢查询日志文件地址	例： F:\windows_slow_log

2.在介绍一个强力的慢查询分析工具：pt_query_digst ，提供的分析更加详细和具体，包含了执行计划，也需要perl环境支持