SQL 권장사항

쿼리 실행 계획에 설명된 대로 Cloud Spanner의 SQL 컴파일러는 SQL 문을 쿼리 실행 계획으로 변환하며, 이 실행 계획은 쿼리 결과를 가져오는 데 사용됩니다. 이 페이지는 Cloud Spanner가 효율적인 실행 계획을 찾을 수 있도록 SQL 문을 구성하는 권장사항을 설명합니다.

이 페이지의 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'

애플리케이션에서 다음과 유사한 수많은 쿼리를 실행해야 하는 경우, 이후 쿼리에서는 리터럴 값만 변경되므로 해당 값에 대한 매개변수 자리표시자를 사용해야 합니다. 매개변수화된 결과 쿼리를 캐싱하고 다시 사용할 수 있으므로, 컴파일 비용이 절감됩니다.

예를 들어 아래의 다시 작성된 쿼리는 Lovetitle이라는 매개변수로 바꿉니다.

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

쿼리 매개변수 사용에 대한 참고사항:

  • 쿼리의 매개변수 참조는 @ 문자 다음에 문자, 숫자, 밑줄의 조합을 포함할 수 있는 매개변수 이름을 사용합니다.
  • 매개변수는 리터럴 값이 필요한 모든 위치에 나타날 수 있습니다.
  • 동일한 매개변수 이름은 단일 SQL 문에서 두 번 이상 사용될 수 있습니다.
  • ExecuteSQL 또는 ExecuteStreamingSQL 요청 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에 쿼리의 시각적 실행 계획이 표시됩니다.

    UI의 설명 콘솔 스크린샷

전체 쿼리 계획 참조는 쿼리 실행 계획을 확인하세요.

보조 색인을 사용하여 일반적인 쿼리 속도 향상

다른 관계형 데이터베이스와 마찬가지로 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가 전체 테이블 스캔을 수행할 필요가 없으므로, 여전히 색인 사용을 통한 성능상의 이점을 얻을 수 있습니다. 대신, 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 사용 시 보다 효율적인 JOIN에 유용한 몇 가지 권장사항이 있습니다.

  • 가능하다면 인터리빙된 테이블 내의 데이터를 기본 키로 조인합니다. 예를 들면 다음과 같습니다.

    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가 쿼리에 지정된 조인 순서(즉, Albums JOIN Singers가 아니라 Singers JOIN Albums)를 사용하도록 합니다. 반환되는 결과는 Cloud Spanner에서 선택한 순서와 관계없이 동일합니다. 하지만 쿼리 계획에서 Cloud Spanner가 조인 순서를 변경하고 더 큰 중간 결과와 같은 원치 않은 결과를 발생시켰거나 행을 찾을 기회를 놓쳤다는 것을 알게 된 경우, 이 조인 지시문을 사용할 수 있습니다.

  • 조인 지시문을 사용하여 조인 구현을 선택합니다. 쿼리에 적합한 조인 알고리즘을 선택하면 지연 시간이나 메모리 소비 또는 둘 다 향상될 수 있습니다. 이 쿼리는 JOIN_METHOD 힌트와 함께 조인 지시문을 사용하여 HASH JOIN을 선택하는 구문을 보여줍니다.

    SELECT *
    FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • HASH JOIN 또는 APPLY JOIN을 사용 중이고 JOIN의 한쪽에 매우 까다로운 WHERE 절이 있는 경우 조인의 FROM 절에 가장 적은 수의 행을 생성하는 테이블을 첫 번째 테이블로 배치합니다. 그 이유는 현재 HASH JOIN에서 Cloud Spanner가 항상 왼쪽 테이블을 빌드로 선택하고 오른쪽 테이블을 프로브로 선택하기 때문입니다. 마찬가지로 APPLY JOIN의 경우, Cloud Spanner는 왼쪽 테이블을 바깥쪽으로 선택하고 오른쪽 테이블을 안쪽으로 선택합니다. 해시 조인적용 조인과 같은 조인 유형에 대한 자세한 내용을 참조하세요.

읽기-쓰기 트랜잭션 내에서 대량 읽기 방지

읽기-쓰기 트랜잭션은 0개 이상의 읽기 또는 SQL 쿼리의 시퀀스를 허용하며 커밋할 호출 전에 일련의 변형을 포함할 수 있습니다. 데이터 일관성을 유지하기 위해 Cloud Spanner는 테이블과 색인에서 행을 읽고 쓸 때 잠금을 획득합니다(읽기 및 쓰기 수명의 잠금 참조).

Cloud Spanner에서 잠금이 작동하는 방식으로 인해 많은 수의 행(예: SELECT * FROM Singers)을 읽는 읽기 또는 SQL 쿼리를 수행하면 트랜잭션이 커밋 또는 취소될 때까지 읽은 행에 다른 트랜잭션이 쓰기를 수행할 수 없습니다. 또한 트랜잭션이 많은 수의 행을 처리하므로 훨씬 작은 행 범위를 읽는 트랜잭션(예: 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는 이 쿼리 결과가 기본 키 순서라고 보장하지 않습니다. 또한 결과 순서는 언제든지 변경될 수 있으며 호출마다 일관성이 보장되지는 않습니다.

LIKE 대신 STARTS_WITH를 사용하여 매개변수화된 SQL 쿼리 증속

Cloud Spanner는 매개변수화된 LIKE 패턴을 실행 시간 전까지 평가하지 않기 때문에 모든 행을 읽고 LIKE 표현식과 비교하여 일치하지 않는 행을 필터링해야 합니다.

LIKE 패턴이 값 시작 부분에 있는 일치 항목을 찾고 열 색인이 생성되는 경우에는 LIKE 대신 STARTS_WITH를 사용하세요. 그러면 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);