Los índices y las claves foraneas son excelentes herramientas cuando se enfrenta a grandes bases de datos. Pueden ser la respuesta a un buen diseño y gran rendimiento. En este artículo, revisamos algunos consejos para comprender el cómo usar estas herramientas de manera eficiente y agilizar el trabajo con bases de datos complejas.
Para los ejemplos se utilizo DBSchema. Esta herramienta está orientada a diagramas, integra muchas características y tiene un precio adecuado.
Llaves Foráneas
Es un error común evitar crear claves foráneas en una base de datos porque afectan negativamente al rendimiento. Es cierto que las claves foráneas afectarán las instrucciones INSERT, UPDATE y DELETE por que estas realizan comprobación de datos, pero mejoran el rendimiento general de una base de datos.
El principal beneficio de las claves foráneas es que hacen cumplir la concistencia de los datos, lo que significa que mantienen limpia la base de datos.
Como ejemplo, considere las dos tablas a continuación. A las dos les faltan claves externas y si eliminamos un departamento, los empleados asociados con él permanecerán en la tabla de empleados como «registros incorrectos». Este escenario ocurre con mucha frecuencia y conducen a una base de datos llena de basura que disminuye el rendimiento.
Sin clave foránea:
Con clave foranea
Índices
Un índice para una base de datos es como una tabla de contenido para un libro. La búsqueda de datos basada en una columna que forma parte del índice nos permitirá hacer uso del índice para acceder rápidamente al registro.
Considere la tabla de empleados con un índice en firstname, la ejecución de la consulta a continuación utilizará este índice.
1 | SELECT * from employees WHERE firstname = ? |
Puntos importantes que se debe saber sobre los índices:
Las bases de datos solo usan un índice por tabla y consulta
Digamos que creamos dos índices en la tabla empleados idx_firstname y idx_lastname. Si ejecutamos la consulta a continuación, la base de datos decidirá usar solo uno de los dos índices.
1 | SELECT * from employees WHERE firstname = ? and lastname=? |
Comparación de índice
Al ejecutar una consulta, la base de datos comparará los índices con respecto al número de entradas diferentes que contiene.
Por ejemplo, sí tenemos 2000 registros diferentes para el firstname y 5000 registros diferentes para el lastname, es más probable que el uso del índice de lastname devuelva un número menor de filas que se ajusten al criterio de búsqueda. Por lo tanto, se utilizará ese índice.
Índices compuestos
Un índice que contiene dos o más columnas es un índice compuesto. Cuando se usa un índice compuesto, la consulta siempre debe contener la primera columna del índice.
Por ejemplo, considere que tenemos el índex idx(firstname, lastname). Este índice funcionará perfectamente en la próxima consulta.
1 | SELECT * from employees WHERE firstname = ? and lastname=? |
Pero no funcionará para este:
1 | SELECT * from employees WHERE lastname=? |
índices agrupados
Dado la analogía desde el principio, en un índice agrupado, la tabla de contenido se encuentra al final del libro. En un índice no agrupado, la tabla de contenido se encuentra en un lugar diferente, fuera del libro. Los índices agrupados se recomiendan cuando tienen menos actualizaciones en sus datos.
Al crear un índice agrupado, la tabla misma se convierte en el índice.
Conclusión
Las claves externas son muy útiles para mantener limpia una base de datos y no afectará las instrucciones SELECT. Los índices ayudan a explotar rápidamente la base de datos y encontrar los datos que necesita. Saber cómo y cuándo usarlos mejorará el rendimiento de la base de datos y facilitará su trabajo.
Este artículo se encuentra basado en The Secrets of Indexes and Foreign Keys.
