SQL 最佳做法

查询执行计划中所述,Cloud Spanner 的 SQL 编译器可将 SQL 语句转换为查询执行计划,以用于获取查询结果。本页面介绍了构造 SQL 语句的最佳做法,这些最佳做法可帮助 Cloud Spanner 找到高效的执行计划。

本页中所示的示例 SQL 语句使用的是以下示例架构:

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) 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 参考文档,请参阅语句语法函数和运算符以及词汇结构和语法

使用查询参数来加快频繁执行的查询的运行速度

参数化查询是一种将查询字符串与查询参数值分开的查询执行方法。例如,假设您的应用需要检索在特定年份发行了具有特定标题的专辑的歌手。您可以编写类似以下示例的 SQL 语句来检索自 2017 年以来发布的所有标题为“Love”的专辑:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

在另一个查询中,您可以将专辑标题的值更改为“Peace”:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

如果您的应用需要执行许多类似查询,并且后续查询中只有一个字面量值会发生变化,那么您应该为该值使用参数占位符。生成的参数查询可以缓存下来并重复使用,这样做可以降低编译开销。

例如,下面重新编写的查询将 Love 替换为名为 title 的参数:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

有关查询参数用法的注意事项:

  • 查询中的参数引用使用 @ 字符,后跟参数名称,该名称可以包含字母、数字和下划线的任意组合。
  • 参数可以出现在需要字面量值的任意位置。
  • 同一个参数名称可以在单个 SQL 语句中多次使用。
  • 指定查询参数和值,以将其绑定到 ExecuteSQLExecuteStreamingSQL 请求 APIparams 字段中。
  • 如需详细了解查询参数语法,请参阅 SQL 词法结构和语法

总之,查询参数通过以下几种方式让查询执行更顺畅无忧:

  • 预先优化的计划:每次调用时,系统都可以更快地执行使用参数的查询,因为参数化使 Cloud Spanner 能更轻松地缓存执行计划。
  • 简化的查询组合:在查询参数中提供字符串值时,不需要对其进行转义。查询参数还可以降低语法错误的风险。
  • 安全性:查询参数保护您免受各种 SQL 注入攻击,使您的查询更安全。这种保护对于根据用户输入构造的查询尤其重要。

了解 Cloud Spanner 执行查询的方式

在 Cloud Spanner 中,您能够使用指定您想要检索的数据的声明性 SQL 语句来查询数据库。如果您还想了解 Cloud Spanner 如何获取结果,则应使用查询执行计划。查询执行计划显示与查询的每个步骤相关的开销。借助这些开销信息,您可以调试查询性能问题并优化查询。

您可以通过 Cloud Console 或客户端库检索查询执行计划。

如需使用 Cloud Console 获取查询计划,请执行以下操作:

  1. 打开 Cloud Spanner 实例页面。

    转到 Cloud Spanner 实例

  2. 点击要查询的 Cloud Spanner 实例和数据库的名称。

  3. 点击查询

  4. 在文本字段中键入查询,然后点击运行查询

  5. 点击说明
    Cloud Console 将直观显示您查询的执行计划。

    Cloud Console 中的可视化执行计划的屏幕截图

如需详细了解可视化方案,请参阅使用查询方案可视化工具调整查询

如需查看完整的查询计划参考,请参阅查询执行计划

使用二级索引来加快常见查询的运行速度

与其他关系型数据库一样,Cloud Spanner 也提供二级索引。凭借二级索引,您可以使用 SQL 语句或 Cloud Spanner 的读取接口检索数据。利用索引提取数据的更常见方式是使用 SQL 查询接口。在 SQL 查询中使用二级索引的好处在于,您能够指定 Cloud Spanner 获取结果的方式。 指定二级索引可以加快查询执行速度。

例如,假设您想要提取具有特定姓氏的所有歌手的 ID。可按照如下方法编写这样一个 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,因此 Cloud Spanner 可以从小很多的索引表中提取所有数据,而不需要扫描整个 Singers 表。

在这种情况下,Cloud Spanner 在执行查询时会自动使用二级索引 SingersByLastName(只要距离数据库创建后经过了 7 天);请参阅有关新数据库的说明)。但最佳做法是,在 FROM 子句中指定一条索引指令,以明确指示 Cloud Spanner 使用该索引:

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

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

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

使用索引还有助于提高性能,因为执行查询计划时,Cloud Spanner 不需要执行全表扫描。相反,它将从 SingersByLastName 索引中选择满足谓词的一小部分行,然后从基表 Singers 执行查找,以便仅为这一小部分行提取名字。

如果希望让 Cloud Spanner 不必从基表中提取任何行,您可以选择将 FirstName 列的副本存储在索引本身中:

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

使用类似的 STORING 子句会占用额外的存储空间,但是它可为使用索引的查询和读取调用带来以下好处:

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

上面的示例说明了当可以使用二级索引快速识别查询的 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);

然后重新编写查询以使用二级索引:

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

请注意,此查询和索引定义符合以下两个条件:

  • ORDER BY 子句中的列列表是索引键列表的前缀。
  • 索引涵盖查询中使用的表中的所有列。

由于这两个条件都得到满足,所生成的查询计划将移除排序步骤,执行速度更快。

尽管二级索引可以加快常见查询的运行速度,但请注意,添加二级索引可能会给您的提交操作带来延迟,因为每个二级索引在每次提交时通常需要涉及一个额外节点。对于大多数工作负载而言,最好是使用少量二级索引。但是,您应该考虑您是否更在意读取或写入延迟,并考虑哪些操作对于您的工作负载最为关键。您还应该对工作负载进行基准测试,以确保其按照预期执行。

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

编写高效查询用于键范围查找

SQL 查询的常见用法是基于一个已知键列表从 Cloud Spanner 中读取多行。

下面介绍了通过一系列键提取数据时编写高效查询的最佳做法:

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

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

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

    注意:

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

    • @KeyList 是一个查询参数,它可以加快查询的运行速度,如上文的最佳做法所述。

  • 如果键列表相邻且在一个范围内,请在 WHERE 子句中指定键范围的下限和上限。

    例如,如果您的键列表是 {1,2,3,4,5},请构造类似下面的查询:

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

    其中,@min@max 分别是与值 1 和 5 绑定的查询参数。

    请注意,只有当键范围内的键相邻时,此查询才会更高效。换句话说,如果您的键列表是 {1, 5, 1000},则不应指定上述查询中的类似下限和上限,因为生成的查询会扫描介于 1 和 1000 之间的所有值。

编写高效的联接查询

联接操作的开销可能非常大。原因在于,JOIN 可能会大幅增加查询需要扫描的行数,导致查询速度变慢。除了您为优化联接查询而在其他关系型数据库中惯于使用的一些方法外,在使用 Cloud Spanner SQL 时,可通过下面一些最佳做法建立更高效的联接:

  • 如有可能,请通过主键联接交错表中的数据。例如:

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

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

  • 如果您想强制 JOIN 的顺序,请使用联接指令。例如:

    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=TRUE} 指示 Cloud Spanner 使用查询中指定的联接顺序(即 Singers JOIN Albums,而不是 Albums JOIN Singers)。无论 Cloud Spanner 选择的顺序如何,返回的结果都是相同的。但是,如果您在查询计划中注意到,Cloud Spanner 更改了联接顺序,并且产生了不想要的结果(例如数量庞大的中间结果)或错失了查找行的机会,则可能需要使用此联接指令。

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

    SELECT *
    FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • 如果您使用的是 HASH JOINAPPLY JOIN,并且 JOIN 的一侧为具有高度选择性的 WHERE 子句,请将产生最小行数的表作为第一个表放入联接的 FROM 子句中。这是因为,目前在 HASH JOIN 中,Cloud Spanner 总是选择左侧表用于构建目的,而选择右侧表用于探测目的。同样,对于 APPLY JOIN,Cloud Spanner 选择左侧作为外侧,选择右侧作为内侧。如需详细了解这些联接类型,请参阅哈希联接应用联接

  • 对于对工作负载至关重要的查询,建议您在 SQL 语句中指定性能最佳的联接方法和联接顺序,以实现更一致的性能。

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

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

由于 Cloud Spanner 中锁定的工作方式,执行读取大量行的读取或 SQL 查询(例如,SELECT * FROM Singers)意味着,除非您的事务被提交或中止,否则没有其他事务可以写入到您已读取的行。此外,由于您的事务正在处理大量行,因此它所花的时间可能比读取更小范围行的事务(例如 SELECT LastName FROM Singers WHERE SingerId = 7)更长,这进一步加剧了问题并减少了系统吞吐量。

因此,除非您愿意接受写入吞吐量降低的状况,否则应该尽量避免在事务内部进行大量读取(例如:全表扫描或大规模联接操作)。在某些情况下,以下模式可以产生更好的结果:

  1. 只读事务中进行大量读取(请注意,只读事务不使用锁定,因此允许的总吞吐量更高)。
  2. [可选] 如果您需要对刚刚读取的数据进行任何处理,请执行此操作。
  3. 启动一个读写事务。
  4. 从执行第 1 步中的只读事务开始,确认您关注的关键行没有更改值。
    1. 如果行已更改,请回滚您的事务并从第 1 步重新开始。
    2. 如果一切正常,则提交您的变更。

确保在读写事务内避免大量读取的一种方法是查看由您的查询生成的执行计划。

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

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

SELECT * FROM Singers
ORDER BY SingerId;

请注意,只有当查询中存在 ORDER BY 子句时,Cloud Spanner 才能保证结果的排序。换句话说,请考虑不含 ORDER BY 的以下查询:

SELECT * FROM Singers;

Cloud Spanner 不保证此查询的结果将按主键顺序排列。此外,结果的排序可能会随时更改,在各个调用之间也可能会不一致。

使用 STARTS_WITH(而不是 LIKE)来加快参数化 SQL 查询的运行速度

由于 Cloud Spanner 在执行时间之前不会评估参数化 LIKE 模式,因此 Cloud Spanner 必须读取所有行并针对 LIKE 表达式评估这些行,以过滤掉不匹配的行。

如果 LIKE 模式查找位于值开头的匹配项并且列已编入索引,请使用 STARTS_WITH 而不是 LIKE。这使 Cloud Spanner 能够更有效地优化查询执行计划。

不推荐:

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

推荐:

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