为什么我们的查询会停留在“正在写入网络"状态中?在MySql中? [英] Why do our queries get stuck on the state "Writing to net" in MySql?

问题描述

我们有很多查询

select * from tbl_message

被卡在正在写入网络"状态中.该表有98k行.

that get stuck on the state "Writing to net". The table has 98k rows.

问题是...我们甚至没有从应用程序中执行任何查询，所以我想问题是:

The thing is... we aren't even executing any query like that from our application, so I guess the question is:

• 什么会生成查询?
• ...以及为什么它卡在状态写入网络"

我问这个问题很愚蠢，但是我99.99％的肯定我们的应用程序不会对数据库执行类似的查询...但是，我们正在使用WHERE语句对该表执行几个查询:

I feel stupid asking this question, but I'm 99,99% sure that our application is not executing a query like that to our database... we are however executing a couple of querys to that table using WHERE statement:

 SELECT Count(*) as StrCount FROM tbl_message WHERE m_to=1960412 AND m_restid=948

 SELECT Count(m_id) AS NrUnreadMail FROM tbl_message WHERE m_to=2019422 AND m_restid=440 AND m_read=1

 SELECT * FROM tbl_message WHERE m_to=2036390 AND m_restid=994 ORDER BY m_id DESC

我已经在应用程序中多次搜索了select * from tbl_message，但是没有找到任何内容...但是，我们在mysql服务器上的查询日志仍然充满了Select * from tbl_message个查询

I have searched our application several times for select * from tbl_message but haven't found anything... But still our query-log on our mysql server is full of Select * from tbl_message queries

推荐答案

由于应用程序无法根据自己的喜好生成查询，因此我认为很可能是应用程序中某处出现错误而导致了这种情况.这里有一些建议，您可以用来追踪它.我猜想您使用的是PHP，因为您使用的是MySQL，所以我将在示例中使用它.

Since applications don't magically generate queries as they like, I think that it's rather likely that there's a misstake somewhere in your application that's causing this. Here's a few suggestions that you can use to track it down. I'm guessing that your using PHP, since your using MySQL, so I'll use that for my examples.

尝试在应用程序中所有查询之前添加评论，例如:

Try adding comments in front of all your queries in the application, like this:

$sqlSelect  = "/* file.php, class::method() */";
$sqlSelect .= "SELECT * FROM foo ";
$sqlSelect .= "WHERE criteria";

该注释将显示在您的查询日志中.如果您使用某种数据库api包装器，则可能会自动添加以下消息:

The comment will show up in your query log. If you're using some kind database api wrapper, you could potentially add these messages automatically:

function query($sql)
{
    $backtrace = debug_backtrace();
    // The function that executed the query
    $prev = $backtrace[1];
    $newSql = sprintf("/* %s */ ", $prev["function"]);
    $newSql .= $sql;

    mysql_query($newSql) or handle_error();
}

如果您不使用包装器，而是直接执行查询，则可以使用runkit扩展和功能

In case you're not using a wrapper, but rather executing the queries directly, you could use the runkit extension and the function runkit_function_rename to rename mysql_query (or whatever you're using) and intercept the queries.

这篇关于为什么我们的查询会停留在“正在写入网络"状态中?在MySql中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！