FACULTAD DE INGENIERÍAS

INGENIERÍA DE SISTEMAS

PROGRAMACIÓN EN BASE DE DATOS

“INDEX SQL”

ESTUDIANTES:

CARLOS MARTINEZ VERGARA

CARLOS GALVIS PADILLA

JESSICA DURANGO GARCÉS

BLEIDER HERNÁNDEZ MORALES

MAURO RODRIGUEZ GOENAGA

DOCENTE:

JIMMY JOSÉ SANCHEZ GARCIA

MONTERIA– CORDOBA

2019
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

¿QUÉ ES UN ÍNDICE?

Un índice es una estructura diferente dentro de la base de datos; Requiere su propio espacio
en disco y contiene una copia de los datos de la tabla. Eso significa que un índice es una
redundancia. Crear un índice no cambia los datos de la tabla; solamente establece una nueva
estructura de datos que hace referencia a la tabla.
Sin un índice, el sistema de base de datos lee a través de toda la tabla (este proceso se
denomina “escaneo de tabla”) para localizar la información deseada. Con el índice correcto
en su lugar, el sistema de base de datos puede entonces primero dirigirse al índice para
encontrar de dónde obtener los datos, y luego dirigirse a dichas ubicaciones para obtener los
datos necesarios. Esto es mucho más rápido. Por lo tanto, generalmente se recomienda crear
índices en tablas. En suma, Los índices mejoran el tiempo de recuperación de los datos en
las consultas realizadas contra nuestra base de datos. Pero los índices no son todo ventajas,
la creación de índices implica un aumento en el tiempo de ejecución sobre aquellas consultas
de inserción, actualización y eliminación realizadas sobre los datos afectados por el índice
(ya que tendrán que actualizarlo). Del mismo modo, los índices necesitan un espacio para
almacenarse, por lo que también tienen un coste adicional en forma de espacio en disco.
El espacio en disco requerido para almacenar el índice es generalmente menor que el espacio
de almacenamiento de la tabla, puesto que los índices generalmente contienen solamente los
campos clave de acuerdo con los que la tabla será ordenada, y excluyen el resto de los detalles
de la tabla. Esto permite almacenar en memoria principal los índices por completo, lo que
permite realizar más rápida la búsqueda de la dirección física del registro en las tablas.
Se debe tener en cuenta:
➢ Es recomendable crear índices para optimizar las consultas.
➢ Las columnas con tipos de datos ntext,text, varchar (max), nvarchar(max), varbinary
(max), xml, o imagen no se pueden crear índices.
➢ El orden puede ser ascendente (Asc) o descendente (Desc). El valor por defecto es
Asc.
En general, se emplea los índices para las siguientes acciones:
❖ Encontrar las filas que cumplen la condición WHERE de la consulta cuyas columnas
estén indexadas.
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

❖ Para recuperar las filas de otras tablas cuando se emplean operaciones de tipo JOIN.
Para ello, es importante que los índices sean del mismo tipo y tamaño ya que
aumentará la eficiencia de la búsqueda. Por ejemplo: una operación de tipo JOIN
sobre dos columnas que tengan un índice del tipo INT(10).
❖ Disminuir el tiempo de ejecución de las consultas con ordenación (ORDER BY) o
agrupamiento (GROUP BY) si todas las columnas presentes en los criterios forman
parte de un índice.
❖ Si la consulta emplea una condición simple cuya columna de la condición está
indexada, las filas serán recuperadas directamente a partir del índice, sin pasar a
consular la tabla.

TIPOS DE INDICES
Los distintos tipos de índices que se pueden crear y las condiciones que deben cumplir cada uno de
ellos:
✓ INDEX (NON-UNIQUE): este tipo de índice se refiere a un índice normal, no único. Esto
implica que admite valores duplicados para la columna (o columnas) que componen el índice.
No aplica ninguna restricción especial a los datos de la columna (o columnas) que componen
el índice, sino que se emplea simplemente para mejorar el tiempo de ejecución de las
consultas.
✓ UNIQUE: este tipo de índice se refiere a un índice en el que todas las columnas deben tener
un valor único. Esto implica que no admite valores duplicados para la columna (o columnas)
que componen el índice. Aplica la restricción de que los datos de la columna (o columnas)
deben tener un valor único.
✓ PRIMARY: este tipo de índice se refiere a un índice en el que todas las columnas deben tener
un valor único (al igual que en el caso del índice UNIQUE) pero con la limitación de que
sólo puede existir un índice PRIMARY en cada una de las tablas. Aplica la restricción de que
los datos de la columna (o columnas) deben tener un valor único.
✓ FULLTEXT: estos índices se emplean para realizar búsquedas sobre texto (CHAR,
VARCHAR y TEXT). Estos índices se componen por todas las palabras que están contenidas
en la columna (o columnas) que contienen el índice. No aplica ninguna restricción especial a
los datos de la columna (o columnas) que componen el índice sino que se emplea
simplemente para mejorar el tiempo de ejecución de las consultas. Este tipo de índices sólo
están soportados por InnoDB y MyISAM en MySQL 5.7.
✓ SPATIAL: estos índices se emplean para realizar búsquedas sobre datos que componen
formas geométricas representadas en el espacio. Este tipo de índices sólo están soportados
por InnoDB y MyISAM en MySQL 5.7.
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

Es importante destacar que todos estos índices pueden construirse empleando una o más columnas.
Del mismo modo, el orden de las columnas que se especifique al construir el orden es relevante para
todos los índices menos para el FULLTEXT (ya que este índice mira en TODAS las columnas que
componen el índice).

ÍNDICES AGRUPADOS

Cuando se crea una restricción PRIMARY KEY en un tabla que no contiene un índice
agrupado, SQL Server creará uno y utilizará la columna de clave primaria como clave para
el índice agrupado. Si ya existe un índice agrupado SQL Server creará un índice no agrupado
sobre la columna definida con una restricción PRIMARY KEY. Una columna definida como
la clave primaria es un índice muy útil porque los valores de la columna están garantizados
que son únicos. Índices sobre columnas de valores únicos son de menor tamaño que los
índices sobre columnas con valores duplicados y generan estructuras de búsqueda más
eficientes. Una columna definida con una restricción UNIQUE genera automáticamente un
índice no agrupado.

NO AGRUPADO

Un índice no agrupado no ordena los datos físicos dentro de la tabla. De hecho, un índice no
agrupado es agrupado en un solo lugar y los datos de la tabla son almacenados en otro lugar.
La base de datos usa los índices no agrupados para encontrar registros según una clave. Si no
existe un índice agrupado para la tabla, los datos de la tabla se encontrarán desordenados
físicamente y se dice que la tabla tendrá la estructura de montón (heap). Un índice no
agrupado sobre una tabla montón contiene punteros a las filas de la tabla. Cada entrada en
las páginas de índice contiene un identificador de fila (RID, row ID). El RID es un puntero a
una fila en un montón, y este consiste de un número de página, un número de archivo y un
número de ranura. Si existe un índice agrupado, las páginas de un índice no agrupado
contienen las claves del índice agrupado en vez del RID.

En conclusión, los índices pueden presentar las siguientes características:

 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

1. Puede haber sólo un índice agrupado por tabla. De todos modos, usted puede crear
múltiples índices no agrupados en una sola tabla.
2. Los índices agrupados sólo ordenan tablas. Por lo tanto, no consumen almacenaje
extra. Los índices no agrupados son almacenados en un lugar separado de la tabla
real. Reclamando más espacio de almacenamiento.
3. Los índices agrupados son más rápidos que los índices no agrupados, ya que no
involucran ningún paso extra de búsqueda.

¿COMO CREAR INDICES?

Es importante crear un índice por cada tipo de búsqueda que queramos hacer en la tabla. Al
momento de actualizar una tabla con índices tarda más tiempo porque también hay que
actualizar los índices, así que solo se deben poner índices en las columnas por las que
buscamos frecuentemente.

Para crear un índice, se empleará la siguiente estructura:

«CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name
(index_col_name…) index_type;»
Donde:
index_name: es el nombre del índice.
table_name: es el nombre de la tabla donde se va a crear el índice.
index_col_name: nombre de la columna (o columnas) que formarán el índice.
index_type: es el tipo del índice. Se emplea con USING [BTREE | HASH].

Un ejemplo sería:
CREATE UNIQUE INDEX mi_indice_unico ON mi_tabla (mi_columna) USING HASH;

SINTAXIS PARA SQL CREATE INDEX

CREATE INDEX nombreindice

ON nombretabla (nombrecolumna)
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

La columna que forma parte de este índice admite valores duplicados en su columna.

Sintaxis para SQL CREATE UNIQUE INDEX

CREATE UNIQUE INDEX nombreindice

ON nombretabla (nombrecolumna)

La columna que forma parte de este índice NO admite valores duplicados en su columna,
porque es una clave única. Es decir, El parámetro UNIQUE especifica que el índice será
único, por tanto no aceptará que dos registros de la tabla tengan la misma combinación de
columnas de índice. Cuando se crea un índice con la cláusula UNIQUE, el servidor examina
si existe alguna combinación de columnas repetida para el índice, si la encuentra falla la
creación del índice. Si se crea con éxito, cada operación de inserción y actualización será
comprobada por el servidor para asegurarse que no se produzcan violaciones de esta
restricción.
SINTAXIS

Para la creación de índices vamos a dividir la sintaxis de acuerdo al tipo de índice.

➢ Crear un índice no agrupado

Create nonclustered index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2

orden])
go

➢ Crear un índice agrupado

Create clustered index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden])

go

➢ Crear un índice único

Create unique index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden])

go

➢ Crear un índice filtrado

 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden])

where ExpresiónLógica
go

➢ Crear un índice con factor de relleno

Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden])

with fillfactor = Valor
go

➢ Crear un índice y sobre escribir el existente

Create index NombreDelIndice on Tabla/Vista (Camp1 orden[, Campo2 orden])

with drop_existing = on
go

No hay una regla estricta respecto de cómo nombrar un índice. El método generalmente
aceptado es colocar un prefijo, tal como “IDX_”, antes del nombre de un índice para evitar
la confusión con otros objetos de la base de datos. También es una buena idea brindar
información sobre qué tabla y columna(s) se utilizará el índice.

ÍNDICES Y RESTRICCIONES
Los índices se crean automáticamente cuando las restricciones PRIMARY KEY y UNIQUE
se definen en las columnas de tabla. Por ejemplo, cuando crea una tabla con una restricción
UNIQUE, Motor de base de datos crea automáticamente un índice no agrupado. Si configura
una restricción PRIMARY KEY, Motor de base de datos crea automáticamente un índice
agrupado, a menos que ya exista uno. Cuando intenta aplicar una restricción PRIMARY KEY
en una tabla existente y ya existe un índice agrupado en esa tabla, SQL Server aplica la clave
principal mediante un índice no agrupado.

¿CÓMO USA LOS ÍNDICES EL OPTIMIZADOR DE CONSULTAS?

 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

Cuando el optimizador de consultas utiliza un índice, busca en las columnas de clave de

índice, busca la ubicación de almacenamiento de las filas que necesita la consulta y extrae
las filas coincidentes de esa ubicación. Generalmente, la búsqueda del índice es mucho más
rápida que la búsqueda de la tabla porque, a diferencia de la tabla, un índice frecuentemente
contiene muy pocas columnas por fila y las filas están ordenadas.

El optimizador de consultas normalmente selecciona el método más eficaz cuando ejecuta

consultas. No obstante, si no hay índices disponibles, el optimizador de consultas debe
utilizar un recorrido de la tabla. Su tarea consiste en designar y crear los índices que sean
más educados para su entorno para que el optimizador de consultas tenga una selección de
índices eficientes entre los cuales elegir.

DISEÑO DE ÍNDICES

los índices mal diseñados y la falta de índices constituyen las principales fuentes de atascos
en aplicaciones de base de datos. El diseño eficaz de los índices tiene gran importancia para
conseguir un buen rendimiento de una base de datos y una aplicación.
La selección de los índices apropiados para una base de datos y su carga de trabajo es una
compleja operación que busca el equilibrio entre la velocidad de la consulta y el costo de
actualización. Los índices estrechos, o con pocas columnas en la clave de índice, necesitan
menos espacio en el disco y son menos susceptibles de provocar sobrecargas debido a su
mantenimiento. Por otra parte, la ventaja de los índices anchos es que cubren más consultas.
Por otro lado, no es recomendable crear índices en tablas pequeñas, ya que lleva al Motor de
SQL menos tiempo de escanear las tablas subyacentes que atravesar el índice al buscar por
una información específica. En este caso, el índice no será usado y todavía afectará el
rendimiento de la modificación de la información, como también siempre será ajustado
cuando se modifique la información de la tabla subyacente.

las características de las columnas de la tabla que son usadas en las consultas presentadas
deberían también ser consideradas al diseñar un índice. Por ejemplo las columnas con los
tipos de información numérica exacta, como información de tipo INT y BIGINT y que son
usadas como UNIQUE y NOT NULL son consideradas columnas óptimas para participar en
la clave de índice. En la mayoría de los casos, una larga consulta es causada por poner índice
a una columna con pocos valores singulares. Es recomendable crear los índices en columnas
que son usadas en consultas y unir condiciones en el orden correcto que es especificado en
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

el postulado. De este modo, el objetivo es guardar la clave de índice corta, sin incluir las
columnas raramente usadas, para minimizar la complejidad del índice, almacenar y mantener
los gastos.

ALMACENAMIENTO DE ÍNDICE
La localización de almacenaje del índice también puede afectar en el rendimiento de las
consultas leyendo del índice. Por defecto, el índice será almacenado en el mismo grupo de
archivo como la tabla subyacente en la cual el índice es creado. Si diseñas un índice No
Agrupado para ser almacenado en un archivo de información diferente del archivo de
información de la tabla subyacente y localizado en una unidad de disco separado, o
horizontalmente separar el índice para separar múltiples archivos de grupo, el rendimiento
de las consultas que se están leyendo del índice serán mejoradas, debido al mejoramiento del
rendimiento I/O obtenido al pulsar en diferentes archivos de información y unidades de disco
al mismo tiempo.

¿En qué estructuras se almacenan los índices?

Una vez hemos visto los tipos de índices, vamos a ver los distintos tipos de estructuras que
se pueden crear para almacenar los índices junto con las características de cada uno de ellas:
B-TREE: este tipo de índice se usa para comparaciones del tipo =, >, <, >=, <=, BETWEEN
y LIKE (siempre y cuando se utilice sobre constantes que no empiecen por %). Para realizar
búsquedas empleando este tipo de índice, se empleará cualquier columna (o conjunto de
columnas) que formen el prefijo del índice. Por ejemplo: si un índice está formado por las
columnas [A, B, C], se podrán realizar búsquedas sobre: [A], [A, B] o [A, B, C].
HASH: este tipo de índice sólo se usa para comparaciones del tipo = o <=>. Para este tipo de
operaciones son muy rápidos en comparación a otro tipo de estructura. Para realizar
búsquedas empleando este tipo de índice, se emplearán todas las columnas que componen el
índice.
Un índice puede ser almacenado en cualquier tipo de estructura pero, en función del uso que
se le vaya a dar, puede interesar crear el índice en un tipo determinado de estructura o en
otro. Por norma general, un índice siempre se creará con la estructura de B-TREE, ya que es
la estructura más empleada por la mayoría de operaciones.
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

ESTRUCTURA DE LOS ÍNDICES

La estructura de los índices en SQL es una estructura es que denominamos árbol balanceado
o b-tree. Un árbol balanceado (b-tree), organiza las búsquedas siguiendo una ramificación
determinada a través de un árbol en el que cada rama se conecta a otras dos o a un nodo
terminal u hoja.
La estructura de un indice B-Tree tiene la forma de un árbol invertido. Las estructuras
superiores se llaman ramas y la estructura inferior constituyen las hojas. Generalmente los
indices B-Tree tienen uno o más niveles de ramas. Cada rama de un nivel contiene
información sobre otras ramas del nivel inmediatamente inferior.

Representación gráfica de la estructura de árbol balanceado.

DESHABILITANDO ÍNDICES

Cuando desactivas el índice SQL Server, la definición, estadísticas, y la información de ese

índice no será removidos del catálogo del sistema, pero no podrás acceder a ese índice.
Desactivando el índice No agrupado te prevendrá de acceder solo a ese índice. Por otro lado
desactivando el índice agrupado te prevendrá de acceder a la información de la tabla
subyacente, hasta que dejes o reconstruyas el índice.
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

Puedes decidir de desactivar un índice temporalmente debido a muchas causas, como

solucionar problemas de un escenario específico, corrigiendo un error de disco I/O antes de
reconstruir el índice, o eliminando el espacio del disco temporario requerido para almacenar
la antigua y nueva versión del índice durante el proceso de reconstrucción de índice, donde
solo 20 por ciento del tamaño del índice será requerido para clasificar la información del
índice.

RENOMBRAR ÍNDICES
Es mejor seguir una convención de nombramiento standard al crear los índices de SQL , para
entender el propósito de ese índice desde el nombre del índice. Puedes especificar el tipo de
índice, el nombre de la tabla en la cual el índice es creado y el nombre de las columnas que
participan en ese índice, en el nombre del índice para hacerlo significante y único en el nivel
de la tabla. Para los índices existentes, puedes remplazar el nombre actual del índice con un
nuevo nombre que sigue la política convencional de nombramiento de tu compañía.
Renombrar el índice no afectará la estructura del índice o la reconstruirá, solo cambiará el
nombre de ese índice.
Por ejemplo cuando creas una restricción PRIMARY KEY o UNIQUE, el SQL creará un
índice relacionado automáticamente para aplicar esa restricción, proveyendo un nombre
largo que contiene el tipo de esa restricción, el nombre de la tabla y un valor único.

CONFIGURANDO LAS OPCIONES DE ÍNDICE

Cuando creas o reconstruyes un índice, hay un número de opciones de índice a ser

considerados y configurados. Estas opciones incluyen:

• PAD_INDEX: Usado para aplicar el porcentaje de espacio libre especificado por

FillFactor al nivel intermedio páginas de índice durante la creación del índice.
• FILLFACTOR: Usado para configurar el porcentaje de espacio libre que el Motor
de SQL Server dejará en el nivel hoja de cada página de índice durante la creación de
índice. FillFactor debería ser un valor entero de 0 a 100 con 0 o 100 como valor por
defecto, en la cual las páginas serán llenadas completamente durante la creación del
índice.
• SORT_IN_TEMPDB: Especifica si la clasificación intermedia resulta, generada
durante la creación del índice, será almacenada en tempdb.
 FACULTAD DE INGENIERÍAS
INGENIERÍA DE SISTEMAS

• IGNORE_DUP_KEY: Especifica si un error de mensaje será mostrado cuando

valores duplicados clave son insertados en un único índice.
• STATISTICS_NORECOMPUTE: Determina si la estadística de distribución del
índice desactualizada será reprogramada automáticamente.
• DROP_EXISTING: Especifica que el índice existente nombrado será abandonado y
recreado otra vez.
• ONLINE: Especifica si las tablas subyacentes son accesibles para consultas y
modificación de información durante la operación de índice.
• ALLOW_ROW_LOCKS: Determina si los bloqueos de fila son permitidos a
acceder en la información de índice.
• ALLOW_PAGE_LOCKS: Determina si los bloqueos de página son permitidos para
acceder en la información de índice.
• MAXDOP: Usado para limitar el máximo número de procesadores usados en
ejecución de plano paralelo de la operación de índice.
• DATA_COMPRESSION: Especifica el nivel de compresión de la información para
los índices especificados, numero de partición, de rango de particiones, con valores
NONE, ROW, y PAGE.

Para concluir, los índices tienes aspectos negativos y Los aspectos positivos que permiten
que las consultas de tipo WHERE se ejecuten de forma más rápida siempre y cuando se
empleen columnas con índices en las condiciones de la consulta. Por otra parte, empeora el
tiempo sobre las consultas de inserción, actualización y eliminación sobre las tablas que
contengan índices. Por norma general, las consultas de tipo WHERE son más predominantes
que el resto de consultas e interesa que éstas se ejecuten lo más rápido posible. En cambio,
las consultas de inserción, actualización y eliminación pueden ver disminuido su tiempo de
ejecución sin comprometer demasiado la usabilidad del sistema.