SQLite Índice (Index)
Un índice (Index) es una tabla de búsqueda especial que el motor de búsqueda de la base de datos utiliza para acelerar la recuperación de datos. En términos simples, un índice es un puntero a los datos de una tabla. Un índice en una base de datos es muy similar al índice de un libro.
Tomemos como ejemplo las páginas del índice de un diccionario de chino: podemos encontrar rápidamente los caracteres necesarios mediante el índice ordenado por pinyin, trazos, radicales, etc.
Los índices ayudan a acelerar las consultas SELECT y la cláusula WHERE, pero ralentizan la entrada de datos cuando se usan sentencias UPDATE e INSERT. Los índices se pueden crear o eliminar sin afectar los datos.
La sentencia CREATE INDEX se utiliza para crear un índice; permite nombrar el índice, especificar la tabla y una o más columnas a indexar, e indicar si el índice está ordenado de forma ascendente o descendente.
Los índices también pueden ser únicos; de manera similar a la restricción UNIQUE, evitan entradas duplicadas en una columna o en una combinación de columnas.
Comando CREATE INDEX
CREATE INDEXLa sintaxis básica es la siguiente:
CREATE INDEX index_name ON table_name;
Índice de una sola columna
Un índice de una sola columna es un índice creado basándose únicamente en una columna de una tabla. La sintaxis básica es la siguiente:
CREATE INDEX index_name ON table_name (column_name);
Índice único
El uso de índices únicos no solo es por rendimiento, sino también por la integridad de los datos. Un índice único no permite insertar ningún valor duplicado en la tabla. La sintaxis básica es la siguiente:
CREATE UNIQUE INDEX index_name on table_name (column_name);
Índice compuesto
Un índice compuesto es un índice creado sobre dos o más columnas de una tabla. La sintaxis básica es la siguiente:
CREATE INDEX index_name on table_name (column1, column2);
Si crear un índice de una sola columna o un índice compuesto depende de las columnas que se utilizan con mucha frecuencia en la cláusula WHERE como condiciones de filtro de las consultas.
Si solo se utiliza una columna, se debe optar por un índice de una sola columna. Si en la cláusula WHERE de filtrado se utilizan con frecuencia dos o más columnas, se debe optar por un índice compuesto.
Índice implícito
Los índices implícitos son índices creados automáticamente por el servidor de la base de datos al crear objetos. Los índices se crean automáticamente para restricciones de clave primaria y restricciones únicas.
Ejemplo
A continuación se muestra un ejemplo: crearemos un índice en la columna salary de la tabla COMPANY:
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
Ahora, usemos.indiceso.indexesel comando para listar todos los índices disponibles en la tabla COMPANY, como se muestra a continuación:
sqlite> .indices COMPANY
Esto producirá el siguiente resultado, dondesqlite_autoindex_COMPANY_1es el índice implícito creado al crear la tabla.
salary_index sqlite_autoindex_COMPANY_1
Puede listar todos los índices de la base de datos, como se muestra a continuación:
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
Comando DROP INDEX
Un índice puede ser eliminado usando el comandoDROPde SQLite. Al eliminar un índice se debe tener especial cuidado, porque el rendimiento puede disminuir o mejorar.
La sintaxis básica es la siguiente:
DROP INDEX index_name;
Puede utilizar la siguiente sentencia para eliminar el índice creado anteriormente:
sqlite> DROP INDEX salary_index;
¿En qué casos se debe evitar el uso de índices?
Aunque el propósito de los índices es mejorar el rendimiento de la base de datos, hay varios casos en los que se debe evitar su uso. Al usar índices, se deben reconsiderar las siguientes pautas:
Los índices no deben usarse en tablas pequeñas.
Los índices no deben usarse en tablas con operaciones frecuentes de actualizaciones o inserciones masivas.
Los índices no deben usarse en columnas que contengan una gran cantidad de valores NULL.
Los índices no deben usarse en columnas con operaciones frecuentes.