Generación de perfiles del rendimiento de las consultas en Azure Database for MySQL: servidor flexible mediante EXPLAIN
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 muestra también Using temporary; Using file sort, lo que significa 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 supone 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 anteriormente en la salida de la instrucción EXPLAIN, el servidor flexible de Azure Database for MySQL usa ahora 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 que es 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 ahora muestra que el servidor flexible de Azure Database for MySQL usa un índice combinado para evitar una 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. El hecho de tener un índice en una tabla no significa necesariamente que el servidor flexible de Azure Database for MySQL pueda usarlo para sus consultas. Valide siempre sus suposiciones con EXPLAIN y optimice las consultas con índices.