Sugerencias de índice de Mariadb: ejemplo de cómo forzar planes de consulta

El optimizador se basa en gran medida en los costos e intentará elegir el plan óptimo para cualquier consulta. Sin embargo, en algunos casos, no tiene suficiente información para elegir un plan perfecto y, en estos casos, es posible que deba proporcionar sugerencias para obligar al optimizador a usar otro plan.

Puede examinar el plan de consulta para SELECT escribiendo EXPLAIN antes de la declaración. A partir de MariaDB 10.0.5, SHOW EXPLAIN muestra el resultado de una consulta en ejecución. En algunos casos, su salida puede estar más cerca de la realidad que EXPLAIN.

Para las siguientes consultas, usaremos la base de datos mundial para los ejemplos.

Configuración de la base de datos de ejemplo mundial

Descárguelo de ftp://ftp.askmonty.org/public/world.sql.gz

Instálelo con:

mysqladmin create world
zcat world.sql.gz |../client/mysql world

o

mysqladmin create world
gunzip world.sql.gz
../client/mysql world < world.sql

Forzar orden de unión

Puede forzar el orden de unión utilizando STRAIGHT_JOIN ya sea en el SELECT o JOIN parte.

La forma más sencilla de forzar el orden de unión es poner las tablas en el orden correcto en el FROM cláusula y uso SELECT STRAIGHT_JOIN al igual que:

SELECT STRAIGHT_JOIN SUM(City.Population)FROM Country,City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Vladimir Putin";

Si solo desea forzar el orden de unión para algunas tablas, use STRAIGHT_JOIN en el FROM cláusula. Cuando se hace esto, solo las tablas conectadas con STRAIGHT_JOIN tendrá su orden forzada. Por ejemplo:

SELECTSUM(City.Population)FROM Country STRAIGHT_JOIN City WHERE
City.CountryCode=Country.Code AND Country.HeadOfState="Vladimir Putin";

En los dos casos anteriores Country se escaneará primero y para cada país coincidente (uno en este caso) todas las filas en City se comprobará si hay una coincidencia. Como solo hay un país coincidente, esto será más rápido que la consulta original.

La salida de EXPLAIN para los casos anteriores es:

Este es uno de los pocos casos en los que ALL está bien, ya que el escaneo del Country tabla solo encontrará una fila coincidente.

Forzar el uso de un índice específico para la cláusula WHERE

En algunos casos, el optimizador puede elegir un índice no óptimo o puede optar por no usar un índice en absoluto, incluso si teóricamente se podría usar algún índice.

En estos casos, tiene la opción de decirle al optimizador que solo use un conjunto limitado de índices, ignorar uno o más índices o forzar el uso de algún índice en particular.

USE ÍNDICE: use un conjunto limitado de índices

Puede limitar qué índices se consideran con la opción USE INDEX.

USEINDEX[{FOR ORDERBY]([index_list])

El valor predeterminado es ‘FOR JOIN‘, lo que significa que la pista solo afecta la forma en que WHERE la cláusula está optimizada.

USE INDEX se utiliza después del nombre de la tabla en el FROM cláusula.

Ejemplo:

CREATEINDEX Name ON City (Name);CREATEINDEX CountryCode ON City (Countrycode);EXPLAINSELECT Name FROM City USEINDEX(CountryCode)WHERE name="Helsingborg"AND countrycode="SWE";

Esto produce:

Si no hubiéramos utilizado USE INDEX, el Name el índice hubiera estado en possible keys.

IGNORAR ÍNDICE: No use un índice en particular

Puede decirle al optimizador que no considere algún índice en particular con la opción IGNORAR ÍNDICE.

IGNOREINDEX[{FOR GROUPBY]([index_list])

Se utiliza después del nombre de la tabla en el FROM cláusula:

CREATEINDEX Name ON City (Name);CREATEINDEX CountryCode ON City (Countrycode);EXPLAINSELECT Name FROM City IGNOREINDEX(Name)WHERE name="Helsingborg"AND countrycode="SWE";

Esto produce:

El beneficio de usar IGNORE_INDEX en lugar de USE_INDEX es que no deshabilitará un nuevo índice que pueda agregar más tarde.

ÍNDICE DE FUERZA: Forzar un índice

Forzar el uso de un índice es más útil cuando el optimizador decide hacer un escaneo de tabla incluso si sabe que usar un índice sería mejor. (El optimizador podría decidir hacer un escaneo de tabla incluso si hay un índice disponible cuando crea que la mayoría o todas las filas coincidirán y puede evitar la sobrecarga de usar el índice).

CREATEINDEX Name ON City (Name);EXPLAINSELECT Name,CountryCode FROM City FORCEINDEX(Name)WHERE name>="A"and CountryCode >="A";

Esto produce:

FORCE_INDEX funciona considerando solo los índices dados (como con USE_INDEX) pero además le dice al optimizador que considere un escaneo de tabla como algo muy costoso. Sin embargo, si no se puede usar ninguno de los índices ‘forzados’, se usará un escaneo de tabla de todos modos.

Prefijos de índice

Cuando se utilizan sugerencias de índice (USE, FORCE o IGNORE INDEX), el valor del nombre del índice también puede ser un prefijo inequívoco de un nombre de índice.

Forzar que un índice se utilice para ORDER BY o GROUP BY

El optimizador intentará utilizar índices para resolver ORDER BY y GROUP BY.

Puede utilizar USE INDEX, IGNORE INDEX y FORCE INDEX como en el WHERE cláusula anterior para garantizar que se utiliza algún índice específico:

USEINDEX[{FOR GROUPBY]([index_list])

Se utiliza después del nombre de la tabla en el FROM cláusula.

Ejemplo:

CREATEINDEX Name ON City (Name);EXPLAINSELECT Name,Count(*)FROM City
FORCEINDEXFORGROUPBY(Name)WHERE population >=10000000GROUPBY Name;

Esto produce:

Sin la opción FORCE INDEX tendríamos ‘Using where; Using temporary; Using filesort‘en la columna’ Extra ‘, lo que significa que el optimizador crearía una tabla temporal y la ordenaría.

Ayude al Optimizador a optimizar GROUP BY y ORDER BY

El optimizador utiliza varias estrategias para optimizar GROUP BY y ORDER BY:

• Resolver con un índice:
  • Escanee la tabla en orden de índice y los datos de salida a medida que avanzamos. (Esto solo funciona si ORDER BY / GROUP BY puede resolverse mediante un índice después de que se realiza una propagación constante).

• Ordenación de archivos:
  • Escanee la tabla que desee clasificar y recopile las claves de clasificación en un archivo temporal.
  • Ordenar las claves + referencia a la fila (con ordenación de archivos)
  • Escanee la tabla en orden ordenado

• Utilice una tabla temporal para ORDER BY:
  • Cree una tabla temporal (en memoria) para los datos ‘por ordenar’. (Si esto se vuelve más grande que max_heap_table_size o contiene blobs, se utilizará una tabla basada en disco Aria o MyISAM)
  • Ordenar las claves + referencia a la fila (con ordenación de archivos)
  • Escanee la tabla en orden ordenado

Siempre se utilizará una tabla temporal si los campos que se ordenarán no son de la primera tabla en el orden JOIN.

• Utilice una tabla temporal para GROUP BY:
  • Cree una tabla temporal para contener el resultado GROUP BY con un índice que coincida con los campos GROUP BY.
  • Producir una fila de resultados
  • Si existe una fila con la clave GROUP BY en la tabla temporal, agréguele la nueva fila de resultados. Si no es así, cree una nueva fila.
  • Antes de enviar los resultados al usuario, ordene las filas con filesort para obtener los resultados en el orden GROUP BY.

Forzar / rechazar tablas temporales que se utilizarán para GROUP BY:

El uso de una tabla en memoria (como se describe anteriormente) suele ser la opción más rápida para GROUP BY si el conjunto de resultados es pequeño. No es óptimo si el conjunto de resultados es muy grande. Puedes decirle al optimizador esto usando SELECT SQL_SMALL_RESULT o SELECT SQL_BIG_RESULT.

Por ejemplo:

EXPLAINSELECT SQL_SMALL_RESULT Name,Count(*)AS Cities FROM City GROUPBY Name HAVING Cities >2;

produce:

tiempo:

EXPLAINSELECT SQL_BIG_RESULT Name,Count(*)AS Cities FROM City
GROUPBY Name HAVING Cities >2;

produce:

La diferencia es que con SQL_SMALL_RESULT se utiliza una mesa temporal.

Forzar el uso de tablas temporales

En algunos casos, es posible que desee forzar el uso de una tabla temporal para que el resultado libere los bloqueos de tabla / fila para las tablas utilizadas lo más rápido posible.

Puedes hacer esto con el SQL_BUFFER_RESULT opción:

CREATEINDEX Name ON City (Name);EXPLAINSELECT SQL_BUFFER_RESULT Name,Count(*)AS Cities FROM City
GROUPBY Name HAVING Cities >2;

Esto produce:

Sin SQL_BUFFER_RESULT, la consulta anterior no usaría una tabla temporal para el conjunto de resultados.

Interruptor optimizador

En MariaDB 5.3 agregamos un conmutador optimizador que le permite especificar qué algoritmos se considerarán al optimizar una consulta.

Consulte la sección del optimizador para obtener más información sobre los diferentes algoritmos que se utilizan.

Ver también

• ÍNDICE DE FUERZA
• ÍNDICE DE USO
• IGNORAR ÍNDICE
• AGRUPAR POR

El contenido reproducido en este sitio es propiedad de sus respectivos dueños, y MariaDB no revisa este contenido con anticipación. los Los puntos de vista, la información y las opiniones expresadas por este contenido no representan necesariamente las de MariaDB o de cualquier otra parte.