mysql - 非常慢的查询曾经非常快。 Explain 在本地备份上显示 rows=1 但在服务器上显示 rows=2287359

Question

我时不时地检查垃圾邮件，然后使用“select * from posts where post like '%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 gig ram 上网本上，唯一的区别是它显示“ROWS”列为 1。我的上网本中只有 130 万个帖子。服务器有 6 gigs ram 和一个快速处理器。我应该优化什么，这样它才不会慢得可怕。最近我添加了一个按 userId 搜索的索引，我不确定这是一个明智的选择，但我在这个问题开始发生之前将它添加到备份和生产服务器中。我想这与由于错过调整而无法在 ram 中排序有关吗？

当我执行诸如“从 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)，并将查询更改为类似

的内容

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

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

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

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