Motores de Motores de almacenamiento en almacenamiento en

MySQLMySQL

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

¿Qué es un motor de almacenamiento en ¿Qué es un motor de almacenamiento en un sistema gestor de bases de datos?un sistema gestor de bases de datos?

¿Por qué son importantes los motores de ¿Por qué son importantes los motores de almacenamiento?almacenamiento?

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

show engines;show engines;

Motores y diferenciasMotores y diferencias

Cuatro funcionalidades clave:Cuatro funcionalidades clave:

Tipos de datosTipos de datos

Bloqueo de datosBloqueo de datos

IndexadoIndexado

TransaccionesTransacciones

Y tenemos que hablar también de...Y tenemos que hablar también de...

ACIDACIDAAtomicity, tomicity, CConsistency, onsistency, IIsolation and solation and DDurability.urability.

AtomicidadAtomicidad

ConsistenciaConsistencia

AislamientoAislamiento

DurabilidadDurabilidad

Motores de almacenamientoMotores de almacenamiento

A continuación, una breve explicación de los diferentes A continuación, una breve explicación de los diferentes motores de almacenamiento de datos para MySql.motores de almacenamiento de datos para MySql.

MyISAMMyISAM

Se basa en el antiguo ISAM.Se basa en el antiguo ISAM. Motor que usa MySQL por defecto.Motor que usa MySQL por defecto. Límite de 2^32 registrosLímite de 2^32 registros Máximo de 64 índices por tablaMáximo de 64 índices por tabla Máximo de 16 columnas por índiceMáximo de 16 columnas por índice Los datos son independientes de la máquina y el sistema operativoLos datos son independientes de la máquina y el sistema operativo Permite campos índice como NULLPermite campos índice como NULL BLOB y TEXT pueden ser índicesBLOB y TEXT pueden ser índices Permite un gran tamaño en las tablas (hasta 256TB)Permite un gran tamaño en las tablas (hasta 256TB) No soporta transaccionesNo soporta transacciones Bloquea los datos a nivel de tablaBloquea los datos a nivel de tabla No permite “claves ajenas”No permite “claves ajenas” Rapidez de las operaciones de lectura.Rapidez de las operaciones de lectura.

Ejemplo de creación de una tabla:Ejemplo de creación de una tabla:

CREATE TABLE pruebaMyISAM (

codigo varchar(5) default NOT NULL,

descripcion varchar(255) default NULL,

PRIMARY KEY (codigo)

) ENGINE=MyISAM;

MERGEMERGE

Permite combinar varias tablasPermite combinar varias tablas Límite de 2^32 registrosLímite de 2^32 registros Las tablas “base” deben ser MyISAMLas tablas “base” deben ser MyISAM Bloqueo a nivel de tablaBloqueo a nivel de tabla No tiene índices, usa los de las tablas “base” (salvo No tiene índices, usa los de las tablas “base” (salvo

FULLTEXT)FULLTEXT) La lectura es más lenta al tener que ir consultando la La lectura es más lenta al tener que ir consultando la

clave en cada una de las tablas subyacentesclave en cada una de las tablas subyacentes No permite REPLACENo permite REPLACE No soporta transaccionesNo soporta transacciones En su creación no comprueba que las tablas que usa En su creación no comprueba que las tablas que usa

existan y tengan una estructura idénticaexistan y tengan una estructura idéntica

Ejemplo de creación de una tabla a partir de otras dos:

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20));CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20));INSERT INTO t1 (message) VALUES ('Testing'),('table'),

('t1');INSERT INTO t2 (message) VALUES ('Testing'),('table'),

('t2');

CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a))TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

MEMORY (HEAP)MEMORY (HEAP)

Almacena todos los datos en memoria.Almacena todos los datos en memoria. Únicamente guarda la estructura de las tablasÚnicamente guarda la estructura de las tablas Bloquea los datos a nivel de tablaBloquea los datos a nivel de tabla Puede usar índices HASHPuede usar índices HASH No soporta BLOB ni TEXTNo soporta BLOB ni TEXT No soporta transaccionesNo soporta transacciones Resulta extremadamente fácil perder los datosResulta extremadamente fácil perder los datos Útil para crear tablas temporales.Útil para crear tablas temporales.

FEDERATEDFEDERATED

Permite el acceso a una base de datos MySQL remota Permite el acceso a una base de datos MySQL remota como si fuese local.como si fuese local.

MySQL no instala este motor por defectoMySQL no instala este motor por defecto No soporta transaccionesNo soporta transacciones No contempla el bloqueo de datosNo contempla el bloqueo de datos No permite ALTERNo 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=FEDERATEDDEFAULT CHARSET=latin1COMMENT='mysql://root@remote_host:9306/federated/

test_table';

ARCHIVEARCHIVE

Almacenar muchos datos sin índices en poco espacio.Almacenar muchos datos sin índices en poco espacio. Gran compresión de los datos con zlibGran compresión de los datos con zlib Sólo permite INSERTS y SELECTSSólo permite INSERTS y SELECTS Bloquea los datos a nivel de registroBloquea los datos a nivel de registro Almacena los datos en un buffer hasta que los comprime Almacena los datos en un buffer hasta que los comprime

e insertae inserta No soporta transaccionesNo soporta transacciones

Útil para el almacenamiento de históricos o logs.Útil para el almacenamiento de históricos o logs.

CSVCSV

Almacena la información.Almacena la información. Cada tabla es un fichero que contiene los datos.Cada tabla es un fichero que contiene los datos. Permitir exportar los datos de forma que puedan ser Permitir exportar los datos de forma que puedan ser

importados fácilmente por algunas suites ofimáticas.importados fácilmente por algunas suites ofimáticas. No soporta indexación ni transaccionesNo soporta indexación ni transacciones

BLACKHOLEBLACKHOLE

Crear un log con la consulta SQL utilizada.Crear un log con la consulta SQL utilizada. No almacena ningún dato.No almacena ningún dato. No soporta índices, ni transacciones... No soporta índices, ni transacciones...

Su principal utilidad es mantener un servidor esclavo Su principal utilidad es mantener un servidor esclavo que mantenga un log del sistema principal.que mantenga un log del sistema principal.

NDBNDB

Motor de almacenamiento de los clúster de MySQLMotor de almacenamiento de los clúster de MySQL Proporciona alta disponibilidad mediante redundancia.Proporciona alta disponibilidad mediante redundancia. Proporciona alto rendimiento mediante fragmentación de Proporciona alto rendimiento mediante fragmentación de

datos sobre los grupos de nodos.datos sobre los grupos de nodos. Proporciona alta escalabilidad mediante la combinación Proporciona alta escalabilidad mediante la combinación

de las dosde las dos características anteriores.características anteriores. Los datos se guardan en memoria, pero los logs van a Los datos se guardan en memoria, pero los logs van a

disco.disco.

Útil cuando queremos un sistema veloz, con muchas Útil cuando queremos un sistema veloz, con muchas transacciones y con una gran redundancia.transacciones y con una gran redundancia.

BerkeleyDB (o BDB)BerkeleyDB (o BDB)

Provee altas prestaciones.Provee altas prestaciones. Almacenamiento basado en hash.Almacenamiento basado en hash. MySQL no lo instala por defectoMySQL no lo instala por defecto Máximo de 31 índices por tablaMáximo de 31 índices por tabla Máximo de 16 columnas por índiceMáximo de 16 columnas por índice Hasta 256TBHasta 256TB Sí soporta transaccionesSí soporta transacciones Usa índices HASHUsa índices HASH MySQL necesita una clave primaria por cada tabla BDB, en caso de MySQL necesita una clave primaria por cada tabla BDB, en caso de

no existir creará una ocultano existir creará una oculta El bloqueo interno de las tablas se hace a nivel de página (8192 El bloqueo interno de las tablas se hace a nivel de página (8192

bytes)bytes) Abrir muchas tablas es bastante lentoAbrir muchas tablas es bastante lento Cada tabla se almacena en la ruta de creación definida y no se Cada tabla se almacena en la ruta de creación definida y no se

puede cambiar de directorio salvo usando mysqldumppuede cambiar de directorio salvo usando mysqldump

InnoDBInnoDB

Características más importantes:Características más importantes:

Caché de índices en memoria --> muy alto rendimiento.Caché de índices en memoria --> muy alto rendimiento. ACID compliantACID compliant Claves ajenas y transacciones --> integridad referencialClaves ajenas y transacciones --> integridad referencial Bloqueo a nivel de registro, no bloquea la lectura Bloqueo a nivel de registro, no bloquea la lectura

durante los selects --> mejora la concurrenciadurante los selects --> mejora la concurrencia Recuperación de caídasRecuperación de caídas Tablas de hasta 1000 columnasTablas de hasta 1000 columnas Tamaño máximo por tabla de 64TBTamaño máximo por tabla de 64TB No mantiene un contador interno de registros --> No mantiene un contador interno de registros -->

select count(*) from tablaselect count(*) from tabla lento al tener recorrer todo el lento al tener recorrer todo el índiceíndice

Ejemplo de uso de transacciones:

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))

-> ENGINE=InnoDB;Query OK, 0 rows affected (0.00 sec)mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');Query OK, 1 row affected (0.00 sec)mysql> COMMIT;Query OK, 0 rows affected (0.00 sec)mysql> SET AUTOCOMMIT=0;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO CUSTOMER VALUES (15, 'John');Query OK, 1 row affected (0.00 sec)mysql> ROLLBACK;Query OK, 0 rows affected (0.00 sec)

+------+--------+| A | B |+------+--------+| 10 | Heikki |+------+--------+1 row in set (0.00 sec)

mysql> SELECT * FROM CUSTOMER;

Resultado:Resultado:

FALCONFALCON

Futuro motor de almacenamiento para MySQL 6.0.Futuro motor de almacenamiento para MySQL 6.0. Diseñado para entornos de servidores Web de alto Diseñado para entornos de servidores Web de alto

volumen.volumen. Cumple ACIDCumple ACID Permite recuperación de estados / datos en caso de Permite recuperación de estados / datos en caso de

caídascaídas Permite una alta concurrencia (bloqueo a nivel de Permite una alta concurrencia (bloqueo a nivel de

registro)registro) Caché de datos muy rápida y potenteCaché de datos muy rápida y potente Incluye tablas para la monitorización de rendimiento y Incluye tablas para la monitorización de rendimiento y

erroreserrores Permite una configuración simplePermite una configuración simple

ReferenciasReferencias

http://www.linuxplanet.com/linuxplanet/tutorials/6034/1/http://www.linuxplanet.com/linuxplanet/tutorials/6034/1/ http://bicosyes.com/motores-de-almacenamiento-de-mysql/http://bicosyes.com/motores-de-almacenamiento-de-mysql/ http://jayant7k.blogspot.com/2006/06/mysql-storage-engines.htmlhttp://jayant7k.blogspot.com/2006/06/mysql-storage-engines.html http://articles.techrepublic.com.com/5100-22-1058872.htmlhttp://articles.techrepublic.com.com/5100-22-1058872.html http://en.wikipedia.org/wiki/InnoDBhttp://en.wikipedia.org/wiki/InnoDB http://en.wikipedia.org/wiki/MyISAMhttp://en.wikipedia.org/wiki/MyISAM http://dev.mysql.com/doc/refman/5.0/es/memory-storage-http://dev.mysql.com/doc/refman/5.0/es/memory-storage-

engine.htmlengine.html http://dev.mysql.com/doc/refman/5.0/es/myisam-storage-http://dev.mysql.com/doc/refman/5.0/es/myisam-storage-

engine.htmlengine.html http://en.wikipedia.org/wiki/MySQL_Federatedhttp://en.wikipedia.org/wiki/MySQL_Federated http://jmonne.blogspot.com/2007/07/innodb.htmlhttp://jmonne.blogspot.com/2007/07/innodb.html http://lampologia.blogspot.com/2007/09/instalacin-de-mysql-60-http://lampologia.blogspot.com/2007/09/instalacin-de-mysql-60-

confalcon.htmlconfalcon.html http://dev.mysql.com/doc/refman/5.0/es/storage-engines.htmlhttp://dev.mysql.com/doc/refman/5.0/es/storage-engines.html http://www.mysql-hispano.org/page.php?id=45&pag=6http://www.mysql-hispano.org/page.php?id=45&pag=6 http://www.mysql.com/mysql60/top-reasons.htmlhttp://www.mysql.com/mysql60/top-reasons.html