±--------±-----±------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | ‘@@log_slow_queries’ is deprecated and will be removed in a future release. Please use ‘@@slow_query_log’ instead |
±--------±-----±------------------------------------------------------------------------------------------------------------------+
–从下面的查询中可知,2个系统变量log_slow_queries,slow_query_log同时被置为on
root@localhost[(none)]> show variables like ‘%slow%’;
±--------------------±--------------------------------+
| Variable_name | Value |
±--------------------±--------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/suse11b-slow.log |
±--------------------±--------------------------------+
root@localhost[tempdb]> show variables like ‘%long_query_time%’;
±----------------±----------+
| Variable_name | Value |
±----------------±----------+
| long_query_time | 10.000000 |
±----------------±----------+
–为便于演示,我们将全局和session级别long_query_time设置为1
root@localhost[tempdb]> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
root@localhost[tempdb]> set session long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
–Author : Leshami
–Blog : http://blog.csdn.net/leshami
root@localhost[tempdb]> create table tb_slow as select * from information_schema.columns;
Query OK, 829 rows affected (0.10 sec)
Records: 829 Duplicates: 0 Warnings: 0
root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 829 rows affected (0.05 sec)
Records: 829 Duplicates: 0 Warnings: 0
…为便于演示,我们插入一些数据,中间重复过程省略
root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 26528 rows affected (4.40 sec)
Records: 26528 Duplicates: 0 Warnings: 0
root@localhost[tempdb]> system tail /var/lib/mysql/suse11b-slow.log
/usr/sbin/mysqld, Version: 5.5.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
Time: 141004 22:05:48
User@Host: root[root] @ localhost []
Query_time: 4.396858 Lock_time: 0.000140 Rows_sent: 0 Rows_examined: 53056
use tempdb;
SET timestamp=1412431548;
insert into tb_slow select * from tb_slow;
…再次插入一些记录…
root@localhost[tempdb]> insert into tb_slow select * from tb_slow;
Query OK, 212224 rows affected (37.51 sec)
Records: 212224 Duplicates: 0 Warnings: 0
root@localhost[tempdb]> select table_schema,table_name,count(*) from tb_slow
-> group by table_schema,table_name order by 3,2;
±-------------------±---------------------------------------------±---------+
| table_schema | table_name | count(*) |
±-------------------±---------------------------------------------±---------+
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | 1024 |
| performance_schema | cond_instances | 1024 |
…
| mysql | user | 21504 |
±-------------------±---------------------------------------------±---------+
83 rows in set (1.58 sec)
root@localhost[tempdb]> system tail /var/lib/mysql/suse11b-slow.log
User@Host: root[root] @ localhost []
Query_time: 37.514172 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 424448
SET timestamp=1412431806;
insert into tb_slow select * from tb_slow;
Time: 141004 22:10:47
User@Host: root[root] @ localhost []
Query_time: 1.573293 Lock_time: 0.000183 Rows_sent: 83 Rows_examined: 424614
SET timestamp=1412431847;
select table_schema,table_name,count(*) from tb_slow --这条SQL被记录下来了,其查询时间为1.573293s
group by table_schema,table_name order by 3,2;
root@localhost[tempdb]> show variables like ‘%log_queries_not_using_indexes’;
±------------------------------±------+
| Variable_name | Value |
±------------------------------±------+
| log_queries_not_using_indexes | OFF |
±------------------------------±------+
root@localhost[tempdb]> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
–查看表tb_slow索引信息,表tb_slow无任何索引
root@localhost[tempdb]> show index from tb_slow;
Empty set (0.00 sec)
root@localhost[tempdb]> select count(*) from tb_slow;
±---------+
| count(*) |
±---------+
| 424448 |
±---------+
1 row in set (0.20 sec)
root@localhost[tempdb]> system tail -n3 /var/lib/mysql/suse11b-slow.log
Query_time: 0.199840 Lock_time: 0.000152 Rows_sent: 1 Rows_examined: 424448
SET timestamp=1412432188;
select count(*) from tb_slow; --此次查询时间为0.199840,被记录的原因是因为没有走索引,因为表本身没有索引
4、格式化慢查询日志
结构化慢查询日志就是把慢查询日志中的重要信息按照便于阅读以及按照特定的排序方式来提取SQL。
这种方式有点类似于Oracle中有个tkprof来格式化oracle的trace文件。
对于前面的慢查询日志我们使用mysqldumpslow来提取如下:
suse11b:~ # mysqldumpslow -s at,al /var/lib/mysql/suse11b-slow.log
Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log
Count: 4 Time=16.87s (67s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into tb_slow select * from tb_slow
Count: 1 Time=0.20s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select count(*) from tb_slow
Count: 1 Time=1.57s (1s) Lock=0.00s (0s) Rows=83.0 (83), root[root]@localhost
select table_schema,table_name,count(*) from tb_slow
group by table_schema,table_name order by N,N
#以下是按照最大耗用时间排最后,只显示2条的方式格式化日志文件
suse11b:~ # mysqldumpslow -r -t 2 /var/lib/mysql/suse11b-slow.log
Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log
Count: 1 Time=1.57s (1s) Lock=0.00s (0s) Rows=83.0 (83), root[root]@localhost
select table_schema,table_name,count(*) from tb_slow
group by table_schema,table_name order by N,N
Count: 4 Time=16.87s (67s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into tb_slow select * from tb_slow
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数软件测试工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年软件测试全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上软件测试开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新
如果你觉得这些内容对你有帮助,可以添加V获取:vip1024b (备注软件测试)
一个人可以走的很快,但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎扫码加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
95%以上软件测试开发知识点,真正体系化!**
由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新
如果你觉得这些内容对你有帮助,可以添加V获取:vip1024b (备注软件测试)
[外链图片转存中…(img-Uo7Gs8eJ-1712978332519)]
一个人可以走的很快,但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎扫码加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!