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 PLAN
instrução, descobrirá que o planejador de consultas SQLite teve que verificar toda a customers
tabela 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 INDEX
instrução, aparece igual à cláusula WHERE ou ORDER BY .
Por exemplo, no banco de dados exemplo , temos a invoice_items
tabela.
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 WHERE
clá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 INDEX
instrução.
- 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.
- A expressão só pode usar a chamada de função determinística .
- 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.