MySQL慢查询日志正在记录更快的查询 [英] Mysql slow query log is logging faster queries

问题描述

我在数据库服务器上设置了mysql慢查询日志，并将长查询时间设置为5. 只需检查日志及其记录查询，只需几毫秒.有人知道为什么会这样吗?，这里有一些日志.

I've setup mysql slow query log on my database server and set the long query time to 5. Just checked the log and its logging queries that take only milliseconds. Anyone know why that would be?, here's some of the log.

上次查询并不是最优化的查询.它说它检查了450000行，因此我在日志中看到它不会感到惊讶.但是查询时间说只花了0.2秒.慢查询日志是否还有更多内容，而不仅仅是查询执行时间?

The last query isn't exactly the most optimised. It says it examined 450000 rows so I wouldn't be surprised seeing it in the log. However the query time said it only took 0.2secs. Is there more to the slow query log then just query execution time?

# Query_time: 0.000525  Lock_time: 0.000151 Rows_sent: 1  Rows_examined: 115
SET timestamp=1349393722;
SELECT `we_members`.*, `we_referrals`.`code` as referral_code
FROM (`we_members`)
LEFT JOIN `we_referrals` ON `we_referrals`.`m_id` = `we_members`.`id`
WHERE `we_members`.`facebook_id` = '100'
LIMIT 1;

# Query_time: 0.000748  Lock_time: 0.000104 Rows_sent: 3  Rows_examined: 691
SET timestamp=1349393722;
select distinct(m_id), m.first_name, m.facebook_id, m.photo_url from
            we_connections f
            left join we_members m on m.id = f.m_id
            where ( (f.friend_id = 75 or f.m_id = 75 and m.id != 75))
            and m.id >0
            and m.id != 75
            order by m_id;

# Query_time: 0.259535  Lock_time: 0.000098 Rows_sent: 16  Rows_examined: 455919
SET timestamp=1349393722;
select distinct(m_id), m.first_name, m.facebook_id, m.photo_url from
            we_connections f
            left join we_members m on m.id = f.m_id
            where (f.friend_id IN (select friend_id from we_connections f where f.m_id = 75) or (f.friend_id = 75 or f.m_id = 75 and m.id != 75))
            and m.id >0
            and m.id != 75
            order by m_id;

推荐答案

鉴于某些查询中要检查的行数众多，您可能还设置了log_queries_not_using_indexes选项-任何不使用索引也将写入慢速查询日志.您可以在my.cnf文件中检查此选项.

Given the large number of rows being examined in some of your queries, you probably have the log_queries_not_using_indexes option set as well - any queries that do not use an index will also be written to the slow query log. You can check for this option in your my.cnf file.

http://dev.mysql .com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

有关在此处写入日志的查询类型的更多信息， http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

More info on the types of queries written to the log here http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

这篇关于MySQL慢查询日志正在记录更快的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！