¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. MySQL 5.7
  3. Otras funcionalidades
Extrait - MySQL 5.7 Administración y optimización
Extractos del libro
MySQL 5.7 Administración y optimización Volver a la página de compra del libro

Otras funcionalidades

Particionado

1. Interés y limitaciones

El particionado horizontal permite dividir una tabla en función de los datos que contiene, de forma transparente para el usuario. Este proceso nos puede permitir una gestión más eficaz de los datos almacenados agrupándolos en diferentes lugares (particiones) según determinados criterios. El particionado de una tabla se define en su creación, indicando el tipo y la clave de las particiones, así como otros parámetros, como el número de particiones que se van a generar.

Particionado manual: podemos particionar de forma manual una tabla MyISAM utilizando el motor MERGE. Sin embargo, este motor solo es compatible con el motor MyISAM, y no es tan transparente como el particionado y requiere en todos los casos escanear todas las tablas para la lectura de los registros. Este tipo de particionado casi ha desaparecido hoy desde que InnoDB se convirtió en el motor de almacenamiento más corriente.

a. Gestión del incremento de carga

Cuanto más solicitada sea una tabla, mayor el riesgo de sufrir problemas de rendimiento. El particionado puede ayudar a reducir la contención de una tabla distribuyéndola en el conjunto de las particiones, gracias al uso de «partition pruning» (implementado a partir de MySQL 5.6.6). Por ejemplo, durante la ejecución de una petición SELECT sobre una tabla con el motor de almacenamiento MyISAM, solo las particiones necesarias serán bloqueadas en lectura en lugar de la tabla entera, como es el caso con versiones anteriores de MySQL 5.6.6.

El particionado también puede permitir aislar los registros a los que se accede de forma ocasional. Esto permite disminuir el tamaño de los índices a los que se accederá con mayor frecuencia y, como efecto, disminuir también el tamaño de la memoria necesaria para su almacenamiento en caché. Por ejemplo, para un índice de tipo b-tree, esto permite disminuir el número de hojas, así como la profundidad, lo que produce una aceleración de las operaciones de búsqueda, inserción y borrado. Además, los datos relacionados con el índice deben tener una mayor probabilidad de encontrarse en memoria, lo que tiene como consecuencia reducir la cantidad de entradas/salidas que se han de realizar y con ello mejorar...

Rutinas almacenadas

1. Rol

Las rutinas almacenadas son programas (procedimientos o funciones) creados por el usuario, precompilados y almacenados en el servidor MySQL. Permiten mover una parte de la lógica de negocio de una aplicación del cliente al servidor. El cliente no tiene la necesidad de volver a introducir todo el comando, sino solo hacer una simple referencia a la rutina.

Las rutinas almacenadas pueden tener varios usos:

  • Mejorar la seguridad: los programas cliente ya no acceden de forma directa a las tablas. En una API de este tipo, todas las operaciones de gestión de datos se realizan a través de rutinas almacenadas, lo que limita los privilegios de ejecución, sin dar acceso a las tablas que albergan la información.

  • Centralizar las peticiones: distintas aplicaciones (que pueden utilizar lenguajes de programación diferentes) pueden acceder a los mismos datos y tener las mismas funcionalidades, lo que permite incluir el código SQL común e implica una disminución de la redundancia y mayor facilidad de mantenimiento del código.

  • Aumentar el rendimiento: los comandos no tienen que analizarse varias veces y mucho menos la información enviada por la red, lo que permite limitar el tráfico y solicitar solo el servidor MySQL para determinadas operaciones.

El lenguaje para programar una rutina es bastante rudimentario, pero es posible utilizar:

  • Las peticiones SQL (INSERT, UPDATE, CREATE...).

  • Las variables definidas utilizando las palabras clave DECLARE y SET.

  • Los operadores (=, AND, LIKE...) y las funciones nativas como el SQL (CEIL, CONCAT, DAYOFWEEK...).

  • Las funciones de control (IF, CASE, REPEAT, LOOP...).

  • Los cursores que permiten recorrer las filas de salida de una petición SQL para realizar bucles de proceso.

Durante la creación de un programa almacenado (procedimiento, función, trigger...), es imprescindible modificar el delimitador, es decir, el símbolo que indica al intérprete que el comando ha terminado y que lo puede interpretar. Por defecto, el delimitador de MySQL es el carácter punto y coma «;», el mismo que se utiliza en el programa almacenado para definir el final de cada instrucción. En caso de que el delimitador no se cambie, la creación del programa almacenado fracasará en el primer punto y coma.


mysql> CREATE PROCEDURE `PS_verif_login`( IN p_mail varchar(50), ...

Disparadores (triggers)

1. Rol

Los disparadores o triggers son objetos cuyo objetivo es ejecutar el código en respuesta a un evento que ocurre en una tabla. Los eventos pueden ser de tres tipos: INSERT, UPDATE o DELETE (o similares, por ejemplo: la instrucción REPLACE equivalente a INSERT, o INSERT y DELETE). La orden de activación se define antes (BEFORE) o después (AFTER) del evento. Por ejemplo, el DBA seleccionará BEFORE para un test de comprobación de datos y AFTER para el registro.

2. Sintaxis

La sintaxis que se debe observar durante la creación de un trigger es:


CREATE 
[DEFINER = { user | CURRENT_USER }] 
TRIGGER nombre_del_disparador momento_de_activación 
acción_ejecutada 
ON nombre_de_tabla FOR EACH ROW cuerpo_del_disparador
 

Con momento_de_activación que vale BEFORE o AFTER y acción_ejecutada que toma los valores INSERT, UPDATE o DELETE.

En el cuerpo del disparador, los alias OLD y NEW están disponibles en función del contexto (INSERT/UPATE/DELETE). Permiten acceder, de forma respectiva, al valor de una columna de la tabla que contiene el disparador, antes y después de la modificación:

  • Durante un UPDATE, OLD.Columna hace referencia al valor de la columna antes de la modificación, mientras que NEW.Columna, al valor después de su modificación.

  • Durante un INSERT, solo existe NEW.Columna.

  • Durante un DELETE, solo existe OLD.Columna.

Ejemplo de un disparador que protege los datos borrados de la tabla City:


CREATE TABLE City (   
  id int(11) NOT NULL DEFAULT '0',   
  name char(35) NOT NULL DEFAULT ''   
) ENGINE=InnoDB   
   
CREATE...

Eventos

1. Rol

El planificador de eventos o «event scheduler» ofrece la posibilidad al administrador de bases de datos de iniciar la ejecución de programas almacenados directamente en el servidor MySQL. Este planificador de tareas interno (CRON-like) permite automatizar de forma sencilla las tareas a intervalos regulares, o a una hora fija, sin necesidad de configurar el sistema operativo, que alberga la base de datos. Para poder utilizarlo, debemos, en primer lugar, activarlo, ya que no es el caso por defecto:


mysql> SHOW VARIABLES LIKE 'event_scheduler';   
+-----------------+-------+   
| Variable_name   | Value |   
+-----------------+-------+   
| event_scheduler | OFF   |    
+-----------------+-------+   
   
mysql> SET GLOBAL event_scheduler = 1;   
   
mysql> SHOW VARIABLES LIKE 'event_scheduler';   
+-----------------+-------+   
| Variable_name   | Value |   
+-----------------+-------+   
| event_scheduler | ON    |    
+-----------------+-------+ 
 

Una vez activado, MySQL inicia un proceso ligero (thread) en segundo plano. Este proceso se encarga de ejecutar los eventos cuando llega el momento. Puede comprobarlo utilizando el comando SHOW PROCESSLIST, para lo que debemos contar con el privilegio SUPER; en caso contrario, solo veremos los procesos que nos están asociados.


mysql> SHOW PROCESSLIST\G   
*************************** 1. row ***************************  
     Id: 12    
   User: event_scheduler    
   Host: localhost    
     db: NULL    
Command: Daemon    
   Time: 58    
  State: Waiting on empty queue    
  ...

Vistas

1. Rol

Las vistas son tablas virtuales creadas a partir de una petición SELECT. No almacenan los datos que generan, sino solo la petición que permite crearlas. La petición SELECT que genera la vista hace referencia a una o varias tablas. La vista puede ser, por ejemplo, un join entre diferentes tablas, la agregación o la extracción de algunas columnas de una tabla. También puede crearse a partir de otra vista.

Las vistas son por lo general de solo lectura y solo permiten leer los datos. Sin embargo, MySQL permite la creación de vistas modificables en ciertas condiciones:

  • La petición que genera la vista debe permitir a MySQL localizar la traza del registro que se va a modificar en la tabla o las tablas subyacentes, así como el de todos los valores de cada columna. La petición SELECT que crea la vista no debe contener la cláusula DISTINCT, GROUP BY, HAVING ni otras funciones de agregación.

  • La cláusula ALGORITHM no debe ser de valor TEMPTABLE. Luego volveremos sobre este tema.

  • La petición no debe acceder a vistas subyacentes no modificables.

Las vistas pueden utilizarse por diferentes razones; estas permiten:

  • Comprobar la integridad al restringir el acceso a los datos para mejorar la privacidad con un particionado vertical y/u horizontal para ocultar los campos a los usuarios. Esto permite personalizar la visualización de información según el tipo de usuario.

  • Ocultar la complejidad del esquema. La independencia lógica de los datos es útil para dar a los usuarios el acceso a un conjunto de relaciones representadas en la forma de una tabla. Los datos de la vista son entonces campos de distintas tablas agrupadas, o los resultados de operaciones en estos campos.

  • Modificar de forma automática los datos seleccionados (sum(), avg(), max()...). Esto permite manipular los valores calculados a partir de otros valores del esquema.

  • Conservar la estructura de una tabla si debe modificarse. El esquema puede modificarse sin que sea necesario cambiar las peticiones del lado de la aplicación.

Ya que en general una vista sirve para ocultar la complejidad, no ayuda a mejorar el rendimiento. A menudo lo que ocurre es lo contrario: los desarrolladores pueden olvidar con facilidad que los registros de una vista son a veces difíciles de generar y que, por tanto, es preferible utilizarla lo menos posible.

Para...

Columnas generadas

1. Introducción

Las columnas generadas aparecieron con MySQL 5.7. La idea es muy simple: se trata de columnas cuyos valores son derivados de una o varias columnas de la misma tabla. Las columnas generadas pueden indexarse, lo que permite añadir índices funcionales para, por ejemplo:

  • Indexar cadenas de caracteres de derecha a izquierda en lugar de izquierda a derecha.

  • Indexar solo la fecha para una columna que contenga fecha y hora.

  • Indexar la suma de dos columnas.

MySQL ofrece dos tipos de columnas generadas: las columnas virtuales, cuyos valores se calculan sobre la marcha y nunca se almacenan en disco, y las columnas persistentes, que son columnas convencionales almacenadas en disco pero cuyo valor se calculará a partir de otras columnas.

MariaDB también dispone de columnas generadas, pero la implementación es diferente. Por lo tanto, la sintaxis también es diferente y algunas funciones disponibles con MySQL 5.7 no están disponibles con MariaDB.

2. Columnas virtuales

Imaginemos que tenemos una tabla t con una columna que almacena un apellido. Para añadir a nuestra tabla una columna virtual que contenga el nombre de familia al revés, utilizaremos la siguiente sintaxis:


mysql> ALTER TABLE t ADD nombre_inverso VARCHAR(50) GENERATED ALWAYS 
AS REVERSE(nom) VIRTUAL;
 

Observe que, aunque la adición de una columna virtual se hiciera empleando el comando ALTER...

Soporte JSON

1. El tipo de datos JSON

Un nuevo tipo de datos hizo su aparición con MySQL 5.7: el tipo JSON. Era perfectamente posible almacenar en JSON en las versiones anteriores con columnas de tipo TEXT, pero el nuevo tipo de datos presenta varias ventajas:

  • Durante su inserción, los documentos JSON se validan para asegurarse de que su sintaxis es correcta.

  • El almacenamiento utiliza un formato binario adaptado a los documentos JSON, el uso del disco es menos pesado.

  • Muchas funciones especiales permiten acceder a los elementos de un documento JSON.

  • Los elementos de un documento JSON pueden indexarse mediante columnas generadas (más detalles a continuación).

Este nuevo tipo de datos marca el primer paso de una gran evolución de MySQL: en el futuro, MySQL será no solo capaz de interactuar con los datos a través de peticiones SQL, sino también a través de otro protocolo de comunicación inspirado por los sistemas NoSQL. En el momento de escribir este libro, Oracle ha publicado un nuevo plugin llamado Document Store que permite usar MySQL como un verdadero sistema NoSQL y que proporciona un resumen de las funcionalidades que estarán con toda probabilidad disponibles con MySQL 5.8.

He aquí un ejemplo de creación de una tabla con un campo de tipo JSON. Puede ver que la inserción de un document JSON no válido falla:


mysql> CREATE TABLE personas (details JSON); ...