mySQL 具有排除优化

Question

我实际上已经成功地做到了这一点，但想知道是否有任何大师能够提供更优化的方法:

SELECT `cfc`.`card_face_id`
FROM `card_face_color` AS `cfc`
INNER JOIN `color` AS `c` ON c.color_id = cfc.color_id
WHERE c.c_url IN ('black', 'blue')
AND card_face_id NOT IN (
    SELECT `cfc`.`card_face_id`
    FROM `card_face_color` AS `cfc`
    INNER JOIN `color` AS `c` ON cfc.color_id = c.color_id
    WHERE c.c_url NOT IN ('black', 'blue')
)
GROUP BY `cfc`.`card_face_id`
HAVING (COUNT(DISTINCT c.c_url) = 2)

本质上，我试图选择包含黑色和蓝色的所有卡面，但没有其他颜色(每个卡面可能最多有 5 个)。尝试使用内部联接来完成此操作，但这几乎慢了 25 倍。我对我的索引非常满意，我只是对having 子句缺乏经验。

更新

针对查询运行EXPLAIN会揭示这一点(对格式表示歉意)。

id select_type table type  possible_keys key      key_len ref                   rows Extra
1  PRIMARY     c      index PRIMARY,c_url c_url    50      NULL                  10   Using where; Using index; Using temporary; Using filesort
1  PRIMARY     cfc    ref   color_id      color_id 1       site.co.uk.c.color_id 1156 Using where; Using index
2  SUBQUERY    c      range PRIMARY,c_url c_url    50      NULL                  9    Using where; Using index
2  SUBQUERY    cfc    ref   color_id      color_id 1       site.co.uk.c.color_id 1156 Using index

Answer 1

这是另一种方法。我首先尝试将结果集限制为只有两种颜色的卡面。让我知道性能是否更好。

SELECT cfc2.card_face_id, COUNT(*) AS cardcount2
FROM card_face_color cfc2 
  INNER JOIN color c ON cfc2.color_id=c.color_id AND c.c_url IN ('black','blue')
  INNER JOIN (SELECT cfc.card_face_id, COUNT(*) AS cardcount
              FROM card_face_color cfc
              GROUP BY cfc.card_face_id
              HAVING cardcount=2) AS color_counter ON cfc2.card_face_id=color_counter.card_face_id
GROUP BY cfc2.card_face_id
HAVING cardcount2=2                                                              

方法 2

另一种离奇的方法:

SELECT card_face_id, SUM(U.counter) AS counter FROM
(
SELECT cfc2.card_face_id, 1 AS counter
FROM card_face_color cfc2 
  INNER JOIN color c ON cfc2.color_id=c.color_id AND c.c_url IN ('black','blue')

UNION ALL

SELECT cfc2.card_face_id, 100 AS counter
FROM card_face_color cfc2 
  INNER JOIN color c ON cfc2.color_id=c.color_id AND NOT c.c_url IN ('black','blue')

) AS U
GROUP BY card_face_id
HAVING counter=2

方法 3我不确定你有多灵活，但在这种方法中我消除了内部联接:

SET @blue_id = (SELECT color_id FROM color WHERE c_url='blue');
SET @black_id = (SELECT color_id FROM color WHERE c_url='black');

SELECT card_face_id, SUM(U.counter) AS counter FROM
(
SELECT cfc2.card_face_id, 1 AS counter
FROM card_face_color cfc2 
WHERE cfc2.color_id IN (@blue_id,@black_id)

UNION ALL

SELECT cfc2.card_face_id, 100 AS counter
FROM card_face_color cfc2 
WHERE cfc2.color_id NOT IN (@blue_id,@black_id)

) AS U
GROUP BY card_face_id
HAVING counter=2