Índice baseado em expressão de SQLite

Nos bancos de dados SQLITE ou SQL, os índices são usados ​​para referenciar algumas colunas de uma tabela. Esses índices podem ser compostos de uma ou mais colunas e podem ser únicos. Junto com isso, podemos criar índices em diferentes expressões contendo diferentes colunas de qualquer tabela. Essas expressões podem ser definidas na cláusula de condição onde a instrução selecionada. Assim, decidimos explicar o uso de expressões para criar índices e como eles afetam os resultados.

Vamos começar com este artigo abrindo o terminal da Shell do Ubuntu 20.04 rapidamente. Use a chave de atalho "Ctrl+Alt+T" para fazer isso. Após o lançamento bem -sucedido do terminal do shell, você precisa atualizar seu sistema Linux pelo uso dos comandos de atualização e atualização com a ajuda de um pacote APT. Depois disso, abra o banco de dados SQLite usando a instrução "SQLITE3". No momento, não temos mesas no banco de dados até agora, eu.e. usando o .comando tabelas.

Para criar os índices sobre expressões, precisamos de uma tabela em nosso banco de dados. Criamos uma nova tabela chamada "Detalhe" em nosso banco de dados contendo um total de 3 colunas do tipo inteiro, i i.e. ID, varejo e venda. A mesa está vazia agora, eu.e. De acordo com o comando select.

Criar detalhes da tabela (Id int Primary Key Not NULL, Retail Int, Sale Int);
Selecione * do detalhe;

Temos que adicionar alguns registros na tabela de detalhes para que possamos criar os índices nas colunas depois disso. Assim, utilizamos a instrução de inserção com a palavra -chave, seguida pelo nome da tabela "Detalhe" e seus nomes de colunas "ID, varejo, venda". Os nomes das colunas são seguidos pelos valores das palavras -chave e os registros a serem inseridos nas colunas. Inserimos um total de 10 registros dentro de três colunas desta tabela; Cada um é único como mostrado:

Inserir em detalhes (ID, varejo, venda) valores (1, 340, 400);
Inserir em detalhes (ID, varejo, venda) valores (2, 380, 420);
Inserir em detalhes (ID, varejo, venda) valores (3, 205, 230);
Inserir em detalhes (ID, varejo, venda) valores (4, 98, 110);
Inserir em detalhes (ID, varejo, venda) valores (5, 311, 340);
Insira em detalhes (ID, varejo, venda) valores (6, 100, 120);
Inserir em detalhes (ID, varejo, venda) valores (7, 512, 540);
Inserir em detalhes (ID, varejo, venda) valores (8, 634, 670);
Inserir em detalhes (ID, varejo, venda) valores (9, 54, 67);
Inserir em detalhes (ID, varejo, venda) valores (10, 934, 990);

Depois de usar a instrução Selecionar com o "*" para exibir todos os registros da coluna da tabela de detalhes, mostramos os 10 registros recém -inseridos na tabela.

Selecione * do detalhe;

Antes de criar um índice nas expressões, analisamos a consulta simples para buscar os registros com o uso de expressões. Além disso, utilizamos o pedido por opção dentro da consulta para organizar os resultados de acordo com a ordem decrescente de um comprimento de um varejo de coluna. Um total de 8 registros são exibidos em nossa tela até agora.

Selecione * FROM Detalhes onde comprimento (varejo)> 2 Ordem por comprimento (varejo) DESC;

Utilizamos a mesma consulta dentro da instrução SQLITE Consulta Planner para ver como o SQLite funciona para executar esta consulta. Descobrimos que ele precisa escanear toda a tabela "detalhe" usando a árvore B temporária (árvore equilibrada) na cláusula de ordem por ordem.

Explique o plano de consulta Selecione * From Detalhe onde comprimento (varejo)> 2 Ordem por comprimento (varejo) Desc;

Vamos criar um índice na mesma expressão "Comprimento" na coluna de varejo de um detalhe da tabela. Utilizamos a instrução Create Index para criar o índice "index_retail".

Criar index index_retail no detalhe (comprimento (varejo));

Depois de criar o índice na tabela de detalhes, usamos o planejador de consulta para ver a estrutura de trabalho da consulta. Isso mostra que a consulta está trabalhando no índice baseado em expressão para buscar os resultados necessários, em vez de pesquisar a tabela inteira.

Explique o plano de consulta Selecione * From Detalhe onde comprimento (varejo)> 2 Ordem por comprimento (varejo) Desc;

Depois de usar a instrução Selecionar sobre a mesma expressão para buscar os registros, os dados agora são exibidos na ordem decrescente da duração da coluna de varejo.

Vamos criar outra consulta selecionada com uma expressão. Desta vez, a expressão de classe Where exibirá apenas os registros em que a soma da coluna de varejo e venda é maior que 700.

Selecione * FROM Detalhes onde varejo+venda> 700;

Utilizamos a instrução Select anterior, juntamente com as colunas únicas e a expressão “varejo+venda> 700” que ele contém, na instrução de planejador de consulta. O resultado do planejador de consultas mostra que o banco de dados SQLite deve pesquisar em toda a tabela de detalhes para buscar os resultados necessários com essa expressão bastante ineficiente.

Explique o plano de consulta Selecione ID, varejo, venda, varejo+venda de detalhes onde varejo+venda> 700;

Vamos criar um novo índice na expressão "varejo+venda" que é usada na instrução Select anterior. Então, utilizamos a instrução Create Index para gerar um novo índice "index_detail" na expressão "varejo+venda" de uma tabela de detalhes. O índice recém -fabricado "index_detail" pode ser visto na lista de índices de acordo com ".Detalhes dos índices ”Instrução.

Criar índice index_detail no detalhe (varejo+venda);

Agora, vamos experimentar a instrução do planejador de consulta para a mesma instrução selecionada que contém a venda+venda> 700 expressões dentro dela que são usadas na cláusula onde a instrução selecionada. Depois de usar o planejador de consulta, descobrimos que o plano SQLite pesquisará apenas as entradas específicas de um detalhe da tabela conforme o índice "index_detail" contendo uma expressão.

Explique o plano de consulta Selecione ID, varejo, venda, varejo+venda de detalhes onde varejo+venda> 700;

Vamos modificar a expressão trocando os nomes das colunas entre si sem mudar seu conhecimento. Sempre que você usa a mesma instrução selecionada com a pouca mudança em sua expressão, eu.e. “Venda+varejo> 700”, exibe os mesmos 5 resultados, pois a lógica está correta e o mesmo é usado na instrução Anterior de Selecionar.

Selecione * FROM Detalhes onde a venda+varejo> 700;

Depois de usar a instrução Select anteriormente modificada dentro da instrução do planejador de consultas, descobrimos que o banco de dados SQLite precisa pesquisar a tabela inteira “detalhes” para esta consulta baseada em expressão. Isso ocorre porque não criamos um índice para a expressão recém -modificada "Sale+Retail> 700" na consulta selecionada.

Explique o plano de consulta Selecione ID, varejo, venda, venda+varejo a partir de detalhes onde venda+varejo> 700;

Conclusão

Este artigo trata de criar um índice em diferentes expressões. Tentamos usar a instrução de planejador de consulta para explicar a diferença entre usar uma expressão simples em uma instrução SELECT sem qualquer índice e o uso de uma expressão em uma instrução selecionada com base em um índice.