CÓMO TRABAJAR CON BASES DE DATOS Y TABLESPACES EN MYSQL

Uno de los conceptos que más confusión genera al empezar a administrar MySQL es la relación entre bases de datos, o schemas y tablespaces. Se suele pensar que una base de datos “contiene” los archivos físicos de las tablas, pero en realidad MySQL separa claramente dos cosas:

base de datos → contenedor lógico
tablespace → contenedor físico de almacenamiento

Base de datos vs Tablespace: la diferencia clave

Concepto	Qué es	Tipo	Dónde se almacena normalmente
Base de datos	Contenedor lógico (namespace)	Lógico	Directorio dentro del datadir
Tablespace	Archivo o conjunto de archivos de datos	Físico	Puede estar en cualquier ruta (incluso otro disco)

La base de datos organiza las tablas, vistas, procedimientos, etc.
El tablespace decide dónde físicamente se guardan los datos de esas tablas.

Se pueden tener tablas de varias bases de datos diferentes dentro del mismo tablespace, y también se pueden tener tablas de una misma base de datos repartidas en distintos tablespaces.

Crear una base de datos

Crear una base de datos que no tiene relación directa con los tablespaces:

CREATE DATABASE tienda
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Esto solo crea un directorio, por ejemplo "/var/lib/mysql/tienda/" donde MySQL guardará metadatos.Todavía no se ha creado ningún archivo de datos de tablas.

Crear un Tablespace personalizados

-- Tablespace dentro del datadir
CREATE TABLESPACE ts_tienda
  ADD DATAFILE 'ts_tienda.ibd'
  ENGINE = InnoDB;

-- Tablespace en una ruta externa (recomendado para discos rápidos)
CREATE TABLESPACE ts_externo
  ADD DATAFILE '/mnt/disco_ssd/datos/ts_ext

Ejemplo completo prácticos

-- 1. Crear base de datos
CREATE DATABASE ecommerce
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 2. Crear tablespace en disco rápido
CREATE TABLESPACE ts_ecommerce
  ADD DATAFILE '/data/ssd/ts_ecommerce.ibd'
  ENGINE = InnoDB;

-- 3. Crear tablas asignándolas al tablespace
USE ecommerce;

CREATE TABLE clientes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE NOT NULL,
  nombre VARCHAR(100)
) TABLESPACE ts_ecommerce;

CREATE TABLE pedidos (
  id INT PRIMARY KEY AUTO_INCREMENT,
  cliente_id INT,
  fecha DATETIME DEFAULT CURRENT_TIMESTAMP,
  total DECIMAL(12,2),
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
) TABLESPACE ts_ecommerce;

CREATE TABLE logs_auditoria (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  accion VARCHAR(100),
  fecha DATETIME
) TABLESPACE innodb_file_per_table;   -- esta tabla en file-per-table

En este ejemplo:

Las tablas "clientes" y "pedidos" están físicamente en "/data/ssd/ts_ecommerce.ibd"
La tabla "logs_auditoria" está en su propio archivo dentro de la carpeta de la base de datos
La base de datos "ecommerce" sigue existiendo como directorio lógico

Ventajas de usar Tablespaces personalizados

Control preciso de la ubicación de los datos (discos SSD vs HDD, distintos volúmenes).
Posibilidad de agrupar tablas relacionadas en el mismo archivo.
Mejor gestión del espacio y del rendimiento de E/S.
Facilidad para hacer backups o mover grupos de tablas de forma independiente.
Soporte de cifrado a nivel de tablespace (ENCRYPTION = 'Y').
Reducción del número de archivos cuando tienes miles de tablas.

Limitaciones y consideraciones importantes

Untablespace de InnoDB solo puede tener un único datafile.
No se puede usar ALTER TABLESPACE ... ADD DATAFILE en InnoDB (sí en NDB Cluster).
Antes de eliminar un tablespace (DROP TABLESPACE) debes eliminar o mover todas las tablas que contiene.
Los general tablespaces no están asociados a ninguna base de datos concreta.
En MySQL 8.0+ el diccionario de datos ya no usa archivos ".frm", por lo que la gestión es más limpia.

Consultas útiles de administración

-- Ver todos los tablespaces
SELECT * FROM information_schema.INNODB_TABLESPACES;

-- Ver en qué tablespace está cada tabla
SELECT 
  TABLE_SCHEMA,
  TABLE_NAME,
  TABLESPACE_NAME
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB';

-- Ver los archivos físicos
SELECT * FROM information_schema.FILES
WHERE FILE_TYPE = 'TABLESPACE';