SQL 编码的最佳实践

对于任何编程语言来说，仅仅编写代码是远远不够的。它应该使用最佳实践来编写，以获得最佳性能。在本文中，我将解释不使用 SQL 编码最佳实践编写代码的缺点。稍后，我将描述如何在我们的数据库代码中实施最佳实践。

不使用最佳实践编写的代码有以下缺点：–

1)难以维护：–如果代码编写不当，将来修改代码将非常困难，有时甚至会变得如此混乱，以至于即使是微小的改动也需要我们重写代码。

2)留下大量不可用代码，导致代码不必要地冗长：–如果我们没有正确地进行设计，我们将不断地修改代码，这会导致大量返工。因此，大多数时候，情况会变成一些存储过程留下了不再需要但仍在执行的函数或查询块，从而降低了它们的性能。

3)代码难以理解：–一个人应该编写易于其他团队成员理解的代码。我们必须为每个代码块编写适当的注释。没有注释，就很难理解代码块背后的目的。

4)因不恰当的复杂查询导致性能低下：–在数据库中，可能有多种方法可以编写用于从表中提取/更新/删除记录的查询，但性能取决于我们编写查询的方式。例如，一个包含大量表连接的冗长复杂 SQL 查询，或者一个包含 DISTINCT 命令来提取数百万条记录但实际上并不需要 DISTINCT 值的 SQL 查询，或者将数百万条记录存储在表变量中，都会严重影响应用程序的性能。

5)因错误和事务处理不当而导致的意外错误或行为：–如果异常处理不当，查询执行可能会中途失败，错误的数据会被插入到表中，从而损坏数据库。

6)因事务处理不当导致表长时间锁定：–应使用事务，以便在发生任何异常或错误时，未提交的数据可以被回滚。但有时我们的事务使用方式非常糟糕，导致主表长时间被锁定，最终出现死锁情况，长时间的锁定期会导致应用程序性能下降。

7)调试困难：–如果代码以一种非常混乱的方式编写，并且包含大量不可用的代码，那么调试代码并找到错误就会变得很困难。

SQL 代码编写最佳实践

易于理解

• 每个代码块都应有适当的注释，说明该代码块的目的。
• 使用缩进和换行提高可读性。
• 表别名应具有意义。
• 应维护数据库模式、关系和数据流的最新文档。

存储过程和函数

• 为了更好的安全性、更好的性能和代码的可重用性，应优先使用存储过程而不是临时查询。
• 包含复杂逻辑的存储过程应分解为更小的存储过程和函数。
• 应通过创建实用程序过程和函数来重用代码。
• 仅在必要时使用动态 SQL 查询。
• 使用参数化查询以防止 SQL 注入。
• 应避免返回不必要的行或列，以最大程度地减少数据传输并提高性能。
• 不要直接在 SQL 中嵌入值。使用参数可以防止 SQL 注入。此方法还有助于提高执行计划的重用性。

优化 JOIN

• 在可能的情况下，尝试使用 SQL JOIN 而不是子查询以获得更好的性能。
• 根据需求使用适当的 JOIN 类型（INNER、LEFT、RIGHT、FULL）。
• 确保 JOIN 列已索引。

SQL 索引

• 在经常在 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中使用的列上创建适当的索引。
• 使用覆盖索引将 SELECT 查询的所有列包含在索引中。
• 在经常更新的列上创建索引时要小心，因为这会减慢 DML 操作。
• 避免在具有非常频繁 DML 更改的表上使用聚集索引。维护聚集索引的成本很高，这会严重影响性能。
• 创建过多索引也会降低性能，因为维护索引需要成本。

避免在 WHERE 子句列上使用函数

• 在 WHERE 子句列上使用函数会阻止 SQL Server 使用该列上的索引，从而导致性能下降。

事务处理

• 应在存储过程中妥善处理事务。这确保在发生错误时，未提交的数据可以轻松回滚。
• 事务持续时间应尽可能短。长时间锁定表会对应用程序性能产生负面影响。不要将整个存储过程应用于一个事务，而是使用多个事务。将相关的代码块放在一个事务中。
• 应避免在事务中进行用户交互。
• 隔离级别应根据一致性和性能之间的平衡来设置。

错误处理

• 应使用 Try….catch 进行适当的错误处理，以管理日志和错误。Catch 块应提供适当的错误消息。
• 在发生任何错误时，在 CATCH 块中执行回滚事务。这可以防止因未提交数据而导致表数据损坏。这有助于维护数据库的完整性。

数据获取优化

• 不要使用“Select * from tablename”命令。此命令会选择表的所有列。相反，只获取所需的列。这种方法可以提高查询性能。
• 仅在需要唯一结果时才使用 DISTINCT 命令。它会增加查询的额外成本。
• 如果我们不需要唯一结果，请使用 UNION ALL 而不是 UNION。
• 如果我们只需要验证表中的值是否存在，请优先使用 EXISTS 而不是 IN。EXISTS 在找到搜索值后立即退出。相比之下，IN 会扫描整个表。

避免嵌套循环

• 在可能的情况下，尽量减少嵌套循环和子查询的使用。
• 对于复杂查询，可以考虑使用通用表表达式 (CTE) 或派生表。

避免使用游标

• 使用基于集合的操作而不是基于行的操作。
• 如果需要游标，请使用 FAST_FORWARD 或 READ_ONLY 游标。

键和约束

• 应使用主键和外键正确定义表之间的关系，因为这有助于维护数据库完整性和提高数据库性能。
• 使用适当的约束，如 NOT NULL 和 UNIQUE，以确保数据完整性。

临时表与表变量

• 当要存储的行数较少时，请尝试使用表变量。如果需要存储的记录数较多，请使用临时表。

适当使用数据类型

• 为每列选择最小的适当数据类型。
• 不要对所有数据类型使用通用数据类型，如 VARCHAR，而不考虑数据大小。根据要存储的数据正确定义数据类型及其大小。

使用“SET NOCOUNT ON”

• 我们应该在代码块的开头使用“SET NOCOUNT ON”。然后，在代码块的末尾使用“SET NOCOUNT OFF”。除非您需要知道查询或存储过程影响了多少行，否则这是必需的。计算受影响的行数会增加额外的性能开销。

定期监控和优化

• 使用 SQL Server Profiler 和执行计划定期监控和优化查询。
• 创建索引是不够的。我们需要创建索引维护计划来检查索引碎片，并相应地重新组织或重建索引。
• 定期更新表统计信息。

摘要

本文试图说明不遵循最佳实践的缺点。它强调了一些在 SQL 编码过程中应遵循的最佳实践。这些实践可以使我们的数据库代码更灵活、可重用、易于理解且更优化。

有关 SQL Server 的更多文章，您可以访问Vivek Johari 的技术

这篇SQL 编码最佳实践的文章首次出现在Vivek Johari 的技术。