문서용 SQL: 쿼리 계획에 대한 간략한 소개입니다.
나는 썼다 이전 에서 새로운 종류의 SQL에 대한 필요성과 N1QL(SQL For Documents)을 소개한 바 있습니다. 이 블로그에서는 곧 출시될 Couchbase Sherlock 릴리즈에서 N1QL의 쿼리 계획 단계에 대해 설명합니다. N1QL 자체는 여러 차례 개발자 프리뷰를 거쳤습니다. 다음에서 온라인 튜토리얼을 확인하세요. https://query.couchbase.com 문서용 SQL(N1QL)이 포함된 Couchbase Sherlock 릴리스의 개발자 미리 보기를 다운로드하세요.
N1QL이란 무엇인가요? 유연한 데이터 모델을 갖춘 최신 분산 아키텍처에서 문서(예: JSON)에 대한 SQL을 제공하도록 설계된 최신 쿼리 처리 엔진입니다. 최신 데이터베이스는 대규모 클러스터에 배포되며, 유연한 데이터 모델(JSON, Column 제품군 등)을 사용합니다. N1QL은 이 데이터 모델 위에 향상된 SQL을 지원하여 쿼리 처리가 더 쉬워집니다.
쿼리 실행: 개요
애플리케이션과 해당 드라이버는 사용 가능한 쿼리 노드 중 하나에 N1QL 쿼리를 제출합니다. 쿼리 노드는 쿼리를 분석하고 기본 개체에 대한 메타 데이터를 사용하여 최적의 실행 계획을 파악한 다음 실행합니다. 실행 중에 쿼리에 따라 해당 인덱스를 사용하여 쿼리 노드는 인덱스 및 데이터 노드와 함께 작동하여 선택-조인-프로젝트 작업을 검색하고 수행합니다. Couchbase는 클러스터형 데이터베이스이므로 성능 및 가용성 목표에 맞게 데이터, 인덱스 및 쿼리 노드를 확장할 수 있습니다.
쿼리 실행: 내부 보기
이 그림(수치를 제공한 @N1QL 아키텍트 제럴드 상구디에게 감사드립니다)은 쿼리가 결과를 반환하기 위해 거치는 모든 가능한 단계를 보여줍니다. 모든 쿼리가 모든 단계를 거쳐야 하는 것은 아니며, 일부 쿼리는 이러한 단계를 여러 번 거치게 됩니다. 예를 들어 쿼리에 ORDER BY 절이 없는 경우 정렬 단계를 건너뛸 수 있으며, 여러 조인을 수행하려면 스캔-조회-조인 단계를 여러 번 거칠 수 있습니다.
N1QL은 쿼리의 각 키 공간(테이블 또는 버킷)에 대해 쿼리 및 사용 가능한 액세스 경로 옵션을 분석하여 쿼리 계획 및 실행 인프라를 생성합니다. 플래너는 먼저 각 버킷에 대한 액세스 경로를 선택하고, 조인 순서를 결정한 다음, 조인 유형을 결정해야 합니다. 큰 결정이 내려지면 플래너는 계획을 실행하는 데 필요한 인프라를 만듭니다. 쿼리 구문 분석 및 계획과 같은 일부 작업은 순차적으로 수행되는 반면, 가져오기, 조인, 정렬과 같은 다른 작업은 병렬로 수행됩니다. 이 블로그에서는 예제를 통해 쿼리 계획 단계에 대한 간략한 개요를 중점적으로 살펴보겠습니다.
액세스 경로 선택:
키스페이스(버킷) 액세스 경로 옵션
a. 키스캔 액세스. 특정 문서 ID(키)를 사용할 수 있는 경우, 키스캔 액세스 방법은 해당 ID에 대한 문서를 검색합니다. 그 후에 해당 키스페이스의 모든 필터가 적용됩니다. 키스캔 액세스 방법은 키스페이스가 자체적으로 쿼리될 때 또는 조인 처리 중에 사용할 수 있습니다. 키 스캔은 일반적으로 조인 처리 중에 내부 키 스페이스에 대한 적격 문서를 검색하는 데 사용됩니다.
b. 기본 스캔 액세스: 관계형 데이터베이스 시스템에서 전체 테이블 스캔에 해당합니다. 문서 ID가 제공되지 않으며 이 키 스페이스에 대해 적격 보조 액세스 방법을 사용할 수 없습니다. N1QL은 각 문서에 적용 가능한 필터를 적용합니다. 이 액세스 방법은 비용이 상당히 많이 들며 버킷의 문서 수에 따라 결과를 반환하는 평균 시간이 선형적으로 증가합니다.
c. 인덱스 스캔 액세스: 적격 보조 인덱스 스캔은 먼저 키스페이스를 필터링하고 적격 문서 ID를 결정하는 데 사용됩니다. 그런 다음 데이터 저장소에서 문서를 검색합니다. Couchbase에서 보조 인덱스는 VIEW 인덱스 또는 글로벌 보조 인덱스(GSI)일 수 있습니다.
조인 방법
N1QL은 모든 조인 지원에서 중첩 루프 액세스 방법을 지원합니다: INNER JOIN 및 LEFT OUTER JOIN. 다음은 조인 메서드에 대한 가장 간단한 설명입니다.
FROM (ORDERS o INNER JOIN CUSTOMER c ON KEYS o.O_C_ID)
이 조인에서 ORDERS는 INNER 키스페이스가 되고 CUSTOMER는 OUTER 키스페이스가 됩니다. ORDERS 키스페이스가 먼저 스캔됩니다(키스페이스 스캔 옵션 중 하나 사용). ORDERS의 각 적격 문서에 대해 ORDERS 문서의 키 O_C_D를 기준으로 CUSTOMER에서 키스캔을 수행합니다.
조인 주문
FROM 절에 지정된 키 스페이스는 쿼리에 지정된 정확한 순서대로 조인됩니다.
이 블로그에서는 예제를 통해 플래너를 살펴보겠습니다. 여기서는 4개의 키 스페이스(버킷)를 사용하겠습니다. 이러한 버킷에 대한 예제 문서는 이 블로그의 마지막에 나와 있습니다. TPCC에 익숙한 분들은 이 표를 알아보실 수 있습니다.
이러한 각 키 공간에는 기본 키 인덱스와 다음과 같은 보조 인덱스가 있습니다.
gsi를 사용하여 CUSTOMER(C_ID, C_D_ID, C_W_ID)에 CU_ID_D_ID_W_ID 인덱스를 생성합니다;
gsi를 사용하여 STOCK(S_I_ID, S_W_ID)에 ST_W_ID,I_ID 인덱스를 생성합니다;
gsi를 사용하여 OR_O_ID_D_ID_W_ID 인덱스를 OR_O_ID(O_ID, O_D_ID, O_W_ID, O_C_ID)에 생성합니다;
gsi를 사용하여 ORDER_LINE(OL_O_ID, OL_D_ID, OL_W_ID)에 인덱스 OL_O_ID_D_ID_W_ID를 생성합니다;
gsi를 사용하여 ITEM(I_ID)에 인덱스 IT_ID를 생성합니다;
예 1:
문서 키를 알고 있는 경우 각 키 공간에 대해 USE KEYS 절로 지정합니다. USE KEYS 절을 지정하면 KEYSCAN 액세스 경로가 선택됩니다. 키가 주어지면 KEYSCAN은 각 키 공간에서 문서를 검색합니다. 노드 를 효율적으로 사용합니다. 특정 문서를 검색한 후 쿼리 노드는 c.C_STATE = "CA" 필터를 적용합니다.
cbq> EXPLAIN select * from CUSTOMER c USE KEYS ["110192", "120143", "827482"] WHERE c.C_STATE = "CA";
{
"요청ID": “991e69d2-b6f9-42a1-9bd1-26a5468b0b5f”,
"서명": "json",
"결과": [
{
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "키스캔",
"keys": "["110192", "120143", "827482"]"
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "Fetch",
"as": "c",
"키스페이스": "고객",
"네임스페이스": "default"
},
{
"1TP5운영자": "필터",
"조건": "((
c.C_STATE) = "CA")" },
{
"1TP5운영자": "초기 프로젝트",
"결과_기간": [
{
"별": true
}
]
},
{
"1TP5운영자": "최종 프로젝트"
}
]
}
}
]
}
],
"상태": "성공",
"metrics": {
"elapsedTime": "10.311912ms",
"실행 시간": "10.205523ms",
"resultCount": 1,
"결과 크기": 1403
}
}
예 2:
이 경우 쿼리는 CUSTOMER 버킷에 있는 모든 "CA" 고객을 계산하려고 합니다. 키-값에 대한 인덱스가 없으므로 키 공간(버킷)의 기본 스캔인 c.C_YTD_PAYMENT가 선택됩니다. c.C_YTD_PAYMENT < 100 필터링
은 문서가 검색된 후에 적용됩니다. 물론 큰 버킷의 경우 1차 스캔에는 시간이 걸립니다. 애플리케이션 성능 계획의 일환으로 필터 내에서 자주 사용되는 키 값에 대한 관련 보조 인덱스를 만드세요.
N1QL은 쿼리 실행 계획 내의 많은 단계를 병렬화합니다. 이 쿼리의 경우 쿼리 실행 내에서 가져오기 및 필터링 애플리케이션이 병렬화됩니다.
cbq> EXPLAIN SELECT c.C_STATE AS state, COUNT(*) AS st_count
고객으로부터 c
WHERE c.C_YTD_PAYMENT < 100
상태별 그룹화
ORDER BY st_count desc;
"결과": [
{
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "PrimaryScan",
"index": "#primary",
"키스페이스": "고객",
"네임스페이스": "기본값",
"사용": "gsi"
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "Fetch",
"as": "c",
"키스페이스": "고객",
"네임스페이스": "default"
},
{
"1TP5운영자": "필터",
"조건": "((
c.C_YTD_PAYMENT) u003c 100)" },
{
"1TP5운영자": "초기그룹",
"집계": [
"count(*)"
],
"그룹_키": [
“(
c.상태)” ]
},
{
"1TP5운영자": "중간 그룹",
"집계": [
"count(*)"
],
"그룹_키": [
“(
c.상태)” ]
}
]
}
},
{
"1TP5운영자": "중간 그룹",
"집계": [
"count(*)"
],
"그룹_키": [
“(
c.상태)” ]
},
{
"1TP5운영자": "최종그룹",
"집계": [
"count(*)"
],
"그룹_키": [
“(
c.상태)” ]
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "초기 프로젝트",
"결과_기간": [
{
"as": "상태",
"expr": "(
c.C_STATE)” },
{
"as": "st_count",
"expr": "count(*)"
}
]
}
]
}
}
]
},
{
"1TP5운영자": "주문",
"sort_terms": [
{
"desc": true,
"expr": "
st_count“ }
]
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "최종 프로젝트"
}
}
]
}
],
예 3:
이 예에서는 키 스페이스 ORDER_LINE을 ITEM과 조인합니다. ORDER_LINE의 각 적격 문서에 대해 ITEM과 일치시키려고 합니다. ON 절이 흥미롭습니다. 여기에서는 키 공간 ORDER_LINE(TO_STRING(ol.OL_I_ID))에 대한 키만 지정하고 ITEM에 대해서는 아무것도 지정하지 않습니다. 이는 암시적으로 ITEM의 문서 키와 결합되어 있기 때문입니다.
N1QL의 FROM 절입니다: ORDER_LINE ol INNER JOIN ITEM i
ON 키 (TO_STRING(ol.OL_I_ID))
SQL과 동일합니다: ORDER_LINE ol INNER JOIN ITEM i
ON (TO_STRING(ol.OL_I_ID) = meta(ITEM).id)
필드가 문자열이 아닌 경우 TO_STRING() 표현식을 사용하여 문자열로 변환할 수 있습니다. 문서와 함께 여러 필드를 사용하여 문서 키를 구성할 수도 있습니다.
예: 주문에서 o 왼쪽 외부 조인 고객 c
ON KEYS (TO_STRING(o.O_C_ID) || TO_STRING(o.O_D_ID))
요약하자면, N1QL에서 JOIN 쿼리를 작성할 때는 키 공간에서 문서 키가 어떻게 구성되는지 이해하는 것이 중요합니다. 결론은 데이터 모델링 중에 이러한 점을 고려해야 한다는 것입니다.
먼저, 사용 가능한 액세스 경로에 따라 주어진 필터 세트에 대해 ORDER_LINE 키 공간을 스캔하기 위해 플래너는 인덱스에서 인덱스 스캔을 선택합니다. OLO_ID_ID_W_ID. 앞서 설명한 것처럼, 조인의 다른 키 공간에 대한 액세스 경로는 항상 기본 키 인덱스를 사용하는 키스캔입니다. 이 계획에서는 먼저 ORDER_LINE 키 스페이스에서 인덱스 스캔을 수행하여 가능한 필터를 인덱스 스캔에 적용합니다. 그런 다음 적격 문서를 검색하고 추가 필터를 적용합니다. 문서가 적격하면 해당 문서는 ITEM과 결합됩니다.
cbq> EXPLAIN SELECT COUNT(DISTINCT(ol.OL_I_ID)) AS CNT_OL_I_ID
FROM ORDER_LINE ol INNER JOIN ITEM i ON 키 (TO_STRING(ol.OL_I_ID))
WHERE ol.OL_W_ID = 1
AND ol.OL_D_ID = 10
AND ol.OL_O_ID < 200
AND ol.OL_O_ID >= 100
AND ol.S_W_ID = 1
AND i.I_PRICE < 10.00;
{
"요청ID": “4e0822fb-0317-48a0-904b-74c607f77b2f”,
"서명": "json",
"결과": [
{
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "IndexScan",
"index": "ol_o_id_d_id_w_id",
"키스페이스": "ORDER_LINE",
"limit": 9.223372036854776e+18,
"네임스페이스": "기본값",
"스팬": [
{
"범위": {
"높음": [
“200”
],
"포함": 1,
"낮음": [
“100”
]
},
"Seek": null
}
],
"사용": "gsi"
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "Fetch",
"as": "ol",
"키스페이스": "ORDER_LINE",
"네임스페이스": "default"
},
{
"1TP5운영자": "Join",
"as": "i",
"키스페이스": "ITEM",
"네임스페이스": "기본값",
"on_keys": "to_string((
ol.OL_I_ID))” },
{
"1TP5운영자": "필터",
"조건": "(((((((
ol.OL_W_ID) = 1) 및 ((ol.OL_D_ID) = 10)) 및 ((ol.OL_O_ID) u003c 200)) 및 (100 u003c= (ol.OL_O_ID))) 및 ((ol.S_W_ID) = 1)) 및 ((i.I_PRICE) u003c 10))" },
{
"1TP5운영자": "초기그룹",
"집계": [
"count(distinct (
ol.OL_I_ID))” ],
"그룹_키": []
},
{
"1TP5운영자": "중간 그룹",
"집계": [
"count(distinct (
ol.OL_I_ID))” ],
"그룹_키": []
}
]
}
},
{
"1TP5운영자": "중간 그룹",
"집계": [
"count(distinct (
ol.OL_I_ID))” ],
"그룹_키": []
},
{
"1TP5운영자": "최종그룹",
"집계": [
"count(distinct (
ol.OL_I_ID))” ],
"그룹_키": []
},
{
"1TP5운영자": "병렬",
"~자식": {
"1TP5운영자": "시퀀스",
"~어린이": [
{
"1TP5운영자": "초기 프로젝트",
"결과_기간": [
{
"as": "CNT_OL_I_ID",
"expr": "count(distinct (
ol.OL_I_ID))” }
]
},
{
"1TP5운영자": "최종 프로젝트"
}
]
}
}
]
}
],
"상태": "성공",
"metrics": {
"elapsedTime": "272.823508ms",
"실행 시간": "272.71231ms",
"resultCount": 1,
"결과 크기": 4047
}
}
문서 예시:
데이터는 다음의 수정된 스크립트에서 생성됩니다. https://github.com/apavlo/py-tpcc
고객
CUSTOMER 한도 1에서 * 메타(CUSTOMER).id를 PKID로 선택합니다;
"결과": [
{
"CUSTOMER": {
"C_BALANCE": -10,
"C_CITY": "트조트무이브호프",
"C_CREDIT": "GC",
"c_credit_lim": 50000,
"C_DATA": “sjlhfnvosawyjedregoctclndqzioadurtnlslwvuyjeowzedlvypsudcuerdzvdpsvjfecouyavnyyemivgrcyxxjsjcmkejvekzetxryhxjlhzkzajiaijammtyioheqfgtbhekdisjypxoymfsaepqkzbitdrpsjppivjatcwxxipjnloeqdswmogstqvkxlzjnffikuexjjofvhxdzleymajmifgzzdbdfvpwuhlujvycwlsgfdfodhfwiepafifbippyonhtahsbigieznbjrmvnjxphzfjuedxuklntghfckfljijfeyznxvwhfvnuhsecqxcmnivfpnawvgjjizdkaewdidhw”,
"c_delivery_cnt": 0,
"C_DISCOUNT": 0.3866,
"C_D_ID": 10,
"C_FIRST": "ujmduarngl",
"C_ID": 1938,
"C_LAST": "프레싱바",
"C_MIDDLE": "OE",
"C_PAYMENT_CNT": 1,
"C_PHONE": "6347232262068241",
"C_SINCE": "2015-03-22 00:50:42.822518",
"C_STATE": "ta",
"C_STREET_1": "데이로비르누크리",
"C_STREET_2": "고즈주아크바브웨",
"C_W_ID": 1,
"C_YTD_PAYMENT": 10,
"C_ZIP": "316011111"
},
"PKID": "1101938"
}
],
ITEM
ITEM 한도 1에서 * 메타(ITEM).id를 PKID로 선택합니다;
"결과": [
{
"ITEM": {
"I_DATA": "DMNJRKNHCNRUJBTKRIRBDDKNXUXYFABOPHX",
"I_ID": 10425,
"I_IM_ID": 1013,
"I_NAME": "aegfkkcbllssxxz",
"I_PRICE": 60.31
},
"PKID": "10425"
}
],
주문
메타(ORDERS).id를 PKID로, *를 ORDERS 제한 1에서 선택합니다;
"결과": [
{
"ORDERS": {
"O_ALL_LOCAL": 1,
"O_CARRIER_ID": 2,
"O_C_ID": 574,
"O_D_ID": 10,
"O_ENTRY_D": "2015-03-22 00:50:44.748030",
"O_ID": 1244,
"O_OL_CNT": 12,
"O_W_ID": 1
},
"PKID": "1101244"
}
],
cbq> select meta(ORDER_LINE).id as PKID, * from ORDER_LINE limit 1;
"결과": [
{
"ORDER_LINE": {
"OL_AMOUNT": 0,
"ol_delivery_d": "2015-03-22 00:50:44.836776",
"ol_dist_info": "OIUKBNBCAZONUBTQZIUVDDI",
"OL_D_ID": 10,
"O_I_ID": 23522,
"OL_NUMBER": 3,
"OL_O_ID": 1389,
"OL_QUANTITY": 5,
"OL_SUPPLY_W_ID": 1,
"OL_W_ID": 1
},
"PKID": "11013893"
}
],
CONTAINS, REGEXP_CONTAINS와 같은 문자열 및 REGEXP 함수에서 GSI를 만들려면 어떻게 해야 하나요?
"쿼리 실행 내에서 가져오기 및 필터링 애플리케이션이 병렬 처리됩니다."를 이해하는 방법
데이터를 가져온 후에 필터가 실행되어야 한다고 생각하나요?