sql - optimizar - ¿Foreign Key mejora el rendimiento de las consultas?

optimizar inner join sql server

Question

Supongamos que tengo 2 tablas, productos y categorías de productos. Ambas tablas tienen una relación en CategoryId. Y esta es la consulta.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

Cuando creo el plan de ejecución, la tabla ProductCategories realiza la búsqueda del índice del clúster, que es una expectativa. Pero para Table Products, realiza un análisis de índice de clúster, lo que me hace dudar. ¿Por qué FK no ayuda a mejorar el rendimiento de las consultas?

Entonces tengo que crear un índice en Products.CategoryId. Cuando vuelvo a crear el plan de ejecución, ambas tablas realizan búsqueda de índice. Y el costo del subárbol estimado se reduce mucho.

Mis preguntas son:

1. Además de FK ayuda a la restricción de relación, ¿tiene alguna otra utilidad? ¿Mejora el rendimiento de las consultas?

2. ¿Debería crear un índice en todas las columnas de FK (me gustó Products.CategoryId) en todas las tablas?

Answer 1

A partir de SQL Server 2008, las claves externas pueden influir en el rendimiento al influir en la forma en que el motor de la base de datos elige optimizar la consulta. Consulte Star Join Heuristics en el siguiente artículo: https://technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx

Answer 2

Agregar una clave foránea en la tabla no mejorará el rendimiento, simplemente diciendo que si está insertando un registro en una base de datos de la tabla ProductCategories intentará encontrar que la columna clave externa tiene un valor que existe en el valor de la clave principal de la tabla de productos, esta búsqueda, la operación está sobrecargada en su base de datos cada vez que agrega una nueva entrada en la tabla ProductCategories. Por lo tanto, agregar una clave externa no mejorará el rendimiento de su base de datos, pero se ocupará de la integridad de su base de datos. Sí, mejorará el rendimiento de su BD si está comprobando la integridad utilizando una clave externa en lugar de ejecutar muchas consultas para verificar que el registro exista en la base de datos de su programa.

Answer 3

Foreign Keys es una herramienta de integridad referencial, no una herramienta de rendimiento. Al menos en SQL Server, la creación de un FK no crea un índice asociado, y debe crear índices en todos los campos FK para mejorar los tiempos de búsqueda.

Answer 4

Foreign Keys puede mejorar (y dañar) el rendimiento

1. Como se indica aquí: las claves externas aumentan el rendimiento

2. Siempre debe crear índices en columnas FK para reducir las búsquedas. SQL Server no hace esto automáticamente.

Editar

Como el enlace ahora parece estar muerto (felicitaciones a Chris por notarlo) , a continuación se muestra la esencia de por qué las claves externas pueden mejorar (y perjudicar) el rendimiento.

¿Puede la clave externa mejorar el rendimiento?

La restricción de clave externa mejora el rendimiento en el momento de la lectura de datos pero, al mismo tiempo, ralentiza el rendimiento en el momento de insertar / modificar / borrar datos.

En caso de leer la consulta, el optimizador puede usar restricciones de clave externa para crear planes de consulta más eficientes ya que las restricciones de clave externa son reglas declaradas previamente. Esto generalmente implica omitir una parte del plan de consulta porque, por ejemplo, el optimizador puede ver que debido a una restricción de clave externa, no es necesario ejecutar esa parte específica del plan.

Answer 5

No sé mucho sobre SQL Server, pero en el caso de Oracle, tener una columna de clave externa reduce el rendimiento de la carga de datos. Esto se debe a que la base de datos debe verificar la integridad de los datos para cada inserción. Y sí, como ya se mencionó, tener un índice en la columna de clave externa es una buena práctica.

Answer 6

Puede usarlo para ayudar a hacer una consulta más eficiente. Le permite reestructurar consultas en SQL Server para usar una combinación externa en lugar de una interna que elimina los servidores SQL necesarios para tener que verificar si hay un nulo en la columna. No es necesario que coloque ese calificador porque la relación de clave externa ya lo informa por usted.

Así que esto:

 select p.ProductId, p.Name, c.CategoryId, c.Name AS Category
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryIdwhere c.CategoryId = 1;

Se convierte en esto:

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM ProductCategories c
LEFT OUTER JOIN Products P ON
c.CategoryId = p.CategoryId
WHERE c.CategoryId = 1;

Esto no necesariamente hará un gran rendimiento en consultas pequeñas, pero cuando las tablas crecen, puede ser más eficiente.

Answer 7

Su mejor apuesta de rendimiento es usar índices en los campos que usa con frecuencia. Si usa SQL Server, puede usar Profiler para crear un perfil de una base de datos específica y tomar el archivo que genera y usar el asistente de ajuste para recibir recomendaciones sobre dónde colocar sus índices. También me gusta usar el generador de perfiles para eliminar los procedimientos almacenados de larga ejecución, tengo una lista de los diez principales delincuentes que publico cada semana, mantiene a la gente honesta: D.

Answer 8

Una clave externa es un concepto de DBMS para garantizar la integridad de la base de datos.

Las implicaciones / mejoras de rendimiento serán específicas de la tecnología de base de datos utilizada y son secundarias a la finalidad de una clave externa.

Es una buena práctica en SQL Server asegurarse de que todas las claves externas tengan al menos un índice no agrupado.

Espero que esto aclare las cosas, pero no dude en solicitar más detalles.