MySQL慢查询日志总结( 四 ) _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_indexes=1;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-format
The 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的帮助信息：
[root@DB-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 default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time