管理数据库连接

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

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

如需了解运行连接到 Cloud SQL 的示例 Web 应用的分步说明，请点击与您的环境所对应的链接：

连接池

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

打开和关闭连接

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

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, parameters={"time_cast": time_cast, "candidate": team})
        conn.commit()
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.
  String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
  try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
    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 (?, ?)';
  // Pool.query automatically checks out, uses, and releases a connection
  // back into the pool, ensuring it is always returned successfully.
  await pool.query(stmt, [timestamp, team]);
} catch (err) {
  // If something goes wrong, handle the error in this section. This might
  // involve retrying or adjusting parameters depending on the situation.
  // ...
}

C#

using MySql.Data.MySqlClient;
using System;

namespace CloudSql
{
    public class MySqlTcp
    {
        public static MySqlConnectionStringBuilder NewMysqlTCPConnectionString()
        {
            // Equivalent connection string:
            // "Uid=<DB_USER>;Pwd=<DB_PASS>;Host=<INSTANCE_HOST>;Database=<DB_NAME>;"
            var connectionString = new MySqlConnectionStringBuilder()
            {
                // Note: Saving credentials in environment variables is convenient, but not
                // secure - consider a more secure solution such as
                // Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
                // keep secrets safe.
                Server = Environment.GetEnvironmentVariable("INSTANCE_HOST"),   // e.g. '127.0.0.1'
                // Set Host to 'cloudsql' when deploying to App Engine Flexible environment
                UserID = Environment.GetEnvironmentVariable("DB_USER"),   // e.g. 'my-db-user'
                Password = Environment.GetEnvironmentVariable("DB_PASS"), // e.g. 'my-db-password'
                Database = Environment.GetEnvironmentVariable("DB_NAME"), // e.g. 'my-database'

                // The Cloud SQL proxy provides encryption between the proxy and instance.
                SslMode = MySqlSslMode.Disabled,
            };
            connectionString.Pooling = true;
            // Specify additional properties here.
            return connectionString;

        }
    }
}

Go

insertVote := "INSERT INTO votes(candidate, created_at) VALUES(?, NOW())"
_, err := db.Exec(insertVote, team)

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 (NOW(), :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

// 'connectionLimit' is the maximum number of connections the pool is allowed
// to keep at once.
connectionLimit: 5,

C#

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

Go

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

// Set maximum number of open connections to the database.
db.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 mysql module automatically uses exponential delays between failed
// connection attempts.

C#

Policy
    .Handle<MySqlException>()
    .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

// 'connectTimeout' is the maximum number of milliseconds before a timeout
// occurs during the initial connection to the database.
connectTimeout: 10000, // 10 seconds
// 'acquireTimeout' is the maximum number of milliseconds to wait when
// checking out a connection from the pool before a timeout error occurs.
acquireTimeout: 10000, // 10 seconds
// 'waitForConnections' determines the pool's action when no connections are
// free. If true, the request will queued and a connection will be presented
// when ready. If false, the pool will call back with an error.
waitForConnections: true, // Default: true
// 'queueLimit' is the maximum number of requests for connections the pool
// will queue at once before returning an error. If 0, there is no limit.
queueLimit: 0, // Default: 0

C#

// ConnectionTimeout sets the time to wait (in seconds) while
// trying to establish a connection before terminating the attempt.
connectionString.ConnectionTimeout = 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.
[
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]

终止连接

在 Cloud SQL 中拥有 PROCESS 权限的用户可以列出不由自己管理的连接。在 MySQL 5.7.x 中，用户必须拥有 SUPER 权限；在 MySQL 8.0.x 中，用户必须拥有 CONNECTION_ADMIN 权限，才能对这些连接执行 KILL 语句。KILL 语句会终止任何其他 mysql 用户（Cloud SQL 管理员用户除外）的连接。没有这些权限的用户只能列出和终止自己管理的连接。

您可以列出实例的各连接（方法是使用 mysql 客户端并执行 SHOW PROCESSLIST 命令），使用 Id 终止连接。 例如：

mysql> SHOW PROCESSLIST;
mysql> KILL 6;

连接时长

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

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
# re-established
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

“mysql” Node.js 库目前未提供任何功能来控制连接的持续时间。

C#

// ConnectionLifeTime sets the lifetime of a pooled connection
// (in seconds) that a connection lives before it is destroyed
// and recreated. Connections that are returned to the pool are
// destroyed if it's been more than the number of seconds
// specified by ConnectionLifeTime since the connection was
// created. The default value is zero (0) which means the
// connection always returns to pool.
connectionString.ConnectionLifeTime = 1800; // 30 minutes

Go

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

Ruby

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

PHP

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

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

Python

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

Java

查看 Java 编程语言的完整应用。

Node.js

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

C#

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

Go

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

Ruby

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

PHP

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

后续步骤