PawSQL更新 | 新增18个SQL性能审核重写规则

2024-08-20 19:55:23 浏览数 (2)

PawSQL最新版本针对DML和DQL新增了审核和重写优化规则共计33个，整体的规则数目达到了83个，覆盖了正确性，安全性、可维护性、性能四个方面的SQL质量问题，并提供了优化建议，已经形成比较完善的针对数据操作的SQL质量审查体系。本文介绍其中新增的11个正确性相关的审核规则。本文介绍新增的18个SQL性能审核及重写优化规则。

性能相关规则

1. 显式禁止结果字段排序

规则描述

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

譬如下面的例子中

代码语言：javascript复制
SELECT l_orderkey, sum(l_quantity)
FROM lineitem
GROUP BY l_orderkey;

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

代码语言：javascript复制
SELECT l_orderkey, sum(l_quantity)
FROM lineitem
GROUP BY l_orderkey
ORDER BY NULL;
默认预警级别
  • 警告
触发条件
  • MySQL数据库，版本低于8.0
  • 存在分组字段，且无排序字段

2. GROUP字段中有表达式导致索引失效

规则描述

数据库可以利用索引的有序性来避免GROUP子句中列的排序，从而提升SQL的性能。但是如果Group字段是一个表达式或函数，则可能无法利用索引来进行排序。

默认预警级别
  • 提示
触发条件
  • 存在分组字段，字段为表达式或函数

3. ORDER字段中有表达式导致索引失效

规则描述

数据库可以利用索引的有序性来避免ORDER子句中列的排序，从而提升SQL的性能。但是如果ORDER字段是一个表达式或函数，则可能无法利用索引来进行排序。

默认预警级别
  • 提示
触发条件
  • 存在排序字段，字段为表达式或函数

4. 排序字段方向不同导致索引失效

规则描述

ORDER BY 子句中的所有表达式需要按统一的 ASC 或 DESC 方向排序，才能利用索引来避免排序；如果ORDER BY 语句对多个不同条件使用不同方向的排序无法使用索引。

默认预警级别
  • 提示
触发条件
  • 有多个排序字段
  • 存在两种排序方向

5. 避免对长字段进行分组

规则描述

在数据库中，分组通常是通过排序或哈希来做，如果需要分组的行数比较多，那么单个字段长度会较大的影响分组效率。此规则可以通过比较分组字段的长度是否超过用户输入的阈值。如果超过阈值，则会进行预警。

默认预警级别
  • 预警
触发条件
  1. GROUP字段类型为CHAR/VARCHAR，且字段长度超过阈值
  2. 或GROUP字段类型为CLOB/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT

6. 避免对条件字段使用负向查询

规则描述

负向查询指的是否定查询，即<> 、NOT IN 等否定条件。此类查询无法利用索引进行快速定位。

默认预警级别
  • 警告
触发条件
  • SQL中的条件为否定条件。

7. 表连接缺少链接条件

规则描述

表连接缺少链接条件会导致结果集变成两个表的笛卡尔集，数据量巨大，且有较大可能性不符合开发者的预期。PawSQL会检查此类写法，并进行提醒。

默认预警级别
  • 提示
触发条件
  • 表连接缺少链接条件

8. 分区表没有使用分区字段过滤

规则描述

在访问分区表时，没有使用分区字段进行过滤，会导致需要访问所有分区。

默认预警级别
  • 提示
触发条件
  • 表是分区表
  • SQL中不存在在分区键的过滤条件

9. 过滤条件中须使用主键或索引列

规则描述

如果一个表的过滤条件上没有主键或索引，则会导致全表扫描。

默认预警级别
  • 提示
触发条件
  • SQL中存在过滤或关联条件
  • 过滤或关联条件是可索引条件
  • 可索引条件不存在任何可利用的索引（包括主键索引）

10. 查询中表连接的个数超过阈值

规则描述

在单机版数据库执行计划的规划中，表连接的顺序和连接的方法是数据库优化器最重要的规划内容。表连接数目的增加将几何级数地增加数据库优化器对于最优执行计划的搜寻空间，导致生成执行计划的时间比较长，且容易生成性能较差的执行计划。所以PawSQL检测查询中表连接的数目是否超过某个阈值，并提醒用户可能的风险。在PawSQL中，阈值的默认值是5，用户可以在创建优化任务时修改此阈值。

默认预警级别
  • 警告
触发条件
  • 单个查询块中表连接的个数超过阈值（默认为5）

11. 避免查询排序时指定COLLATION

规则描述

可以在SQL中指定排序字段所使用的COLLATION,譬如下面的SQL

代码语言：javascript复制
select * from customer c order by c_name COLLATE utf8mb4_0900_bin

这样的话，该SQL将无法利用索引的有序性来避免排序。

默认预警级别
  • 警告
触发条件
  • 显式的指定排序的COLLATION

12.避免对长字段进行排序

规则描述

在计算机中，排序是一个OlnN时间复杂度的操作，如果需要排序的行数比较多，那么单个字段长度会较大地影响排序效率。此规则可以通过比较排序字段的长度是否超过用户输入的阈值。如果超过阈值，则会进行预警。

默认预警级别
  • 预警
触发条件
  1. ORDER字段类型为CHAR/VARCHAR，且字段长度超过阈值
  2. 或ORDER字段类型为CLOB/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT

13. 避免使用标量子查询

规则描述

标量子查询返回单行单列的一个值，它可以出现在SQL中任何单值出现的地方。标量子查询通常需要在执行时才能确定其是否只返回单行值，且其通常为相关子查询。容易引起运行时错误，以及性能问题。

默认预警级别
  • 提示
触发条件
  • SQL中出现标量子查询

14. 避免更新主键的值

规则描述

在MySQL InnoDB引擎或是SQL Server数据库中，数据存储方式都是以主键的方式组织的。在这种情况下，对主键的更新会涉及到对数据在磁盘上物理组织的调整，而且也涉及到主键值唯一性的检查，在表数据量非常大的情况下，更新的代价可能非常之大。

默认预警级别
  • 警告
触发条件
  • 更新主键的值
  • MySQL InnoDB引擎或是SQL Server数据库

16. 避免更新唯一约束的值

规则描述

对唯一性约束的列的值的更新，需要对它进行唯一性检查，在表数据量非常大的情况下，更新的代价可能非常大。

默认预警级别
  • 警告
触发条件
  • 更新唯一性列的值

17. DELETE/UPDATE禁止使用表连接

规则描述

表连接的误操作可能导致结果集的行非常大，对大结果集的DELETE/UPDATE可能会非常耗时，锁表时间较长，也难以对操作进行回滚。

默认预警级别
  • 警告
触发条件
  • DELETE/UPDATE存在多表

18. 避免使用不必要的内置函数

规则描述

某些内置函数可能不满足业务或是计算上的某些规范要求。通过配置该规则可以指定业务中需要禁止使用的内置函数。

默认预警级别
  • 提示
触发规则
  • 函数列表中的函数名称出现在SQL中。

关于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语法的工具。
排序 索引 性能 sql 连接

0 人点赞