En este módulo aprenderemos las estrategias y técnicas para optimizar el modelo físico de una base de datos. La optimización es crucial para asegurar un buen rendimiento, especialmente en bases de datos con grandes volúmenes de datos o múltiples usuarios concurrentes.

Planificación de Consultas

Las consultas son la forma en que interactuamos con la base de datos. Para optimizar una consulta, es importante entender cómo el SGBD la ejecuta. Esto se hace mediante planes de ejecución.

Uso de EXPLAIN en SQL

EXPLAIN (o su equivalente) muestra cómo el SGBD ejecutará una consulta, permitiéndonos identificar posibles problemas.

Ejemplo: Supongamos que tenemos una tabla Productos con miles de registros y queremos buscar un producto por nombre:

1EXPLAIN SELECT * FROM Productos WHERE nombre = 'Zapatos';

El resultado puede mostrar:

• Si se está utilizando un índice.
• El número de registros que el SGBD necesita examinar.

Si no se usa un índice, podríamos crearlo para mejorar el rendimiento:

1CREATE INDEX idx_nombre ON Productos(nombre);

Índices Avanzados

Los índices son esenciales para acelerar consultas. Sin embargo, usarlos incorrectamente puede aumentar el tiempo de inserción y actualización.

Tipos de Índices Avanzados

1. Índices B-Tree:

   • El más común, usado para búsquedas de rango y exactas.
   • Ejemplo: Buscar productos cuyo precio esté entre $10 y $50.

2. Índices Hash:

   • Útil para búsquedas exactas.
   • Ejemplo: Buscar un producto por su ID.

3. Índices GiST (PostgreSQL):

   • Útil para datos espaciales o búsquedas complejas.
   • Ejemplo: Encontrar ubicaciones cercanas.

Cuándo NO usar índices:

• En tablas pequeñas (el índice podría ser más lento que una búsqueda completa).
• En columnas que cambian frecuentemente (inserciones y actualizaciones pueden ser más lentas).

Gestión de Almacenamiento

El diseño físico debe considerar cómo se almacenan los datos para optimizar el espacio y el rendimiento.

Tamaño de Bloques y Páginas

• Las bases de datos almacenan datos en bloques (o páginas) de un tamaño fijo.
• Ajustar el tamaño del bloque puede mejorar la velocidad de lectura o escritura, dependiendo del tipo de datos.

Ejemplo en PostgreSQL: Para tablas con registros grandes, usa un tamaño de bloque mayor.

Almacenamiento de Tablas e Índices

• Usa compresión para tablas grandes.
• Divide tablas en particiones para manejar datos históricos.

Paralelismo y Concurrencia

Las bases de datos modernas permiten manejar múltiples consultas al mismo tiempo.

Problemas comunes de concurrencia:

• Bloqueos: Una consulta impide que otra acceda a los datos.
• Condiciones de carrera: Dos transacciones intentan modificar el mismo dato.

Optimización de concurrencia:

• Usa índices para reducir el tiempo de bloqueo.
• Aplica niveles de aislamiento apropiados:
  • READ COMMITTED: Menos estricto, más rápido.
  • SERIALIZABLE: Más estricto, evita inconsistencias.

Compresión de Datos

La compresión reduce el tamaño de los datos almacenados, lo que puede acelerar las consultas en ciertos casos.

Ejemplo en MySQL:

1CREATE TABLE Productos (
2    idProducto INT PRIMARY KEY,
3    nombre VARCHAR(100),
4    precio DECIMAL(10,2)
5) ROW_FORMAT=COMPRESSED;

Ventajas y desventajas:

• Ventaja: Menor uso de disco.
• Desventaja: Puede aumentar el tiempo de escritura.

Caso Práctico: Optimización Paso a Paso

Supongamos un sistema de ventas con estas consultas comunes:

1. Obtener el producto más vendido.
2. Buscar productos por nombre.
3. Listar ventas de un cliente específico.

Diseño Inicial:

1CREATE TABLE Productos (
2    idProducto INT PRIMARY KEY,
3    nombre VARCHAR(100),
4    precio DECIMAL(10,2)
5);
6
7CREATE TABLE Ventas (
8    idVenta INT PRIMARY KEY,
9    idProducto INT,
10    idCliente INT,
11    fecha DATE,
12    FOREIGN KEY (idProducto) REFERENCES Productos(idProducto)
13);

Problemas detectados:

1. La consulta de productos por nombre es lenta.
2. Buscar ventas de un cliente requiere revisar toda la tabla Ventas.

Optimización Aplicada:

1. Crear un índice en nombre:

   1CREATE INDEX idx_nombre ON Productos(nombre);

2. Crear un índice compuesto en Ventas para búsquedas frecuentes:

   1CREATE INDEX idx_cliente_fecha ON Ventas(idCliente, fecha);

3. Particionar la tabla Ventas por año:

   • mysql
   • postgresql
   • sqlite
   • oracle
   • sqlserver

   1-- No admite particiones como tablas hijas. En su lugar, la tabla completa se crea con sus particiones:
   2CREATE TABLE Ventas (
   3   idVenta INT,
   4   idProducto INT,
   5   idCliente INT,
   6   fecha DATE,
   7   anio INT,
   8   PRIMARY KEY (idVenta, anio)
   9)
   10PARTITION BY LIST (anio) (
   11   PARTITION p2024 VALUES IN (2024)
   12);

Resultados:

• Las consultas son más rápidas porque el SGBD busca en particiones más pequeñas y utiliza índices.

Resumen del Módulo 4

1. Planificación de consultas: Usa herramientas como EXPLAIN para analizar y mejorar el rendimiento.
2. Índices avanzados: Aplica índices adecuados a las columnas que más consultas reciben.
3. Gestión de almacenamiento: Ajusta el tamaño de bloques, usa compresión y particiones para mejorar el rendimiento.
4. Concurrencia: Diseña para manejar múltiples usuarios sin conflictos.
5. Compresión: Reduce el espacio utilizado, pero evalúa el impacto en el rendimiento.