sql server - tipos - ¿Una clave externa crea automáticamente un índice?

sintaxis indices sql

Question

MS SQL Server 2000

Me siento tonto por hacer esta pregunta, pero me han dicho que si me meto una clave foránea en dos tablas, ese servidor SQL creará algo parecido a un índice en la tabla secundaria. Me cuesta creer que esto sea verdad, pero no puedo encontrar mucho relacionado específicamente con esto.

Mi verdadera razón para preguntar esto es porque estamos experimentando un tiempo de respuesta muy lento en una declaración de eliminación contra una tabla que probablemente tenga 15 tablas relacionadas. Le he preguntado a nuestro tipo de base de datos y él dice que si hay una clave externa en los campos, entonces actúa como un índice. ¿Cuál es tu experiencia con esto? ¿Debo agregar índices en todos los campos de clave externa o son simplemente una sobrecarga innecesaria?

Answer 1

Digamos que tienes una mesa grande llamada pedidos y una mesa pequeña llamada clientes. Hay una clave externa de un pedido a un cliente. Ahora, si elimina un cliente, el servidor SQL debe verificar que no haya pedidos huérfanos; Si las hay, se levanta un error.

Para verificar si hay pedidos, el servidor SQL tiene que buscar en la tabla de pedidos grandes. Ahora si hay un índice, la búsqueda será rápida; Si no hay, la búsqueda será lenta.

Entonces, en este caso, la eliminación lenta podría explicarse por la ausencia de un índice. Especialmente si Sql Server tendría que buscar 15 tablas grandes sin un índice.

PS Si la clave externa tiene ON DELETE CASCADE, Sql Server aún tiene que buscar en la tabla de pedidos, pero luego eliminar cualquier pedido que haga referencia al cliente eliminado.

Answer 2

En PostgeSql usted puede verificar los índices usted mismo si golpea / d tablename

Verá que los índices de btree se han creado automáticamente en columnas con clave principal y restricciones únicas, pero no en columnas con claves externas.

Creo que eso responde a tu pregunta al menos para postgres.

Answer 3

Estrictamente hablando, las claves externas no tienen absolutamente nada que ver con los índices, sí. Pero, como señalaron los oradores que están por encima de mí, tiene sentido crear uno para acelerar las búsquedas de FK. De hecho, en MySQL, si no especifica un índice en su declaración FK, el motor (InnoDB) lo crea automáticamente.

Answer 4

Las claves externas no crean índices. Solo las restricciones de clave alternativa (UNIQUE) y las restricciones de clave principal crean índices. Esto es cierto en Oracle y SQL Server.

Answer 5

No que yo sepa. Una clave externa solo agrega una restricción para que el valor en la clave secundaria también se represente en algún lugar de la columna principal. No le está diciendo a la base de datos que la clave secundaria también debe ser indexada, solo restringida.

Answer 6

No, no hay un índice implícito en los campos de clave externa, de lo contrario, ¿por qué diría Microsoft que "Crear un índice en una clave externa a menudo es útil" ? Su colega puede estar confundiendo el campo de clave foránea en la tabla de referencia con la clave primaria en la tabla de referencia: las claves primarias crean un índice implícito.

Answer 7

Observo que Entity Framework 6.1 apuntado a MSSQL agrega automáticamente índices en claves externas.

Answer 8

SQL Server crea automáticamente índices para las claves principales, pero no para las claves externas. Crea el índice para las claves foráneas. Probablemente valga la pena el gasto.

Answer 9

Una clave externa es una restricción, una relación entre dos tablas, que no tiene nada que ver con un índice en sí.

Pero es un hecho conocido que tiene mucho sentido indexar todas las columnas que forman parte de cualquier relación de clave externa, porque a través de una relación FK, a menudo tendrá que buscar una tabla relacionada y extraer ciertas filas basándose en Un solo valor o un rango de valores.

Por lo tanto, tiene sentido indexar las columnas involucradas en un FK, pero un FK per se no es un índice.

Consulte el excelente artículo de Kimberly Tripp "¿Cuándo dejó de colocar SQL Server los índices en las columnas de clave externa?" .

Answer 10

Wow, las respuestas están en todo el mapa. Entonces la Documentation dice:

Una restricción FOREIGN KEY es candidata para un índice porque:

• Los cambios en las restricciones de PRIMARY KEY se verifican con las restricciones de FOREIGN KEY en las tablas relacionadas.

• Las columnas de clave externa a menudo se utilizan en criterios de combinación cuando los datos de las tablas relacionadas se combinan en consultas al hacer coincidir las columnas en la restricción FOREIGN KEY de una tabla con las columnas de clave principal o única en la otra tabla. Un índice permite que Microsoft® SQL Server ™ 2000 encuentre datos relacionados en la tabla de clave externa rápidamente. Sin embargo, la creación de este índice no es un requisito. Los datos de dos tablas relacionadas se pueden combinar incluso si no se han definido restricciones CLAVE PRINCIPAL o CLAVE EXTRAÑA entre las tablas, pero una relación de clave externa entre dos tablas indica que las dos tablas se han optimizado para combinarse en una consulta que usa las claves como sus criterios.

Así que parece bastante claro (aunque la documentación está un poco confusa) que, de hecho, no crea un índice.