为什么我们的查询卡在MySql中的“写入网络”状态？

We have a lot of queries

我们有很多疑问

select * from tbl_message

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

陷入“写入网络”的状态。该表有98k行。

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

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

What might be generating the query?

什么可能生成查询?

...and why does it get stuck on the state "writing to net"

......为什么它会陷入“写入网络”的状态

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:

我觉得这个问题很愚蠢,但我99.99%肯定我们的应用程序没有像我们的数据库那样执行查询......但是我们使用WHERE语句对该表执行了几个查询:

 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

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

我已经多次搜索我们的应用程序select * from tbl_message但是没有找到任何东西......但是我们的mysql服务器上的查询日志仍然是来自tbl_message查询的Select *

3 个解决方案

#1

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.

由于应用程序不会像他们喜欢的那样神奇地生成查询,因此我认为应用程序中的某个地方很可能存在导致此问题的错误。以下是您可以用来跟踪它的一些建议。我猜你使用PHP,因为你使用MySQL,所以我将把它用于我的例子。

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";

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:

评论将显示在您的查询日志中。如果您正在使用某种类型的数据库api包装器,则可以自动添加这些消息:

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();
}

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.

如果你没有使用包装器,而是直接执行查询,你可以使用runkit扩展和函数runkit_function_rename来重命名mysql_query(或者你正在使用的任何东西)并拦截查询。

3 个解决方案

#1

更多相关文章

随机推荐