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