20、SQL数据库联邦与性能调优全解析-优快云博客

SQL数据库联邦与性能调优全解析

联邦数据库的操作与管理

在处理数据库时，若想针对特定原子单元进行操作，可使用如下语句更新记录，注意使用 FILTERING=ON ：

// Connect to the first federation member
// FILTERING=ON is optional; it is the default setting.
USE FEDERATION PurchaseHistory (purchaseId = 100) WITH FILTERING=ON, RESET

// Get some data; three records will come back
UPDATE Purchases SET amount = 17.25

同样的逻辑可用于在联邦成员中添加或删除记录。若要删除多个原子单元，需使用 FILTERING=OFF 选项，使语句能影响多条记录。另外，若插入的原子单元的键超出当前联邦成员分区键范围， INSERT 语句将失败。

我们还可以创建多个联邦。例如，除了已创建的 PurchaseHistory 联邦，若还有一个跟踪客户访问的大表，可创建名为 CustomerVisits 的联邦：

// Connect back to the root database
USE FEDERATION ROOT WITH RESET

// Create a new federation
CREATE FEDERATION CustomerVisits (visitId BigInt RANGE)

// Connect to our first federation member
USE FEDERATION CustomerVisits (visitId=0) WITH FILTERING=OFF, RESET

// And let's create a table with a few records
CREATE TABLE CustomerVisits (
  vId bigint NOT NULL PRIMARY KEY,
  dateOfVisit dateTime NOT NULL,
  storeId int NOT NULL)
FEDERATED ON (visitId = vid)

INSERT INTO CustomerVisits VALUES (1, getdate(), 1)
INSERT INTO CustomerVisits VALUES (2, getdate(), 2)
INSERT INTO CustomerVisits VALUES (3, getdate(), 2)
INSERT INTO CustomerVisits VALUES (4, getdate(), 1)
INSERT INTO CustomerVisits VALUES (5, getdate(), 1)

至此，我们共有两个联邦和三个联邦成员。

需要注意的是，目前创建的联邦键为 BigInt 值，但在联邦表中不能使用 IDENTITY 列，应用程序需为联邦表生成下一个主键值。虽然 UniqueIdentifier 的使用稍显繁琐，但因其无需维护创建下一个标识符的逻辑，所以较为流行。

管理联邦可通过 SQL 数据库管理门户进行。操作步骤如下：
1. 登录 Windows Azure 管理门户（http://manage.windowsazure.com）。
2. 在 SQL 数据库中找到新创建的数据库，点击屏幕底部的“Manage”按钮。
3. 点击“Manage”图标后，会弹出一个窗口，要求登录所选的数据库服务器，在此屏幕上还可指定数据库名称。
4. 登录后，进入 SQL 数据库管理门户。在该门户中，联邦会显示在右侧，点击某个联邦（如 PurchaseHistory ），其下方的“Drop Federation”按钮将启用，可一键删除整个联邦，删除联邦会删除所有联邦成员。此外，还可通过点击顶部的“New”按钮创建新联邦，或点击“Query Performance”查看查询性能，但显示的性能信息仅适用于根数据库。
5. 点击联邦右侧的箭头可查看其详细信息，点击联邦成员可查看更多选项，如调整单个联邦成员的大小或进行拆分操作。

联邦数据库的局限性与解决方案

联邦数据库虽为云应用提供了重要的可扩展性模型，但也存在一些局限性：
|局限性|说明|
| ---- | ---- |
|无合并功能|当前版本的 SQL 数据库中，联邦成员拆分后无法合并，但未来版本可能支持此功能。|
|独立架构|联邦成员创建后，其架构定义与同一联邦中的其他成员相互独立，容易导致联邦成员的架构略有不同。因此，对某个联邦成员的架构进行更新时，需对所有其他联邦成员进行相同更改。|
|工具支持有限|目前市场上的大多数工具不支持联邦数据库，包括微软提供的一些工具，如无法使用数据同步服务、Azure 报告服务或数据库复制操作。|
|无扇出功能|不支持跨联邦成员查询，若要获取销售总额等信息，需在每个联邦成员上运行查询，并在代码中进行最终求和操作。不过，扇出操作在代码中实现并不困难，可使用多线程执行扇出查询以提高获取数据的性能。|

此外，联邦数据库还有许多其他局限性，如不支持索引视图、不支持 timestamp 和 rowversion 数据类型等。若计划在应用中使用联邦数据库，建议在 MSDN 上查看所有记录的限制。

对于扇出查询的局限性，可通过自行编写逻辑或使用开源库解决。例如，Enzo 分片库的最新版本支持联邦数据库，可轻松跨多个联邦成员执行语句，使用多线程操作以获得更快结果。还可使用扩展 T-SQL 语法的分布式查询（D-SQL），以下是一些 D-SQL 查询示例：

-- 抓取所有 PurchaseHistory 记录
SELECT * USING (SELECT * FROM Purchases) FEDERATED ON (PurchaseHistory)

-- 查找所有联邦成员中的最大金额
SELECT MAX(amount) USING (SELECT MAX(amount) FROM Purchases) FEDERATED ON (PurchaseHistory)

-- 查找邮政编码为 33498 的所有销售总额
SELECT SUM(amount) USING (SELECT SUM(amount) FROM Purchases P
  INNER JOIN StoreLocations S ON P.storeid = S.storeid
  WHERE S.zipcode = '33498' ) FEDERATED ON (PurchaseHistory)

-- 跨所有联邦成员执行存储过程并返回所有记录
SELECT * USING (EXEC GetPurchasesByZipCode '33498' ) FEDERATED ON (PurchaseHistory)

-- 在同一联邦中执行 JOIN 操作以排除某些记录
SELECT * USING (SELECT * FROM CustomerVisits) FEDERATED ON (CustomerVisits)
WHERE storeid IN (SELECT DISTINCT storeid FROM Purchases WHERE amount >= 100)
FEDERATED ON (PurchaseHistory)

该库还提供缓存、排序和基于原子单元值过滤记录等附加功能，由于是开源项目，可根据自身需求剖析代码并实现自定义版本。若想了解更多关于 Enzo 分片库的信息，可访问 http://enzosqlshard.codeplex.com 下载开源库和示例应用。

SQL 数据库性能调优

在云环境中运行的数据库，设计高性能的应用变得尤为重要，因为开发目标通常是尽量减少往返次数和返回的数据量。以下是 SQL 数据库性能调优的相关内容。

调优方法和工具

由于 SQL 数据库运行在托管和共享的基础设施上，了解哪些调优方法可用、哪些不可用很重要。常见的调优方法和工具如下表所示：
|方法或工具|是否可用|说明|
| ---- | ---- | ---- |
|SQL Profiler|否|使用服务器端跟踪的工具，如大多数审计工具、SQL Profiler 和数据库引擎调优顾问等不支持。|
|执行计划|是|SQL Server Management Studio (SSMS) 可显示针对 SQL 数据库的实际执行计划。|
|Perfmon|否|通常用于性能调优的任何 Windows 监控工具均不可用。|
|DMVs|有限|一些动态管理视图 (DMVs) 可用，可提供有关正在运行的会话和先前执行的语句的有用信息。|
|库指标|是|ADO.NET 提供库级统计信息，为开发人员提供额外的见解，如从消费者角度的总处理时间和传输的字节数。|

编码影响

由于无法访问 SQL 数据库的服务器端配置设置，如磁盘配置、内存分配、CPU 亲和性等，因此需要更加注重 SQL 语句的质量和网络流量。性能调优应关注以下几个方面：
- 连接池 ：建立新连接需要多次网络往返，会影响应用程序性能，应确保使用服务层和服务帐户正确池化连接，避免连接字符串更改。此外，若建立过多连接，SQL 数据库会进行限流。
- 数据包数量 ：返回数据的时间可能比预期长，需注意生成过多数据包的 SQL 代码，如 Print 语句会产生不必要的网络流量，应尽量从 T-SQL 中删除。
- 索引：SQL 数据库若检测到语句消耗过多资源，可能会对连接进行限流，因此正确的索引对于性能调优至关重要。
- 数据库设计 ：某些数据库设计对性能更有利，高度规范化的设计可提高数据质量，而松散规范化的数据库通常可提高性能，开发时需理解这种权衡。
- 并行数据访问 ：若代码访问多个数据库，如在分片环境中，可能需要在代码中引入多线程或调整并行访问方法，以避免不必要的锁。

调优技术 - 动态管理视图

SQL 数据库提供了一些类似于 SQL Server 的动态管理视图 (DMVs)，支持的 DMVs 如下表所示：
|DMV|说明|
| ---- | ---- |
|sys.dm_exec_connections|部分支持，返回 SQL 数据库中建立的连接列表，但一些有趣的列（如返回客户端机器 MAC 地址的 client_net_address ）不可用。|
|sys.dm_exec_query_plan|完全支持，返回 SQL 查询或批处理的 XML 执行计划。|
|sys.dm_exec_query_stats|完全支持，返回缓存查询计划的聚合性能信息。|
|sys.dm_exec_requests|完全支持，返回 SQL 数据库正在执行的语句的信息。|
|sys.dm_exec_sessions|部分支持，返回当前打开的会话以及该会话的性能信息，但不返回最后登录信息，如 last_successful_logon 列。|
|sys.dm_exec_sql_text|完全支持，返回 SQL 批处理的文本。|
|sys.dm_exec_text_query_plan|完全支持，以文本格式返回 SQL 查询或批处理的执行计划。|

若要获取 SQL 语句的性能指标，且能将语句隔离到唯一的数据库连接，或语句已停止执行，可使用 dm_exec_sessions DMV。但该 DMV 会累积通过同一连接执行的所有语句的性能指标，因此测试数据库查询并单独检索该查询的性能指标时，需建立两个连接：一个用于执行查询，另一个用于读取性能指标，以避免干扰 SQL 数据库收集的性能数据，且两个连接需使用相同的登录名。

例如，建立与 SQL 数据库的连接，运行以下查询：

SELECT TOP 50 * FROM sys.indexes

记录会话 ID（可在 Microsoft SQL Server Management Studio 的状态栏或查询选项卡中找到）。然后打开另一个查询窗口，运行以下查询并指定要调查的会话 ID：

SELECT
   login_time,
   host_name,
   program_name,
   host_process_id,
   cpu_time,
   memory_usage,
   total_elapsed_time,
   reads,
   writes,
   logical_reads,
   row_count,
   original_login_name
FROM sys.dm_exec_sessions
WHERE session_id = 176           // replace with your session_id

该语句将返回上一个 SQL 语句的重要性能指标。

综上所述，SQL 数据库的联邦功能为数据库层提供了弹性扩展的能力，虽存在一些局限性，但通过合理的设计和使用相关工具，可在一定程度上解决这些问题。同时，在性能调优方面，需综合考虑方法和工具的可用性、编码影响等因素，利用动态管理视图等技术提高数据库性能。

SQL数据库联邦与性能调优全解析

性能调优案例分析

为了更深入地理解性能调优的实际操作，下面通过一个具体案例来展示如何运用前面提到的方法和工具进行性能优化。

假设我们有一个电子商务应用，其数据库中有一个 Orders 表，用于存储订单信息。随着业务的增长，查询该表的性能逐渐下降，我们需要对其进行调优。

分析查询性能

首先，我们使用 dm_exec_sessions DMV 来分析查询的性能。按照前面提到的步骤，建立两个连接：
1. 第一个连接执行查询：

SELECT * FROM Orders WHERE order_date > '2023-01-01' AND total_amount > 100;

记录下该查询的会话 ID。
2. 第二个连接使用 dm_exec_sessions DMV 来获取性能指标：

SELECT
   login_time,
   host_name,
   program_name,
   host_process_id,
   cpu_time,
   memory_usage,
   total_elapsed_time,
   reads,
   writes,
   logical_reads,
   row_count,
   original_login_name
FROM sys.dm_exec_sessions
WHERE session_id = [your_session_id];

通过分析这些性能指标，我们发现该查询的 total_elapsed_time 较长， reads 和 logical_reads 数量较多，说明查询可能没有使用合适的索引。

优化索引

根据查询条件，我们可以为 order_date 和 total_amount 列创建复合索引：

CREATE INDEX idx_orders_date_amount ON Orders (order_date, total_amount);

创建索引后，再次执行查询并获取性能指标，发现 total_elapsed_time 明显缩短， reads 和 logical_reads 数量也大幅减少，查询性能得到了显著提升。

优化连接池

在应用代码中，我们需要确保连接池的正确配置。以下是一个使用 ADO.NET 进行连接池配置的示例：

using System.Data.SqlClient;

// 配置连接字符串，启用连接池
string connectionString = "Data Source=your_server;Initial Catalog=your_database;User ID=your_user;Password=your_password;Pooling=true;Max Pool Size=100;Min Pool Size=10;";

// 创建连接
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // 打开连接
    connection.Open();

    // 执行查询
    SqlCommand command = new SqlCommand("SELECT * FROM Orders WHERE order_date > '2023-01-01' AND total_amount > 100;", connection);
    SqlDataReader reader = command.ExecuteReader();

    // 处理结果
    while (reader.Read())
    {
        // 处理数据
    }

    // 关闭读取器
    reader.Close();
}

通过合理配置连接池的 Max Pool Size 和 Min Pool Size ，可以避免频繁建立新连接，提高应用程序的性能。