SQL para documentos: Breve introdução ao planejamento de consultas.
Eu escrevi anterior sobre a necessidade de um novo tipo de SQL e a introdução do SQL para documentos (N1QL). Neste blog, discutiremos a fase de planejamento de consultas do N1QL na próxima versão do Couchbase Sherlock. O próprio N1QL passou por várias prévias de desenvolvedor. Confira o tutorial on-line em https://query.couchbase.com Faça o download da prévia do desenvolvedor da versão Sherlock do Couchbase, que inclui SQL para documentos (N1QL).
O que é o N1QL? É um mecanismo moderno de processamento de consultas projetado para fornecer SQL para documentos (por exemplo, JSON) em uma arquitetura distribuída moderna com modelo de dados flexível. Os bancos de dados modernos são implantados em clusters maciços e usam um modelo de dados flexível (JSON, família de colunas etc.). O N1QL oferece suporte a SQL aprimorado sobre esse modelo de dados para facilitar o processamento de consultas.
Execução de consultas: Visão geral
Os aplicativos e seus drivers enviam a consulta N1QL a um dos nós de consulta disponíveis. O nó de consulta analisa a consulta e usa metadados em objetos subjacentes para descobrir o plano de execução ideal, que é então executado. Durante a execução, dependendo da consulta, usando os índices aplicáveis, o nó de consulta trabalha com os nós de índice e de dados para recuperar e executar as operações select-join-project. Como o Couchbase é um banco de dados em cluster, você dimensiona os nós de dados, índice e consulta para atender às suas metas de desempenho e disponibilidade.
Execução da consulta: Visão interna
Esta figura (graças ao arquiteto do @N1QL, Gerald Sangudi, pelas figuras) mostra todas as fases possíveis pelas quais uma consulta passa para retornar os resultados. Nem todas as consultas precisam passar por todas as fases, algumas passam por muitas dessas fases várias vezes. Por exemplo, a fase de ordenação pode ser ignorada quando não há cláusula ORDER BY na consulta; a fase de varredura-busca-união pode ser feita várias vezes para executar várias uniões.
O N1QL analisa a consulta e as opções de caminho de acesso disponíveis para cada espaço-chave (tabela ou bucket) na consulta para criar um plano de consulta e uma infraestrutura de execução. O planejador precisa primeiro selecionar o caminho de acesso para cada bucket, determinar a ordem de junção e, em seguida, determinar o tipo de junção. Depois que as grandes decisões são tomadas, o planejador cria a infraestrutura necessária para executar o plano. Algumas operações, como análise e planejamento de consultas, são feitas em série, enquanto outras operações, como busca, junção e classificação, são feitas em paralelo. Neste blog, vamos nos concentrar em uma breve visão geral da fase de planejamento de consultas usando exemplos.
Seleção do caminho de acesso:
Opções de caminho de acesso ao espaço-chave (Bucket)
a. Acesso Keyscan. Quando IDs de documentos específicos (chaves) estão disponíveis, o método de acesso Keyscan recupera documentos para esses IDs. Qualquer filtro nesse espaço-chave é aplicado depois disso. O método de acesso Keyscan pode ser usado quando um espaço-chave é consultado por si só ou durante o processamento de junção. O keyscan é normalmente usado para recuperar documentos qualificados para o espaço-chave interno durante o processamento de junção.
b. Acesso PrimaryScan: Equivale à varredura completa da tabela em sistemas de bancos de dados relacionais. Os IDs dos documentos não são fornecidos e nenhum método de acesso secundário qualificado está disponível para esse espaço-chave. O N1QL aplicará os filtros aplicáveis em cada documento. Esse método de acesso é bastante caro e o tempo médio para retornar os resultados aumenta linearmente com o número de documentos no intervalo.
c. Acesso ao IndexScan: Uma varredura de índice secundário qualificado é usada para filtrar primeiro o espaço-chave e determinar as IDs dos documentos qualificados. Em seguida, ele recupera os documentos do armazenamento de dados. No Couchbase, o índice secundário pode ser um índice VIEW ou um índice secundário global (GSI).
Métodos JOIN
O N1QL oferece suporte ao método de acesso de loop aninhado para todos os suportes de junções: INNER JOIN e LEFT OUTER JOIN. Aqui está a explicação mais simples do método de união.
FROM (ORDERS o INNER JOIN CUSTOMER c ON KEYS o.O_C_ID)
Para essa união, ORDERS se torna o espaço-chave INNER e CUSTOMER se torna o espaço-chave OUTER. O espaço-chave ORDERS é verificado primeiro (usando uma das opções de verificação de espaço-chave). Para cada documento qualificado em ORDERS, fazemos um KEYSCAN em CUSTOMER com base na chave O_C_D no documento ORDERS.
JOIN order
Pois os espaços-chave especificados na cláusula FROM são unidos na ordem exata fornecida na consulta.
Neste blog, vamos analisar o planejador por meio de exemplos. Usarei 4 espaços-chave (compartimentos) para esses exemplos. Exemplos de documentos para esses compartimentos são fornecidos no final deste blog. Quem estiver familiarizado com o TPCC reconhecerá essas tabelas.
Cada um desses espaços-chave tem um índice de chave primária e os seguintes índices secundários.
criar índice CU_ID_D_ID_W_ID em CUSTOMER(C_ID, C_D_ID, C_W_ID) usando gsi;
criar índice ST_W_ID,I_ID em STOCK(S_I_ID, S_W_ID) usando gsi;
criar índice OR_O_ID_D_ID_W_ID em ORDERS(O_ID, O_D_ID, O_W_ID, O_C_ID) usando gsi;
criar índice OL_O_ID_D_ID_W_ID em ORDER_LINE(OL_O_ID, OL_D_ID, OL_W_ID) usando gsi;
criar o índice IT_ID em ITEM(I_ID) usando gsi;
Exemplo 1:
Se você conhece as chaves do documento, especifique com a cláusula USE KEYS para cada espaço-chave. Quando uma cláusula USE KEYS é especificada, o caminho de acesso do KEYSCAN é escolhido. Dadas as chaves, o KEYSCAN recuperará os documentos dos respectivos nós com eficiência. Depois de recuperar os documentos específicos, o nó de consulta aplica o filtro c.C_STATE = "CA".
cbq> EXPLAIN select * from CUSTOMER c USE KEYS ["110192", "120143", "827482"] WHERE c.C_STATE = "CA";
{
"requestID": “991e69d2-b6f9-42a1-9bd1-26a5468b0b5f”,
"signature" (assinatura): "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "KeyScan",
"chaves": "["110192", "120143", "827482"]"
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "c",
"Espaço-chave": "CUSTOMER",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((
c.C_STATE) = "CA")" },
{
"#operator": "InitialProject",
"result_terms": [
{
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "10.311912ms",
"executionTime": "10.205523ms",
"resultCount": 1,
"resultSize": 1403
}
}
Exemplo 2:
Nesse caso, a consulta está procurando contar todos os clientes "CA" no bucket CUSTOMER. Como não temos um índice sobre o valor-chave, c.C_YTD_PAYMENT, optamos por uma varredura primária do espaço-chave (bucket). Filtro c.C_YTD_PAYMENT < 100
é aplicado depois que o documento é recuperado. Obviamente, para compartimentos maiores, a varredura primária leva tempo. Como parte do planejamento do desempenho do aplicativo, crie índices secundários relevantes sobre valores-chave usados com frequência nos filtros.
O N1QL paraleliza muitas das fases do plano de execução da consulta. Para essa consulta, os aplicativos de busca e filtragem são paralelizados na execução da consulta.
cbq> EXPLAIN SELECT c.C_STATE AS state, COUNT(*) AS st_count
FROM CUSTOMER c
WHERE c.C_YTD_PAYMENT < 100
GROUP BY estado
ORDER BY st_count desc;
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"Espaço-chave": "CUSTOMER",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "c",
"Espaço-chave": "CUSTOMER",
"namespace": "default"
},
{
"#operator": "Filter",
"condition": "((
c.C_YTD_PAYMENT) u003c 100)" },
{
"#operator": "InitialGroup",
"agregados": [
"count(*)"
],
"group_keys": [
“(
c.estado)” ]
},
{
"#operator": "IntermediateGroup" (Grupo intermediário),
"agregados": [
"count(*)"
],
"group_keys": [
“(
c.estado)” ]
}
]
}
},
{
"#operator": "IntermediateGroup" (Grupo intermediário),
"agregados": [
"count(*)"
],
"group_keys": [
“(
c.estado)” ]
},
{
"#operator": "FinalGroup",
"agregados": [
"count(*)"
],
"group_keys": [
“(
c.estado)” ]
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "state",
"expr": "(
c.C_STATE)” },
{
"as": "st_count",
"expr": "count(*)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"desc": true,
"expr": "
st_count“ }
]
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "FinalProject"
}
}
]
}
],
Exemplo 3:
Neste exemplo, juntamos o espaço-chave ORDER_LINE com ITEM. Para cada documento qualificado em ORDER_LINE, queremos fazer a correspondência com ITEM. A cláusula ON é interessante. Aqui, você especifica apenas as chaves para o espaço-chave ORDER_LINE (TO_STRING(ol.OL_I_ID)) e nada para ITEM. Isso ocorre porque ele é implicitamente associado à chave do documento do ITEM.
Cláusula FROM do N1QL: ORDER_LINE ol INNER JOIN ITEM i
ON KEYS (TO_STRING(ol.OL_I_ID))
É equivalente ao SQL: ORDER_LINE ol INNER JOIN ITEM i
ON (TO_STRING(ol.OL_I_ID) = meta(ITEM).id)
Se o campo não for uma cadeia de caracteres, ele poderá ser convertido em uma cadeia de caracteres usando a expressão TO_STRING(). Você também pode construir a chave do documento usando vários campos com o documento.
Por exemplo, FROM ORDERS o LEFT OUTER JOIN CUSTOMER c
ON KEYS (TO_STRING(o.O_C_ID) || TO_STRING(o.O_D_ID))
O resumo é que, ao escrever consultas JOIN no N1QL, é importante entender como a chave do documento é construída no espaço-chave. O corolário é que é preciso pensar nisso durante a modelagem de dados.
Primeiro, para verificar o espaço-chave ORDER_LINE, para o conjunto de filtros fornecido, com base no caminho de acesso disponível, o planejador escolhe a verificação do índice no índice OL_O_ID_D_ID_W_ID. Como discutimos anteriormente, o caminho de acesso no outro espaço de chaves na união é sempre uma varredura de chaves usando o índice de chave primária. Nesse plano, primeiro fazemos a varredura de índice no espaço de chave ORDER_LINE, colocando os possíveis filtros na varredura de índice. Em seguida, recuperamos o documento qualificado e aplicamos filtros adicionais. Se o documento se qualificar, ele será unido ao ITEM.
cbq> EXPLAIN SELECT COUNT(DISTINCT(ol.OL_I_ID)) AS CNT_OL_I_ID
FROM ORDER_LINE ol INNER JOIN ITEM i ON KEYS (TO_STRING(ol.OL_I_ID))
WHERE ol.OL_W_ID = 1
E ol.OL_D_ID = 10
E ol.OL_O_ID < 200
E ol.OL_O_ID >= 100
E ol.S_W_ID = 1
E i.I_PRICE < 10,00;
{
"requestID": “4e0822fb-0317-48a0-904b-74c607f77b2f”,
"signature" (assinatura): "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "OL_O_ID_D_ID_W_ID",
"Espaço-chave": "ORDER_LINE",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range": {
"Alto": [
“200”
],
"Inclusão": 1,
"Low": [
“100”
]
},
"Seek": nulo
}
],
"using": "gsi"
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "ol",
"Espaço-chave": "ORDER_LINE",
"namespace": "default"
},
{
"#operator": "Join",
"as": "i",
"Espaço-chave": "ITEM",
"namespace": "default",
"on_keys": "to_string((
ol.OL_I_ID))” },
{
"#operator": "Filter",
"condition": "(((((((
ol.OL_W_ID) = 1) e ((ol.OL_D_ID) = 10)) e ((ol.OL_O_ID) u003c 200)) e (100 u003c= (ol.OL_O_ID))) e ((ol.S_W_ID) = 1)) e ((i.I_PRICE) u003c 10))" },
{
"#operator": "InitialGroup",
"agregados": [
"count(distinct (
ol.OL_I_ID))” ],
"group_keys": []
},
{
"#operator": "IntermediateGroup" (Grupo intermediário),
"agregados": [
"count(distinct (
ol.OL_I_ID))” ],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup" (Grupo intermediário),
"agregados": [
"count(distinct (
ol.OL_I_ID))” ],
"group_keys": []
},
{
"#operator": "FinalGroup",
"agregados": [
"count(distinct (
ol.OL_I_ID))” ],
"group_keys": []
},
{
"#operator": "Paralelo",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "CNT_OL_I_ID",
"expr": "count(distinct (
ol.OL_I_ID))” }
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "272.823508ms",
"executionTime": "272.71231ms",
"resultCount": 1,
"resultSize": 4047
}
}
Exemplos de documentos:
Os dados são gerados a partir de scripts modificados de: https://github.com/apavlo/py-tpcc
CLIENTE
select meta(CUSTOMER).id as PKID, * from CUSTOMER limit 1;
"results": [
{
"CUSTOMER": {
"C_BALANCE": -10,
"C_CITY": "ttzotwmuivhof",
"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": "PRESEINGBAR",
"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": "deilobyrnukri",
"C_STREET_2": "goziejuaqbbwe",
"C_W_ID": 1,
"C_YTD_PAYMENT": 10,
"C_ZIP": "316011111"
},
"PKID": "1101938"
}
],
ITEM
select meta(ITEM).id as PKID, * from ITEM limit 1;
"results": [
{
"ITEM": {
"I_DATA": "dmnjrkhncnrujbtkrirbddknxuxiyfabopmhx",
"I_ID": 10425,
"I_IM_ID": 1013,
"I_NAME": "aegfkkcbllssxxz",
"I_PRICE": 60.31
},
"PKID": "10425"
}
],
PEDIDOS
select meta(ORDERS).id as PKID, * from ORDERS limit 1;
"results": [
{
"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;
"results": [
{
"ORDER_LINE": {
"OL_AMOUNT": 0,
"OL_DELIVERY_D": "2015-03-22 00:50:44.836776",
"OL_DIST_INFO": "oiukbnbcazonubtqziuvcddi",
"OL_D_ID": 10,
"OL_I_ID": 23522,
"OL_NUMBER": 3,
"OL_O_ID": 1389,
"OL_QUANTITY": 5,
"OL_SUPPLY_W_ID": 1,
"OL_W_ID": 1
},
"PKID": "11013893"
}
],
como podemos criar GSI em STRING E REXEXP FUNCATIONS COMO CONTAINS, REGEXP_CONTAINS
Como entender \"os aplicativos de busca e filtragem são paralelizados na execução da consulta\".
Acho que o filtro deve ser executado após a busca de dados?