I'm using MySQL Server version: 5.5.8-log MySQL Community Server (GPL)
I want to log queries which are not using INDEX and is slow too !
I'm copying here my my.ini settings.
我正在使用MySQL服务器版本:5.5.8-log MySQL社区服务器(GPL)我想记录不使用INDEX的查询,也很慢!我在这里复制我的my.ini设置。
[mysqld]
port=3306
log = "E:/wamp/logs/genquery.log"
[mysqld] port = 3306 log =“E:/wamp/logs/genquery.log”
log_slow_queries
long_query_time = 1
slow_query_log = 1
slow_query_log_file = "E:/wamp/logs/slowquery.log"
log_slow_queries long_query_time = 1 slow_query_log = 1 slow_query_log_file =“E:/wamp/logs/slowquery.log”
what change i need to do ?
我需要做些什么改变?
4 个解决方案
#1
28
log_queries_not_using_indexes =1 //(or Yes)
(From mysql)
log_queries_not_using_indexes = 1 //(或是)(来自mysql)
#2
6
log_queries_not_using_indexes
log_queries_not_using_indexes
Command-Line Format --log-queries-not-using-indexes
Option-File Format log-queries-not-using-indexes
Option Sets Variable Yes, log_queries_not_using_indexes
Variable Name log_queries_not_using_indexes
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type boolean
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.4,
是否将不使用索引的查询记录到慢查询日志中。见5.2.4节,
#3
3
Maybe useful for Linux user. (Testet: Ubuntu 16.04)
对Linux用户可能有用。 (Testet:Ubuntu 16.04)
Get root in terminal and edit mysql configuration
获取终端root并编辑mysql配置
su
vim /etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time=1
log_queries_not_using_indexes=1
Add log file and restart mysql server
添加日志文件并重启mysql服务器
touch /var/log/mysql/slow-query.log
chown mysql:adm /var/log/mysql/slow-query.log
chmod 640 slow-query.log
service mysql restart
Test slow logging with SQL queries
使用SQL查询测试慢速日志记录
/* Activate query log - Maybe useful to show errors (not necessary) */
SET GLOBAL SLOW_QUERY_LOG=ON;
/* Check if slow query log is working */
SELECT SLEEP(2);
#4
1
In addition to a1ex07's answer you can use the shell command mk-query-digest
to output a report of your running queries without using the log.
除了a1ex07的答案,您还可以使用shell命令mk-query-digest输出正在运行的查询的报告,而无需使用日志。
See the full methodology: http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/
请参阅完整的方法:http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/
As mentioned in the article it is also possible to group the queries by tables doing --group-by tables --report-format profile
正如文章中提到的,还可以通过表格--group-by tables --report-format profile对查询进行分组
Useful to fast detect unindexed queries.
用于快速检测未编制索引的查询。
#1
28
log_queries_not_using_indexes =1 //(or Yes)
(From mysql)
log_queries_not_using_indexes = 1 //(或是)(来自mysql)
#2
6
log_queries_not_using_indexes
log_queries_not_using_indexes
Command-Line Format --log-queries-not-using-indexes
Option-File Format log-queries-not-using-indexes
Option Sets Variable Yes, log_queries_not_using_indexes
Variable Name log_queries_not_using_indexes
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type boolean
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.4,
是否将不使用索引的查询记录到慢查询日志中。见5.2.4节,
#3
3
Maybe useful for Linux user. (Testet: Ubuntu 16.04)
对Linux用户可能有用。 (Testet:Ubuntu 16.04)
Get root in terminal and edit mysql configuration
获取终端root并编辑mysql配置
su
vim /etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time=1
log_queries_not_using_indexes=1
Add log file and restart mysql server
添加日志文件并重启mysql服务器
touch /var/log/mysql/slow-query.log
chown mysql:adm /var/log/mysql/slow-query.log
chmod 640 slow-query.log
service mysql restart
Test slow logging with SQL queries
使用SQL查询测试慢速日志记录
/* Activate query log - Maybe useful to show errors (not necessary) */
SET GLOBAL SLOW_QUERY_LOG=ON;
/* Check if slow query log is working */
SELECT SLEEP(2);
#4
1
In addition to a1ex07's answer you can use the shell command mk-query-digest
to output a report of your running queries without using the log.
除了a1ex07的答案,您还可以使用shell命令mk-query-digest输出正在运行的查询的报告,而无需使用日志。
See the full methodology: http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/
请参阅完整的方法:http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/
As mentioned in the article it is also possible to group the queries by tables doing --group-by tables --report-format profile
正如文章中提到的,还可以通过表格--group-by tables --report-format profile对查询进行分组
Useful to fast detect unindexed queries.
用于快速检测未编制索引的查询。