Indexar claves ajenas

Una clave ajena, en esencia, es una columna o conjunto de columnas que apuntan a la clave primaria de una tabla. La idea detrás de crear una clave ajena suele ser la de mantener nuestra BBDD en tercera forma normal, de forma que evitemos duplicidad de datos en nuestras tablas y entre otras cosas mejoremos la calidad de nuestros datos y con ello el mantenimiento posterior. Crear una clave ajena  , por definición no implica ningúna acción a priori de mejora de rendimiento y de hecho no se crea pensando en ello principalmente. Sin embargo, cuando las creamos, realmente debemos tener en cuenta algunas cosas para aprovechar su existencia, y precisamente de su indexación trata este post.

Crear una clave ajena requiere por tanto una clave primaria a la que apuntar y una tabla esclava desde la cual apuntar, vamos a simular esto mismo mediante un ejemplo muy sencillo basado en el patrón maestro-esclavo, donde la tabla “esclavo” va a tener una FK hacia “maestro”:

USE Tempdb;
go
SET NOCOUNT ON 
DROP TABLE dbo.Esclavo;
DROP TABLE dbo.Maestro;
GO 
-- creacion de tablas con tipica relacion maestro-esclavo
CREATE TABLE dbo.Maestro( 
	id INT IDENTITY (1,1)	primary key,
	v int
) 
create table dbo.Esclavo(
	id int identity(1,1),
	fk_maestro int --foreign key references Maestro(id)
)
go
-- Añado la relación, por defecto es confiable y activada 
--
alter table dbo.Esclavo add constraint fk_esclavo_maestro foreign key (fk_maestro) references Maestro(id) ON DELETE cascade
GO 
-- inserto 1000 filas
--
DECLARE @i INT 
SET @i=0 

WHILE @i < 1000000
BEGIN 
	INSERT dbo.Maestro (v) VALUES (@i) 
	SET @i=@i+1 
END 
GO 

-- inserto en esclavo datos válidos apuntando al maestro
insert into dbo.Esclavo (fk_maestro)
	select id from dbo.Maestro
GO

Tras crear los datos de estas sencillas tablas, vemos la clave ajena que acabamos de crear.

-- vemos el estado confiable de la fk creada
--
SELECT name as [Nombre FK],object_name(parent_object_id) as Tabla, schema_name(schema_id) as [Schema Name],is_not_trusted,is_disabled
FROM sys.foreign_keys

Ahora para ver qué pasa cuando tenemos o no tenemos la clave ajena indexada, activamos las estadísticas de entrada/salida y de tiempos

SET STATISTICS IO ON
SET STATISTICS TIME ON

Veamos pues qué ocurre cuando intentamos borrar del maestro (nótese que estoy diciendo de la tabla padre, no de la que tiene la FK que queremos indexar)

delete from dbo.Maestro where  id = 9998

Nos damos cuenta que debido a que tengo activada la restricción con ON DELETE CASCADE, al no disponer de una forma óptima de buscar en la tabla “Esclavo” aquella clave a borrar, a pesar de tratarse de una única fila, debemos acabar recorriendo toda la tabla…lo cual es un desastre porque a priori no es algo que hubiéramos podido pensar. Básicamente lo que nos ocurre es que de forma indirecta, estamos teniendo un problema de rendimiento en una tabla que a priori no estábamos tocando, pero dado que hemos definido la clave para que gestione esos borrados (o updates), nos acaba afectando.

La solución obviamente pasa por indexar la clave ajena en nuestra tabla “Esclavo”

CREATE UNIQUE NONCLUSTERED INDEX idx_fk_esclavo_maestro ON dbo.Esclavo(fk_maestro)

Repitiendo la misma cláusula delete, vemos que el resultado ahora es dramáticamente diferente, puesto que ahora sí que se dispone de un índice a utilizar de cara a borrar sobre la tabla “Esclavo”

delete from dbo.Maestro where  id = 9999

Generalmente, crear índices para todas nuestras claves ajenas suele ser siempre una buena idea, siendo algo interesante a posteriori eliminar aquellas que producen mayor coste de modificaciones que de seeks-scans…pero eso es motivo para otro post.

Para conocer qué claves ajenas no tenemos indexadas en nuestro sistema, aquí teneis una sencilla query , que lo disfrutéis:

WITH    subselect
          AS (
              SELECT    *
              FROM      sys.foreign_key_columns fkc
              EXCEPT
              SELECT    fkc.*
              FROM      sys.foreign_key_columns fkc
                        INNER JOIN sys.index_columns ic ON fkc.parent_object_id = ic.object_id
                                                           AND fkc.parent_column_id = ic.column_id
                        INNER JOIN sys.columns sc ON ic.column_id = sc.column_id
                                                     AND ic.object_id = sc.object_id
             )
     SELECT DB_NAME() AS database_name,
            ss.name [schema],
            st.name [table],
            OBJECT_NAME(constraint_object_id) [foreign_key]
     FROM   subselect
            INNER JOIN sys.columns sc ON sc.column_id = subselect.parent_column_id
                                         AND sc.object_id = subselect.parent_object_id
            INNER JOIN sys.tables st ON st.object_id = subselect.parent_object_id
            INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id

[box] Recuerda que aunque el script anterior no lo contempla (por no alargar este post), es siempre interesante tener en cuenta el nº de cambios que soporta la tabla a indexar, así como el tamaño de la misma cuando te decidas a crear el índice.[/box]