表連接消除

連接消除（Join Elimination）通過在不影響最終結果的情況下從查詢中刪除表，來簡化SQL以提高查詢性能。通常，當查詢包含主鍵-外鍵連接并且查詢中僅引用主表的主鍵列時，可以使用此優化。

考慮下面的例子，

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

訂單表（orders）和客戶表（customer）關聯，且c_custkey是客戶表的主鍵，那么客戶表可以被消除掉，重寫后的SQL如下：

select * from orders where o_custkey

獲取該優化的更詳細信息。

外連接轉化為內連接

外連接優化指的是滿足一定條件（外表具有NULL拒絕條件）的外連接可以轉化為內連接，從而可以讓數據庫優化器可以選擇更優的執行計劃，提升SQL查詢的性能。

考慮下面的例子，

select c_custkey from orders left join customer on c_custkey=o_custkey where C_NATIONKEY  < 20

C_NATIONKEY < 20是一個customer表上的NULL拒絕條件，所以上面的左外連接可以重寫為內連接，

select c_custkey from orders inner join customer on c_custkey=o_custkey where C_NATIONKEY  < 20

獲取該優化的更詳細信息。

SATTC重寫優化

SAT-TC(SATisfiability-Transitive Closure) 重寫優化是指分析一組相關的查詢條件，去發現是否有條件自相矛盾、簡化或是推斷出新的條件，從而幫助數據庫優化器選擇更好的執行計劃，提升SQL性能。

考慮下面的例子，

select c.c_name FROM customer c where c.c_name = 'John' and c.c_name = 'Jessey'

由于條件自相矛盾，所以重寫后的SQL為，

select c.c_name from customer as c where 1 = 0

獲取該優化的更詳細信息。

查詢折疊(Query Folding)

查詢折疊指的是把視圖、CTE或是DT子查詢展開，并與引用它的查詢語句合并，來減少序列化中間結果集，或是觸發更優的關于表連接規劃的優化技術。

考慮下面的例子，

SELECT * FROM (SELECT c_custkey, c_name FROM customer) AS derived_t1;

重寫后的SQL為，

SELECT c_custkey, c_name FROM customer

獲取該優化的更詳細信息。

投影下推(Projection Pushdown)

投影下推指的通過刪除DT子查詢中無意義的列（在外查詢中沒有使用），來減少IO和網絡的代價，同時提升優化器在進行表訪問的規劃時，采用無需回表的優化選項的幾率。

考慮下面的例子，

SELECT count(1) FROM (SELECT c_custkey, avg(age) FROM customer group by c_custkey) AS derived_t1;

重寫后的SQL為，

SELECT count(1) FROM (SELECT 1 FROM customer group by c_custkey) AS derived_t1;

獲取該優化的更詳細信息。

IN可空子查詢重寫

對于以下想要查詢沒有訂單用戶的SQL，

select * from customer where c_custkey not in (select o_custkey from orders)

如果子查詢的結果集里有空值，這個SQL永遠返回為空。正確的寫法應該是在子查詢里加上非空限制，即

select * from customer where c_custkey not in (select o_custkey from orders where o_custkey is not null)

獲取該優化的更詳細信息。

HAVING條件下推到WHERE

從邏輯上，HAVING條件是在分組之后執行的，而WHERE子句上的條件可以在表訪問的時候（索引訪問）,或是表訪問之后、分組之前執行，這兩種條件都比在分組之后執行代價要小。

考慮下面的例子，

select c_custkey, count(*) from customer group by c_custkey having c_custkey < 100

重寫后的SQL為，

select c_custkey, count(*) from customer where c_custkey < 100 group by c_custkey

ALL修飾的子查詢重寫優化

假設通過下面的SQL來獲取訂單系統關閉后注冊的用戶

select * from customer where c_regdate > all(select o_orderdate from orders)

如果子查詢的結果中存在NULL，這個SQL永遠返回為空。正確的寫法應該是在子查詢里加上非空限制，或使用max/min的寫法

select * from customer where c_regdate > (select max(o_custkey) from orders)

PawSQL推薦采用第二種寫法，可以通過max/min重寫進一步優化SQL，獲取該優化的更詳細信息。

MAX/MIN子查詢重寫優化

對于使用MAX/MIN的子查詢，

select * from customer where c_custkey = (select max(o_custkey) from orders)

可以重寫為以下的形式，從而利用索引的有序來避免一次聚集運算，

select * from customer where c_custkey = (select o_custkey from orders order by o_custkey desc null last limit 1)

獲取該優化的更詳細信息。

COUNT標量子查詢重寫優化

對于下面子查詢，

select * from customer where (select count(*) from orders where c_custkey=o_custkey) > 0

可以重寫為, 避免了一次聚集運算

select * from customer where exists(select 1 from orders where c_custkey=o_custkey)

獲取該優化的更詳細信息。

避免使用=NULL判斷空值

=null或是case when null并不能判斷表達式為空, 判斷表達式為空應該使用is null。在SQL中出現=null或是case when null大概率是開發人員的錯誤寫法，因為它們總是被判斷為假，和1=0是等價的。所以PawSQL會檢查此類寫法，并進行提醒和重寫。

譬如如下的SQL，

select case c_phone when null then 0 else 1 end from customer;

PawSQL會將其重寫為

select case when c_phone is null then 0 else 1 end from customer;

獲取該優化的更詳細信息。

避免在查詢中使用SELECT *

在查詢中使用SELECT *的缺點如下：

• SELECT * 中如果包含無用的大字段，尤其是 text /CLOB類型的字段，容易造成無謂磁盤IO和網絡開銷。
• 使用SELECT *容易增加代碼維護的成本，譬如增減字段容易與 resultMap 配置不一致、insert into select *時字段映射出錯。
• 數據庫優化器無法進行覆蓋索引的規劃。
• PawSQL索引推薦無法推薦覆蓋索引。

獲取該優化的更詳細信息。

避免使用隨機函數排序

MySQL的函數rand或PostgreSQL的函數random會返回一個在范圍0到1.0之間的隨機浮點數。我們有時候會使用以下查詢語句獲取數據集的隨機樣本。

select * from orders order by rand() limit 1;

如果customer表少于10,000行，則此方法效果很好。但是當您有1,000,000行時，排序的開銷變得不可接受。原因很明顯：我們將所有行排序，但只保留其中的一行。其實有更高效的方法來實現此需求，點擊獲取該優化的更詳細信息。

盡量使用UNION ALL代替UNION

使用UNION來得到兩個結果集的并集時，會對并集的結果集進行去重，去重操作在數據庫內部是通過排序或是哈希的方式實現，這兩種方式都會需要大量的計算資源。如果邏輯上可以保證兩個結果集沒有重復數據，可以使用UNION ALL來代替UNION，可以獲得較大的性能提升。

限制子查詢的嵌套層次

子查詢的嵌套會讓SQL變得復雜，而太復雜的SQL會讓數據庫的優化器生成執行計劃的時間比較長，且容易生成性能較差的執行計劃，所以PawSQL檢測子查詢嵌套的層次是否超過某個閾值，并提醒用戶可能的風險。在PawSQL中，閾值的默認值是2，用戶可以在創建優化任務時修改此閾值。

限制查詢中表連接的個數

在執行計劃的規劃中，表連接的順序和連接的方法是數據庫優化器最重要的規劃內容。表連接數目的增加將幾何級數地增加數據庫優化器對于最優執行計劃的搜尋空間，導致生成執行計劃的時間比較長，且容易生成性能較差的執行計劃。所以PawSQL檢測查詢中表連接得數目是否超過某個閾值，并提醒用戶可能的風險。在PawSQL中，閾值的默認值是5，用戶可以在創建優化任務時修改此閾值。

類型轉換導致索引失效

當條件表達式的數據類型不同時，在查詢執行過程中會進行一些隱式的數據類型轉換。類型轉換有時會應用于條件中的常量，有時會應用于條件中的列。當在列上應用類型轉換時，在查詢執行期間無法使用索引，可能導致嚴重的性能問題。譬如對于以下的SQL，

select count(*) from ORDERS where O_ORDERDATE = current_date();

如果O_ORDERDATE列的數據類型是CHAR(16)，那么O_ORDERDATE上的索引將不會被使用，導致全表掃描。解決方案通常有兩個，一是ALTER TABLE改變O_ORDERDATE的數據類型，二是把current_date強制換換為CHAR類型（PawSQL提供該重寫建議）。

select count(*) ORDERS where ORDERS.O_ORDERDATE = cast(current_date() as CHAR(16));

獲取該優化的更詳細信息。

避免連接字段類型不匹配

當條件表達式的數據類型不同時，在查詢執行過程中會進行一些隱式的數據類型轉換。當在列上應用類型轉換時，在查詢執行期間無法使用索引，可能導致嚴重的性能問題。PawSQL會檢查類型不匹配的連接條件，并進行提醒。

獲取該優化的更詳細信息。

避免在SELECT語句添加FOR UPDATE

SELECT語句添加FOR UPDATE會導致鎖表或鎖數據行，影響查詢的并發性，導致阻塞和整體性能下降，需謹慎使用。所以PawSQL會檢查此類寫法，并進行提醒。

避免在UPDATE語句中使用LIMIT

在UPDATE語句中使用LIMIT會導致不可預測更新的數據，需謹慎使用。所以PawSQL會檢查此類寫法，并進行提醒。

避免在UPDELETE語句中使用LIMIT而沒有ORDER BY

在UPDATE或DELETE語句中使用LIMIT而沒有ORDER BY,會導致每次執行的結果不一致。PawSQL會檢查此類寫法，并進行提醒。

PostgreSQL/Opengauss不支持在UPDATE或DELETE語句中使用ORDER BY子句。

避免在SELECT語句中使用LIMIT而沒有ORDER BY

在SELECT語句中使用LIMIT而沒有ORDER BY,會導致每次執行的結果不一致。PawSQL會檢查此類寫法，并進行提醒。

避免無條件且無分組的SELECT語句

沒有查詢條件或查詢條件恒真的查詢語句，且無分組語法，會導致全表掃描以及結果集巨大。PawSQL會檢查此類寫法，并進行提醒。

避免無條件的UPDELETE語句

沒有查詢條件或查詢條件恒真的UPDATE或DELETE語句，會更新或刪除所有數據記錄，是非常危險的操作。PawSQL會檢查此類寫法，并進行提醒。

INSERT語句中值的數量不要超過閾值

批量插入值可以有效的提升數據插入的效率，如下例，

insert into customer(c_custkey, lastname, firstName)
values(1, 'Dan', 'Mike'),(2, 'Chaw', 'Tomas'),(3, 'Wang', 'Nancy');

但是如果插入的數據量太多，超過數據庫的限制（MySQL: max_allowed_packet），導致數據庫端報錯。在PawSQL中，會檢查此類寫法，并對超過閾值（默認為500）的SQL提示預警。

避免INSERT不指定列名

INSERT語句應該指定列名，它可以減少插入的值與目標表的列之間出現錯位的可能性。

insert into customer value(1, 'Dan', 'Mike');

下面的寫法可以減少插入的值與目標表的列之間出現錯位的可能性，代碼更容易維護。

insert into customer(c_custkey, lastname, firstName) value(1, 'Dan', 'Mike');

OFFSET的值超過閾值

在SQL查詢中，LIMIT子句用于限制查詢結果的數量，而OFFSET子句用于指定從查詢結果集中的哪一行開始返回數據。當OFFSET值很大時，查詢引擎必須掃描越來越多的數據，以找到偏移量之后的數據行。在數據集很大的情況下，可能會導致查詢變得非常慢，并且可能會占用大量的系統資源。

避免%開頭的LIKE查詢

在SQL查詢中，LIKE操作符用于匹配字符串。如果模式字符串以%開頭（例如LIKE '%ABC'），則數據庫優化器無法利用索引來過濾數據，容易造成全表掃描。在沒有其他過濾條件的情況下，可能會對查詢性能和效率產生較大的影響。所以應該盡量避免%開頭的查詢條件，如果不得不使用%開頭的匹配，可以考慮創建全文索引來提升查詢性能。

OR條件的SELECT重寫為UNION

如果使用OR條件的查詢語句，數據庫優化器有可能無法使用索引來完成查詢。譬如，

select * from lineitem where l_shipdate = date '2010-12-01' or l_partkey<100

如果這兩個字段上都有索引，可以把查詢語句重寫為UNION查詢，以便使用索引提升查詢性能。

select * from lineitem where l_shipdate = date '2010-12-01'
union select * from lineitem where l_partkey<100

如果數據庫支持INDEX MERGING（請參考如何創建高效的索引），也可以調整數據庫相關參數啟用INDEX MERGING優化策略來提升數據庫性能。獲取該優化的更詳細信息。

OR條件的UPDELETE重寫優化

如果有使用OR條件的UPDATE或DELETE語句，數據庫優化器有可能無法使用索引來完成操作。

delete from lineitem where l_shipdate = date '2010-12-01' or l_partkey<100

如果這兩個字段上都有索引，可以把它重寫為多個DELETE語句，利用索引提升查詢性能。

delete from lineitem where l_shipdate = date '2010-12-01';
delete from lineitem where l_partkey<100;

獲取該優化的更詳細信息。

無條件的DELETE重寫優化

沒有查詢條件或查詢條件恒真的DELETE語句會刪除表中的所有數據。DELETE語句需要寫大量日志，以便進行事務回滾及主備同步。對于大表而言，可能會導致數據庫的鎖定和事務阻塞，同時會占用大量的日志空間。如果確認表中的數據不再需要，可以通過TRUNCATE表了代替DELETE語句。TRUNCATE比DELETE語句更快，因為它不會記錄每個刪除的行，而是直接將表清空并釋放空間。

delete from lineitem

重寫為：

truncate lineitem

避免在索引列上運算

在索引列上的運算將導致索引失效，容易造成全表掃描，產生嚴重的性能問題。所以需要盡量將索引列上的運算轉換到常量端進行，譬如下面的SQL。

select * from tpch.orders where adddate(o_orderdate,  INTERVAL 31 DAY) =date '2019-10-10'

adddate函數將導致o_orderdate上的索引不可用，可以將其轉換成下面這個等價的SQL，以便使用索引提升查詢效率。

select * from tpch.orders where o_orderdate = subdate(date '2019-10-10' , INTERVAL 31 DAY);

PawSQL可以幫助轉換大量的函數以及+、-、*、/運算符相關的操作。點擊獲取該優化的更詳細信息。

IN子查詢重寫優化

IN子查詢是指符合下面形式的子查詢，IN子查詢可以改寫成等價的相關EXISTS子查詢或是內連接，從而可以產生一個新的過濾條件，如果該過濾條件上有合適的索引，或是通過PawSQL索引推薦引擎推薦合適的索引，可以獲得更好的性能。

(expr1, expr2...) [NOT] IN (SELECT expr3, expr4, ...)

• IN子查詢重寫為EXISTS

譬如下面的IN子查詢語言是為了獲取最近一年內有訂單的用戶信息。

select * from customer where c_custkey in (select o_custkey from orders where O_ORDERDATE>=current_date - interval 1 year)

它可以重寫為exists子查詢，從而可以產生一個過濾條件（c_custkey = o_custkey）：

select * from customer where exists (select * from orders where c_custkey = o_custkey and O_ORDERDATE>=current_date - interval 1 year)

• IN子查詢重寫為內關聯

如果子查詢的查詢結果是不重復的，則IN子查詢可以重寫為兩個表的關聯，從而讓數據庫優化器可以規劃更優的表連接順序，也可以讓PawSQL推薦更好的優化方法。

譬如下面的SQL， c_custkey是表customer的主鍵。

select * from orders where o_custkey in (select c_custkey from customer where c_phone like '139%')

則上面的查詢語句可以重寫為。

select orders.* from orders, customer where o_custkey=c_custkey and c_phone like '139%'

點擊獲取該優化的更詳細信息。

關于PawSQL

PawSQL專注數據庫性能優化的自動化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL優化產品包括

• PawSQL Cloud，在線自動化SQL優化工具，支持SQL審查，智能查詢重寫、基于代價的索引推薦，適用于數據庫管理員及數據應用開發人員。
• PawSQL Advisor，IntelliJ 插件, 適用于數據應用開發人員，可以IDEA/DataGrip應用市場通過名稱搜索“PawSQL Advisor”安裝。
• PawSQL Engine, 是PawSQL系列產品的后端優化引擎，可以以docker鏡像的方式獨立安裝部署，并通過http/json的接口提供SQL優化服務。
• PawSQL Ora2pg/PawsQL Ora2op，Oracle語法的SQL應用轉換為PostgreSQL和openGauss語法的工具。