管理数据库连接

本页面提供最佳做法和特定语言的代码示例，帮助您创建高效地使用 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

/**  * Insert a vote record into the database.  *  * @param {object} pool The Knex connection object.  * @param {object} vote The vote record to insert.  * @returns {Promise}  */ const insertVote = async (pool, vote) => {   try {     return await pool('votes').insert(vote);   } catch (err) {     throw Error(err);   } };

C#

using Npgsql; using System;  namespace CloudSql {     public class PostgreSqlTcp     {         public static NpgsqlConnectionStringBuilder NewPostgreSqlTCPConnectionString()         {             // Equivalent connection string:             // "Uid=<DB_USER>;Pwd=<DB_PASS>;Host=<INSTANCE_HOST>;Database=<DB_NAME>;"             var connectionString = new NpgsqlConnectionStringBuilder()             {                 // 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.                 Host = Environment.GetEnvironmentVariable("INSTANCE_HOST"),     // e.g. '127.0.0.1'                 // Set Host to 'cloudsql' when deploying to App Engine Flexible environment                 Username = 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 = SslMode.Disable,             };             connectionString.Pooling = true;             // Specify additional properties here.             return connectionString;         }     } }

Go

insertVote := "INSERT INTO votes(candidate, created_at) VALUES($1, 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

// '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 Knex maintains in the pool. // Additional connections will be established to meet this value unless the pool is full. config.pool.min = 5;

C#

// MaxPoolSize sets maximum number of connections allowed in the pool. connectionString.MaxPoolSize = 5; // MinPoolSize sets the minimum number of connections in the pool. connectionString.MinPoolSize = 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

// 'knex' uses a built-in retry strategy which does not implement backoff. // 'createRetryIntervalMillis' is how long to idle after failed connection creation before trying again config.pool.createRetryIntervalMillis = 200; // 0.2 seconds

C#

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

// 'acquireTimeoutMillis' is the number of milliseconds before a timeout occurs when acquiring a // connection from the pool. This is slightly different from connectionTimeout, because acquiring // a pool connection does not always involve making a new connection, and may include multiple retries. // when making a connection config.pool.acquireTimeoutMillis = 60000; // 60 seconds // 'createTimeoutMillis` is the maximum number of milliseconds to wait trying to establish an // initial connection before retrying. // After acquireTimeoutMillis has passed, a timeout exception will be thrown. config.pool.createTimeoutMillis = 30000; // 30 seconds // '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; // 10 minutes

C#

// Timeout sets the time to wait (in seconds) while // trying to establish a connection before terminating the attempt. connectionString.Timeout = 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, ]

连接时长

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

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

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

C#

// ConnectionIdleLifetime sets the time (in seconds) to wait before // closing idle connections in the pool if the count of all // connections exceeds MinPoolSize. connectionString.ConnectionIdleLifetime = 300;

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 编程语言的完整应用。

后续步骤