SQLite - índices

Os índices são tabelas de pesquisa especiais que o mecanismo de pesquisa do banco de dados pode usar para acelerar a recuperação de dados. Simplificando, umindexé um ponteiro para dados em uma tabela. Um índice em um banco de dados é muito semelhante a um índice no final de um livro.

Por exemplo, se você quiser fazer referência a todas as páginas de um livro que discute um determinado tópico, primeiro consulte o índice, que lista todos os tópicos em ordem alfabética e, em seguida, é referido a um ou mais números de página específicos.

Um índice ajuda a acelerar as consultas SELECT e cláusulas WHERE, mas diminui a entrada de dados, com instruções UPDATE e INSERT. Os índices podem ser criados ou eliminados sem efeito nos dados.

A criação de um índice envolve a instrução CREATE INDEX, que permite nomear o índice, especificar a tabela e qual coluna ou colunas indexar e indicar se o índice está em ordem crescente ou decrescente.

Os índices também podem ser exclusivos, semelhantes à restrição UNIQUE, no sentido de que o índice evita entradas duplicadas na coluna ou combinação de colunas nas quais há um índice.

O comando CREATE INDEX

A seguir está a sintaxe básica de CREATE INDEX.

CREATE INDEX index_name ON table_name;

Índices de coluna única

Um índice de coluna única é aquele criado com base em apenas uma coluna da tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column_name);

Índices Únicos

Índices exclusivos são usados ​​não apenas para desempenho, mas também para integridade de dados. Um índice exclusivo não permite que nenhum valor duplicado seja inserido na tabela. A sintaxe básica é a seguinte -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices Compostos

Um índice composto é um índice em duas ou mais colunas de uma tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
on table_name (column1, column2);

Seja para criar um índice de coluna única ou um índice composto, leve em consideração a (s) coluna (s) que você pode usar com muita frequência na cláusula WHERE de uma consulta como condições de filtro.

Se houver apenas uma coluna usada, um índice de coluna única deve ser a escolha. Se houver duas ou mais colunas que são freqüentemente usadas na cláusula WHERE como filtros, o índice composto seria a melhor escolha.

Índices Implícitos

Índices implícitos são índices criados automaticamente pelo servidor de banco de dados quando um objeto é criado. Os índices são criados automaticamente para restrições de chave primária e restrições exclusivas.

Example

A seguir está um exemplo em que criaremos um índice na tabela COMPANY para a coluna de salários -

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Agora, vamos listar todos os índices disponíveis na tabela COMPANY usando .indices comando da seguinte forma -

sqlite> .indices COMPANY

Isso produzirá o seguinte resultado, onde sqlite_autoindex_COMPANY_1 é um índice implícito que foi criado quando a própria tabela foi criada.

salary_index
sqlite_autoindex_COMPANY_1

Você pode listar todos os índices do banco de dados da seguinte forma -

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

O comando DROP INDEX

Um índice pode ser eliminado usando SQLite DROPcomando. Deve-se ter cuidado ao descartar um índice, pois o desempenho pode ser reduzido ou melhorado.

A seguir, a sintaxe básica é a seguinte -

DROP INDEX index_name;

Você pode usar a seguinte instrução para excluir o índice criado anteriormente.

sqlite> DROP INDEX salary_index;

Quando os índices devem ser evitados?

Embora os índices tenham como objetivo melhorar o desempenho de um banco de dados, há momentos em que eles devem ser evitados. As diretrizes a seguir indicam quando o uso de um índice deve ser reconsiderado.

Os índices não devem ser usados ​​em -

  • Mesas pequenas.
  • Tabelas que possuem operações de inserção ou atualização de lote frequentes e grandes.
  • Colunas que contêm um grande número de valores NULL.
  • Colunas que são frequentemente manipuladas.