SQLite: índices

Los índices son tablas de búsqueda especiales que el motor de búsqueda de la base de datos puede utilizar para acelerar la recuperación de datos. En pocas palabras, unindexes un puntero a datos en una tabla. Un índice en una base de datos es muy similar a un índice al final de un libro.

Por ejemplo, si desea hacer referencia a todas las páginas de un libro que tratan un tema determinado, primero debe consultar el índice, que enumera todos los temas en orden alfabético y luego se hace referencia a uno o más números de página específicos.

Un índice ayuda a acelerar las consultas SELECT y las cláusulas WHERE, pero ralentiza la entrada de datos, con declaraciones UPDATE e INSERT. Los índices se pueden crear o eliminar sin ningún efecto sobre los datos.

La creación de un índice implica la instrucción CREATE INDEX, que le permite nombrar el índice, especificar la tabla y qué columna o columnas indexar, e indicar si el índice está en orden ascendente o descendente.

Los índices también pueden ser únicos, similar a la restricción ÚNICA, en el sentido de que el índice evita entradas duplicadas en la columna o combinación de columnas en las que hay un índice.

El comando CREATE INDEX

A continuación se muestra la sintaxis básica de CREATE INDEX.

CREATE INDEX index_name ON table_name;

Índices de una sola columna

Un índice de una sola columna es aquel que se crea basándose en una sola columna de la tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
ON table_name (column_name);

Índices únicos

Los índices únicos se utilizan no solo para el rendimiento, sino también para la integridad de los datos. Un índice único no permite que se inserten valores duplicados en la tabla. La sintaxis básica es la siguiente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices compuestos

Un índice compuesto es un índice en dos o más columnas de una tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
on table_name (column1, column2);

Ya sea para crear un índice de una sola columna o un índice compuesto, tenga en cuenta las columnas que puede usar con mucha frecuencia en la cláusula WHERE de una consulta como condiciones de filtro.

Si solo se usa una columna, la opción debe ser un índice de una sola columna. Si hay dos o más columnas que se utilizan con frecuencia en la cláusula WHERE como filtros, el índice compuesto sería la mejor opción.

Índices implícitos

Los índices implícitos son índices que el servidor de bases de datos crea automáticamente cuando se crea un objeto. Los índices se crean automáticamente para restricciones de clave primaria y restricciones únicas.

Example

A continuación se muestra un ejemplo en el que crearemos un índice en la tabla EMPRESA para la columna de salario:

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Ahora, enumeremos todos los índices disponibles en la tabla EMPRESA usando .indices comando de la siguiente manera:

sqlite> .indices COMPANY

Esto producirá el siguiente resultado, donde sqlite_autoindex_COMPANY_1 es un índice implícito que se creó cuando se creó la tabla.

salary_index
sqlite_autoindex_COMPANY_1

Puede enumerar toda la base de datos de índices de la siguiente manera:

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

El comando DROP INDEX

Se puede eliminar un índice usando SQLite DROPmando. Se debe tener cuidado al eliminar un índice porque el rendimiento se puede ralentizar o mejorar.

A continuación, la sintaxis básica es la siguiente:

DROP INDEX index_name;

Puede utilizar la siguiente declaración para eliminar el índice creado anteriormente.

sqlite> DROP INDEX salary_index;

¿Cuándo se deben evitar los índices?

Aunque los índices están destinados a mejorar el rendimiento de una base de datos, hay ocasiones en las que deben evitarse. Las siguientes pautas indican cuándo debe reconsiderarse el uso de un índice.

Los índices no deben usarse en:

• Mesas pequeñas.
• Tablas que tienen operaciones de inserción o actualización por lotes grandes y frecuentes.
• Columnas que contienen una gran cantidad de valores NULL.
• Columnas que se manipulan con frecuencia.