sql - 使用OR语句驯服MySQL查询性能时出现问题

Question

[警告：前面有很长的柱子！]
我对这件事已经耿耿于怀好长一段时间了，但还是搞不清到底发生了什么事。我找到了一个解决办法，看最后，但我内心的禅还不满意。
我有一个带有论坛消息的主表（它来自Phorum），简体如下（暂时忽略anon_user_id，我稍后会讨论）：

CREATE TABLE `test_msg` (
  `message_id` int(10) unsigned NOT NULL auto_increment,
  `status` tinyint(4) NOT NULL default '2',
  `user_id` int(10) unsigned NOT NULL default '0',
  `datestamp` int(10) unsigned NOT NULL default '0',
  `anon_user_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`message_id`)
);

消息可以通过软件匿名，在这种情况下 user_id设置为 0。该软件还允许张贴完整的匿名消息，我们认可。在我们的例子中，我们仍然需要知道哪个用户发布了一条消息，因此通过Phorum提供的hook系统，我们有了第二个相应更新的表：

CREATE TABLE `test_anon` (
  `message_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  KEY `fk_user_id` (`user_id`),
  KEY `fk_message_id` (`message_id`)
);

对于配置文件中的视图，我需要获取来自用户的消息列表，无论这些消息是否已被用户异常化。
用户本身总是有权看到他匿名或稍后匿名写的消息。
因为 user_id被设置为 0如果匿名，我们不能简单地使用WHERE；我们需要加入自己的第二个表。将上面的语句表示为SQL，如下所示（需要 status = 2，其他状态意味着post是隐藏的，等待批准等）：

SELECT * FROM  test_msg AS m
LEFT JOIN test_anon ON test_anon.message_id = m.message_id
WHERE (test_anon.user_id = 20 OR m.user_id = 20)
AND m.status = 2
ORDER BY m.datestamp DESC
LIMIT 0,10

当查询缓存为空时，这个查询本身需要几秒钟，大约是当前的4秒钟。当多个用户发出查询并且查询缓存为空时，情况会变得更糟（这种情况正好发生；人们发布消息，缓存的查询无效）；我们在内部测试阶段面临的问题是，系统有时会变慢。由于并发性，我们看到查询需要30到60秒。我不想开始想象当我们扩展用户群时会发生什么。。。
现在看来我并没有对瓶颈问题进行任何分析。
我试着重写WHERE子句，添加indice并删除它们。
这是当我发现当我不使用任何索引时，查询在某些条件下执行快速点亮。如果不使用索引，查询看起来像：

SELECT * FROM  test_msg AS m USE INDEX()
LEFT JOIN test_anon ON test_anon.message_id = m.message_id
WHERE (test_anon.user_id = 20 OR m.user_id = 20)
AND m.status = 2
ORDER BY m.datestamp DESC
LIMIT 0,10

现在出现了一个特定的条件：限制将结果限制为10行。假设我的完整结果 n = 26。使用 LIMIT 0,10到 LIMIT 16,0只需0秒（大约<0.01s）：这些情况下，结果总是10行。
从 LIMIT 17,10开始，结果将只有9行。从这一点开始，查询将再次花费大约4秒的时间。这是适用于所有结果的结果集较小，然后限制最大行数通过 LIMIT。惹人生气的！
回到第一个CREATE TABLE语句，我也在没有左连接的情况下进行了测试；我们只是假设匿名消息的 user_id=0和 anon_user_id=<the previous user_id>完全绕过了第二个表：

SELECT * FROM test_msg
WHERE status = 2 AND (user_id = 20 OR anon_user_id = 20)
ORDER BY m.datestamp DESC
LIMIT 20,10

结果：没关系。性能仍在4或5秒内；强制不使用 USE INDEX()的索引不会加快此查询的速度。
我现在真的很困惑。索引将始终只用于 status列， OR防止使用其他索引，这也是MySQL文档在这方面告诉我的。
我尝试的另一种解决方案是：不要使用 test_anon表只与匿名消息相关，而只与所有消息相关。这允许我编写这样的查询：

SELECT * FROM test_msg AS m, test_anon AS t
WHERE m.message_id = t.message_id
AND t.user_id = 20
AND m.status = 2
ORDER BY m.datestamp DESC
LIMIT 20,10

这个查询总是给我即时的结果（=<0.01秒），无论什么限制等等。
是的，我找到了解决办法。不过，我还没有将整个应用程序重写到模型中。
但是我想更好地理解我观察到的行为背后的原因（特别是不强制索引加速查询）。纸面上看，原来的方法没有什么问题。
一些数字（反正也没那么大）：
约一百万条信息
消息表数据大小约为600MB
消息表索引大小约为350MB
所有邮件中 test_anon<3%的匿名邮件数
来自注册用户的邮件数<所有邮件的25%
所有的桌子都是MyISAM；我试过InnnoDB，但是性能更差。

Answer 1

实际上，这里有两个不同的查询，最好作为单独的查询处理。
要改进LIMIT，您需要使用LIMIT on LIMIT技术：

SELECT  *
FROM    (
        SELECT  *
        FROM    test_msg AS m
        WHERE   m.user_id = 20
                AND m.status = 2
        ORDER BY
                m.datestamp DESC
        LIMIT 20
        ) q1
UNION ALL
SELECT  *
        (
        SELECT  m.*
        FROM    test_msg m
        JOIN    test_anon a
        ON      a.message_id = m.message_id
        WHERE   a.user_id = 20
                AND m.user_id = 0
                AND m.status = 2
        ORDER BY
                m.datestamp DESC
        LIMIT 20
        ) q2
ORDER BY
        datestamp DESC
LIMIT 20

有关此解决方案的详细信息，请参阅我的博客中的此条目：
MySQL: LIMIT on LIMIT
您需要创建两个复合索引才能快速工作：

test_msg (status, user_id, datestamp)
test_msg (status, user_id, message_id, datestamp)

然后需要选择索引在第二个查询中的用途：排序或筛选。
在查询中，索引不能同时用于这两种情况，因为您正在对 message_id上的范围进行筛选。
有关更多说明，请参阅本文：
Choosing index
几句话：
如果有很多来自这个用户的匿名消息，也就是说，很有可能在索引开头的某个地方找到该消息，那么应该使用索引进行排序。使用第一个索引。
如果来自这个用户的匿名消息很少，也就是说，在索引的开头找到消息的可能性很低，那么应该使用索引进行筛选。使用第二个索引。
如果有可能重新设计表，只需向表中添加另一列 is_anonymous。
它能解决很多问题。