如何消除SQL中冗餘的DISTINCT操作 - PolarDB

當SQL查詢中包含DISTINCT運算元時，資料庫需要通過排序或雜湊操作移除重複行，這會消耗大量的CPU和記憶體資源，尤其是在處理大規模資料時。在許多情境下，查詢結果本身具有唯一性（例如，SELECT列表中包含主鍵），此時DISTINCT操作是多餘的。PolarDB MySQL版的最佳化器通過分析表結構和查詢條件中的函數依賴關係，識別並消除冗餘的DISTINCT操作。此功能通過減少不必要的計算，降低查詢的執行時間、CPU及記憶體使用量率。

工作原理

此最佳化的核心是利用資料庫的函數依賴（Functional Dependency）。函數依賴指表中一列或多列的值能夠唯一地決定另一列或多列的值。例如，表的主鍵（Primary Key）可以唯一確定該表中的任何其他列。

最佳化器分析帶有DISTINCT的查詢時，會根據表的元資訊（如主鍵、唯一索引）和查詢條件進行推導。如果最佳化器斷定投影列（SELECT列）中的列組合已能保證結果的唯一性，則會將DISTINCT從執行計畫中移除。

適用範圍

叢集版本：
- 產品系列：叢集版、標準版。
- 核心版本：MySQL 8.0.2，且修訂版本需為8.0.2.2.31.1及以上版本。
功能生效範圍：
- 投影列包含唯一鍵：當SELECT語句中的列包含表的唯一鍵（主鍵或組成唯一索引的全部列）時，返回結果將確保唯一性。
```
-- 假定 user_id 是表 users 的主鍵
SELECT DISTINCT user_id, user_name FROM users; -- DISTINCT 可被消除
```
- 投影列由唯一鍵函數決定：當SELECT語句中所有列能夠被某一唯一鍵唯一確定時，查詢結果也是唯一的。這種情況通常出現在單表查詢或與事實表進行一對一或多對一的關聯時。
```
-- 假定 user_id 是主鍵, (user_name, email) 由 user_id 唯一決定
SELECT DISTINCT user_name, email FROM users WHERE user_id = 123; -- DISTINCT 可被消除
```
- 投影列均為常量：當SELECT語句中的所有列均為常量時，結果集最多僅包含一行，此時使用DISTINCT是多餘的。最佳化器將會去除DISTINCT並添加LIMIT 1。
```
SELECT DISTINCT 'hello', 123 FROM t1 WHERE a = 1; -- DISTINCT 可被消除，並添加 LIMIT 1
```
- UNION DISTINCT查詢：在進行UNION DISTINCT查詢時，如果UNION中的每個SELECT語句自身的結果集已具備唯一性，則最佳化器能夠消除這些內部SELECT語句上的DISTINCT操作。
說明
最佳化器在推導過程中會嚴格考慮NOT NULL約束。如果唯一鍵的列允許為NULL，可能會影響唯一性的判斷，導致最佳化器不消除DISTINCT。

開啟DISTINCT消除功能

通過設定distinct_elimination_mode參數來控制此最佳化功能的行為。

PolarDB叢集參數在控制台與會話中修改方式存在差異，詳細區別如下：

在PolarDB控制台上修改：
- 相容性說明：部分叢集參數在PolarDB控制台上均已添加MySQL設定檔的相容性首碼loose_。
- 操作方法：找到並修改這些帶loose_首碼的參數。
在資料庫會話中修改（使用命令列或用戶端）：
- 操作方法：當您串連到資料庫，使用SET命令修改參數時，請去掉loose_首碼，直接使用參數的原始名稱進行修改。

參數名稱

層級

描述

loose_distinct_elimination_mode

Global/Session

控制該功能的主開關。取值範圍如下：

REPLICA_ON（預設值）：僅在唯讀（RO）節點上開啟此功能。
ON：開啟。
OFF：關閉。

最佳化情境樣本

要驗證DISTINCT消除功能是否生效，您可以使用EXPLAIN命令查看查詢的執行計畫。如果最佳化生效，執行計畫中將不再出現處理DISTINCT的運算元（如排序或雜湊去重）。以下為幾個典型的最佳化情境。

情境一：投影列為常量

當最佳化器識別到投影列為常量時，可消除DISTINCT並添加LIMIT 1。

準備資料：建立t1表並插入資料。

CREATE TABLE testdb.t1 (a INT, b INT);
INSERT INTO testdb.t1 VALUES (1, 10), (1, 20);

執行原始查詢：

EXPLAIN SELECT DISTINCT a + 1 FROM testdb.t1 WHERE a = 1;

驗證最佳化：在最佳化後的執行計畫中，查看warnings中的資訊，DISTINCT操作被消除。

SHOW warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select (`testdb`.`t1`.`a` + 1) AS `a + 1` from `testdb`.`t1` where (`testdb`.`t1`.`a` = 1) limit 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------+

情境二：基於函數依賴推導唯一性

當列a唯一決定列b，且b為非NULL的唯一鍵時，最佳化器可推匯出a也唯一，從而消除DISTINCT。

準備資料：建立t2表，其中b列由a列產生且具有唯一性。

CREATE TABLE t2 (
  a INT,
  b INT AS (a + 1) UNIQUE
);
INSERT INTO t2(a) VALUES (10), (20);

執行原始查詢：

EXPLAIN SELECT DISTINCT a FROM t2 WHERE b IS NOT NULL;

驗證最佳化：由於b的唯一性保證了a在結果中的唯一性，DISTINCT被移除。查看warnings中的資訊：

SHOW warnings;
+-------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t2`.`a` AS `a` from `testdb`.`t2` where (`testdb`.`t2`.`b` is not null) |
+-------+------+---------------------------------------------------------------------------------------------------------+

情境三：子查詢結果天然唯一

當子查詢dt因包含唯一鍵c而天然唯一時，即使經過JOIN操作，結果的唯一性也可能保持，從而消除外層的DISTINCT。

準備資料：建立t3表，其中c列由a和b產生且具有唯一性。

CREATE TABLE t3 (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT AS (a + b) UNIQUE
);
INSERT INTO t3(a, b) VALUES (1, 10), (2, 20);

執行原始查詢：

EXPLAIN SELECT DISTINCT a, b FROM (SELECT a, b, c FROM t3) dt, (SELECT a AS d FROM t3 limit 1) dt2 WHERE c IS NOT NULL;

驗證最佳化：在最佳化後的執行計畫中，查看warnings中的資訊，DISTINCT操作被消除。

SHOW warnings;                                                                                                 
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t3`.`a` AS `a`,`testdb`.`t3`.`b` AS `b` from `testdb`.`t3` where (`testdb`.`t3`.`c` is not null) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+

效能表現

開啟DISTINCT消除功能後，您可以在典型情境中獲得顯著的效能提升：

指標	效能提升幅度
執行時間	降低10%～60%。
記憶體使用量	降低20%～70%。
CPU佔用	減少因雜湊或排序操作帶來的CPU開銷。