SQL: Transacciones, Índices y Constraints

En sistemas con múltiples usuarios escribiendo datos al mismo tiempo, la diferencia entre una aplicación confiable y una que corrompe información suele estar en cómo manejas transacciones en SQL, índices y constraints. En el artículo anterior analizamos Vistas (Views); ahora cerramos la serie con mecanismos de integridad y rendimiento.

Qué es una transacción en SQL

Una transacción agrupa una o varias sentencias SQL que deben ejecutarse como una unidad lógica. El objetivo es que los cambios se apliquen completos o no se apliquen en absoluto.

Consideremos una transferencia bancaria. Restar dinero de una cuenta y sumarlo a otra no son acciones independientes desde el punto de vista del negocio. Si una actualización se ejecuta y la otra falla, el sistema queda en un estado inconsistente.

START TRANSACTION;

  /* retirar dinero de la primera cuenta, validando saldo suficiente */
UPDATE account SET avail_balance = avail_balance - 500
WHERE account_id = 9988
  AND avail_balance > 500;

IF <exactly one row was updated by the previous statement> THEN
  /* depositar dinero en la segunda cuenta */
  UPDATE account SET avail_balance = avail_balance + 500
    WHERE account_id = 9989;

  IF <exactly one row was updated by the previous statement> THEN
    /* todo funcionó, hacer los cambios permanentes */
    COMMIT;
  ELSE
    /* algo falló, deshacer todos los cambios de esta transacción */
    ROLLBACK;
  END IF;
ELSE
  /* saldo insuficiente o error durante la actualización */
  ROLLBACK;
END IF;

En este flujo, COMMIT confirma los cambios y ROLLBACK revierte todo lo ejecutado desde el inicio de la transacción. La base de datos actúa como una frontera de consistencia.

Candados y concurrencia

Cuando varias sesiones intentan leer o escribir los mismos datos, el motor puede usar candados (locks) sobre filas, páginas o tablas. Los locks evitan que una transacción lea datos en transición o sobrescriba cambios que todavía no han sido confirmados.

Iniciar una transacción

Los servidores de base de datos suelen manejar la creación de transacciones de una de estas dos formas:

• Siempre existe una transacción activa asociada a la sesión. Cuando la transacción actual termina, el servidor inicia automáticamente una nueva.
• Cada sentencia SQL se confirma automáticamente, a menos que inicies una transacción explícita.

En MySQL, una transacción explícita puede comenzar con:

start transaction

Terminar una transacción

Una vez iniciada una transacción, necesitas terminarla explícitamente para que los cambios sean permanentes. COMMIT marca los cambios como definitivos y libera recursos usados durante la transacción,.

Si decides deshacer lo ejecutado desde el inicio de la transacción, usas ROLLBACK. El servidor devuelve los datos al estado previo de la transacción.

Además de COMMIT y ROLLBACK, una transacción puede terminar por otros eventos:

• El servidor se apaga; al reiniciar, la transacción pendiente se revierte automáticamente.
• Ejecutas una sentencia de esquema como ALTER TABLE; en varios motores esto puede confirmar la transacción actual e iniciar otra.
• Ejecutas otro START TRANSACTION; algunos motores confirman la transacción anterior antes de abrir la nueva.
• El servidor detecta un deadlock, elige una transacción como víctima y la revierte para desbloquear el sistema.

Savepoints dentro de una transacción

Los savepoints permiten definir puntos intermedios de rollback dentro de una misma transacción. En lugar de revertir todo, puedes regresar a un punto específico y continuar trabajando.

Índices y constraints

Una consulta sin apoyo puede obligar al servidor a escanear fila por fila. En tablas con millones de registros y decenas de columnas, esto se convierte en un cuello de botella.

Los índices son estructuras auxiliares que permiten encontrar filas con menos trabajo. Aceleran búsquedas, filtros, joins y ordenamientos.

Creación de índices

Para crear un índice en MySQL podemos usar ALTER TABLE. Supongamos que el sistema consulta clientes frecuentemente por correo electrónico:

mysql> ALTER TABLE customer
    -> ADD INDEX idx_email (email);
Query OK, 0 rows affected (1.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

Después de crear el índice, el optimizador decide si conviene usarlo. El motor evalúa estadísticas, cardinalidad y costo estimado.

Podemos inspeccionar los índices existentes con SHOW INDEX:

mysql> SHOW INDEX FROM customer \G;
*************************** 1. row ***************************
        Table: customer
   Non_unique: 1
     Key_name: idx_email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 599
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
...
5 rows in set (0.06 sec)

Y también podemos eliminarlos cuando dejan de aportar valor:

mysql> ALTER TABLE customer
    -> DROP INDEX idx_email;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

Índices únicos

Un índice único cumple dos funciones: acelera búsquedas y evita valores duplicados en las columnas indexadas. Cada vez que insertas una fila o modificas la columna, el servidor valida si el valor ya existe.

mysql> ALTER TABLE customer
    -> ADD UNIQUE idx_email (email);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

Si intentas insertar un cliente con un email ya registrado, el motor rechaza la operación:

mysql> INSERT INTO customer
    ->  (store_id, first_name, last_name, email, address_id, active)
    -> VALUES
    ->  (1,'ALAN','KAHN', 'ALAN.KAHN@sakilacustomer.org', 394, 1);
ERROR 1062 (23000): Duplicate entry 'ALAN.KAHN@sakilacustomer.org' 
  for key 'idx_email'

Índices multicolumna

También puedes crear índices que cubren varias columnas. Si buscas clientes por apellido y nombre, un índice compuesto puede ser más útil que dos índices separados.

mysql> ALTER TABLE customer
    -> ADD INDEX idx_full_name (last_name, first_name);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

El orden importa. Un índice (last_name, first_name) ayuda a consultas que filtran por last_name o por last_name junto con first_name. No ayuda igual a una consulta que filtra solo por first_name.

Tipos de índices

Índices B-tree

Los índices B-tree organizan los valores como árboles balanceados. Los branch nodes sirven para navegar por rangos, mientras que los leaf nodes almacenan los valores reales del índice.

Branch nodes dirigen la búsqueda hacia el rango correcto; leaf nodes contienen las entradas finales asociadas a los valores indexados.

flowchart TD
    root["Nodo raíz
A - M | N - Z"]
    b1["A-C | D-F | G-I | J-M"]
    b2["N-P | Q-S | T-V | W-Z"]
    root --> b1
    root --> b2
    b1 --> l1["Barker
Blake"]
    b1 --> l2["Fleming
Fowler"]
    b1 --> l3["Gooding
Grossman
Hawthorne"]
    b1 --> l4["Jameson
Markham
Mason"]
    b2 --> l5["Parker
Portman"]
    b2 --> l6["Roberts
Smith"]
    b2 --> l7["Tucker
Tulman
Tyler"]
    b2 --> l8["Ziegler"]

Este tipo de índice funciona muy bien para columnas con muchos valores distintos, como customer.last_name, fechas, identificadores externos o campos usados en rangos.

Índices bitmap

Los bitmap indexes están diseñados para columnas de baja cardinalidad, es decir, columnas con pocos valores posibles repetidos en muchas filas. Un ejemplo clásico es customer.active, donde puede haber solo 1 para activo y 0 para inactivo.

En lugar de mantener un árbol grande, el motor mantiene bitmaps por valor. Para consultar clientes inactivos, puede recorrer el bitmap del valor 0 y localizar las filas correspondientes.

Esta estrategia es compacta para baja cardinalidad, pero se degrada cuando la columna tiene demasiados valores distintos.

Índices de texto

Cuando almacenas documentos o contenido largo, buscar palabras con un escaneo completo es costoso.

MySQL y SQL Server ofrecen índices full-text; Oracle incluye herramientas bajo Oracle Text. Estos mecanismos están especializados para documentos y búsquedas textuales.

Cómo se usan los índices

Los índices permiten acceder rápidamente a datos, pero el motor decide si usarlos según el plan de ejecución. Un índice creado no garantiza automáticamente una consulta más rápida.

Observemos tres criterios prácticos:

• Crea índices alrededor de consultas frecuentes, no alrededor de columnas al azar.
• Evita indexar columnas con baja selectividad usando B-tree si el motor ofrece una estrategia mejor.
• Mide el impacto en escrituras, porque cada índice debe mantenerse actualizado.

Constraints: restricciones de integridad

Una constraint es una restricción aplicada a una o más columnas de una tabla. Vive dentro del esquema y protege los datos desde la base.

Los tipos principales son:

• Primary key constraints: identifican la columna o combinación de columnas que garantizan unicidad dentro de una tabla.
• Foreign key constraints: restringen una o más columnas para que contengan valores existentes en la primary key de otra tabla.
• Unique constraints: evitan duplicados en una o más columnas; una primary key es un caso especial de unicidad.
• Check constraints: restringen los valores permitidos para una columna según una condición.

Normalmente las constraints se crean junto con la tabla usando CREATE TABLE. Este ejemplo del esquema Sakila combina primary key, índices y foreign keys:

CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  create_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id),
  KEY idx_fk_store_id (store_id),
  KEY idx_fk_address_id (address_id),
  KEY idx_last_name (last_name),
  CONSTRAINT fk_customer_address FOREIGN KEY (address_id) 
    REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_customer_store FOREIGN KEY (store_id) 
    REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

También puedes agregar constraints después con ALTER TABLE:

ALTER TABLE customer
ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_id)
REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE customer
ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id)
REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE;

La cláusula ON DELETE RESTRICT protege contra registros huérfanos cuando se intenta eliminar una fila padre.

Para foreign keys, las acciones comunes ante borrados y actualizaciones son:

• ON DELETE RESTRICT: impide borrar la fila padre si existen filas hijas relacionadas.
• ON DELETE CASCADE: borra automáticamente las filas hijas cuando se borra la fila padre.
• ON DELETE SET NULL: asigna NULL en la foreign key de las filas hijas cuando se borra la fila padre.
• ON UPDATE RESTRICT: impide actualizar la clave padre si hay filas hijas relacionadas.
• ON UPDATE CASCADE: propaga el cambio de clave hacia las filas hijas.
• ON UPDATE SET NULL: asigna NULL en la foreign key de las filas hijas cuando cambia la clave padre.

Conclusión de la serie SQL

Las transacciones, índices y constraints completan la base práctica para trabajar con SQL de forma profesional. Protegen operaciones compuestas, reducen el costo de acceso a datos y llevan reglas críticas al esquema.

Con este artículo cerramos la serie de SQL. Para repasar desde el inicio, vuelve a: Fundamentos SQL: SELECT, FROM y WHERE. Desde ahí puedes recorrer consultas, filtros, joins, agrupaciones, subconsultas, vistas y estos mecanismos de integridad y rendimiento.