MySql - 慢速发送数据阶段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/863447/
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
MySql - slow sending data phase
提问by
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 5.0.45 的查询之一在“发送数据”阶段运行缓慢。查询是一个简单的选择，返回大约 300 个整数 ID 字段作为结果集。
mysql> SELECT source_id FROM directions WHERE (destination_id = 10); +-----------+ | source_id | +-----------+ | 2 | | 8 | ... | 2563 | +-----------+ 341 rows in set (2.13 sec)
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?
我很清楚为什么“发送数据”阶段如此缓慢，以及可以做些什么来使其更快。请注意，我在服务器本身的 MySQL 提示符下执行此查询，因此并没有真正期望它在“发送数据”上花费这么多时间。有什么线索吗？
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.
如果有帮助，我在这个表上有 3 个文本字段，但由于它们没有被选中，我希望它们不是造成这种缓慢的原因。
This query runs thousands of times a day and can't really afford to spend 2 secs on it each time.
这个查询每天运行数千次，每次都花 2 秒的时间实在是承受不起。
Profiling result:
分析结果：
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
http://forums.mysql.com/read.php?24,241461,242012#msg-242012
Can somebody validate?
有人可以验证吗？
回答by Brad B
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);
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.
这将显示一个表格，其中列出了执行查询所需的步骤。如果您在“行”列中看到一个较大的值，而在“键”列中看到 NULL，则表明您的查询必须扫描大量行以确定要返回哪些行。
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).
在这种情况下，在 destination_id 上添加索引应该会大大加快您的查询速度，但插入和删除速度会有所增加（因为索引也需要更新）。
回答by Peter Duley
I had the same issue: Send Data was very slow, but I had the correct indexes etc.
我有同样的问题：发送数据很慢，但我有正确的索引等。
After much digging around, I found that my joinwas comparing two fields that were indexed, but had different Collation - one was latin1_swedish_ci and the other was uft8_general_ci.
经过大量挖掘，我发现我join正在比较两个已编入索引但具有不同排序规则的字段 - 一个是 latin1_swedish_ci，另一个是 uft8_general_ci。
Once I sent them both to utf8 the query was significantly faster (from 2.7 seconds to 0.002 seconds)
一旦我将它们都发送到 utf8 查询速度明显加快（从 2.7 秒到 0.002 秒）
回答by Ivan
You could probably look at hardware part of mysql server. As Mysql docsays:
您可能会查看 mysql 服务器的硬件部分。正如Mysql 文档所说：
Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
发送数据
该线程正在读取和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态。
So, if your server has slow disk I/O due to huge db/table file or disabled tableperfile InnoDB option/fragmentation/incorrectly configured RAID/disk crash process started (wait for soon disk death)/any other reason of disk I/O slowness - it could bereason for dramatically increased "Sending data" step as at this stage server gathering all requested data from disk and sends it to client.
因此，如果您的服务器由于巨大的 db/table 文件或禁用 tableperfile InnoDB 选项/碎片/错误配置的 RAID/磁盘崩溃过程启动（等待很快磁盘死亡）/任何其他磁盘 I/O 原因而导致磁盘 I/O 缓慢缓慢 - 这可能是显着增加“发送数据”步骤的原因，因为在此阶段服务器从磁盘收集所有请求的数据并将其发送到客户端。
Of course you should try to optimize select to use indexes first and make sure this is not programming issue as this affects this stage time in most cases.
当然，您应该首先尝试优化 select 以使用索引，并确保这不是编程问题，因为这在大多数情况下会影响此阶段时间。
回答by BQffen
I experienced this after moving from MySQL 5.5.x to 5.7.x. My query using joins was fast on MySQL 5.5 and really slow on MySQL 5.7.
从 MySQL 5.5.x 迁移到 5.7.x 后，我遇到了这种情况。我使用连接的查询在 MySQL 5.5 上很快，在 MySQL 5.7 上真的很慢。
The problem was that MySQL 5.7 chose another set of indexes than MySQL 5.5 did.
问题是 MySQL 5.7 选择了另一组索引而不是 MySQL 5.5。
Adding USE INDEX fixed the issue.
添加 USE INDEX 解决了该问题。
回答by Ehsan Chavoshi
I had two index (date_indexand id) ,
我有两个索引 (date_index和id) ，
i had WHERE date_index>NOW() - INTERVAL 24 HOURSand an ORDER BY idin query,
MySql preferred idas index and it didn't use date_index that caused long query time for big tables.
我有WHERE date_index>NOW() - INTERVAL 24 HOURS一个ORDER BY idin 查询，MySql 更喜欢id作为索引，它没有使用导致大表查询时间过长的 date_index。
i found it in a legacy system after 5 years that tables was grown.
5 年后，我在一个遗留系统中发现了它，该表增长了。
回答by Arnaud Bienvenu
Your query spends 2.127019 to executethe query. This is probably because you have a large amount of data, and your are missing an index on the destination_id column. Try :
您的查询花费 2.127019 来执行查询。这可能是因为您有大量数据，并且在 destination_id 列上缺少索引。尝试 ：
CREATE INDEX index_destination_id ON directions (destination_id);
CREATE INDEX index_destination_id ON 方向（destination_id）；
Then your request will run smoothly.
然后您的请求将顺利进行。
