Mysql Motores de bases de datos

Introducción

• Lo primero que tendremos que preguntarnos leyendo el título de este punto es ¿ qué es un motor de bases de datos ?

• Resumiendo podemos decir que un motor determina la forma física en cómo los datos son guardados en disco y además cada motor ofrece una serie de ventajas relativas a los tipos de datos que pueden guardar, tipos de índices, velocidad de acceso y soporte de diferentes características como puede ser el uso de transacciones, integridad referencial,...

• En la imagen siguiente podemos ver en la capa más cercana al almacenamiento físico de los datos los diferentes motores (Imagen obtenida de Universidad De Gerona - Administracion De Bases De Datos Mysql 5):

• MYISAM Storage Engine
• MEMORY Storage Engine
• CSV Storage Engine
• ARCHIVE Storage Engine
• BLACKHOLE Storage Engine
• MERGE Storage Engine
• FEDERATED Storage Engine
• EXAMPLE Storage Engine
• INNODB Storage Engine
• NDB Storage Engine

• A partir de la versión Mysql 5.7 el motor por defecto cuando se crean las tablas es INNODB.
• Además la base de datos Mysql deja de ser MyIsam y pasa a ser Innodb.

• Más información:

• Motores diferentes a INNODB y NDB
• Motor INNODB.
• Motor NDB.

Órdenes SQL relacionadas con el manejo de los motores

• Para ver cuales son los motores que puede soportar el servidor Mysql (algunos pueden estar desactivados o no compilados), disponemos de la orden SQL: SHOW ENGINES;.

Podemos ver cual es la salida de ejecutar esta orden:

En esta primera gráfica ya podemos advertir como el motor INNODB posee una serie de funcionalidades que no tienen los demás. Las analizaremos cuando comentemos dicho motor.

• Para crear una tabla con un motor determinado tendremos que ejecutar la orden SQL CREATE TABLE de la forma: CREATE TABLE nombre_tabla (col1 tipo1, col2 tipo2,....coln tipon) ENGINE=MYISAM;

Por defecto, a partir de la versión 5.7 el motor por defecto es INNODB. Anteriormente era MYISAM.

• Para modificar el motor de una tabla tenemos que ejecutar la orden SQL ALTER TABLE: ALTER TABLE nombre_tabla ENGINE = MYISAM;

• Para ver cual es el motor de una tabla tenemos que ejecutar la orden SQL SHOW TABLE STATUS: SHOW TABLE STATUS WHERE Name = 'nombre_tabla';

Variables del sistema relacionadas con los motores de bases de datos

• default-storage-engine: Determina el motor de bases de datos por defecto.
• disabled_storage_engines: Determina que motores de bases de datos no pueden ser utilizados.
• default_tmp_storage_engine: Determina el motor de bases de datos por defecto para tablas temporales.

Características a tener en cuenta para elegir el motor adecuado

• Cada uno de los motores tienen características, algunas de ellas son comunes y otras son específicas de cada tipo de motor.
• Dependiendo de la base de datos a guardar, los tipos de datos, seguridad,.... deberíamos elegir el motor que mejor se adapte a nuestras necesidades.

• Como norma general, la mayor parte de las ocasiones, elegiremos el motor INNODB (motor por defecto a partir de la versión 5.7), pero de todas formas, indicará alguno de los factores principales (no todos) que podríamos tener en cuenta:

• Tamaño máximo de la tabla: Dependerá de los tipos de registros y del número de ellos.
• Capacidad de almacenamiento en filas (registros), es decir, ¿ cuántas filas es capaz de guardar una tabla ?
• Número de índices que podemos guardar en una tabla. Veremos en profundidad el uso de índices en la Unidad 5. Por ahora señalaremos que un índice se aplica sobre una o varias columnas de una tabla, y esto lleva consigo que podamos acceder directamente al lugar 'físico' donde se encuentra la fila en base al valor ordenado de la columna/s que conforman el índice.
• Tipos de datos sobre los que podemos crear índices.
• Tipo de bloqueo (más información en este enlace):

• Un bloqueo de tabla significa que nadie puede acceder a la tabla (puede ser bloque de lectura, de escritura o ambos) mientras dure el bloque.
• Un bloqueo de fila, significa que nadie puede acceder a la fila (puede ser bloque de lectura, de escritura o ambos) mientras dure el bloque.

• Soporta Integridad referencial.
• Soporta transacciones.

Motor MyISAM

• Características:

Imagen obtenida de http://dev.mysql.com

• Físicamente cada tabla se guarda en 3 ficheros:

• nombre_tabla.frm: Guarda la definición del formato de la tabla.
• nombre_tabla.myd (MyData): Guarda los datos de la tabla.
• nombre_tabla.myi (MyIndex): Guarda los índices que tenga creada la tabla.

• Orden SQL para crear una tabla con este motor:

1 CREATE TABLE nombre_tabla (nombre_col tipo_dato) ENGINE = MYISAM;

• Podéis leer todas características en este enlace.

• Características principales:

• Tamaño máximo de la tabla: 256TB.
• Capacidad de almacenamiento en filas (registros): (2^32)^2 (1.844E+19)
• Número máximo de índices que podemos guardar en una tabla: 64
• Número máximo de columnas por índice: 16

• Tipos de datos sobre los que podemos crear índices: BLOB, TEXT, y columnas con valor NULL.
• Bloqueo a nivel de tabla cuando hay múltiples operaciones de modificación sobre la tabla.
• No soporta integridad referencial.
• No soporta transacciones.

Motor InnoDB

• Características:

Imagen obtenida de http://dev.mysql.com

• Físicamente:

• Anteriormente a la versión 5.6.6 de Mysql, todas los datos e índices de las tablas Innodb se guardaban en un conjunto de archivos de nombre ibdata1, ibdata2, dentro del directorio de datos de Mysql.

Esta forma tiene como principal inconveniente la recuperación del espacio físico por parte de tablas que hubieran crecido mucho de tamaño y que después fueran borradas.

Una vez ocupado el espacio, ya no se reduce.

La forma de solucionarlo era la de hacer un backup de las bases de datos, borrar los archivos ibd y volver a recuperarlas.

• A partir de la versión 5.7 está activada la variable del sistema innodb_file_per_table que provoca que cada tabla ocupe un archivo con extensión nombre_tabla.ibd.

Existe un archivo con extensión .frm que guarda la estructura de la tabla.

• Orden SQL para crear una tabla con este motor:

1 CREATE TABLE nombre_tabla (nombre_col tipo_dato) ENGINE = INNODB;

• Podéis leer todas características en este enlace.

• Características principales:

• Tamaño máximo de la tabla: 256TB para un tamaño de página de 64KB. Por defecto, el tamaño de página es de 16KB para un almacenamiento máximo de 64TB. El tamaño de la página viene determinado por la variable del sistema innodb_page_size.
• Capacidad de almacenamiento en filas (registros): Dependerá del tamaño de la fila y este depende del tamaño de la página. En el caso de un tamaño de página de 16KB el tamaño máximo por fila es de 8KB, por lo tanto el número máximo de filas sería: 64TB / 8000b = 8796093022,21 (sin contar los campos BLOB y TEXT).
• Número de columnas: 1017
• Número máximo de índices que podemos guardar en una tabla: 64
• Número máximo de columnas por índice: 16
• El acceso a los datos utilizando la clave primaria es muy rápido (utiliza un clustered index.

• Hacer un count(*) sobre una tabla le lleva más tiempo que en el motor anterior ya que no guarda un contador interno de filas.
• Tipos de datos sobre los que podemos crear índices: BLOB, TEXT, y columnas con valor NULL.
• Bloqueo a nivel de fila de datos (permite mayor concurrencia)
• Soporta integridad referencial.
• Soporta transacciones.

• Más información en este enlace.

MyIsam vs InnoDB

• Estos son los dos principales motores de almacenamiento y normalmente vais a tener que escoger uno u otro.

• Si los comparamos podemos llegar a las siguientes conclusiones:

• A nivel de velocidad, a priori, el motor MyIsam es más rápido ya que no tiene reglas de integrad referencial y no soporta transacciones, que hacen que las operaciones contra las tablas sean más lentas.
• En contra, MyIsam tiene un bloqueo a nivel de tabla, por lo que si vamos a realizar operaciones que impliquen modificaciones sobre los datos, las consultas se van a ralentizar.
• InnoDB por otro lado posee mecanismos para implementar la integridad referencial y transacciones que pueden ser necesarios en nuestra base de datos, ya que le da una mayor seguridad sobre los datos.
• En caso de caída del sistema, Innodb posee mecanismos de recuperación para volver a dejar a la base de datos en el estado anterior a la caída del sistema, mientras que el motor MyIsam no.

• Ventajas de InnoDB:

• Velocidad: Innodb sigue siendo más lento que MyIsam en la inserción de datos pero la diferencia cada vez es menor.
• Concurrencia: Cuando tenemos operaciones de INSERT y SELECT al mismo tiempo (de forma concurrente) al tener InnoDB un bloqueo por registro, su velocidad es mayor.
• Fiabilidad: InnoDB soporta transacciones e integridad referencial.
• Seguridad de los datos: Capacidad de recuperarse de caídas del sistema.

• Ventajas de MyISAM:

• Simplicidad: Las opciones de configuración para este motor son mínimas.
• Optimización: Es el motor que más tiempo lleva siendo utilizado por Mysql, por lo que está mas optimizado. Las aplicaciones web que en su mayor parte hacen consulta, suelen usar este motor.

Anteriormente, sólo el motor MyIsam permitía realizar búsquedas de varias palabras en cadenas de texto (denominadas búsquedas FULLTEXT) pero ahora InnoBD también las soporta, por lo que ha dejado de ser una ventaja de MyIsam.

• Uso de recursos: En general consumen menos CPU y menos espacio en disco.

Motor Memory

Imagen obtenida de http://dev.mysql.com

• Características principales:

• Tamaño máximo de la tabla: Capacidad de la RAM y por la variable del sistema max_heap_table_size que por defecto tiene un tamaño de 16MB.
• Los campos varchar son almacenados con un tamaño fijo.
• No soporta campos TEXT o BLOB.
• Bloquea los datos a nivel de tabla.

• El tamaño máximo de la clave primaria es de 3072 bytes.
• Número máximo de índices que podemos guardar en una tabla: 64
• Número máximo de columnas por índice: 16
• No soporta integridad referencial.
• No soporta transacciones.

• En caso de reiniciar el servidor, el contenido de las tablas se pierde, no así la tabla con su estructura.
• Normalmente se utilizan como tablas temporales, o cuando tenemos un conjunto pequeño de datos a los que hagamos consultas de forma continua.

Nota: Anteriormente este motor tenía el nombre de HEAP.

Motor CSV

• Los archivos CSV son archivos de texto formado por múltiples filas, en el que cada dato (columna a nivel de tabla) está separado por una coma.

• Físicamente:

• Archivo con extensión nombre_tabla.frm donde guarda la estructura de la tabla.
• Archivo con exntesión nombre_tabla.csv donde guarda los datos en formato CSV.
• Archivo con exntesión nombre_tabla.csm donde guarda metadatos como el número de filas guardadas en el fichero o el estado de la tabla.

• Orden SQL para crear una tabla con este motor:

1 CREATE TABLE nombre_tabla (nombre_col tipo_dato) ENGINE = CSV;

• Podéis leer todas características en este enlace.

• Con este motor, podríais importar-exportar archivos CSV a una base de datos Mysql.

Por ejemplo, para crear un archivo CSV a partir de los datos de una tabla (podría ser una consulta):

1 CREATE TABLE tabla_CSV 
2 SELECT * FROM tabla_MYSQL;
3 
4 ALTER TABLE tabla_CSV ENGINE=CSV;

Para traer datos de un archivo CSV a una base de datos:

• Partimos de:

• Disponemos de una tabla con la estructura adecuada para albergar los datos CSV, de nombre tabla_CSV, con el motor CSV, por lo tanto físicamente tendrá un archivo de nombre 'tabla_CSV.CSV'
• Disponemos de un archivo CSV el cual queremos 'llevar' a la tabla anterior. El nombre del archivo es 'datos.CSV'

Las órdenes a ejecutar son, desde consola:

1 mv datos.CSV tabla_CSV.CSV

Desde Mysql (orden SQL):

1 flush table tabla_CSV.CSV;

Otros motores

• El motor de almacenamiento EXAMPLE es un motor de almacenamiento que no hace nada.

Puedes crear tablas con este motor, pero no puede almacenar datos ni recuperarlos. El objetivo es que sirva como ejemplo en el código MySQL para ilustrar cómo escribir un motor de almacenamiento.

• El motor NDB Cluster es el motor de almacenamiento usado por MySQL Cluster para implementar tablas que se particionan en varias máquinas.

Características más importantes:

• Proporciona alta disponibilidad mediante redundancia.
• Proporciona alto rendimiento mediante fragmentación de datos sobre los grupos de nodos.
• Proporciona alta escalabilidad mediante la combinación de las dos características anteriores.
• Los datos se guardan en memoria, pero los logs van a disco.

• El motor de almacenamiento ARCHIVE se usa para guardar grandes cantidades de datos sin índices.Los datos son comprimidos/descomprimidos cuando se guardan/recuperan de las tablas. Normalmente se utiliza cuando se necesita guardar datos antiguos que ocupan mucha cantidad de espacio y que casi nunca se van a necesitar.

Puede dar problemas con las claves autonuméricas como se indica en este post.

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

• El motor de almacenamiento FEDERATED se añadió en MySQL 5.0.3. Este motor guarda datos en una base de datos remota.

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

• El motor de almacenamiento BLACKHOLE hacer que todo lo que se guarde en tablas que utilizan dicho motor desaparezca.

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

Ejercicios propuestos

• Crea una tabla y comprueba que motor de base de datos tiene.

• Muestra la variable del sistema que determina el motor de base de datos por defecto.

Cambia el valor de la variable para que sea MyIsam.

Crea una tabla sin indicar el motor de almacenamiento y comprueba que es MyIsam.

Deja el valor de la variable a su valor por defecto.

• Crea un archivo CSV desde el Excell u CALC con al menos 3 columnas y 5 filas y tráelo a una tabla de nombre 'datos_csv' en Mysql.

• Crea una tabla que tenga el motor BlackHole y comprueba como no se guardan los datos.

Enlace a la página principal de la UD1

Enlace a la página principal del curso

-- Ángel D. Fernández González -- (2017).