Mysql Blog en Español

Una de las principales ayudas en el examen de código que sea ineficiente funcionamiento, es utilizar el comando EXPLAIN.
mysql manual

La sentencia EXPLAIN puede utilizarse como una manera para obtener información acerca de cómo Mysql ejecuta una sentencia SELECT. Podemos determinar si los resultados de Mysql ha elegido una forma óptima de obtener los datos. Dos de las columnas son particularmente útil de examinar.
El primero es la Type columna que muestra el tipo de acceso Mysql utiliza para la consulta. En orden de menos un acceso eficiente a la más eficiente, estos son los valores que deben aparecer en la columna Type:
All - index - range - index_subquery - unique_subquery - index_merge - ref_or_null - ref - eq_ref - const - system

La siguiente columna es la columna Extra (este muestra información adicional relativa a esta estrategia de acceso).

Ahora, si usamos el comando Explain en una consulta (mirando a un conjunto de datos de registros de 2921, ligando con 50 registros de ciudades) tenemos esto:

Ejemplo de script
explain
select a.* from city a
where a.city_id in
(select b.city_id
from people b
group by b.city_id
having max(b.income) > 68500) \G

*************** 1. row ****
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using where
*************** 2. row ****
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: index
possible_keys: NULL
key: city_id
key_len: 3
ref: NULL
rows: 2921
Extra: Using filesort
2 rows in set (0.06 sec)

Ten en cuenta que la columna Extra de la subconsulta se señala que "using filesort" es necesario. Si se muestra Filesort significa que el ordenación no se pueden utilizar de un índice, es decir el ordenación se lleva a cabo en la memoria (y, a veces, continuó en el disco si hay problemas de memoria). Esto es ineficiente, y cada vez ocupan más recursos en la tabla aumenta. Siquiera con ordenar un pequeño conjunto de datos, solo de 2921 filas, puede tener un efecto adverso.

En este caso, ya hay un índice sobre el campo city_id en la tabla people. De hecho, no es el índice que está haciendo esta consulta lento - es el hecho de que el Mysql hace subconsultas muy ineficiente. (De acuerdo con el siguiente enlace en ingles, parece que la versión 6.0.x de Mysql es mucho más eficiente en el procesamiento de subconsultas a través del "IN" cláusula

bigdbahead blog

En el ínterin, la mejor manera de optimizar este tipo de consulta es por escrito en un formato de Join.

explain
select a.state, a.capital, a.largest, a.city_id
from city a
inner join people p on a.city_id = p.city_id
group by a.state, a.capital, a.largest, a.city_id
having max(p.income) > 68500 \G

Ahora, si ejecutamos el comando de Explain de nuevo:

**************** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50
Extra: Using temporary; Using filesort
**************** 2. row ***
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: city_id
key: city_id
key_len: 3
ref: cookbook.a.city_id
rows: 40
Extra: Using where

¡Vaya! - la primera fila sigue utilizando filesort! Sin embargo, esto ya no se realiza en la subconsulta ineficiente; además, sólo se aplica a las 50 filas de nuevo en la agrupación del campo city_id. (En la declaración inicial de la ordenacion de filesort se realizó en 2921 en las filas de la subconsulta). Por cierto, esta consulta tuvo una media de tan sólo .2 de un segundo más rápido, pero en términos de base de datos un tamaño de registros de 2921 es relativamente pequeño, y cuanto mayor sea el conjunto de datos, mayor será la ganancia de velocidad mediante el uso de las Join de tabla.

Por supuesto, aunque re-escribir tus preguntas a utilizar Join ayudará inmensamente, no significa que la función de los índices que se ha olvidado. Por el contrario, la importancia de utilizar correctamente los índices es crucial. Espero que mire con más detalle en esta en mi siguiente artículo.

Una de las tareas más importantes para los encargados de administrar un sistema de base de datos (y también para muchos desarrolladores) es que se aseguren de que se ejecute de manera eficaz las consultas. En esta primera parte de varios artículos sobre la optimización de las consultas, voy a mostrar la forma de establecer que las consultas se toma un largo tiempo para llevar a cabo. Esto no siempre es evidente, sobre todo si eres el administrador del sistema y no el usuario.

El sistema de Mysql utiliza algo llamado el registro de consultas lentas (slow query log), lo que puede ser muy útil para optimizar el rendimiento del sistema. Sin embargo, por defecto, MySQL no registra de consultas lentas. Para utilizar este registro, necesitas editar el archivo my.cnf (puede estar en el directorio /etc/mysql/ o, si tienes servidor de lampp, en /opt/lampp/etc/ ). Luego, para poner esta herramiento en marcha, debes asegurarte de que las dos líneas siguientes están escritas:

Code:

En la línea anterior he entrado 2 (por segundos). Puedes cambiar si lo deseas - Tengo entendido que el tiempo por defecto de MySQL es de 20 segundos. Una nota sobre el nombre de ruta para log-slow-queries - Si no se da ningún valor aqui, el nombre por defecto es el nombre de la máquina host con el sufijo -slow.log. Si se da un nombre de archivo, pero no como ruta absoluta, el archivo se escribe en el directorio de datos. Puedes encontrar la ubicación de su directorio de datos por usar mysql y ejecutando el siguiente comando

Code:

Otra cosa más. Tendrás que asegurarte de que ya existe un archivo en /var/log/mysql llamado mysql-slow.log (utilizar el comando touch para crear este archivo si estás utilizando una máquina Unix o Linux) y de que los permisos de archivo permiten a los usuarios escribir en ella. Todos están bien, cualquier consulta que funciona después de que el servidor mysql se reinicia, se escribirá en este registro si toman más largo que el período de tiempo especificado.

Si estás interesado en saber más sobre los registros del servidor MySQL, aquí está el enlace para la parte correspondiente del manual
dev.mysql.com doc

Bueno, una vez que hemos establecido que las consultas se tarda mucho tiempo para terminar, tenemos que ver cómo podemos hacer que corra más rápido. En los próximos artículos voy a ver:

* Cambio de la codificación - esto puede implicar un mejor uso de los índices. También puede significar volver a escribir la consulta para utilizar un Join que es generalmente mucho más eficiente de utilizar Mysql en sub-consultas. Tengo que admitir que este ha sido uno de mis puntos débiles. También voy a mostrar las ventajas de usar "Explain plan".

* Malas opciones de indices e ineficiente diseño de bases de datos. A veces la forma de crear índices o tablas de bases de datos de diseño puede tener un impacto negativo en el rendimiento cuestiones

Una cosa que tengo que señalar es que cambian las bases de datos (a través de necesidad) con el tiempo, por lo que, lamentablemente, el rendimiento necesita ser más controldao a intervalos regulares. Sin embargo, se puede ir demasiado lejos en las cosas cambian constantemente para aumentar rendimiento. Debemos ser realistas en relación con estos asuntos. ¿Es que vale la pena el gasto de 4 horas de un dba el momento de hacer una consulta correr 1 segundo más rápido, en caso de que la consulta sólo se ejecute una vez al día o una vez a la semana?
Sentido común (como en todas las cosas) se debe tener en cuenta.

Bueno, espero que el próximo artículo sobre la optimización de consulta salga bastante pronto. Hasta entonces, disfrutar de tu codificación!