主页 / dba / 问题 / 241280
Accepted
lit
Asked: 2019-06-25 06:31:04 +0800 CST

省略架构名称以更灵活地管理对 SQL Server 数据库的访问

  • 772

当应用程序硬编码模式名称时dbo.TABLE，它会降低 DBA 根据需要迁移数据的能力。

应用程序省略模式名称是否安全且更灵活？

我知道您会说这是“基于意见的”，但这是管理数据库的关键问题。

sql-server application-design

2 个回答

  1. 这里的简短回答是不，它既不是“安全”也不是“权宜之计”，而且可以说省略模式名称也不是更灵活。事实上，这样做是“射中自己的脚”的经典方式之一。

    编程的主要规则之一是以防御方式编写代码。这是一种众所周知的策略，有助于防止错误，尤其是难以追踪的错误。防御性编程定义为：

    有远见地进行设计，以确保软件在不可预见的情况下继续正常运行。

    这里需要注意的是，SQL Server 中的每个对象都属于一个特定的架构。大多数情况下，模式只是dbo模式，人们可以很容易地设计一个充满存储过程、视图、函数等的整个数据库，甚至永远不会意识到模式的概念，因为默认情况下，一切都会被创建在dbo架构中。可以这样做，但我不建议这样做，原因如下：

    1. 它使代码难以解释。查看SELECT下面的两个语句，关于明确指定模式的语句更加清晰。

      SELECT name FROM databases;

SELECT name FROM sys.databases;

      由于我已经指定sys.databases了 ，您立即知道结果将来自系统视图，毫无疑问。指定模式使复杂的代码更容易可靠地理解。

    2. 架构解析可能并不总是如您所愿。如果数据库中有多个架构，则当您未指定架构时，SQL Server 需要执行对象搜索。当您指定架构时，SQL Server 确切地知道在哪里查找对象。

      • 两名开发人员 Melanie 和 Hannah 正在编写代码。Melanie 的默认架构设置为“Melanie”，Hannah 的默认架构设置为“Hannah”。Melaniex无意中在Melanie模式中创建了表 ，因为它们没有dbo在CREATE TABLE语句中指定。Melanie 告诉 Hannah 开始使用 table x。Hannah 然后继续使用 tablex而不指定模式，但由于两个开发人员都为他们的登录分配了不同的默认模式，他们的新代码无法找到该对象。这对 Hannah 来说就像一个错误，同时当 Melanie 运行相同的代码时，它运行良好。如果两个开发人员都指定了模式，他们将永远不会看到这个错误。

      • 您的数据库有两个架构，warehouse并且accounting. 两种模式都有一个名为 的表Invoices。该warehouse.Invoice表包含有关需要处理的发票的详细信息。该accounting.Invoices表包含与实际发票相关的所有列，例如帐单明细、应付金额等。由既warehouse没有也没有accounting默认模式、未指定模式名称的用户编写的代码将返回对象解析在运行时或将代码保存到数据库时出错。在下面的代码中，我invoices在不同的模式中创建了两个表：

        CREATE SCHEMA warehouse
CREATE TABLE invoices
(
    i int NOT NULL
        PRIMARY KEY CLUSTERED
    , HasShipped bit NOT NULL
        DEFAULT (0)
);
GO
CREATE SCHEMA accounting
CREATE TABLE invoices
(
    i int NOT NULL
        PRIMARY KEY CLUSTERED
    , TotalAmount decimal(10,4) NOT NULL
);
GO

        现在，如果我尝试在不指定架构的情况下创建视图，则会收到错误消息：

        CREATE VIEW SomeView
AS
SELECT *
FROM invoices;
GO

        消息 208，级别 16，状态 1，过程 SomeView，第 4 行 [批处理开始第 25 行]   
  无效的对象名称“发票”。

        现在，以在dbo模式中创建存储过程为例：

        CREATE PROCEDURE myproc
AS
BEGIN
    SELECT *
    FROM invoices;
END
GO

        当您执行代码以创建该过程时，由于 SQL Server 的延迟名称解析，SQL Server 不会报告任何错误。但是，当您执行 proc 时，您会收到以下错误：

        消息 208，级别 16，状态 1，过程 myproc，第 4 行 [批处理开始第 42 行]  
  无效的对象名称“发票”。

    3. 性能可能会受到影响。以多种方式。

      • 如果执行计划中引用的 T-SQL 语句没有架构前缀，则通常在具有不同默认架构的用户之间共享的执行计划将被单独缓存。因此，例如，以下简单的 T-SQL 语句将为具有不同默认模式的每个用户缓存多次：

        SELECT UserName FROM Users;

        对于一个复杂的数据库，许多不同的用户正在执行许多不同的 T-SQL 语句，这可能会导致显着的计划缓存膨胀，从而对查询执行产生负面影响。更不用说每个用户可能会根据正在执行的语句的复杂性以及参数嗅探是否在起作用而获得显着不同的计划。

      • 在有许多架构和许多对象的环境中，指定架构名称会限制 SQL Server 在编译时将对象名称解析为对象 ID 时必须执行的工作。指定模式可以显着减少具有复杂代码的大型繁忙数据库的负载。

      • 当您不指定架构时，自旋锁争用可能会出现问题。 这个关于诊断和解决自旋锁争用的 Microsoft 文档指出：

      所有对象的完全限定名称将导致 SQL Server 无需执行解析名称所需的代码路径。我们还观察到在调用存储过程时未使用完全限定名称时遇到的 SOS_CACHESTORE 自旋锁类型的争用点。未能完全限定这些名称会导致 SQL Server 需要为用户查找默认架构，这会导致执行 SQL 所需的代码路径更长。

    4. 某些对象没有默认架构。以Maria Zakourdaev 在此处展示的有关 DDL 触发器的示例为例。当您创建服务器端触发器时，该触发器没有默认模式的概念，因此会在运行时失败，这可能会特别痛苦。Maria 在文章中承认，除非代码要求，否则他们通常不会指定模式。我会提出，如果他们习惯于始终指定模式，Maria 就不会花费数小时来追踪该错误。

    总而言之，我总是为我编写的每一段代码指定架构，仅仅是因为在编写代码时几乎不需要付出任何努力，而且它保证了某些错误永远不会发生。

    指定模式成本低且影响大，我认为我们都同意这是一件好事™。

    • 4
  2. Best Answer

    您似乎理解这一点，但以防万一……您不必总是指定架构，只要架构是用户的默认架构。

    因此，您可以在 SQL Server 的登录级别处理此问题。可以将登录设置为默认模式，例如模式“MySchema”。对于此登录，该表mySchema.Orders将是该用户的默认表，可简单寻址Orders。

    这不经常使用，因为它可能会让人感到困惑，但它是一种功能齐全的方法。但请注意：如果您的数据库中没有该表mySchema.Orders，系统也会查找dbo.Orders。如果您想要一些个性化的表格和一些共同的表格，这很好 - 如果mySchema用户不应该访问dbo表格，那就不好了。

    • 0

相关问题