Red de conocimiento informático - Aprendizaje de programación - Algoritmo de consulta de conexión de conexión de bucle anidado (BNL) de bloque MySQL II

Algoritmo de consulta de conexión de conexión de bucle anidado (BNL) de bloque MySQL II

El proceso de escanear una tabla consiste en realidad en cargar la tabla desde el disco a la memoria y luego comparar si se cumplen las condiciones coincidentes desde la memoria. Sin embargo, es posible que la memoria no pueda almacenar todos los registros de la tabla, por lo que al escanear los registros anteriores de la tabla, es posible que los registros posteriores aún estén en el disco y, al escanear los registros posteriores, es posible que la memoria no sea suficiente. , por lo que los registros anteriores deben liberarse de la memoria. Como se mencionó anteriormente, al unir dos tablas usando el algoritmo SNLJ, se accede a la tabla controlada varias veces. ¡La cantidad de veces que se accede a la tabla controlada depende de la cantidad de registros en el conjunto de resultados devuelto por la tabla controladora! Si el volumen de datos de la tabla controlada es grande y no se puede acceder a él mediante un índice, entonces equivale a leer la tabla varias veces desde el disco, lo cual es muy costoso en términos de E/S, por lo que debemos encontrar una manera de haga esto:

Cuando la cantidad de datos en la tabla controlada es grande, cada vez que se accede a la tabla controlada, los registros de la tabla controlada se cargarán en la memoria y cada registro en la memoria se compararse con el resultado de la tabla conducida coincide con un registro en el conjunto. Cada registro en la memoria solo puede coincidir con un registro en el conjunto de resultados de la tabla del controlador y luego se borra de la memoria. Luego tome otro registro del conjunto de resultados de la tabla de controladores y vuelva a cargar la tabla de controladores en la memoria, y así sucesivamente. La tabla de controladores debe cargarse desde el disco en la memoria tantas veces como registros haya en el conjunto de resultados de la tabla de controladores. .

En otras palabras, cuando hay un índice, MySQL intentará utilizar el algoritmo de unión de bucle anidado de índice. En algunos casos, es posible que la columna de unión no tenga un índice, por lo que la elección de MySQL en este momento es definitivamente. El algoritmo de unión de bucle anidado simple no es el primero en introducirse. El algoritmo SNLJ es el algoritmo de unión más lento. Después de todo, ¡es un producto cartesiano!

El algoritmo de unión de bucle anidado en bloque es una mejora del algoritmo de unión de bucle anidado simple. Reduce el número de escaneos de la tabla interna e incluso se puede usar con el algoritmo de unión hash, que solo escanea el interior. tabla. La tabla de capas se escanea una vez. Utiliza Join Buffer para reducir la cantidad de bucles internos para leer la tabla.

Acerca del búfer de unión/p/3c0816862cc9

Se puede ver que, en comparación con el algoritmo de unión de bucle anidado simple, el algoritmo de unión de bucle anidado en bloque solo tiene una llamada unión más. área de búfer, entonces ¿por qué reduce el número de escaneos de la tabla interna? La siguiente figura explica el algoritmo de unión de bucle anidado en bloque mejor que un diagrama de flujo:

Como puede ver, el búfer de unión se utiliza para almacenar en caché las columnas necesarias para la unión (así que, nuevamente, es mejor no hacerlo). * Como lista de consulta, solo las columnas que nos interesan se colocan en la lista de consulta, que también puede estar en el búfer de unión (así que recuérdeles a todos nuevamente que es mejor no usar * como lista de consulta, solo las columnas que nos interesan) about se colocan en la lista de consultas) Bien, esto también puede estar en el búfer de unión (así que les recuerdo nuevamente, es mejor no usar * como lista de consultas, solo coloque las columnas que nos interesan en la lista de consultas, así también puede estar en el búfer de unión), es mejor no usarlo * Como lista de consulta, simplemente coloque las columnas que nos interesan en la lista de consulta, para que podamos colocar más registros en el búfer de conexión), y luego realice La conexión con los datos en la tabla interna en el proceso por lotes del búfer de conexión. Como se puede ver en la figura anterior, la conexión para registrar r1, r2...rT solo necesita escanear la tabla interna una vez. Puede almacenar en caché todas las columnas externas, luego la conexión solo necesita escanear la tabla externa una vez, lo que mejorará en gran medida la eficiencia de la conexión.

Bloquear la sobrecarga de unión del bucle anidado

Bloquear el rendimiento del bucle anidado. join evita en gran medida el escaneo de la tabla interna. Si el búfer de unión puede almacenar en caché los datos de la tabla externa, entonces la tabla interna El escaneo solo necesita una vez, lo cual es muy similar a la unión hash. Sin embargo, la unión de bucle anidado en bloque todavía no lo hace. resuelva el problema del número de comparaciones de uniones. En resumen, la comparación de costos del algoritmo de unión se muestra a continuación:

Impacto de la unión del bucle anidado del bloque

Cuando se utiliza el bloque anidado. Algoritmo de unión en bucle (BNL), aún es posible realizar múltiples escaneos de la tabla controlada (a pesar de estar controlada, es posible que la mayoría de los datos de campo relevantes en la tabla ya estén en el búfer de unión).

Si la tabla controlada es una tabla grande de datos fríos, además de causar una alta presión de E/S, ¡también tendrá un impacto grave en el grupo de búfer!

Si comprende el algoritmo LRU de InnoDB, lo sabrá porque InnoDB optimiza el algoritmo LRU del grupo de búfer, es decir, la primera vez que se lee la página de datos del disco a la memoria. , la página de datos se colocará primero en el área anterior. Si ya no se accede a la página después de 1 segundo, no se moverá al encabezado de la tabla LRU, lo que tiene poco impacto en la tasa de aciertos del grupo de búfer.

Sin embargo, si la declaración de unión que utiliza el algoritmo BNL escanea la tabla fría varias veces y el tiempo de ejecución de la declaración excede 1 segundo, cuando vuelve a escanear la tabla fría, las páginas de datos de la tabla fría La tabla se moverá al encabezado de la tabla LRU. Esta situación es equivalente al hecho de que el volumen de datos de la tabla fría es menos de 3/8 de todo el grupo de búfer y se puede colocar completamente en el área anterior. Si la mesa fría es muy grande, surgirá otra situación: las páginas de datos a las que normalmente accede el negocio no tienen posibilidad de ingresar a la nueva área. (Dado que no queda espacio restante en el grupo de búfer para aumentar aún más la E/S del disco, las consultas SQL comerciales normales se ralentizarán)

Debido al mecanismo de optimización, una página de datos a la que se accede normalmente tarda 1 segundo en ingresar al grupo joven. Visitar nuevamente más tarde. Sin embargo, dado que nuestra declaración de unión recorre el disco y elimina las páginas de memoria, lo más probable es que las páginas de datos que van a la región anterior se eliminen en 1 segundo. Por lo tanto, durante este tiempo, el grupo de búfer de la instancia de MySQL no elimina razonablemente páginas en la nueva región.

En otras palabras, ambas situaciones afectarán el funcionamiento normal del buffer pool. Las operaciones de unión de tablas grandes tienen un impacto en IO, pero el impacto en IO finaliza después de que se ejecuta la declaración. Sin embargo, el impacto en el grupo de búfer es persistente y depende de solicitudes de consulta posteriores para recuperar lentamente la tasa de aciertos de memoria.

Para minimizar este impacto, podría considerar aumentar el valor de join_buffer_size para reducir la cantidad de escaneos de la tabla de controladores.

En otras palabras, el impacto del algoritmo BNL en el sistema incluye principalmente tres aspectos: puede escanear la tabla controlada varias veces, ocupando así recursos de E/S del disco y necesita realizar comparaciones M*N para determinar; las condiciones de conexión (M y N son el número de filas en las dos tablas respectivamente). Si es una tabla grande, esto ocupará muchos recursos de la CPU y puede provocar que se eliminen datos activos del grupo de búfer, lo que afectará; la tasa de aciertos de la memoria. Tasa de aciertos de la memoria.

Entonces, suponiendo que todas las tablas de controladores estén en la memoria, ¿existe una diferencia de rendimiento entre los algoritmos SNLJ y BNL? Por supuesto, debido a que el algoritmo SNLJ accede naturalmente a los datos de la tabla del controlador varias veces, es más fácil contaminar el grupo de búfer colocando estas páginas de datos al principio del grupo de búfer. Además, aunque los datos de la tabla controlada están en la memoria, cada "operación de siguiente registro" de búsqueda es similar a una operación de puntero. join_buffer en el algoritmo BNL es una matriz y su costo de recorrido es mucho menor que leer datos de la tabla del controlador y atravesar en join_buffer.

Configuraciones relacionadas con BNL

mysql activa BNL de forma predeterminada

Activa/desactiva BNL

1. Bucle anidado de bloque de caché Se une a través un caché Para varios datos, almacene en caché las columnas involucradas en la consulta en el búfer de unión, luego obtenga los datos del búfer de unión y haga coincidir los datos en la tabla interna en lotes.

2. ¿Cuándo utilizar BNL? Cuando no hay índice en el campo relacionado de la tabla interna, cuando no se usa la conexión de bucle anidado de índice, se usa la conexión de bucle anidado de bloque de forma predeterminada.

3. Conceptos relacionados con los buffers de conexión:

Continuará. . . . .

4. Para utilizar el algoritmo de conexión de bucle anidado en bloque, debe activar la configuración block_nested_loop de optimizador_switch en la configuración de administración del optimizador.