usar - tipos de indices en sql

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

Dado que la indexación es tan importante a medida que su conjunto de datos aumenta de tamaño, ¿puede alguien explicar cómo funciona la indexación a un nivel de base de datos independiente?

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

Ahora, digamos que queremos ejecutar una consulta para encontrar todos los detalles de los empleados que se llaman ''Abc''?

SELECT * FROM Employee WHERE Employee_Name = ''Abc''

¿Qué pasaría sin un índice?

El software de la base de datos literalmente tendría que mirar cada fila en la tabla de Empleados para ver si el Nombre de empleado para esa fila es ''Abc''. Y, como queremos que todas las filas con el nombre ''Abc'' estén dentro, no podemos dejar de buscar una vez que encontremos una sola fila con el nombre ''Abc'', porque podría haber otras filas con el nombre Abc . Por lo tanto, cada fila hasta la última debe ser buscada, lo que significa que la base de datos tendrá que examinar miles de filas en este escenario para encontrar las filas con el nombre ''Abc''. Esto es lo que se llama un escaneo de tabla completa

Cómo un índice de base de datos puede ayudar al rendimiento.

El objetivo principal de tener un índice es acelerar las consultas de búsqueda al reducir esencialmente el número de registros / filas en una tabla que se debe examinar. Un índice es una estructura de datos (más comúnmente un árbol B) que almacena los valores de una columna específica en una tabla.

¿Cómo funciona el índice B-trees?

La razón por la que los árboles B son la estructura de datos más popular para los índices se debe a que son eficientes en el tiempo, ya que las búsquedas, eliminaciones e inserciones se pueden realizar en tiempo logarítmico. Y, otra razón importante por la que los árboles B son más comúnmente usados es porque los datos que están almacenados dentro del árbol B se pueden clasificar. El RDBMS generalmente determina qué estructura de datos se usa realmente para un índice. Pero, en algunos escenarios con ciertos RDBMS, realmente puede especificar qué estructura de datos desea que use su base de datos cuando cree el índice.

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

La razón por la que se utilizan los índices hash es porque las tablas hash son extremadamente eficientes cuando se trata de buscar valores. Por lo tanto, las consultas que comparan la igualdad con una cadena pueden recuperar valores muy rápidamente si usan un índice de hash.

Por ejemplo, la consulta que analizamos anteriormente podría beneficiarse de un índice hash creado en la columna Employee_Name. La forma en que un índice hash funcionaría es que el valor de la columna será la clave en la tabla hash y el valor real asignado a esa clave solo sería un puntero a los datos de la fila en la tabla. Dado que una tabla hash es básicamente una matriz asociativa, una entrada típica se vería como "Abc => 0x28939", donde 0x28939 es una referencia a la fila de la tabla donde Abc se almacena en la memoria. Buscar un valor como "Abc" en un índice de tabla hash y recuperar una referencia a la fila en la memoria es obviamente mucho más rápido que escanear la tabla para encontrar todas las filas con un valor de "Abc" en la columna Employee_Name.

Las desventajas de un índice hash

Las tablas de hash no son estructuras de datos ordenadas, y hay muchos tipos de consultas con las que los índices de hash ni siquiera pueden ayudar. Por ejemplo, suponga que desea conocer a todos los empleados que tienen menos de 40 años de edad. ¿Cómo podrías hacer eso con un índice de tabla hash? Bueno, no es posible porque una tabla hash solo es buena para buscar pares de valores clave, lo que significa consultas que verifican la igualdad

¿Qué es exactamente dentro de un índice de base de datos? Entonces, ahora sabe que un índice de base de datos se crea en una columna en una tabla y que el índice almacena los valores en esa columna específica. Pero, 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 creamos un índice en la columna Employee_Name, esto significa que los valores de las columnas Employee_Age y Employee_Address no se almacenan también en el índice. Si solo almacenáramos todas las demás columnas en el índice, sería como crear otra copia de toda la tabla, lo que ocuparía demasiado espacio y sería muy ineficiente.

¿Cómo sabe una base de datos cuándo usar un índice? Cuando se ejecuta una consulta como "SELECT * FROM Employee WHERE Employee_Name = ''Abc''", la base de datos verificará si hay un índice en la (s) columna (s) consultada (s). Suponiendo que la columna Employee_Name tenga un índice creado, la base de datos tendrá que decidir si realmente tiene sentido usar el índice para encontrar los valores que se buscan, porque hay algunos escenarios en los que es menos eficiente usar el índice de la base de datos , y más eficiente solo para escanear toda la tabla.

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

Ocupa espacio, y cuanto más grande sea su tabla, mayor será su índice. Otro golpe de rendimiento con los índices es el hecho de que cada vez que agregue, elimine o actualice filas en la tabla correspondiente, tendrá que realizar las mismas operaciones en su índice. Recuerde que un índice debe contener los mismos datos actualizados hasta el minuto que figura en la (s) columna (s) de la tabla que cubre el índice.

Como regla general, solo se debe crear un índice en una tabla si los datos en la columna indexada se consultarán con frecuencia.

Ver también

Descripción simple !!!!!!!!!!

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.

Por ejemplo, tenemos una tabla de base de datos llamada Usuario con tres columnas: Nombre, Edad y Dirección. Supongamos que la tabla de usuario tiene miles de filas.

Ahora, digamos que queremos ejecutar una consulta para encontrar todos los detalles de cualquier usuario que se llame ''John''. Si ejecutamos la siguiente consulta.

SELECT * FROM User WHERE Name = ''John''

El software de la base de datos literalmente tendría que ver cada fila en la tabla de Usuarios para ver si el Nombre de esa fila es "John". Esto tomará un largo tiempo.
Aquí es donde el índice nos ayuda a "el índice se utiliza para acelerar las consultas de búsqueda al reducir esencialmente el número de registros / filas en una tabla que debe ser examinada".
Cómo crear un índice

CREATE INDEX name_index ON User (Name)

Un índice consiste en valores de columna (Ej .: John) de una tabla, y esos valores se almacenan en una estructura de datos.
Así que ahora la base de datos usará el índice para encontrar empleados llamados John porque el índice probablemente se ordenará alfabéticamente por el nombre de los Usuarios. Y, debido a que está ordenado, significa que buscar un nombre es mucho más rápido porque todos los nombres que comiencen con una "J" estarán uno junto al otro en el índice.

Ejemplo clásico "Índice en 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 en particular que comience con la letra "S", no tiene otra opción que escanear todo el libro. es decir: 1000 páginas

Pero con una página de índice al principio, estás ahí. Y más, para leer cualquier sección en particular que sea importante, solo necesita revisar la página del índice, una y otra vez, cada vez. Después de encontrar el índice coincidente, puede saltar a la sección de manera eficiente omitiendo otras secciones.

Pero luego, además de 1000 páginas, necesitará otras ~ 10 páginas para mostrar la página de índice, por lo tanto, totalmente 1010 páginas.

Por lo tanto, el índice es una sección separada que almacena los valores de la columna indexada + puntero a la fila indexada en un orden ordenado para búsquedas eficientes.

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

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

Desde entonces, obtuve información sobre el inconveniente de crear índices: si escribe en una tabla ( UPDATE o INSERT ) con un índice, en realidad tiene dos operaciones de escritura en el sistema de archivos. Uno para los datos de la tabla y otro para los datos del índice (y su resurgimiento (y, si se agrupan), el recorte de los datos de la tabla)). Si la tabla y el índice se encuentran en el mismo disco duro, esto cuesta más tiempo. Por lo tanto, una tabla sin un índice (un montón) permitiría operaciones de escritura más rápidas. (Si tuviera dos índices, terminaría con tres operaciones de escritura, y así sucesivamente)

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

Otro problema con los índices es su fragmentación en el tiempo a medida que se insertan los datos. REORGANIZE ayuda, debes escribir rutinas para que se haga.

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

por ejemplo: - Si tiene muchas escrituras rivales, pero solo una vez por la noche, lea fuera del horario laboral para informar.

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

Me ayudó: - ¿Qué significa realmente el índice agrupado y no agrupado?

Simplemente piense en el Índice de base de datos como el Índice de un libro.

Si tiene un libro sobre perros y quiere encontrar información sobre los pastores alemanes, por supuesto, puede hojear todas las páginas del libro y encontrar lo que está buscando, pero esto, por supuesto, consume mucho tiempo y no muy rapido.

Otra opción es que, simplemente puede ir a la sección Índice del libro y luego encontrar lo que está buscando usando el Nombre de la entidad que está buscando (en este caso, Pastores alemanes) y también mirando el número de página para Encuentra rápidamente lo que buscas.

En Base de datos, el número de página 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 con German Shepherd, podríamos tener algo como esto ("German Shepherd", 0x77129) donde 0x77129 es la dirección en el disco donde se almacenan los datos de la fila de German Shepherd.

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

Solo una sugerencia rápida ... Como la indexación le cuesta más escrituras y espacio de almacenamiento, así que si su aplicación requiere más operaciones de inserción / actualización, puede usar tablas sin índices, pero si requiere más operaciones de recuperación de datos, debería ir a la indexación. mesa.

Un índice es solo una estructura de datos que hace más rápida la búsqueda de una columna específica en una base de datos. Esta estructura suele ser un b-tree o una tabla hash, pero puede ser cualquier otra estructura lógica.

¿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 a disco atómico. Los bloques de disco se estructuran de forma muy similar a las listas enlazadas; ambos contienen una sección para datos, un puntero a la ubicación del siguiente nodo (o bloque), y ambos no necesitan ser almacenados de forma contigua.

Debido al hecho de que un número de registros solo se puede ordenar en un campo, podemos afirmar que la búsqueda en un campo que no está ordenado requiere una Búsqueda lineal que requiere accesos de bloque N/2 (en promedio), donde N es el Número de bloques que abarca la tabla. Si ese campo es un campo no clave (es decir, no contiene entradas únicas), se debe buscar todo el espacio de tabla en los accesos N bloque.

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

¿Qué es la indexación?

La indexación es una forma de ordenar un número de registros en múltiples campos. La creación de un índice en un campo en una tabla crea otra estructura de datos que contiene el valor del campo y un puntero al registro al que se refiere. Esta estructura de índice se clasifica, lo que permite realizar búsquedas binarias en ella.

La desventaja de la indexación es que estos índices requieren espacio adicional en el disco, ya que los índices se almacenan juntos en una tabla utilizando 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, vamos a describir un esquema de tabla de base de datos de muestra;

Field name Data type Size on disk id (Primary key) Unsigned INT 4 bytes firstName Char(50) 50 bytes lastName Char(50) 50 bytes emailAddress Char(100) 100 bytes

Nota : se utilizó 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. Ahora se analizará el rendimiento de varias consultas. Se trata de una consulta que utiliza el ID (un campo de clave ordenado) y una que utiliza el primer Nombre (un campo sin clasificar sin clave).

Ejemplo 1 - campos ordenados vs no clasificados

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

Una búsqueda lineal en el campo de identificación requeriría un promedio de N/2 = 500,000 accesos de bloque para encontrar un valor, dado que el campo de identificación es un campo clave. Pero como el campo id también está ordenado, se puede realizar una búsqueda binaria que requiera un promedio de log2 1000000 = 19.93 = 20 accesos de bloque. Al instante podemos ver que esto 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, ni los valores son únicos, y por lo tanto la tabla requerirá una búsqueda al final para un N = 1,000,000 accesos de bloque exactos. 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 lógico pensar que será más pequeño que el registro de campos múltiples al que apunta. Por lo tanto, el índice en sí requiere menos bloques de disco que la tabla original, lo que, por lo tanto, requiere menos bloqueos de acceso para iterar. El esquema para un índice en el campo de primer nombre se describe a continuación;

Field name Data type Size on disk firstName Char(50) 50 bytes (record pointer) Special 4 bytes

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

Ejemplo 2 - indexación

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

Ahora, una búsqueda que utiliza 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 = 19 accesos de bloque. Para encontrar la dirección del registro real, que requiere un acceso de bloque adicional para leer, lo que hace que el total sea de 19 + 1 = 20 accesos de bloque, muy lejos de los 1,000,000 accesos de bloque requeridos para encontrar una coincidencia de primer nombre en la tabla no indexada .

¿Cuándo debería usarse?

Dado que la creación de un índice requiere espacio en disco adicional (277,778 bloques adicionales del ejemplo anterior, un aumento de ~ 28%), y que demasiados índices pueden causar problemas derivados de los límites de tamaño de los sistemas de archivos, debe usarse una reflexión cuidadosa para seleccionar la correcta Campos a indexar.

Dado que los índices solo se usan para acelerar la búsqueda de un campo coincidente dentro de los registros, es lógico pensar que los campos de indexación usados solo para la salida serían simplemente una pérdida de espacio en disco y tiempo de procesamiento al realizar una operación de inserción o eliminación, y por lo tanto debería ser evitado. También dada la naturaleza de una búsqueda binaria, la cardinalidad o singularidad 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 una ordenación lineal, y el optimizador de consultas evitará usar el índice si la cardinalidad es inferior al 30% del número de registro, lo que hace que el índice sea una pérdida de espacio.