¿Cómo funciona la indexación de bases de datos?

votos
1k

Dado que indexinges tan importante a medida que su conjunto de datos aumenta de tamaño, ¿alguien puede explicar cómo funciona la indexación en un database-agnosticnivel?

Para obtener información sobre consultas para indexar un campo, consulte Cómo indexar una columna de base de datos .

Publicado el 04/08/2008 a las 11:07
fuente por usuario
En otros idiomas...                            


10 respuestas

votos
2k

¿Por qué es necesario?

Cuando los datos se almacenan en dispositivos de almacenamiento basados ​​en disco, se almacenan como bloques de datos. Se accede a estos bloques en su totalidad, lo que los convierte en la operación de acceso al disco atómico. Los bloques de disco están estructurados de la misma manera que las listas vinculadas; ambos contienen una sección para datos, un puntero a la ubicación del próximo nodo (o bloque), y ambos no necesitan almacenarse contiguamente.

Debido al hecho de que varios registros solo se pueden ordenar en un campo, podemos afirmar que la búsqueda en un campo que no está ordenado requiere una búsqueda lineal que requiere N/2accesos de bloque (en promedio), ¿dónde Nestá el número de bloques que la mesa se extiende Si ese campo es un campo que no es clave (es decir, no contiene entradas únicas), entonces se debe buscar el espacio de tabla completo en los Naccesos de bloque.

Mientras que con un campo ordenado, se puede utilizar una búsqueda binaria, que tiene log2 Nacceso de bloque. Además, dado que los datos se ordenan dado un campo no clave, no es necesario buscar valores duplicados en el resto de la tabla, una vez que se encuentra un valor más alto. Por lo tanto, el aumento en el rendimiento es sustancial.

¿Qué es la indexación?

La indexación es una forma de clasificar una cantidad de registros en múltiples campos. Al crear un índice en un campo de una tabla, se crea otra estructura de datos que contiene el valor del campo y un puntero al registro al que se refiere. Luego, esta estructura de índice se ordena, lo que permite realizar búsquedas binarias en ella.

La desventaja de indexar es que estos índices requieren espacio adicional en el disco ya que los índices se almacenan juntos en una tabla usando el motor MyISAM, este archivo puede alcanzar rápidamente los límites de tamaño del sistema de archivos subyacente si se indexan muchos campos dentro de la misma tabla .

¿Como funciona?

En primer lugar, perfilemos un esquema de tabla de base de datos de muestra;

Nombre del campo Tipo de datos Tamaño en el disco
id (clave principal) INT sin signo 4 bytes
firstName Char (50) 50 bytes
lastName Char (50) 50 bytes
emailAddress Char (100) 100 bytes

Nota : se usó char en lugar de varchar para permitir un tamaño preciso en el valor del disco. Esta base de datos de muestra contiene cinco millones de filas y no está indexada. El rendimiento de varias consultas ahora será analizado. Estos son una consulta utilizando el ID (un campo clave ordenados) y una utilizando el primerNombre (sin ordenar un campo que no son clave).

Ejemplo 1 : ordenado frente a campos no ordenados

Dada nuestra base de datos de muestra de r = 5,000,000registros de un tamaño fijo que da una longitud de registro de R = 204bytes y se almacenan en una tabla utilizando el motor MyISAM que está utilizando los B = 1,024bytes de tamaño de bloque predeterminados . El factor de bloqueo de la tabla serían los bfr = (B/R) = 1024/204 = 5registros por bloque de disco. El número total de bloques necesarios para mantener la tabla es N = (r/bfr) = 5000000/5 = 1,000,000bloques.

Una búsqueda lineal en el campo de identificación requeriría un promedio de N/2 = 500,000bloque de accesos para encontrar un valor, dado que el campo de identificación es un campo clave. Pero dado que el campo de ID también está ordenado, se puede realizar una búsqueda binaria que requiere un promedio de log2 1000000 = 19.93 = 20accesos de bloque. Instantáneamente podemos ver que esta es una mejora drástica.

Ahora el campo firstName no está ordenado ni es un campo clave, por lo que una búsqueda binaria es imposible, y los valores no son únicos, por lo que la tabla requerirá buscar hasta el final para acceder a un N = 1,000,000bloque exacto . Es esta situación la que la indexación pretende corregir.

Dado que un registro de índice contiene solo el campo indexado y un puntero al registro original, es razonable pensar que será más pequeño que el registro de campo múltiple al que apunta. Por lo tanto, el índice en sí requiere menos bloques de disco que la tabla original, por lo que requiere menos accesos de bloque para iterar. El esquema de un índice en el campo firstName se describe a continuación;

Nombre del campo Tipo de datos Tamaño en el disco
firstName Char (50) 50 bytes
(puntero de registro) 4 bytes especiales

Nota : Los punteros en MySQL tienen 2, 3, 4 o 5 bytes de longitud, dependiendo del tamaño de la tabla.

Ejemplo 2 - indización

Dada nuestra base de datos de muestra de r = 5,000,000registros con una longitud de registro de índice de R = 54bytes y utilizando los B = 1,024bytes de tamaño de bloque predeterminados . El factor de bloqueo del índice serían los bfr = (B/R) = 1024/54 = 18registros por bloque de disco. El número total de bloques necesarios para mantener el índice es N = (r/bfr) = 5000000/18 = 277,778bloques.

Ahora una búsqueda con el campo firstName puede utilizar el índice para aumentar el rendimiento. Esto permite una búsqueda binaria del índice con un promedio de log2 277778 = 18.08 = 19accesos de bloque. Para encontrar la dirección del registro real, que requiere un acceso de bloque adicional para leer, llevando el total a los 19 + 1 = 20accesos de bloque, muy lejos de los 1,000,000 de bloque de accesos requeridos para encontrar una coincidencia firstName en la tabla no indexada.

¿Cuándo debería usarse?

Dado que crear un índice requiere espacio de disco adicional (277,778 bloques adicionales del ejemplo anterior, un ~ 28% de aumento), y que demasiados índices pueden causar problemas derivados de los límites de tamaño de los sistemas de archivos, se debe pensar cuidadosamente para seleccionar el correcto campos para indexar

Dado que los índices solo se utilizan para acelerar la búsqueda de un campo coincidente dentro de los registros, es lógico que los campos de indexación utilizados solo para la salida sean simplemente un desperdicio de espacio en disco y tiempo de procesamiento al realizar una operación de inserción o eliminación, y así debería ser evitado. También dada la naturaleza de una búsqueda binaria, la cardinalidad o unicidad de los datos es importante. La indexación en un campo con una cardinalidad de 2 dividiría los datos a la mitad, mientras que una cardinalidad de 1,000 devolvería aproximadamente 1,000 registros. Con una cardinalidad tan baja, la efectividad se reduce a un tipo lineal, y el optimizador de consultas evitará usar el índice si la cardinalidad es menor al 30% del número de registro, lo que hace que el índice sea una pérdida de espacio.

Respondida el 04/08/2008 a las 11:41
fuente por usuario

votos
168

La primera vez que leí esto, fue muy útil para mí. Gracias.

Desde entonces he adquirido una idea acerca de la desventaja de la creación de índices: si se escribe en una tabla ( UPDATEo INSERT IGNORE ) con un índice, que tiene en realidad dos operaciones de escritura en el sistema de archivos. Uno para los datos de tabla y otra para los datos de índice (y la reordenación de la misma (y - si agrupado - la reordenación de los datos de la tabla)). Si tabla y el índice se encuentran en el mismo disco duro esto cuesta más tiempo. Así, una tabla sin un índice (un montón), permitiría operaciones de escritura rápidos. (si tuviera dos índices que terminaría con tres operaciones de escritura, y así sucesivamente)

Sin embargo, la definición de dos lugares diferentes en dos discos duros diferentes para datos de índice y los datos de la tabla pueden reducir / eliminar el problema del aumento del costo de tiempo. Esto requiere la definición de los grupos de archivos adicionales con acuerdo archivos en el disco y definición de ubicación de la tabla / índice duros deseados según se desee.

Otro problema con los índices es su fragmentación con el tiempo a medida que se inserta datos. REORGANIZEle ayuda, se debe escribir rutinas de tener que hacer.

En ciertos escenarios de un montón es más útil que una tabla con índices,

por ejemplo: - Si usted tiene un montón de escrituras que rivalizan pero sólo uno lee todas las noches fuera del horario comercial para la presentación de informes.

Además, una diferenciación entre los índices agrupados y no agrupados es bastante importante.

Me ayudó: - ¿Qué agrupados y no agrupados índice significan realmente?

Respondida el 30/04/2013 a las 15:31
fuente por usuario

votos
124

Un índice es sólo una estructura de datos que hace que la búsqueda más rápida para una columna específica de una base de datos. Esta estructura es por lo general un árbol B o una tabla hash, pero puede ser cualquier otra estructura lógica.

Para obtener más información, recomiendo: ¿Cómo funcionan los índices de bases de datos? Y, ¿cómo ayudan índices?

Respondida el 20/02/2014 a las 15:40
fuente por usuario

votos
86

Ahora, supongamos que queremos ejecutar una consulta para encontrar todos los detalles de cualquier empleado que se denominan 'Abc'?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

¿Qué pasaría sin un índice?

Software de base de datos Literalmente, tendría que buscar en cada hilera de la tabla Employee para ver si el Employee_Name para esa fila es 'Abc'. Y, porque queremos que cada fila con el nombre de 'Abc' en su interior, no podemos simplemente dejar de buscar una vez que nos encontramos con una sola fila con el nombre de 'Abc', porque podría haber otras filas con el nombre Abc . Así, cada fila hasta la última fila se debe buscar - lo que significa miles de filas en este escenario tendrá que ser examinado por la base de datos para encontrar las filas con el nombre 'Abc'. Esto es lo que se llama un escaneo completo de tabla

Como un índice de base de datos puede mejorar el rendimiento

El punto de tener un índice es acelerar las consultas de búsqueda, esencialmente reduciendo el número de registros / filas de una tabla que necesitan ser examinados. Un índice es una estructura de datos (lo más comúnmente un árbol B-) que almacena los valores para una columna específica en una tabla.

¿Cómo funciona el índice de los árboles B?

Los árboles B- razón son la estructura de datos más popular para los índices se debe al hecho de que son eficiente en el tiempo - porque look-up, supresiones e inserciones de todo se puede hacer en el tiempo logarítmica. Y, otra razón importante árboles B- son más comúnmente utilizados se debe a que los datos que se almacena en el interior del árbol B- puede ser ordenada. El RDBMS típicamente determina qué estructura de datos en realidad se utiliza para un índice. Sin embargo, en algunos escenarios con ciertos de RDBMS, en realidad se puede especificar la estructura de datos que desea que su base de datos utilizar cuando se crea el propio índice.

¿Cómo funciona un índice de tabla hash?

La razón se utilizan índices hash se debe a que las tablas hash son extremadamente eficiente cuando se trata de simplemente búsqueda de valores. Por lo tanto, las consultas que comparan la igualdad en una cadena pueden recuperar valores muy rápido si utilizan un índice hash.

Por ejemplo, la consulta hemos comentado anteriormente podría beneficiarse de un índice hash creado en la columna de la Employee_Name. La forma en que un índice hash funcionaría es que el valor de la columna será la llave en la tabla hash y el valor real asignada a esa tecla no sería más que un puntero a los datos de la fila en la tabla. Desde una tabla hash es básicamente una matriz asociativa, una entrada típica sería algo como “ABC => 0x28939", donde 0x28939 es una referencia a la fila de la tabla, donde Abc se almacena en la memoria. Mirando hacia arriba un valor como “ABC” en un índice de la tabla hash y volver una referencia a la fila en la memoria es, obviamente, mucho más rápido que el escaneo de la tabla para encontrar todas las filas con un valor de “ABC” en la columna de la Employee_Name.

Las desventajas de un índice hash

Las tablas hash no se ordenan las estructuras de datos, y hay muchos tipos de consultas, que índices hash ni siquiera pueden ayudar. Por ejemplo, supongamos que desea averiguar todos los empleados que tienen menos de 40 años de edad. ¿Cómo pudiste hacer eso con un índice de tabla hash? Bueno, no es posible debido a una tabla hash sólo es bueno para buscar pares de valores clave - lo que significa que las consultas que comprobar la igualdad

¿Qué es exactamente dentro de un índice de base de datos? Así pues, ahora usted sabe que se crea un índice de base de datos en una columna de una tabla, y que el índice almacena los valores de esa columna específica. Sin embargo, es importante entender que un índice de base de datos no almacena los valores en las otras columnas de la misma tabla. Por ejemplo, si se crea un índice en la columna de la Employee_Name, esto significa que los valores de las columnas y Employee_Age Employee_Address no se almacenan también en el índice. Si nosotros nos limitamos a almacenar todas las otras columnas en el índice, entonces sería igual que la creación de otra copia de toda la tabla - que ocupan demasiado espacio y sería muy ineficiente.

¿Cómo sabe una base de datos al utilizar un índice? Cuando una consulta como “SELECT * FROM Empleado DONDE Employee_Name = 'Abc'” se ejecute, la base de datos va a comprobar para ver si hay un índice en la columna (s) que se está consultando. Suponiendo que la columna de la Employee_Name tiene un índice creado en él, la base de datos tendrá que decidir si realmente tiene sentido utilizar el índice para encontrar los valores que se busca - porque hay algunos escenarios en los que es en realidad menos eficiente de utilizar el índice de base de datos y más eficiente sólo para escanear toda la tabla.

¿Cuál es el costo de tener un índice de base de datos?

Se ocupa espacio - y cuanto mayor sea su mesa, cuanto mayor sea el índice. Otro impacto en el rendimiento con índices es el hecho de que cada vez que agregar, eliminar o actualizar filas en la tabla correspondiente, las mismas operaciones tendrán que ser hecho a su índice. Recuerde que un índice debe contener la misma hasta los datos minuto como lo que está en la columna de la tabla (s) que el índice cubre.

Como regla general, un índice sólo se debe crear en una mesa si los datos de la columna indexada se consultan con frecuencia.

Ver también

  1. Lo columnas generalmente hacen buenos índices?
  2. ¿Cómo funcionan los índices de bases de datos
Respondida el 13/08/2016 a las 15:36
fuente por usuario

votos
64

Clásico ejemplo "Índice de Libros"

Considere un "libro" de 1000 páginas, dividido por 100 secciones, cada sección con X páginas.

Simple, ¿eh?

Ahora, sin una página de índice, para encontrar una sección particular que comienza con la letra "S", que no tienen otra opción que la exploración a través de todo el libro. es decir: páginas 1000

Pero con una página de índice al principio, usted está allí. Y más, para leer cualquier sección particular que importa, sólo tiene que mirar por encima de la página de índice, una y otra vez, cada vez. Después de encontrar el índice coincidente se puede saltar de manera eficiente a la sección saltándose otras secciones.

Pero entonces, además de 1000 páginas, necesitará otro ~ 10 páginas para mostrar la página de índice, de forma TOTALMENTE 1.010 páginas.

Por lo tanto, el índice es una sección separada que almacena valores de la columna indexada + puntero a la fila indexada en una forma ordenada para una eficiente look-ups.

Las cosas son simples en las escuelas, ¿no? :PAG

Respondida el 23/04/2017 a las 11:43
fuente por usuario

votos
40

Descripción sencilla !!!!!!!!!!

El índice no es más que una estructura de datos que almacena los valores de una columna específica en una tabla. Se crea un índice en una columna de una tabla.

Ejemplo, tenemos una tabla de base de datos llamada del usuario con tres columnas - nombre, edad y dirección. Supongamos que la tabla usuario tiene miles de filas.

Ahora, supongamos que queremos ejecutar una consulta para encontrar todos los detalles de los usuarios que llevan el nombre 'John'. Si corremos la siguiente consulta.

SELECT * FROM User 
WHERE Name = 'John'

El software de base de datos Literalmente, tendría que mirar cada hilera en la tabla de usuario para ver si el nombre de esa fila es 'John'. Esto llevará un largo tiempo.
Aquí es donde nos ayuda índice "índice se utiliza para acelerar las consultas de búsqueda, esencialmente reduciendo el número de registros / filas de una tabla que necesita ser examinado".
Cómo crear un índice

CREATE INDEX name_index
ON User (Name)

Un índice consta de valores de columna (por ejemplo John) de una mesa, y que esos valores se almacenan en una estructura de datos.
Así que ahora la base de datos va a utilizar el índice para encontrar empleados llamado John porque el índice de suponer, serán ordenados alfabéticamente por el nombre del usuario. Y, debido a que está ordenada, significa la búsqueda de un nombre es mucho más rápido debido a que todos los nombres que comienzan con una “J” serán uno al lado del otro en el índice!

Respondida el 01/08/2016 a las 22:30
fuente por usuario

votos
18

Sólo una sugerencia rápida .. Como los costos de indexación que las escrituras adicionales y espacio de almacenamiento, por lo que si su aplicación requiere una operación más inserción / actualización, es posible que desee utilizar las tablas sin índices, pero si se requiere más operaciones de recuperación de datos, hay que ir por indexada mesa.

Respondida el 14/01/2015 a las 06:44
fuente por usuario

votos
14

Basta pensar en Índice de base de datos como índice de un libro. Si usted tiene un libro sobre perros y usted quiere encontrar una información sobre digamos, pastores alemanes, usted podría, por supuesto, dar la vuelta a través de todas las páginas del libro y encontrar lo que está buscando, pero esto por supuesto es mucho tiempo y no muy rápido. Otra opción es que, usted podría ir a la sección de índice del libro y luego encontrar lo que busca mediante el nombre de la entidad que busca (en este caso, los pastores alemanes) y también mirar el número de página para encontrar rápidamente lo que busca. En la base de datos, el número de página que se conoce como un puntero que dirige la base de datos a la dirección en el disco donde se encuentra la entidad. Usando la misma analogía pastor alemán, podríamos tener algo como esto ( “pastor alemán”,

En resumen, un índice es una estructura de datos que almacena los valores de una columna específica en una tabla con el fin de acelerar la búsqueda.

Respondida el 21/12/2016 a las 14:16
fuente por usuario

votos
7

Índice de SQL es algo relacionado con speedup la búsqueda en la base de datos SQL. Índice permite programador para recuperar datos de la base de datos muy rápido. Supongamos que usted es un estudiante o algún lector de libros. Su libro contiene 50.000 páginas. Primer día de leer algún tema día siguiente “ABC” que desea leer algún otro tema “xyz”. nunca va a salir manualmente a través de página por página. Lo que va a hacer en esta situación es el uso de índice de libro para mirar el algún tema específico y luego saltar directamente a su tema. Índice salvó su gran cantidad de tiempo para buscar tema. Lo mismo en el índice de SQL, índice permite buscar millones de registros de base de datos muy rápidamente.

Respondida el 15/02/2018 a las 07:17
fuente por usuario

votos
2

Un índice de base de datos es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos a costa de escrituras adicionales y espacio de almacenamiento para mantener la estructura de datos del índice. Los índices se utilizan para localizar rápidamente los datos sin tener que buscar en cada fila de una tabla de base de datos cada vez que se accede a una tabla de base de datos. Los índices pueden ser creados usando una o más columnas de una tabla de base de datos, proporcionando la base para ambas búsquedas aleatorias rápidas y el acceso eficiente de los registros ordenados.

Respondida el 09/07/2018 a las 05:33
fuente por usuario

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more