Actividad #21
INDICE
- Definición de índice
Un índice es una estructura de datos definida sobre una columna de tabla
(o varias) y que permite localizar de forma rápida las filas de la tabla en
base a su contenido en la columna indexada además de permitir recuperar las
filas de la tabla ordenadas por esa misma columna.
Funciona de forma parecida al índice de un libro donde tenemos el título del capítulo y la página donde empieza dicho capítulo, en un índice definido sobre una determinada columna tenemos el contenido de la columna y la posición de la fila que contiene dicho valor dentro de la tabla.
Funciona de forma parecida al índice de un libro donde tenemos el título del capítulo y la página donde empieza dicho capítulo, en un índice definido sobre una determinada columna tenemos el contenido de la columna y la posición de la fila que contiene dicho valor dentro de la tabla.
La definición de los índices de la base de datos es tarea del
administrador de la base de datos, los administradores más experimentados
pueden diseñar un buen conjunto de índices, pero esta tarea es muy compleja,
consume mucho tiempo y está sujeta a errores, incluso con cargas de trabajo y
bases de datos con un grado de complejidad no excesivo.
- Tipos de índices
Índice simple y compuesto.
Un índice simple está definido sobre una sóla
columna de la tabla mientras que un índice compuesto está formado por varias
columnas de la misma tabla (tabla sobre la cual está definido el índice.
Cuando se define un índice sobre una columna, los registros que se recuperen utilizando el índice aparecerán ordenados por el campo indexado. Si se define un índice compuesto por las columnas col1 y col2, las filas que se recuperen utilizando dicho índice aparecerán ordenadas por los valores de col1 y todas las filas que tengan el mismo valor de col1 se ordenarán a su vez por los valores contenidos en col2, función igual que la cláusula ORDER BY vista en el tema de consultas simples.
Por ejemplo si definimos un índice compuesto basado en las columnas (provincia, localidad), las filas que se recuperen utilizando este índice aparecerán ordenadas por provincia y dentro de la misma provincia por localidad.
Cuando se define un índice sobre una columna, los registros que se recuperen utilizando el índice aparecerán ordenados por el campo indexado. Si se define un índice compuesto por las columnas col1 y col2, las filas que se recuperen utilizando dicho índice aparecerán ordenadas por los valores de col1 y todas las filas que tengan el mismo valor de col1 se ordenarán a su vez por los valores contenidos en col2, función igual que la cláusula ORDER BY vista en el tema de consultas simples.
Por ejemplo si definimos un índice compuesto basado en las columnas (provincia, localidad), las filas que se recuperen utilizando este índice aparecerán ordenadas por provincia y dentro de la misma provincia por localidad.
Índice agrupado y no agrupado
El término índice agrupado no se debe
confundir con índice compuesto, el significado es totalmente diferente.
Un índice agrupado (CLUSTERED) es un índice en el que el orden lógico de los valores de clave determina el orden físico de las filas correspondientes de la tabla. El nivel inferior, u hoja, de un índice agrupado contiene las filas de datos en sí de la tabla. Una tabla o vista permite un solo índice agrupado al mismo tiempo.
Los índices no agrupados existentes en las tablas se vuelven a generar al crear un índice agrupado, por lo que es conveniente crear el índice agrupado antes de crear los índices no agrupados.
Un índice no agrupado especifica la ordenación lógica de la tabla. Con un índice no agrupado, el orden físico de las filas de datos es independiente del orden indizado.
Un índice agrupado (CLUSTERED) es un índice en el que el orden lógico de los valores de clave determina el orden físico de las filas correspondientes de la tabla. El nivel inferior, u hoja, de un índice agrupado contiene las filas de datos en sí de la tabla. Una tabla o vista permite un solo índice agrupado al mismo tiempo.
Los índices no agrupados existentes en las tablas se vuelven a generar al crear un índice agrupado, por lo que es conveniente crear el índice agrupado antes de crear los índices no agrupados.
Un índice no agrupado especifica la ordenación lógica de la tabla. Con un índice no agrupado, el orden físico de las filas de datos es independiente del orden indizado.
Índice único
Índice
único es aquel en el que no se permite que dos filas tengan el mismo valor en
la columna de clave del índice. Es decir que no permite valores duplicados.
Ventajas e inconvenientes de los índices
Ventajas
La utilización de índices puede mejorar el
rendimiento de las consultas, ya que los datos necesarios para satisfacer las
necesidades de la consulta existen en el propio índice. Es decir, sólo se
necesitan las páginas de índice y no las páginas de datos de la tabla o el
índice agrupado para recuperar los datos solicitados; por tanto, se reduce la
E/S global en el disco. Por ejemplo, una consulta de las columnas ay b de
una tabla que dispone de un índice compuesto creado en las columnas a, b y c puede
recuperar los datos especificados del propio índice.
Los índices en vistas pueden mejorar de forma
significativa el rendimiento si la vista contiene agregaciones, combinaciones
de tabla o una mezcla de agregaciones y combinaciones.
Inconvenientes
Las tablas utilizadas para almacenar los índices ocupan espacio.
Los índices consumen recursos ya que cada vez que se realiza una operación de actualización, inserción o borrado en la tabla indexada, se tienen que actualizar todas las tablas de índice definidas sobre ella (en la actualización sólo es necesaria la actualización de los índices definidos sobre las columnas que se actualizan).
Por estos motivos no es buena idea definir índices indiscriminadamente.
Los índices consumen recursos ya que cada vez que se realiza una operación de actualización, inserción o borrado en la tabla indexada, se tienen que actualizar todas las tablas de índice definidas sobre ella (en la actualización sólo es necesaria la actualización de los índices definidos sobre las columnas que se actualizan).
Por estos motivos no es buena idea definir índices indiscriminadamente.
Consideraciones a tener en cuenta
A la hora de definir índices se deben de
tener en cuenta estas consideraciones:
Hay que evitar crear demasiados índices en
tablas que se actualizan con mucha frecuencia y procurar definirlos con el
menor número de columnas posible.
Es conveniente utilizar un número mayor de
índices para mejorar el rendimiento de consultas en tablas con pocas
necesidades de actualización, pero con grandes volúmenes de datos. Un gran
número de índices contribuye a mejorar el rendimiento de las consultas que no
modifican datos, como las instrucciones SELECT, ya que el optimizador de
consultas dispone de más índices entre los que elegir para determinar el método
de acceso más rápido.
La indización de tablas pequeñas puede no ser
una solución óptima, porque puede provocar que el optimizador de consultas
tarde más tiempo en realizar la búsqueda de los datos a través del índice que
en realizar un simple recorrido de la tabla. De este modo, es posible que los
índices de tablas pequeñas no se utilicen nunca; sin embargo, sigue siendo
necesario su mantenimiento a medida que cambian los datos de la tabla.
Se recomienda utilizar una longitud corta en
la clave de los índices agrupados. Los índices agrupados también mejoran si se
crean en columnas únicas o que no admitan valores NULL.
Un índice único en lugar de un índice no
único con la misma combinación de columnas proporciona información adicional al
optimizador de consultas y, por tanto, resulta más útil.
Hay que tener en cuenta el orden de las
columnas si el índice va a contener varias columnas. La columna que se utiliza
en la cláusula WHERE en una condición de búsqueda igual a (=), mayor que
(>), menor que (<) o BETWEEN, o que participa en una combinación, debe
situarse en primer lugar. Las demás columnas deben ordenarse basándose en su
nivel de diferenciación, es decir, de más distintas a menos distintas.
Index en mysql:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
Index en Oracle :
CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
ON [esquema.]table_name [tbl_alias]
(col [ASC | DESC])
index_clause index_attribs
No hay comentarios:
Publicar un comentario