Optimización de Bases de Datos: Uso Efectivo de Índices

Un índice de base de datos es una estructura de datos que almacena un valor y su posición concreta en la tabla, creada para mejorar la velocidad de las operaciones sobre los datos de una tabla. Se suelen crear con campos sobre los que se realizan búsquedas frecuentemente.

Habitualmente, las primary key suelen ser índices de las tablas. Aunque una tabla tenga más de un índice, MySQL solo utilizará un índice por tabla y consulta.

Consideraciones según el motor de almacenamiento

En las tablas MyISAM, los índices se almacenan en archivos separados, lo cual implica dos accesos distintos a disco: uno para buscar el índice y otro para leer el registro.

En las tablas InnoDB, se utilizan los índices cluster, donde se guardan las claves primarias y los registros ordenados según dichas claves primarias. Estas se crean automáticamente al crear la clave primaria.

¿Cómo Crear un Índice?

Para crear un índice en la tabla movimientos sobre el campo codigo_cuenta, se puede usar:

ALTER TABLE movimientos ADD INDEX (codigo_cuenta); (para un solo campo)

O

CREATE INDEX ind_codigo_cuenta ON movimientos(codigo_cuenta); (para un solo campo)

Para crear un índice parcial, es decir, de solo parte del campo pcontenido:

ALTER TABLE posts ADD INDEX (pcontenido(120)); (para un trozo de campo de texto)

Estructuras de los Índices

• B-tree: Estructura en árbol formada por nodos que contienen valores de índice. Los nodos están ordenados entre sí, de forma que se van recorriendo en función de estos valores, optando en cada caso por una de las ramas. Se recomiendan para uso en consultas que incluyen rangos de datos, es decir, consultas con BETWEEN o con comparadores. Es el más utilizado.
• Hash: Se aplica una función hash que hace corresponder a los valores de las claves con un número único.
• R-tree: Se utilizan cuando se manejan datos n-dimensionales (coordenadas en el espacio de un objeto: x, y, z).

Tipos de Índice

Al crear un índice, se debe elegir el tipo:

• UNIQUE: Campos cuyo valor no se repite en la tabla.
• PRIMARY: Sus campos forman parte de la clave primaria. Mejor que sean numéricos.
• INDEX: Índice normal.
• FULLTEXT: Formados por uno o más campos de texto para buscar palabras dentro. Funcionan solo en tablas tipo MyISAM.

Según el Motor de Almacenamiento

• En MyISAM, se almacenan en los ficheros .MYI. Utilizan índices B-Tree y Fulltext.
• En InnoDB, se utilizan tablespaces para almacenar los datos y los índices. Estas estructuras son lógicas y están definidas dentro de uno o más ficheros.

Para Crear, Ver y Borrar Índices

CREATE UNIQUE | FULLTEXT | SPATIAL INDEX mi_indice_uno ON mi_tabla (colum1, colum2, ...) USING BTREE | HASH;

Para borrar un índice:

DROP INDEX mi_indice_uno ON mi_tabla;

Para ver los índices de una tabla:

SHOW INDEX FROM mi_tabla;

SHOW INDEXES FROM mi_basedatos;