MySQL-慢速发送数据阶段 [英] MySql - slow sending data phase
问题描述
我对MySQL 5.0.45的查询之一在发送数据"阶段运行缓慢.该查询是一个简单选择，返回约300个整数ID字段作为结果集.
One of my queries on MySQL 5.0.45 is running slow in "sending data" phase. The query is a simple select, returns about 300 integer ID fields as result set.
mysql> SELECT source_id FROM directions WHERE (destination_id = 10);
+-----------+
| source_id |
+-----------+
| 2 |
| 8 |
...
| 2563 |
+-----------+
341 rows in set (2.13 sec)
我注意到，发送数据"阶段为什么这么慢，可以做些什么来使其更快.请注意，我是在服务器本身的MySQL提示符下执行此查询的，因此并不是真正希望它在发送数据"上花费大量时间.有任何线索吗?
I am note sure why is "sending data" phase so slow and what can be done to make it fast. Please note I am executing this query on MySQL prompt on the server itself, so not really expecting it to spend so much time in "sending data". Any clues?
如果有帮助，我在此表上有3个文本字段，但是由于未选中它们，所以我希望它们不是这种缓慢的原因.
If it helps, I have 3 text fields on this table, but since they are not being selected, I am expecting they are not cause of this slowness.
该查询每天运行数千次，因此每次都花2秒钟的时间实在负担不起.
This query runs thousands of times a day and can't really afford to spend 2 secs on it each time.
分析结果:
mysql> show profile for query 4;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| (initialization) | 0.000003 |
| checking query cache for query | 0.000051 |
| checking permissions | 0.000007 |
| Opening tables | 0.000011 |
| System lock | 0.000005 |
| Table lock | 0.000023 |
| init | 0.00002 |
| optimizing | 0.00001 |
| statistics | 0.00006 |
| preparing | 0.000014 |
| executing | 0.000005 |
| Sending data | 2.127019 |
| end | 0.000015 |
| query end | 0.000004 |
| storing result in query cache | 0.000039 |
| freeing items | 0.000011 |
| closing tables | 0.000007 |
| logging slow query | 0.000047 |
+--------------------------------+----------+
18 rows in set (0.00 sec)
更新:我偶然发现了以下网址，
UPDATE: I stumbled upon the following URL which says
Each time means the time elapsed between the previous event and the new event. So, the line:
| Sending data | 0.00016800 |
means that 0.00016800 seconds elapsed between "executing" and "Sending data". It is, it takes 0.00016800 seconds to execute the query.
http://forums.mysql.com/read. php?24,241461,242012#msg-242012
有人可以验证吗?
推荐答案
解释计划通常是每当查询缓慢时最好的起点.要获得一个，运行
An explain-plan is usually the best place to start whenever you have a slow query. To get one, run
DESCRIBE SELECT source_id FROM directions WHERE (destination_id = 10);
这将为您显示一个表格，列出执行查询所需的步骤.如果您在行"列中看到一个较大的值，而在键"列中看到一个NULL，则表明您的查询必须扫描大量行以确定要返回的行.
This will show you a table listing the steps required to execute your query. If you see a large value in the 'rows' column and NULL in the 'key' column, that indicates that your query having to scan a large number of rows to determine which ones to return.
在那种情况下，在destination_id上添加索引应该极大地加快查询速度，但要花一些时间才能插入和删除(因为索引也需要更新).
In that case, adding an index on destination_id should dramatically speed your query, at some cost to insert and delete speed (since the index will also need to be updated).
这篇关于MySQL-慢速发送数据阶段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
