Instrucciones detalladas sobre cómo crear correctamente un índice MySQL
¿Qué es un índice?
La definición oficial de índice de MySQL es: Índice es una estructura de datos que ayuda a MySQL a obtener datos de manera eficiente. Simplemente podemos entenderlo como: la estructura de datos que puede encontrar rápidamente la secuencia. El índice MySQL tiene principalmente dos estructuras: índice de árbol B + e índice Hash. A menos que se especifique lo contrario, el índice al que habitualmente nos referimos generalmente se refiere a un índice organizado en una estructura de árbol B (índice de árbol B +). Los indicadores se muestran en la figura:
El bloque de disco 1 de color azul claro más externo contiene los datos 17 y 35 (azul oscuro) y los punteros P1, P2 y P3 (amarillo). El puntero P1 representa bloques de disco menores de 17, P2 está entre 17 y 35 y P3 apunta a bloques de disco mayores de 35. Los datos reales existen en los nodos cotiledonarios, es decir, las capas inferiores 3, 5, 9, 10 y 13. Los nodos que no son hoja no almacenan datos reales, solo elementos de datos que guían la dirección de búsqueda, como 17 y 35.
Proceso de búsqueda: por ejemplo, para buscar 28 elementos de datos, primero cargue el bloque de disco 1 en la memoria, genere una E/S y utilice la búsqueda binaria para determinar el puntero en P2. Luego se descubrió que entre 26 y 30, el bloque de disco 3 se cargó en la memoria a través de la dirección del puntero P2 y se produjo la segunda E/S. Utilice el mismo método para encontrar el bloque de disco 8 y se producirá la tercera E/S.
La situación real es que las tres capas de árboles B+ anteriores pueden representar millones de datos. Un millón de datos solo tienen tres E/S en lugar de un millón de E/S, por lo que la mejora de tiempo es enorme.
Los índices de tablas de datos pueden mejorar la eficiencia de la recuperación de datos, reducir el costo de IO de la base de datos y reducir el costo de clasificación de la base de datos. Las operaciones de clasificación y agrupación consumen principalmente recursos de CPU y memoria, por lo que hacer un buen uso de los índices en las operaciones de clasificación y agrupación reducirá en gran medida el consumo de recursos de la CPU. Analicemos brevemente cómo crear correctamente un índice de datos MySQL.
¿Cómo determinar si se debe crear un índice?
1. Algunos campos requieren indexación cuando se utilizan con frecuencia como condiciones de consulta.
Todo el mundo debería saber esto, ¿qué es la frecuencia? Análisis completo de todas las declaraciones SQL que ejecuta. Lo mejor es enumerarlos uno por uno. Luego analizamos y descubrimos que algunos de estos campos se usarían en la mayoría de las consultas de declaraciones SQL, por lo que creamos índices decisivamente para ellos.
2. Los campos con poca unicidad no son adecuados para la indexación.
¿Qué es un campo con unicidad pobre? Como el campo de estado y el campo de tipo. Solo se almacenan campos con valores fijos, como el estado de inicio de sesión del usuario, el estado del mensaje, etc. Esto involucra las características del escaneo de índice. Por ejemplo, si busca algunos datos con valores clave A y B a través del índice, encuentra algunos datos coincidentes a través de A, en la página X, y luego continúa escaneando y descubre que los datos que coinciden con A aparecen en la página Y, entonces el El motor de almacenamiento descartará la página X. Página de datos, y luego almacenará los datos de la página Y hasta que encuentre todos los datos correspondientes a A, y luego encuentre los datos correspondientes al campo B en la página X, luego escaneará la página X nuevamente. igual a. Espere, para que la misma página de datos pueda leerse, descartarse y leerse repetidamente, lo que sin duda aumenta en gran medida la carga de IO del motor de almacenamiento.
3. Los campos que se actualizan con demasiada frecuencia no son adecuados para crear índices.
Al crear un índice para un campo, si los datos de este campo se actualizan nuevamente, la base de datos actualizará automáticamente su índice. Por lo tanto, cuando este campo se actualiza con demasiada frecuencia, el índice se actualizará continuamente. y el impacto en el rendimiento se puede imaginar. Los campos que se actualizan después de decenas de búsquedas son más consistentes con las especificaciones de indexación. Sin embargo, si un campo se actualiza varias veces durante el mismo período de tiempo, la decisión no puede indexarlo.
4. Los campos que no aparecen en la condición donde no deben indexarse.
No hay nada que decir al respecto y es inútil si el índice no se utiliza como campo en la condición de consulta.
Resumen