Cuando diseñamos una tabla en MySQL (y en general en cualquier otro SGBD) solemos prestar mucha más importancia a las claves primarias que a las foráneas. Sin embargo las claves foráneas son también muy importantes principalmente por un doble motivo. Por un lado permiten mantener la integridad de la base de datos ante posibles modificaciones o eliminación de datos. Por otro lado aceleran enormemente determinadas combinaciones (joins) de tablas. No obstante, si estamos utilizando MySQL este segundo tema no depende estrictamente de las claves foráneas sino de la unicidad de las claves. Son temas parecidos pero no iguales, ya que para que una campo pueda ser referenciado por la clave foránea de otra tabla debe cumplir el criterio de unicidad, pero que lo cumpla no obliga a que alguna clave foránea tenga que referenciarlo. De esto es de lo que va a tratar este post.
Como el tema va a ser algo complicado de explicar sin unas tablas de datos a las que sujetarnos, voy a partir de tres tablas con las que desarrollaré toda la explicación. Estas tres tablas son las que paso a explicar a continuación.
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| mat_ped | varchar(6) | NO | PRI | | |
| mat_pos | smallint(5) unsigned | NO | PRI | 0 | |
| mat_typ | varchar(6) | YES | | NULL | |
| mat_fin | varchar(2) | YES | | NULL | |
| mat_qty | double unsigned | YES | | NULL | |
+---------+----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Es una tabla que recoge todos los materiales que se han vendido en algún momento a lo largo de la trayectoria de una empresa. Los campos mat_ped y mat_pos son la clave primaria e identifican el número de pedido de venta y la posición dentro de dicho pedido. No afectarán en nada a nuestro desarrollo, así que podemos olvidarnos de ellos. En cambio los restantes campos serán importantes: mat_typ indica el tipo de material, mat_fin indica el acabado de ese material y mat_qty indica el número de kilos vendidos en ese pedido y posición.
Quedará más claro viendo algunos datos que puede contener esta tabla:
+---------+---------+---------+---------+---------+
| mat_ped | mat_pos | mat_typ | mat_fin | mat_qty |
+---------+---------+---------+---------+---------+
| 100023 | 1 | 430 | XP | 800 |
| A09085 | 1 | 430 | BA | 3000 |
| A09086 | 1 | 430 | BA | 7380 |
| A09138 | 1 | 409 | 2B | 538 |
| A09139 | 1 | 409 | 2B | 539 |
+---------+---------+---------+---------+---------+
5 rows in set (0.00 sec)
Lo he limitado a 5 resultados pero en realidad en esta tabla es donde estará el enorme volumen de datos, ya que la tabla entera contendrá…
+----------+
| COUNT(*) |
+----------+
| 177047 |
+----------+
1 row in set (0.06 sec)
El objetivo final que querremos conseguir (para lo cual necesitaremos llevar a cabo una consulta) es una agrupación de esos 177.047 materiales vendidos. Para ello agruparemos tanto los tipos de material como los acabados a través de unas tablas de parametrización en las que le diremos qué tipo de material o qué acabado agrupado corresponden a cada tipo de material o acabado original. Estas dos tablas son las que ahora vemos. Primero la de tipos de material.
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
Es una tabla extremadamente simple donde sólo consta el tipo de material básico (el original, el que aparece en los pedidos) y el tipo de material agrupado que le corresponde a cada tipo de material básico.
Algunos datos de los que consta son los siguientes:
+---------+---------+
| typ_bas | typ_agg |
+---------+---------+
| 14462 | 524 |
| 202 | 202 |
| 301PK | 524 |
| 304 | 304 |
| 304/L | 304 |
+---------+---------+
5 rows in set (0.00 sec)
Así, los materiales 14462 y 301PK quedarán agrupados como material 524. Los 304 y 304/L quedarán como 304. Y así para un total de unos 70 materiales.
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
También es una tabla extremadamente simple donde sólo consta el acabado básico (el original, el que aparece en los pedidos) y el acabado agrupado que le corresponde a cada acabado básico.
Algunos datos de los que consta son los siguientes:
+---------+---------+
| fin_bas | fin_agg |
+---------+---------+
| 1 | 1 |
| 1D | 1 |
| 1P | K3 |
| 2B | 2B |
| 2C | 2B |
+---------+---------+
5 rows in set (0.00 sec)
Así, los acabados 1 y 1D quedarán agrupados como acabado 1. El 2B y el 2C quedarán agrupados como acabado 2B. Y así para un total de 94 acabados.
Tenemos pues las tres tablas presentadas. Vamos a llevar a cabo una primera ejecución de la consulta de combinación y agrupación que necesitaremos para obtener el resultado deseado y evaluaremos cuánto tiempo ha tardado MySQL en devolvernos el resultado:
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (0.95 sec)
No está nada mal. En apenas un segundo ha hecho los correspondientes joins y las agregaciones de los 177.047 registros para devolvernos lo que deseábamos obtener. Entonces… ¿si estoy defendiendo la importancia de las claves foráneas y sobretodo de la unicidad, no es un contrasentido haber obtenido unos resultados tan buenos? No, no lo es. Tiene una explicación. Fijémonos que estamos haciendo las combinaciones con los cambos typ_bas y fin_bas que son claves principales en sus respectivas tablas y que por tanto son campos únicos en ellas. Dicho de otro modo, MySQL no necesita estrictamente que existan claves foráneas (en este caso serían mat_typ y mat_fin) referenciando a estas claves primarias para ejecutar la consulta rápidamente. Lo que sí necesita es que exista ese unicidad.
Bien, vamos a complicarlo y se entenderá mejor -espero-. Imaginemos que no estamos trabajando con datos de una sola empresa sino que formamos parte de un pequeño grupo de empresas que comparten base de datos. Así, en las tablas de parametrización de tipos de material y acabados, la clave principal no es únicamente el tipo de material y el acabado, sino que incorporamos también el código de la empresa en cuestión, ya que el material 304/L en una empresa se agrupará como 304 mientras que en otra se agrupará como 524 (es un decir).
De este modo las dos tablas quedarán ahora así.
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_cpy | varchar(3) | NO | PRI | | |
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Con unos datos como estos (AMG es el código de una de las empresas, concretamente de la que nos interesará, ya que pondremos absolutamente todos los datos con este código de empresa):
+---------+---------+---------+
| typ_cpy | typ_bas | typ_agg |
+---------+---------+---------+
| AMG | 14462 | 524 |
| AMG | 202 | 202 |
| AMG | 301PK | 524 |
| AMG | 304 | 304 |
| AMG | 304/L | 304 |
+---------+---------+---------+
5 rows in set (0.00 sec)
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_cpy | varchar(3) | NO | PRI | | |
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Con unos datos como estos:
+---------+---------+---------+
| fin_cpy | fin_bas | fin_agg |
+---------+---------+---------+
| AMG | 1 | 1 |
| AMG | 1D | 1 |
| AMG | 1P | K3 |
| AMG | 2B | 2B |
| AMG | 2C | 2B |
+---------+---------+---------+
5 rows in set (0.00 sec)
Tenemos pues de nuevo las tres tablas presentadas, sólo que ahora las dos últimas han sufrido un pequeño cambio, aunque aparentemente no demasiado importante puesto que sólo hemos añadido un campo adicional, pero no hemos tocado los que ya había y siguen siendo claves primarias los que lo eran anteriormente. Vamos a llevar a cabo la segunda ejecución de la consulta de combinación y agrupación y evaluaremos cuánto tiempo ha tardado esta vez MySQL en devolvernos el resultado:
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (21.02 sec)
Nada menos que 21 segundos. Una barbaridad. De acuerdo que tiene que combinar y agrupar 177.047 registros, pero tampoco son tantos… ¿qué pasará cuando sean millones? El problema que nos ha aparecido es ni más ni menos que la unicidad. Antes los campos typ_bas y fin_bas que se utilizan para los joins eran únicos. Ahora ya no lo son. Vamos a intentar solucionarlo.
Para ello ejecutaremos las siguientes sentencias:
-> CHANGE typ_bas typ_bas VARCHAR(6) UNIQUE;
Query OK, 70 rows affected (0.03 sec)
Records: 70 Duplicates: 0 Warnings: 0
mysql> DESCRIBE typ_alltypesx;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| typ_cpy | varchar(3) | NO | PRI | | |
| typ_bas | varchar(6) | NO | PRI | | |
| typ_agg | varchar(6) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE fin_allfinish
-> CHANGE fin_bas fin_bas VARCHAR(2) UNIQUE;
Query OK, 94 rows affected (0.03 sec)
Records: 94 Duplicates: 0 Warnings: 0
mysql> DESCRIBE fin_allfinish;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| fin_cpy | varchar(3) | NO | PRI | | |
| fin_bas | varchar(2) | NO | PRI | | |
| fin_agg | varchar(2) | NO | | | |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Aparentemente las tablas no han cambiado. Al menos no nos muestra cambios en el DESCRIBE. Sin embargo sí que lo han hecho, ya que le hemos establecido que los campos typ_bas y fin_bas además de formar parte de claves primarias, son en sí mismos únicos. Si quisiéramos que claves foráneas de otras tablas hicieran referencia a estos campos ahora podríamos hacerlo (antes no habríamos podido ya que MySQL nos habría devuelto un error), pero en realidad para evaluar la velocidad de la consulta no nos es estrictamente necesario. Ya he comentado antes que para la velocidad a la hora de combinar y agrupar MySQL requiere unicidad, no necesariamente claves foráneas. Vamos a verlo.
Respecto a la ejecución anterior sólo hemos cambiado que ahora las dos tablas de parametrización han visto como sus campos clave utilizados en los joins se han hecho únicos. Veamos cómo va la tercera ejecución de la consulta de combinación y agrupación y evaluaremos cuánto tiempo ha tardado ahora MySQL en devolvernos el resultado:
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (1.43 sec)
Genial. Hemos pasado de 21 segundos a apenas uno y medio. La mejora es espectacular… ¿Qué ocurriría ahora si aprovechamos que esos campos ya son únicos para hacer que también sean el destino de claves foráneas en la tabla principal? Veámoslo.
-> ADD FOREIGN KEY(mat_typ) REFERENCES typ_alltypesx(typ_bas);
Query OK, 177047 rows affected (3.71 sec)
Records: 177047 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE mat_materialsx
-> ADD FOREIGN KEY(mat_fin) REFERENCES fin_allfinish(fin_bas);
Query OK, 177047 rows affected (4.10 sec)
Records: 177047 Duplicates: 0 Warnings: 0
¿Habremos mejorado algo al incorporar estas claves foráneas? Veamos, veamos…
-> fin_agg AS finish,
-> SUM(mat_qty) AS quantity
-> FROM
-> ((mat_materialsx LEFT JOIN typ_alltypesx ON mat_typ = typ_bas)
-> LEFT JOIN fin_allfinish ON mat_fin = fin_bas)
-> GROUP BY typ_agg, fin_agg;
+-------+--------+-----------+
| type | finish | quantity |
+-------+--------+-----------+
| 202 | 2B | 7217 |
| 202 | BA | 31140 |
| 430 | 1 | 994298 |
| 430 | 2B | 11621394 |
| 430 | BA | 22678569 |
| 430 | K3 | 546858 |
| 441 | 2B | 2862123 |
| 441 | K3 | 55160 |
| 524 | 1 | 4930442 |
| 524 | 2B | 23379844 |
| 524 | BA | 4499020 |
| 524 | K3 | 652620 |
+-------+--------+-----------+
12 rows in set (1.45 sec)
En este caso la respuesta es que no. Nos hemos quedado exactamente igual. No obstante con todo esto no quiero decir que las claves foráneas no sean necesarias, ya que como he dicho al comenzamiento del post, nos sirven para mantener la integridad de la base de datos (¡aspecto fundamental!). Lo único que pretendo con este post es dejar claro que MySQL no las necesita estrictamente para combinar eficientemente distintas tablas, ya que es capaz de hacerlo igual de bien simplemente con que los campos que utilizamos para los joins sean únicos.
A lo largo de todo el ejemplo es básico ir borrando la caché de consultas de MySQL para no obtener resultados distorsionados. Para ello he utilizado la sentencia:
Query OK, 0 rows affected (0.00 sec)
De lo contrario nos encontraremos con tiempos de respuesta maravillosos de 0.00 sec… 
Últimos comentarios
RSS