SQL 最佳做法

如查询执行计划中所述，SQL 编译器 将 SQL 语句转换为查询执行计划，即 用于获取查询结果。本页面介绍了 构建 SQL 语句以帮助 Spanner 找到高效的执行 方案。

本页面中显示的示例 SQL 语句使用以下示例架构：

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

如需查看完整的 SQL 参考文档，请参阅语句语法、函数和运算符以及词汇结构和语法。

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

如需了解详情，请参阅 Spanner 中的 PostgreSQL 语言。

使用查询参数

Spanner 支持查询参数，以提高性能并提供帮助 在使用用户输入构建查询时防止 SQL 注入。您可以 使用查询参数来代替任意表达式，但不能作为 会替代标识符、列名称、表格名称或 查询。

形参可以出现在需要字面量值的任何位置。相同 参数名称可以在单个 SQL 语句中多次使用。

总的来说，查询参数通过以下方式支持查询执行：

• 预先优化的计划： 参数化可以简化 Spanner 缓存执行计划。
• 简化的查询组合：在以下情况下，您无需转义字符串值： 在查询参数中提供它们。查询参数还可以降低语法错误的风险。
• 安全性：查询参数保护您，使您的查询更安全 抵御各种 SQL 注入攻击这种保护对于根据用户输入构造的查询尤其重要。

了解 Spanner 如何执行查询

注意 ：Spanner Studio（在 Google Cloud 控制台中以前标记为 Query） 在单个编辑器中支持 SQL、DML 和 DDL 操作。如需更多信息 请参阅使用 Google Cloud 控制台管理您的数据。

Spanner 支持使用声明式 SQL 语句来查询数据库 并指定要检索哪些数据如果您想了解 Spanner 获取结果，检查查询的执行计划。答 查询执行计划显示了与每个步骤相关的计算开销 。利用这些开销信息，您可以调试查询性能问题并 优化查询如需了解详情，请参阅查询执行计划。

您可以通过 Google Cloud 控制台或 客户端库。

如需使用 Google Cloud 控制台获取特定查询的查询执行计划，请执行以下操作： 请按以下步骤操作：

1. 打开 Spanner 实例页面。

   转到 Spanner 实例

2. 选择 Spanner 实例的名称和 查询。

3. 点击左侧导航面板中的 Spanner Studio。

4. 在文本字段中输入查询，然后点击运行查询。

5. 点击说明
   。Google Cloud 控制台会显示 执行计划

   

详细了解如何理解可视化方案并将其用于调试 请参阅使用查询计划可视化工具调整查询。

您还可以查看历史查询计划的示例，并比较 特定查询随时间变化的情况如需了解详情，请参阅 采样查询计划。

使用二级索引

与其他关系型数据库一样，Spanner 提供二级索引， 来检索数据，只需使用 SQL 语句或 Spanner 的读取接口。较常见的是 使用 Spanner Studio。在 SQL 查询中使用二级索引 用于指定 Spanner 获取结果的方式。 指定二级索引可以加快查询执行速度。

例如，假设您想提取所有具有 特定姓氏。可按照如下方法编写这样一个 SQL 查询：

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

此查询将返回您期望的结果，但需要的时间可能很长，具体取决于 Singers 表中的行数以及满足谓词 WHERE s.LastName = 'Smith' 的行数。如果没有包含要读取的 LastName 列的二级索引，那么查询计划将读取整个 Singers 表来查找与谓词相匹配的行。阅读完整内容 称为“全表扫描”。全表扫描的开销很大 获取结果的方法，但前提是表中仅包含 Singers 替换为该姓氏。

您可以通过在姓氏列上定义二级索引来提高此查询的性能：

CREATE INDEX SingersByLastName ON Singers (LastName);

因为二级索引 SingersByLastName 包含编入索引的表 列 LastName 和主键列 SingerId 时，Spanner 可以 从小得多的索引表中获取所有数据，而不是扫描 完整的 Singers 表。

在这种情况下，Spanner 会自动使用 索引 SingersByLastName（前提是三天有 自创建数据库以来传递的数据；请参阅 关于新数据库的说明）。不过，您最好 通过指定 FROM 子句中的索引指令：

GoogleSQL

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

 SELECT s.SingerId
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

现在，假设除了 ID 之外，您还想提取歌手的名字。即使 FirstName 列未包含在该索引中，您仍应按照上述方式指定索引指令：

GoogleSQL

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId, s.FirstName
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

您仍然可以通过使用索引获得性能优势，因为 Spanner 在执行查询计划时无需执行全表扫描。相反， 从 SingersByLastName 中选择满足谓词的那部分行 索引，然后从基表 Singers 执行查找，以获取第一个 为行的子集指定名称。

如果您希望 Spanner 不必从基库提取任何行 您可以存储 FirstName 列的副本， 将自身编入索引：

GoogleSQL

CREATE INDEX SingersByLastName ON Singers (LastName) STORING (FirstName);

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

使用 STORING 子句（适用于 GoogleSQL 方言）或 INCLUDE 子句 （针对 PostgreSQL 方言），这样会消耗额外的存储空间， 具有以下优势：

• 使用索引并选择存储在 STORING 中的列的 SQL 查询，或 INCLUDE 子句不需要与基表进行额外联接。
• 使用索引的读取调用可以读取存储在 STORING 或 INCLUDE 子句。

前面的示例说明了当出现以下情况时，二级索引如何加快查询的运行速度： 使用查询的 WHERE 子句选择的行可以使用 二级索引

此外，对于返回有序结果的某些查询，二级索引也有助于提高性能。对于 例如，假设您要提取所有专辑标题及其发布日期 按发行日期升序和专辑标题降序排列。 您可以编写如下所示的 SQL 查询：

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

如果没有二级索引，则此查询可能需要在执行计划中执行一个开销很大的排序步骤。您可以通过定义此二级索引来加快查询的执行速度：

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

然后，重写查询以使用二级索引：

GoogleSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

此查询和索引定义同时满足以下两个条件：

• 如需移除排序步骤，请确保 ORDER BY 中的列列表 子句是索引键列表的前缀。
• 为避免从基表联接以提取任何缺失的列， 确保索引涵盖查询使用的表中的所有列。

虽然二级索引可以加快常见查询的运行速度， 二级索引可能会增加提交操作的延迟时间，因为 二级索引通常需要在每次提交时都涉及一个额外节点。对于大多数工作负载而言，最好是使用少量二级索引。但是，您应该考虑您是否更在意读取或写入延迟，并考虑哪些操作对于您的工作负载最为关键。对 以确保其按预期运行

如需查看二级索引的完整参考，请参阅二级索引。

优化扫描

某些 Spanner 查询可能会因使用面向批处理的 处理方法，而不是更常见的面向行的处理方法， 处理方法批量处理扫描能更高效地 同时处理大量数据，并且支持查询， 降低 CPU 利用率和延迟时间。

Spanner 扫描操作始终在 面向行模式。在此期间，Spanner 会收集 运行时指标然后，Spanner 会应用一组启发式算法， 根据这些指标的结果来确定最佳扫描模式。时间 Spanner 会切换到面向批处理的处理模式 以帮助提高扫描吞吐量和性能。

常见使用场景

具有以下特征的查询通常适合使用 面向批处理：

• 对不经常更新的数据进行大规模扫描。
• 使用谓词对固定宽度的列进行扫描。
• 具有大量跳转次数的扫描。（跳转使用索引来检索记录。）

没有性能提升的用例

并非所有查询都能从面向批处理的处理中受益。以下查询 类型在面向行的扫描处理中效果更好：

• 点查询查询：仅提取一行的查询。
• 小规模扫描查询：仅扫描几行的表扫描，除非 大量跳转。
• 使用 LIMIT 的查询。
• 读取高流失数据的查询： 会频繁更新数据
• 行包含大型值的查询：大型值行是指 在单个 列。

如何检查查询使用的扫描方法

要检查查询使用的是面向批处理还是面向行的处理 或者会自动在两种扫描方法之间切换：

1. 前往 Google Cloud 控制台中的 Spanner 实例页面。

   转到“实例”页面

2. 点击要调查的查询所在的实例的名称。

3. 在“数据库”表下，点击包含您要查询的查询的数据库 来进行调查。

4. 在导航菜单中，点击 Spanner Studio。

5. 点击 即可打开新的标签页 新的 SQL 编辑器标签页或 打开新的标签页。

6. 在查询编辑器出现时，编写查询。

7. 点击运行。

   Spanner 运行查询并显示结果。

8. 点击查询编辑器下方的说明标签页。

   Spanner 显示了查询计划执行计划可视化工具。 图表上的每张卡片都代表一个迭代器。

9. 点击 Table scan 迭代器卡片以打开信息面板。

   信息面板会显示背景信息 所选扫描。此卡片上显示了扫描方法。 自动表示 Spanner 确定扫描 方法。其他可能的值包括 Vectorized（用于批量处理） 和标量（适用于面向行的处理）。

   

如何强制执行查询使用的扫描方法

为了优化查询性能，Spanner 会选择最佳扫描 方法。我们建议您使用此默认扫描方法。 不过，在某些情况下，您可能需要强制执行特定的 扫描方法的类型。

如何强制执行面向批处理的扫描

您可以在表级层和语句级层强制执行面向批处理的扫描。

如需在表级层强制执行面向批处理的扫描方法，请使用表提示 在您的查询中：

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=BATCH} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=batch */ JOIN t2 on ...)
  WHERE ...

如需在语句级别强制执行面向批处理的扫描方法，请使用 语句提示：

GoogleSQL

  @{SCAN_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

如何停用自动扫描并强制执行面向行的扫描

尽管我们不建议停用 Spanner，您可能会决定将其停用并使用 面向行的扫描方法，用于排查问题，例如诊断 延迟时间

停用自动扫描方法并在表上强制执行行处理 ，请在查询中使用表提示：

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=ROW} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=row */ JOIN t2 on ...)
  WHERE ...

停用自动扫描方法并在语句中强制执行行处理 ，请在查询中使用语句提示：

GoogleSQL

  @{SCAN_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=row */
  SELECT ...
  FROM ...
  WHERE ...

优化范围键查找

SQL 查询的一个常见用途是，从基于 Spanner 的 Spanner 中读取多行。 一组已知键。

以下最佳实践有助于您在提取数据时编写高效的查询 按键组合：

• 如果键列表稀疏且不相邻，请使用查询参数和 UNNEST 来构造查询。

  例如，如果您的键列表是 {1, 5, 1000}，请编写类似下面的查询：

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST (@KeyList)
  

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST ($1)
  

  注意：

  • 数组 UNNEST 运算符可将输入数组展平为多行元素。

  • 查询参数，对于 GoogleSQL 和 $1，为 @KeyList 可以加快查询的运行速度 上述最佳实践。

• 如果键列表相邻且在一个范围内，请指定较低值和 WHERE 子句中键范围的更高限制。

  例如，如果您的键列表是 {1,2,3,4,5}，则按以下方式构造查询 如下：

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN @min AND @max
  

  注意： 此处，@min 和 @max 是绑定到 值 1 和 5。

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN $1 AND $2
  

  注意： 此处，$1 和 $2 是绑定到 值 1 和 5。

  仅当键范围内的键是 相邻。换句话说，如果您的键列表是 {1, 5, 1000}，则 您可以像上述查询一样指定下限和上限，因为 生成的查询会扫描 1 到 1000 之间的每个值。

优化联接

联接操作的开销可能会很大， 因为这会增加查询需要扫描的行数 运行速度会比较慢除了您惯用的技巧外 优化联接查询，以下是一些 在使用 Spanner SQL 时实现更高效的联接做法：

• 如果可能，请通过主键联接交错表中的数据。例如：

  SELECT s.FirstName, a.ReleaseDate
  FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
  

  交错表 Albums 中的行一定会以物理方式与 Singers 中的父行一起存储在同一个分片中，如架构和数据模型中所述。因此，联接 在本地完成，而不通过网络发送大量数据。

• 如果要强制进行联接，请使用联接指令。例如：

  GoogleSQL

  SELECT *
  FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
  

  PostgreSQL

  SELECT *
  FROM Singers AS s JOIN/*@ FORCE_JOIN_ORDER=TRUE */ Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
  

  联接指令 FORCE_JOIN_ORDER 告知 Spanner 使用 查询中指定的联接顺序（即 Singers JOIN Albums，而不是 Albums JOIN Singers).无论使用什么 Spanner 选择的顺序。但是，您可能需要使用此联接 指令，如果您在查询计划中发现 Spanner 已更改， 联接顺序，并导致了不良后果，例如较大的中间 或错失了查找行的机会。

• 使用联接指令选择一种联接实现。当您使用 SQL 来 查询多个表，Spanner 会自动使用联接方法 以便提高查询效率不过，Google 建议您使用不同的联接算法进行测试。选择正确的联接算法可以缩短延迟时间和/或降低内存消耗。此查询演示了 使用 JOIN 指令的语法，在 JOIN_METHOD 提示下选择 HASH JOIN：

  GoogleSQL

  SELECT *
  FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
  ON a.SingerId = a.SingerId
  

  PostgreSQL

  SELECT *
  FROM Singers s JOIN/*@ JOIN_METHOD=HASH_JOIN */ Albums AS a
  ON a.SingerId = a.SingerId
  

• 如果您使用的是 HASH JOIN 或 APPLY JOIN，并且使用的是 WHERE JOIN 中具有高度选择性的子句，请将表格 该函数会生成行数最小的行，作为 FROM 中的第一个表 子句。这种结构很有帮助，因为目前在 HASH JOIN 中， Spanner 始终选择左侧表作为 build， 作为探测器。同样，对于 APPLY JOIN，Spanner 选择左侧表为外侧，选择右侧表为 内部。如需详细了解这些联接类型，请参阅哈希联接和 申请联接。

• 对于对您的工作负载至关重要的查询，请指定您希望 SQL 语句中的联接方法和联接顺序，以提高一致性 性能

在读写事务中避免大量读取

在提交调用之前，读写事务允许一系列（零个或多个）读取或 SQL 查询，并且可以包含一组变更。为了保持数据一致性 在表和索引中读取和写入行时获取锁。对于 如需详细了解锁定，请参阅读取和写入的生命周期。

由于 Spanner 中锁定的工作原理，执行读取或 SQL 读取大量行的查询（例如 SELECT * FROM Singers） 这意味着其他任何事务都无法写入到您已读取的行， 您的事务会被提交或中止。

此外，由于您的事务正在处理大量行， 所用时间可能比读取较小范围行的事务要长 （例如 SELECT LastName FROM Singers WHERE SingerId = 7），它会进一步 加剧了问题并降低系统吞吐量。

因此，请尽量避免执行大量读取操作（例如全表扫描或大规模联接） 操作），除非您愿意接受 吞吐量。

在某些情况下，以下模式可以产生更好的结果：

1. 在只读事务中执行大批量读取。 只读事务具有更高的聚合吞吐量， 不使用锁。
2. 可选：对您刚刚读取的数据进行任何所需的处理。
3. 启动一个读写事务。
4. 验证自执行以来关键行是否未更改值 第 1 步中的只读事务。
   • 如果行已更改，请回滚您的事务并从第 1 步重新开始。
   • 如果一切正常，则提交您的变更。

确保在读写模式下避免大量读取的一种方式 “事务”是指查看查询生成的执行计划

使用 ORDER BY 来确保 SQL 结果的排序

如果您希望 SELECT 查询的结果按特定顺序排列， 明确包含 ORDER BY 子句。例如，如果您希望 按主键顺序列出所有歌手，请使用以下查询：

SELECT * FROM Singers
ORDER BY SingerId;

仅当使用 ORDER BY 子句时，Spanner 才会保证结果排序 。换句话说，请考虑不含 ORDER BY 的以下查询：

SELECT * FROM Singers;

Spanner 不保证此查询的结果将采用 主键顺序。此外，结果的排序可能会随时发生变化 并且不保证每次调用都会保持一致。如果查询 具有 ORDER BY 子句，Spanner 使用索引，它提供 所需的顺序，则 Spanner 不会明确对数据进行排序。因此， 不必担心包含此子句对性能的影响。您可以查看 通过查看执行中是否包含显式排序操作， 查询计划

使用 STARTS_WITH 而非 LIKE

由于 Spanner 在 2020 年 3 月 18 日之前LIKE 因此 Spanner 必须读取所有行，并根据 LIKE 表达式，用于过滤掉不匹配的行。

当 LIKE 模式采用 foo% 形式时（例如，它以固定的 字符串并以单个百分号结尾）且列已编入索引，请使用 STARTS_WITH，而非 LIKE。这个 可让 Spanner 更有效地优化查询执行， 。

不推荐：

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

注意： 此示例假定 @like_clause 绑定到 'Love%'。

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

注意： 此示例假定 $2 绑定到 'Love%'。

<ph type="x-smartling-placeholder"></ph> 建议：

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

注意： 此示例假定 @prefix 绑定到 'Love'。此查询更详细 查询效率要高于上一个查询。如果在 Albums.AlbumTitle。

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

注意： 此示例假定 $1 绑定到 'Love'。此查询更详细 查询效率要高于上一个查询。如果在 Albums.AlbumTitle。

使用提交时间戳

如果您的应用需要查询在特定时间之后写入的数据， 将提交时间戳列添加到相关表中。提交时间戳 启用 Spanner 优化，以减少 其 WHERE 子句将结果限制为最近写入的行的查询 而不是特定时间

要详细了解此优化，请访问 GoogleSQL 方言数据库或使用 PostgreSQL 方言数据库。