mysql - 连接两个表后如何仅选择唯一的行

Question

我有两个表:

table 'A'
| id | name  |
| 1  | Larry |
| 2  | Maria |
| 3  | Ponyo |
| 4  | Panda |


table 'B'
| m_id | items |
| 1    |  7    |
| 2    |  9    |

我只想显示表“A”中不在表“B”上的记录。所以那就是

| 3  | Ponyo |
| 4  | Panda |

仅。

Answer 1

反连接模式通常是最有效的方法，尽管有多种方法可以获得相同的结果。

SELECT a.id
     , a.name
  FROM table_a a
  LEFT
  JOIN table_b b
    ON b.id = a.id
 WHERE b.id IS NULL

让我们稍微解开一下。

LEFT [OUTER] JOIN 操作获取 a 中的所有 行，以及 b 中的匹配行>。 “技巧”是过滤掉所有匹配的行；为此，我们在 WHERE 子句中使用谓词，检查 b 中的 NULL 值，我们知道如果找到匹配项，该值将不会为 NULL。

在这种情况下，如果我们找到匹配项，我们就知道 b.id 不为 null，因为如果 b.id 为 NULL，则 b.id = a.id 不会返回 TRUE。

反连接不会从 a 创建任何“重复”行(就像常规连接一样)。如果您需要消除 a 中已存在的“重复项”，则可以在选择列表之前添加 GROUP BY 子句或添加 DISTINCT 关键字。

<小时/>

还有其他方法，例如将 NOT EXISTS 谓词与相关子查询一起使用，或将 NOT IN 与子查询一起使用，但这些形式通常效率不高。

<小时/>

跟进

查询的实际性能将取决于几个因素；拥有合适的索引可能是最大的因素。谓词中涉及的列的可空性在执行计划中起着重要作用，基数、值的分布等、MySQL 版本和服务器的配置(例如 innodb 池大小)也有影响

作为测试用例:

SHOW VARIABLES LIKE 'version'
-- Variable_name  Value                        
-- -------------  -----------------------------
-- version        5.5.35-0ubuntu0.12.04.2-log  

CREATE TABLE `table_a` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name_` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

CREATE TABLE `table_b` (
  `a_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `item` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`a_id`,`item`)
) ENGINE=INNODB;

-- table_a  1,000,000 rows, id values   1 through 1000000
-- table_b    990,000 rows, a_id values 1 through 1000000 (less a_id MOD 100 = 0)

左连接位置

-- EXPLAIN
SELECT /*! SQL_NO_CACHE */ a.id
     , a.name_
  FROM table_a a
  LEFT
  JOIN table_b b
    ON b.a_id = a.id
 WHERE b.a_id IS NULL

不在

-- EXPLAIN 
SELECT /*! SQL_NO_CACHE */ a.id
     , a.name_
  FROM table_a a
 WHERE a.id NOT IN (SELECT b.a_id FROM table_b b)

不存在

-- EXPLAIN 
SELECT /*! SQL_NO_CACHE */ a.id
     , a.name_
  FROM table_a a
 WHERE NOT EXISTS 
       (SELECT 1
          FROM table_b b
         WHERE b.a_id = a.id)

性能结果(以秒为单位):

                  run 2  run 3  run 4  run 5  avg
                  -----  -----  -----  -----  -----
left-join-where   0.227  0.227  0.227  0.227  0.227 
not-in            0.233  0.233  0.234  0.233  0.233
not-exists        1.031  1.029  1.032  1.031  1.031

解释三个查询的输出:

left-join-where  
id  select_type        table type           possible_ key     key_len ref       rows Extra                                 
--  -----------        ----- -------------- --------- ------- ------- ------ ------- ------------------------------------
 1  SIMPLE             a     ALL                                             1000392                                       
 1  SIMPLE             b     ref            PRIMARY   PRIMARY 4       a.id         1 Using where; Using index; Not exists

not-in
id  select_type        table type           possible_ key     key_len ref       rows Extra        
--  ------------------ ----- -------------- --------- ------- ------- ------ ------- ------------------------------------
 1  PRIMARY            a     ALL                                             1000392 Using where  
 2  DEPENDENT SUBQUERY b     index_subquery PRIMARY   PRIMARY 4       func         1 Using index 

not-exists
id  select_type        table type           possible_ key     key_len ref       rows Extra        
--  ------------------ ----- ------         --------- ------- ------- ------ ------- ------------------------------------
 1  PRIMARY            a     ALL                                             1000392 Using where  
 2  DEPENDENT SUBQUERY b     ref            PRIMARY   PRIMARY 4       a.id         1 Using index