Tipos de tabla

MySQL es una suerte de contenedor. Interpreta el SQL y le pasa las peticiones correspondientes a módulos que trabajan con las tablas. Es debido a esta estructura modular que dispone de una característica única: soporta varios tipos de tablas. MySQL llamará a un módulo u otro dependiendo del tipo concreto de la tabla con la que necesite interactuar. Hay dos tipos de tabla que soportan transacciones pero son más lentas (InnoDB y BDB) y cuatro de menores prestaciones pero que son más rápidas.

Las tablas ISAM (Indexed Sequential Access Method) son el tipo de tabla más antiguo de MySQL. Aunque seguirán siendo incluidas en los binarios de MySQL hasta las versiones 4.1.x, están siendo sustituidas por las tablas MyISAM, cuyos índices ocupan menos espacio. Eso significa que emplean menos memoria en los SELECT, pero más potencia de procesador.

Tienen las siguientes características:

  • Almacenan en el fichero nombretabla.isd los datos y en nombretabla.ism los índices.
  • Esos ficheros no se pueden copiar de un sistema operativo a otro y que funcionen, de modo que ese sistema de backup no se puede emplear.

Dado que este tipo de tablas va a dejar de existir en breve, se aconseja convertir a MyISAM todas las tablas de este tipo:

ALTER TABLE nombretabla TYPE=MyISAM;

Las tablas MyISAM tienen una estructura similar a las tablas ISAM, aunque las extensiones de los ficheros son myd para los datos y myi para los índices. Existen tres subtipos de tablas: estáticas, dinámicas o comprimidas. MySQL decide automáticamente entre las dos primeras. Si en la definición de los campos hay alguno de longitud variable (como VARCHAR, BLOB o TEXT) la tabla será dinámica.

Tablas estáticas

En las tablas estáticas (o de longitud fija) todos los registros tienen el mismo tamaño. Eso las hace muy rápidas (el gestor siempre sabrá que el segundo registro está en el carácter número 11), fácilmente reconstruibles tras algún desastre, pero requieren mucho espacio (en este caso se usan 30 caracteres cuando sólo harían falta 19).

Tablas dinámicas

En las tablas dinámicas todas las columnas de tipo carácter son de anchura variable (a menos que tengan menos de 4 bytes). Normalmente ocupan mucho menos que las fijas. No obstante, de vez en cuando hay que desfragmentarlas, cual disco duro de Windows y no son fáciles de recuperar tras un desastre (especialmente si estaban muy fragmentadas en ese momento).

Tablas comprimidas

Son tablas de sólo lectura que emplean muy poco espacio. Para emplearlas necesitaremos tener instalado myisampack y emplear el parámetro ROW_FORMAT="compressed" detrás del tipo de tabla. No resultan demasiado útiles.

Las tablas MERGE son un tipo muy especial de tablas, pues se forman a base de la unión de dos tablas MyISAM idénticas.

Ejemplo:

Creamos una tabla MERGE. Si después de hacerlo probamos a insertar registros en las dos primeras tablas con MyCC y examinamos el contenido de unidas, veremos que tiene los registros insertados en ambas tablas.

CREATE TABLE aunir1 (
id INT NOT NULL,
campo CHAR(10),
PRIMARY KEY (id)
) TYPE = MyISAM;

CREATE TABLE aunir2 (
id INT NOT NULL,
campo CHAR(10),
PRIMARY KEY (id)
) TYPE = MyISAM;

CREATE TABLE unidas (
id INT NOT NULL,
campo CHAR(10),
PRIMARY KEY (id)
) TYPE = MERGE UNION=(aunir1, aunir2);

Principalmente se utilizan para tablas muy grandes, que superen el tamaño máximo de archivo permitido por el sistema operativo. También pueden emplearse por seguridad, dejando en una tabla de sólo lectura los contenidos antiguos y arriesgandose a que falle en una inserción o modificación otra, más pequeña. No obstante, las consultas pueden ser más lentas.

Los registros estarán físicamente en las tablas MyISAM, de modo que podemos realizar modificaciones y borrados en la tabla MERGE con la tranquilidad de que se realizarán sobre la tabla correcta. No obstante, al insertar MySQL no tiene manera de saber en cual de las dos tablas deseamos insertar físicamente el registro. Esto se soluciona añadiendo detrás de UNION la cláusula INSERT_METHOD que puede ser FIRST (las inserciones se realizarán en la primera tabla de la unión), LAST (en la última) o NO (no se harán). La definición anterior quedaría:

CREATE TABLE unidas (
id INT NOT NULL,
campo CHAR(10),
PRIMARY KEY (id)
) TYPE = MERGE
UNION=(aunir1, aunir2)
INSERT_METHOD=LAST;

Las tablas HEAP se almacenan en memoria y tienen índices de tipo hash. Son, por tanto, las más rápidas pero si se produce un problema o, simplemente, se necesitar parar MySQL, los datos que contiene se perderán. Normalmente no almacenarán muchos datos, a no ser que tengamos una cantidad ingente de RAM.

El uso normal de este tipo de tablas es la creación de tablas de acceso rápido a las que pediremos los datos, mientras las inserciones y las modificaciones se realizan en otra tabla algo más seguro que se almacene en el disco duro. Sirven como una suerte de tablas caché. Para poder crearlas emplearemos una sintaxis específica:

CREATE TABLE prueba_heap TYPE=HEAP SELECT * FROM departamentos;

Ahora podemos ver con MyCC que tiene la misma estructura y las mismas columnas que la tabla original.

Las tablas InnoDB soportan transacciones (lo que significa que podremos utilizar en ellas COMMIT y ROLLBACK). Las tablas MyISAM son completamente bloqueadas cuando se inserta. Durante la fracción de segundo que dura la inserción no se puede realizar nada más en la tabla. InnoDB emplea bloqueo a nivel de registro, de modo que sólo ese registro estará bloqueado y no toda la tabla.

Las tablas InnoDB no se almacenan en ficheros divididos por directorios, cada uno con el nombre de la base de datos a la que pertenecen: todas las tablas e índices se almacenan en el llamado espacio de tabla, para el que se usa uno o varios ficheros, dependiendo del tamaño total de todas las tablas InnoDB. De ese modo. Estos ficheros tendrán el nombre ibdata1, ibdata2, etc..

Las tablas InnoDB son también las únicas que, de momento, disponen de la posibilidad de emplear claves extranjeras.

BDB son las siglas de Berkeley Database (pues este tipo de tablas se creó en esa Universidad). Soporta transacciones, como InnoDB, pero no claves extranjeras, al igual que MyISAM. Actualmente, el soporte de BDB en MySQL todavía se considera en estado beta. Ambos productos son ya antiguos, es el interfaz entre ellos el que es relativamente nuevo. No obstante, comprueba la documentación de tu versión de MySQL por si dicho problema ya está solucionado. Las tablas BDB se crean de esta manera:

CREATE TABLE prueba_bdb (
id INT NOT NULL,
campo CHAR(10),
PRIMARY KEY (id)
) TYPE = BDB;

Esto creará un fichero prueba_bdb.db donde se almacenarán tanto los datos como los índices.

Seguramente no existe un tipo de tabla universal para emplear en todas las situaciones y proyectos. Por sus características, seguramente nos veremos tentados a emplear siempre InnoDB, no obstante también la rapidez es algo que tendremos que tener en cuenta. Estas son unas medidas de rendimiento basadas en tablas simples:

Inserciones por segundo Consultas por segundo
HEAP 4150 2395
MyISAM 2968 2079
InnoDB 3042 2051
BDB 2602 2107

Por prestaciones, vemos que las tablas HEAP son las mejores, pero no parecen suficientemente rápidas como para justificar las complicaciones que debemos afrontar al usarlas. Las demás presentan un comportamiento parecido, pese a que los manuales de MySQL recomiendan tablas MyISAM cuando hay muchas más consultas que inserciones e InnoDB cuando está la cosa más equilibrada o hay más inserciones.

« MySQL 4 | Creación de bases de datos

Si no se indica lo contrario, el contenido de esta página se ofrece bajo Creative Commons Attribution-ShareAlike 3.0 License