mysql - 替代 COUNT for innodb 以防止表扫描？

Question

我设法整理了一个适合我需要的查询，尽管比我希望的要复杂。但是，对于表的大小，查询比它应该的慢(0.17s)。原因，基于EXPLAIN下面提供，是因为在meta_relationships上有表扫描表，因为它有 COUNT在 WHERE条款 innodb发动机。

查询:

SELECT
posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
RIGHT JOIN meta_relationships ON (posts.post_id = meta_relationships.object_id)
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE meta.meta_name = computers AND meta_relationships.object_id 
NOT IN (SELECT meta_relationships.object_id FROM meta_relationships
        GROUP BY meta_relationships.object_id HAVING count(*) > 1)
GROUP BY meta_relationships.object_id

这个特定的查询，选择只有 computers 的帖子类别。 count > 1的目的是排除包含 computers/hardware 的帖子, computers/software等。选择的类别越多，计数就越高。

理想情况下，我想让它像这样运行:

WHERE meta.meta_name IN ('computers') AND meta_relationships.meta_order IN (0)

或

WHERE meta.meta_name IN ('computers','software') 
AND meta_relationships.meta_order IN (0,1)

等等..

但不幸的是这不起作用，因为它没有考虑到可能存在 meta_relationships.meta_order = 2。

我试过了...

WHERE meta.meta_name IN ('computers')
GROUP BY meta_relationships.meta_order
HAVING meta_relationships.meta_order IN (0) AND meta_relationships.meta_order NOT IN (1)

但它没有返回正确的行数。

说明:

id  select_type   table               type    possible_keys          key               key_len ref                                   rows   Extra   
1   PRIMARY       meta                ref     PRIMARY,idx_meta_name  idx_meta_name     602     const                                 1      Using where; Using index; Using temporary; Using filesort
1   PRIMARY       meta_data           ref     PRIMARY,idx_meta_id    idx_meta_id       8       database.meta.meta_id                 1  
1   PRIMARY       meta_relationships  ref     idx_meta_data_id       idx_meta_data_id  8       database.meta_data.meta_data_id       11     Using where
1   PRIMARY       posts               eq_ref  PRIMARY                PRIMARY           4       database.meta_relationships.object_id 1  
2   MATERIALIZED  meta_relationships  index   NULL                   idx_object_id     4       NULL                                  14679  Using index

表/索引:
元
此表包含类别和标签名称。
索引:
主键 ( meta_id ), 键 idx_meta_name ( meta_name )
元数据
此表包含有关类别和标签的附加数据，例如类型(类别或标签)、描述、父级、计数。
索引:
主键 ( meta_data_id ), 键 idx_meta_id ( meta_id )
元关系
这是一个结点/查找表。它包含posts_id 的外键、meta_data_id 的外键，还包含类别的顺序。
索引:
主键 ( relationship_id ), 键 idx_object_id ( object_id ), key idx_meta_data_id ( meta_data_id )

计数允许我只选择具有正确类别级别的帖子。例如，类别computers 有仅包含computers 类别的帖子，但也有包含computer/hardware 的帖子。计数过滤掉包含这些额外类别的帖子。我希望这是有道理的。

我相信优化查询的关键是完全摆脱 COUNT .

COUNT 的替代品可能会使用 meta_relationships.meta_order或 meta_data.parent相反。

meta_relationships表将快速增长，并且在当前大小(~15K 行)下，我希望在 100 秒而不是 10 秒内实现执行时间。

由于WHERE中需要有多个条件每个类别/标签的子句，任何针对动态查询优化的答案都是首选。

我用 sample data 创建了一个 IDE .

如何优化此查询？

编辑:

我永远无法找到解决此问题的最佳解决方案。这确实是 smcjones 对改进索引的建议的组合，我建议对其进行 EXPLAIN看着 EXPLAIN Output Format然后将索引更改为任何可以提供最佳性能的索引。
此外，hpf 建议添加另一列总计数也有很大帮助。最后，在更改索引后，我最终使用了这个查询。

SELECT posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE posts.meta_count = 2
GROUP BY posts.post_id
HAVING category = 'category,subcategory'

摆脱后 COUNT ，最大的性能杀手是 GROUP BY和 ORDER BY ，但索引是你最好的 friend 。我在做 GROUP BY 时了解到, WHERE条款很重要，越具体越好。

Answer 1

结合优化查询和 优化你的表，你将有快速的查询。但是，如果没有优化的表，就无法进行快速查询。
我再怎么强调都不为过:如果您的表的结构正确且索引数量正确，那么您不应该在像 GROUP BY... HAVING 这样的查询中遇到任何完整的表读取，除非您按照设计这样做。
根据您的示例，我创建了 this SQLFiddle .
将其与 SQLFiddle #2 进行比较，其中我添加了索引并添加了 UNIQUE指数对meta.meta_naame .
根据我的测试，Fiddle #2 更快。
优化您的查询
这个查询让我发疯，即使在我提出索引是优化它的最佳方法之后也是如此。尽管我仍然认为该表是提高性能的最大机会，但似乎必须有更好的方法在 MySQL 中运行此查询。我在解决这个问题后得到了启示，并使用了以下查询(见 in SQLFiddle #3):

SELECT posts.post_id,posts.post_name,posts.post_title,posts.post_description,posts.date,meta.meta_name
   FROM posts
   LEFT JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
   LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
   LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
   WHERE meta.meta_name = 'animals'
   GROUP BY meta_relationships.object_id
   HAVING sum(meta_relationships.object_id) = min(meta_relationships.object_id);

HAVING sum() = min()在 GROUP BY应该检查每种类型是否有多个记录。显然，每次记录出现时，它都会增加总和。 (编辑:在后续测试中，这似乎与 count(meta_relationships.object_id) = 1 具有相同的影响。哦，重点是我相信您可以删除子查询并获得相同的结果)。
我想明确一点，除非我提供给您的查询有任何优化，否则您不会注意到 WHERE meta.meta_name = 'animals' 部分。正在查询索引(最好是唯一索引，因为我怀疑您需要的不仅仅是这些索引，它会防止意外复制数据)。
所以，而不是看起来像这样的表:

CREATE TABLE meta_data (
  meta_data_id BIGINT,
  meta_id BIGINT,
  type VARCHAR(50),
  description VARCHAR(200),
  parent BIGINT,
  count BIGINT);

您应该确保像这样添加主键和索引:

CREATE TABLE meta_data (
  meta_data_id BIGINT,
  meta_id BIGINT,
  type VARCHAR(50),
  description VARCHAR(200),
  parent BIGINT,
  count BIGINT,
  PRIMARY KEY (meta_data_id,meta_id),
  INDEX ix_meta_id (meta_id)
);

不要做得太过分，但每个表都应该有一个主键，并且任何时候您对特定值进行聚合或查询时，都应该有索引。
当不使用索引时，MySQL 将遍历表的每一行，直到找到您想要的内容。在像您这样的有限示例中，这不会花费太长时间(即使它仍然明显变慢)，但是当您添加数千或更多记录时，这将变得非常痛苦。
将来，在查看查询时，请尝试确定全表扫描发生的位置，并查看该列是否有索引。无论您在哪里聚合或使用 WHERE，都是一个很好的起点。句法。
关于 count 的说明柱子
我还没找到放 count列到表中很有帮助。它可能会导致一些非常严重的完整性问题。如果一个表优化得当，应该很容易使用 count()并获取当前计数。如果你想把它放在表格中，你可以使用 VIEW ，虽然这不是最有效的拉动方式。
放置问题 count表中的列是您需要更新该计数，使用 TRIGGER或者，更糟糕的是，应用程序逻辑。随着您的程序向外扩展，该逻辑可能会丢失或被掩埋。添加该列是对规范化的一种偏差，当发生这样的事情时，应该有一个 非常好理由。
关于是否有充分的理由这样做存在一些争论，但我认为我明智地避开这场争论，因为双方都有很好的争论。相反，我会选择一个小得多的战斗，并说我认为在这个用例中这会让你头疼而不是好处，所以它可能值得 A/B 测试。