Slowlog中小于long_query_time时间设置的query
来源:互联网 发布:蚁群算法处理tsp问题 编辑:程序博客网 时间:2024/06/05 05:52
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
运行一个全表扫描的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官方文档):
5.2.5 The Slow Query Log
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:
Before 5.1.6, the slow query log destination is always a file. To enable the log, start mysqld with the
--log-slow-queries[=
option.file_name
]As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the
--log-slow-queries[=
option to enable the slow query log, and optionally usefile_name
]--log-output
to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).As of MySQL 5.1.12, as an alternative to
--log-slow-queries
, use--slow_query_log[={0|1}]
to specify the initial slow query log state. In this case, the default slow query log file name is used. With no argument or an argument of 1,--slow_query_log
enables the log. With an argument of 0, this option disables the log.As of MySQL 5.1.29, use
--slow_query_log[={0|1}]
to enable or disable the slow query log, and optionally--slow_query_log_file=
to specify a log file name. Thefile_name
--log-slow-queries
option is deprecated.
If the slow query log file is enabled but no name is specified, the default name is
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.host_name
-slow.log
To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log
and 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-output
option 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-format
option.
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 query must either not be an administrative statement, or
--log-slow-admin-statements
must have been specified.The query must have taken at least
long_query_time
seconds, or--log-queries-not-using-indexes
must have been specified and the query used no indexes for row lookups.The query must have examined at least
min_examined_row_limit
rows.
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”.
- Slowlog中小于long_query_time时间设置的query
- mybatis中小于号的用法
- MyBatis中小于等于号的处理
- 数组中小于nums[i]的个数
- 关于long_query_time的设置,可不可以说是mysql的一个小小bug呢
- MySQL 5.6+版本设置long_query_time的值无效的原因及解决方法
- 关于long_query_time的设置,可不可以说是mysql的一个小小bug呢
- mysql 设置 global long_query_time 不生效问题
- 为什么mysql的slowlog记录了查询时间很短的日志
- mybatis中小于,大于等的转义的总结
- (汇编)求数组中小于某个数的个数
- xml中小于号与大于号的处理转换
- 查找二叉搜索树中小于某个节点的最大值
- mybatis配置文件中小于大于号的处理
- XTML中小于号被视为标签的解决办法
- 找出堆中小于某个值X的所有节点
- mybatis配置文件中小于大于号的处理
- Linux删除目录中小于指定大小的文件
- UITableView 折叠效果 masksToBounds
- Java处理正则表达式特殊字符转义
- 【Git学习笔记】git fetch取回远程库的更新到本地仓库中
- Gem5学习01-Introduction
- 免费的STUN服务器列表
- Slowlog中小于long_query_time时间设置的query
- 从零开始学CSRF
- 时光老去,远了年少的我
- Oracle rownum原理
- MATLAB的M文件的学习和使用
- RFID系统安全概述
- li中的span右浮动问题
- 详解java定时任务
- 实习感受