¿Cómo funciona la indexación de bases de datos?
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 independiente de la base de datos?
Para obtener información sobre las consultas para indexar un campo, consulte Cómo indexo una columna de base de datos.
10 answers
¿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 enlazadas; ambos contienen una sección de datos, un puntero a la ubicación del siguiente nodo (o bloque), y ambos no necesitan almacenarse contiguamente.
Debido al hecho de que un número de 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/2 accesos de bloque (en promedio), donde N es el número de bloques que abarca la tabla. Si ese campo no es un campo clave (es decir, no contiene entradas únicas), entonces se debe buscar todo el espacio de tabla en N accesos de bloque.
Mientras que con un campo ordenado, se puede usar una Búsqueda Binaria, que tiene log2 N accesos de bloque. También dado que los datos se ordenan dado un campo no clave, el resto de la tabla no necesita ser buscado por valores duplicados, una vez que se encuentra un valor más alto. Por lo tanto, el aumento del 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 ordena, lo que permite realizar búsquedas binarias en se.
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 muchos campos dentro de la misma tabla están indexados.
¿Cómo funciona?
En primer lugar, vamos a esbozar un esquema de tabla de base de datos de ejemplo;
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 : char se utilizó en lugar de varchar para permitir una tamaño exacto 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 ordenada) y otra que utiliza el FirstName (un campo sin clasificar sin clave).
Ejemplo 1 - ordenados vs sin clasificar campos
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 son almacenado en una tabla usando el motor MyISAM que está usando 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 id requeriría un promedio de N/2 = 500,000 accesos en bloque para encontrar un valor, dado que el campo id es un campo clave. Pero dado que el campo id también está ordenado, se puede realizar una búsqueda binaria que requiera un promedio de bloque log2 1000000 = 19.93 = 20 acceso. Al instante 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, ni los valores son únicos, y por lo tanto la tabla requerirá buscar hasta el final un bloque de accesos N = 1,000,000 exacto. Es esta situación la que la indexación pretende corregir.
Dado que un registro í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 campo múltiple 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 accesos de bloque para iterar. El esquema para un índice en el campo FirstName 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 - la indexación
Dada nuestra muestra base de datos de registros r = 5,000,000 con una longitud de registro de índice de R = 54 bytes y utilizando 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 usando el campo FirstName puede utilizar el índice para aumentar el rendimiento. Esto permite una búsqueda binaria del índice con un promedio de accesos de bloque log2 277778 = 18.08 = 19. Para encontrar la dirección de la real registro, que requiere un acceso de bloque adicional para leer, llevando el total a 19 + 1 = 20 accesos de bloque, muy lejos de los 1.000.000 de accesos de bloque necesarios para encontrar una coincidencia FirstName en la tabla no indexada.
¿Cuándo se debe utilizar?
Dado que la creación de un índice requiere espacio adicional en disco (277,778 bloques extra 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, cuidado el pensamiento debe ser usado para seleccionar los campos correctos a indexar.
Dado que los índices solo se utilizan para acelerar la búsqueda de un campo coincidente dentro de los registros, es lógico pensar que los campos de indexación utilizados solo para la salida serían simplemente una pérdida de espacio en disco y tiempo de procesamiento cuando se realiza una operación de inserción o eliminación, y por lo tanto debe evitarse. También dada la naturaleza de una búsqueda binaria, la cardinalidad o singularidad de los datos es importante. Indexación en un campo con una cardinalidad de 2 dividiría los datos por 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 clasificación lineal, y el optimizador de consultas evitará usar el índice si la cardinalidad es inferior al 30% del número de registro, haciendo que el índice sea un desperdicio de espacio.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-10 10:40:54
La primera vez que leí esto fue muy útil para mí. Agradecer.
Desde entonces obtuve una idea sobre la desventaja 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 el recurso de los mismos (y - si está agrupado - el recurso de los datos de la tabla)). Si la tabla y el índice se encuentran en el mismo disco duro, esto cuesta más tiempo. Así un tabla sin un índice (un montón) , permitiría operaciones de escritura más rápidas. (si tuvieras dos índices terminarías con tres operaciones de escritura, y así sucesivamente)
Sin embargo, definir dos ubicaciones diferentes en dos discos duros diferentes para los datos de índice y los datos de tabla puede disminuir/eliminar el problema del aumento del costo de tiempo. Esto requiere la definición de grupos de archivos adicionales con archivos de acuerdo en los discos duros deseados y la definición de la ubicación de la tabla/índice como se desee.
Otro el problema con los índices es su fragmentación a lo largo del tiempo a medida que se insertan los datos. REORGANIZE ayuda, debes escribir rutinas para hacerlo.
En ciertos escenarios un montón es más útil que una tabla con índices,
E. g:- Si tienes muchas escrituras rivales, pero solo una lectura nocturna fuera del horario comercial para informar.
Además, una diferenciación entre índices agrupados y no agrupados es bastante importante.
Me ayudó: - ¿Qué hacer agrupado y No agrupado índice realmente significa?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:36
Un índice es solo una estructura de datos que hace que la búsqueda de una columna específica en una base de datos sea más rápida. Esta estructura suele ser un árbol b o una tabla hash, pero puede ser cualquier otra estructura lógica.
Para más información, recomiendo: ¿Cómo funcionan los índices de base de datos? Y, ¿cómo ayudan los índices?
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-22 12:10:49
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 comienza 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 importe, solo tiene que echar un vistazo la página de índice, una y otra vez, cada vez. Después de encontrar el índice correspondiente, puede saltar eficientemente a la sección 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 que 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? : P
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-10 11:14:17
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 base de datos literalmente tendría que mirar cada fila en la tabla Employee para ver si el Employee_Name para esa fila es 'Abc'. Y, debido a que queremos cada fila con el nombre' Abc 'dentro de ella, no podemos dejar de buscar una vez que encontramos solo una fila con el nombre 'Abc', porque podría haber otras filas con el nombre Abc. Por lo tanto, cada fila hasta la última fila debe buscarse, lo que significa que miles de filas en este escenario tendrán que ser examinadas por la base de datos para encontrar las filas con el nombre 'Abc'. Esto es lo que se llama un análisis completo de la tabla
Cómo un índice de base de datos puede ayudar al rendimiento
El objetivo de tener un índice es acelerar las consultas de búsqueda al reducir esencialmente el número de registros/filas en una tabla que deben examinarse. 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 al hecho de que son eficientes en el tiempo, porque las búsquedas, eliminaciones e inserciones se pueden hacer en tiempo logarítmico. Y, otra razón importante por la que los árboles B se usan más comúnmente es porque los datos que se almacenan dentro del árbol B se pueden ordenar. El RDBMS normalmente determina qué estructura de datos se utiliza realmente para un índice. Pero, en algunos escenarios con ciertos RDBMS, puede especificar qué estructura de datos desea que use su base de datos cuando cree el índice en sí.
¿Cómo funciona un índice de tabla hash?
La razón por la que se utilizan í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ápido si usan un índice hash.
Por ejemplo, la consulta que discutimos anteriormente podría beneficiarse de un índice hash creado en la columna Employee_Name. La forma en que funcionaría un índice hash 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 algo así como "Abc => 0x28939", donde 0x28939 es una referencia a la fila de la tabla donde Abc se almacena en 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 Hash no son estructuras de datos ordenadas, y hay muchos tipos de consultas con las que los índices hash ni siquiera pueden ayudar. Por ejemplo, supongamos que desea averiguar todos los empleados menores de 40 años. ¿Cómo pudiste 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 comprueban la igualdad
¿Qué hay exactamente dentro de un índice de base de datos? Por lo tanto, ahora usted 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 hace no almacenar 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 también se almacenan en el índice. Si hicimos almacenar todas las otras columnas en el índice, entonces sería como crear otra copia de toda la tabla – que ocuparía demasiado espacio y sería muy ineficiente.
¿Cómo sabe una base de datos cuándo usar un índice? Cuando una consulta como "SELECT * FROM Employee WHERE Employee_Name = 'Abc' " se ejecuta, la base de datos comprobará si hay un índice en la columna(s) que se está consultando. Suponiendo que la columna Employee_Name tiene un índice creado en ella, 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 en realidad es menos eficiente usar el índice de la base de datos, y más eficiente solo para escanear tabla.
¿Cuál es el costo de tener un índice de base de datos?
Ocupa espacio – y cuanto más grande sea tu tabla, más grande será tu í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, las mismas operaciones tendrán que realizarse en su índice. Recuerde que un índice debe contener los mismos datos hasta el minuto que lo que está en la(s) columna (s) de la tabla que cubre el índice.
Como regla general, un index solo se debe crear en una tabla si los datos de la columna indexada se consultarán con frecuencia.
Véase también
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:36
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.
Ejemplo, tenemos una tabla de base de datos llamada Usuario con tres columnas: Nombre, Edad y Dirección. Supongamos que la tabla User 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 mirar cada fila en la tabla de usuarios para ver si el nombre de esa fila es 'John'. Esto llevará mucho tiempo.
Aquí es donde index nos ayuda "index se utiliza para acelerar las consultas de búsqueda al reducir esencialmente el número de registros / filas en una tabla que debe examinarse".
Cómo crear un índice
CREATE INDEX name_index
ON User (Name)
Un índice consiste en valores de columna (por ejemplo: John) de una tabla, y que esos valores se almacenan en un estructura de datos.
Así que ahora la base de datos utilizará el índice para encontrar empleados llamados John porque el índice presumiblemente 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 comienzan con una "J" estarán uno al lado del otro en el índice!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-04 10:29:34
Solo una sugerencia rápida.. Como la indexación le cuesta escrituras y espacio de almacenamiento adicionales, por lo que si su aplicación requiere más operaciones de inserción/actualización, es posible que desee usar tablas sin índices, pero si requiere más operaciones de recuperación de datos, debe optar por tabla indexada.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-14 06:44:51
Solo piense en el Índice de la Base de datos como el Índice de un libro. Si tiene un libro sobre perros y desea encontrar información sobre, digamos, 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 es muy rápido. Otra opción es que, simplemente puede ir a la sección de Índice del libro y luego encontrar lo que está buscando utilizando el Nombre de la entidad que está buscando ( en este caso, alemán Pastores) y también mirando el número de página para encontrar rápidamente lo que está buscando. En la 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 del Pastor Alemán, podríamos tener algo como esto ("Pastor Alemán", 0x77129) donde 0x77129 es la dirección en el disco donde se almacenan los datos de la fila para el Pastor Alemán.
En resumen, un índice es una estructura de datos que almacena los columna específica en una tabla para acelerar la búsqueda de consulta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-21 17:16:02
SQL index es algo relacionado con acelerar la búsqueda en la base de datos SQL. Index permite al programador 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. El primer día que lees un tema " ABC "al día siguiente quieres leer otro tema "xyz". nunca pasará manualmente página por página. Lo que va a hacer en esta situación es utilizar el índice del libro para mirar el algún tema específico y luego Saltar directamente a su tema. Índice guardado tienes mucho tiempo para buscar temas. Lo mismo en SQL Index, Index permite buscar millones de registros muy rápidamente desde la base de datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-15 10:17:05
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 de índice. Los índices se utilizan para localizar datos rápidamente 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 se pueden crear utilizando una o más columnas de una tabla de base de datos, proporcionando la base tanto para búsquedas aleatorias rápidas como para el acceso eficiente de registro.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-09 05:33:17