JSON Index

MySQL no permite crear índices directamente sobre una columna de tipo JSON. Cuando haces un WHERE que filtra por un valor dentro de un documento JSON, MySQL tiene que examinar todos los documentos de la tabla fila por fila para encontrar las coincidencias, lo que resulta muy lento en tablas grandes. Para resolver esto, MySQL ofrece dos mecanismos: columnas generadas con índices tradicionales y los índices multi-valor introducidos en MySQL 8.0.17. Ambos permiten que las consultas sobre datos JSON utilicen índices y se ejecuten en tiempo logarítmico en lugar de lineal.

Sintaxis

La estrategia general es crear una columna generada que extraiga un valor escalar del JSON y luego crear un índice sobre esa columna:

-- Columna generada virtual con índice
ALTER TABLE tabla
ADD COLUMN nombre_columna TIPO_DATO
    GENERATED ALWAYS AS (columna_json->>'$.ruta') VIRTUAL;
 
CREATE INDEX idx_nombre ON tabla(nombre_columna);
 
-- Índice multi-valor para arrays
CREATE INDEX idx_array ON tabla((CAST(columna_json->>'$.ruta_array' AS CHAR(50) ARRAY)));

Comportamiento básico

Supongamos una tabla de productos con atributos almacenados en JSON:

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    atributos JSON
);
 
INSERT INTO productos (nombre, precio, atributos) VALUES
('MacBook Air M3', 1399.00, '{"marca": "Apple", "categoria": "portatiles", "color": "Plata", "ram": 16}'),
('ThinkPad X1 Carbon', 1549.00, '{"marca": "Lenovo", "categoria": "portatiles", "color": "Negro", "ram": 32}'),
('Galaxy S24', 899.99, '{"marca": "Samsung", "categoria": "moviles", "color": "Violeta", "pantalla": 6.2}'),
('iPhone 15 Pro', 1299.99, '{"marca": "Apple", "categoria": "moviles", "color": "Titanio", "pantalla": 6.1}'),
('Pixel 8', 699.00, '{"marca": "Google", "categoria": "moviles", "color": "Negro", "pantalla": 6.2}');

Sin un índice, una consulta que filtre por marca dentro del JSON necesita un full table scan:

-- Esta consulta recorre TODAS las filas
SELECT nombre, precio
FROM productos
WHERE atributos->>'$.marca' = 'Apple';

Ahora añadimos una columna generada virtual que extrae la marca del JSON y la indexamos:

ALTER TABLE productos
ADD COLUMN marca VARCHAR(50)
    GENERATED ALWAYS AS (atributos->>'$.marca') VIRTUAL;
 
CREATE INDEX idx_marca ON productos(marca);

La columna marca no ocupa espacio en disco porque es VIRTUAL: su valor se calcula sobre la marcha cuando se necesita. Sin embargo, el índice idx_marca sí se almacena en disco y permite búsquedas rápidas:

SELECT nombre, precio, marca
FROM productos
WHERE marca = 'Apple';

Para verificar que MySQL usa el índice, examina el plan de ejecución con EXPLAIN:

EXPLAIN SELECT nombre, precio FROM productos WHERE marca = 'Apple';

El valor ref en la columna type y el uso de idx_marca confirman que la consulta utiliza el índice en lugar de un escaneo completo de tabla.

Columnas generadas almacenadas vs virtuales

Existen dos tipos de columnas generadas. Las columnas VIRTUAL no ocupan espacio en disco y se calculan cada vez que se leen. Las columnas STORED se escriben en disco cuando se inserta o actualiza la fila. Ambas admiten índices, pero hay diferencias prácticas:

-- Virtual: no ocupa espacio, se calcula al leer
ALTER TABLE productos
ADD COLUMN categoria VARCHAR(50)
    GENERATED ALWAYS AS (atributos->>'$.categoria') VIRTUAL;
 
-- Stored: ocupa espacio, se calcula al escribir
ALTER TABLE productos
ADD COLUMN color VARCHAR(50)
    GENERATED ALWAYS AS (atributos->>'$.color') STORED;

Usa VIRTUAL cuando solo necesitas el índice para búsquedas. Usa STORED cuando además necesitas incluir esa columna frecuentemente en el SELECT sin recalcularla cada vez, o cuando la expresión de cálculo es costosa.

Caso práctico: índice compuesto sobre JSON

Puedes crear índices compuestos que combinen columnas generadas del JSON con columnas regulares:

CREATE INDEX idx_categoria_precio ON productos(categoria, precio);
 
SELECT nombre, precio
FROM productos
WHERE categoria = 'moviles'
  AND precio < 1000
ORDER BY precio;

Este índice compuesto permite filtrar eficientemente por categoría (extraída del JSON) y precio (columna regular) en una sola búsqueda de índice.

Caso práctico: índices multi-valor para arrays

A partir de MySQL 8.0.17, puedes crear índices multi-valor que indexan todos los elementos de un array JSON. Esto permite buscar eficientemente si un array contiene un valor determinado. Supongamos una tabla donde cada producto tiene etiquetas almacenadas como un array JSON:

CREATE TABLE articulos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    metadata JSON,
    INDEX idx_etiquetas ((CAST(metadata->>'$.etiquetas' AS CHAR(50) ARRAY)))
);
 
INSERT INTO articulos (titulo, metadata) VALUES
('Guía de MySQL 8', '{"etiquetas": ["mysql", "bases-de-datos", "tutorial"], "autor": "Carlos"}'),
('Node.js y MySQL', '{"etiquetas": ["mysql", "nodejs", "backend"], "autor": "Laura"}'),
('React desde cero', '{"etiquetas": ["react", "frontend", "javascript"], "autor": "Miguel"}'),
('API REST con Express', '{"etiquetas": ["nodejs", "backend", "api"], "autor": "Laura"}');

La sintaxis CAST(... AS CHAR(50) ARRAY) le indica a MySQL que el valor es un array y que debe indexar cada elemento individualmente. Ahora puedes buscar artículos que contengan una etiqueta específica:

SELECT titulo
FROM articulos
WHERE 'mysql' MEMBER OF (metadata->>'$.etiquetas');

La expresión MEMBER OF verifica si un valor existe dentro del array, y el índice multi-valor permite que esta búsqueda sea eficiente incluso con millones de filas.

Caso práctico: e-commerce con filtros dinámicos

En un sistema de e-commerce, los filtros de búsqueda varían según la categoría. Puedes combinar columnas generadas indexadas para los filtros más comunes:

CREATE TABLE catalogo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    atributos JSON,
    marca VARCHAR(100) GENERATED ALWAYS AS (atributos->>'$.marca') STORED,
    categoria VARCHAR(100) GENERATED ALWAYS AS (atributos->>'$.categoria') STORED,
    valoracion DECIMAL(2,1) GENERATED ALWAYS AS (atributos->>'$.valoracion') VIRTUAL,
    INDEX idx_marca (marca),
    INDEX idx_cat_precio (categoria, precio),
    INDEX idx_valoracion (valoracion)
);
 
INSERT INTO catalogo (nombre, precio, atributos) VALUES
('Monitor 4K 27"', 349.99, '{"marca": "LG", "categoria": "monitores", "valoracion": 4.5, "resolucion": "3840x2160"}'),
('Monitor UW 34"', 599.00, '{"marca": "Dell", "categoria": "monitores", "valoracion": 4.7, "resolucion": "3440x1440"}'),
('Teclado mecánico', 129.99, '{"marca": "Keychron", "categoria": "perifericos", "valoracion": 4.8, "switches": "Brown"}'),
('Ratón gaming', 79.99, '{"marca": "Logitech", "categoria": "perifericos", "valoracion": 4.3, "dpi": 25600}');

Ahora los filtros típicos de una tienda online usan los índices:

SELECT nombre, precio, marca, valoracion
FROM catalogo
WHERE categoria = 'monitores'
  AND precio < 500
  AND valoracion >= 4.0;

Manejo de NULL

Cuando el documento JSON no contiene la clave que extrae la columna generada, el valor de esa columna será NULL. Los índices en MySQL incluyen las entradas NULL, así que una búsqueda como WHERE marca IS NULL también puede usar el índice:

INSERT INTO productos (nombre, precio, atributos) VALUES
('Producto genérico', 9.99, '{"categoria": "varios"}');
 
SELECT nombre FROM productos WHERE marca IS NULL;

Combinación con otras funciones

Es importante que las columnas generadas usen ->> (que devuelve texto sin comillas) en lugar de -> (que devuelve JSON entrecomillado). Si usas ->, el índice almacenará "Apple" con comillas en lugar de Apple, y tus comparaciones tendrían que incluir las comillas para que el índice funcione correctamente.

Cada índice adicional ralentiza las inserciones y actualizaciones porque MySQL debe mantener tanto el documento JSON como los índices sincronizados. Indexa solo los campos que realmente uses en cláusulas WHERE, JOIN o ORDER BY. Si un campo JSON solo se usa en el SELECT para mostrarlo, no necesita índice.

Los índices multi-valor son más costosos de mantener que los índices regulares porque cada fila puede generar múltiples entradas en el índice. Úsalos solo cuando necesites buscar pertenencia a arrays con frecuencia.

En el siguiente artículo veremos JSON_ARRAY para crear arrays JSON.