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屋！