Optimización y Gestión Avanzada de Bases de Datos MySQL

Diferencias entre Procedimientos y Funciones

Las funciones solo devuelven un valor como máximo. Por otro lado, un trigger es una función que se ejecuta automáticamente cuando se produce un cambio en una tabla.

Gestión de Variables y Rutinas

• Variable de entrada/salida: Es aquella que entra y sale de la rutina.
• Acceso al código de una rutina: ¿Cómo accedemos al código de una rutina? Estas se guardan compiladas en el servidor.
• Variables de sesión: Desaparecen automáticamente al cerrar la sesión.

Estructuras y Control de Datos

Las vistas permiten acceder a partes específicas de una tabla, facilitando la seguridad y la organización. En el contexto de MySQL, una transacción debe incluir obligatoriamente un commit y un rollback para asegurar la integridad de los datos.

Triggers y Eventos

La palabra reservada OLD se utiliza en triggers, especialmente para gestionar inserciones. Un evento ocurre a una hora dada o programada. Cabe destacar que un trigger de tipo BEFORE no tiene sentido en operaciones de borrado.

Índices de una Base de Datos

Un índice de una base de datos es una estructura de datos que mejora significativamente la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla. Ejemplo de creación: ALTER TABLE movimientos ADD INDEX (codigo_cuenta);

Tipos de Índices

• Parciales: Ocupan aproximadamente la mitad del tamaño.
• Multivolumna: Indexan varios campos simultáneamente.
• Secundarios y Clúster: En el motor InnoDB, la clave primaria y los registros están ordenados respecto a las claves primarias.

Optimización del Diseño de Bases de Datos

Para mejorar el rendimiento, se recomiendan las siguientes prácticas:

• Usar MEDIUMINT en lugar de INT (utilizar tipos de datos menores siempre que sea posible).
• Definir campos como NOT NULL para facilitar la creación de índices.
• Utilizar COMPACT o COMPRESSED para el ROW_FORMAT en InnoDB.
• En MyISAM, indicar ROW_FORMAT FIXED en la creación de tablas; aunque los datos ocupan un tamaño fijo y pueden desperdiciar espacio, esto aumenta la velocidad.
• Mantener el índice de la clave primaria lo más pequeño posible.
• Para búsquedas en varios campos, crear un índice conjunto.
• En tipos no binarios, usar la opción BINARY para comparar byte por byte.

Procesamiento y Optimización de Consultas

El proceso de una consulta incluye las etapas de parseo, análisis y optimización. El comando EXPLAIN permite detectar cuándo un índice se usa o no, si se usa incorrectamente o ver si las consultas se ejecutan de forma óptima. Ejemplo: EXPLAIN EXTENDED SELECT * FROM wp_links WHERE link_id=24\G

Mantenimiento de Tablas

Existen diversos comandos para asegurar la salud de la base de datos:

• ANALYZE TABLE: Analiza y almacena la distribución de claves de una tabla (disponible para MyISAM e InnoDB).
• OPTIMIZE TABLE: Utilizado en MyISAM, InnoDB y ARCHIVE tras realizar una gran cantidad de borrados o modificaciones en tablas con tipos variables (VARCHAR, VARBINARY, BLOB o TEXT). Reutiliza el espacio y desfragmenta la tabla.
• CHECK TABLE: Revisa tablas y vistas de tipo MyISAM, InnoDB y ARCHIVE buscando errores.
• EXPLAIN: Permite saber cómo se va a ejecutar cierta consulta; también funciona como sinónimo de DESCRIBE.
• REPAIR: Repara tablas MyISAM corruptas (similar al programa myisamchk).
• CHECKSUM: Devuelve la suma de verificación de una tabla para verificar su integridad.
• SHOW: Conjunto de comandos para mostrar información detallada de los objetos del servidor.