MySQL“发送数据”非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1445597/
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 "Sending data" horribly slow
提问by Hyman M.
I have a modest-sized table, 277k records at the moment, which I am trying to do a FULLTEXTsearch on. The search seems to be very quick until it gets to the Sending data phase.
我有一个中等大小的表，目前有 277k 条记录，我正在尝试对其进行FULLTEXT搜索。在进入发送数据阶段之前，搜索似乎非常快。
The Table:
桌子：
CREATE TABLE `sqinquiries_inquiry` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ts` datetime NOT NULL,
`names` longtext NOT NULL,
`emails` longtext NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `sqinquiries_inquiry_search` (`names`,`emails`)
) ENGINE=MyISAM AUTO_INCREMENT=305560 DEFAULT CHARSET=latin1
The Query:
查询：
SELECT * FROM `sqinquiries_inquiry` WHERE (
MATCH (`sqinquiries_inquiry`.`names`) AGAINST ('smith' IN BOOLEAN MODE) OR
MATCH (`sqinquiries_inquiry`.`emails`) AGAINST ('smith' IN BOOLEAN MODE)
) ORDER BY `sqinquiries_inquiry`.`id` DESC LIMIT 100
The Profile:(I snipped out seemingly useless info)
个人资料：（我剪掉了看似无用的信息）
+-------------------------+----------+
| Status | Duration |
+-------------------------+----------+
| preparing | 0.000014 |
| FULLTEXT initialization | 0.000015 |
| executing | 0.000004 |
| Sorting result | 0.000008 |
| Sending data | 2.247934 |
| end | 0.000011 |
| query end | 0.000003 |
+-------------------------+----------+
The DESCRIBElooks great, a simple one liner:
The Describe:
在DESCRIBE看起来不错，一个简单的内衬：
该描述：
id: 1
select_type: SIMPLE
table: sqinquiries_inquiry
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 100
Extra: Using where
So what I don't understand is where the 2.25 seconds of Sending data is coming from? I'm seeing similar performance in Python and in the console mysqlapp, both connecting to localhost.
所以我不明白的是，发送数据的 2.25 秒是从哪里来的？我在 Python 和控制台mysql应用程序中看到类似的性能，两者都连接到localhost.
Updates:
更新：
- Per comment requesting the average row size, it is: 53.8485
- Per comment, here is the
DESCRIBEabove.
- 每个请求平均行大小的评论是：53.8485
- 根据评论，这是
DESCRIBE上面的。
回答by Quassnoi
The
DESCRIBElooks great, a simple one liner.
在
DESCRIBE看起来不错，简单的一行。
Since you are using only one table in your query it cannot be anything other than a one-liner.
由于您在查询中仅使用一张表，因此它只能是单行表。
However, your query does not use the FULLTEXTindex.
但是，您的查询不使用FULLTEXT索引。
For the index to be usable, you should rewrite the query a little:
为了使索引可用，您应该稍微重写一下查询：
SELECT *
FROM sqinquiries_inquiry
WHERE MATCH (names, emails) AGAINST ('smith' IN BOOLEAN MODE)
ORDER BY
id DESC
LIMIT 100
MATCHonly uses the index if the you match against the exact set of columns the index is defined on.
MATCH仅当您与定义索引的确切列集匹配时才使用索引。
So your query uses the index scan on id: Using index; Using whereat the very end of your DESCRIBE.
因此，您的查询使用索引扫描id：Using index; Using where在DESCRIBE.
Sending datais quite misleading: this is actually time elapsed between the end of the previous operation and the end of the current operation.
Sending data非常具有误导性：这实际上是前一次操作结束和当前操作结束之间经过的时间。
For instance, I just ran this query:
例如，我刚刚运行了这个查询：
SET profiling = 1;
SELECT *
FROM t_source
WHERE id + 1 = 999999;
SHOW PROFILE FOR QUERY 39;
which returned a single row and this profile:
它返回了一行和这个配置文件：
'starting', 0.000106
'Opening tables', 0.000017
'System lock', 0.000005
'Table lock', 0.000014
'init', 0.000033
'optimizing', 0.000009
'statistics', 0.000013
'preparing', 0.000010
'executing', 0.000003
'Sending data', 0.126565
'end', 0.000007
'query end', 0.000004
'freeing items', 0.000053
'logging slow query', 0.000002
'cleaning up', 0.000005
Since the index is not usable, MySQLneeds to perform the full table scan.
由于索引不可用，MySQL需要进行全表扫描。
0.126565seconds are the time from the beginning of the execution (the time the first row was read) and the end on the execution (the time the last row was sent to the client).
0.126565秒是从执行开始（读取第一行的时间）到执行结束（最后一行发送到客户端的时间）的时间。
This last row is at the very end of the table and it took a long time to find and send it.
最后一行位于表格的最末尾，需要很长时间才能找到并发送它。
P. S.Edited to remove the downvote :)
P. S.编辑删除downvote :)
回答by codymanix
I think you transfer lot of data an a slow network connection.
我认为您通过慢速网络连接传输大量数据。
Instead of select * do only select the columns which you really need.
而不是 select * 只选择你真正需要的列。
If your table contains large textfields which you want to show in the result you can use substring to only transfer the first few characters/words of the text.
如果您的表格包含要在结果中显示的大型文本字段，您可以使用子字符串仅传输文本的前几个字符/单词。
Some clients support compression of the result packets. Maybe yxou want to have a look on that.
一些客户端支持压缩结果包。也许yxou想看看那个。
