Generación de perfiles del rendimiento de las consultas en Azure Database for MySQL: servidor flexible mediante EXPLAIN
SE APLICA A:
Azure Database for MySQL: servidor único Azure Database for MySQL: servidor flexible
Importante
El servidor único de Azure Database for MySQL está en la ruta de retirada. Se recomienda encarecidamente actualizar al servidor flexible de Azure Database for MySQL. Para más información sobre la migración al servidor flexible de Azure Database for MySQL, consulte ¿Qué ocurre con Azure Database for MySQL con servidor único?
EXPLAIN es una herramienta útil que puede ayudarle a optimizar las consultas. La instrucción EXPLAIN se pueden usar para obtener información sobre cómo se ejecutan las instrucciones SQL. A continuación se muestra la salida de ejemplo de la ejecución de una instrucción EXPLAIN.
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 10.00
Extra: Using where
En este ejemplo, el valor de clave es NULL, lo que significa que el servidor flexible de Azure Database for MySQL no puede encontrar ningún índice optimizado para la consulta. Como resultado, realiza un examen de tabla completo. Vamos a optimizar esta consulta agregando un índice en la columna ID. Luego, vuelva a ejecutar la instrucción EXPLAIN.
mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
Ahora, la salida muestra que el servidor flexible de Azure Database for MySQL usa un índice para limitar el número de filas a 1, lo que reduce drásticamente el tiempo de búsqueda.
Índice de cobertura
Un índice de cobertura consta de todas las columnas de una consulta, lo que reduce la recuperación de valores de las tablas de datos. La siguiente instrucción GROUP BY y la salida relacionada lo ilustran.
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
La salida muestra que el servidor flexible de Azure Database for MySQL no usa índices, ya que los índices adecuados no están disponibles. La salida también muestra Using temporary; Con filesort, que indica que el servidor flexible de Azure Database for MySQL crea una tabla temporal para satisfacer la cláusula GROUP BY .
La creación de un índice solo en la columna c2 no hace ninguna diferencia y el servidor flexible de Azure Database for MySQL todavía necesita crear una tabla temporal:
mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
En este caso, se puede crear un índice cubierto en c1 y c2 agregando el valor de c2 directamente en el índice, lo que eliminará las búsquedas de datos adicionales.
mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: covered
key: covered
key_len: 108
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using index
Como se muestra en la salida de la explicación anterior, el servidor flexible de Azure Database for MySQL ahora usa el índice cubierto y evita tener que crear una tabla temporal.
Índice combinado
Un índice combinado consta de valores de varias columnas y puede considerarse como una matriz de filas que se ordenan mediante la concatenación de valores de las columnas indexadas. Este método puede ser útil en una instrucción GROUP BY.
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using filesort
El servidor flexible de Azure Database for MySQL realiza una operación de ordenación de archivos bastante lenta, especialmente cuando tiene que ordenar muchas filas. Para optimizar esta consulta, cree un índice combinado en las dos columnas que se están ordenando.
mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: NULL
key: my_sort2
key_len: 108
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where; Using index
La salida de la instrucción EXPLAIN muestra ahora que el servidor flexible de Azure Database for MySQL usa un índice combinado para evitar la ordenación adicional, ya que el índice ya está ordenado.
Conclusión
Puede aumentar significativamente el rendimiento mediante EXPLAIN junto con distintos tipos de índices. Tener un índice en una tabla no significa necesariamente que el servidor flexible de Azure Database for MySQL pueda usarlo para las consultas. Valide siempre sus suposiciones con EXPLAIN y optimice las consultas con índices.
Pasos siguientes
- Para encontrar respuestas de los compañeros a sus preguntas o publicar una nueva pregunta o respuesta, visite Stack Overflow.