PawSQL周更新 | 新增6个SQL审查重写规则

概述

本文介绍PawSQL上一周新增的四个SQL审查规则

• 避免使用STRAIGHT_JOIN
• 避免使用Natural Join
• 避免使用CROSS JOIN
• 避免COUNT DISTINCT多个可空列

以及两个重写优化规则，

• NPE问题重写
• 显式禁止结果排序

这六个新的规则在PawSQL Cloud已可以正常使用。

1. 避免使用STRAIGHT_JOIN

Straight Join是MySQL中的一种表连接方式，它会强制以表的定义顺序来进行表连接，在结果上它等价于内连接。它给予了开发人员对数据库执行SQL的一定的控制能力。但它也失去了优化器带来的进行表连接顺序的优化，需要根据场景谨慎使用。PawSQL对使用STRAIGHT_JOIN的语句进行了风险提示，以提示用户其可能引起的性能问题。

以tpch库中的lineitem和orders两张表为例，下面的查询将直接以lineitem表在前,orders表在后进行连接，表示数据库将以表lineitem为驱动表，orders为被驱动表进行连接操作，不会对表的顺序进行优化。

SELECT *
FROM lineitem STRAIGHT_JOIN orders 
ON lineitem.l_orderkey = orders.o_orderkey;

默认预警级别

• 警告

从低到高三个预警级别，提示（Notice） < 警告（Warning） < 禁止（Critical）

预警触发条件

• SQL语句中出现STRAIGHT_JOIN语法

数据库类型

• MySQL

2. 避免使用Natural Join

Natural Join是一种特殊的等值连接,它可以和内连接、外连接及全连接配合使用，它会自动搜索两张表中所有相同列名和类型的列，并且以这些列为条件进行等值连接。Natural Join可以简化语句,但隐式连接条件降低代码的可读性，不利于理解表之间的关系，而且容易出现误连接。PawSQL对使用Natural Join的语句进行了风险提示，以避免其引发的正确性问题。以tpch库中的lineitem和orders表为例

SELECT *
FROM lineitem
NATURAL JOIN orders;

这会自动将lineitem表和orders表中名称和类型都相同的列(如orderkey)作为条件进行等值连接。

默认预警级别

• 警告

从低到高三个预警级别，提示（Notice） < 警告（Warning） < 禁止（Critical）

预警触发条件

• SQL语句中出现Natural JOIN语法

数据库类型及版本

• MySQL、openGauss、Oracle、PostgreSQL、KingbaseES、MariaDB

3. 避免使用CROSS JOIN

CROSS JOIN会将第一张表的每一行与第二张表的每一行进行笛卡尔乘积。它会生成表1行数x表2行数的记录。理论上它等价于条件为1=1的内连接。CROSS JOIN可以快速将多表拼接，但是其会产生大量记录,造成效率低下；而且不指定连接条件,结果可能没有实际意义。

PawSQL对使用CROSS JOIN的语句进行了风险提示，以避免其引发的性能问题。

案例SQL如下

SELECT *
FROM lineitem
CROSS JOIN orders;

这会把lineitem表的每条记录都与orders表的所有记录进行组合。

默认预警级别

• 警告

从低到高三个预警级别，提示（Notice） < 警告（Warning） < 禁止（Critical）

预警触发条件

• SQL语句中出现CROSS JOIN语法

数据库类型

• MySQL、openGauss、Oracle、PostgreSQL、KingbaseES、MariaDB

4. 避免COUNT DISTINCT多个可空列

当你使用COUNT (DISTINCT) 进行多列的计算时，它的计算结果可能和你预想的不同。COUNT (DISTINCT col) 计算该列除 NULL 之外的不重复行数，而COUNT (DISTINCT col, col2)则会排除掉任何一列为NULL的行。

譬如对于如下的查询,对列a和列组合（a，b）的统计不同值的个数，

select count(distinct t.a) as a_cnt, 
       count(distinct t.a,t.b) as a_b_cnt
from (values row(1,2),row(3,null)) as t(a,b);

返回结果如下：

对于列a返回2，而对于列组合（a，b）则为1，这可能不符合用户的直观感受，需特别关注。

默认预警级别

• 提示

从低到高三个预警级别，提示（Notice） < 警告（Warning） < 禁止（Critical）

触发条件

• SQL中存在COUNT DISTINCT函数
• COUNT的参数包括二个或两个以上的表达式
• 参数至少有一个是可以为空的

数据库类型

• MySQL、openGauss、Oracle、PostgreSQL、KingbaseES、MariaDB

5. NPE问题重写

SQL的NPE(Null Pointer Exception)问题是指在SQL查询中,当聚合列全为NULL时,SUM、AVG等聚合函数会返回NULL,这可能会导致后续的程序出现空指针异常。

select sum(t.b) from (values row(1,null)) as t(a,b);

可以使用如下方式避免NPE问题:

SELECT IFNULL(SUM(t.b), 0) from (values row(1,null)) as t(a,b);

或者:

SELECT case when SUM(t.b) is null 
       then 0 
       else sum(t.b) 
       end 
from (values row(1,null)) as t(a,b);

这会返回0而不是NULL,避免了空指针异常。

Oracle:NVL(); SQL Server和MS Access:ISNULL(); MySQL:IFNULL()或COALESCE(); PostgreSQL/openGauss CASE WHEN语法

触发条件

1. SUM或AVG聚集函数
2. 聚集函数的参数可能全为NULL, 包括
   1. 参数是列，列定义可以为空
   2. 参数是表达式，表达式可以为空
   3. 列定义不可为空，但是是外连接的内表，结果可能为空

数据库类型及版本

• MySQL、openGauss、Oracle、PostgreSQL、KingbaseES、MariaDB

6. 显式禁止结果排序

在MySQL的早期版本中，即使没有order by子句，group by默认也会按分组字段排序，这就可能导致不必要的文件排序，影响SQL的查询性能。可以通过添加order by null来强制取消排序，禁用查询结果集的排序；PawSQL对此语法结构进行了识别并提供了重写建议。

譬如下面的例子中

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY l_orderkey;

在MySQL 5.x版本中，group by l_orderkey会引起默认排序, 可以通过添加order by null来避免该排序。

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY l_orderkey
ORDER BY NULL;

触发条件

• MySQL数据库，版本低于8.0
• 存在分组字段，且无排序字段

数据库类型

• MySQL

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，openGauss，Oracle等，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以独立安装部署，并通过http/json的接口提供SQL优化服务。PawSQL Engine以docker镜像的方式提供部署安装。