La velocidad de consulta de la tabla de conexión MySQL es muy lenta

Problema

Tenemos un SQL para buscar una tabla sin clave principal/única, pero se ejecuta muy lentamente en MySQL 5.7.

Experimento

Creamos un entorno MySQL 5.7 y el paso de compilación se omite aquí.

Escriba un script simple para crear un lote de tablas con y sin claves primarias:

Ejecute el script:

Ahora ejecute el siguiente SQL para ver el efecto :

...

La ejecución tardó 16,80 segundos, lo que se siente muy lento.

Ahora use DBA Sanbanaxe para ver el plan de ejecución:

Se siente un poco mal porque information_schema.columns es una tabla de metadatos y no tiene las estadísticas necesarias.

Entonces, mostremos la advertencia y veamos el SQL reescrito de MySQL:

Formateemos el SQL:

Como puede ver, MySQL convertido

seleccione de A donde A.x no está (seleccione x de B) //subconsultas no asociativas

Convertidas

seleccione de A donde no existe (seleccione 1 de B donde B.x = a. x) //subconsultas no asociativas

Convertidas

seleccione de A donde no existe (seleccione 1 de B donde B.x = a.x ) // Subconsulta correlacionada

Si somos MySQL, podemos usar una estrategia muy simple al ejecutar subconsultas no correlacionadas:

seleccionar de A donde A.x no está (seleccionar x de B donde...)

Seleccione de A donde A.x no está (seleccione x de B donde...) existe (seleccione 1 de B donde B.x = a.x y...) // La subconsulta correlacionada escanea cada fila de la tabla A para rA: escanea la tabla B en busca de la primera fila que satisface la condición de rA.

Obviamente, el costo de escaneo de las subconsultas correlacionadas será mayor que el de las subconsultas no correlacionadas.

Queremos que MySQL "almacene en caché" los resultados de la subconsulta primero (el paso de almacenamiento en caché se llama "materialización"), pero MySQL cree que será más rápido no hacer esto, por lo que debemos darle a MySQL algo de guía.

...

Puedes ver que el tiempo de ejecución pasa a ser 0,67s.

Organización

Nuestros puntos de diagnóstico son los siguientes:

(1. El plan de ejecución no proporciona información válida para la tabla de metadatos en information_schema.

2. Al observar el SQL reescrito por MySQL, suponemos que el optimizador cometió un error

3. Agregamos una sugerencia para indicarle a MySQL que realice el juicio de optimización correcto. >

Pero por ahora, nuestros experimentos se limitan a adivinar, e incluso si acertamos, no podemos hacer un diagnóstico correcto.