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

• 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 máximo 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.

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.

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