我设法整理了一个适合我需要的查询，尽管比我希望的要复杂。但是，对于表的大小，查询比它应该的慢(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

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)

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

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'

最佳答案

结合优化查询和 优化你的表，你将有快速的查询。但是，如果没有优化的表，就无法进行快速查询。
我再怎么强调都不为过:如果您的表的结构正确且索引数量正确，那么您不应该在像 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);

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 - 替代 COUNT for innodb 以防止表扫描？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/29443769/