Investigar que es un archivo índice, tipos de indices
Investigar como crear los Indices en Mysql y oracle
Crear minimo 4 archivos indices de su base de datos (veterinaria), capturar pantallas y publicar en el blog
INDICE EN MYSQL
El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, permitiendo un rápido acceso a los registros de una tabla en una base de datos. Al aumentar drásticamente la velocidad de acceso, se suelen usar sobre aquellos campos sobre los cuales se hacen frecuentes búsquedas.
El índice tiene un funcionamiento similar al índice de un libro, guardando parejas de elementos: el elemento que se desea indexar y su posición en la base de datos. Para buscar un elemento que esté indexado, sólo hay que buscar en el índice dicho elemento para, una vez encontrado, devolver el registro que se encuentre en la posición marcada por el índice.
Los índices pueden ser creados usando una o más columnas, proporcionando la base tanto para búsquedas rápidas al azar como de un ordenado acceso a registros eficiente.
Los índices son construidos sobre árboles B, B+, B* o sobre una mezcla de ellos, funciones de cálculo u otros metodos.
El espacio en disco requerido para almacenar el índice es típicamente 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), lo que da la posibilidad de almacenar en memoria los índices de tablas que no cabrían en ella. En una base de datos relacional un índice es una copia de una parte de la tabla.
Algunas bases de datos amplían la potencia del indexado al permitir que los índices sean creados de funciones o expresiones. Por ejemplo, un índice puede ser creado sobre la función
upper(apellido), que almacenaría en el índice solamente las versiones mayúsculas del campo apellido. Otra opción a veces soportada, es el uso de índices "filtrados", donde las entradas del índice son creadas solamente para los registros que satisfagan una cierta expresión condicional. Un aspecto adicional de flexibilidad es permitir la indexación en funciones definidas por el usuario, también como expresiones formadas de un surtido de funciones incorporadas. Todos estos refinamientos de la indexación son soportados en Visual FoxPro y otros lenguajes de programación, por ejemplo.Los índices pueden ser definidos como únicos o no únicos. Un índice único actúa como una restricción en la tabla previniendo filas idénticas en el índice.
Crear índices (motor de base de datos)
Las siguientes tareas forman parte de la estrategia recomendada para crear índices:
- Diseñar el índice.
El diseño de índices es una tarea crítica. El diseño de índices incluye la determinación de las columnas que se utilizarán, la selección del tipo de índice (por ejemplo, agrupado o no agrupado), la selección de opciones de índice adecuadas y la determinación de grupos de archivos o de la ubicación de esquemas de partición. Para obtener más información, vea Diseñar índices. - Determinar el mejor método de creación. Los índices se crean de las siguientes maneras:
- Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE
SQL Server Database Engine (Motor de base de datos de SQL Server) crea automáticamente un índice único para hacer cumplir los requisitos de unicidad de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice clúster único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice clúster en la tabla o que usted especifique un índice no clúster único. De forma predeterminada se crea un índice único no clúster para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice clúster único y no exista un índice clúster en la tabla.
También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición.
Un índice creado como parte de una restricción PRIMARY KEY o UNIQUE recibe automáticamente el mismo nombre que la restricción. Para obtener más información, vea Restricciones PRIMARY KEY y Restricciones UNIQUE. - Creando un índice independiente de una restricción utilizando la instrucción CREATE INDEX , o el cuadro de diálogo Nuevo índice en el Explorador de objetos de SQL Server Management Studio
Debe especificar el nombre del índice, de la tabla y de las columnas a las que se aplica el índice. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. De forma predeterminada, se crea un índice que no es único y no está agrupado si no se especifican las opciones únicas o agrupadas. Para crear un índice filtrado, use la cláusula opcional WHERE. Para obtener más información, vea Directrices generales para diseñar índices filtrados.
- Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE
- Crear el índice.
Un factor importante que debe tenerse en cuenta es si el índice se creará en una tabla vacía o en una tabla con datos. La creación de un índice en una tabla vacía no tiene implicaciones de rendimiento en el momento de creación del índice; sin embargo, el rendimiento se verá afectado cuando se agreguen los datos a la tabla.
La creación de índices en tablas grandes debe planearse con cuidado para que el rendimiento de la base de datos no se vea afectado. La mejor manera de crear índices en tablas de gran tamaño es empezar con el índice clúster y, a continuación, generar los índices no clúster. Considere la posibilidad de establecer la opción ONLINE en ON cuando cree índices en tablas existentes. Cuando se establece en ON, los bloqueos a largo plazo no se retienen, lo que permite que continúen consultas o actualizaciones a la tabla subyacente. Para obtener más información, vea Realizar operaciones de índices en línea.
Consideraciones de implementación
En la siguiente tabla se enumeran los valores máximos que se aplican a los índices clúster, no clúster, espaciales, filtrados y XML. A menos que se especifique lo contrario, las limitaciones se aplican a todos los tipos de índices.
*Puede evitar limitaciones de tamaño de registro y de columna de clave de índice de índices no clúster incluyendo columnas sin clave en el índice. Para obtener más información, vea Índice con columnas incluidas.
| Límites de índice máximos | Valor | Información adicional |
|---|---|---|
| Índices clúster por tabla | 1 | |
| Índices no clúster por tabla | 999 | Incluye índices no clúster creados por restricciones PRIMARY KEY o UNIQUE e índices filtrados, pero no índices XML. |
| Índices XML por tabla | 249 | Incluye índices XML principales y secundarios en columnas de tipos de datos xml. Índices en columnas del tipo de datos XML |
| Índices espaciales por tabla | 249 | Trabajar con índices espaciales (motor de base de datos) |
| Número de columnas de clave por índice | 16* | El índice clúster está limitado a 15 columnas si la tabla también contiene un índice XML principal o un índice espacial. Tamaño máximo de las claves de índices. |
| Tamaño del registro de clave de índice | 900 bytes* | No se aplica a índices XML ni a índices espaciales. Para que una tabla admita el uso de índices espaciales, el tamaño máximo del registro de clave de índice es de 895 bytes. Tamaño máximo de las claves de índices. |
Tipos de datos
Generalmente, se puede indizar cualquier columna de una tabla o de una vista. En la siguiente tabla se muestran todos los tipos de datos que tienen una participación de índice restringida.
| Tipo de datos | Participación de índice | Información adicional |
|---|---|---|
| Tipo definido por el usuario CLR | Se puede indizar si el tipo admite el orden binario. | Trabajar con tipos definidos por el usuario para CLR |
| Tipos de datos de objetos grandes (LOB): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml | No pueden ser una columna de clave de índice. No obstante, una columna XML puede ser una columna de clave en una tabla o en un índice XML secundario o principal. Pueden participar como columnas sin clave (incluidas) en un índice no clúster, excepto image, ntext y text. Pueden participar si son parte de una expresión de columna calculada. | Índice con columnas incluidas Índices en columnas del tipo de datos XML |
| Columnas calculadas | No se pueden indizar. Esto incluye columnas calculadas definidas como invocaciones de métodos de una columna del tipo definido por un usuario CLR, mientras los métodos se marquen como deterministas. Las columnas calculadas que se derivan de tipos de datos LOB se pueden indizar como columna con clave o sin clave mientras el tipo de datos de columna calculada se permita como columna de clave de índice o columna sin clave. | Crear índices en columnas calculadas |
| Columnas de Varchar de inserción no consecutiva | La clave de índice de un índice clúster no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna varchar y los datos existentes están en la unidad de asignación IN_ROW_DATA, las acciones de inserción o actualización posteriores de la columna que constituirían inserciones no consecutivas producirán un error. | Organización de tablas e índices Datos de desbordamiento de fila superiores a 8 KB |
| geometry | Se puede indizar con varios índices espaciales. | Tipos de datos espaciales |
Consideraciones adicionales
A continuación se ofrecen algunas consideraciones adicionales para crear un índice:
- Puede crear un índice si tiene el permiso CONTROL o ALTER en la tabla.
- Cuando se crea, el índice se habilita automáticamente y está disponible para su uso. Puede quitar el acceso a un índice deshabilitándolo. Para obtener más información, vea Deshabilitar índices.
Requisitos de espacio en disco
El espacio en disco necesario para almacenar el índice depende de los siguientes factores:
- El tamaño de cada fila de datos de la tabla y el número de filas por página. Así se determina el número de páginas de datos que se deben leer del disco para crear el índice.
- Las columnas del índice y los tipos de datos utilizados. Así se determina el número de páginas de índice que se deben escribir en disco. Para obtener más información, vea Estimar el tamaño de un índice clúster y Estimar el tamaño de un índice no clúster.
- Espacio temporal en disco necesario durante el proceso de creación del índice. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.
Consideraciones de rendimiento
El tiempo que ocupa la creación física de un índice depende en gran medida del subsistema de disco. Los factores importantes que se deben tener en cuenta son:
- El modelo de recuperación de la base de datos. El modelo de recuperación optimizado para cargas masivas de registros proporciona un rendimiento mucho mayor y un consumo de espacio de registro más reducido que la recuperación completa durante la operación de creación del índice. Sin embargo, la recuperación por medio de registros de operaciones masivas reduce la flexibilidad para la recuperación a un momento dado. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice.
- RAID (matriz redundante de discos económicos) utilizada para almacenar los archivos de base de datos y del registro de transacciones. Normalmente, los niveles de RAID que utilizan la creación de bandas tienen un ancho de banda de E/S mejor.
- Número de discos de la matriz de discos, si se utiliza RAID. Más unidades en la matriz aumentan las tasas de transferencia de datos proporcionalmente.
- Dónde se almacenan las ordenaciones intermedias de los datos. Si utiliza la opción SORT_IN_TEMPDB puede reducir el tiempo necesario para crear un índice cuando tempdb se encuentra en un conjunto de discos diferente que la base de datos del usuario. Para obtener más información, vea tempdb y la creación de índices.
- Creación del índice en línea o sin conexión.
Cuando se crea un índice sin conexión (valor predeterminado), los bloqueos exclusivos se mantienen en la tabla subyacente hasta que la transacción que crea el índice se ha completado. La tabla no está accesible para los usuarios mientras se crea el índice.
Excepto en el caso de los índices XML y los índices espaciales, es posible especificar que se cree el índice en línea. Cuando la opción en línea está establecida en ON, los bloqueos de la tabla a largo plazo no se conservan, lo que permite que las consultas o actualizaciones a la tabla subyacente continúen mientras se crea el índice. Aunque recomendamos operaciones de índice en línea, se debe evaluar el entorno y los requisitos específicos. Puede ser mejor ejecutar operaciones de índice sin conexión. Al hacerlo así, los usuarios tienen acceso restringido a los datos durante la operación, pero la operación acaba con mayor rapidez y utiliza menos recursos. Para obtener más información, vea Realizar operaciones de índices en línea.
- CREATE TABLE
- ALTER TABLE
- CREATE INDEX
- CREATE SPATIAL INDEX (Transact-SQL)
- CREATE XML INDEX (Transact-SQL)
- Cómo crear un índice espacial (SQL Server Management Studio)
Crear índices con columnas incluidas
En SQL Server 2005, un índice no clúster se puede ampliar incluyendo columnas sin clave además de las columnas de clave de índice. Las columnas sin clave se almacenan en el nivel hoja del árbol b del índice.
Los índices que incluyen columnas sin clave resultan especialmente útiles cuando abarcan la consulta. Esto significa que los índices contienen todas las columnas a las que se hace referencia en la consulta. Para obtener más información, vea Índice con columnas incluidas.
Los índices que incluyen columnas sin clave resultan especialmente útiles cuando abarcan la consulta. Esto significa que los índices contienen todas las columnas a las que se hace referencia en la consulta. Para obtener más información, vea Índice con columnas incluidas.
Requisitos de espacio en disco
Al agregar columnas sin clave al índice, se utiliza más espacio en disco para almacenar el índice. Concretamente, el hecho de agregar los tipos de datos varchar(max), nvarchar(max), varbinary(max) o xml como columnas sin clave puede aumentar de forma significativa los requisitos de espacio en disco, ya que los valores de las columnas se copian en el nivel hoja del índice y también permanecen en la tabla o el índice clúster.
El proceso para determinar los requisitos de espacio en disco para los índices con columnas incluidas es el mismo que el de los índices no clúster. Para obtener información, vea Determinar requisitos de espacio en disco del índice
El proceso para determinar los requisitos de espacio en disco para los índices con columnas incluidas es el mismo que el de los índices no clúster. Para obtener información, vea Determinar requisitos de espacio en disco del índice
Consideraciones de rendimiento
Las mejoras en el rendimiento se consiguen porque el optimizador de consultas puede localizar todos los datos de columnas necesarios del índice sin tener acceso a la tabla ni al índice clúster. No obstante, la presencia de un número excesivo de columnas puede aumentar el tiempo necesario para realizar operaciones de inserción, actualización o eliminación en la tabla subyacente o la vista indizada, ya que aumentará el mantenimiento del índice.
Ejemplos
A. Abarcar una consulta
En el ejemplo siguiente se crea un índice no clúster en la tabla Person.Address con cuatro columnas incluidas. La columna de clave de índice es PostalCode y las columnas sin clave son AddressLine1, AddressLine2, City, y StateProvinceID.
El índice abarcará esta consulta.
Copiar
USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
Copiar
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000' and '99999';
GO
B. Superar el límite de tamaño del índice
En el ejemplo siguiente, la cláusula INCLUDE de la instrucción CREATE INDEX se utiliza para indizar columnas que normalmente superarán el límite de tamaño máximo de 900 bytes de la columna de clave. La tabla Production.ProductReview incluye las columnas ProductID(int), ReviewerName(nvarchar (50)) y Comments (nvarchar (3850)). Estas columnas se utilizan con frecuencia en consultas, pero el tamaño de la columna Comments es demasiado grande para participar como una columna de clave de índice. No obstante, la cláusula INCLUDE permite agregar la columna Comments como una columna sin clave en el índice.
Copiar
USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_ProductReview_ProductID_ReviewerName
ON Production.ProductReview (ProductID, ReviewerName)
INCLUDE (Comments);
GO
El índice abarcará esta consulta.
Copiar
SELECT Comments
FROM Production.ProductReview
WHERE ProductID = 937;
GO
Para crear un índice con columnas incluidas
CREATE INDEX (Transact-SQL).
ORACLE
El índice es un instrumento que aumenta la velocidad de respuesta de la consulta, mejorando su rendimiento y optimizando su resultado. El manejo de los índices en ORACLE se realiza de forma inteligente, donde el programador sólo crea los índices sin tener que especificar, explícitamente, cuál es el índice que va a usar.
Es el propio sistema, al analizar la condición de la consulta, quien decide qué índice se necesita. Por ejemplo cuando en una consulta se relacionan dos tablas por una columna, si ésta tiene definido un índice se activa, como en el caso cuando relacionamos la tabla de clientes y ventas por la columna código para identificar al cliente (WHERE clientes.codigo=ventas.codigo)
No hay comentarios:
Publicar un comentario