SQL编写建议
📄字数 2.0K
👁️阅读量 加载中...
SQL编写是数据库开发过程中最重要的环节之一，本章旨在为开发人员提供一套标准、高效、安全的SQL编写指导，确保代码的可读性与可维护性，充分发挥数据库性能。
一、通用SQL编写
1.1 DDL
DDL语句用于定义与管理数据库对象，是对数据库操作的基础。开发过程中建议遵守以下原则：
- 注释：创建表或添加字段时，建议使用
COMMENT子句，为表或字段添加清晰的注释。 - 显示指定属性：创建字段时，建议显示指定字段的相关属性，避免依赖数据库的系统设置。
- 索引长度：建议表上索引字段的总长度控制在一定范围内，避免因索引膨胀而带来的存储开销以及性能下降。
- 强制删除：谨慎使用强制删除，除非已明确对象之间的依赖关系，否则容易造成误删除，带来数据丢失等风险。
1.2 类型转换
在某些业务场景下，需要对数据类型进行转换，在转换时建议按照如下原则：
- 禁止隐式转换：隐式类型转换通常是导致索引失效和性能下降的主要原因，建议明确业务需求与场景，使用
EXPLAIN VERBOSE查看执行计划，规避不必要的类型转换。 - 显示转换：当业务明确需要进行类型转换，使用
CAST或::显式指定目标转换类型，并且清晰的了解可能带来的负面影响，建议在转换时尽可能以字段类型为目标类型。
二、查询
查询时，SQL质量的好坏是数据库性能能否充分发挥的关键，本节旨在提出在查询SQL编写与优化时应当遵守的建议。
2.1 通用查询规范
在大多数场景下，进行查询命令时可参考如下优化建议：
- 除数据清洗、数据迁移等场景外，建议尽量避免向客户端返回大量结果集的操作，应当进一步评估业务设计。
- 建议使用事务方式封装执行DDL、DML操作，必要时可以进行回滚。
- 查询时，不建议使用
SELECT *语法，应当明确指定查询涉及的所有字段，避免造成性能问题。 - 在访问表对象时，建议添加模式前缀，明确访问对象，避免因模式切换访问非预期对象。
- 在进行多表关联查询时，执行代价难以预估，建议控制在3张表以下，避免搜索空间爆炸；如明确要求进行多表连接，建议考虑创建中间临时表。
- 不建议使用全连接进行查询，这类操作计算笛卡尔积，导致结果集迅速膨胀，执行性能较低。
NULL值在比较中建议使用IS NULL或IS NOT NULL进行判断，任何依赖运算符的逻辑比较可能返回NULL值，而非布尔类型结果。- 不建议在
SELECT语句中的结果输出部分使用子查询，此类操作会导致查询性能劣化，建议改造为表关联查询。 - 建议尽量避免不必要的排序操作，排序需要消耗大量的计算资源，除非业务明确要求排序。
2.2 关联查询
- 显式指定连接方式，建议明确使用
INNER JOIN、LEFT JOIN、RIGHT JOIN等方式，不建议直接使用FROM tab1, tab2的隐式连接，避免产生笛卡尔积。 - 使用
ON关联的字段，其数据类型、字符集、排序规则建议保持一致，使用比较效率较高的字段，且左右操作字段都应建立索引。 - 单次连接的表数量建议小于5个，避免关联嵌套深度过深，产生慢SQL。
- 当关联字段上存在大量
NULL值时，建议使用IS NOT NULL进行过滤，提升执行效率。 - 在多表连接时，建议尽量使用结果集较小的表作为驱动表。
2.3 子查询
- 不建议在查询字段中出现重复的子查询语句。
- 大多数场景下，建议优先考虑将子查询优化为关联查询，关联查询的执行效率通常优于子查询。
- 不建议在
SELECT目标列中出现标量子查询，影响执行性能。
2.4 排序与分组
在SQL查询中，排序(ORDER BY)和分组(GROUP BY)操作通常会影响查询执行的性能，本节提出以下建议与规范，从编写维度提高查询性能。
排序
排序属于数据库执行命令中频度较大的操作，尤其在数据量较大的表上，通常是影响性能的关键原因，可参考如下建议：
- 索引优化：建议在排序字段上建立索引，数据库可以利用索引优化排序操作，从而提高性能，避免全表扫描。
- 保持排序字段：建议避免在排序子句中对排序字段使用计算或函数，此类操作可能导致索引不可用，从而造成性能下降。
分组
分组是聚合操作的一种，可参考如下的建议，在分组操作中提高性能：
- 避免多字段分组：不建议在
GROUP BY中使用过多字段，这样会显著增加计算的复杂度，尤其是数据量较大的场景下，建议只对需要聚合的字段进行分组。 - 分组的字段类型：建议保持分组字段的类型统一，避免由于数据类型不一致带来的性能下降。
- 索引优化：建议在分组字段上建立索引，数据库能够利用索引提高分组操作的性能。
- HAVING：在需要分组过滤时，使用
HAVING关键字进行过滤，而不是将过滤条件放在WHERE子句中，HAVING对聚合之后的数据进行过滤操作。
三、WHERE条件语句
在使用WHERE进行条件过滤时，可参考如下建议：
| 场景 | 推荐写法 | 不推荐写法和会导致的问题 |
|---|---|---|
| 空值判断 | col IS NULL／col IS NOT NULL | col = NULL 会始终为 UNKNOWN，返回空集 |
| 函数嵌套 | WHERE name = 'abc' | WHERE UPPER(name) = 'ABC' 索引失效 |
| 范围查询 | col BETWEEN 10 AND 20 | col > 10 AND col < 20（相似，注意边界需确认） |
| 模糊查询 | name LIKE 'abc%' | name LIKE '%abc%' 无法走索引 |
| IN 列表过大 | JOIN tmp_values v ON t.id = v.id | WHERE id IN (大量常量) 解析及执行效率低下 |
| 不等式过滤 | col != 0 ⇒ col > 0 OR col < 0 | 直接 col != 0 索引不完全利用 |