SQL 最佳做法

如「查詢執行計畫」一文所述，SQL 編譯器會將 SQL 陳述式轉換為查詢執行計畫，用於取得查詢結果。本頁面說明建構 SQL 陳述式的最佳做法，協助 Spanner 找出效率較高的執行計畫。如需改善查詢效能的協助，請參閱 [查詢調整][query_tuning] 指南。

本頁顯示的 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 注入。您可以使用查詢參數替代任意運算式，但不能替代 ID、資料欄名稱、資料表名稱或查詢的其他部分。

參數可以出現在任何需要常值的位置。在單一 SQL 陳述式中，同一個參數名稱可使用多次。

總之，查詢參數支援查詢執行的方式如下：

• 預先最佳化的計畫：使用參數的查詢在每次叫用時都能更快執行，因為參數化可讓 Spanner 更容易快取執行計畫。
• 簡化查詢組合：在查詢參數中提供字串值時，不需要逸出字串值。查詢參數也能降低語法錯誤的風險。
• 安全性：查詢參數可保護您免受各種 SQL 注入式攻擊，提高查詢安全性。如果查詢是根據使用者輸入內容建構，這項保護措施就特別重要。

為提升效能，如果最佳查詢執行計畫不依賴特定常值，Spanner 會將 SQL 中硬式編碼的常值轉換為參數。這樣一來，Spanner 就能快取及重複使用查詢計畫，降低延遲時間和 CPU 成本。不過，這種方法無法提供相同的 SQL 注入防護，也無法簡化查詢組合。最佳做法是在應用程式程式碼中使用查詢參數。

注意： 如果查詢的最佳查詢計畫可能取決於常值的值，則不會自動轉換為參數化 SQL。如要強制將常值轉換為參數，請使用 auto_parameter 提示。

瞭解 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. 按一下「Explanation」(說明)
   。 Google Cloud 控制台會顯示查詢的視覺化執行計畫。

   

如要進一步瞭解如何解讀視覺化計畫，並使用這些計畫對查詢進行偵錯，請參閱「使用查詢計畫視覺化工具調整查詢」。

您也可以查看歷來查詢計畫的樣本，並比較特定查詢在一段時間內的成效。詳情請參閱「取樣查詢計畫」。

使用次要索引

與其他關聯式資料庫一樣，Spanner 提供次要索引，您可以使用 SQL 陳述式或 Spanner 的讀取介面，透過次要索引擷取資料。在 SQL 查詢中使用次要索引，即可指定 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，Spanner 可以從較小的索引資料表擷取所有資料，而不必掃描整個 Singers 資料表。

在這種情況下，Spanner 會在執行查詢時自動使用次要索引 SingersByLastName (只要資料庫建立後經過三天即可，請參閱「新資料庫注意事項」)。不過，建議您在 FROM 子句中指定索引指令，明確指示 Spanner 使用該索引：

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';

如果您使用具名結構定義，請對 FROM 子句使用下列語法：

GoogleSQL

FROM NAMED_SCHEMA_NAME.TABLE_NAME@{FORCE_INDEX="NAMED_SCHEMA_NAME.TABLE_INDEX_NAME"}

PostgreSQL

FROM NAMED_SCHEMA_NAME.TABLE_NAME /*@ FORCE_INDEX = TABLE_INDEX_NAME */

現在，假設您除了 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 或 INCLUDE 子句中的資料欄時，SQL 查詢不需要額外彙整至基礎資料表。
• 使用索引的讀取呼叫可以讀取儲存在 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 子句中的資料欄清單是索引鍵清單的前置字串。
• 為避免從基礎資料表重新彙整，以擷取任何缺少的資料欄，請確保索引涵蓋查詢使用的資料表中所有資料欄。

雖然次要索引可以加快常見查詢的速度，但新增次要索引可能會增加修訂作業的延遲時間，因為每個次要索引通常需要在每個修訂版本中加入額外的節點。對大多數工作負載來說，有幾個次要索引就足夠。不過，您應考量自己更重視讀取或寫入延遲時間，以及哪些作業對工作負載最重要。對工作負載進行基準測試，確保效能符合預期。

如需完整的次要索引參考資料，請參閱次要索引。

最佳化範圍鍵查詢

SQL 查詢的常見用途是根據已知索引鍵清單，從 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 為 @KeyList，PostgreSQL 為 $1) 可加快查詢速度，詳情請參閱前述最佳做法。

  • 建議使用 UNNEST 搭配單一參數，而非 OR 搭配多個參數。

• 如果鍵清單相鄰且在某個範圍內，請在 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 之間的所有值。

最佳化彙整作業

Join 作業可能會大幅增加查詢需要掃描的資料列數量，導致查詢速度變慢，因此可能需要較高的費用。除了您習慣在其他關聯式資料庫中使用的技術，以最佳化 JOIN 查詢外，以下提供一些最佳做法，讓您在使用 Spanner SQL 時能更有效率地執行 JOIN：

• 可能的話，利用主鍵將資料彙整於交錯的資料表中。例如：

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

The rows in the interleaved table `Albums` are physically
stored in the same splits as the parent row in `Singers`, as discussed in
[Schema and Data Model](/spanner/docs/schema-and-data-model). Therefore, joins can be
completed locally without sending lots of data across the network.

• 如要強制執行彙整順序，請使用 join 指令。例如：

  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%';

  join 指令 FORCE_JOIN_ORDER 會指示 Spanner 使用查詢中指定的 join 順序 (即 Singers JOIN Albums，而非 Albums JOIN Singers)。無論 Spanner 選擇何種順序，傳回的結果都相同。不過，如果您在查詢計畫中發現 Spanner 變更了 join 順序，並造成不良後果 (例如中繼結果較大)，或錯失搜尋資料列的機會，則建議使用這個 join 指令。

• 使用 join 指令選擇 join 實作。使用 SQL 查詢多個資料表時，Spanner 會自動採用可能提高查詢效率的彙整方法。不過，Google 建議您測試不同的 join 演算法。選擇合適的彙整演算法，可以改善延遲時間和/或記憶體消耗量。這項查詢示範了如何使用 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 的一側具有高度選擇性，請將產生最少資料列數的資料表，設為 join 的 FROM 子句中的第一個資料表。這種結構很有幫助，因為在 HASH JOIN 中，Spanner 一律會選擇左側資料表做為建構，右側資料表做為探測。同樣地，對於 APPLY JOIN，Spanner 會選擇左側資料表做為外部資料表，右側資料表做為內部資料表。進一步瞭解這些彙整類型：[雜湊彙整][hash_join] 和 [套用彙整][apply_join]。

• 對於工作負載的重要查詢，請在 SQL 陳述式中指定效能最佳的 join 方法和 join 順序，以獲得更一致的效能。

使用時間戳記述詞下推功能最佳化查詢

時間戳記述詞下推是 Spanner 的查詢最佳化技術，可提高查詢效率，適用於使用時間戳記和依年齡分層儲存政策資料的查詢。啟用這項最佳化功能後，系統會在查詢執行計畫中，盡早對時間戳記資料欄執行篩選作業。這可大幅減少處理的資料量，並提升整體查詢效能。

使用時間戳記述詞下推時，資料庫引擎會分析查詢並找出時間戳記篩選器。接著，系統會將這個篩選器「推送」至儲存層，因此只會從 SSD 讀取符合時間戳記條件的相關資料。這樣可以盡量減少處理及移轉的資料量，加快查詢執行速度。

如要最佳化查詢，只存取儲存在 SSD 上的資料，必須符合下列條件：

• 查詢必須啟用時間戳記述詞下推功能。詳情請參閱「GoogleSQL 陳述式提示」和「PostgreSQL 陳述式提示」。

• 查詢必須使用以年齡為依據的限制，且限制必須等於或小於資料溢出政策中指定的年齡 (在 CREATE LOCALITY GROUP 或 ALTER LOCALITY GROUP DDL 陳述式中使用 ssd_to_hdd_spill_timespan 選項設定)。詳情請參閱 GoogleSQL LOCALITY GROUP 陳述式和 PostgreSQL LOCALITY GROUP 陳述式。

  • 查詢中篩選的資料欄必須是包含修訂版本時間戳記的時間戳記欄。如要瞭解如何建立修訂版本時間戳記資料欄，請參閱「GoogleSQL 中的修訂版本時間戳記」和「PostgreSQL 中的修訂版本時間戳記」。這些資料欄必須與時間戳記資料欄一併更新，並位於同一個位置群組中，該群組具有以年齡為依據的分層儲存政策。

    如果查詢的資料列中，部分資料欄位於 SSD，部分資料欄位於 HDD (因為資料欄更新時間不同，且移至 HDD 的時間也不同)，則使用提示時，查詢效能可能會降低。這是因為查詢必須填入不同儲存層的資料。使用提示後，Spanner 會根據每個儲存格的修訂版本時間戳記，在個別儲存格層級 (資料列和資料欄精細程度層級) 淘汰資料，導致查詢速度變慢。為避免這個問題，請務必在同一筆交易中，使用這項最佳化技術定期更新所有查詢的資料欄，讓所有資料欄共用相同的修訂版本時間戳記，並享有最佳化效益。

如要在陳述式層級啟用時間戳記述詞下推，請在查詢中使用陳述式提示。例如：

GoogleSQL

@{allow_timestamp_predicate_pushdown=TRUE}
SELECT s.SingerInfo
FROM Singers s
WHERE s.ModificationTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

PostgreSQL

/*@allow_timestamp_predicate_pushdown=TRUE*/
SELECT s.SingerInfo
FROM Singers s
WHERE s.ModificationTime > CURRENT_TIMESTAMP - INTERVAL '12 hours';

避免在讀寫交易內執行大量讀取作業

讀寫交易允許在修訂呼叫前，進行零個或多個序列讀取作業或 SQL 查詢作業，而且可包含一組變異。為維持資料一致性，Spanner 在讀取及寫入資料表和索引中的資料列時，會取得鎖定。如要進一步瞭解鎖定，請參閱「讀取和寫入作業的生命週期」。

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 在執行前不會評估參數化的 LIKE 模式，因此必須讀取所有資料列，並根據 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;

注意： 這個範例假設 $1 已繫結至 'Love%'。

建議做法：

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);

注意： 這個範例假設 $2 已繫結至 'Love'。這項查詢比前一項更有效率。如果 Albums.AlbumTitle 已定義索引，執行速度會更快。

使用修訂版本時間戳記

如果應用程式需要查詢特定時間後寫入的資料，請在相關資料表中新增修訂版本時間戳記資料欄。提交時間戳記可啟用 Spanner 最佳化功能，減少查詢的 I/O，其 WHERE 子句會將結果限制在特定時間後寫入的資料列。

進一步瞭解如何使用 GoogleSQL 方言資料庫或使用 PostgreSQL 方言資料庫進行這項最佳化。