关于数据库:SQL改写系列九外连接转内连接的常见场景与错误2

引言

家喻户晓，内连贯和外连贯是数据库中最常见的两种表连贯；其中外连贯包含左外连贯、右外连贯及全外连贯。图 1 展现了数据库中不同的表连贯的示意图（其中深色局部代表最终的连贯之后的后果），从图中咱们不难发现，内连贯的连贯程序是能够随便替换的，然而外连贯是不能的，连贯程序的随便替换能够让数据库的查问优化器生成更多可抉择的打算。

图 1 连贯示意图

内连贯相比拟于外连贯另外一个长处在于：内连贯是能够随便下压基表谓词条件的，然而外连贯是有限度的，比方以影院排片为例，影院应用 Q1 查问所有未排片和排片待定价格的电影进行排片和定价，Q1 中不能把 PLAY.price is null 这个谓词条件提前下压到 PLAY 表进行数据过滤；相同，如果影院应用 Q2 查问所有排片待定价格的电影进行定价，则能够提前把 PLAY.price is null 这个谓词条件下压到 PLAY 表进行数据过滤，这样能够缩小连贯的数据量，晋升查问效率。你能够先了解一下上述场景的区别，下文将开展介绍其背地的原理。

-- 影片表
MOVIE(movie_id，movie_name, release_date)
-- 排片表
PLAY(play_id, movie_id, time, price, seats)

-- 查问所有未排片和排片待定价格的电影
Q1: 
SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
FROM   MOVIE
       LEFT JOIN PLAY
              ON MOVIE.movie_id = PLAY.movie_id
       where PLAY.price is null; 

-- 查问所有排片待定价格的电影
Q2: 
SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
FROM   MOVIE
       INNER JOIN PLAY
              ON MOVIE.movie_id = PLAY.movie_id
       where PLAY.price is null; 

综上所述，内连贯相较于外连贯在数据库查问优化器中有着更大的优化空间（更多的连贯程序抉择，更广的基表谓词条件下压），因而在 OceanBase 中，依据不同的查问场景，设计和实现了一系列的外连贯转内连贯的策略。下文将次要介绍这些外连贯转内连贯的策略。

外连贯转内连贯的通用场景

在数据库的外连贯中，如果在 probe 表（外连贯的右表）中找不到满足连贯条件的数据，则须要在相应的连贯后果中针对 probe 表的投影列进行补 null 输入，图 2 左外连贯示意图展现了一个简略外连贯的情景。思考到外连贯这个行为，不难看出，如果在连贯之后，存在针对 probe 表的基表谓词条件能够过滤掉连贯后果中的 null 情景的，那么这个时候外连贯的后果就和内连贯的后果是一样的，就能够将外连贯改写为内连贯。

图 2 左外连贯示意图
针对上述查问中的 probe 表存在能够过滤 null 的基表谓词条件，最先想到的是否存在 column is not null 条件，从而可能疾速判断以后的外连贯是否能够改写为内连贯，图 3 形容了基于 column is not null 基表谓词条件进行不同的外连贯转内连贯的场景：

图 3 外连贯转内连贯示意图
上述场景形容了外连贯转内连贯的一个根本策略，即可能在 probe 表的相干基表谓词条件中找到可能过滤 null 的，其中 column is not null 是一种最简略直观的过滤 null 的基表谓词条件，其实还有更多的场景可能将外连贯转内连贯，比方上面这两个通用场景。

场景 1：WHERE 条件的外连贯转内连贯

在介绍基于 WHERE 条件的外连贯转内连贯之前，咱们先引入一个重要的概念——空值回绝条件，顾名思义就是回绝所有 null 的谓词条件，也就是上文所形容的在相干基表谓词条件中找到可能过滤 null 的条件。因而，基于 WHERE 条件的外连贯转内连贯的要害是是否找到 probe 表的空值回绝条件。
从上文能够晓得，column is not null 其实是最简略直观的一种空值回绝条件；除此之外，还有很多空值回绝条件，比方，Q3 是用于查问排片价格高于 30 元的电影，其中的 PLAY.price > 30 实质上就是一个空值回绝条件，因为当 PLAY.price 的值为 null 时，从 SQL 语义上来讲 null > 30 的比拟后果是空，也即查问后果中不会有 PLAY.price 为 null 的行，从而可能过滤掉所有因不满足连贯条件而对 probe 表的投影列补 null 的行。因而，Q3 等价于 Q4，也就是说如果将用户输出的查问 Q3 改写为 Q4，那么后续优化器的打算生成抉择等会有更大的优化空间。

Q3: 
SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
FROM   MOVIE
       LEFT JOIN PLAY
              ON MOVIE.movie_id = PLAY.movie_id
       where play.price > 30; 
       
Q4: 
SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
FROM   MOVIE
       INNER JOIN play
              ON MOVIE.movie_id = PLAY.movie_id
       where PLAY.price > 30; 

当然并不是所有的基表过滤条件都是空值回绝的，如 Q1 和 Q2，两者在语义上是不等价的，因为 column is null 不是一个空值回绝条件，所以在做基于 WHERE 条件的外连贯转内连贯改写时，须要特地留神空值回绝条件的笼罩场景，防止陷入改写陷阱。常见的空值回绝条件场景如下：

• 间接和 null 比拟相干的一些条件，比方 column is not null；
• 一般的比拟运算表达式，比方“=”“!=”“>”“<”“in”“not in”等；

• 匹配相干的表达式，比方“regexp”“like”等。

  场景 2：主外键的外连贯转内连贯

  在介绍利用主外键的束缚关系进行外连贯转内连贯之前，先简略介绍一下主外键束缚。如果你理解数据库，那你应该晓得，主键是用一组非 null 的惟一值来辨别表中的每一行，而外键用于建设两个表之间的分割，从表（外键所在表）的局部列依赖与主表的局部列，外键列只能援用主表中的列的值或 null 值，从而达到束缚两个表中数据的一致性和完整性的目标。
  那么外连贯如何利用主外键束缚来转换为内连贯呢？先看一个例子，如下创立了 MOVIE 和 PLAY 两张表，其中 PLAY.movie_id（非 null 列）外键依赖于 MOVIE.movie_id（主键列）。Q5 和 Q6 的差异是：从表在外连贯的地位不同，从表在 Q5 中是外连贯的右表（probe 表），而在 Q6 中是外连贯的左表（build 表）；你能够先自行思考一下，Q5 和 Q6 是否都能够将外连贯改写为内连贯？

  -- 影片表
  MOVIE(movie_id，movie_name, release_date, PRIMARY KEY(movie_id))
  -- 排片表
  PLAY(play_id, movie_id not null, time, price, seats,
     foreign key(movie_id) REFERENCES MOVIE(movie_id))
  
  Q5:
  SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
  FROM   MOVIE
       LEFT JOIN PLAY
              ON MOVIE.movie_id = PLAY.movie_id; 
                          
  Q6:
  SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
  FROM   PLAY
       LEFT JOIN MOVIE
              ON MOVIE.movie_id = PLAY.movie_id;

  OK，先颁布答案：Q5 不容许，Q6 容许！
  首先剖析 Q6 为什么能够，因为外连贯的等值连贯条件都来自于主外键列，同时从表 PLAY.movie_id 是一个非 null 列，阐明从表 PLAY.movie_id 的值都来自于主表 MOVIE.movie_id，因而当从表 PLAY 作为外连贯的左表、等值连贯条件都来自于主外键列时，阐明针对从表 PLAY.movie_id 总能在主表找到对应的列，不会呈现补 null 的场景，这就是 Q6 容许改写的起因；相同如果当主表 MOVIE 做为外连贯的左表时，并不能保障肯定能够在从表找到对应主表行的数据，是存在呈现 补 null 的场景的，因而 Q5 是不容许改写的。

  一个改写陷阱

  当初咱们分明了基于主外键的外连贯转内连贯改写的原理，其实基于主外键的改写限度是十分刻薄的，一不小心就会掉入改写的陷阱中，比方上面的 Q7 相较于 Q6 多了一个主外键列的非等值连贯条件，但 Q7 是不容许改写的。置信聪慧的你曾经识破其中起因，次要是因为新增的 MOVIE.movie_id > 100 谓词条件会毁坏连贯过程中的主外键束缚关系，当满足等值连贯条件然而不满足范畴谓词条件时，依然会补 null 输入，因而不能进行外连贯转内连贯的改写。这就揭示咱们在基于主外键的外连贯转内连贯改写时，要特地留神主外键束缚关系是否被突破。

  Q7:
  SELECT MOVIE.movie_name,
       PLAY.time,
       PLAY.price
  FROM   PLAY
       LEFT JOIN MOVIE
              ON MOVIE.movie_id = PLAY.movie_id
                 AND MOVIE.movie_id > 100;
  -- 不可产生改写

  小结

  此外，因为基于主外键的外连贯转内连贯条件刻薄，所以，在这里简略总结一下利用主外键束缚进行外连贯转内连贯的一些根本条件：

• 连贯条件仅仅只有主外键列的等值连贯；
• 等值连贯条件中应用到列是和主外键列是一一对应的；
• 连贯条件中的外键列有非 null 性质；
• 从表（外键所在表）为外连贯的左表。

除了上述根本条件外，其实针对非凡场景也会有一些限度，比方限定了主表的分区（应用 partition hint），因为存在补 null 输入的可能，不能改写。当然，也能够将上述根本条件进行推广，比方针对“连贯条件中的外键列有非空性质”这一条件，如果表中没有定义非空列，是不是也能够在 WHERE 条件中找到对应外键列的空值回绝条件，从而能够进行外连贯转内连贯的改写。本文只是抛砖引玉，你能够深刻思考，丰盛更多的改写场景及限度。

总结

本文次要介绍了一些外连贯转内连贯的策略，外连贯转内连贯对于数据库查问优化器而言是不可或缺的。内连贯容许更多的连贯程序抉择、基表谓词条件下压，有助于查问优化器抉择到更好的查问执行打算，晋升查问效率。因而，外连贯转内连贯是一项十分重要的优化技术。