MySQL“发送数据"太慢了 [英] MySQL "Sending data" horribly slow
问题描述
我有一个中等大小的表,目前有277k条记录,我正在尝试对其进行FULLTEXT
搜索.在进入发送数据"阶段之前,搜索似乎非常快.
I have a modest-sized table, 277k records at the moment, which I am trying to do a FULLTEXT
search on. The search seems to be very quick until it gets to the Sending data phase.
表格:
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
查询:
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 |
+-------------------------+----------+
DESCRIBE
看起来很不错,很简单:
描述:
The DESCRIBE
looks great, a simple one liner:
The 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
所以我不明白的是2.25秒的发送数据来自何处?我在Python和控制台mysql
应用程序中都看到了类似的性能,它们都连接到localhost
.
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 mysql
app, both connecting to localhost
.
更新:
- 每个请求平均行大小的评论为:53.8485
- 每个评论,这是上面的
DESCRIBE
.
推荐答案
DESCRIBE
看起来很棒,很简单.
The
DESCRIBE
looks great, a simple one liner.
由于您在查询中仅使用一个表,因此只能是一个表.
Since you are using only one table in your query it cannot be anything other than a one-liner.
但是,您的查询未使用FULLTEXT
索引.
However, your query does not use the FULLTEXT
index.
要使索引可用,您应该稍微重写一下查询:
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
MATCH
仅在与定义索引所在的确切列匹配时使用索引.
MATCH
only uses the index if the you match against the exact set of columns the index is defined on.
因此您的查询在DESCRIBE
的最后使用id
:Using index; Using where
上的索引扫描.
So your query uses the index scan on id
: Using index; Using where
at the very end of your DESCRIBE
.
Sending data
极具误导性:实际上,这是上一次操作结束到当前操作结束之间的时间.
Sending data
is quite misleading: this is actually time elapsed between the end of the previous operation and the end of the current operation.
例如,我只是运行了这个查询:
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
由于索引不可用,因此MySQL
需要执行全表扫描.
Since the index is not usable, MySQL
needs to perform the full table scan.
0.126565
秒是从执行开始(读取第一行的时间)到执行结束(最后一行发送到客户端的时间)的时间.
0.126565
seconds 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).
最后一行位于表的最后,花了很长时间才找到并发送它.
This last row is at the very end of the table and it took a long time to find and send it.
P. S.
编辑以删除下降投票:)
P. S.
Edited to remove the downvote :)
这篇关于MySQL“发送数据"太慢了的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!