Josel Toro: Indexación

¡Hola mis coders! El objetivo de este pequeño post es aprender a emplear índices en la optimización del rendimiento de una base de datos. Para ello, primero debemos determinar la necesidad de empleo de índices en una base de datos y posteriormente emplear los comandos CREATE y DROP para el uso de índices.

Recordemos que en el tema anterior aprendimos a generar consultas sencillas de unión interna (INNER). Si no lo viste, dale clic al siguiente enlace. (Clic aquí).

¿QUÉ ES UN ÍNDICE?

Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través de un campo (o campos clave). Un índice permite un acceso mucho más rápido a los datos.

¿CÓMO TRABAJA LOS ÍNDICES?

Cada tabla se divide en páginas de datos, o bien en páginas a las que podemos acceder rápidamente a través de un índice.

Está idea es la que se aplica en el mundo de las bases de datos, la información esta guardada en una tabla (el libro) que tiene muchas hojas de datos (las páginas del libro), con un índice en el que podemos buscar la información que nos interesa.

Si queremos buscar la palabra zapato, ¿qué hacemos?

Leemos todo el diccionario hasta encontrar la palabra, o
Buscamos en el índice en que página está la letra z, y es en esa página es donde buscamos.

Ni que decir que la opción dos (2) es la correcta, y es de este modo como se utiliza un índice en las bases de datos.

CREACIÓN DE ÍNDICES

Un índice adecuadamente situado en una tabla, ayudará a la base de datos a recuperar más rápidamente los datos, sobre todo cuando las tablas son muy grandes. Los índices se asocian a una única tabla. Podrá haber un índice primario (índice por el cual se ordena la tabla de datos) y múltiples índices secundarios.

Para la creación de índices, se utiliza la siguiente sintaxis:

Donde:

índice: Es el nombre del índice a crear

tabla: Es el nombre de una tabla existente en la que se creará el índice

campo: Es el nombre del campo o lista de campos que constituyen el índice

ASC |DESC: Indica el orden de los valores de los campos ASC indica un orden ascendente (valor predeterminado) y DESC un orden descendente

UNIQUE Indica que el índice no puede contener valores duplicados

DISALLOW NULL: Prohíbe valores nulos en el índice

IGNORE NULL: Excluye del índice los valores nulos incluidos en los campos que lo componen

PRIMARY: Asigna al índice la categoría de clave principal, en cada tabla sólo puede existir un único índice que sea "Clave Principal". Si un índice es clave principal implica que no puede contener valores nulos ni duplicados

IMPORTANCIA DE LOS ÍNDICES

La selección de índices requiere que el diseñador de la base de datos haga un compromiso:

La existencia de un índice en un atributo agiliza enormemente las consultas en que se especifica un valor de él.
En cambio, todos los índices construidos para un atributo de alguna tabla hacen que inserciones, modificaciones y eliminaciones sean más complejas y Lentas

A la hora de decidirse a crear los índices, se debe estimar cuál será la combinación normal de consultas y otras operaciones sobre la base de datos. Si una tabla se consulta con mucha mayor frecuencia de lo que se modifica, conviene utilizar índices sobre los atributos utilizados como filtro de consulta más a menudo.

Si las modificaciones son la operación predominante, hay que ser más cauto respecto a la creación de índices.

USO DE LOS ÍNDICES

Razones para crear un índice

Acelerar el acceso a datos
Fuerzan la unicidad de las filas

Razones para no crear un índice

Consumen espacio en disco
Generan costos de procesamiento

TIPOS DE ÍNDICES

Índices Agrupados

Cada tabla sólo puede tener un índice agrupado
El orden físico de las filas de la tabla y el orden de las filas en el índice son el mismo
La unicidad de los valores de clave se mantiene explícitamente o implícitamente

Índices No Agrupados

Los índices no agrupados son los predeterminados de SQL Server
Los índices no agrupados existentes se vuelven a generar automáticamente:
Se quita un índice agrupado existente
Se crea un índice agrupado
Se utiliza la opción DROP_EXISTING para cambiar las columnas que definen el índice agrupado

ACTIVIDAD

Usando TRANSACT/SQL, realice las siguientes actividades en la base de datos KYO que creamos en el tema de Creación de Tablas. Identifique, cuáles son las tablas de donde es posible que se realicen la mayor cantidad de consultas, y luego cree los siguientes índices:

1. Cree un índice para acelerar las búsquedas por el apellido paterno del cliente y ante coincidencias por el nombre.

2. Para una rápida identificación de una comida cree el índice para la columna DES_COMi.

RESUMEN

Las sentencias CREATE INDEX y DROP INDEX definen índices, los cuales aceleran las consultas de la base de datos notablemente, pero a cambio ellos añaden recargo a las actualizaciones.
UNIQUE, indica que se trata de un índice único, es decir, no admite más de una tupla con el mismo valor en los atributos que forman el índice. Cuando se crea una tabla con clave primaria, automáticamente se crea un índice único.
A la hora de decidirse a crear los índices, se debe estimar cuál será la combinación normal de consultas y otras operaciones sobre la base de datos. Si una tabla se consulta con mucha mayor frecuencia de lo que se modifica, conviene utilizar índices sobre los atributos utilizados como filtro de consulta más a menudo.

SOLUCIÓN

/********************************************************/
/* Crear la base de datos KYO con las siguientes tablas */
/********************************************************/
 
CREATE DATABASE KYO
GO
 
USE KYO
GO
 
CREATE TABLE CLIENTES
(
 COD_CLI  CHAR(5) NOT NULL PRIMARY KEY,
 NOM_CLI VARCHAR(50),
 APE_PAT_CLI VARCHAR(50),
 FEC_NAC_CLI DATETIME,
 DNI_CLI CHAR(8),
 DIR_CLI VARCHAR(50), 
 SEX_CLI CHAR (1),
 GAS_MEN_CLI DECIMAL (9,2)
)
 
CREATE TABLE COMIDAS
(
 COD_COMI CHAR(5) NOT NULL PRIMARY KEY,
 DES_COMI VARCHAR(20),
 PRE_COMI DECIMAL(9,2)
)
 
CREATE TABLE INGREDIENTES
(
 COD_ING CHAR(5)NOT NULL PRIMARY KEY,
 DES_ING VARCHAR(20),
 TIP_ING VARCHAR(30),
 FEC_INGRE_ING DATETIME,
 RAZ_PRO_ING VARCHAR(20),
 RUC_PRO_ING CHAR(11)
)
 
CREATE TABLE COMPROBANTES
(
 NRO_COMP INT IDENTITY (100,1) NOT NULL PRIMARY KEY,
 FEC_COMP DATETIME,
 COD_VEND CHAR (5),
 IGV_COMP DECIMAL(9,2),
 MON_TOT_COMP DECIMAL(9,2),
 COD_CLI  CHAR(5) REFERENCES CLIENTES
)
 
CREATE TABLE DETALLECOMPROBANTES
(
 NRO_COMP INT NOT NULL REFERENCES COMPROBANTES,
 COD_COMI CHAR(5) NOT NULL REFERENCES COMIDAS,
 CAN_VEN INT,
 MON_PAR DECIMAL(9,2), 
 PRIMARY KEY(NRO_COMP,COD_COMI)
)
 
CREATE TABLE DETALLECOMIDAS
(
 COD_COMI CHAR(5) NOT NULL REFERENCES COMIDAS,
 COD_ING CHAR(5)NOT NULL REFERENCES INGREDIENTES,
 CANT_ING INT  PRIMARY KEY(COD_COMI,COD_ING)
)
GO
 
/*********************/
/* Insertar registro */
/*********************/
 
-- TABLA CLIENTES
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI001','MERCEDES','PEREZ','01/01/1979','45235678','JR.LOS PINOS 124 SAN ISIDRO','F',20.2)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI002','JOSE','BUSTAMANTE','10/12/1970','22536045','AV.LA  MARINA 123 SAN MIGUEL','M',115.3)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI003','ROBERTO','MUÑOZ','01/11/1972','00245876','CALLE PLUTON 155 SANTIAGO DE SURCO','M',125.47)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI004','MANUELA','RODRIGUEZ','11/11/1965','45824586','JR.LOS PINOS 124 SAN ISIDRO','F',85.78)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI005','JUAN','RAMON','12/06/1970','00124578','JR.LOS LOROS 124 LOS OLIVOS','M',58.5)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI006','LUCIO','PEREZ','01/01/1989','43335678','JR. PINAR 124 ATE VITARTE','M',120.2)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI007','CARLOS','BUSTIOS','10/12/1970','34536045','AV.LA  MARINA 134 SAN MIGUEL','M',215.3)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI008','ROSA','CADIZ','11/11/1972','98745876','CALLE SATURNO 1855 SANTIAGO DE SURCO','M',33.25)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI009','ILIANA','LOPEZ-RODRIGUEZ','1/1/1966','45834586','JR.LOS PUYOLES 678 SAN ISIDRO','F',85.78)
INSERT INTO CLIENTES (COD_CLI,NOM_CLI ,APE_PAT_CLI ,FEC_NAC_CLI ,DNI_CLI ,DIR_CLI,SEX_CLI,GAS_MEN_CLI)
VALUES('CI010','ULISES','PONTE','3/6/1976','52314578','JR.LOS MOLLOS 723 LOS OLIVOS','M',358.5)
GO
-- TABLA COMIDAS
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)VALUES('CO001','ARROZ CON POLLO',8.5)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)VALUES('CO002','CEVICHE',12.0)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)VALUES('CO003','MONDONGUITO ITALIANO',9.0)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)VALUES('CO004','SECO DE CABRITO',10.5)
INSERT INTO COMIDAS(COD_COMI,DES_COMI,PRE_COMI)VALUES('CO005','SUDADO DE PESCADO',12.0)
GO
--TABLA INGREDIENTES
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN001','ARROZ','CEREALES','11/06/2004','EL MOLINO','45678921456')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN002','SAL','SECOS','07/10/2004','METRO','22356458721')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN003','CEBOLLA','VERDURAS','07/12/2004','METRO','12345682')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN004','AZUCAR','SECOS','01/08/2004','METRO','12345682')
INSERT INTO INGREDIENTES(COD_ING ,DES_ING ,TIP_ING ,FEC_INGRE_ING,RAZ_PRO_ING,RUC_PRO_ING)
VALUES('IN005','POLLO','CARNES','07/12/2004','SAN FERNANDO','03254678952')
GO
--TABLA COMPROBANTES
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('11/06/2004','C0235',11.4608,60.3200,'CI001')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('01/07/2004','C0456',28.5950,150.5000,'CI001')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('11/12/2004','C1254',44.7564,235.5600,'CI002')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('06/10/2004','C3580',6.4600,34.0000,'CI002')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('07/12/2004','C0357',18.9050,99.5000,'CI003')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('1/06/2004','C1254',44.7564,235.5600,'CI006')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('1/03/2004','C3580',6.4600,34.0000,'CI006')
INSERT INTO COMPROBANTES(FEC_COMP ,COD_VEND ,IGV_COMP ,MON_TOT_COMP ,COD_CLI)
VALUES('07/11/2004','C0357',18.9050,99.5000,'CI008')
GO
--TABLA DETALLE COMPROBANTES
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('100','CO001',4,34)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('100','CO002',3,36)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('101','CO003',1,9)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('101','CO004',1,12)
INSERT INTO DETALLECOMPROBANTES(NRO_COMP,COD_COMI,CAN_VEN,MON_PAR)
VALUES('102','CO005',54,60)
GO
--TABLA DETALLE COMIDAS
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)VALUES('CO002','IN001',5)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)VALUES('CO002','IN003',5)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)VALUES('CO003','IN001',5)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)VALUES('CO003','IN002',1)
INSERT INTO DETALLECOMIDAS(COD_COMI ,COD_ING,CANT_ING)VALUES('CO003','IN003',5)
GO
 
/*1. Cree un índice para acelerar las búsquedas por el apellido paterno 
     del cliente y ante coincidencias por el nombre. */
 
 -- Se crea el índice
 CREATE NONCLUSTERED INDEX I_APE_NOM 
 ON CLIENTES (APE_PAT_CLI, NOM_CLI)
 GO
 
 -- Se verifica la existencia del índice
 EXEC SP_HELPINDEX CLIENTES
 GO
  
 -- Se comprueba
 SELECT *  FROM CLIENTES
 GO
 
/*2. Para una rápida identificación de una comida cree el índice 
     para la columna DES_COMI */
 
 -- Se crea el índice
 CREATE INDEX I_DES_COMI
 ON COMIDAS (DES_COMI)
 GO
 
 -- Se verifica la existencia del índice
 EXEC SP_HELPINDEX COMIDAS
 GO
 
 -- Se comprueba
 SELECT *  FROM COMIDAS
 GO
 
 -- Para eliminar
 DROP INDEX I_APE_NOM 
 ON CLIENTES
 GO

SI NO TIENES EL SQL SERVER INSTALADO, HAZ CLIC EN EL SIGUIENTE ENLACE Y EJECUTA EL QUERY.

RexTexter: https://rextester.com/JRX3525

¡Acabas de ver el tema Indexación del curso de Base de Datos en SQL Server! ¡Fácil verdad! Ir al índice.

Asimismo, si deseas saber más acerca de estos temas, recuerda que puedes comprar la guía completa con todos los temas, ejercicios, evaluaciones resueltas, proyectos, aplicaciones, banco de datos y más; para ello, tienes que ingresar al siguiente enlace. Y si eres seguidor de la comunidad Aprendec puedes acceder a un descuento del -%50.