Índice baseado em expressão SQLite

Resumo : neste tutorial, você aprenderá como usar o índice baseado em expressão SQLite para consultar dados para melhorar o desempenho da consulta, especialmente para as consultas que usam expressão ou função.

Introdução ao índice baseado em expressão SQLite

Ao criar um índice , você geralmente usa uma ou mais colunas em uma tabela. Além dos índices normais, o SQLite permite formar um índice baseado nas expressões envolvidas nas colunas da tabela. Esse tipo de índice é chamado de índice baseado em expressão.

A consulta a seguir seleciona os clientes cujo comprimento da empresa é superior a 10 caracteres.

SELECT customerid,
       company
  FROM customers
 WHERE length(company) > 10
 ORDER BY length(company) DESC;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Se você usar a EXPLAIN QUERY PLANinstrução, descobrirá que o planejador de consultas SQLite teve que verificar toda a customerstabela para retornar o conjunto de resultados.

EXPLAIN QUERY PLAN
SELECT customerid,
       company
  FROM customers
 WHERE length(company) > 10
 ORDER BY length(company) DESC;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O planejador de consultas SQLite é um componente de software que determina o melhor algoritmo ou plano de consulta para executar uma instrução SQL. A partir da versão 3.8.0 do SQLite, o componente planejador de consultas foi reescrito para ser executado mais rapidamente e gerar melhores planos de consulta. A reescrita é conhecida como planejador de consultas de próxima geração ou NGQP.

Para criar um índice baseado na expressão LENGTH(company), você usa a seguinte instrução.

CREATE INDEX customers_length_company 
ON customers(LENGTH(company));Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Agora se você executar a consulta acima novamente, o SQLite usará o índice de expressão para pesquisar e selecionar os dados, o que é mais rápido.

Como funciona o índice baseado em expressão SQLite

O planejador de consulta SQLite usa o índice baseado em expressão somente quando a expressão, que você especificou na CREATE INDEXinstrução, aparece igual à cláusula WHERE ou ORDER BY .

Por exemplo, no banco de dados exemplo , temos a invoice_itemstabela.

A instrução a seguir cria um índice usando as colunas de preço unitário e quantidade.

CREATE INDEX invoice_line_amount 
ON invoice_items(unitprice*quantity);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

No entanto, quando você executa a seguinte consulta:

EXPLAIN QUERY PLAN 
SELECT invoicelineid,
       invoiceid, 
       unitprice*quantity
FROM invoice_items
WHERE quantity*unitprice > 10;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O planejador de consultas SQLite não usou o índice porque a expressão em CREATE INDEX( unitprice*quantity) não é a mesma que na WHEREcláusula (quantidade*preço unitário)

Restrição de índice baseada em expressão SQLite

A seguir listamos todas as restrições à expressão que aparece na CREATE INDEXinstrução.

1. A expressão deve referir-se apenas às colunas da tabela que está sendo indexada. Não pode referir-se às colunas de outras tabelas.
2. A expressão só pode usar a chamada de função determinística .
3. A expressão não pode usar uma subconsulta.

Neste tutorial, você aprendeu como usar o índice baseado em expressão SQLite para melhorar o desempenho da consulta.