为 MYSQL 创建最近 30 天的视图

Question

我知道我写的查询是错误的，当我们获得大量流量时，我们的数据库会受到严重打击，页面速度会变慢...... 我想我需要根据 CURDATE 过去 30 天的 CREATE VIEW 编写查询？但不确定从哪里开始，或者这是否会是更有效的数据库查询？

无论如何，这是我编写的示例查询..

$query_Recordset6 = "SELECT `date`, title, category, url, comments 
                       FROM cute_news 
                      WHERE category LIKE '%45%' 
                   ORDER BY `date` DESC";

任何帮助或建议都会很棒！我有大约 11 个这样的查询，但我相信如果我能在其中一个查询上获得帮助，那么我就可以将它们实施到其余的查询中！

Answer 1

在值比较的左侧放置通配符：

LIKE '%xyz'

...意味着无法使用索引，即使索引存在。可能需要考虑使用全文搜索 (FTS)，这意味着添加全文索引。

标准化数据将是另一个需要考虑的步骤 - 类别可能应该位于单独的表中。

Answer 2

SELECT `date`, title, category, url, comments 
                       FROM cute_news 
                      WHERE category LIKE '%45%' 
                   ORDER BY `date` DESC

LIKE '%45%' 表示需要执行全表扫描。您是否可能在该列中存储类别列表？如果是这样，创建一个存储类别和 news_article_id 的新表将允许使用索引更有效地检索匹配记录。

Answer 3

好的，是时候进行心理调试了。

在我看来，我发现通过数据库规范化，查询性能将得到显着提高，特别是通过将类别多值列拆分为一个具有两列的单独表：cute_news 的主键和类别 ID。

这还允许您直接将所述表链接到类别表，而无需先解析它。

或者，正如 Chris Date 所说：“每个行和列的交集都包含来自适用域的一个值（而不是其他值）。”

Answer 4

任何带有 LIKE '%XXX%' 的东西都会很慢。这是一个缓慢的操作。

对于类别之类的内容，您可能希望将类别分离到另一个表中，并在cute_news 表中使用外键。这样你就可以拥有category_id，并在查询中使用它，这样会快得多。

---

另外，我不太清楚你为什么要谈论使用 CREATE VIEW。视图并不能真正帮助你提高速度。除非它是物化视图，而 MySQL 本身并不认为它是物化视图。

Answer 5

如果您的数据库受到严重打击，解决方案不是创建视图（视图仍然基本上与数据库要做的工作量相同），解决方案是缓存结果。

这一点特别适用，因为听起来您的数据只需每 30 天刷新一次。

Answer 6

我猜您的 category 列是一个类别值列表，例如“12,34,45,78”？

这不是好的关系数据库设计。正如您所发现的，它不好的原因之一是：搜索可能出现在该列表中间的子字符串非常慢。

有些人建议使用全文搜索而不是带有通配符的 LIKE 谓词，但在这种情况下，创建另一个表会更简单，这样您就可以每行列出一个类别值，并引用回您的 cute_news 表：

CREATE TABLE cute_news_category (
  news_id INT NOT NULL,
  category INT NOT NULL,
  PRIMARY KEY (news_id, category),
  FOREIGN KEY (news_id) REFERENCES cute_news(news_id)
) ENGINE=InnoDB;

然后你就可以查询了，速度会快很多：

SELECT n.`date`, n.title, c.category, n.url, n.comments 
FROM cute_news n
JOIN cute_news_category c ON (n.news_id = c.news_id)
WHERE c.category = 45 
ORDER BY n.`date` DESC

Answer 7

任何答案都是猜测，请显示：
- 相关的 SHOW CREATE TABLE 输出
- 常见查询的 EXPLAIN 输出。

比尔·卡尔文的评论当然适用。

毕竟这&优化，仍然需要将数据采样到仅包含最近 30 天的表中，在这种情况下，您最好运行每日 cronjob 来做到这一点。