Índice invisible

Un índice invisible es un índice que existe en la tabla pero que el optimizador de consultas ignora. MySQL 8.0 introdujo esta funcionalidad para permitir probar el impacto de eliminar un índice antes de borrarlo definitivamente. Es como un "soft delete" de índices: si el rendimiento empeora, puedes hacerlo visible de nuevo instantáneamente.

Sintaxis

-- Crear un índice invisible
CREATE INDEX nombre_indice ON tabla (columna) INVISIBLE;
 
-- Hacer invisible un índice existente
ALTER TABLE tabla ALTER INDEX nombre_indice INVISIBLE;
 
-- Hacer visible un índice invisible
ALTER TABLE tabla ALTER INDEX nombre_indice VISIBLE;

Ejemplo básico

-- Crear un índice en productos
CREATE INDEX idx_precio ON productos (precio);
 
-- Verificar que el optimizador lo usa
EXPLAIN SELECT * FROM productos WHERE precio > 500;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	range	idx_precio	idx_precio	8	Using index condition

-- Hacer el índice invisible
ALTER TABLE productos ALTER INDEX idx_precio INVISIBLE;
 
-- El optimizador ya no lo ve
EXPLAIN SELECT * FROM productos WHERE precio > 500;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	productos	ALL	NULL	NULL	30	Using where

El optimizador hace un full table scan porque no "ve" el índice. El índice sigue existiendo y se actualiza con cada INSERT/UPDATE/DELETE, pero no se usa para consultas.

-- Si el rendimiento empeoró, hacerlo visible de nuevo
ALTER TABLE productos ALTER INDEX idx_precio VISIBLE;

Ver índices invisibles

-- SHOW INDEXES muestra la visibilidad
SHOW INDEXES FROM productos WHERE Key_name = 'idx_precio';

Table	Non_unique	Key_name	Column_name	Visible	Index_type
productos	1	idx_precio	precio	YES	BTREE

La columna Visible indica si el índice es visible (YES) o invisible (NO).

-- Consultar todos los índices invisibles
SELECT TABLE_NAME, INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'tienda_mysql'
  AND IS_VISIBLE = 'NO';

Caso de uso: evaluar si un índice es necesario

Cuando sospechas que un índice no se usa y quieres eliminarlo, los índices invisibles permiten probar sin riesgo:

-- Paso 1: Hacer invisible el índice sospechoso
ALTER TABLE pedidos ALTER INDEX cliente_id INVISIBLE;
 
-- Paso 2: Ejecutar las consultas habituales y medir rendimiento
EXPLAIN SELECT p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE p.estado = 'pendiente';
 
-- Paso 3: Si todo funciona bien, borrar el índice
-- DROP INDEX cliente_id ON pedidos;
 
-- Paso 3 alternativo: Si el rendimiento empeoró, restaurar
ALTER TABLE pedidos ALTER INDEX cliente_id VISIBLE;

Caso de uso: crear índice sin afectar consultas

Puedes crear un índice invisible para que se construya y se mantenga actualizado sin afectar el plan de consultas actual. Cuando estés listo, lo haces visible:

-- Crear índice invisible (se construye pero no se usa)
CREATE INDEX idx_nuevo ON productos (stock, precio) INVISIBLE;
 
-- Verificar que no afecta las consultas existentes
-- ... ejecutar pruebas ...
 
-- Hacerlo visible cuando estés listo
ALTER TABLE productos ALTER INDEX idx_nuevo VISIBLE;

Forzar el uso de un índice invisible

La variable optimizer_switch tiene una opción para permitir el uso de índices invisibles en la sesión actual:

-- Permitir que el optimizador use índices invisibles (solo para testing)
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
 
-- Ahora el optimizador puede usar índices invisibles
EXPLAIN SELECT * FROM productos WHERE precio > 500;
 
-- Restaurar comportamiento normal
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Esto es útil para testing, pero no debe usarse en producción.

Restricciones

La clave primaria (PRIMARY KEY) no puede hacerse invisible
El índice invisible sigue ocupando espacio y ralentiza las escrituras
Se actualiza con cada INSERT/UPDATE/DELETE, igual que un índice visible

Limpieza

DROP INDEX idx_precio ON productos;
DROP INDEX idx_nuevo ON productos;

En el siguiente artículo veremos los índices clustered, que determinan cómo InnoDB almacena físicamente los datos en disco.

Escrito por Eduardo Lázaro