Índices y Rendimiento

Los índices aceleran dramáticamente las consultas al permitir a la base de datos encontrar filas sin escanear toda la tabla.

Cómo Funcionan los Índices

Sin un índice, una consulta escanea cada fila (O(n)):

-- Escaneo completo de tabla — lento en tablas grandes
SELECT * FROM pedidos WHERE cliente_id = 42;

Con un índice, la BD usa un árbol B para encontrar filas en O(log n):

-- Crear índice en cliente_id
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
-- ¡Ahora la consulta anterior es rápida!

Tipos de Índices

Clave Primaria (índice automático)

CREATE TABLE productos (
    id INT PRIMARY KEY AUTO_INCREMENT  -- índice B-tree creado automáticamente
);

Índice Único

-- Aplica unicidad + crea un índice
ALTER TABLE clientes ADD UNIQUE INDEX idx_clientes_email (email);

Índice Compuesto

-- Útil para consultas que filtran/ordenan por múltiples columnas
CREATE INDEX idx_pedidos_cliente_estado ON pedidos(cliente_id, estado);

-- Este índice ayuda a todos estos:
SELECT * FROM pedidos WHERE cliente_id = 5;                            -- ✅
SELECT * FROM pedidos WHERE cliente_id = 5 AND estado = 'pagado';      -- ✅
SELECT * FROM pedidos WHERE estado = 'pagado';                         -- ❌ no aplica (regla del prefijo más a la izquierda)

Índice Full-Text

ALTER TABLE productos ADD FULLTEXT INDEX idx_productos_ft (nombre, descripcion);

SELECT * FROM productos
WHERE MATCH(nombre, descripcion) AGAINST ('laptop gaming' IN BOOLEAN MODE);

EXPLAIN — Plan de Ejecución de Consultas

Siempre usa EXPLAIN antes de optimizar:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

-- Columnas de salida importantes:
-- type: tipo de join (ALL=escaneo completo, ref=búsqueda por índice)
-- key: índice usado
-- rows: filas estimadas examinadas
-- Extra: info adicional

Buenas Prácticas con Índices

-- ✅ Indexar columnas usadas en WHERE, JOIN, ORDER BY
CREATE INDEX idx_productos_categoria_precio ON productos(categoria, precio);

SELECT * FROM productos
WHERE categoria = 'Electrónica'
ORDER BY precio DESC;  -- usa el índice eficientemente

-- ❌ Evitar funciones en columnas indexadas en WHERE
-- MAL (no puede usar el índice en email):
SELECT * FROM clientes WHERE LOWER(email) = '[email protected]';

-- BIEN (almacenar en minúsculas desde el inicio):
SELECT * FROM clientes WHERE email = '[email protected]';

Transacciones

Una transacción garantiza que un conjunto de operaciones todas tengan éxito o todas fallen:

START TRANSACTION;

-- Transferir $100 de la cuenta 1 a la cuenta 2
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;

COMMIT;    -- hacer los cambios permanentes
-- o
ROLLBACK;  -- deshacer todos los cambios si algo salió mal

Propiedades ACID

Niveles de Aislamiento de Transacciones

-- Establecer nivel de aislamiento para la sesión
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Niveles de aislamiento (de menor a mayor restricción):
-- READ UNCOMMITTED → lectura sucia posible
-- READ COMMITTED   → sin lecturas sucias (predeterminado de muchas BD)
-- REPEATABLE READ  → sin lecturas fantasma (predeterminado de MySQL)
-- SERIALIZABLE     → aislamiento total, menor concurrencia

Savepoints (Puntos de Guardado)

START TRANSACTION;

INSERT INTO pedidos (cliente_id, total) VALUES (1, 500);
SAVEPOINT despues_pedido;

INSERT INTO items_pedido (pedido_id, producto_id, cantidad) VALUES (LAST_INSERT_ID(), 1, 2);
-- Algo salió mal con los items
ROLLBACK TO SAVEPOINT despues_pedido;

-- El pedido aún existe, pero los items se revirtieron
COMMIT;