Indexación eficaz de bases de datos

Una indexación eficaz en una base de datos normalizada tendrá un mayor impacto en el rendimiento que el simple uso de recursos adicionales en el sistema.

¿Qué es una base de datos normalizada?

En términos sencillos, la normalización es el proceso de estructurar bases de datos relacionales de forma que se reduzca la redundancia de datos mediante la división y vinculación de datos en trozos más pequeños de datos actualizables.

Este artículo se centra principalmente en las bases de datos que funcionan con una estructura normalizada, y explorará un área con la que la mayoría de la gente está familiarizada (o puede imaginar): las transacciones financieras, los clientes y los contactos.

¿Por qué normalizada?

Un cierto nivel de normalización puede aportar una enorme cantidad de mejoras a la mayoría de los conjuntos de datos, y aunque los lagos de datos y el procesamiento de datos no normalizados están ganando terreno en algunos aspectos del uso empresarial, la mayoría de las empresas probablemente se beneficiarían de tener sus datos principales almacenados en algún tipo de forma normal, ya que puede:

Acelerar las actualizaciones (véase más adelante)
Facilitar la consulta de datos
Normalmente proporciona una huella de datos más pequeña
Cumplir las normas del sector

Nuestro enfoque

Nuestro enfoque estándar consiste en considerar los datos como si estuvieran almacenados de tres formas distintas, y al crear nuevos sistemas basados en SQL Server intentamos mantenerlos en esquemas diferentes.

Este enfoque nos ha funcionado con clientes anteriores, e incluso hemos ampliado mejoras sustanciales de velocidad a sus proveedores de sistemas.

En un futuro, vamos a añadir un subartículo independiente para cada sección, así como una sección para explorar conceptos relacionados con la generación de informes de sistemas neutrales entre varias bases de datos.

Visión general de los índices

Aunque se centra en SQL Server, los mismos principios se aplican a muchos sistemas diferentes. El número y los tipos de índices pueden mejorar o reducir el rendimiento de lectura y escritura de forma independiente.

Agrupados

Se limita a uno por tabla, y esto define cómo se almacenan los datos en el disco.

Las tablas que tienen un índice de este tipo se denominan tablas agrupadas, y las que no lo tienen se denominan montón.

No agrupados

Casi se puede pensar en esto como una tabla separada que hace referencia a cada fila, sin embargo en SQL Server, el almacenamiento real cambia dependiendo del tipo de tabla ( agrupada / montón)

Unicidad

Ambos índices pueden ser únicos y, si se utilizan correctamente, pueden aportar mejoras reales en la forma de almacenar los datos.

Índices compuestos

Todos los índices pueden utilizar una o más columnas, pero un índice agrupado debe tener menos de 900 bytes.

Espera, ¿qué pasa con la clave primaria?

Cuando se habla de una "clave primaria", a menudo se hace referencia a un "índice agrupado único", y muchas personas lo almacenan automáticamente en una tabla dentro de un campo de identidad basado en números enteros que aumenta en uno cada vez que se crea un nuevo registro.

De hecho, una clave foránea puede hacer referencia a cualquier índice único e incluso a varias columnas.

Datos de referencia

Esta área debe incluir toda la información de nivel superior, como los tipos de cuenta y los tipos de pago a los que hace referencia otra tabla más abajo en la cadena. La ventaja es que una sola actualización puede utilizarse para cambiar varias filas en una base de datos normalizada, mientras que en una base de datos no normalizada sería necesario actualizar todas las filas.

Uso estándar

En general, lo ideal es utilizar una columna de identidad como índice agrupado único. A continuación crearemos cuatro tablas y un esquema.

Tablas de referencia

CREATE SCHEMA RefGOCREATE TABLE Ref.AddressType(AddressTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_AddressType PRIMARY KEY CLUSTERED,AddressTypeName NVARCHAR(100))CREATE TABLE Ref.ClientType(ClientTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_Client PRIMARY KEY CLUSTERED,ClientTypeName NVARCHAR(100))CREATE TABLE Ref.ContactType(ContactTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_ContactType PRIMARY KEY CLUSTERED,ContactTypeName NVARCHAR(100))CREATE TABLE Ref.TransactionType(TransactionTypeID INT IDENTITY(1,1) CONSTRAINT PK_Ref_TransactionType PRIMARY KEY CLUSTERED,TransactionTypeName NVARCHAR(100))

Datos comerciales

Este nivel medio de área incluiría cuentas, clientes y contactos u otras áreas que pueden ser referenciadas por algo más, y también referenciar la información tipo.

Este nivel es normalmente el más difícil de trabajar en términos de decidir dónde poner su índice principal, ya que probablemente será una mezcla de diferentes enfoques.

A continuación se muestra la tabla para crear las tablas Dirección, Cliente y Contacto. En este código hay una tabla adicional (de unión) que une los campos Cliente, Dirección y Tipo de dirección, y aquí hemos creado un índice agrupado que se ejecuta de forma diferente a las otras tablas. Esto se debe a que en la mayoría de las aplicaciones, esta sería una tabla de lectura intensiva, y podemos conformarnos con un incremento mínimo en el rendimiento de la inserción. Si se tratara de una aplicación creada por nosotros, probablemente separaríamos los datos de contacto de los clientes de forma similar.

Tablas comerciales

CREATE SCHEMA BusGOCREATE TABLE Bus.[Address](AddressID INT CONSTRAINT PK_Bus_Address PRIMARY KEY CLUSTERED,AddressName NVARCHAR(100),AddressTypeID INT CONSTRAINT FK_Bus_Client_AddressTypeID FOREIGN KEY REFERENCES Ref.AddressType(AddressTypeID),AddressLine1 NVARCHAR(MAX)--Use more detail as required...)CREATE TABLE Bus.Client(ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ClientName NVARCHAR(100),ClientType INT CONSTRAINT FK_Bus_Client_ClientType FOREIGN KEY REFERENCES Ref.ClientType(ClientTypeID))--Use one table to handle all client addressesCREATE TABLE Bus.ClientAddress(ClientAddressID INT IDENTITY(1,1) CONSTRAINT PK_Bus_ClientAddressID PRIMARY KEY NONCLUSTERED,AddressTypeID INT,ClientID INT,AddressID INT,CONSTRAINT UQ_Bus_ClientAddress UNIQUE NONCLUSTERED (ClientID,AddressTypeID)--This ensures one type per client, can slow down inserts slightly)CREATE UNIQUE CLUSTERED INDEX CDX_Bus_ClientAddress ON Bus.ClientAddress(ClientID,AddressTypeID,AddressID)CREATE TABLE Bus.Contact(ContactID INT IDENTITY(1,1) CONSTRAINT PK_Bus_Contact PRIMARY KEY CLUSTERED,ContactName NVARCHAR(100),ContactTypeID INT CONSTRAINT FK_Bus_Contact_ContactTypeID FOREIGN KEY REFERENCES Ref.ContactType(ContactTypeID)--Could be broken out into a joining table if desired--Use more detail as required...)

Datos transaccionales

Esta área incluye cosas como notas, pagos y pedidos, y generalmente apuntan tanto a las áreas de negocio como a las de referencia.

Si bien las claves únicas son buenas para la identificación, en el uso general probablemente no es la forma en que se quiere ordenar los datos en el disco, ya que los tiempos de lectura se verían afectados. A continuación sólo se crea una tabla, pero debería darle una idea.

Tablas transaccionales

CREATE SCHEMA TraGOCREATE TABLE Tra.[Transaction](TransactionID INT IDENTITY(1,1) CONSTRAINT PK_Tra_TransactionID PRIMARY KEY NONCLUSTERED,TransactionDate DATETIME CONSTRAINT DF_Tra_Transaction_TransactionDate DEFAULT GETUTCDATE(),--Use GETDATE() for local time.TransactionTypeID INT CONSTRAINT FK_Tra_Transaction_TransactionTypeID FOREIGN KEY REFERENCES Ref.TransactionType(TransactionTypeID),ClientID INT CONSTRAINT FK_Tra_Transaction_ClientID FOREIGN KEY REFERENCES Bus.Client(ClientID),ContactID INT CONSTRAINT FK_Tra_Transaction_ContactID FOREIGN KEY REFERENCES Bus.Contact(ContactID),TransactionAmount DECIMAL(18,2)--Use more detail as required...)CREATE CLUSTERED INDEX CDX_Tra_Transaction ON Tra.[Transaction](TransactionDate,TransactionTypeID,ClientID,ContactID)

Uniones e informes

En la base de datos ficticia anterior, hemos intentado representar la vida real lo más fielmente posible. No se trata en absoluto de un enfoque que deba adoptarse, y en última instancia el usuario es responsable de cómo utiliza la información anterior.

Como los datos han pasado al tercer nivel, el enfoque de indexación se ha trasladado a cómo se leerían los datos desde una aplicación o informe, y esto implicaría invariablemente las uniones entre tablas, y cualquier punto que pudiera o debiera incluirse en las cláusulas WHERE.