设计型网站自带优化,wordpress分页目录,室内装修设计资质,书写网站建设策划书慢查询日志概念MySQL的慢查询日志是MySQL提供的一种日志记录#xff0c;它用来记录在MySQL中响应时间超过阀值的语句#xff0c;具体指运行时间超过long_query_time值的SQL#xff0c;则会被记录到慢查询日志中。long_query_time的默认值为10#xff0c;意思是运行10S以上的…慢查询日志概念MySQL的慢查询日志是MySQL提供的一种日志记录它用来记录在MySQL中响应时间超过阀值的语句具体指运行时间超过long_query_time值的SQL则会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10S以上的语句。默认情况下Mysql数据库并不启动慢查询日志需要我们手动来设置这个参数当然如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件也支持将日志记录写入数据库表。官方文档关于慢查询的日志介绍如下(部分资料具体参考官方相关链接)The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed usinglog_slow_admin_statements and log_queries_not_using_indexes, as described later.慢查询日志相关参数MySQL 慢查询的相关参数解释slow_query_log 是否开启慢查询日志1表示开启0表示关闭。log-slow-queries 旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数系统则会默认给一个缺省的文件host_name-slow.logslow-query-log-file新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数系统则会默认给一个缺省的文件host_name-slow.loglong_query_time 慢查询阈值当查询时间多于设定的阈值时记录日志。log_queries_not_using_indexes未使用索引的查询也被记录到慢查询日志中(可选项)。log_output日志存储方式。log_outputFILE表示将日志存入文件默认值是FILE。log_outputTABLE表示将日志存入数据库这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式配置的时候以逗号隔开即可如log_outputFILE,TABLE。日志记录到系统的专用日志表中要比记录到文件耗费更多的系统资源因此对于需要启用慢查询日志又需要能够获得更高的系统性能那么建议优先记录到文件。慢查询日志配置默认情况下slow_query_log的值为OFF表示慢查询日志是禁用的可以通过设置slow_query_log的值来开启如下所示mysql show variables like %slow_query_log%;--------------------------------------------------------------------| Variable_name | Value |--------------------------------------------------------------------| slow_query_log | OFF || slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |--------------------------------------------------------------------2 rows in set (0.00 sec)mysql set global slow_query_log1;Query OK, 0 rows affected (0.09 sec)mysql show variables like %slow_query_log%;--------------------------------------------------------------------| Variable_name | Value |--------------------------------------------------------------------| slow_query_log | ON || slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |--------------------------------------------------------------------2 rows in set (0.00 sec)mysql使用set global slow_query_log1开启了慢查询日志只对当前数据库生效如果MySQL重启后则会失效。如果要永久生效就必须修改配置文件my.cnf(其它系统变量也是如此)。例如如下所示mysql show variables like slow_query%;--------------------------------------------------------------------| Variable_name | Value |--------------------------------------------------------------------| slow_query_log | OFF || slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |--------------------------------------------------------------------2 rows in set (0.01 sec)mysql修改my.cnf文件增加或修改参数slow_query_log 和slow_query_log_file后然后重启MySQL服务器如下所示slow_query_log 1slow_query_log_file/tmp/mysql_slow.logmysql show variables like slow_query%;------------------------------------------| Variable_name | Value |------------------------------------------| slow_query_log | ON || slow_query_log_file | /tmp/mysql_slow.log |------------------------------------------2 rows in set (0.00 sec)mysql关于慢查询的参数slow_query_log_file 它指定慢查询日志文件的存放路径系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)mysql show variables like slow_query_log_file;--------------------------------------------------------------------| Variable_name | Value |--------------------------------------------------------------------| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |--------------------------------------------------------------------1 row in set (0.00 sec)那么开启了慢查询日志后什么样的SQL才会记录到慢查询日志里面呢 这个是由参数long_query_time控制默认情况下long_query_time的值为10秒可以使用命令修改也可以在my.cnf参数里面修改。关于运行时间正好等于long_query_time的情况并不会被记录下来。也就是说在mysql源码里是判断大于long_query_time而非大于等于。从MySQL 5.1开始long_query_time开始以微秒记录SQL语句运行时间之前仅用秒为单位记录。如果记录到表里面只会记录整数部分不会记录微秒部分。mysql show variables like long_query_time%;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------1 row in set (0.00 sec)mysql set global long_query_time4;Query OK, 0 rows affected (0.00 sec)mysql show variables like long_query_time;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------1 row in set (0.00 sec)如上所示我修改了变量long_query_time但是查询变量long_query_time的值还是10难道没有修改到呢注意使用命令 set global long_query_time4修改后需要重新连接或新开一个会话才能看到修改值。你用show variables like long_query_time查看是当前会话的变量值你也可以不用重新连接会话而是用show global variables like long_query_time; 如下所示在MySQL里面执行下面SQL语句然后我们去检查对应的慢查询日志就会发现类似下面这样的信息。mysql select sleep(3);----------| sleep(3) |----------| 0 |----------1 row in set (3.00 sec)[rootDB-Server ~]# more /tmp/mysql_slow.log/usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition (Commercial)). started with:Tcp port: 0 Unix socket: (null)Time Id Command Argument/usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition (Commercial)). started with:Tcp port: 0 Unix socket: (null)Time Id Command Argument# Time: 160616 17:24:35# UserHost: root[root] localhost [] Id: 5# Query_time: 3.002615 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp1466069075;select sleep(3);log_output 参数是指定日志的存储方式。log_outputFILE表示将日志存入文件默认值是FILE。log_outputTABLE表示将日志存入数据库这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式配置的时候以逗号隔开即可如log_outputFILE,TABLE。日志记录到系统的专用日志表中要比记录到文件耗费更多的系统资源因此对于需要启用慢查询日志又需要能够获得更高的系统性能那么建议优先记录到文件。mysql show variables like %log_output%;----------------------| Variable_name | Value |----------------------| log_output | FILE |----------------------1 row in set (0.00 sec)mysql set global log_outputTABLE;Query OK, 0 rows affected (0.00 sec)mysql show variables like %log_output%;----------------------| Variable_name | Value |----------------------| log_output | TABLE |----------------------1 row in set (0.00 sec)mysql select sleep(5) ;----------| sleep(5) |----------| 0 |----------1 row in set (5.00 sec)mysqlmysql select * from mysql.slow_log;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------| 2016-06-16 17:37:53 | root[root] localhost [] | 00:00:03 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 | select sleep(3) | 5 || 2016-06-16 21:45:23 | root[root] localhost [] | 00:00:05 | 00:00:00 | 1 | 0 | | 0 | 0 | 1 | select sleep(5) | 2 |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------2 rows in set (0.00 sec)mysql系统变量log-queries-not-using-indexes未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话建议开启这个选项。另外开启了这个参数其实使用full index scan的sql也会被记录到慢查询日志。This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.mysql show variables like log_queries_not_using_indexes;--------------------------------------| Variable_name | Value |--------------------------------------| log_queries_not_using_indexes | OFF |--------------------------------------1 row in set (0.00 sec)mysql set global log_queries_not_using_indexes1;Query OK, 0 rows affected (0.00 sec)mysql show variables like log_queries_not_using_indexes;--------------------------------------| Variable_name | Value |--------------------------------------| log_queries_not_using_indexes | ON |--------------------------------------1 row in set (0.00 sec)mysql系统变量log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志mysql show variables like log_slow_admin_statements;----------------------------------| Variable_name | Value |----------------------------------| log_slow_admin_statements | OFF |----------------------------------1 row in set (0.00 sec)mysql系统变量log_slow_slave_statements 表示By default, a replication slave does not write replicated queries to the slow query log. To change this, use thelog_slow_slave_statements system variable.When the slow query log is enabled, this variable enables logging for queries that have taken more than long_query_time seconds to execute on the slave. This variable was added in MySQL 5.7.1. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements.参数--log-short-formatThe server writes less information to the slow query log if you use the --log-short-format option.Command-Line Format--log-short-formatPermitted ValuesTypebooleanDefaultFALSE另外如果你想查询有多少条慢查询记录可以使用系统变量。mysql show global status like %Slow_queries%;----------------------| Variable_name | Value |----------------------| Slow_queries | 2104 |----------------------1 row in set (0.00 sec)mysql日志分析工具mysqldumpslow在生产环境中如果要手工分析日志查找、分析SQL显然是个体力活MySQL提供了日志分析工具mysqldumpslow查看mysqldumpslow的帮助信息[rootDB-Server ~]# mysqldumpslow --helpUsage: 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 defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries-a dont 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 dont subtract lock time from total time-s, 是表示按照何种方式排序c: 访问计数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间-t, 是top n的意思即为返回前面多少条的数据-g, 后边可以写一个正则匹配模式大小写不敏感的比如得到返回记录集最多的10个SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log得到访问次数最多的10个SQLmysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log得到按照时间排序的前10条里面含有左连接的查询语句。mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现刷屏的情况。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more参考资料https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.htmlhttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_time
