表連接消除

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

考慮下面的例子，

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

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

select * from orders where o_custkey

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

外連接轉(zhuǎn)化為內(nèi)連接

外連接優(yōu)化指的是滿足一定條件（外表具有NULL拒絕條件）的外連接可以轉(zhuǎn)化為內(nèi)連接，從而可以讓數(shù)據(jù)庫優(yōu)化器可以選擇更優(yōu)的執(zhí)行計(jì)劃，提升SQL查詢的性能。

考慮下面的例子，

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

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

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

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

SATTC重寫優(yōu)化

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

查詢折疊(Query Folding)

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

考慮下面的例子，

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

重寫后的SQL為，

SELECT c_custkey, c_name FROM customer

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

投影下推(Projection Pushdown)

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

考慮下面的例子，

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)化的更詳細(xì)信息。

IN可空子查詢重寫

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

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

如果子查詢的結(jié)果集里有空值，這個(gè)SQL永遠(yuǎn)返回為空。正確的寫法應(yīng)該是在子查詢里加上非空限制，即

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

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

HAVING條件下推到WHERE

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

考慮下面的例子，

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)化

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

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

如果子查詢的結(jié)果中存在NULL，這個(gè)SQL永遠(yuǎn)返回為空。正確的寫法應(yīng)該是在子查詢里加上非空限制，或使用max/min的寫法

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

PawSQL推薦采用第二種寫法，可以通過max/min重寫進(jìn)一步優(yōu)化SQL，獲取該優(yōu)化的更詳細(xì)信息。

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

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

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

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

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

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

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

對于下面子查詢，

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

可以重寫為, 避免了一次聚集運(yùn)算

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

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

避免使用=NULL判斷空值

=null或是case when null并不能判斷表達(dá)式為空, 判斷表達(dá)式為空應(yīng)該使用is null。在SQL中出現(xiàn)=null或是case when null大概率是開發(fā)人員的錯(cuò)誤寫法，因?yàn)樗鼈兛偸潜慌袛酁榧?，?=0是等價(jià)的。所以PawSQL會(huì)檢查此類寫法，并進(jìn)行提醒和重寫。

譬如如下的SQL，

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

PawSQL會(huì)將其重寫為

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

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

避免在查詢中使用SELECT *

在查詢中使用SELECT *的缺點(diǎn)如下：

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

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

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

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

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

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

盡量使用UNION ALL代替UNION

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

限制子查詢的嵌套層次

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

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

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

類型轉(zhuǎn)換導(dǎo)致索引失效

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

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

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

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

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

避免連接字段類型不匹配

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

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

避免在SELECT語句添加FOR UPDATE

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

避免在UPDATE語句中使用LIMIT

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

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

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

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

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

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

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

沒有查詢條件或查詢條件恒真的查詢語句，且無分組語法，會(huì)導(dǎo)致全表掃描以及結(jié)果集巨大。PawSQL會(huì)檢查此類寫法，并進(jìn)行提醒。

避免無條件的UPDELETE語句

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

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），導(dǎo)致數(shù)據(jù)庫端報(bào)錯(cuò)。在PawSQL中，會(huì)檢查此類寫法，并對超過閾值（默認(rèn)為500）的SQL提示預(yù)警。

避免INSERT不指定列名

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

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

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

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

OFFSET的值超過閾值

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

避免%開頭的LIKE查詢

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

OR條件的SELECT重寫為UNION

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

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

如果這兩個(gè)字段上都有索引，可以把查詢語句重寫為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)建高效的索引），也可以調(diào)整數(shù)據(jù)庫相關(guān)參數(shù)啟用INDEX MERGING優(yōu)化策略來提升數(shù)據(jù)庫性能。獲取該優(yōu)化的更詳細(xì)信息。

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

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

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

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

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

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

delete from lineitem

重寫為：

truncate lineitem

避免在索引列上運(yùn)算

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

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

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

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

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

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

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

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

• IN子查詢重寫為EXISTS

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

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

它可以重寫為exists子查詢，從而可以產(chǎn)生一個(gè)過濾條件（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子查詢重寫為內(nèi)關(guān)聯(lián)

如果子查詢的查詢結(jié)果是不重復(fù)的，則IN子查詢可以重寫為兩個(gè)表的關(guān)聯(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%'

點(diǎn)擊獲取該優(yōu)化的更詳細(xì)信息。

關(guān)于PawSQL

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

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