Slowlog中小于long_query_time時間設置的query

Mysql Version ：5.1.72

OS Version ：CentOS release 6.4 (Final)

說到慢查詢，有點陳詞濫調了，但是慢查詢相干的有個參數可能會被很多人疏忽。

log_queries_not_using_indexes

              這個參數設置當查詢沒有使用索引的情況下，是不是記錄到slowlog中。

摹擬情形：

當前long_query_time = 1 && log_queries_not_using_indexes = on

運行1個全表掃描的sql：

當履行當前sql的時候，查看slowlog會發現，雖然long_query_time=1，但是這條sql依然會被記錄在slowlog中

圖中看到query_time:0.001311秒

如果你使用的mysql版本大于5.6.5，也能夠通過設置

log_throttle_queries_not_using_indexes

                 這個參數表示每分鐘允許記錄到slowlog且未使用索引的次數。默許值為0，標識沒有限制。

相干文檔（摘取Mysql 5.1官方文檔）：

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined. The default value oflong_query_time is 10. Beginning with MySQL 5.1.21, the minimum is 0, and 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. Prior to MySQL 5.1.21, the minimum value is 1, and the value for this variable must be an integer.

By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using --log-slow-admin-statements and --log-queries-not-using-indexes, as described later.

The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.

Control the slow query log at server startup as follows:

If the slow query log file is enabled but no name is specified, the default name is host_name-slow.log and the server creates the file in the same directory where it creates the PID file. If a name is given, the server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_logand slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the slow query log is enabled, the server writes output to any destinations specified by the --log-outputoption or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the slow query log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

The server writes less information to the slow query log (and binary log) if you use the --log-short-formatoption.

To include slow administrative statements in the statements written to the slow query log, use the --log-slow-admin-statements server option. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

To include queries that do not use indexes for row lookups in the statements written to the slow query log, use the --log-queries-not-using-indexes server option. See Section 5.1.3, “Server Command Options”. When such queries are logged, the slow query log may grow quickly.

The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:

The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.

Prior to MySQL 5.1.45, replication slaves did not write replicated queries to the slow query log, even if the same queries were written to the slow query log on the master. (Bug #23300) In MySQL 5.1.45 and later, this behavior can be overridden using the --log-slow-slave-statements option.

The slow query log should be protected because logged statements might contain passwords. See Section 6.1.2.3, “Passwords and Logging”.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow ― Summarize Slow Query Log Files”.

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈