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 陳述式中,可以多次使用相同的參數名稱。
  • ExecuteSQLparams 欄位或 ExecuteStreamingSQL 要求 API 中指定查詢參數和值,便可將查詢參數繫結。
  • 如要進一步瞭解查詢參數語法,請參閱 SQL 詞法結構和語法

請注意,上方重新寫入的查詢不會在日期值 2017-01-01 使用預留位置,這是因為該值在後續的每次查詢叫用中都是固定的。在此案例中,將常數保留為文字值是有利的,這可改善您所選擇的查詢計劃。

簡單來說,查詢參數對於執行查詢有下列優點:

  • 預先為計劃進行最佳化:使用參數的查詢在每次叫用時能夠獲得更快的執行速度,因為參數化讓 Cloud Spanner 更容易快取執行計劃。
  • 簡化查詢的撰寫:在查詢參數中提供字串值時,無需逸出字串值。如此也會降低語法錯誤的風險。
  • 安全性:查詢參數可抵禦各種 SQL 植入攻擊,讓查詢更安全。這對於來自使用者輸入的查詢而言尤其重要。

瞭解 Cloud Spanner 執行查詢的方式

Cloud Spanner 可讓您使用宣告式 SQL 陳述式查詢資料庫,指定您要擷取的資料。若您也想瞭解 Cloud Spanner 取得結果的方法,您應使用查詢執行計劃。查詢執行計劃可顯示查詢每個步驟的相關成本。有了這些資訊,您就能針對效能問題進行除錯,最佳化查詢作業。

您可以使用 GCP 主控台或透過用戶端程式庫擷取查詢計劃。

若要使用 GCP 主控台取得查詢計劃:

  1. 開啟 Cloud Spanner 執行個體頁面。

    前往 Cloud Spanner 執行個體

  2. 按一下 Cloud Spanner 執行個體的名稱,以及您要查詢的資料庫。

  3. 按一下 [Query] (查詢)

  4. 在文字欄位中輸入查詢內容,然後按一下 [Run query] (執行查詢)

  5. 按一下 [Explanation] (說明)
    GCP 主控台會顯示視覺化的查詢執行計畫:

    UI 中「Explanation」(說明) 主控台的螢幕擷取畫面

如需完整的查詢計畫參考資料,請參閱查詢執行計畫

使用次要索引,針對一般查詢加快速度

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,但最佳做法是在 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 不需要在執行查詢計劃時進行完整表格掃描。Cloud Spanner 會改從 SingersByLastName 索引中選取一部分滿足述詞的資料列,然後在主資料表 Singers 執行查詢,針對該資料列子集擷取名字。

若要避免 Cloud Spanner 從主資料表擷取任何資料列,您可以選擇將 FirstName 資料欄的複本儲存在索引中:

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

像這樣使用 STORING 子句雖然會消耗額外的儲存空間,但會對查詢提供以下優勢,並使用索引讀取呼叫:

  • 如果 SQL 查詢使用索引並選取儲存在 STORING 子句中的資料欄,就不需額外與主資料表進行彙整。
  • 如果讀取呼叫使用索引,可讀取儲存在 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 時,讓 JOIN 更有效率的最佳做法:

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

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

    結構定義與資料模型中所討論,系統保證會將交錯資料表 Albums 中的資料列實體儲存在與 Singers 中父項資料列的相同分割中。因此,JOIN 可以完全儲存在本機,而不需要跨網路傳送大量資料。

  • 若要強制排序 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%';
    

    join 指令 @{FORCE_JOIN_ORDER=TRUE} 告訴 Cloud Spanner 要使用查詢中指定的彙整順序 (也就是 Singers JOIN Albums,而非Albums JOIN Singers)。不論 Cloud Spanner 選擇何種順序,傳回的結果都是相同的。然而,若您發現 Cloud Spanner 在查詢計劃中變更彙整順序會導致不想要的結果,例如更大量的中繼資料或失去搜尋資料列的機會,您可以考慮使用此 join 指令。

  • 使用 join 指令選擇 JOIN type。為查詢選擇的適當的 join 演算法可改善延遲和/或記憶體耗用。此查詢示範使用 JOIN 指令選擇 HASH JOIN 的語法:

    SELECT *
    FROM Singers s JOIN@{JOIN_TYPE=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • 若您使用 HASH JOINLOOP JOIN,而且您 JOIN 的其中一方是高度選擇性的 WHERE 子句,請將產生最少資料列數的資料表當做 join 中 FROM 子句的第一個資料表。這是因為目前在 HASH JOIN 中,Cloud Spanner 只會將左手邊的資料表當做組建,右手邊資料表當做探查的緣故。同樣地,針對 LOOP JOIN,Cloud Spanner 會將左手邊的資料表當做外部資料,右手邊的資料表當做內部資料。如要進一步瞭解這些彙整類型,請參閱 Hash 彙整Loop 彙整

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

讀寫交易允許在修訂呼叫前,進行零個或多個序列讀取作業或 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 模式,因此必須先讀取所有資料列,並使用 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);

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Cloud Spanner 說明文件