## 引言
眾所周知，內連線和外連線是資料庫中最常見的兩種表連線；其中外連線包括左外連線、右外連線及全外連線。圖1 展示了資料庫中不同的表連線的示意圖（其中深色部分代表最終的連線之後的結果），從圖中我們不難發現，內連線的連線順序是可以隨意交換的，但是外連線是不能的，連線順序的隨意交換可以讓資料庫的查詢最佳化器生成更多可選擇的計劃。

![](https://intranetproxy.alipay.com/skylark/lark/0/2022/png/225031/1660096342123-51cccfae-f652-48c8-ab63-a78b521a92d0.png#crop=0&crop=0&crop=1&crop=1&from=url&id=tAzyX&margin=%5Bobject%20Object%5D&originHeight=284&originWidth=1390&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
圖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 情形的，那麼這個時候外連線的結果就和內連線的結果是一樣的，就可以將外連線改寫為內連線。
![](https://intranetproxy.alipay.com/skylark/lark/0/2022/png/225031/1660096429219-00574e84-d58f-458a-901e-f32969584238.png#crop=0&crop=0&crop=1&crop=1&from=url&id=FYUIR&margin=%5Bobject%20Object%5D&originHeight=676&originWidth=1310&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
圖2 左外連線示意圖
針對上述查詢中的 probe 表存在可以過濾 null 的基表謂詞條件，最先想到的是否存在 column is not null 條件，從而能夠快速判斷當前的外連線是否可以改寫為內連線，圖3 描述了基於 column is not null 基表謂詞條件進行不同的外連線轉內連線的場景：
![](https://intranetproxy.alipay.com/skylark/lark/0/2022/png/225031/1660097314645-e7e71356-4506-4144-924b-ba1fc10fadec.png#crop=0&crop=0&crop=1&crop=1&from=url&id=kbdcU&margin=%5Bobject%20Object%5D&originHeight=932&originWidth=1090&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
圖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 ；
- 普通的比較運算表示式，比如“=”“!=”“>”“ 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 條件中找到對應外來鍵列的空值拒絕條件，從而可以進行外連線轉內連線的改寫。本文只是拋磚引玉，你可以深入思考，豐富更多的改寫場景及限制。
## 總結
本文主要介紹了一些外連線轉內連線的策略，外連線轉內連線對於資料庫查詢最佳化器而言是不可或缺的。內連線允許更多的連線順序選擇、基表謂詞條件下壓，有助於查詢最佳化器選擇到更好的查詢執行計劃，提升查詢效率。因此，外連線轉內連線是一項非常重要的最佳化技術。

SQL改寫系列九：外連線轉內連線的常見場景與錯誤-2

相關文章