慢查询日志：
MySQL慢查询日志记录下所有执行超过long_query_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。
慢查询日志的配置：
默认情况下，mysql没有启用慢查询日志。
[root@rh64 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners。
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%slow%';
+------------------------------------+------------------------------+ | Variable_name | Value | +------------------------------------+------------------------------+ | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_rate_limit | 1 | | log_slow_rate_type | session | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_verbosity | | | max_slowlog_files | 0 | | max_slowlog_size | 0 | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_always_write_time | 10.000000 | | slow_query_log_file | /var/lib/mysql/rh64-slow.log | | slow_query_log_timestamp_always | OFF | | slow_query_log_timestamp_precision | second | | slow_query_log_use_global_control | | +------------------------------------+------------------------------+ 16 rows in set (0.01 sec)
1、可以配置my.cnf文件，服务启动时自动配置
[root@rh64 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend
sql_mode=STRICT_TRANS_TABLES ,NO_ENGINE_SUBSTITUTION
slow_query_log=true
slow_query_log_file = "/var/lib/mysql/rh64-slow.log"
long_query_time=1
log-queries-not-using-indexes=true
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
explicit_defaults_for_timestamp=true
innodb_buffer_pool_size = 128M
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
重启server后，查看：
mysql> show variables like '%slow%';
+------------------------------------+------------------------------+ | Variable_name | Value | +------------------------------------+------------------------------+ | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_rate_limit | 1 | | log_slow_rate_type | session | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_verbosity | | | max_slowlog_files | 0 | | max_slowlog_size | 0 | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_always_write_time | 10.000000 | | slow_query_log_file | /var/lib/mysql/rh64-slow.log | | slow_query_log_timestamp_always | OFF | | slow_query_log_timestamp_precision | second | | slow_query_log_use_global_control | | +------------------------------------+------------------------------+ 16 rows in set (0.00 sec)
2、在系统中配置slow-query-log
mysql> set @@global.slow_query_log = on;
mysql> show variables like '%slow%';
+------------------------------------+------------------------------+ | Variable_name | Value | +------------------------------------+------------------------------+ | log_slow_admin_statements | OFF | | log_slow_filter | | | log_slow_rate_limit | 1 | | log_slow_rate_type | session | | log_slow_slave_statements | OFF | | log_slow_sp_statements | ON | | log_slow_verbosity | | | max_slowlog_files | 0 | | max_slowlog_size | 0 | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_always_write_time | 10.000000 | | slow_query_log_file | /var/lib/mysql/rh64-slow.log | | slow_query_log_timestamp_always | OFF | | slow_query_log_timestamp_precision | second | | slow_query_log_use_global_control | | +------------------------------------+------------------------------+ 16 rows in set (0.00 sec)
3、查看慢查询日志信息
[root@rh64 mysql]# tail rh64-slow.log
use prod; SET timestamp=1449476453; insert into emp1 select * from emp1; # Time: 151207 16:21:11 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 16.748949 Lock_time: 0.000137 Rows_sent: 0 Rows_examined: 1236992 Rows_affected: 618496 # Bytes_sent: 58 SET timestamp=1449476471; insert into emp1 select * from emp1; [root@rh64 mysql]# tail -f rh64-slow.log use prod; SET timestamp=1449476453; insert into emp1 select * from emp1; # Time: 151207 16:21:11 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 16.748949 Lock_time: 0.000137 Rows_sent: 0 Rows_examined: 1236992 Rows_affected: 618496 # Bytes_sent: 58 SET timestamp=1449476471; insert into emp1 select * from emp1; # Time: 151207 16:22:54 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 44.036039 Lock_time: 0.000083 Rows_sent: 0 Rows_examined: 2473984 Rows_affected: 1236992 # Bytes_sent: 59 SET timestamp=1449476574; insert into emp1 select * from emp1; # Time: 151207 16:26:46 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 106.674422 Lock_time: 0.000148 Rows_sent: 0 Rows_examined: 4947968 Rows_affected: 2473984 # Bytes_sent: 59 SET timestamp=1449476806; insert into emp1 select * from emp1;
记录没有使用索引的语句：
mysql> set @@global.log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%index%'; +----------------------------------------+-------+ | Variable_name | Value | +----------------------------------------+-------+ | eq_range_index_dive_limit | 10 | | expand_fast_index_creation | OFF | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_partitions | 1 | | innodb_cmp_per_index_enabled | OFF | | log_bin_index | | | log_queries_not_using_indexes | ON | | log_throttle_queries_not_using_indexes | 0 | | relay_log_index | | +----------------------------------------+-------+ 9 rows in set (0.00 sec)
测试：
mysql> select count(*) from emp1 where empno=7788;
+----------+
| count(*) |
+----------+
| 688128 |
+----------+
1 row in set (4.03 sec)
[root@rh64 mysql]# tail rh64-slow.log
SET timestamp=1449476453; insert into emp1 select * from emp1; # Time: 151207 16:21:11 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 16.748949 Lock_time: 0.000137 Rows_sent: 0 Rows_examined: 1236992 Rows_affected: 618496 # Bytes_sent: 58 SET timestamp=1449476471; insert into emp1 select * from emp1; # Time: 151207 16:22:54 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 44.036039 Lock_time: 0.000083 Rows_sent: 0 Rows_examined: 2473984 Rows_affected: 1236992 # Bytes_sent: 59 SET timestamp=1449476574; insert into emp1 select * from emp1; # Time: 151207 16:26:46 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 106.674422 Lock_time: 0.000148 Rows_sent: 0 Rows_examined: 4947968 Rows_affected: 2473984 # Bytes_sent: 59 SET timestamp=1449476806; insert into emp1 select * from emp1; # Time: 151207 16:30:44 # User@Host: root[root] @ localhost [] Id: 2 # Schema: prod Last_errno: 0 Killed: 0 # Query_time: 4.025612 Lock_time: 0.000098 Rows_sent: 1 Rows_examined: 4947968 Rows_affected: 0 # Bytes_sent: 68 SET timestamp=1449477044; select count(*) from emp1 where empno=7788;
4、通过mysqldumpslow工具查看慢查询日志
[root@rh64 mysql]# mysqldumpslow Can't determine basedir from 'my_print_defaults mysqld' output: --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --user=mysql --symbolic-links=0 --innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend --sql_mode=STRICT_TRANS_TABLES ,NO_ENGINE_SUBSTITUTION
[root@rh64 mysql]# 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.server startup script)
-l don't subtract lock time from total time
[root@rh64 mysql]# mysqldumpslow rh64-slow.log
Reading mysql slow query log from rh64-slow.log Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost # Schema: prod Last_errno: N Killed: N # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N # Bytes_sent: N SET timestamp=N; insert into emp1 select * from emp1 Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost # Schema: prod Last_errno: N Killed: N # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N # Bytes_sent: N use prod; SET timestamp=N; insert into emp1 select * from emp1 Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost # Schema: prod Last_errno: N Killed: N # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N # Bytes_sent: N SET timestamp=N; select count(*) from emp1 where empno=N
按照平均锁定时间进行排序，查找前10名：
[root@rh64 mysql]# mysqldumpslow -s al -n 10 rh64-slow.log
Reading mysql slow query log from rh64-slow.log
Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
SET timestamp=N;
insert into emp1 select * from emp1
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
use prod;
SET timestamp=N;
insert into emp1 select * from emp1
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
SET timestamp=N;
select count(*) from emp1 where empno=N
win10的事件ID 6013是什么？Jan 09, 2024 am 10:09 AMwin10的日志可以帮助用户详细的了解系统使用情况，很多的用户在寻找自己的管理日志的时候，肯定都遇到过日志6013吧，那么这个代码的意思是什么呢，下面就来介绍一下。win10日志6013是什么：1、这个是正常的日志。这个日志的信息并不是表示你的计算机重启了，而是说明自从上次启动以来，系统运行了多长的时间了。该日志会每天12点整出现一次。如何查看系统运行多长时间了，可以在cmd中输入systeminfo。其中有一行就是。
日志记录器缓冲区大小日志有什么用Mar 13, 2023 pm 04:27 PM作用是：给工程师们反馈使用信息与记录便于分析问题（开发时使用的）；由于用户本身不是经常产生上传日志，所以对用户无用。日志记录缓冲区是小型的、用于短期存储将写入到磁盘上的重做日志的变更向量的临时区域。日志缓冲区对磁盘的一次写入是来自多个事务的一批变更向量。即使如此，日志缓冲区中的变更向量也是接近实时地写入磁盘，当会话发出COMMIT语句时，会实时执行日志缓冲区写操作。
解决Win10中的事件7034错误日志问题Jan 11, 2024 pm 02:06 PMwin10的日志可以帮助用户详细的了解系统使用情况，很多的用户在寻找自己的管理日志的时候，肯定都看到过很多的错误日志吧，那么该怎么解决他们呢，下面就一起来看看吧。win10日志事件7034怎么解决：1、点击“开始”打开“控制面板”2、找到“管理工具”3、点击“服务”4、找到HDZBCommServiceForV2.0右击“停止服务”，并改为“手动启动”
如何在ThinkPHP6中使用日志Jun 20, 2023 am 08:37 AM随着互联网和Web应用的迅猛发展，日志管理越来越重要。在开发Web应用时，如何查找和定位问题是一个非常关键的问题。日志系统是一种非常有效的工具，可以帮助我们实现这些任务。ThinkPHP6提供了一个强大的日志系统，可以帮助应用程序开发人员更好地管理和跟踪应用程序中发生的事件。本文将介绍如何在ThinkPHP6中使用日志系统，以及如何利用日志系统
linux查看日志的三种命令Jan 04, 2023 pm 02:00 PMlinux查看日志的三种命令分别是：1、tail命令，该命令可以实时查看文件内容的变以及日志文件；2、multitail命令，该命令可以同时监视多个日志文件；3、less命令，该命令可以快速查看日志的更改，并且不会使屏幕混乱。
了解win10日志中事件ID455的含义Jan 12, 2024 pm 09:45 PMwin10的日志有着很多丰富的内容，很多的用户在寻找自己的管理日志的时候，肯定都见到过事件ID455显示错误，那么它到底是什么意思呢，下面就一起来看看。win10日志中事件ID455是什么：1、ID455是信息存储打开日志文件时<文件>发生的错误<错误>
如何在iPhone上的健康应用程序中查看您的用药日志历史记录Nov 29, 2023 pm 08:46 PMiPhone可让您在“健康”App中添加药物，以便跟踪和管理您每天服用的药物、维生素和补充剂。然后，您可以在设备上收到通知时记录已服用或跳过的药物。记录用药后，您可以查看您服用或跳过用药的频率，以帮助您跟踪自己的健康状况。在这篇文章中，我们将指导您在iPhone上的健康应用程序中查看所选药物的日志历史记录。如何在“健康”App中查看用药日志历史记录简短指南：前往“健康”App>浏览“>用药”>用药“>选择一种用药>”选项“&a
利用Java的日志框架监控系统运行情况Nov 06, 2023 am 11:41 AM在Java应用程序开发中，监控系统的运行情况是非常重要的。通过日志框架记录关键信息、异常和性能指标，可以及时捕获问题，进行故障排除，并优化系统性能。下面将介绍如何利用Java的日志框架监控系统运行情况，并提供一些实践技巧和经验。一、选择适合的日志框架1、常见日志框架：常见的Java日志框架包括Log4j、Logback和java.util.logging等。2、特点比较：不同的日志框架具有不同的特点。例如，Log4j具有灵活的配置和丰富的输出格式，Logback是Log4j的继任者并采用了更先进
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
뜨거운 도구
VSCode Windows 64비트 다운로드
Microsoft에서 출시한 강력한 무료 IDE 편집기
SublimeText3 Linux 새 버전
SublimeText3 Linux 최신 버전
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
에디트플러스 중국어 크랙 버전
작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
