选择“标记帖子”的最优化方式使用 where & mysql数据库的限制

Question

我有以下表结构：

+----------+------------------------+------+-----+------------------------------------------+----------------+
| Field    | Type                   | Null | Key | Default                                  | Extra          |
+----------+------------------------+------+-----+------------------------------------------+----------------+
| id       | int(10) unsigned       | NO   | PRI | NULL                                     | auto_increment |
| body     | varchar(200)           | NO   |     | Hey now!                                 |                |
| flags    | int(10) unsigned       | NO   |     | 0                                        |                |
| views    | int(10) unsigned       | NO   |     | 1                                        |                |
+----------+------------------------+------+-----+------------------------------------------+----------------+

并且我想选择“仅未标记的行超过其总视图的 5%，并且已被查看至少 5 次”。

这是我的查询：

SELECT id,body
FROM tablename
WHERE id NOT IN (
    SELECT id
    FROM tablename
    WHERE flags/views * 100 > 5.0
    AND views > 5
    ORDER BY id DESC
)
ORDER BY id DESC
LIMIT 6

我认为选择“已标记超过总视图 30% 的每一行”将会产生大量开销，尤其是当表增长到大量行时。有人可以帮我优化这个吗？

我还考虑创建一个“flag_score”列，并在每次标记某些内容时更新它，这样我就可以在 flag_score 列上进行选择，而不是在选择中进行数学运算（并节省额外的选择查询）。这听起来是个好方法吗？非常感谢。

编辑：我遇到的另一个问题是，如果我简单地执行以下操作：

SELECT *
FROM tabelname
WHERE flags/views * 100 > 5.0
AND views > 5
ORDER BY id DESC
LIMIT 5

...如果 5 个帖子中的 4 个已被标记，它只会返回 1 行！我希望该语句返回 5 行。

Answer 1

数学计算并不是很昂贵，所以只要你的条目少于，比如说，100 000 个条目，你就可以放心地这样做。

正如您自己所建议的，您始终可以缓存条件：

UPDATE tablename
SET is_over_30_percent = (flags/views * 100 > 5.0)
WHERE id='id_of_updated_entry'

它的优点是您可以在 *is_over_30_percent* 上放置索引，这样查询根本不会影响性能。

请记住在 *id_of_updated_entry* 和 date 和 id 上放置一个组合索引，以便索引可用于选择和排序（ORDER BY 的成本很高） ）。

Answer 2

我想说缓存是一种合理的方法，但这完全取决于系统中的读/写情况。如果人们一直在标记，那么每次都需要更新 flag_score 值，因此昂贵的操作将会频繁发生。

另外，我不确定您是否会获得性能提升，但您可以执行 flag/views > .05 来保存每个查询的乘法，尽管我不希望这部分那么昂贵。