Servidor Couchbase 7.0 (Enterprise Edition) introduziu um otimizador baseado em custo (CBO). As versões anteriores do Couchbase Server usavam um otimizador baseado em regras (RBO). O Couchbase Server evoluiu naturalmente do RBO para o CBO, como a maioria dos principais sistemas de bancos de dados relacionais.
O CBO usa estimativas de custo para escolher um plano de consulta calculado a partir de estatísticas do otimizador. As estatísticas do otimizador são coletadas usando as instruções UPDATE STATISTICS ou ANALYZE em uma consulta SQL++ (denominada abaixo como N1QL).
Declaração UPDATE STATISTICS/ANALYZE
O novo UPDATE STATISTICS ou ANALYZE acionam a coleta de estatísticas do otimizador necessárias para a funcionalidade do CBO. Atualmente, um usuário emite essas novas instruções manualmente, o que calcula as estatísticas do otimizador no espaço-chave/coleção, os índices definidos no espaço-chave/coleção e as informações de distribuição (como histogramas) em uma lista de termos (expressões).
Quando há vários nós de consulta, as estatísticas do otimizador coletadas de um nó de consulta estão disponíveis em todos os nós de consulta.
O novo N1QL_SYSTEM_BUCKET
O novo otimizador coleta estatísticas por meio do ATUALIZA ESTATÍSTICAS/ANÁLISES e os armazena em um novo bucket (N1QL_SYSTEM_BUCKET), especificamente em um novo escopo (N1QL_SYSTEM_SCOPE) e uma nova coleção (N1QL_CBO_STATS).
Na primeira vez em que é executado, o ATUALIZAR ESTATÍSTICAS/ANALISAR cria automaticamente esse novo bucket/escopo/coleção. Um índice primário também é criado automaticamente.
Descarte do novo bucket/escopo/coleção
Esses novos objetos do sistema (bucket, escopo, coleção) podem ser descartados do banco de dados, o que faz com que todas as estatísticas do otimizador não estejam mais disponíveis.
Qualquer consulta futura não aproveitará a funcionalidade CBO até que um usuário solicite que as estatísticas sejam analisadas novamente. Até esse momento, o sistema volta ao RBO para o planejamento da consulta.
Cota de memória para o novo bucket/escopo/coleção
O novo balde do sistema (N1QL_SYSTEM_BUCKET) é criado com uma cota de memória mínima de 100 MB. Em um sistema grande, os DBAs devem monitorar a residência de memória no bucket e aumentar a cota de memória quando necessário para evitar que a residência de memória caia abaixo do nível recomendado de 15%.
Sintaxe
Em um alto nível, há três aspectos de um comando UPDATE STATISTICS/ANALYZE:
1. Estatísticas de espaço-chave/coleta - coletados toda vez que uma instrução de atualização/análise é emitida
2. Estatísticas do índice - coletados para índices "relevantes
3. Estatísticas de distribuição (histograma) - reunidos nos termos especificados.
Há várias maneiras de especificar a lista de termos para os quais as estatísticas de distribuição são coletadas.
Fornecimento de uma lista de termos para a criação de estatísticas
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS [PARA] Espaço-chave(termo1, termo2, ...) ANALISAR [ESPAÇO-CHAVE/COLEÇÃO] Espaço-chave(termo1, termo2, ...) |
Essas duas declarações são equivalentes. A ESTATÍSTICAS DE ATUALIZAÇÃO tem uma palavra-chave FOR opcional seguida de um nome de espaço-chave. O comando ANALISAR tem uma instrução opcional ESPAÇO-CHAVE ou COLEÇÃO seguida de um nome de espaço-chave.
O nome do espaço-chave é normalmente um nome de coleção. Você pode especificar um nome de bucket como o nome do espaço-chave; nesse caso, as estatísticas do otimizador são coletadas no Padrão coleta sob o Padrão escopo para esse balde.
A lista de termos é semelhante à lista de expressões usadas no CRIAR ÍNDICE declaração. De fato, qualquer expressão suportada em um CRIAR ÍNDICE também pode ser usado como um termo em uma declaração ATUALIZAR ESTATÍSTICAS/ANALISAR declaração. Por exemplo:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA `viagens-amostra`.inventário.aeroporto(cidade, país) ANALISAR `viagens-amostra`.inventário.aeroporto(cidade, país) |
As declarações acima podem ser simplificadas se o contexto de consulta é definido como travel-sample.inventory:
|
1 2 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto(cidade, país) ANALISAR aeroporto(cidade, país) |
Expressão de chave funcional
Semelhante à especificação de uma chave de índice funcional em um CRIAR ÍNDICE também é possível especificar uma tecla de função em um comando ATUALIZAR ESTATÍSTICAS/ANALISAR declaração. Por exemplo:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto(inferior(país)) ANALISAR aeroporto(inferior(país)) |
Expressão de chave de matriz
Como especificar uma chave de índice de matriz em um CRIAR ÍNDICE também é possível especificar uma chave de índice de matriz em uma instrução ATUALIZAR ESTATÍSTICAS/ANALISAR declaração. Por exemplo:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA rota(DISTINTO ARRAY v.voo PARA v IN cronograma FIM) ANALISAR COLEÇÃO rota(DISTINTO ARRAY v.voo PARA v IN cronograma FIM) |
ou usando a versão abreviada:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA hotel(TODOS public_likes) ANALISAR ESPAÇO-CHAVE hotel(TODOS public_likes) |
Análise de um ou mais índices
Os comandos Analyze podem, opcionalmente, especificar um ou mais índices usando a opção ÍNDICE opção:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto ÍNDICE(def_inventory_airport_city, def_inventory_airport_faa) ANALISAR aeroporto ÍNDICE(def_inventory_airport_city, def_inventory_airport_faa) |
Quando o ÍNDICE for especificada, a lista de expressões de chave de índice para todos os índices será usada como a lista de termos para a opção ATUALIZAR ESTATÍSTICAS/ANALISAR declaração.
Expressões no ONDE para um índice parcial também são adicionados aos termos para análise. Assim, a cláusula ÍNDICE é um atalho conveniente, pois o usuário não precisa listar todos os termos explicitamente.
As declarações acima são equivalentes às seguintes, pois cada índice tem apenas uma única chave de índice e não há ONDE para qualquer índice referenciado:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto(cidade, faa) ANALISAR aeroporto(cidade, faa) |
Análise de todos os índices
Para ir além, você pode usar a opção INDEX ALL para uma instrução UPDATE STATISTICS/ANALYZE, que considera todos os índices disponíveis definidos na coleção e que estão em um estado on-line. Por exemplo:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto ÍNDICE TODOS ANALISAR aeroporto ÍNDICE TODOS |
Embora outras opções permitam que você especifique um nome de bucket ou um nome de coleção, a opção INDEXAR TUDO só é válida para uma coleção. Especificando INDEXAR TUDO com um nome de bucket retornará um erro.
Índice de residência
A análise também atualiza as estatísticas do otimizador para os índices relevantes. Quando uma lista de termos é especificada, a lista de índices aplicável é construída verificando as chaves de índice que fazem referência a qualquer termo da lista. As listas de índices relevantes são deduzidas dos índices quando o parâmetro ÍNDICE ou ÍNDICE ALA opção L é usada.
As estatísticas do otimizador de um índice só podem ser coletadas quando o índice é, pelo menos parcialmente, residente na memória. As estatísticas do otimizador são coletadas da parte residente na memória de um índice. Uma proporção maior de residentes na memória produz estatísticas de otimizador mais precisas para esse índice.
Se um índice tiver uma proporção de zero residente na memória, os avisos a seguir serão retornados pelo comando de otimização:
|
1 2 3 4 5 6 |
"avisos": [ { "código": 5390, "msg": "O índice def_inventory_airport_city não está na memória" } ], |
Cláusula opcional WITH
Semelhante ao CRIAR ÍNDICE você também pode especificar uma instrução opcional COM para adicionar opções de análise estatística. Por exemplo:
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto(cidade, país) COM {"resolução": 0.5} ANALISAR aeroporto(cidade, país) COM {"resolução": 0.5} |
As seguintes opções são atualmente suportadas no COM cláusula de um ATUALIZAR ESTATÍSTICAS/ANALISAR declaração: resolução, sample_size, batch_size, update_statistics_timeout. Cada um deles é abordado a seguir.
A opção de resolução
A estatística de distribuição do termo (histograma) é representada como uma série de "compartimentos". A resolução indica qual porcentagem de documentos está representada em cada compartimento. Por padrão, é usada uma resolução de 1,0 (ou seja, 100 compartimentos).
Você pode especificar uma resolução diferente na cláusula WITH. Por exemplo, se você precisar de uma granularidade mais fina das estatísticas de distribuição, poderá especificar uma resolução menor (correspondente a um número maior de compartimentos).
Observe que quanto menor a resolução (maior número de compartimentos), mais amostras são necessárias para construir o histograma e mais tempo/recursos são necessários para concluir o processo. ATUALIZAR ESTATÍSTICAS/ANALISAR declaração.
O intervalo válido da opção de resolução é de 0,02 a 5,0.
A opção sample_size
As amostras são necessárias para construir as estatísticas de distribuição (histograma) para os termos especificados em um ATUALIZAR ESTATÍSTICAS/ANALISAR declaração. A resolução determina o tamanho da amostra e não está diretamente relacionada ao número de documentos em uma coleção.
Você pode especificar um tamanho de amostra a ser usado. No entanto, é necessário um tamanho mínimo de amostra para uma determinada resolução. Se o tamanho da amostra for menor do que o tamanho mínimo de amostra exigido, será usado o tamanho mínimo de amostra exigido, e um aviso será retornado.
A opção batch_size
A construção de histogramas exige uma operação de classificação e, portanto, consome muitos recursos. Para evitar a utilização excessiva de recursos, os histogramas são construídos em lotes. Por padrão, cada lote processa até 10 termos para a construção do histograma. Se houver mais termos listados ou se a combinação das expressões de chave de índice (dos índices especificados pelas opções INDEX) exceder dez termos, serão necessários vários lotes para processar todos os termos.
Você pode especificar um tamanho_do_lote para que o servidor saiba quantos termos devem ser processados em cada lote. Por exemplo, um usuário pode solicitar um tamanho de lote maior se o sistema não estiver ocupado e houver muitos recursos de computação disponíveis. Por outro lado, se o sistema já estiver sobrecarregado, um tamanho de lote menor poderá ser usado. É um equilíbrio entre o uso de recursos e a duração da instrução de análise.
A opção update_statistics_timeout
O ATUALIZAR ESTATÍSTICAS/ANALISAR é uma tarefa de manutenção e pode fazer com que o tempo de resposta seja significativamente maior do que o de uma simples consulta, especialmente quando são necessários vários lotes.
Um valor de tempo limite de 60 segundos é aplicado a um comando update/analyze por padrão. É possível especificar um valor opcional (em segundos) para o valor de tempo limite. Observe que o valor de tempo limite se aplica a cada lote quando são necessários vários lotes.
Cláusula DELETE
Os usuários podem descartar as estatísticas atuais do otimizador usando um DELETE com a instrução update/analyze. Essa cláusula desativa seletivamente o CBO para uma consulta ou conjunto de consultas.
Há algumas maneiras de especificar o DELETE especificando uma lista de termos para os quais as estatísticas do otimizador serão excluídas ou solicitando a exclusão de todas as estatísticas do otimizador de um espaço-chave/coleção.
Cláusula DELETE com uma lista de termos
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto DELETE (cidade, país) ANALISAR COLEÇÃO aeroporto DELETE ESTATÍSTICAS(cidade, país) |
A consulta excluirá as estatísticas de distribuição somente para os termos especificados com essa opção. Outras estatísticas do otimizador, como espaço-chave e estatísticas de índice, não serão afetadas. Com essa opção, se uma consulta tiver um predicado no campo "city" ou "country" da coleção "airport", o CBO será desativado para a consulta.
Cláusula DELETE para todas as estatísticas do otimizador de um espaço-chave/coleção
|
1 2 3 |
ATUALIZAÇÃO ESTATÍSTICAS PARA aeroporto DELETE TODOS ANALISAR ESPAÇO-CHAVE aeroporto DELETE ESTATÍSTICAS |
Com essa opção, todas as estatísticas do otimizador para a coleção "airport" serão excluídas, incluindo estatísticas de espaço-chave, estatísticas de índice e estatísticas de distribuição. Qualquer consulta que faça referência à coleção "airport" terá o CBO desativado nesse caso.
Quais campos/expressões devem ser analisados?
Como posso saber quais campos/expressões devo incluir em um ATUALIZAR ESTATÍSTICAS/ANALISAR declaração? Como regra geral, qualquer expressão referenciada em uma consulta (exceto aquelas apenas na cláusula de projeção) requer estatísticas do otimizador.
Cláusulas WHERE, ON, GROUP BY, ORDER BY
Expressões referenciadas no ONDE, EM, GRUPO POR e ORDER BY exigem estatísticas do otimizador. Por exemplo:
|
1 2 3 4 |
SELECIONAR a.nome do aeroporto, r.companhia aérea DE aeroporto a JUNTAR rota r ON a.faa = r.aeroporto de origem ONDE a.cidade = "São Francisco" ORDEM BY a.nome do aeroporto; |
Para essa consulta, as estatísticas do otimizador são necessárias:
|
1 2 |
aeroporto(faa, cidade, nome do aeroporto) rota(aeroporto de origem) |
Se for usada uma expressão mais complexa:
|
1 2 3 |
SELECT * DE aeroporto ONDE inferior(país) = "nós"; |
Para essa consulta, as estatísticas do otimizador são necessárias:
|
1 |
aeroporto(inferior(país)) |
Expressões de índice de matriz
Para QUALQUER ou QUALQUER E TODOS que utilizam índices de matriz, são necessárias estatísticas do otimizador para a expressão de chave de índice de matriz correspondente. Por exemplo:
|
1 2 3 |
SELECT * DE rota ONDE QUALQUER v IN cronograma SATISFAÇÕES v.voo = "FL121" FIM; |
Para essa consulta, as estatísticas do otimizador são necessárias:
|
1 |
rota(DISTINTO ARRAY v.voo PARA v IN cronograma FIM) |
Observe que a expressão usada é a mesma que a expressão da chave do índice da matriz para a definição do índice da matriz correspondente.
Para um INÚTIL operação:
|
1 2 3 |
SELECIONAR r.aeroporto de destino DE rota r INÚTIL r.cronograma AS s ONDE s.voo = "FL121"; |
Para essa consulta, as estatísticas do otimizador são necessárias:
|
1 |
rota(TODOS ARRAY v.voo PARA v IN cronograma FIM) |
Novamente, a expressão usada é a mesma que a expressão da chave de índice de matriz para a definição de índice de matriz correspondente.
Subconsulta e expressão de tabela comum (CTE)
Para uma consulta que envolva uma subconsulta ou um CTE, examine o bloco de consulta da subconsulta ou do CTE para determinar quais expressões exigem estatísticas do otimizador:
|
1 2 3 4 |
COM usairport AS (SELECIONAR nome do aeroporto, faa DE aeroporto ONDE inferior(país) = "nós") SELECT * DE usairport; |
Observando o bloco de consulta que define o CTE, as estatísticas do otimizador são necessárias:
|
1 |
aeroporto(inferior(país)) |
Funções da janela
Se for usada uma função de janela, serão necessárias estatísticas do otimizador nas expressões usadas nas cláusulas PARTITION BY e ORDER BY:
|
1 2 |
SELECIONAR CONTAGEM(1) SOBRE (PARTIÇÃO BY país) DE companhia aérea; |
Para essa consulta, as estatísticas do otimizador são necessárias:
|
1 |
companhia aérea(país) |
Exibir estatísticas do otimizador
Há dois novos espaços de chave do sistema introduzidos para as estatísticas do otimizador: system:dictionary e system:dictionary_cache. O primeiro corresponde aos documentos em disco armazenados em N1QL_SYSTEM_BUCKET.N1QL_SYSTEM_SCOPE.N1QL_CBO_STATS, enquanto o system:dictionary_cache corresponde à representação em cache na memória de um subconjunto das mesmas informações.
O cache do dicionário é inicializado quando um nó de consulta é iniciado, e as informações do cache são preenchidas durante a execução das consultas. Os dados recuperados do sistema:dicionário será o mesmo, independentemente do nó de consulta que obtiver as informações ao usar vários nós de consulta.
No entanto, as informações armazenadas em cache de todos os nós de consulta em um cluster de vários nós estarão em system:dictionary_cache, em que cada nó pode ter um subconjunto diferente de informações armazenadas em cache.
Essa consulta mostra a estatística em system:dictionary:
|
1 2 3 |
SELECT * DE sistema:dicionário ONDE `balde` = "amostra de viagem" E `escopo` = "inventário" E `Espaço-chave` = "aeroporto"; |
Os resultados da consulta são:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
{ "dicionário": { "avgDocKeySize": 12, "avgDocSize": 278, "bucket" (balde): "amostra de viagem", "distributionKeys": [ "país", "lower(country)" ], "docCount": 1968, "indexes" (índices): [ { "indexId": "4978a27d62bc592a", "indexName": "def_inventory_airport_primary", "indexStats": [ { "avgItemSize": 23, "avgPageSize": 7202, "numItems": 1968, "numPages": 6, "resRatio": 1 } ] }, { "indexId": "c5071b3ac2730bff", "indexName": "def_inventory_airport_faa", "indexStats": [ { "avgItemSize": 32, "avgPageSize": 9394, "numItems": 1968, "numPages": 7, "resRatio": 1 } ] }, { "indexId": "e260327a9933350e", "indexName": "def_inventory_airport_city", "indexStats": [ { "avgItemSize": 39, "avgPageSize": 11410, "numItems": 1968, "numPages": 7, "resRatio": 1 } ] } ], "espaço-chave": "aeroporto", "namespace": "default", "escopo": "inventário" } } |
Cada entrada contém informações básicas sobre o espaço-chave, uma matriz de distributionKeys para os quais os histogramas estão disponíveis e uma matriz de informações de índice.
Da mesma forma, ao consultar o system:dictionary_cache:
|
1 2 3 |
SELECT * DE sistema:dicionário_cache ONDE `balde` = "amostra de viagem" E `escopo` = "inventário" E `Espaço-chave` = "aeroporto"; |
O resultado é o mesmo que o sistema:dicionário exceto por um campo de nó extra que mostra entradas de diferentes nós de consulta.
Explicar a saída com optimizer_estimates
Quando as estatísticas apropriadas do otimizador estiverem disponíveis, a saída do EXPLAIN conterá uma nova seção optimizer_estimates para cada operador. Essas seções refletem a estimativa do otimizador para as informações de custo desse operador e de todos os seus filhos. Por exemplo:
|
1 |
EXPLICAR SELECT * DE aeroporto ONDE cidade = "São Francisco"; |
A saída a seguir é para o operador IndexScan3:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "#operator": "IndexScan3", "índice": "airpot_city_country", ...... "optimizer_estimates": { "cardinalidade": 1.1764705882352944, "custo": 12.212415941159103, "fr_cost": 12.180553549985238, "tamanho": 12 }, "vãos": [ ...... ], ...... }, |
Ter o otimizador_estimativas para um operador é uma indicação de que o banco de dados está usando o CBO para esse estágio do processo de planejamento da consulta.
Resumo
Novos recursos inovadores de banco de dados foram disponibilizados no Couchbase 7.0; a adição do Cost-Based Optimizer (CBO) é apenas um dos muitos. As consultas de atualização/análise mostradas nesta postagem do blog são essenciais para coletar as estatísticas necessárias para que o CBO funcione com eficiência.
Quando o banco de dados reúne estatísticas precisas do otimizador, a saída EXPLAIN mostra as estimativas que o CBO usará para escolher um plano de consulta. Isso ajudará a orientá-lo para uma geração de consultas mais eficiente e previsível no futuro.
- Leia mais sobre o CBO na seção Documentos do Couchbase
- Leia mais sobre o Lançamento do Couchbase 7 e seus novos recursos
- Mergulhe no SQL para JSON: Linguagem de consulta SQL++
Isso foi útil, obrigado Bingjie.
Uma consulta: Atualmente, não há nenhuma tarefa pronta para uso, como a disponível no Oracle, para coletar estatísticas. Portanto, isso precisa ser feito manualmente em uma base regular, talvez com tarefas cron. O entendimento está correto?
Sim, seu entendimento está correto. Atualmente, a coleta de estatísticas do otimizador é um processo manual. Estamos considerando aprimoramentos nessa área para facilitar o uso.