我的系统在实际应用中当show processlist的时候看到大多时候都是Sending data!也就是我一直非常奇怪为什么会有这么多Sending data 滞留在我的系统中,真是往客户端发送查询结果不应该这么慢的!
文档上说:Sending data
The thread is processing rows for a SELECT
statement and is also sending data to the client.
我特别想要说明的是黄底部分的,虽然字面上看起来好像这个状态意味着在给客户端发送数据而已,实际上不完全是这样的,比如MySQL找到查询结果(30条记录,没有Cache到内存中,散布在硬盘上不同的地方)了,这样的话实际上要从这30个不同的地方取出这30条记录,也就是要寻道30次(特别散,Cache不到 :)),这个任务我觉得可以看成 The thread is processing rows for a SELECT
statement !
这个时候也许要想办法去增加内存,让MySQL不但将Index能Cache到内存中,才能够更多地将具体数据Cache到内存中了!
来自MySQL手册:
The following list describes thread State
values that are associated with general query processing and not more specialized activities such as replication. Many of these are useful only for finding bugs in the server.
-
Occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.
-
The thread is calculating a
MyISAM
table key distributions (for example, forANALYZE TABLE
). -
The thread is performing a table check operation.
-
The thread has processed one command and is preparing to free memory and reset certain state variables.
-
Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.
-
The thread is converting an internal temporary table from a
MEMORY
table to an on-diskMyISAM
table. <