Curso de MariaDB / Crear índices simples y compuestos y elegir el orden de columnas

Un índice permite localizar y ordenar filas mediante una estructura de claves, y el orden de las columnas de un índice compuesto influye en las consultas que puede ayudar a resolver. Revise los filtros, la ordenación, las columnas solicitadas y los índices existentes, y compare el beneficio de lectura con el coste de escritura y almacenamiento.

Considerar el mantenimiento del índice

Una estructura de claves ordenadas puede acotar una búsqueda en lugar de examinar todas las filas. Sin embargo, las inserciones, actualizaciones y eliminaciones también deben mantener sus índices. Añadir más no siempre es mejor: estudie qué consultas se repiten, qué proporción de filas seleccionan y qué necesitan para sus uniones y su ordenación.

Consulta o regla Orden candidato Comprobación
Pedidos recientes de un cliente customer_id, ordered_at, order_id Igualdad, intervalo temporal y desempate
Pedidos de todos los clientes por fecha Puede requerir comenzar por la fecha Es un requisito diferente al filtro por cliente
Código de producto único UNIQUE(sku) También aplica una regla de integridad
Apoyo de una clave foránea existente Revisar SHOW INDEX Evitar duplicados accidentales

La regla UNIQUE del código del producto se aplica en la práctica de ALTER TABLE. Un índice puede servir tanto a las consultas como a una condición de integridad; no lo elimine basándose solo en una consulta aislada.

Inspeccionar los índices existentes

Conéctese al servidor de prácticas como course_user y seleccione shopcourse. La guía del cliente y los archivos SQL UTF-8 explica cómo guardar los bloques y ejecutarlos con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. El SQL se ejecuta dentro del cliente MariaDB, no en la consola del sistema operativo.

Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c39_customers, c39_products, c39_orders y c39_items. Los nombres pertenecen exclusivamente al ejercicio. Si ya existen, revise sus definiciones y registros antes de decidir si son prescindibles; no sustituya tablas de una aplicación.

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

El SQL conserva los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 viven en Seúl; el 2 en Busan y el 4 en Daejeon. Mantenga los literales UTF-8 al reproducir los resultados.

SHOW INDEX FROM c39_orders;

Compruebe Key_name, Column_name y Seq_in_index: este último indica la posición de la columna dentro de cada clave. Revise además Non_unique y Sub_part si necesita comparar unicidad y prefijos. Nombres distintos no demuestran que los índices sean diferentes.

La clave primaria y las claves foráneas pueden tener ya índices de apoyo. Inspeccione también SHOW CREATE TABLE para identificar qué restricciones dependen de ellos. No cree otro índice únicamente porque no esperaba encontrar el que generó el servidor.

Crear un índice por cliente, fecha e identificador

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

La consulta devuelve los pedidos 101 y 102. customer_id limita la población a un cliente; ordered_at expresa el intervalo de enero, con límite superior exclusivo; order_id define el desempate entre pedidos de la misma fecha y hora.

El resultado de EXPLAIN debe leerse tal como lo devuelve su servidor. possible_keys indica candidatos y key el índice elegido; crear el índice no obliga al optimizador a utilizarlo. Con cuatro pedidos puede preferir razonablemente examinar toda la tabla. Esta práctica no demuestra una mejora de velocidad.

Entender columnas iniciales, intervalos e índices de cobertura

Un índice compuesto organiza las claves según el orden de sus columnas. No suponga que una consulta que solo filtra por fecha obtiene el mismo acceso por intervalo con una clave que empieza por cliente. Tampoco equivale a afirmar que nunca pueda realizar ningún tipo de lectura del índice si falta una igualdad sobre la primera columna.

Los intervalos, las direcciones de ordenación, los prefijos de texto y las columnas devueltas influyen en el acceso posible. Un índice de cobertura contiene la información que necesita una consulta y puede evitar parte de las lecturas de filas de la tabla. No añada indiscriminadamente todas las columnas: aumenta el tamaño de las claves y su mantenimiento.

Compare el índice seleccionado y el campo Extra de EXPLAIN, sin confundir «índice candidato», «índice utilizado» y «consulta cubierta». La guía oficial para diseñar índices según un SELECT ofrece criterios y excepciones que deben contrastarse con las consultas y la versión reales.

Revisar solapamientos y dependencias de restricciones

Dos índices pueden tener nombres diferentes y secuencias de columnas iguales o parcialmente solapadas. Compare también su unicidad y la longitud de los prefijos antes de decidir si son redundantes. Vuelva a ejecutar SHOW INDEX después de crear la clave compuesta para observar cómo queda representado el apoyo de la clave foránea.

Eliminar el único índice que sostiene una clave foránea puede ser rechazado. Antes de quitar uno, identifique la restricción y las otras consultas que lo necesitan y prepare un sustituto cuando corresponda. La práctica de claves y relaciones permite revisar esas dependencias. PRIMARY KEY y UNIQUE también aplican reglas de datos y no son prescindibles solo porque un SELECT no las utilice.

Conservar resultados al comparar costes de acceso

Añadir un índice no debe cambiar las filas solicitadas ni el sentido del orden. Compare resultados antes y después, además de planes representativos, lecturas reales, latencia y coste de escritura con una distribución de datos adecuada. Registre el entorno y el método: una caché más caliente en la segunda ejecución no demuestra que toda la diferencia proceda del índice.

Antes de repetir la preparación, revise la base seleccionada, las definiciones y los registros y respalde lo que deba conservar. Si las cuatro tablas siguientes son únicamente objetos prescindibles del ejercicio, elimínelas en este orden, primero las hijas y después las referenciadas. DROP TABLE elimina estructuras y datos y no se revierte con un ROLLBACK ordinario; mantenga esta limpieza fuera de las pruebas transaccionales.

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;