Utilizando funcionalidades avanzadas MySQL

¡Bienvenidos un día más a un nuevo rootie queridos amigos! Aquí estamos un día mas con vosotros para amenizar el día (¡o la noche!). Este es el primer post tras el largo, largo verano (mas largo incluso que en Poniente) y vamos a ver algo que se quedo pendiente, hace meses: funcionalidades avanzadas MySQL.

Consultas mysql, recapitulando.

En el este rootie de hace unos meses, hice una pequeña introducción a (my)SQL. En el hablamos sobre las operaciones más básicas, las consultas para crear bases de datos y tablas y las consultas para extraer, modificar, insertar o eliminar información, hoy veremos operaciones avanzadas mysql.

consultas-mysql-dba-a-tope
DBA a tope en un día cualquiera.

Con todo lo que vimos en el anterior post, la verdad es que da para hacer bastante, pero no es suficiente cuando queremos crear estructuras de datos mas complejas. Es por ello que SQL cuenta con muchas mas funcionalidades, que nos van a permitir conseguir nuestros objetivos.

Por cierto, todos ejemplos están escritos en mySQL usando InnoDB como motor ya que algunas funcionalidades podrían no funcionar en MyISAM, ¡avisados quedáis!

Funcionalidades avanzadas MySQL

Indices.

Las bases de datos SQL utilizan indices para agilizar los procesos de encontrar información. Poniéndonos técnicos, en SQL en general, cuando creamos indices, esta información se almacena en un árbol-b (o b-tree en inglés).

Esta es un tipo de estructura de datos que mantiene la información ordenada y permite búsquedas, lo que hace que cuando necesitamos información específica, mas que hacer que el RBDS tenga que leer una a una las entradas de una tabla (si así es como funciona si no hay indices) usando el árbol-b simplemente ha de buscar en el árbol y extraer la información.

b-tree-sql
Estructura de un árbol b

Creando indices

Crear un indice en una tabla es muy sencillo, tomemos como ejemplo la siguiente consulta para crear una tabla:

Como podemos observar estamos creando una tabla socios, que contiene cuatro campos:

  • id
  • nombre
  • dni
  • activo

Imaginemos que queremos optimizar nuestras búsquedas basadas en DNI, para ello lo mejor es crear un indice utilizando la operación ADD KEY  o CREATE INDEX, pero ademas podemos crear un indice usando la restricción UNIQUE, lo que hará imposible que un dato sensible como el DNI se duplique.

Para ello tenemos dos opciones podemos modificar la tabla o agregar el indice en la creación de la siguiente forma:

Para eliminar indices podemos utilizar:

Indices compuestos.

Otro punto a tener en cuenta es que un indice puede tener mas de un campo.

whaaat-varios-campos-indice
¿Cómooooo? ¿Varios campos en el indice?

Efectivamente, podemos agregar dos, tres o virtualmente, los campos que queramos. Tened en cuenta que a mas campos indexados, menos eficacia.

El poder hacer esto ademas nos trae una serie de ventajas a la hora de imponer controles en la integridad de los datos. En el ejemplo anterior, hemos modificado la tabla para impedir duplicados de DNI, y no es mala idea, pero si quisiéramos imponer algo mas restrictivo, podríamos hacer que la combinación única fuera nombre-dni.

Para ello bastaría con hacer lo siguiente:

Integridad referencial

Ahora que hemos visto los indices, hablemos sobre integridad referencial como preludio a las siguientes operaciones mysql.

Se puede afirmar que la integridad referencial es la seguridad que una vez que una tabla empieza a hacer referencia un registro en una tabla diferente, este último debe existir. Todo esto se deriva de la teoría de bases de datos que vimos en el anterior rootie.

Una vez que empezamos a convertir nuestro diagrama Entidad-Relación y derivados en una base de datos, tenemos que tener una forma de asegurar la integridad referencial que existe sobre el papel.

Restricciones y claves foráneas (Constraints & Foreign Keys)

Para conseguir la deseada integridad referencial usaremos las restricciones de clave foránea (FK, pa’ los amigos).

Una clave foránea, no es más que la propagación de una clave primaria de una tabla a otra. La tabla que contiene la FK suele ser considerada la tabla hija.

Para crear restricciones de clave foránea podemos utilizar varios métodos, podemos crear la tabla padre y la tabla hija y modificarlas o podemos ir agregando las claves en la creación, ¡pero ojo! si haces esto, asegúrate que la tabla padre se crea siempre primero.

Podéis observar que tanto en la creación como en la modificación de la tabla he utilizado ON DELETE CASCADE. Esto es porque específicamente las acciones referencíales es lo que mantienen la integridad referencial. Cuando tenemos dos tablas relacionadas, y la información en la tabla padre cambia, el RDBMS tiene que tener las instrucciones, de como actuar con las relaciones.

Acciones referencíales.

Cuando realizamos acciones de actualización o eliminación sobre la base de datos es, normalmente, cuando la integridad referencial puede perderse, es por ello que podemos definir el comportamiento hacia la información.

Para ellos contamos con las acciones referencíales:

  • ON UPDATE
  • ON DELETE

Estas acciones como su nombre indican, solo se evalúan si la información cambia o se elimina. Respecto a que podemos definir, podemos usar

  • CASCADE – propaga la acción eliminando o actualizando la información.
  • SET NULL – elimina el valor de la clave foránea y lo sustituye por NULL.
  • RESTRICT – Rechaza la operación de actualización o eliminación. Este es el funcionamiento por defecto en InnoDB, si no se define nada.

Esta serie de acciones y comportamientos nos dan las herramientas para mantener la integridad siempre intacta, por eso es siempre bueno cuando se están diseñando bases de datos, pararse a pensar e invertir tiempo en decidir como se van a configurar las FK.

Y con esto nos despedimos por hoy gente! Espero que como siempre el post os sirva de ayuda y os haya gustado.

¡Nos vemos en el próximo rootie!

Como siempre para cualquier duda, pregunta, comentario, insulto, soborno o amenaza, podéis usar los comentarios o en nuestras redes sociales Facebook y Twitter.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *