目录
3.2 设置参数 log_bin_trust_function_creators
实验条件：在本机上安装了虚拟机，安装了 centos7 和 mysql8
一、开启慢查询日志参数
1.1 开启 slow_query_log
默认情况下，慢查询日志是关闭的。可以使用如下命令打开（注意设置变量值的时候需要使用 global）：
mysql> set global slow_query_log='ON';
然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
你能看到这时慢查询分析已经开启，同时文件保存在
/var/lib/mysql/atguigu05
-
slow.log
文件中。
1.2 修改 long_query_time 阈值
接下来我们来看下慢查询的时间阈值设置，使用如下命令：
mysql> show variables like '%long_query_time%';
这里如果我们想把时间缩短，比如设置为
1
秒，需要做两方面设置：
- 全局设置（设置global的方式对当前session的long_query_time失效）
mysql> set global long_query_time = 1;
- 会话级设置
mysql> set long_query_time=1;
二、查看慢查询数目
查询当前系统中有多少条慢查询记录
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
当前系统中没有慢查询。
三、案例演示
3.1 建表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;3.2 设置参数 log_bin_trust_function_creators
创建函数，假如报错：
This function has none of DETERMINISTIC......命令开启：允许创建函数设置
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。3.3 创建函数
产生随机字符串：
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#测试
SELECT rand_string(10);产生随机数值：
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#测试：
SELECT rand_num(10,100);3.4 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;3.5 调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);四、测试及分析
4.1 测试
mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 3355654 | 3455655 | rdrxnY | 13 | 302 |
+---------+---------+--------+------+---------+
1 row in set (2.31 sec)
mysql> SELECT * FROM student WHERE name = 'rdrxnY';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 81629 | 181630 | rDRxNy | 19 | 80 |
| 190737 | 290738 | rDRxNy | 17 | 966 |
| 1507970 | 1607971 | RDrXNY | 62 | 949 |
| 1842741 | 1942742 | rDRxNy | 13 | 808 |
| 1858203 | 1958204 | RdRXny | 62 | 483 |
| 2405348 | 2505349 | rdrxnY | 15 | 399 |
| 2430609 | 2530610 | RdRXny | 58 | 274 |
| 2806416 | 2906417 | RdRXny | 57 | 235 |
| 3126683 | 3226684 | rDRxNy | 15 | 876 |
| 3355654 | 3455655 | rdrxnY | 13 | 302 |
| 3559533 | 3659534 | RDrXNY | 61 | 933 |
| 3612590 | 3712591 | rDRxNy | 13 | 788 |
+---------+---------+--------+------+---------+
12 rows in set (2.00 sec)
从上面的结果可以看出来，查询学生编号为
“3455655”
的学生信息花费时间为
2.31
秒。查询学生姓名为 “rdrxnY”的学生信息花费时间为2.00
秒。已经达到了秒的数量级，说明目前查询效率是比较低的。
4.2 分析
查询慢查询的数量：
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
1 row in set (0.02 sec)五、慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析
SQL
，显然是个体力活，
MySQL
提供了日志分析工具
mysqldumpslow
。
5.1 查看mysqldumpslow的帮助信息
[root@atguigu05 ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.se
mysqldumpslow
命令的具体参数如下：
- -a: 不将数字抽象成N，字符串抽象成S
- -s: 是表示按照何种方式排序：
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间 （默认方式）
- ac:平均查询次数
- -t: 即为返回前面多少条的数据；
- -g: 后边搭配一个正则匹配模式，大小写不敏感的；
5.2 示例
举例：我们想要按照查询时间排序，查看前五条
SQL
语句，这样写即可：
[root@atguigu05 ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu05?-slow.log
Reading mysql slow query log from /var/lib/mysql/atguigu05 -slow.log
Count: 1 Time=1059.37s (1059s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.44.1]
/* ApplicationName=DBeaver N.N.N - SQLEditor <Script-N.sql> */ CALL insert_stu1(N,N)
Count: 1 Time=2.31s (2s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
SELECT * FROM student WHERE stuno = N
Count: 1 Time=2.00s (1s) Lock=0.00s (0s) Rows=12.0 (12), root[root]@localhost
SELECT * FROM student WHERE name = 'S'
Count: 1 Time=1.11s (1s) Lock=0.14s (0s) Rows=1.0 (1), root[root]@[192.168.44.1]
/* ApplicationName=DBeaver N.N.N - SQLEditor <Script-N.sql> */ SELECT COUNT(N) from student s
LIMIT N, N
Died at /usr/bin/mysqldumpslow line 162, <> chunk 4.六、关闭慢查询日志
MySQL
服务器停止慢查询日志功能有两种方法：
6.1 永久性方式
[mysqld]
slow_query_log=OFF
或者，把
slow_query_log
一项注释掉 或 删除
[mysqld]
#slow_query_log =OFF
重启
MySQL
服务，执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长6.2 临时性方式
停止
MySQL
慢查询日志功能，具体
SQL
语句如下：
SET GLOBAL slow_query_log=off;
使用
SHOW
语句查询慢查询日志功能信息，具体
SQL
语句如下：
SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';
重启MySQL服务，慢查询的时间阈值重置为系统默认值（10秒）：
七、删除慢查询日志
慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日子文件即可。
使用命令 mysqladmin flushlogs 来重新生成查询日志文件，具体命令如下：
mysqladmin -uroot -p flush-logs slow提示：慢查询日志都是用 mysqladmin flushlogs 命令来删除重建的。一旦执行了此命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，旧必须事先备份。
扫一扫
5617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
