MyISAM era el motor de almacenamiento (storage engine) por defecto en MySQL hasta la versión 5.5.5. Está basado en el viejo (y ya no disponible) motor de almacenamiento ISAM (Indexed Sequential Access Method) desarrollado por IBM. A pesar de ser viejo y tener varias desventajas (no soporta transacciones, claves foráneas, ni locks a nivel de filas), aún conserva algunas extensiones útiles. En la versión 5.5.5 de MySQL, el motor de almacenamiento por defecto fue cambiado a InnoDB.

MyISAM está optimizado para entornos con grandes cantidades de operaciones de lectura y pocas escrituras, o ninguna. Esto se debe a la estructura de sus índices: cada entrada apunta a un registro en el archivo de datos, donde el puntero es un simple offset desde el comienzo del archivo. De esta forma los registros pueden leerse rápidamente, especialmente cuando su formato es fijo (es decir, las filas tiene una longitud constante). Los inserts son simples porque las nuevas filas se agregan al final del archivo. Sin embargo, las operaciones de borrado (delete) y actualización (update) son problemáticas: cuando se borra se debe dejar un espacio en blanco (de lo contrario se pierde la integridad de los offsets de los índices). Lo mismo para las actualizaciones si cambia el ancho de la fila: si se acorta se deja un espacio en blanco, y si se amplía la fila queda fragmentada. Esto implica una sobrecarga de administración, ya que es necesario optimizar y defragmentar tablas luego de muchos inserts/updates.

Aunque el mayor problema de las tablas ISAM/MyISAM es su pobre robustez y resistencia ante fallos. Las tablas MyISAM se corrompen fácilmente ante caídas del sistema. Por lo tanto, luego de un crash en un servidor de bases de datos que posee tablas MyISAM, es necesario verificar y reparar las mismas.



Cada tabla MyISAM es almacenada en disco en tres archivos cuyo nombre comienza con el nombre de la tabla y tiene una extensión que indica el tipo. El archivo con extensión .frm almacena el formato de la tabla. Los datos de la tabla se guardan en el archivo con extensión .MYD (MYData). El índice de la tabla tiene la extensión .MYI (MYIndex).

Cabe mencionar que una misma base de datos MySQL puede contener una mezcla de tablas InnoDB y MyISAM.

Cómo determinar qué motor de almacenamiento utiliza una tabla

Si se desea saber qué motor de almacenamiento (MyISAM o InnoDB) utiliza una tabla, seguir los siguientes pasos:

Conectarse al servidor de bases de datos MySQL por SSH (esto si el servidor MySQL no admite sesiones remotas, o se está utilizando un firewall que restringe el acceso):

[emi@hal9000 ~]$ ssh -p 2501 root@db1.test.linuxito.com
root@db1.test.linuxito.com's password: 
Last login: Thu Oct 23 09:12:02 2014 from hal9000

Luego conectarse al servidor MySQL de forma local:

[root@db1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50217
Server version: 5.5.40-0+wheezy1 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Cambiar a la base de datos que contiene la tabla que se desea examinar:

mysql> use test_foro;
Database changed

Si es necesario listar las bases de datos existentes, ejecutar el comando SHOW DATABASES. Luego es posible listar todas las tablas de la base de datos seleccionada utilizando SHOW TABLES.

Para determinar el motor de almacenamiento que utiliza una tabla, se debe examinar cómo fue creada una tabla. Para ello utilizar el comando SHOW CREATE TABLE especificando el nombre de la misma:

mysql> show create table usuarios\G
*************************** 1. row ***************************
       Table: usuarios
Create Table: CREATE TABLE `usuarios` (
  `id` varchar(20) NOT NULL default '',
  `codigo` bigint(20) NOT NULL auto_increment,
  `passwd` varchar(255) NOT NULL default '',
  `nombre` varchar(30) NOT NULL default '',
  `apellido` varchar(30) NOT NULL default '',
  `mail` varchar(80) NOT NULL default '',
  `sexo` varchar(20) NOT NULL default '',
  `fechanac` date default NULL,
  `telefono` varchar(20) default NULL,
  `login` datetime default NULL,
  `certificado` varchar(20) NOT NULL default '',
  `comentario` varchar(40) NOT NULL default '',
  `pregunta` varchar(80) NOT NULL default '',
  `respuesta` varchar(80) NOT NULL default '',
  `recordar` datetime default NULL,
  PRIMARY KEY  (`codigo`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2406 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Luego de la definición de columnas (todo lo encerrado entre paréntesis en la sentencia) se observa la especificación del motor de almacenamiento. En este ejemplo ENGINE=MyISAM indica que se trata de una tabla MyISAM.

Cómo obtener un listado de todas las tablas MyISAM

Una técnica para conocer el motor de almacenamiento de todas las tablas en un servidor MySQL consiste en consultar el estado de las tablas. Para ello se puede ejecutar la consulta SQL SHOW TABLE STATUS, la cual, entre mucha información que presenta, incluye el motor de almacenamiento utilizado. Se puede filtrar por tabla utilizando una cláusula "where", por ejemplo: show table status where name='usuarios'

Inclusive se puede obtener información o consultar el estado de las tablas utilizando la herramienta mysqlshow desde línea de comandos:

mysqlshow -u root -p -i test_foro
mysqlshow -u root -p --status test_foro

Por ejemplo, para consultar el motor de almacenamiento que utiliza la tabla "usuarios" perteneciente a la base de datos "test_foro", desde línea de comandos, ejecutar:

[root@db1 ~]# mysqlshow -u root -p --status test_foro | grep usuarios | cut -d '|' --fields=2,3
Enter password: 
 usuarios        | MyISAM 
 usuarios_copia  | MyISAM
 usuarios_foro   | InnoDB 

Aunque, para mi gusto, la alternativa para obtener un listado de todas las tablas MyISAM que mejor salida presenta consiste en consultar la tabla "tables" de la base de datos "information_schema":

mysql> select table_schema,table_name,engine from information_schema.tables where engine='MyISAM';
+--------------------+-------------------------------------+--------+
| table_schema       | table_name                          | engine |
+--------------------+-------------------------------------+--------+
| information_schema | COLUMNS                             | MyISAM | 
| information_schema | ROUTINES                            | MyISAM | 
| information_schema | TRIGGERS                            | MyISAM | 
| information_schema | VIEWS                               | MyISAM | 
| test_web           | articulos                           | MyISAM | 
| test_web           | categorias                          | MyISAM | 
| test_web           | multimedia                          | MyISAM | 
| test_web           | modulos                             | MyISAM | 
| test_web           | plugins                             | MyISAM | 
| test_web           | usuarios                            | MyISAM | 
| test_web           | repositorio_archivos_usuarios       | MyISAM | 
| test_web           | menu                                | MyISAM | 
| test_web           | sesiones                            | MyISAM | 
| test_web           | mod_comments_related_content_tags   | MyISAM | 
| test_web           | mod_comments_related_content_posts  | MyISAM | 
| test_web           | mod_comments_related_content_links  | MyISAM | 
| test_web           | comentarios                         | MyISAM | 
| mysql              | columns_priv                        | MyISAM | 
| mysql              | db                                  | MyISAM | 
| mysql              | func                                | MyISAM | 
| mysql              | help_category                       | MyISAM | 
| mysql              | help_keyword                        | MyISAM | 
| mysql              | help_relation                       | MyISAM | 
| mysql              | help_topic                          | MyISAM | 
| mysql              | host                                | MyISAM | 
| mysql              | proc                                | MyISAM | 
| mysql              | procs_priv                          | MyISAM | 
| mysql              | tables_priv                         | MyISAM | 
| mysql              | time_zone                           | MyISAM | 
| mysql              | time_zone_leap_second               | MyISAM | 
| mysql              | time_zone_name                      | MyISAM | 
| mysql              | time_zone_transition                | MyISAM | 
| mysql              | time_zone_transition_type           | MyISAM | 
| mysql              | user                                | MyISAM | 
| test_foro          | plugins                             | MyISAM | 
| test_foro          | atributos                           | MyISAM | 
| test_foro          | batch                               | MyISAM | 
| test_foro          | grupos                              | MyISAM | 
| test_foro          | mensajes                            | MyISAM | 
| test_foro          | menu                                | MyISAM | 
| test_foro          | nodos                               | MyISAM | 
| test_foro          | paises                              | MyISAM | 
| test_foro          | historial_passwd                    | MyISAM | 
| test_foro          | roles                               | MyISAM | 
| test_foro          | sesion                              | MyISAM | 
| test_foro          | usuarios                            | MyISAM | 
| test_foro          | usuarios_copia                      | MyISAM | 
+--------------------+-------------------------------------+--------+
47 rows in set (1.34 sec)

Cómo verificar y reparar tablas MyISAM

Una vez que se conoce que existen tablas MyISAM en un servidor de bases de datos MySQL, tal vez sea necesario verificarlas (y posiblemente repararlas si se han encontrado errores durante la verificación).

Para verificar una tabla en un servidor MySQL, es posible ejecutar la consulta SQL CHECK TABLE, por ejemplo:

mysql> check table usuarios;
+--------------------+-------+----------+----------------------------+
| Table              | Op    | Msg_type | Msg_text                   |
+--------------------+-------+----------+----------------------------+
| test_foro.usuarios | check | status   | OK                         | 
+--------------------+-------+----------+----------------------------+
1 row in set (0.05 sec)

CHECK TABLE funciona para tablas InnoDB, MyISAM, ARCHIVE y CSV.

Si se ha corrompido una tabla o encuentran errores durante la verificación, será necesario reparar la tabla utilizando la sentencia REPAIR TABLE. Por ejemplo, al verificar la tabla "usuarios_copia", se detecta que se ha corrompido:

mysql> check table usuarios;
+--------------------------+-------+----------+----------------------------+
| Table                    | Op    | Msg_type | Msg_text                   |
+--------------------------+-------+----------+----------------------------+
| test_foro.usuarios_copia | check | warning  | Table is marked as crashed | 
| test_foro.usuarios_copia | check | status   | OK                         | 
+--------------------------+-------+----------+----------------------------+
2 rows in set (0.10 sec)

Por lo tanto, es necesario repararla ejecutando REPAIR TABLE:

mysql> repair table usuarios_copia;
+--------------------------+--------+----------+----------+
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| test_foro.usuarios_copia | repair | status   | OK       |
+--------------------------+--------+----------+----------+
1 row in set (0.02 sec)

Desde línea de comandos es posible utilizar la herramienta myisamchk (por ejemplo para crear un script Bash que verifique y repare todas las tablas MyISAM). Aunque para verificar una tabla, antes es necesario localizarla, ya que la herramienta myisamchk desconoce la ubicación en disco de las tablas (funciona independientemente del servidor MySQL).

Cómo localizar la ubicación en disco de las tablas MyISAM

No hay forma de determinar la ubicación en disco de una tabla ejecutando una consulta SQL, lo cual tiene sentido desde el punto de vista de seguridad.

El directorio donde el servidor MySQL guarda físicamente las tablas en disco se puede definir de dos formas. Es posible especificarlo como parámetro en la línea de comandos al iniciar el servidor o, más comúnmente, en la variable datadir dentro del archivo my.cnf de configuración del servicio.

Revisar primero si se ha pasado como parámetro en la línea de comandos. Ejecutar el comando ps aux | grep mysql | grep --color datadir para tratar de determinar si se ha especificado un directorio de datos a través de la variable "datadir".

En el siguiente ejemplo, se observa que se ha indicado el directorio de datos /databases/my_db1:

[root@db1 ~]# ps aux | grep mysql | grep --color datadir
root      1499  0.0  0.1 108168   736 ?        S    Sep10   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/databases/my_db1 --pid-file=/databases/my_db1/db1.test.linuxito.com.pid
mysql     1532  0.0 33.6 2224552 169052 ?      Sl   Sep10  54:05 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/databases/my_db1 --user=mysql --pid-file=/databases/my_db1/db1.test.linuxito.com.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock

Por ende, en tal directorio se encuentran los archivos de las tablas MyISAM:

[root@db1 ~]# ll /databases/my_db1/test_foro/ | grep usuarios
-rw-rw---- 1 mysql mysql     9882 Oct 21 08:57 usuarios_copia.frm
-rw-rw---- 1 mysql mysql   450384 Oct 21 08:57 usuarios_copia.MYD
-rw-rw---- 1 mysql mysql    83968 Oct 23 12:24 usuarios_copia.MYI
-rw-rw---- 1 mysql mysql     9882 Oct 21 08:57 usuarios_foro.frm
-rw-rw---- 1 mysql mysql     8950 Oct 21 08:57 usuarios.frm
-rw-rw---- 1 mysql mysql   651248 Oct 22 13:03 usuarios.MYD
-rw-rw---- 1 mysql mysql   111616 Oct 23 12:13 usuarios.MYI

Notar que la tabla "usuarios_foro" no posee los archivos MYD y MYI, ya que se trata de una tabla InnoDB.

Si no se ha especificado el directorio de datos como parámetro en la línea de comandos, MySQL utiliza el que se ha configurado en la variable datadir dentro del archivo de configuración del servidor (my.cnf).

Consultar al servidor qué archivos de configuración trata de leer, y en qué orden:

[root@db1 ~]# mysql --help --verbose | grep my.cnf
/etc/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf

Según la configuración actual del servidor MySQL, lee los archivos de configuración /etc/my.cnf, ~/.my.cnf y /usr/local/mysql/etc/my.cnf. Para saber si existe el archivo ~/.my.cnf hay que determinar con qué usuario se levanta el servicio mysqld (revisar la salida de ps aux) y cuál es su directorio $HOME (revisar el archivo /etc/passwd). Por ejemplo, si el servicio corre como "mysql" y el $HOME de "mysql" es /home/mysql, el archivo ~/.my.cnf será entonces /home/mysql/.my.cnf.

Revisar los archivos de configuración en orden hasta encontrar la primera aparición de la variable datadir (no debe estar comentada):

[root@db1 ~]# grep datadir /home/mysql/my.cnf
grep: /home/mysql/my.cnf: No such file or directory
[root@db1 ~]# grep datadir /etc/.my.cnf
grep: /etc/.my.cnf: No such file or directory
[root@db1 ~]# grep datadir /usr/local/mysql/etc/my.cnf
datadir         = /databases/my_db1

Se observa que se ha configurado /databases/my_db1 como directorio de datos (el mismo que se había pasado como parámetro).

Hay una forma más sencilla para determinar rápidamente cuál es el directorio de datos que utiliza un servidor MySQL. La cual consiste en utilizar la herramienta lsof. lsof es una herramienta para listar archivos abiertos. Recuerden que en los sistemas operativos de la familia UNIX todo es un archivo, así que la salida de este comando es abrumadora. Aunque simplemente filtrando la extensión de uno de los archivos de las tablas MyISAM, se puede determinar rápidamente cual es el directorio donde se guardan las tablas MyISAM:

[root@db ~]# lsof | grep ".MYD"
mysqld     1532   mysql   27u      REG              253,2   11989668     524460 /databases/my_db1/test_foro/sesion.MYD
mysqld     1532   mysql   32u      REG              253,2     651248     524463 /databases/my_db1/test_foro/usuarios.MYD

Pero para que esta técnica funcione el servidor debe tener abierto al menos un archivo .MYD.

Verificar y reparar tablas MyISAM utilizando myisamchk

Si conocemos o hemos determinado la ubicación de los archivos correspondientes a las tablas MyISAM, es posible verificar y reparar las mismas utilizando la herramienta de línea de comandos myisamchk.

Por ejemplo, para verificar la tabla "sesion" de la base de datos "test_foro". Primero es necesario cambiar al directorio donde se encuentran los archivos sesion.frm, sesion.MYD y sesion.MYI:

[root@db1 ~]# cd /databases/my_db1/test_foro/

Luego se verifica la tabla "sesion" ejecutando myisamchk -s --check sesion. Notar que no se debe utilizar ninguna extension, sólo el nombre de la tabla, ya que myisamchk buscará los archivos en el directorio actual:

[root@db1 test_foro]# myisamchk -s --check sesion
myisamchk: MyISAM file sesion
myisamchk: warning: Found row where the auto_increment column has the value 0

En este caso reporta una advertencia en una columna "auto_increment". Si no hubiese advertencias o errores, no retorna ninguna salida.

Para reparar una tabla ejecutar:

myisamchk --safe-recover [TABLA]

Esta herramienta permite desarrollar un script Bash para verificar y reparar todas las tablas automáticamente.

Para mayor información acerca de myisamchk, remitirse a su página de manual: man myisamchk.

Más sobre MyISAM

Salvo en contadas excepciones, por ejemplo en el área de data warehousing o en aplicaciones muy simples, no es recomendable utilizar tablas MyISAM. En los casos normales de uso, InnoDB ha probado ser más rápido, robusto y confiable que MyISAM. Por esta razón, a partir de la versión 5.5.5 ha pasado a ser el motor de almacenamiento por defecto utilizado por MySQL.

Lectura adicional:

MySQL 5.7 Reference Manual :: 15 Alternative Storage Engines :: 15.2 The MyISAM Storage Engine

MySQL 5.7 Reference Manual :: 4 MySQL Programs :: 4.6 MySQL Administrative and Utility Programs :: 4.6.3 myisamchk — MyISAM Table-Maintenance Utility

MySQL 5.7 Reference Manual :: 14 The InnoDB Storage Engine :: 14.1 Introduction to InnoDB


Tal vez pueda interesarte


Compartí este artículo