管理資料庫連線

此頁面提供最佳做法和特定程式設計語言的程式碼範例，幫助您建立應用程式，有效率地使用 Cloud SQL 資料庫連線。

這些範例是完整網頁應用程式的摘錄內容，您可以在 GitHub 上取得完整應用程式。瞭解詳情。

如需逐步操作說明，瞭解如何執行連線至 Cloud SQL 的範例網頁應用程式，請點選下列適用於您環境的連結：

連線集區

連線集區是資料庫連線的快取，系統會共用和重複使用此快取，藉此縮短連線延遲時間和改善連線效率。當您的應用程式需要資料庫連線時，會從集區暫時借出連線；使用完畢後，應用程式會將連線還給集區，以便下次需要資料庫連線時能重複使用。

開啟及關閉連線

您使用連線集區時，必須正確開啟和關閉連線，以確保連線在您每次使用完畢後都會回到集區。未回到集區的連線 (或稱「外洩」連線) 將無法重複使用，因而造成資源浪費，也可能導致應用程式效能瓶頸。

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 程式設計語言撰寫的完整應用程式。

後續步驟