非常慢的查询曾经非常快。解释显示本地备份上的 rows=1，但服务器上的 rows=2287359

Question

我时不时地检查垃圾邮件，然后使用“从像 '%http://%' order by id desc limit 10 的帖子中选择 *”并搜索其他一些关键字。最近，选择速度慢得令人难以置信。

mysql> explain select * from posts where reply like "%http://%" order by id desc limit 1;
+----+-------------+-----------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | posts     | index | NULL          | PRIMARY | 4       | NULL | 2287347 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

在我的上网本上，有 1 GB 内存，唯一的区别是它显示“ROWS”列为 1。我的上网本中只有 130 万个帖子。该服务器有大约 6 GB 内存和一个快速处理器。我应该优化什么，这样它才不会太慢。最近，我添加了一个索引来按 userId 进行搜索，我不确定这是否是一个明智的选择，但在这个问题开始发生之前，我将其添加到了备份和生产服务器中。我想这与由于错过调整而无法在内存中排序有关？

当我做“从 threadId=X 的帖子中删除”之类的事情时，它似乎也很慢，不知道是否相关。

Answer 1

关于

SELECT * FROM posts WHERE reply LIKE "%http://%" ORDER BY id DESC LIMIT 1

由于 http://，MySQL 将无法使用 reply 上的索引来快速找到您要查找的内容。此外，由于您要求的是具有最大id的结果，MySQL将必须提取所有结果以确保您拥有具有最大“id”的结果。

根据 posts 表中有多少数据由 reply 组成，可能值得在 (id,reply)< 上添加复合索引/code>，并将查询更改为类似

SELECT id FROM posts WHEREreply LIKE "%http://%" ORDER BY id DESC LIMIT 1

（这将仅执行索引），然后加入 posts 表或使用检索到的 id 检索帖子。如果查询仅执行索引，并且索引适合内存并且已经在内存中（由于正常使用或有意预热），您可能会加速启动查询执行。

话虽如此，如果具有相同数据的两个相同服务器上的相同查询给出了不同的执行计划和执行时间，那么可能是时候OPTIMIZE TABLE posts刷新索引统计信息和/或对表进行碎片整理了。如果您最近添加/删除了索引，事情可能会误入歧途。此外，如果数据是碎片化的，当它按主键顺序提取行时，它可能会跳过整个磁盘来检索数据。

对于DELETE FROM posts WHERE threadId=X，只要threadId上有索引就应该没问题。

Answer 2

如果您以“%”开始搜索比较，则不会使用索引。您的问题在于，

where reply like "%http://%"

正如您的解释所证实的那样，没有使用任何索引。速度差异可能是由于缓存造成的。

Answer 3

您的表上有什么类型的索引？一个好的经验法则是在 WHERE 子句中最常出现的列上建立索引。如果您的 threadId 列上没有索引，则您的最后一个查询将比有索引时慢很多。

您的第一个查询（从帖子中选择*，其中像“%http://%”这样的帖子会因为查询中的“like”而变慢。我建议使用另一个 WHERE 子句过滤您的查询 - 也许按日期（即希望被索引）：

select * from posts where postdate > 'SOMEDATE' and post like '%http://%'

Answer 4

您能否编写一个插入后触发器来检查文本以查找子字符串“http://”并标记当前记录或将其 id 写入 SPAM 表？正如@brent 所说，索引不用于“包含子字符串”搜索。