Cuando se trabaja con MySQL, existe la opción de variar el tipo de tabla después de creada (salvo con las tablas del sistema llamadas MySQL y test , que por defecto son MyISAM y no se recomienda modificarlas).
Para indicar el tipo de tabla al crearla usamos la siguiente sintaxis:
CREATE TABLE tabla1 (
campo1 INT(4) UNSIGNED,
campo2 VARCHAR(25) NOT NULL
) ENGINE=MYISAM;
Si se omite la opción ENGINE=... por defecto se crea una tabla MyISAM.
Importante: según MySQL, la opción TYPE (similar a ENGINE) es soportada hasta la versión 4.x de MySQL (dejará de usarse a partir de la 5). La opción ENGINE fue añadida en la versión 4.0.18 del lenguaje (para las series 4.x) y en la versión 4.1.2 (correspondiente a las versiones 4.1).
Si se intenta crear un tipo de tabla no disponible en nuestra versión, MySQL optará por crear una tabla tipo MyISAM.
A continuación, haremos una reseña de los tipos de tablas soportados.
ISAM
En un principio, el gestor de bases de datos MySQL empezó utilizando este tipo de tablas y, actualmente, se las considera en desuso. Entre sus desventajas figura el hecho de no poder transportar archivos entre máquinas con distinta arquitectura (tiene un formato diferente para cada arquitectura / sistema operativo, lo cual resulta más rápido, pero presenta el problema de la incompatibilidad) y el de no manejar archivos de tablas superiores a 4 GB.
Los índices se guardan en archivos .ISM y los datos, en archivos .ISD. MySQL recomienda actualizar este tipo de tablas hacia las de tipo MyISAM. Esto puede hacerse con la siguiente instrucción SQL:
ISAM
En un principio, el gestor de bases de datos MySQL empezó utilizando este tipo de tablas y, actualmente, se las considera en desuso. Entre sus desventajas figura el hecho de no poder transportar archivos entre máquinas con distinta arquitectura (tiene un formato diferente para cada arquitectura / sistema operativo, lo cual resulta más rápido, pero presenta el problema de la incompatibilidad) y el de no manejar archivos de tablas superiores a 4 GB.
Los índices se guardan en archivos .ISM y los datos, en archivos .ISD. MySQL recomienda actualizar este tipo de tablas hacia las de tipo MyISAM. Esto puede hacerse con la siguiente instrucción SQL:
ALTER TABLE nombre_de_la_tabla ENGINE = MYISAM;
MyISAM
Es el tipo de tabla por defecto en MySQL desde la versión 3.23 y está basada sobre las tablas ISAM, por supuesto que ofreciendo más opciones que éstas. Cuando se usan estas tablas, los datos se almacenan físicamente en dos archivos: uno que tiene la extensión .MYI (MYISAM INDEX), en donde se almacenan los índices, y otro .MYD (MYISAM DATA), donde se almacenan los datos.
Se encargan de proveer un almacenamiento independiente: esto significa que se pueden copiar tablas de una máquina a otra de distinta plataforma.
Si estamos trabajando con MySQL en forma local, normalmente, se podrá ver en el propio equipo que estos archivos se almacenan en una carpeta que tiene por nombre una base de datos (estas carpetas están en el directorio data dentro del directorio en donde se instaló MySQL, y allí hay una carpeta por cada base de datos). Esto vale también para otros tipos de tablas.
Además:
• Soportan archivos de gran tamaño (63 bits, archivos de tablas superiores a 4 GB)
en comparación con los que soportaban las ISAM.
• Están optimizadas para sistemas operativos de 64 bits.
• Posibilidad de indexar campos BLOB y TEXT.
• Se permiten valores NULL en columnas indexadas.
• Cada tabla guarda un registro que indica si fue cerrada correctamente o no, y al iniciar MySQL existe la opción de indicarle que se verifique ese registro, y se repare la tabla de ser necesario, de forma automática. Esto se logra iniciando MySQL con la opción:
--myisam-recover
Si se encontró un error, trata de repararlo de forma rápida ordenando los registros de la tabla. Si éste persiste, se vuelve a crear el archivo que contiene la tabla. Y si continúa existiendo, se intenta repararlo escribiendo los registros sin un ordenamiento.
• Inserts concurrentes (se pueden insertar varios registros al mismo tiempo).
MERGE
Este tipo de tabla es muy utilizada en los casos en que se precise tratar un número N de tablas MyISAM (de idéntica estructura y pertenecientes a la misma base de datos, de la que también deberá formar parte la tabla MERGE) como si fuera una sola.
Esto podría aplicarse si la tabla MyISAM original es de gran tamaño, y acceder a su contenido llevará una cantidad considerable de tiempo y recursos. Obviamente, estamos hablando de una tabla realmente muy grande.
Otras características:
• Sólo se pueden aplicar instrucciones SELECT, DELETE y UPDATE.
• La definición de la tabla se almacena en un archivo .FRM, y el listado de las tablas MyISAM, en un archivo .MRG (aquí hay en realidad un índice de los archivos .MYI usados. Ver tablas MYISAM).
• Permiten de alguna forma burlar el tamaño máximo de una tabla y el tamaño máximo de un archivo en un sistema operativo específico.
• Debemos saber que si queremos borrar una de las tablas MyISAM que forma parte de la tabla MERGE, no podremos hacerlo bajo el sistema operativo Windows, ya que éste no permite borrar archivos que estén abiertos, y la tabla, al formar parte de la tabla MERGE, se considera abierta. Por la misma razón se producen inconvenientes al aplicar instrucciones tales como DROP TABLE, ALTER TABLE, DELETE FROM sin WHERE, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE y ANALYZE TABLE. Una forma de solucionar esto es borrar el contenido de la tabla MERGE (aplicando la instrucción DELETE sin WHERE a una tabla MERGE, no se borra el contenido de las tablas MyISAM sino que se las quita del listado de componentes de la tabla MERGE). Así estas tablas se considerarán no abiertas.
Cuando se crea una tabla de este tipo hay que especificar (con la instrucción UNION) la lista de tablas asociadas. Veamos un ejemplo.
Devolvería algo como:
1 uno
2 dos
3 tres
1 cuatro
2 cinco
3 seis
El parámetro INSERT_METHOD especifica en qué tabla se realizarán los INSERTS; si en la primera de la lista (poniendo INSERT_METHOD=FIRST) o en la última (poniendo INSERT_METHOD=LAST). En nuestro caso la tabla t1 es la primera y t2 es la última.
HEAP
Este tipo de tablas tienen una particularidad que las hace diferentes del resto: son tablas en memoria, son temporales y desaparecen cuando el servidor se cierra.
Es importante saber que esto las hace realmente rápidas y que, a diferencia de una tabla TEMPORARY, que sólo puede ser accedida por el usuario que la crea, una tabla HEAP puede ser utilizada por diversas personas.
Algunas otras particularidades:
• No soportan columnas de tipo BLOB o TEXT.
• No soportan columnas de tipo AUTO_INCREMENT.
• No se permiten valores NULL en las columnas que han sido indexadas (antes de MySQL en la versión correspondiente a la 4.0.2).
• Siempre conviene especificar el número máximo de filas (MAX_ROWS) cuando se crea la tabla, para no usar toda la memoria disponible.
InnoDB
Debemos saber que estas tablas, al igual que las BerkeleyDB, son TST: este término significa Transactions Safe Tables, o tablas para transacciones seguras. Las tablas tipo TST son menos rápidas y ocupan más memoria, pero a cambio ofrecen mayor seguridad frente a fallos durante la consulta.
Además, las tablas InnoDB tienen las siguientes características:
• Proveen la posibilidad de transacciones seguras. ACID (Atomicidad; Consistencia; Separación, en inglés Isolation y Durabilidad).
• Recuperación ante fallos.
• Soporta FOREIGN KEY (Claves foráneas). Primera vez que se da esto en MySQL.
• Bloqueo a nivel de fila.
• Permite realizar copias de seguridad mientras la base está funcionando.
• Gran eficacia en el procesamiento de grandes volúmenes de información.
• No permite crear claves sobre columnas de tipo BLOB o TEXT.
• Una tabla no puede tener más de 1000 columnas.
• Al borrar todas las filas de una tabla las borra una por una –lo que produce problemas relacionados a la velocidad. Hasta ahora puede truncar tablas.
Fueron agregadas en la versión 4.0 de MySQL.
BerkeleyDB
Estas tablas pueden ser usadas independientemente de MySQL: están desarrolladas por otra empresa (Sleepycat) y el gestor de bases de datos MySQL ofrece una interfaz para trabajar con ellas como una posibilidad más.
• Soportan operaciones COMMIT y ROLLBACK.
• Es de tipo denominado TST (Transactions Safe Tables). Podemos ver tablas INNODB para obtener más información acerca de este tema.
• Normalmente, para instalarlas hay que buscar una versión de MySQL que incluya soporte para este tipo de tablas, y habilitar la opción al momento de la instalación (––with*berkeley–dboption).
• En el archivo en donde se guardan los datos también se guarda la ruta a ese mismo archivo, de modo que no es posible cambiar la base de directorio.
¿En qué casos usar cada una?
Como siempre, la respuesta depende de lo que tengamos que hacer. Las tablas que normalmente se usan hoy en día son las MyISAM, pero pronto (quizás muy pronto) se comenzarán a usar las INNODB, especialmente por la posibilidad de crear relaciones entre tablas (fundamental en el modelo relacional) y ofrecer mayores prestaciones respecto de la seguridad, además de las transacciones.
Las ISAM están prácticamente en desuso (incluso la empresa que desarrolla MySQL admite la posibilidad de que en su versión 5 ya no estén disponibles), y las demás tienen usos muy específicos e incluso compatibles con otros tipos: la clave está en estudiar los problemas que se necesita solucionar, y ver en cada caso qué conviene.
Es el tipo de tabla por defecto en MySQL desde la versión 3.23 y está basada sobre las tablas ISAM, por supuesto que ofreciendo más opciones que éstas. Cuando se usan estas tablas, los datos se almacenan físicamente en dos archivos: uno que tiene la extensión .MYI (MYISAM INDEX), en donde se almacenan los índices, y otro .MYD (MYISAM DATA), donde se almacenan los datos.
Se encargan de proveer un almacenamiento independiente: esto significa que se pueden copiar tablas de una máquina a otra de distinta plataforma.
Si estamos trabajando con MySQL en forma local, normalmente, se podrá ver en el propio equipo que estos archivos se almacenan en una carpeta que tiene por nombre una base de datos (estas carpetas están en el directorio data dentro del directorio en donde se instaló MySQL, y allí hay una carpeta por cada base de datos). Esto vale también para otros tipos de tablas.
Además:
• Soportan archivos de gran tamaño (63 bits, archivos de tablas superiores a 4 GB)
en comparación con los que soportaban las ISAM.
• Están optimizadas para sistemas operativos de 64 bits.
• Posibilidad de indexar campos BLOB y TEXT.
• Se permiten valores NULL en columnas indexadas.
• Cada tabla guarda un registro que indica si fue cerrada correctamente o no, y al iniciar MySQL existe la opción de indicarle que se verifique ese registro, y se repare la tabla de ser necesario, de forma automática. Esto se logra iniciando MySQL con la opción:
--myisam-recover
Si se encontró un error, trata de repararlo de forma rápida ordenando los registros de la tabla. Si éste persiste, se vuelve a crear el archivo que contiene la tabla. Y si continúa existiendo, se intenta repararlo escribiendo los registros sin un ordenamiento.
• Inserts concurrentes (se pueden insertar varios registros al mismo tiempo).
MERGE
Este tipo de tabla es muy utilizada en los casos en que se precise tratar un número N de tablas MyISAM (de idéntica estructura y pertenecientes a la misma base de datos, de la que también deberá formar parte la tabla MERGE) como si fuera una sola.
Esto podría aplicarse si la tabla MyISAM original es de gran tamaño, y acceder a su contenido llevará una cantidad considerable de tiempo y recursos. Obviamente, estamos hablando de una tabla realmente muy grande.
Otras características:
• Sólo se pueden aplicar instrucciones SELECT, DELETE y UPDATE.
• La definición de la tabla se almacena en un archivo .FRM, y el listado de las tablas MyISAM, en un archivo .MRG (aquí hay en realidad un índice de los archivos .MYI usados. Ver tablas MYISAM).
• Permiten de alguna forma burlar el tamaño máximo de una tabla y el tamaño máximo de un archivo en un sistema operativo específico.
• Debemos saber que si queremos borrar una de las tablas MyISAM que forma parte de la tabla MERGE, no podremos hacerlo bajo el sistema operativo Windows, ya que éste no permite borrar archivos que estén abiertos, y la tabla, al formar parte de la tabla MERGE, se considera abierta. Por la misma razón se producen inconvenientes al aplicar instrucciones tales como DROP TABLE, ALTER TABLE, DELETE FROM sin WHERE, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE y ANALYZE TABLE. Una forma de solucionar esto es borrar el contenido de la tabla MERGE (aplicando la instrucción DELETE sin WHERE a una tabla MERGE, no se borra el contenido de las tablas MyISAM sino que se las quita del listado de componentes de la tabla MERGE). Así estas tablas se considerarán no abiertas.
Cuando se crea una tabla de este tipo hay que especificar (con la instrucción UNION) la lista de tablas asociadas. Veamos un ejemplo.
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, mensaje CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, mensaje CHAR(20));
INSERT INTO t1 (mensaje) VALUES ("uno");
INSERT INTO t1 (mensaje) VALUES ("dos");
INSERT INTO t1 (mensaje) VALUES ("tres");
INSERT INTO t2 (mensaje) VALUES ("cuatro");
INSERT INTO t2 (mensaje) VALUES ("cinco");
INSERT INTO t2 (mensaje) VALUES ("seis");
CREATE TABLE total (
a INT AUTO_INCREMENT PRIMARY KEY,
mensaje CHAR(20)
)
ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
SELECT * FROM total;
Devolvería algo como:
1 uno
2 dos
3 tres
1 cuatro
2 cinco
3 seis
El parámetro INSERT_METHOD especifica en qué tabla se realizarán los INSERTS; si en la primera de la lista (poniendo INSERT_METHOD=FIRST) o en la última (poniendo INSERT_METHOD=LAST). En nuestro caso la tabla t1 es la primera y t2 es la última.
HEAP
Este tipo de tablas tienen una particularidad que las hace diferentes del resto: son tablas en memoria, son temporales y desaparecen cuando el servidor se cierra.
Es importante saber que esto las hace realmente rápidas y que, a diferencia de una tabla TEMPORARY, que sólo puede ser accedida por el usuario que la crea, una tabla HEAP puede ser utilizada por diversas personas.
Algunas otras particularidades:
• No soportan columnas de tipo BLOB o TEXT.
• No soportan columnas de tipo AUTO_INCREMENT.
• No se permiten valores NULL en las columnas que han sido indexadas (antes de MySQL en la versión correspondiente a la 4.0.2).
• Siempre conviene especificar el número máximo de filas (MAX_ROWS) cuando se crea la tabla, para no usar toda la memoria disponible.
InnoDB
Debemos saber que estas tablas, al igual que las BerkeleyDB, son TST: este término significa Transactions Safe Tables, o tablas para transacciones seguras. Las tablas tipo TST son menos rápidas y ocupan más memoria, pero a cambio ofrecen mayor seguridad frente a fallos durante la consulta.
Además, las tablas InnoDB tienen las siguientes características:
• Proveen la posibilidad de transacciones seguras. ACID (Atomicidad; Consistencia; Separación, en inglés Isolation y Durabilidad).
• Recuperación ante fallos.
• Soporta FOREIGN KEY (Claves foráneas). Primera vez que se da esto en MySQL.
• Bloqueo a nivel de fila.
• Permite realizar copias de seguridad mientras la base está funcionando.
• Gran eficacia en el procesamiento de grandes volúmenes de información.
• No permite crear claves sobre columnas de tipo BLOB o TEXT.
• Una tabla no puede tener más de 1000 columnas.
• Al borrar todas las filas de una tabla las borra una por una –lo que produce problemas relacionados a la velocidad. Hasta ahora puede truncar tablas.
Fueron agregadas en la versión 4.0 de MySQL.
BerkeleyDB
Estas tablas pueden ser usadas independientemente de MySQL: están desarrolladas por otra empresa (Sleepycat) y el gestor de bases de datos MySQL ofrece una interfaz para trabajar con ellas como una posibilidad más.
• Soportan operaciones COMMIT y ROLLBACK.
• Es de tipo denominado TST (Transactions Safe Tables). Podemos ver tablas INNODB para obtener más información acerca de este tema.
• Normalmente, para instalarlas hay que buscar una versión de MySQL que incluya soporte para este tipo de tablas, y habilitar la opción al momento de la instalación (––with*berkeley–dboption).
• En el archivo en donde se guardan los datos también se guarda la ruta a ese mismo archivo, de modo que no es posible cambiar la base de directorio.
¿En qué casos usar cada una?
Como siempre, la respuesta depende de lo que tengamos que hacer. Las tablas que normalmente se usan hoy en día son las MyISAM, pero pronto (quizás muy pronto) se comenzarán a usar las INNODB, especialmente por la posibilidad de crear relaciones entre tablas (fundamental en el modelo relacional) y ofrecer mayores prestaciones respecto de la seguridad, además de las transacciones.
Las ISAM están prácticamente en desuso (incluso la empresa que desarrolla MySQL admite la posibilidad de que en su versión 5 ya no estén disponibles), y las demás tienen usos muy específicos e incluso compatibles con otros tipos: la clave está en estudiar los problemas que se necesita solucionar, y ver en cada caso qué conviene.
