Concepto de Índices

Un índice es una estructura de datos que permite a MySQL acceder a información de manera eficiente. Consiste en algoritmos especializados que referencian registros, facilitando operaciones complejas de búsqueda.

Ventajas y Desventajas

Beneficios:

• Reduce operaciones de E/S durante consultas
• Optimiza ordenamiento de datos disminuyendo consumo de CPU

Limitaciones:

• Ocupa espacio adicional en almacenamiento
• Ralentiza operaciones de escritura (INSERT/UPDATE/DELETE)

Estructuras de Índices

Árbol B+

Estructura predominante con estas características:

• Datos almacenados exclusivamente en nodos hoja
• Nodos internos funcionan como guías de búsqueda
• Nodos hoja enlazados para recorridos secuenciales

MySQL optimiza esta estructura con punteros bidireccionales para acelerar consultas por rangos.

Índice Hash

Implementado mediante tablas hash:

• Solo válido para búsquedas exactas (=, IN)
• Incapaz de manejar operaciones de rango u ordenamiento
• Resuelve colisiones mediante listas enlazadas

Soportado principalmente por el motor Memory. InnoDB lo genera automáticamente bajo ciertas condiciones.

Otros Tipos

• R-tree: Para datos geoespaciales (MyISAM)
• Texto Completo: Búsqueda en contenido textual mediante índice invertido

Clasificación de Índices

Índice Agrupado (Clúster)

Almacena datos físicos junto al índice. Reglas de selección:

1. Prioriza clave primaria
2. Usa primer índice UNIQUE disponible
3. Genera rowid oculto si no hay alternativas

Índices Secundarios

Referencian claves primarias en nodos hoja. Requieren búsquedas secundarias para obtener datos completos.

Sintaxis Básica

-- Creación
CREATE [UNIQUE] INDEX nombre_indice ON tabla (columna);

-- Consulta
SHOW INDEX FROM tabla;

-- Eliminación
DROP INDEX nombre_indice ON tabla;

Análisis de Rendimiento

Frecuencia de Consultas

SHOW GLOBAL STATUS LIKE 'Com_______';

Registro de Consultas Lentas

# /etc/my.cnf
slow_query_log = 1
long_query_time = 2

Perfilado de Consultas

SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE CPU FOR QUERY 5;

Plan de Ejecución

EXPLAIN SELECT columna FROM tabla WHERE condición;

Campos clave: type (calidad de acceso), key (índice usado), rows (filas estimadas).

Uso Avanzado

Reglas de Índices Compuestos

• Requieren uso secuencial desde primera columna
• Operadores de rango (>, <) invalidan columnas posteriores

Causas de Ineficiencia

• Operaciones en columnas indexadas
• Cadenas sin comillas en comparaciones
• Comodines iniciales en búsquedas
• Condiciones OR con columnas no indexadas

Índices de Prefijo

CREATE INDEX idx_prefijo ON tabla (columna(5));

-- Cálculo de selectividad
SELECT 
  COUNT(DISTINCT LEFT(columna, 5)) / COUNT(*) 
FROM tabla;

Cobertura de Índices

Buscar incluir todas las columnas requeridas en el índice para evitar acceso adicional a tablas.

Principios de Diseño

1. Priorizar tablas con alto volumen y frecuencia de consultas
2. Indexar columnas en cláusulas WHERE, ORDER BY, GROUP BY
3. Preferir alta selectividad (valores únicos)
4. Usar prefijos para cadenas largas
5. Optar por índices compuestos sobre simples
6. Limitar cantidad total de índices
7. Declarar columnas NOT NULL cuando sea posible