不使用 INDEX 的日志查询 - MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6428532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Log QUERIES not using INDEX - MySQL
提问by Sourav
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 Community Server (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 ?
我需要做什么改变？
回答by a1ex07
回答by Cyb10101
Maybe useful for Linux user. (Testet: Ubuntu 16.04)
也许对 Linux 用户有用。（测试：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);
回答by mikeq
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 节，
回答by RafaSashi
In addition to a1ex07's answer you can use the shell command mk-query-digestto output a reportof 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.
用于快速检测未索引的查询。
