Índice SQLite

Resumo : neste tutorial, você aprenderá como usar índices SQLite para consultar dados com mais rapidez, acelerar operações de classificação e impor restrições exclusivas.

O que é um índice?

Em bancos de dados relacionais, uma tabela é uma lista de linhas. Ao mesmo tempo, cada linha possui a mesma estrutura de colunas que consiste em células. Cada linha também possui um número de sequência rowid consecutivo usado para identificar a linha. Portanto, você pode considerar uma tabela como uma lista de pares: (rowid, row).

Ao contrário de uma tabela, um índice possui um relacionamento oposto: (linha, rowid). Um índice é uma estrutura de dados adicional que ajuda a melhorar o desempenho de uma consulta.

SQLite usa árvore B para organizar índices. Observe que B significa balanceado, árvore B é uma árvore balanceada, não uma árvore binária.

A árvore B mantém equilibrada a quantidade de dados em ambos os lados da árvore, de modo que o número de níveis que devem ser percorridos para localizar uma linha esteja sempre no mesmo número aproximado. Além disso, consultar usando igualdade (=) e intervalos (>, >=, <,<=) nos índices da árvore B é muito eficiente.

Como funciona um índice

Cada índice deve estar associado a uma tabela específica. Um índice consiste em uma ou mais colunas, mas todas as colunas de um índice devem estar na mesma tabela. Uma tabela pode ter vários índices.

Sempre que você cria um índice, o SQLite cria uma estrutura de árvore B para armazenar os dados do índice.

O índice contém dados das colunas especificadas no índice e o rowidvalor correspondente. Isso ajuda o SQLite a localizar rapidamente a linha com base nos valores das colunas indexadas.

Imagine um índice no banco de dados como o índice de um livro. Observando o índice, você pode identificar rapidamente os números das páginas com base nas palavras-chave.

CREATE INDEXInstrução SQLite

Para criar um índice, você usa a CREATE INDEXinstrução com a seguinte sintaxe:

CREATE [UNIQUE] INDEX index_name 
ON table_name(column_list);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para criar um índice, você especifica as seguintes informações:

• O nome do índice após as CREATE INDEXpalavras-chave.
• O nome da tabela pertence ao índice.
• Uma lista de colunas do índice.

Caso queira ter certeza de que os valores em uma ou mais colunas são únicos, como email e telefone, use a UNIQUEopção no CREATE INDEXextrato. Isso CREATE UNIQUE INDEXcria um novo índice exclusivo.

UNIQUEExemplo de índice SQLite

Vamos criar uma nova tabela nomeada contactspara demonstração.

CREATE TABLE contacts (
	first_name text NOT NULL,
	last_name text NOT NULL,
	email text NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Suponha que você queira garantir que o e-mail seja exclusivo e crie um índice exclusivo da seguinte maneira:

CREATE UNIQUE INDEX idx_contacts_email 
ON contacts (email);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Para testar isso.

Primeiro,  insira uma linha na tabela contacts.

INSERT INTO contacts (first_name, last_name, email)
VALUES('John','Doe','[email protected]');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Segundo, insira outra linha com um e-mail duplicado.

INSERT INTO contacts (first_name, last_name, email)
VALUES('Johny','Doe','[email protected]');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

SQLite emitiu uma mensagem de erro indicando que o índice exclusivo foi violado. Porque quando você inseriu a segunda linha, o SQLite verificou e certificou-se de que o e-mail era exclusivo nas linhas da emailtabela contacts.

Vamos inserir mais duas linhas na contactstabela.

INSERT INTO contacts (first_name, last_name, email)
VALUES('David','Brown','[email protected]'),
      ('Lisa','Smith','[email protected]');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Se você consultar os dados da contactstabela com base em um email específico, o SQLite usará o índice para localizar os dados. Veja a seguinte declaração:

SELECT
	first_name,
	last_name,
	email
FROM
	contacts
WHERE
	email = '[email protected]';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Para verificar se o SQLite usa o índice ou não, você usa a EXPLAIN QUERY PLANseguinte instrução:

EXPLAIN QUERY PLAN 
SELECT
	first_name,
	last_name,
	email
FROM
	contacts
WHERE
	email = '[email protected]';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Exemplo de índice multicoluna SQLite

Se você criar um índice que consiste em uma coluna, o SQLite usará essa coluna como chave de classificação. Caso você crie um índice que possua múltiplas colunas, o SQLite usa as colunas adicionais como segunda, terceira,… como chaves de classificação.

SQLite classifica os dados no índice multicolunas pela primeira coluna especificada na CREATE INDEXinstrução. Em seguida, classifica os valores duplicados pela segunda coluna e assim por diante.

Portanto, a ordem das colunas é muito importante ao criar um índice com várias colunas.

Para utilizar um índice de múltiplas colunas, a consulta deve conter a condição que possui a mesma ordem de coluna definida no índice.

A instrução a seguir cria um índice de múltiplas colunas nas colunas first_namee last_nameda contactstabela:

CREATE INDEX idx_contacts_name 
ON contacts (first_name, last_name);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

Se você consultar a contactstabela com uma das seguintes condições na WHERE cláusula, o SQLite utilizará o índice de múltiplas colunas para pesquisar dados.

1) filtrar os dados pela first_namecoluna.

WHERE
	first_name = 'John';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) filtrar dados por colunas first_namee last_name:

WHERE
	first_name = 'John' AND last_name = 'Doe';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

No entanto, o SQLite não usará o índice multicolunas se você usar uma das seguintes condições.

1) filtrar last_nameapenas pela coluna.

WHERE
	last_name = 'Doe';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) filtrar por colunas first_nameOR .last_name

last_name = 'Doe' OR first_name = 'John';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Índices de exibição SQLite

Para encontrar todos os índices associados a uma tabela, use o seguinte comando:

PRAGMA index_list('table_name');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por exemplo, esta instrução mostra todos os índices da contactstabela:

PRAGMA index_list('contacts');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Para obter as informações sobre as colunas em um índice, use o seguinte comando:

PRAGMA index_info('idx_contacts_name');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Este exemplo retorna a lista de colunas do índice idx_contacts_name:

Outra maneira de obter todos os índices de um banco de dados é consultar a sqlite_mastertabela:

SELECT
   type, 
   name, 
   tbl_name, 
   sql
FROM
   sqlite_master
WHERE
   type= 'index';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

DROP INDEXInstrução SQLite

Para remover um índice de um banco de dados, você usa a DROP INDEXseguinte instrução:

DROP INDEX [IF EXISTS] index_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, você especifica o nome do índice que deseja eliminar após as DROP INDEXpalavras-chave. A IF EXISTSopção remove um índice somente se ele existir.

Por exemplo, você usa a seguinte instrução para remover o idx_contacts_nameíndice:

DROP INDEX idx_contacts_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Tente

O idx_contacts_nameíndice é removido completamente do banco de dados.

Neste tutorial, você aprendeu sobre o índice SQLite e como utilizar índices para melhorar o desempenho de consultas ou impor restrições exclusivas.