使用数据依赖型路由可将查询路由到相应的数据库Use Data-dependent routing to route a query to appropriate database

数据依赖型路由是使用查询中的数据将请求路由到相应数据库的功能。Data-dependent routing is the ability to use the data in a query to route the request to an appropriate database. 在使用分片数据库时,数据依赖型路由是一种基础模式。Data-dependant routing is a fundamental pattern when working with sharded databases. 请求上下文也可用于路由请求,尤其是当分片键不是查询的一部分时。The request context may also be used to route the request, especially if the sharding key is not part of the query. 将应用程序中使用数据依赖型路由的每个特定查询和事务限制为针对每个请求访问一个数据库。Each specific query or transaction in an application using data-dependent routing is restricted to accessing one database per request. 对于 Azure SQL 数据库弹性工具,这种路由是通过 ShardMapManager(Java.NET)类实现的。For the Azure SQL Database Elastic tools, this routing is accomplished with the ShardMapManager (Java, .NET) class.

应用程序无需在分片环境中跟踪与不同的数据片相关联的各种连接字符串或数据库位置。The application does not need to track various connection strings or DB locations associated with different slices of data in the sharded environment. 但是,分片映射管理器在需要时基于分片映射中的数据以及作为应用程序请求目标的分片键值,建立与正确数据库的连接。Instead, the Shard Map Manager opens connections to the correct databases when needed, based on the data in the shard map and the value of the sharding key that is the target of the application's request. 该键通常为 customer_id、tenant_id、date_key 或一些作为数据库请求的基础参数的其他特定标识符。The key is typically the customer_id, tenant_id, date_key, or some other specific identifier that is a fundamental parameter of the database request.

有关详细信息,请参阅使用数据依赖型路由横向扩展 SQL ServerFor more information, see Scaling Out SQL Server with Data-Dependent Routing.

下载客户端库Download the client library

若要下载:To download:

在数据依赖型路由应用程序中使用 ShardMapManagerUsing a ShardMapManager in a data-dependent routing application

应用程序应使用工厂调用 GetSQLShardMapManager(Java.NET)在初始化期间实例化 ShardMapManager。Applications should instantiate the ShardMapManager during initialization, using the factory call GetSQLShardMapManager (Java, .NET). 在本示例中,将同时初始化 ShardMapManager 以及它所包含的特定 ShardMapIn this example, both a ShardMapManager and a specific ShardMap that it contains are initialized. 本例演示 GetSqlShardMapManager 和 GetRangeShardMap(Java.NET)方法。This example shows the GetSqlShardMapManager and GetRangeShardMap (Java, .NET) methods.

ShardMapManager smm = ShardMapManagerFactory.getSqlShardMapManager(connectionString, ShardMapManagerLoadPolicy.Lazy);
RangeShardMap<int> rangeShardMap = smm.getRangeShardMap(Configuration.getRangeShardMapName(), ShardKeyType.Int32);
ShardMapManager smm = ShardMapManagerFactory.GetSqlShardMapManager(smmConnectionString, ShardMapManagerLoadPolicy.Lazy);
RangeShardMap<int> customerShardMap = smm.GetRangeShardMap<int>("customerMap"); 

尽可能使用最低特权凭据来获取分片映射Use lowest privilege credentials possible for getting the shard map

如果应用程序本身无法处理分片映射,在工厂方法中使用的凭据应该对全局分片映射数据库具有只读权限。If an application is not manipulating the shard map itself, the credentials used in the factory method should have read-only permissions on the Global Shard Map database. 这些凭据通常与用于分发到分片映射管理器的开放连接的凭据不同。These credentials are typically different from credentials used to open connections to the shard map manager. 另请参阅用于访问弹性数据库客户端库的凭据See also Credentials used to access the Elastic Database client library.

调用 OpenConnectionForKey 方法Call the OpenConnectionForKey method

ShardMap.OpenConnectionForKey 方法(Java.NET)将返回连接,该连接可随时基于 key 参数的值将命令分发到相应的数据库中。The ShardMap.OpenConnectionForKey method (Java, .NET) returns a connection ready for issuing commands to the appropriate database based on the value of the key parameter. ShardMapManager 将分片信息缓存在应用程序中,因此这些请求通常不会针对全局分片映射数据库调用数据库查找。Shard information is cached in the application by the ShardMapManager, so these requests do not typically involve a database lookup against the Global Shard Map database.

// Syntax:
public Connection openConnectionForKey(Object key, String connectionString, ConnectionOptions options)
// Syntax:
public SqlConnection OpenConnectionForKey<TKey>(TKey key, string connectionString, ConnectionOptions options)
  • key 参数在分片映射中用作查找键,以确定该请求的相应数据库。The key parameter is used as a lookup key into the shard map to determine the appropriate database for the request.
  • connectionString 用于仅传递所需连接的用户凭据。The connectionString is used to pass only the user credentials for the desired connection. 此 connectionString 中不包含数据库名称或服务器名称,因为该方法使用 ShardMap 确定数据库和服务器。No database name or server name is included in this connectionString since the method determines the database and server using the ShardMap.
  • 在分片映射可能会发生更改并且行可能会由于拆分或合并操作而移到其他数据库的环境中,connectionOptions(Java.NET)应设置为 ConnectionOptions.Validate。The connectionOptions (Java, .NET) should be set to ConnectionOptions.Validate if an environment where shard maps may change and rows may move to other databases as a result of split or merge operations. 此验证涉及在将连接传送到应用程序之前,在目标数据库上简要查询局部分片映射(而不是全局分片映射)。This validation involves a brief query to the local shard map on the target database (not to the global shard map) before the connection is delivered to the application.

如果针对局部分片映射进行的验证失败(指示缓存不正确),分片映射管理器会查询全局分片映射来获取新的正确值以供查找、更新缓存以及获取和返回相应的数据库连接。If the validation against the local shard map fails (indicating that the cache is incorrect), the Shard Map Manager queries the global shard map to obtain the new correct value for the lookup, update the cache, and obtain and return the appropriate database connection.

仅在应用程序处于联机状态时没有按预期进行分片映射更改的情况下,才使用 ConnectionOptions.NoneUse ConnectionOptions.None only when shard mapping changes are not expected while an application is online. 在该情况下,可以假设缓存的值始终正确,并且可以安全地跳过对目标数据库的额外双向验证调用。In that case, the cached values can be assumed to always be correct, and the extra round-trip validation call to the target database can be safely skipped. 这可以减少数据库流量。That reduces database traffic. 还可以通过配置文件中的某个值设置 connectionOptions ,以指示在此期间是否按预期进行了分片更改。The connectionOptions may also be set via a value in a configuration file to indicate whether sharding changes are expected or not during a period of time.

本示例使用整数键 CustomerID 的值,并使用名为 customerShardMapShardMap 对象。This example uses the value of an integer key CustomerID, using a ShardMap object named customerShardMap.

int customerId = 12345;
int productId = 4321;
// Looks up the key in the shard map and opens a connection to the shard
try (Connection conn = shardMap.openConnectionForKey(customerId, Configuration.getCredentialsConnectionString())) {
    // Create a simple command that will insert or update the customer information
    PreparedStatement ps = conn.prepareStatement("UPDATE Sales.Customer SET PersonID = ? WHERE CustomerID = ?");

    ps.setInt(1, productId);
    ps.setInt(2, customerId);
    ps.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}
int customerId = 12345;
int newPersonId = 4321;

// Connect to the shard for that customer ID. No need to call a SqlConnection
// constructor followed by the Open method.
using (SqlConnection conn = customerShardMap.OpenConnectionForKey(customerId, Configuration.GetCredentialsConnectionString(), ConnectionOptions.Validate))
{
    // Execute a simple command.
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = @"UPDATE Sales.Customer
                        SET PersonID = @newPersonID WHERE CustomerID = @customerID";

    cmd.Parameters.AddWithValue("@customerID", customerId);cmd.Parameters.AddWithValue("@newPersonID", newPersonId);
    cmd.ExecuteNonQuery();
}  

OpenConnectionForKey 方法返回与正确数据库建立的、新的且已打开的连接。The OpenConnectionForKey method returns a new already-open connection to the correct database. 此方法中所采用的连接仍然可以充分利用连接池。Connections utilized in this way still take full advantage of connection pooling.

如果应用程序使用异步编程,也可以使用 OpenConnectionForKeyAsync 方法(Java.NET)。The OpenConnectionForKeyAsync method (Java, .NET) is also available if your application makes use asynchronous programming.

与暂时性故障处理集成Integrating with transient fault handling

在云中开发数据访问应用程序的最佳实践是,确保暂时性故障由应用引起,并且确保在引发错误之前重试几次这些操作。A best practice in developing data access applications in the cloud is to ensure that transient faults are caught by the app, and that the operations are retried several times before throwing an error. 暂时性故障处理(Java.NET)中讨论了云应用程序的暂时性故障处理。Transient fault handling for cloud applications is discussed at Transient Fault Handling (Java, .NET).

暂时性故障处理在本质上可以与数据依赖型路由模式共存。Transient fault handling can coexist naturally with the Data-Dependent Routing pattern. 关键需求是重试整个数据访问请求,包括已获取依赖于数据的路由连接的 using 块。The key requirement is to retry the entire data access request including the using block that obtained the data-dependent routing connection. 可以将上述示例重写为以下内容。The preceding example could be rewritten as follows.

示例 - 数据依赖型路由与暂时性故障处理Example - data-dependent routing with transient fault handling

int customerId = 12345;
int productId = 4321;
try {
    SqlDatabaseUtils.getSqlRetryPolicy().executeAction(() -> {
        // Looks up the key in the shard map and opens a connection to the shard
        try (Connection conn = shardMap.openConnectionForKey(customerId, Configuration.getCredentialsConnectionString())) {
            // Create a simple command that will insert or update the customer information
            PreparedStatement ps = conn.prepareStatement("UPDATE Sales.Customer SET PersonID = ? WHERE CustomerID = ?");

            ps.setInt(1, productId);
            ps.setInt(2, customerId);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    });
} catch (Exception e) {
    throw new StoreException(e.getMessage(), e);
}
int customerId = 12345;
int newPersonId = 4321;

Configuration.SqlRetryPolicy.ExecuteAction(() =&gt;
{
    // Connect to the shard for a customer ID.
    using (SqlConnection conn = customerShardMap.OpenConnectionForKey(customerId, Configuration.GetCredentialsConnectionString(), ConnectionOptions.Validate))
    {
        // Execute a simple command
        SqlCommand cmd = conn.CreateCommand();

        cmd.CommandText = @"UPDATE Sales.Customer
                            SET PersonID = @newPersonID
                            WHERE CustomerID = @customerID";

        cmd.Parameters.AddWithValue("@customerID", customerId);
        cmd.Parameters.AddWithValue("@newPersonID", newPersonId);
        cmd.ExecuteNonQuery();

        Console.WriteLine("Update completed");
    }
});

生成弹性数据库示例应用程序时,会自动下载需要实现暂时性故障处理的程序包。Packages necessary to implement transient fault handling are downloaded automatically when you build the elastic database sample application.

事务一致性Transactional consistency

确保分片的所有局部操作的事务属性。Transactional properties are guaranteed for all operations local to a shard. 例如,通过依赖于数据的路由提交的事务会在目标分片范围内执行以供连接。For example, transactions submitted through data-dependent routing execute within the scope of the target shard for the connection. 此时,没有提供用于将多个连接包含在一个事务中的功能,因此对于在分片上执行的操作,没有事务保证。At this time, there are no capabilities provided for enlisting multiple connections into a transaction, and therefore there are no transactional guarantees for operations performed across shards.

后续步骤Next steps

若要分离分片或重新附加分片,请参阅使用 RecoveryManager 类解决分片映射问题To detach a shard, or to reattach a shard, see Using the RecoveryManager class to fix shard map problems

其他资源Additional resources

尚未使用弹性数据库工具?Not using elastic database tools yet? 请查看入门指南Check out our Getting Started Guide.