为什么我们的查询会卡在 MySql 中的“Writing to net”状态？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/802534/
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
Why do our queries get stuck on the state "Writing to net" in MySql?
提问by Fredrik
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_messagebut haven't found anything... But still our query-log on our mysql server is full of Select * from tbl_messagequeries
我已经多次搜索了我们的应用程序，select * from tbl_message但没有找到任何东西......但是我们的 mysql 服务器上的查询日志仍然充满了Select * from tbl_message查询
回答by Emil H
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_renameto rename mysql_query (or whatever you're using) and intercept the queries.
如果您没有使用包装器，而是直接执行查询，您可以使用 runkit 扩展和函数runkit_function_rename来重命名 mysql_query（或您正在使用的任何东西）并拦截查询。
回答by VolkerK
There are (at least) two data retrieval modes for mysql. With the c api you either call mysql_store_result()or mysql_use_result().
mysql_store_result() returns when all result data is transferred from the MySQL server to your process' memory, i.e. no data has to be transferred for further calls to mysql_fetch_row().
mysql 有（至少）两种数据检索模式。使用 c api，您可以调用mysql_store_result()或mysql_use_result()。
当所有结果数据从 MySQL 服务器传输到进程的内存时，mysql_store_result() 返回，即不需要传输数据以进一步调用 mysql_fetch_row()。
However, by using mysql_use_result() each record has to be fetched individually if and whenmysql_fetch_row() is called. If your application does some computing that takes longer than the time period specified in net_write_timeoutbetween two calls to mysql_fetch_row() the MySQL server considers your connection to be timed out.
但是，通过使用 mysql_use_result()，当调用 mysql_fetch_row()时，必须单独获取每条记录。如果您的应用程序在两次调用 mysql_fetch_row() 之间执行的计算时间超过net_write_timeout 中指定的时间段，则 MySQL 服务器认为您的连接已超时。
回答by Artem Russakovskii
Temporarily enable the query log by putting
通过放置临时启用查询日志
log=
into your my.cnf file, restart mysql and watch the query log for those mystery queries (you don't have to give the log a name, it'll assume one from the host value).
进入你的 my.cnf 文件，重新启动 mysql 并观察那些神秘查询的查询日志（你不必给日志一个名字，它会假设一个来自主机值）。
