Procedimientos y Funciones Almacenadas en MySQL: Optimización y Mejores Prácticas

Rutinas: Procedimientos y Funciones

Las rutinas (procedimientos y funciones) almacenadas son un conjunto de comandos que pueden almacenarse en el servidor. Una función almacenada es un programa almacenado que devuelve un valor. Los procedimientos devuelven valores a través de parámetros OUT e INOUT. Las funciones devuelven a través de cero o un único valor de retorno.

• DELIMITER: Indica el carácter de comienzo y fin del programa. Se utiliza $$.
• DROP PROCEDURE IF EXISTS hola_mundo$$: Elimina el procedimiento si existe.
• CREATE PROCEDURE test.hola_mundo(): Comienzo de la definición de un procedimiento, nombre seguido de paréntesis para poner parámetros.
• BEGIN: Indica el comienzo de una serie de bloques de sentencias SQL que componen el cuerpo del procedimiento.
• SELECT: Imprime la cadena por pantalla.
• END$$: Fin.

Parámetros y Variables

• DECLARE: Crea una nueva variable con su nombre y tipo (CHAR, VARCHAR...).
• SET: Permite asignar valores a las variables usando el operador de igualdad.
• Tipos de parámetros:
  • IN: Es el valor por defecto y sirve para incluir parámetros de entrada que usará el procedimiento.
  • OUT: Parámetro de salida. El procedimiento puede asignar valores a dichos parámetros que son devueltos en la llamada.
  • INOUT: Permite pasar valores al programa que serán modificados y devueltos en la llamada.

Estructuras de Control

• CASE: Cuando hay muchas condiciones. Ejemplo: CASE variable1 WHEN 0 THEN INSERT INTO t VALUES(17); END CASE;
• Bucles:
  • LOOP: Ejemplo: bucle: LOOP SELECT 'esto no se acaba'; END LOOP bucle;
• Cursor:
  • DECLARE cursor_name CURSOR FOR select_statement: Declara un cursor.
  • OPEN: Inicializa el conjunto de resultados asociados con el cursor.
  • FETCH: Extrae la siguiente fila de valores del conjunto de resultados del cursor, moviendo el puntero una posición.
  • CLOSE: Cierra el cursor, liberando la memoria que ocupa.

Triggers (Disparadores)

Un trigger es un disparador que se activa cuando hay un INSERT, DELETE o UPDATE.

Sintaxis: CREATE TRIGGER nombre_disparador momento_disparador evento_disparador ON nombre_tabla FOR EACH ROW sentencia_disparador

• Momento: Puede ser BEFORE o AFTER.
• Evento: Puede ser INSERT, UPDATE o DELETE.
• No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y evento. No se puede tener dos disparadores BEFORE UPDATE.
• Se puede tener BEFORE UPDATE y BEFORE INSERT o BEFORE UPDATE y AFTER UPDATE.
• OLD y NEW permiten acceder a columnas en los registros afectados por un disparador.
• Eliminación de triggers: DROP TRIGGER IF EXISTS schema_name.trigger_name;
• Consulta de triggers: SHOW TRIGGERS

Vistas

Las vistas son objetos de la base de datos que, mediante una consulta, incluyen un subconjunto de datos de la base. Es como una consulta guardada. Podemos borrar y actualizar los datos de una vista de forma que queden reflejados en los correspondientes campos de las tablas subyacentes.

Existen restricciones:

• No se pueden usar tablas temporales.
• No usar cláusulas GROUP BY ni HAVING.
• No usar uniones ni reuniones externas.
• No usar consultas correlacionadas.
• Para el caso de INNER JOIN, podemos actualizar o insertar siempre y cuando los campos afectados sean únicamente los de las tablas implicadas en el JOIN.

Las vistas pueden crearse a partir de varios tipos de sentencias SELECT. Algunas vistas son actualizables.

• CREATE VIEW: Para creación.
• ALTER VIEW: Para modificación.
• DROP VIEW: Eliminación.
• Consulta: SHOW CREATE VIEW.

Índices en MySQL: Tipos y Optimización para Mejorar el Rendimiento

Un índice es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla. Se suelen crear sobre campos con frecuentes búsquedas.

Ejemplo: ALTER TABLE movimientos ADD INDEX (codigo_cuenta)

Tipos de Índices

• Parciales: Ahorran espacio a costa de disminuir el rendimiento. Ejemplo: ALTER TABLE posts ADD INDEX(pcontenido(120))
• Multicolumna: Es la posibilidad de crear índices sobre más de una columna. Ejemplo: ALTER TABLE post ADD INDEX (pcontenido(120)), pfecha)
• Secundarios y Clúster: InnoDB utiliza índices clúster. En ellos se guardan las claves primarias junto con los registros ordenados respecto a la clave primaria, así las búsquedas por clave primaria son muy rápidas.
• Estructura de un índice:
  • B-Tree: Está formado por un conjunto de nodos, cada uno de los cuales contiene valores de índice ordenados que apuntan a registros de disco. Son recomendables en consultas basadas en rangos de datos con cláusulas BETWEEN y operadores de comparación.
  • Hash: Son más rápidos que B-Tree, pero menos predecibles y flexibles, ya que tienen colisiones y lentitud en consultas basadas en rangos de valores.
  • R-Tree: Son usados para datos de tipo espacial, como coordenadas de un objeto.

Índices en MySQL

• UNIQUE: Para campos que no se repiten en la tabla.
• PRIMARY: Son índices sobre los campos que forman parte de la clave primaria de una tabla.
• FULLTEXT: Uno o varios campos de texto y utilizados para la búsqueda de palabras dentro de un campo en funciones de búsqueda de cadenas.
• SPATIAL: Índices usados para los datos de tipos espaciales, como LINE o CURVE.

Índices en MyISAM, BEAP y BDB

• MyISAM: Usan índices tipo B-Tree.
• BEAP: Para trabajos masivos y se almacenan completamente en memoria. Pueden usar también B-Tree y Hash.
• BDB: Los índices se guardan junto a los datos de las tablas y estos solo pueden ser de tipo B-Tree.

Optimización en MySQL

• Usar los tipos de datos menores posible siempre que se ajusten a nuestros requisitos.
• Siempre que sea posible, utilizaremos NOT NULL en la definición de nuestros campos.
• Usar COMPACT o COMPRESSED para la opción ROW_FORMAT cuando creamos tablas InnoDB.
• Para MyISAM, indicar ROW_FORMAT FIXED.
• El índice de la clave primaria debe ser lo más pequeño posible para acelerar la búsqueda de datos en consultas.
• Debemos crear solo los índices adecuados según nuestras necesidades.
• Crear índices conjuntos en lugar de uno por cada campo.