GoogleSQL 方言数据库中的提交时间戳

本主题介绍了如何为使用 Spanner 执行的每个插入和更新操作编写提交时间戳。要使用此功能，请在 TIMESTAMP 列设置 allow_commit_timestamp 选项，然后写入时间戳，将其作为每个事务的一部分。

概览

基于 TrueTime 技术的提交时间戳是在数据库中提交事务的时间。allow_commit_timestamp 列选项允许您以原子方式将提交时间戳存储到列中。借助存储在表中的提交时间戳，您可以确定变更的确切顺序并构建更改日志等功能。

要在数据库中插入提交时间戳，请完成以下步骤：

  1. 创建一个带有类型 TIMESTAMP 的列，并在架构定义中将列选项 allow_commit_timestamp 设置为 true。例如：

    CREATE TABLE Performances (
    ...
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
    ...
) PRIMARY KEY (...);

  2. 如果要使用 DML 执行插入或更新操作，请使用 PENDING_COMMIT_TIMESTAMP 函数来写入提交时间戳。

    如果要通过变更执行插入或更新操作，请在插入或更新提交时间戳列时使用占位符字符串 spanner.commit_timestamp()。您还可以使用客户端库提供的提交时间戳常量。例如，Java 客户端中的此常量为 Value.COMMIT_TIMESTAMP。

当 Spanner 使用这些占位符作为列值提交事务时，实际提交时间戳会写入指定列（例如：LastUpdateTime 列）。然后，您可以使用此列值来创建表的更新历史记录。

提交时间戳的值不保证是唯一的。写入不重叠字段集的事务可能具有相同的时间戳。写入重叠字段集的事务具有唯一的时间戳。

Spanner 提交时间戳以微秒为单位，当存储在 TIMESTAMP 列中时会转换为纳秒。

创建和删除提交时间戳列

使用 allow_commit_timestamp 列选项来添加和移除对提交时间戳的支持：

  • 创建新表以指定列支持提交时间戳，请执行以下操作。
  • 更改现有表时，请执行以下操作：
    • 添加支持提交时间戳的新列，
    • 更改现有 TIMESTAMP 列以支持提交时间戳，
    • 更改现有 TIMESTAMP 列以移除提交时间戳支持，

键和索引

您可以使用提交时间戳列作为主键列或非键列。主键可以定义为 ASC 或 DESC。

  • ASC（默认）- 升序键适用于解答从特定时间往前的查询。
  • DESC - 降序键将最新的行保留在表的顶部，可提供对最近记录的快速访问。

allow_commit_timestamp 选项在父表和子表的主键之间必须保持一致。如果该选项在主键之间不一致，则 Spanner 会返回错误。只有在创建或更新架构的时候，该选项可以不一致。

在以下情况下使用提交时间戳会引发热点，这会降低数据的性能：

  • 提交时间戳列是表的主键的第一部分：

    CREATE TABLE Users (
  LastAccess TIMESTAMP NOT NULL,
  UserId     INT64 NOT NULL,
  ...
) PRIMARY KEY (LastAccess, UserId);

  • 提交时间戳列是二级索引的主键的第一部分：

    CREATE INDEX UsersByLastAccess ON Users(LastAccess)

    或

    CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)

出现热点后，即使写入速率较低，也会降低数据的性能。如果在没有索引的非键列上启用提交时间戳，则不会产生任何性能开销。

创建提交时间戳列

以下 DDL 使用支持提交时间戳的列创建一个表。

CREATE TABLE Performances (
    SingerId        INT64 NOT NULL,
    VenueId         INT64 NOT NULL,
    EventDate       Date,
    Revenue         INT64,
    LastUpdateTime  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE

添加选项会更改时间戳列，如下所示：

  • 可以使用 spanner.commit_timestamp() 占位符字符串（或由客户端库提供的常量）进行插入和更新。
  • 该列只能包含过去的值。如需了解详情，请参阅为时间戳提供自己的值。

选项 allow_commit_timestamp 区分大小写。

将提交时间戳列添加到现有表

要将提交时间戳列添加到现有表中，请使用 ALTER TABLE 语句。例如，要将 LastUpdateTime 列添加到 Performances 表中，请使用以下语句：

ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
    NOT NULL OPTIONS (allow_commit_timestamp=true)

将时间戳列转换为提交时间戳列

您可以将现有时间戳列转换为提交时间戳列，但这样做需要 Spanner 验证现有时间戳值是否为过去的时间。例如：

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=true)

您不能更改包含 SET OPTIONS 的 ALTER TABLE 语句中某一列的数据类型或 NULL 注解。如需了解详情，请参阅数据定义语言。

移除提交时间戳选项

如果您想从列中删除提交时间戳支持，请在 ALTER TABLE 语句中使用 allow_commit_timestamp=null 选项。提交时间戳操作被移除后，该列仍然是时间戳。更改选项不会更改该列的任何其他特性，例如类型或可为空性 (NOT NULL)。例如：

ALTER TABLE Performances ALTER COLUMN LastUpdateTime
    SET OPTIONS (allow_commit_timestamp=null)

使用 DML 语句写入提交时间戳

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

以下 DML 语句使用提交时间戳更新 Performances 表中的 LastUpdateTime 列：

UPDATE Performances SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

以下代码示例使用 PENDING_COMMIT_TIMESTAMP 函数在 LastUpdateTime 列中写入提交时间戳。

C++

void DmlStandardUpdateWithTimestamp(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()"
                "  WHERE SingerId = 1"));
        if (!update) return std::move(update).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Update was successful "
            << "[spanner_dml_standard_update_with_timestamp]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingDmlWithTimestampCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlWithTimestampCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1");
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

func updateUsingDMLWithTimestamp(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 {
		stmt := spanner.Statement{
			SQL: `UPDATE Albums
				SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
				WHERE SingerId = 1`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDmlWithTimestamp(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        String sql =
            "UPDATE Albums "
                + "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1";
        long rowCount = transaction.executeUpdate(Statement.of(sql));
        System.out.printf("%d records updated.\n", rowCount);
        return null;
      });
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Albums
        SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
        WHERE SingerId = 1`,
    });

    console.log(`Successfully updated ${rowCount} records.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;

/**
 * Update data with a DML statement using timestamps.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_timestamp(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $rowCount = $t->executeUpdate(
            'UPDATE Albums '
            . 'SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1');
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

# 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_albums(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Albums "
        "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
        "WHERE SingerId = 1"
    )

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

database.run_in_transaction(update_albums)

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1"
  )
end

puts "#{row_count} records updated."

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

commit_timestamp = client.commit_timestamp

client.commit do |c|
  c.update "Albums", [
    { SingerId: 1, AlbumId: 1, MarketingBudget: 100_000, LastUpdateTime: commit_timestamp },
    { SingerId: 2, AlbumId: 2, MarketingBudget: 750_000, LastUpdateTime: commit_timestamp }
  ]
end

puts "Updated data"

提交时间戳只能写入包含 allow_commit_timestamp=true 选项注释的列。

如果在多个表的行中存在变更，则必须为每个表的提交时间戳列指定 spanner.commit_timestamp()（或客户端库常量）。

查询提交时间戳列

以下示例展示了如何查询表的提交时间戳列。

C++

void QueryDataWithTimestamp(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime"
      "  FROM Albums"
      " ORDER BY LastUpdateTime DESC");
  using RowType =
      std::tuple<std::int64_t, std::int64_t, absl::optional<std::int64_t>,
                 absl::optional<spanner::Timestamp>>;

  auto rows = client.ExecuteQuery(std::move(select));
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << std::get<0>(*row) << " " << std::get<1>(*row);
    auto marketing_budget = std::get<2>(*row);
    if (!marketing_budget) {
      std::cout << " NULL";
    } else {
      std::cout << ' ' << *marketing_budget;
    }
    auto last_update_time = std::get<3>(*row);
    if (!last_update_time) {
      std::cout << " NULL";
    } else {
      std::cout << ' ' << *last_update_time;
    }
    std::cout << "\n";
  }
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithTimestampColumnAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public DateTime? LastUpdateTime { get; set; }
        public long? MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithTimestampColumnAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums ORDER BY LastUpdateTime DESC");

        var albums = new List<Album>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            albums.Add(new Album
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                AlbumId = reader.GetFieldValue<int>("AlbumId"),
                LastUpdateTime = reader.IsDBNull(reader.GetOrdinal("LastUpdateTime")) ? (DateTime?)null : reader.GetFieldValue<DateTime>("LastUpdateTime"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

Go


import (
	"context"
	"fmt"
	"io"
	"strconv"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func queryWithTimestamp(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{
		SQL: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
				FROM Albums ORDER BY LastUpdateTime DESC`}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var singerID, albumID int64
		var marketingBudget spanner.NullInt64
		var lastUpdateTime spanner.NullTime
		if err := row.ColumnByName("SingerId", &singerID); err != nil {
			return err
		}
		if err := row.ColumnByName("AlbumId", &albumID); err != nil {
			return err
		}
		if err := row.ColumnByName("MarketingBudget", &marketingBudget); err != nil {
			return err
		}
		budget := "NULL"
		if marketingBudget.Valid {
			budget = strconv.FormatInt(marketingBudget.Int64, 10)
		}
		if err := row.ColumnByName("LastUpdateTime", &lastUpdateTime); err != nil {
			return err
		}
		timestamp := "NULL"
		if lastUpdateTime.Valid {
			timestamp = lastUpdateTime.String()
		}
		fmt.Fprintf(w, "%d %d %s %s\n", singerID, albumID, budget, timestamp)
	}
}

Java

static void queryMarketingBudgetWithTimestamp(DatabaseClient dbClient) {
  // Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
  // null. A try-with-resource block is used to automatically release resources held by
  // ResultSet.
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement.of(
                  "SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime FROM Albums"
                      + " ORDER BY LastUpdateTime DESC"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %s %s\n",
          resultSet.getLong("SingerId"),
          resultSet.getLong("AlbumId"),
          // We check that the value is non null. ResultSet getters can only be used to retrieve
          // non null values.
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"),
          resultSet.isNull("LastUpdateTime") ? "NULL" : resultSet.getTimestamp("LastUpdateTime"));
    }
  }
}

Node.js

// ...

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
          FROM Albums ORDER BY LastUpdateTime DESC`,
};

// Queries rows from the Albums table
try {
  const [rows] = await database.run(query);

  rows.forEach(row => {
    const json = row.toJSON();

    console.log(
      `SingerId: ${json.SingerId}, AlbumId: ${
        json.AlbumId
      }, MarketingBudget: ${
        json.MarketingBudget ? json.MarketingBudget : null
      }, LastUpdateTime: ${json.LastUpdateTime}`
    );
  });
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Queries sample data from a database with a commit timestamp column.
 *
 * This sample uses the `MarketingBudget` column. You can add the column
 * by running the `add_column` sample or by running this DDL statement against
 * your database:
 *
 *      ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * This sample also uses the 'LastUpdateTime' commit timestamp column. You can
 * add the column by running the `add_timestamp_column` sample or by running
 * this DDL statement against your database:
 *
 * 		ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)
 *
 * Example:
 * ```
 * query_data_with_timestamp_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function query_data_with_timestamp_column(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        'SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime ' .
        ' FROM Albums ORDER BY LastUpdateTime DESC'
    );

    foreach ($results as $row) {
        if ($row['MarketingBudget'] == null) {
            $row['MarketingBudget'] = 'NULL';
        }
        if ($row['LastUpdateTime'] == null) {
            $row['LastUpdateTime'] = 'NULL';
        }
        printf('SingerId: %s, AlbumId: %s, MarketingBudget: %s, LastUpdateTime: %s' . PHP_EOL,
            $row['SingerId'], $row['AlbumId'], $row['MarketingBudget'], $row['LastUpdateTime']);
    }
}

Python

def query_data_with_timestamp(instance_id, database_id):
    """Queries sample data from the database using SQL.

    This updates the `LastUpdateTime` column which must be created before
    running this sample. You can add the column by running the
    `add_timestamp_column` sample or by running this DDL statement
    against your database:

        ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
        OPTIONS (allow_commit_timestamp=true)

    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(database_id)

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT SingerId, AlbumId, MarketingBudget FROM Albums "
            "ORDER BY LastUpdateTime DESC"
        )

    for row in results:
        print("SingerId: {}, AlbumId: {}, MarketingBudget: {}".format(*row))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

client.execute("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
                FROM Albums ORDER BY LastUpdateTime DESC").rows.each do |row|
  puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:MarketingBudget]} #{row[:LastUpdateTime]}"
end

为提交时间戳列提供您自己的值

您可以为提交时间戳列提供自己的值，而非传递 spanner.commit_timestamp()（或客户端库常量）作为列值。该值必须为过去的时间戳。此限制可确保写入时间戳的操作成本低且速度快。如果指定了未来的时间戳，服务器会返回 FailedPrecondition 错误。

创建更新日志

假设您希望创建一个关于发生在表中的每个变更的更改日志，然后使用该更改日志进行审核。例如一个用于存储字处理文档的更改记录的表。提交时间戳使得创建更改日志更加容易，因为时间戳可以强制对更改日志条目进行排序。您可以使用类似以下示例的架构构建一个更改日志，以便将更改记录存储到指定的文档中：

CREATE TABLE Documents (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Contents   STRING(MAX) NOT NULL,
) PRIMARY KEY (UserId, DocumentId);

CREATE TABLE DocumentHistory (
  UserId     INT64 NOT NULL,
  DocumentId INT64 NOT NULL,
  Ts         TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Delta      STRING(MAX),
) PRIMARY KEY (UserId, DocumentId, Ts),
  INTERLEAVE IN PARENT Documents ON DELETE NO ACTION;

要创建一个更新日志，请在为 Document 插入或更新某一行的相同事务中为 DocumentHistory 插入一个新行。在插入 DocumentHistory 中的新行时，使用占位符 spanner.commit_timestamp()（或客户端库常量）告知 Spanner 将提交时间戳写入 Ts 列。将 DocumentsHistory 表与 Documents 表交错将实现数据局部性及更高效的插入和更新。但是，它也存在必须一起删除父行和子行的限制。若要在 DocumentHistory 中的行删除后保留 Documents 中的行，请不要交错表。

使用提交时间戳优化近期数据查询

提交时间戳支持 Spanner 优化，可以在检索在特定时间之后写入的数据时减少查询 I/O。

如需激活此优化，查询的 WHERE 子句必须包含表的提交时间戳列与您提供的特定时间之间的比较，具有以下属性：

  • 请以常量表达式的形式提供特定时间：字面量、参数或其参数求值为常量的函数。

  • 通过 > 或 >= 运算符比较提交时间戳是否晚于指定时间。

  • （可选）使用 AND 向 WHERE 子句添加进一步限制。使用 OR 扩展该子句会使查询失去此优化的资格。

例如，请参考以下 Performances 表，其中包含一个提交时间戳列：

CREATE TABLE Performances (
    SingerId INT64 NOT NULL,
    VenueId INT64 NOT NULL,
    EventDate DATE,
    Revenue INT64,
    LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate);

此查询受益于前面介绍的提交时间戳优化，因为它在表的提交时间戳列和常量表达式（在本例中为字面量）之间具有大于或等于的比较：

SELECT * FROM Performances WHERE LastUpdateTime >= "2022-05-01";

以下查询也符合优化条件，因为它在提交时间戳与函数（其参数在查询执行期间全部求值为常量）之间具有大于比较的结果：

SELECT * FROM Performances
  WHERE LastUpdateTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

后续步骤