表連接消除

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

考慮下面的例子，

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

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

select * from orders where o_custkey

獲取該優(yōu)化的更詳細信息。

外連接轉化為內連接

外連接優(yōu)化指的是滿足一定條件（外表具有NULL拒絕條件）的外連接可以轉化為內連接，從而可以讓數(shù)據(jù)庫優(yōu)化器可以選擇更優(yōu)的執(zhí)行計劃，提升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

獲取該優(yōu)化的更詳細信息。

SATTC重寫優(yōu)化

SAT-TC(SATisfiability-Transitive Closure) 重寫優(yōu)化是指分析一組相關的查詢條件，去發(fā)現(xiàn)是否有條件自相矛盾、簡化或是推斷出新的條件，從而幫助數(shù)據(jù)庫優(yōu)化器選擇更好的執(zhí)行計劃，提升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

獲取該優(yōu)化的更詳細信息。

查詢折疊(Query Folding)

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

考慮下面的例子，

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

重寫后的SQL為，

SELECT c_custkey, c_name FROM customer

獲取該優(yōu)化的更詳細信息。

投影下推(Projection Pushdown)

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

考慮下面的例子，

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;

獲取該優(yōu)化的更詳細信息。

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)

獲取該優(yōu)化的更詳細信息。

HAVING條件下推到WHERE

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

考慮下面的例子，

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修飾的子查詢重寫優(yōu)化

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

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重寫進一步優(yōu)化SQL，獲取該優(yōu)化的更詳細信息。

MAX/MIN子查詢重寫優(yōu)化

對于使用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)

獲取該優(yōu)化的更詳細信息。

COUNT標量子查詢重寫優(yōu)化

對于下面子查詢，

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)

獲取該優(yōu)化的更詳細信息。

避免使用=NULL判斷空值

=null或是case when null并不能判斷表達式為空, 判斷表達式為空應該使用is null。在SQL中出現(xiàn)=null或是case when null大概率是開發(fā)人員的錯誤寫法，因為它們總是被判斷為假，和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;

獲取該優(yōu)化的更詳細信息。

避免在查詢中使用SELECT *

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

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

獲取該優(yōu)化的更詳細信息。

避免使用隨機函數(shù)排序

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

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

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

盡量使用UNION ALL代替UNION

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

限制子查詢的嵌套層次

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

限制查詢中表連接的個數(shù)

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

類型轉換導致索引失效

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

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

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

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

獲取該優(yōu)化的更詳細信息。

避免連接字段類型不匹配

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

獲取該優(yōu)化的更詳細信息。

避免在SELECT語句添加FOR UPDATE

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

避免在UPDATE語句中使用LIMIT

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

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

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

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

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

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

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

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

避免無條件的UPDELETE語句

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

INSERT語句中值的數(shù)量不要超過閾值

批量插入值可以有效的提升數(shù)據(jù)插入的效率，如下例，

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

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

避免INSERT不指定列名

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

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

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

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

OFFSET的值超過閾值

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

避免%開頭的LIKE查詢

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

OR條件的SELECT重寫為UNION

如果使用OR條件的查詢語句，數(shù)據(jù)庫優(yōu)化器有可能無法使用索引來完成查詢。譬如，

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

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

OR條件的UPDELETE重寫優(yōu)化

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

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;

獲取該優(yōu)化的更詳細信息。

無條件的DELETE重寫優(yōu)化

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

delete from lineitem

重寫為：

truncate lineitem

避免在索引列上運算

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

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

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

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

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

IN子查詢重寫優(yōu)化

IN子查詢是指符合下面形式的子查詢，IN子查詢可以改寫成等價的相關EXISTS子查詢或是內連接，從而可以產(chǎn)生一個新的過濾條件，如果該過濾條件上有合適的索引，或是通過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子查詢，從而可以產(chǎn)生一個過濾條件（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子查詢重寫為內關聯(lián)

如果子查詢的查詢結果是不重復的，則IN子查詢可以重寫為兩個表的關聯(lián)，從而讓數(shù)據(jù)庫優(yōu)化器可以規(guī)劃更優(yōu)的表連接順序，也可以讓PawSQL推薦更好的優(yōu)化方法。

譬如下面的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%'

點擊獲取該優(yōu)化的更詳細信息。

關于PawSQL

PawSQL專注數(shù)據(jù)庫性能優(yōu)化的自動化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL優(yōu)化產(chǎn)品包括

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