高效的MySQL多对多标签查询

Question

我遇到了一点麻烦，无法找到一种有效的方法来根据它的标记在数据库中选择行，并返回与该行相关的所有其他标记。当我使用的查询没有返回该行的所有标记时，大约需要0.001秒。我最初的方案更加规范化，并为标签的标签使用了另一个表，但最终完成一次查询需要几秒钟的时间，所以我结束了删除该表，并使其不那么规范化，但即使是这种解决方案似乎也相当慢。

SELECT c.*
FROM collections c,
     tags t
WHERE t.collection_id=c.id
  AND (t.name IN ("foo",
                  "bar"))
GROUP BY c.id HAVING COUNT(t.id)=2 LIMIT 10

现在，我想不出一种有效的方法来获得该元素的所有其他标记，而不会变得很慢。我目前的解决方案大约慢10倍，需要0.01秒才能完成，而且我感觉它的伸缩性不好(我发现它相当难看)。

SELECT c.*,
       GROUP_CONCAT(t1.name) AS tags
FROM collections c,
     tags t,
     tags t1
WHERE t1.collection_id = c.id
  AND t.collection_id=c.id
  AND (t.name IN ("foo",
                  "bar"))
GROUP BY c.id HAVING COUNT(t.id)=2 LIMIT 10

实际上有没有一种有效的或者至少是更有效的方法来实现这一点呢？将非常感谢您在这方面的任何建议或提示！

Answer 1

好的。请考虑以下几点：

DROP TABLE IF EXISTS ingredients;

CREATE TABLE ingredients 
(ingredient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,ingredient VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO ingredients (ingredient_id, ingredient) VALUES
(1, 'Macaroni'),
(2, 'Cheese'),
(3, 'Beans'),
(4, 'Toast'),
(5, 'Jam'),
(6, 'Jacket Potato'),
(7, 'Peanut Butter');


DROP TABLE IF EXISTS recipes;

CREATE TABLE recipes 
(recipe_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,recipe VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO recipes (recipe_id, recipe) VALUES
(1, 'Macaroni & Cheese'),
(2, 'Cheese on Toast'),
(3, 'Beans on Toast'),
(4, 'Cheese & Beans on Toast'),
(5, 'Toast & Jam'),
(6, 'Beans & Macaroni'),
(9, 'Beans on Jacket Potato'),
(10, 'Cheese & Beans on Jacket Potato'),
(12, 'Peanut Butter on Toast');

DROP TABLE IF EXISTS recipe_ingredient;

CREATE TABLE recipe_ingredient 
(recipe_id INT NOT NULL
,ingredient_id INT NOT NULL
,PRIMARY KEY (recipe_id,ingredient_id)
);

INSERT INTO recipe_ingredient (recipe_id, ingredient_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 4),
(3, 3),
(3, 4),
(4, 2),
(4, 3),
(4, 4),
(5, 4),
(5, 5),
(6, 1),
(6, 3),
(9, 3),
(9, 6),
(10, 2),
(10, 3),
(10, 6),
(12, 4),
(12, 7);

SELECT r.*
      , GROUP_CONCAT(CASE WHEN i.ingredient IN ('Cheese','Beans') THEN i.ingredient END) i
      , GROUP_CONCAT(CASE WHEN i.ingredient NOT IN('Cheese','Beans') THEN i.ingredient END) o 
   FROM recipes r 
   LEFT 
   JOIN recipe_ingredient ri 
     ON ri.recipe_id = r.recipe_id 
   LEFT 
   JOIN ingredients i 
     ON i.ingredient_id = ri.ingredient_id 
  GROUP 
     BY recipe_id;

+-----------+---------------------------------+--------------+---------------------+
| recipe_id | recipe                          | i            | o                   |
+-----------+---------------------------------+--------------+---------------------+
|         1 | Macaroni & Cheese               | Cheese       | Macaroni            |
|         2 | Cheese on Toast                 | Cheese       | Toast               |
|         3 | Beans on Toast                  | Beans        | Toast               |
|         4 | Cheese & Beans on Toast         | Cheese,Beans | Toast               |
|         5 | Toast & Jam                     | NULL         | Toast,Jam           |
|         6 | Beans & Macaroni                | Beans        | Macaroni            |
|         9 | Beans on Jacket Potato          | Beans        | Jacket Potato       |
|        10 | Cheese & Beans on Jacket Potato | Cheese,Beans | Jacket Potato       |
|        12 | Peanut Butter on Toast          | NULL         | Toast,Peanut Butter |
+-----------+---------------------------------+--------------+---------------------+

相同的小提琴：http://www.sqlfiddle.com/#!2/45aa0/1

Answer 2

让它使用显式的连接语法(这不应该对性能造成影响，因为MySQL应该设法优化它)

SELECT c.*,
       GROUP_CONCAT(t1.name) AS tags
FROM collections c
INNER JOIN tags t ON t.collection_id = c.id
INNER JOIN tags t1 ON t1.collection_id = c.id
WHERE t.name IN ("foo", "bar")
GROUP BY c.id 
HAVING COUNT(t.id) = 2 
LIMIT 10

可能值得为每个正在检查的标记做一个单独的内连接，这样就不需要使用HAVING:-

SELECT c.*,
       GROUP_CONCAT(t1.name) AS tags
FROM collections c
INNER JOIN tags t ON t.collection_id = c.id AND t.name = "foo"
INNER JOIN tags t0 ON t.collection_id = c.id AND t0.name = "bar"
INNER JOIN tags t1 ON t1.collection_id = c.id
GROUP BY c.id 
LIMIT 10

但是，您的原始查询看起来并不差，因此可能是一个索引问题。