数据操纵语言最佳做法

本页面介绍了针对 GoogleSQL 方言数据库和 PostgreSQL 方言数据库使用数据操纵语言 (DML) 和分区 DML 的最佳实践。

使用 WHERE 子句缩小锁定范围

在读写事务中执行 DML 语句。当 Spanner 读取数据时，它会对其读取的有限范围的行获取共享读取锁定。具体而言，它仅会对您访问的列获取这些锁定。锁定可能包含不满足 WHERE 子句的过滤条件的数据。

当 Spanner 使用 DML 语句修改数据时，它会对您所修改的特定数据获取独占锁定。此外，它还会采用与读取数据时相同的方式获取共享锁定。如果您的请求包含大范围的行或整个表，则共享锁定可能会阻止其他事务并行执行。

为尽可能高效地修改数据，请使用 WHERE 子句，以使 Spanner 只读取必要的行。您可以通过按主键进行过滤或按二级索引的键进行过滤来实现此目标。WHERE 子句限制了共享锁定的范围，使 Spanner 能够更高效地处理更新。

例如，假设 Singers 表中的某位音乐人更改了其名字，则您需要在您的数据库中更新该名字。您可以执行以下 DML 语句，但该 DML 语句会强制 Spanner 扫描整个表，并获取共享锁定以覆盖整个表。因此，Spanner 必须读取数据超出需要的数据，因此并发事务不能并行修改数据：

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

为使该更新更加高效，请在 WHERE 子句中添加 SingerId 列。SingerId 列是 Singers 表的唯一主键列：

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

如果没有用于 FirstName 或 LastName 的索引，您需要扫描整个表以查找目标歌手。如果您不想添加二级索引来提高更新效率，请在 WHERE 子句中添加 SingerId 列。

SingerId 列是 Singers 表的唯一主键列。如需查找该列，请在更新事务之前，在单独的只读事务中运行 SELECT：


  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

避免在同一事务中使用 DML 语句和变更

Spanner 可以在服务器端缓冲使用 DML 语句执行的插入、更新和删除，并且结果对同一事务中的后续 SQL 和 DML 语句可见。此行为与 Mutation API 不同，使用 Mutation API 时，Spanner 在客户端缓冲变更，并将变更作为提交操作的一部分发送至服务器端。因此，提交请求中的变更对同一事务中的 SQL 或 DML 语句不可见。

避免在同一事务中同时使用 DML 语句和变更。如果您在同一事务中使用这两者，则需要在客户端库代码中考虑执行顺序。如果事务在同一请求中同时包含 DML 语句和变更，则 Spanner 会在变更之前执行 DML 语句。

对于仅支持使用变更的操作，您可能希望在同一事务中组合 DML 语句和变更，例如 insert_or_update。

如果同时使用两者，则缓冲区仅在事务的最后写入数据。

使用 PENDING_COMMIT_TIMESTAMP 函数写入提交时间戳

GoogleSQL

使用 PENDING_COMMIT_TIMESTAMP 函数在 DML 语句中写入提交时间戳。Spanner 会在事务提交时选择提交时间戳。

PostgreSQL

使用 SPANNER.PENDING_COMMIT_TIMESTAMP() 函数在 DML 语句中写入提交时间戳。Spanner 会在事务提交时选择提交时间戳。

分区 DML 以及日期和时间戳函数

分区 DML 使用可能在不同时间运行和提交的一个或多个事务。如果使用日期或时间戳函数，则修改的行可能包含不同的值。

通过批处理 DML 缩短延迟时间

为了缩短延迟时间，请使用批处理 DML 在单个客户端-服务器往返期间向 Spanner 发送多个 DML 语句。

批处理 DML 可对一个批次中的语句组应用优化，从而实现更快、更高效的数据更新。

  • 通过单个请求执行写入操作

    如果连续的类似 INSERT、UPDATE 或 DELETE 批量语句具有不同的参数值，并且不违反数据依赖项，Spanner 会自动对其进行优化。

    例如，设想一个场景，您想要将大量新行插入名为 Albums 的表中。为了让 Spanner 将所有必需的 INSERT 语句优化为单个高效的服务器端操作，应先编写使用 SQL 查询参数的相应 DML 语句：

    INSERT INTO Albums (SingerId, AlbumId, AlbumTitle) VALUES (@Singer, @Album, @Title);

    然后，向 Spanner 发送一项 DML 批处理操作，用于连续地重复调用此语句；重复仅在您绑定到语句的三个查询参数的值方面不同。Spanner 会将这些结构相同的 DML 语句优化为单个服务器端操作，然后再执行该操作。

  • 并行执行写入操作

    当并行执行不会违反数据依赖关系时，Spanner 会自动优化连续的 DML 语句组。此优化可为更广泛的批处理 DML 语句带来性能优势，因为它可以应用于混合 DML 语句类型（INSERT、UPDATE 和 DELETE），以及参数化或非参数化 DML 语句。

    例如，我们的示例架构包含 Singers、Albums 和 Accounts 表。Albums 在 Singers 中交织，并存储有关 Singers 的专辑信息。以下连续的语句组会将新行写入多个表，并且没有复杂的数据依赖关系。

    INSERT INTO Singers (SingerId, Name) VALUES(1, "John Doe");
INSERT INTO Singers (SingerId, Name) VALUES(2, "Marcel Richards");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10001, "Album 1");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (1, 10002, "Album 2");
INSERT INTO Albums(SingerId, AlbumId, AlbumTitle) VALUES (2, 10001, "Album 1");
UPDATE Accounts SET Balance = 100 WHERE AccountId = @AccountId;

    Spanner 通过并行执行这组 DML 语句来优化它们。写入操作会按批处理中的语句顺序应用，并且如果语句在执行期间失败，写入操作会保持批处理 DML 语义。

在 JDBC 中启用客户端批处理

对于使用 Spanner 支持的 JDBC 驱动程序的 Java 应用，您可以通过启用客户端 DML 批处理来缩短延迟时间。JDBC 驱动程序具有一个名为 auto_batch_dml 的连接属性，启用该属性后，系统会在客户端上缓冲 DML 语句，然后将它们作为单个批次发送到 Spanner。这样可以减少往返服务器的次数，并提高整体性能。

默认情况下，auto_batch_dml 设置为 false。 您可以在 JDBC 连接字符串中将该属性设置为 true 来启用它。

例如：

String url = "jdbc:cloudspanner:/projects/my-project/instances/my-instance/databases/my-database;auto_batch_dml=true";
try (Connection connection = DriverManager.getConnection(url)) {
    // Include your DML statements for batching here
}

启用此连接属性后，当执行非 DML 语句或提交当前事务时，Spanner 会将缓冲的 DML 语句作为批次发送。此属性仅适用于读写事务；自动提交模式下的 DML 语句会直接执行。

默认情况下，缓冲 DML 语句的更新计数设置为 1。您可以通过将 auto_batch_dml_update_count 连接变量设置为其他值来更改此设置。如需了解详情，请参阅 JDBC 支持的连接属性。

使用 last_statement 选项可缩短 DML 延迟时间

如果读写事务中的最后一个语句是 DML 语句，您可以使用 last_statement 查询选项来缩短延迟时间。此选项适用于 executeSql 和 executeStreamingSql 查询 API。

使用此选项会将一些验证步骤（例如唯一性限制验证）推迟到提交事务之后进行。使用 last_statement 时，同一事务中的后续操作（例如读取、查询和 DML）会被拒绝。此选项与突变不兼容。如果您在同一事务中包含突变，Spanner 会返回错误。

以下客户端库支持 last_statement 选项：

  • Go 1.77.0 版或更高版本
  • Java 2.27.0 版或更高版本
  • Python 3.53.0 版或更高版本
  • PGAdapter 0.45.0 版或更高版本

在以下驱动程序中使用自动提交模式时，系统会默认支持并启用该属性：

Go

GoogleSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func updateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

PostgreSQL 

import (
    "context"
    "fmt"
    "io"

    "cloud.google.com/go/spanner"
)

// Updates a row while also setting the update DML as the last
// statement.
func pgUpdateDmlWithLastStatement(w io.Writer, db string) error {
    ctx := context.Background()
    client, err := spanner.NewClient(ctx, db)
    if err != nil {
        return err
    }
    defer client.Close()

    _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // other statements for the transaction if any.

        updateStmt := spanner.Statement{
            SQL: `UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214`,
        }
        opts := spanner.QueryOptions{LastStatement: true}
        updateRowCount, err := txn.UpdateWithOptions(ctx, updateStmt, opts)
        if err != nil {
            return err
        }
        fmt.Fprintf(w, "%d record(s) updated.\n", updateRowCount)
        return nil
    })
    if err != nil {
        return err
    }

    return nil
}

Java

GoogleSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of(
                    "UPDATE Singers SET Singers.LastName = 'Doe' WHERE SingerId = 54213\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

PostgreSQL 

static void UpdateUsingLastStatement(DatabaseClient client) {
    client
        .readWriteTransaction()
        .run(
            transaction -> {
            // other statements for the transaction if any.

            // Pass in the `lastStatement` option to the last DML statement of the transaction.
            transaction.executeUpdate(
                Statement.of("UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214\n"),
                Options.lastStatement());
            System.out.println("Singer last name updated.");

            return null;
            });
}

Python

GoogleSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# [START spanner_dml_last_statement]
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54213",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)

PostgreSQL 

def dml_last_statement_option(instance_id, database_id):
"""Updates using DML where the update set the last statement option."""
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

def update_singers(transaction):
    # other statements for the transaction if any.

    update_row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Doe' WHERE SingerId = 54214",
        last_statement=True)

    print("{} record(s) updated.".format(update_row_ct))

database.run_in_transaction(update_singers)