管理数据库连接

本页面提供最佳做法和特定语言的代码示例,帮助您创建高效地使用 Cloud SQL 数据库连接的应用。

这些示例摘自 GitHub 上提供的完整 App Engine 应用。了解详情

连接池

连接池是数据库连接的缓存,共享和重复使用缓存中的连接可以缩短连接延迟时间并提高性能。当应用需要使用数据库连接时,它会从连接池中暂借一个,并在用完之后放回池中,供下次需要连接时重复使用。

打开和关闭连接

使用连接池时必须正确地打开和关闭连接,以便在用完连接后始终将其放回池中。未放回(也称为“外泄”)的连接无法重复使用,会造成资源浪费并可能导致应用出现性能瓶颈。

Python

# Preparing a statement before hand can help protect against injections.
stmt = sqlalchemy.text(
    "INSERT INTO votes (time_cast, candidate)" " VALUES (:time_cast, :candidate)"
)
try:
    # Using a with statement ensures that the connection is always released
    # back into the pool at the end of statement (even if an error occurs)
    with db.connect() as conn:
        conn.execute(stmt, time_cast=time_cast, candidate=team)
except Exception as e:
    # If something goes wrong, handle the error in this section. This might
    # involve retrying or adjusting parameters depending on the situation.
    # ...

Java

// Using a try-with-resources statement ensures that the connection is always released back
// into the pool at the end of the statement (even if an error occurs)
try (Connection conn = pool.getConnection()) {

  // PreparedStatements can be more efficient and project against injections.
  PreparedStatement voteStmt = conn.prepareStatement(
      "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);");
  voteStmt.setTimestamp(1, now);
  voteStmt.setString(2, team);

  // Finally, execute the statement. If it fails, an error will be thrown.
  voteStmt.execute();

} catch (SQLException ex) {
  // If something goes wrong, handle the error in this section. This might involve retrying or
  // adjusting parameters depending on the situation.
  // ...
}

Node.js

try {
  const stmt =
    'INSERT INTO votes (time_cast, candidate) VALUES (@timestamp, @team)';
  // Using a prepared statement protects against SQL injection attacks.
  // When prepare is called, a single connection is acquired from the connection pool
  // and all subsequent executions are executed exclusively on this connection.
  const ps = new mssql.PreparedStatement(pool);
  ps.input('timestamp', mssql.DateTime);
  ps.input('team', mssql.VarChar(6));
  await ps.prepare(stmt);
  await ps.execute({
    timestamp: timestamp,
    team: team,
  });
  await ps.unprepare();
} catch (err) {
  // If something goes wrong, handle the error in this section. This might
  // involve retrying or adjusting parameters depending on the situation.
  // ...
}

C#

insertTimestamp = DateTime.UtcNow;
try
{
    using(var connection = new SqlConnection(_connectionString.ConnectionString))
    {
        connection.OpenWithRetry();
        // Insert a vote for SPACE or TAB with a timestamp.
        using (var insertVoteCommand = connection.CreateCommand())
        {
            insertVoteCommand.CommandText =
                @"INSERT INTO votes (candidate, time_cast) VALUES (@candidate, @time_cast)";
            var candidate = insertVoteCommand.CreateParameter();
            candidate.ParameterName = "@candidate";
            candidate.DbType = DbType.String;
            candidate.Value = team;
            insertVoteCommand.Parameters.Add(candidate);
            var timeCast = insertVoteCommand.CreateParameter();
            timeCast.ParameterName = "@time_cast";
            timeCast.DbType = DbType.DateTime;
            timeCast.Value = insertTimestamp;
            insertVoteCommand.Parameters.Add(timeCast);
            await insertVoteCommand.ExecuteNonQueryAsync();
        }
    }
    return Content($"Vote successfully cast for '{team}' at time {insertTimestamp}!");
}
catch (Exception ex)
{
    // If something goes wrong, handle the error in this
    // section. This might involve retrying or adjusting
    // parameters depending on the situation.
    return StatusCode((int)HttpStatusCode.InternalServerError, ex);
}

Go

sqlInsert := "INSERT INTO votes (candidate, time_cast) VALUES (?, GETDATE())"
if team == "TABS" || team == "SPACES" {
	if _, err := app.db.Exec(sqlInsert, team); err != nil {
		fmt.Fprintf(w, "unable to save vote: %s", err)
		return fmt.Errorf("DB.Exec: %v", err)
	}
	fmt.Fprintf(w, "Vote successfully cast for %s!\n", team)
}
return nil

Ruby

@vote = Vote.new candidate: candidate

# ActiveRecord creates and executes your SQL and automatically
# handles the opening and closing of the database connection.
if @vote.save
  render json: "Vote successfully cast for \"#{@vote.candidate}\" at #{@vote.time_cast} PST!"
else
  render json: @vote.errors, status: :unprocessable_entity
end

PHP

// Use prepared statements to guard against SQL injection.
$sql = "INSERT INTO votes (time_cast, candidate) VALUES (GETDATE(), :voteValue)";

try {
    $statement = $conn->prepare($sql);
    $statement->bindParam('voteValue', $value);

    $res = $statement->execute();
} catch (PDOException $e) {
    throw new RuntimeException(
        "Could not insert vote into database. The PDO exception was " .
        $e->getMessage(),
        $e->getCode(),
        $e
    );
}

连接数

每个数据库连接都会使用客户端和服务器端资源。此外,Cloud SQL 存在一个不能超出的总连接数量限制。创建和使用的连接越少,开销就越低,还有助于确保不超过连接数上限。

Python

# Pool size is the maximum number of permanent connections to keep.
"pool_size": 5,
# Temporarily exceeds the set pool_size if no connections are available.
"max_overflow": 2,
# The total number of concurrent connections for your application will be
# a total of pool_size and max_overflow.

Java

// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
// values for this setting are highly variable on app design, infrastructure, and database.
config.setMaximumPoolSize(5);
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.setMinimumIdle(5);

Node.js

  // 'max' limits the total number of concurrent connections this pool will keep. Ideal
  // values for this setting are highly variable on app design, infrastructure, and database.
  (config.pool.max = 5);
// 'min' is the minimum number of idle connections maintained in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.pool.min = 1;

C#

// MaximumPoolSize sets maximum number of connections allowed in the pool.
connectionString.MaxPoolSize = 5;
// MinimumPoolSize sets the minimum number of connections in the pool.
connectionString.MinPoolSize = 0;

Go


// Set maximum number of connections in idle connection pool.
dbPool.SetMaxIdleConns(5)

// Set maximum number of open connections to the database.
dbPool.SetMaxOpenConns(7)

Ruby

# 'pool' is the maximum number of permanent connections to keep.
pool: 5

PHP

PDO 目前不提供任何功能来配置连接限制。

指数退避算法

如果您的应用尝试连接到数据库但未成功,则表示数据库可能暂时不可用。在这种情况下,同时发送过多的连接请求可能会浪费更多的数据库资源并增加恢复所需的时间。使用指数退避算法可防止应用在无法连接到数据库时发送无响应的连接请求数。

只有在首次连接或首次从池中获取连接时,这种重试才有意义。如果错误发生在事务的中间,应用必须执行重试,并且必须从事务的开始重试。因此,即使您的池配置正确,如果连接丢失,应用仍可能会遇到错误。

Python

# SQLAlchemy automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

Java

// Hikari automatically delays between failed connection attempts, eventually reaching a
// maximum delay of `connectionTimeout / 2` between attempts.

Node.js

// The node-mssql module uses a built-in retry strategy which does not implement backoff.
// 'createRetryIntervalMillis' is the number of milliseconds to wait in between retries.
config.pool.createRetryIntervalMillis = 200;

C#

Policy
    .Handle<SqlException>()
    .WaitAndRetry(new[]
    {
        TimeSpan.FromSeconds(1),
        TimeSpan.FromSeconds(2),
        TimeSpan.FromSeconds(5)
    })
    .Execute(() => connection.Open());

Go

database/sql 软件包目前未提供任何用于配置指数退避算法的功能。

Ruby

# ActiveRecord automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

PHP

PDO 目前未提供任何用于配置指数退避算法的功能。

连接超时

尝试连接时失败的原因有很多。可能是网络通信出了问题,也可能是数据库暂时无法响应。请确保您的应用可以正常处理连接中断或连接失败的情况。

Python

# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# exception will be thrown.
"pool_timeout": 30,  # 30 seconds

Java

// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
// SQLException.
config.setConnectionTimeout(10000); // 10 seconds
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
config.setIdleTimeout(600000); // 10 minutes

Node.js

// 'connectionTimeout` is the maximum number of milliseconds to wait trying to establish an
// initial connection. After the specified amount of time, an exception will be thrown.
config.connectionTimeout = 30000;
// 'acquireTimeoutMillis' is the number of milliseconds before a timeout occurs when acquiring a
// connection from the pool.
config.pool.acquireTimeoutMillis = 30000;
// 'idleTimeoutMillis' is the number of milliseconds a connection must sit idle in the pool
// and not be checked out before it is automatically closed
(config.pool.idleTimeoutMillis = 600000),

C#

// ConnectionTimeout sets the time to wait (in seconds) while
// trying to establish a connection before terminating the attempt.
connectionString.ConnectTimeout = 15;

Go

database/sql 软件包目前不提供任何用来配置连接超时的功能。在驱动程序级别配置超时。

Ruby

# 'timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# ActiveRecord::ConnectionTimeoutError will be raised.
timeout: 5000

PHP

// Here we set the connection timeout to five seconds and ask PDO to
// throw an exception if any errors occur.
$connConfig = [
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

连接时长

限制连接的生命周期长度有助于防止被抛弃的连接不断累加。您可以使用连接池来限制连接的生命周期。

Python

# 'pool_recycle' is the maximum number of seconds a connection can persist.
# Connections that live longer than the specified amount of time will be
# reestablished
"pool_recycle": 1800,  # 30 minutes

Java

// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
// live longer than this many milliseconds will be closed and reestablished between uses. This
// value should be several minutes shorter than the database's timeout value to avoid unexpected
// terminations.
config.setMaxLifetime(1800000); // 30 minutes

Node.js

node-mssql”Node.js 库目前未提供任何功能来控制连接时长。

C#

// ADO.NET connection pooler removes a connection
// from the pool after it's been idle for approximately
// 4-8 minutes, or if the pooler detects that the
// connection with the server no longer exists.

Go


// Set Maximum time (in seconds) that a connection can remain open.
dbPool.SetConnMaxLifetime(1800)

Ruby

ActiveRecord 目前未提供任何功能来控制连接时长。

PHP

PDO 目前未提供任何功能来控制连接时长。

要查看完整应用,请点击下面的链接。

Python

查看 Python 编程语言的完整应用

Node.js

查看 Node.js 编程语言的完整应用

C#

查看 C# 编程语言版本的完整应用

Go

查看 Go 编程语言版本的完整应用

PHP

查看 PHP 编程语言的完整应用

后续步骤