上一节讲到了建表思想,那如果想要通过优化表结构 来提高执行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环境支持