PDF de programación - Motores de almacenamiento en MySQL 5.0

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

Motores de almacenamiento en MySQL 5.0

por Mario López y Juan A. Sánchez

15 de enero de 2008

¿Qué es un motor de almacenamiento en un sistema gestor de

bases de datos?

Un motor de almacenamiento es una parte esencial de un SGDB puesto
que se encarga de crear, recuperar, actualizar y borrar los datos de una base
de datos1.

¿Por qué son importantes los motores de almacenamiento?

Los datos en MySQL pueden ser almacenados de diversas formas, con
distintas técnicas que nos aportan distintas funcionalidades. Aquí es donde
encontramos la importancia de los motores de almacenamiento, ya que,
dependiendo del motor que elijamos obtendremos distintas ventajas y
dependerá de nosotros saber sacarles partido de forma acorde a nuestras
necesidades.

MySQL nos provee por defecto de una serie de motores que podemos
usar fácilmente (a parte de otros desarrollados por terceras partes que
también podemos instalar), de hecho esta flexibilidad es una de las bazas que
han jugado a favor de la popularidad de MySQL.

¿Cómo saber que motores están disponibles?

Es muy sencillo, basta con escribir en una consola MySQL la siguiente

consulta:

show engines;

Y obtendremos algo como esto:

1 http://en.wikipedia.org/wiki/Storage_engine

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

Motores y diferencias

Las comparativas entre motores se suelen hacer basándose en cuatro

funcionalidades clave:

Tipos de datos: aunque la mayoría son comunes hay algunos

específicos que pueden ser decisivos bajo determinadas circunstancias.

Bloqueo de datos: la forma en la que el motor protege un dato que
está siendo modificado para evitar problemas de acceso concurrente a los
datos y mantener la integridad referencial.

Indexado:

las diferentes técnicas de indexado pueden influir

drásticamente es el rendimiento de una base de datos.

Transacciones: dota de fiabilidad a los datos mientras se realizan
operaciones, te permite utilizar los datos pero sólo te permite guardarlos
cuando se comprueba que las otras condiciones que pudiesen requerirse se
han cumplido.

Y tenemos que hablar también de...

ACID2

Se denomina ACID a la propiedad de una base de datos para realizar
transacciones seguras. Así pues ACID compliant define a un sistema de gestión
de bases de datos que puede realizar transacciones seguras.

En concreto ACID es un acrónimo de Atomicity, Consistency, Isolation
and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en español.

2 http://es.wikipedia.org/wiki/ACID

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

● Atomicidad: es la propiedad que asegura que la operación se ha
realizado o no, y por lo tanto ante un fallo del sistema no puede quedar a
medias. Si una operación consiste en una serie de pasos, u ocurren todos
o ninguno

● Consistencia: es la propiedad que asegura que sólo se empieza aquello
que se puede acabar. Por lo tanto se ejecutan aquellas operaciones que
no van a romper la reglas y directrices de integridad de la base de datos.
● Aislamiento: es la propiedad que asegura que una operación no puede
afectar a otras. Esto asegura que la realización de dos transacciones
sobre la misma información nunca generará ningún tipo de error.

● Durabilidad: es la propiedad que asegura que una vez realizada la
operación, ésta persistirá y no se podrá deshacer aunque falle el sistema.

Motores de almacenamiento

MyISAM

Se basa en el antiguo ISAM, al que añade muchas mejoras, es el motor
que usa MySQL por defecto. Es una buena combinación entre funcionalidad y
rendimiento aunque carece de algunas características interesantes.

Características más importantes:

● Límite de 2^32 registros
● Máximo de 64 índices por tabla
● Máximo de 16 columnas por índice
● Los datos son independientes de la máquina y el sistema operativo
● Permite campos índice como NULL
● BLOB3 y TEXT pueden ser índices
● Permite un gran tamaño en las tablas (hasta 256TB)
● No soporta transacciones
● Bloquea los datos a nivel de tabla
● No permite “claves ajenas”

Este motor pone especial empeño en la rapidez de las operaciones de
lectura (predominio de SELECT), es una de las razones por las que MySQL es
tan popular en la web, ya que la mayoría de las operaciones que se realizan
son de este tipo. Que no tenga que hacer comprobaciones de integridad
referencial también influye en su velocidad.

Ejemplo de creación de una tabla:

3 binary large object (generalmente son imágenes, archivos de sonido y otros objetos

multimedia)

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

CREATE TABLE pruebaMyISAM (

codigo varchar(5) default NOT NULL,
descripcion varchar(255) default NULL,
PRIMARY KEY (codigo)

) ENGINE=MyISAM;

En general no hará falta indicar el uso de este motor pues es el que se

usa por defecto.

MERGE

Permite combinar varias tablas de igual estructura en una única tabla,
pudiendo así realizar consultas sobre una tabla que nos devuelve datos de
varias.

Características más importantes:

● Límite de 2^32 registros
● Las tablas “base” deben ser MyISAM
● Bloqueo a nivel de tabla
● No tiene índices, usa los de las tablas “base” (salvo FULLTEXT)
● La lectura es más lenta al tener que ir consultando la clave en cada una

de las tablas subyacentes

● No permite REPLACE
● No soporta transacciones
● En su creación no comprueba que las tablas que usa existan y tengan

una estructura idéntica

Una de sus funcionalidades puede ser partir una tabla muy grande en
otras más pequeñas y, al unirlas con MERGE, permitirnos trabajar con ellas
como si fuesen una sola.

Ejemplo de creación de una tabla a partir de otras dos:
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

MEMORY (HEAP)

Guarda todos los datos en memoria, de forma que si se cae el servidor o
reiniciamos MySQL se pierden los datos, aunque la estructura de las tablas se
guarda.

Características más importantes:

● Bloquea los datos a nivel de tabla
● Puede usar índices HASH
● No soporta BLOB ni TEXT
● No soporta transacciones
● Resulta extremadamente fácil perder los datos

Resultan útiles como tablas temporales para determinadas consultas ya
que al estar en memoria y poder tener Hash como índice resultan
extremadamente rápidas, es una buena elección cuando necesitamos realizar
operaciones muy rápidas sobre conjuntos pequeños de datos.

FEDERATED

Este motor se incluyó en la versión 5.03. La novedad de este motor es
que permite el acceso a una base de datos MySQL remota como si fuese local,
en realidad tenemos una tabla local que representa a otra remota, ambas
deben ser idénticas.

Características más importantes:

● Permite acceso a BBDD remotas
● MySQL no instala este motor por defecto
● No soporta transacciones
● No contempla el bloqueo de datos
● No permite ALTER

Ejemplo de tabla en la que se le indica la dirección de los datos:
CREATE TABLE federated_table (
id int(20) NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
other int(20) NOT NULL default '0',
PRIMARY KEY (id),
KEY name (name),
KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

ARCHIVE

Se utiliza básicamente para almacenar grandes cantidades de datos sin
índices en muy poco espacio, ya que los comprime con zlib alcanzando un nivel
de ahorro de espacio considerable.

Características más importantes:

● Gran compresión de los datos
● Sólo permite INSERTS y SELECTS
● Bloquea los datos a nivel de registro
● Almacena los datos en un buffer hasta que los comprime e inserta
● No soporta transacciones

Este motor resulta especialmente útil para el almacenamiento de
históricos o logs ya que suelen ocupar gran cantidad de espacio y no es
necesario modificarlos con posterioridad.

CSV

Almacena la información utilizando el formato de valores separados por
comas (comma-separated values), de forma que cada tabla es un fichero que
contiene los datos. No soporta indexado y su fin principal es permitir exportar
las datos de forma que puedan ser importados fácilmente por algunas suites
ofimáticas.

Características más importantes:

● Útil para exportar e importar datos
● No soporta indexación ni transacciones

BLACKHOLE

El sorprendente uso de este motor es no almacenar los datos sino crear
un log con la consulta SQL utilizada. Como no almacena ningún dato
lógicamente no soporta índices, ni transacciones...

Su principal utilidad es mantener un servidor esclavo que mantenga un

log del sistema principal.

NDB

Es el motor de almacenamiento de los clúster de MySQL, las bases de

datos se reparten por los diferentes nodos de un clúster.

Motores de almacenamiento en MySQL 5.0

Mario López / Juan A. Sánchez

Características más importantes:

● Proporciona alta disponibilidad mediante redundancia.
● Proporciona alto rendimiento mediante fragmentac