PawSQL 优化引擎，看看这些你就知道了！

表连接消除

连接消除（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语法的工具。