1. Introducción

En este artículo se estudiaremos la implementación de las más importantes mejoras que un desarrollador puede encontrar en el uso de MySQL 5 respecto a la versión 4.

Trataremos el concepto de vistas (VIEW) , como consultas almacenadas sobre las cuales luego podremos realizar consultas como si fuesen tablas de registros; y también de stored programs , que abarca desde desencadenadores (TRIGGER, que son como procedimientos que se asocian a un evento en las tablas) hasta funciones almacenadas y procedimientos almacenados.

Veremos no sólo la utilidad de cada objeto, sino también el contexto en el que se deben usar y sus peculiaridades. Y como información adicional tendréis también un resumen de los statements que podéis usar en el cuerpo de los stored programas (declaraciones, bloques, cursores, bucles...) así como un ejemplo en el que se usa un poco de todas esas estructuras.

Vistas

Se definen para facilitar y restringir el acceso a los datos de las tablas a usuarios limitados.

La estructura es estática y se hace en tiempo de creación: los futuros campos / cambios sobre las tablas no se reflejan en la vista.

Entonces si una vista tiene un SELECT FROM tabla* y después de definirla añadimos un campo a la tabla original, no lo veremos en la vista.

Si después cambiamos la definición de algún campo de la tabla original entonces se generará un error/warning al utilizarla pues seguramente tendrá que truncar valores: los campos eliminados los pasará a NULL, y un DECIMAL cambiado a INT tendrá que truncarlo. En estos casos hay que redefinir la vista porque se volverá muy lenta al consultarla, aunque use algoritmo MERGE.

Su uso es igual que una tabla,

 SELECT * FROM myview, 

pudiendo usarse en operaciones JOIN, subconsultas y cualquier otro lugar donde una tabla es válida. La única excepción es que excepto SELECT, sólo admiten INSERT, UPDATE o DELETE sobre ellas y en circunstancias especiales.

Para que una vista admita transacciones (UPDATE, DELETE e INSERT) debe cumplir ciertos requisitos. Cito los más habituales:

• Su algoritmo debe ser MERGE explícitamente o UNDEFINED pero que el sistema pueda usar MERGE a la hora de realizar la transacción.

• La relación columnas de la vista --> columnas de la tabla usada en el FROM debe ser 1 --> 1. Por lo tanto no podemos intentar lanzar un INSERT sobre una VIEW que tiene columnas que no se corresponden con la tabla. Así mismo, no puede haber columnas duplicadas; y en el caso de INSERT, debe contener todas las columnas que no tengan un DEFAULT value.

Permisos (GRANT)

CREATE VIEW (para hacer CREATE, DROP y ALTER VIEW) y SHOW VIEW (para ejecutar SHOW VIEW, que muestra el detalle de la vista). Para usarlas en el FROM de un SELECT hace falta tener GRANT SELECT sobre la vista, o SELECT * sobre la BD de la vista.

INFORMATION_SCHEMA

Para obtener toda la información de todas las vistas:

SELECT * FROM INFORMATION_SCHEMA.VIEWS

Definición

CREATE
 [OR REPLACE]
 [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  DEFINER = { user | CURRENT_USER }]
  [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
 AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

El ALTER VIEW se realiza de forma idéntica.

ALGORITHM

MERGE: se traduce la definición de la vista con su equivalente tabla/tablas sin uso de tablas temporales.

No puede ser MERGE si hay DISTINCT, GROUP BY, funciones de agregado, subconsultas en el SELECT...

La manera más sencilla de comprenderlo es dividiendo la consulta en partes.

• La parte del SELECT se puede añadir siempre pero sin funciones de agregado (MAX(), MIN(), AVG()...) porque implicaría no poder usar GROUP BY al consultar la vista. Las subconsultas se podrían añadir en el MERGE, lo que pasa es que una subconsulta en el SELECT implica SIEMPRE que se use una tabla temporal, y por lo tanto no se puede hacer MERGE.

• La parte FROM admite de todo menos subconsultas, que no se permiten ahí en las vistas (aún así, crearían tabla temporal).

• La parte WHERE sólo puede tener condiciones o nada, pero no GROUP BY, HAVING... esto es porque si luego hacemos un GROUP BY sobre la vista no se podría añadir otro GROUP BY.

Un ejemplo de la traducción que hace el sistema en un MERGE:

CREATE ALGORITHM MERGE VIEW myview c1, c2 
AS SELECT campo1, campo2 
  FROM tabla WHERE idt > 2000; 
SELECT c1, NOW() 
    FROM myview 
    WHERE c2 IS NOT NULL 
     GROUP BY c2; 
 SELECT campo1, NOW() 
     FROM tabla 
    WHERE (idt > 2000) AND (c2 IS NOT NULL) 
      GROUP BY c2;

Se observa que el sistema puede solapar la vista "myview" con "tabla" sin ningún problema. Además podemos hacer un GROUP BY sobre la vista manteniéndose el MERGE. Otra cosa sería que la consulta hacia una vista implique la creación de una tabla temporal

SELECT *, (subquery) FROM myview;

En este caso el sistema seguiría haciendo el MERGE pero luego tendría que crear una tabla temporal por la subconsulta del SELECT. Es una regla que se aplica a cualquier SELECT.

TEMPTABLE : se crea una tabla temporal al consultar la vista. Si se fuerza a TEMPTABLE, siempre se creará la temporal aunque se pudiera usar MERGE.

UNDEFINED (por defecto) : el sistema selecciona MERGE y si no puede, TEMPTABLE. Si conocemos el algoritmo que se va a usar es mejor indicarlo explícitamente (sobre todo si es TEMPTABLE, para ahorrar que compruebe si puede hacer MERGE).

DEFINER

Los privilegios se comprueban sobre el usuario indicado, que puede no existir (se produciría un warning aunque permitiría crear la vista). Hace falta privilegio SUPER para indicar un usuario distinto a CURRENT_USER.

SQL SECURITY

Los privilegios requeridos para la ejecución de la vista deben ser cumplidos por el indicado. Si la vista tiene SELECT * FROM tabla el sistema comprobará si DEFINER o INVOKER (según se indique) tiene permiso para acceder a "tabla".

CHECK OPTION

No permite hacer INSERT o UPDATE (cuando la definición lo permite) si no se cumplen las condiciones del WHERE de la definición de la vista.

Es decir, si el UPDATE o INSERT que vamos a realizar ocasiona que el registro actualizado/insertado no apareciese al consultar la vista, deniega la operación mostrando un error. Evidentemente esto sólo se aplica a INSERT/UPDATE sobre la vista, no sobre la tabla de la definición de la vista.

CASCADED (por defecto) extiende la restricción a las vistas que se usen en la definición de la vista, y LOCAL sólo a la vista en definición.

Observaciones

• En la parte FROM del SELECT de la definición se pueden usar tantas tablas como queramos.
• Comparten el namespace con las tablas, de modo que no pueden tener el mismo nombre que una tabla ya existente en esa BD.
• column_list es una manera agrupada de usar alias para las columnas de la vista, y debe coincidir en número con las columnas del SELECT.
• Se puede usar ORDER BY en la definición, y también en el uso de la vista.
• No puede haber una subconsultas en el FROM.
• No se puede hacer referencia a variables del usuario o sistema (tampoco a variables dentro de un stored program).
• El SELECT debe ser válido completamente, aunque sólo se comprueban los nombres de columnas, tablas, referencias... si se usa una Stored Function (SF) que no es válida no se mostrará un error al definirla, pero sí al usarla.
• No se puede asociar un TRIGGER a una vista (lógicamente).
• Una vista sólo puede ser actualizable y permitir inserciones cuando la relación de sus columnas y la tabla seleccionada es de 1 a 1. Por lo tanto, el algoritmo debe ser MERGE o UNDEFINED y que el sistema pueda realizar el MERGE.
• R/SP/SF se generará un error de permiso denegado.
• Cuidado con los TRIGGERs, pues eso derivará en que la tabla asociada no pueda efectuar nunca el tipo de transacción.
• Tanto en las vistas como en los TRIGGER, SP y SF usar un DEFINER que no existe derivará en error al usarlos. Del mismo modo, si se borra el usuario o se le quitan los privilegios necesarios para la ejecución de los statements que tiene la VIEW/TRIGGER.

Rendimiento

El rendimiento es prácticamente igual que el de una consulta normal, teniendo en cuenta que si forzamos TEMPTABLE cuando se podría usar MERGE, entonces el resultado será peor.

Sobre esto cabe destacar que el uso de tablas temporales no tiene por qué ser más lento, de hecho el optimizador de mysql las usa cuando ve que es la mejor opción. Pero claro, el consumo de memoria será mayor en estos casos, aparte de que tiene que volcar los datos a la memoria, usarlos y luego liberar esa memoria.

Siempre se puede hacer un EXPLAIN sobre el statement de la vista para comprobar cómo resuelve la consulta el servidor. Ahí podremos ver si hay un fallo en el diseño de la consulta o es lenta ya de por sí (en la doc de mysql hay mucha información del uso de EXPLAIN para optimización). Si la consulta es buena y al usar la vista tarda en devolver resultados, seguramente sea la consulta sobre la vista lo que debas optimizar (usa EXPLAIN también para ello).