¿Cuáles son las funciones y beneficios de los índices de bases de datos?

Un índice de base de datos es un identificador adjunto a un campo de tabla para mejorar la velocidad de consulta. Veo que mucha gente entiende el concepto de índice de forma mecánica y piensa que aumentar el índice sólo tiene beneficios y no desventajas. Aquí me gustaría resumir mis notas de estudio de índices anteriores: Primero, entiendo por qué la indexación aumenta la velocidad. Cuando DB ejecuta una declaración Sql, el método predeterminado es escanear toda la tabla de acuerdo con las condiciones de búsqueda. Si se cumplen las condiciones coincidentes, se agrega al conjunto de resultados de búsqueda. Si agregamos un índice a un campo, primero iremos al número de filas en la lista de índice y ubicaremos un valor específico a la vez. Esto reduce en gran medida el número de filas que se atraviesan y coinciden, por lo que la velocidad de la consulta puede ser. mejorado significativamente. Entonces, ¿debería indexarse ​​en cualquier momento? He aquí algunos contraejemplos: 1. Si necesita obtener todos los registros de la tabla cada vez, debe escanear toda la tabla de todos modos, por lo que no tiene sentido agregar un índice. 2. Para campos no únicos, como "género", agregar índices no tiene ningún sentido. 3. Para tablas con pocos registros, agregar índices no optimizará la velocidad, pero desperdiciará espacio de almacenamiento, porque los índices requieren espacio de almacenamiento y existe la desventaja fatal de que cada vez que se ejecuta actualizar/insertar/eliminar, el índice de la El campo será Debe ser recalculado y actualizado. Entonces, ¿cuándo es apropiado agregar un índice? Veamos un ejemplo dado en el manual de Mysql. La siguiente es una declaración SQL: Seleccione C. CompanyID, C. Nombre de la empresa de las empresas C, Usuario U donde C. CompanyID = U.FK_CompanyID y C. Numemployees >= 0 y c.nombre de la empresa Como '% I%' y U . groupid en (Seleccione g . groupid de grupos donde g.group label ='executive') implica la unión de tres tablas, incluidas muchas condiciones de búsqueda, como comparación de tamaños y coincidencia de Me gusta. Sin índices, el número de filas que Mysql necesita ejecutar es 77721876. Después de agregar índices a companyID y groupLabel, la cantidad de filas escaneadas solo necesita 134. En Mysql, puede ver la cantidad de escaneos a través de Explicar Seleccionar. Se puede ver que en el caso de tablas conjuntas y condiciones de búsqueda complejas, la mejora del rendimiento aportada por el índice es mucho más importante que el espacio en disco que ocupa. Entonces, ¿cómo se implementa el índice? La mayoría de los proveedores de bases de datos implementan índices basados ​​en una estructura de datos: un árbol B. Porque las características del árbol B son adecuadas para organizar tablas de búsqueda dinámicas en dispositivos de almacenamiento directo como discos. La definición de árbol B es la siguiente: un árbol B de orden M(M>=3) es un árbol M que satisface las siguientes condiciones: 1. Cada nodo incluye el siguiente rango (J, P0, K1, P1, K2, P2,...KI, PI), donde J es el número de claves y P es el subpuntero. 2. Todos los nodos de las hojas están en el mismo nivel y el número de niveles es igual a la altura del árbol. =j<=m-1 4. Si el árbol no está vacío, la raíz tiene al menos 1 palabra clave; si la raíz no es una hoja, hay al menos 2 subárboles y como máximo M subárboles. Tomando como ejemplo un árbol B con 26 letras en inglés, se puede construir de la siguiente manera: Se puede ver que la complejidad de buscar letras en inglés en este árbol B es solo O (m). grande, es así. Sin embargo, existe otra estructura de datos cuyos números imaginarios son más rápidos que las tablas hash de árbol B. La definición de una tabla hash es la siguiente: Sea U el conjunto de todas las palabras clave posibles, las palabras clave almacenadas reales se denotan por K y |k es mucho más pequeño que |u|. El método hash consiste en asignar U al subíndice de la tabla T [0, m-1] a través de la función hash H, de modo que las palabras clave en U sean variables y el resultado de la operación con H como función es la dirección de almacenamiento del nodo correspondiente. Para que la búsqueda pueda completarse en O(1).

Pero la tabla hash tiene un defecto, que es el conflicto hash, es decir, dos palabras clave calculan el mismo resultado a través de la función hash. Sean myn representan la longitud de la tabla hash y el número de nodos llenos respectivamente, y n/m es el factor de llenado de la tabla hash. Cuanto mayor sea el factor, mayor será la posibilidad de que se produzcan colisiones de hash.

Debido a esta falla, la base de datos no utiliza tablas hash como implementación predeterminada de índices.

Mysql afirma que para mejorar aún más la velocidad de búsqueda, intentará convertir el índice del árbol B basado en disco en un índice hash adecuado basado en el formato de consulta de ejecución. Creo que otros proveedores de bases de datos tendrán estrategias similares. Después de todo, en el campo de batalla de las bases de datos, la velocidad de búsqueda es tan importante como la seguridad de la gestión.