Share
He usado phpMyAdmin durante más de una década. En mis primeros años con la herramienta, simplemente necesitaba algo que pudiera mostrarme la estructura de la tabla y darme rápidamente los datos que contenía. A medida que crecieron mis necesidades, también lo hicieron las herramientas incluidas con phpMyAdmin, que me hacen volver como mi principal herramienta MySQL, incluso con optimización.
He tenido el placer de trabajar con varias bases de datos diferentes. Cada uno tiene sus inconvenientes, y cada uno tiene sus puntos fuertes. Cuando se me da una opción, tiendo a migrar de nuevo a MySQL, a pesar de que soy demasiado barato para comprar MySQL Enterprise. En su lugar, hago lo debido con phpMyAdmin como mi principal herramienta de creación de perfiles. Funciona bien para mí, pero tuve que investigar un poco para comprender lo que estoy viendo al perfilar mis aplicaciones. Espero poder transmitir esto de una manera que pueda ser entendida por el principiante, hasta el profesional experimentado..
La optimización lleva tiempo. A los gerentes, clientes y compañeros, no les gusta escuchar que un proyecto está retrasado debido a la optimización. Muchas veces aceleramos la optimización para cumplir con esos puntos de referencia. Al final, sin embargo, no le estamos haciendo ningún favor a nadie. La aplicación web más bonita del mundo te hará repetir el negocio si te lleva 10 segundos cargar cada página. Del mismo modo, si esperamos para optimizar hasta el final de nuestros proyectos, es probable que haya mucho más trabajo por hacer, que si hubiéramos estado comprobando a medida que avanza el proyecto..
Un par de notas antes de entrar en la carne y las patatas. Primero, no voy a entrar en MySQL Tuning, ya que está un poco fuera del alcance de este tutorial. Si bien la optimización es la optimización, en mi opinión es un tema en sí mismo. Mencionaré brevemente un par de oportunidades para optimizar cómo ajustar su servidor, pero las menciones serán breves. Además, miraré principalmente las tablas MyISAM y no las tablas InnoDB. La regla de oro es si está escribiendo muchos datos, use InnoDB, pero si está usando SELECT mucho más, entonces use MyISAM. Además, no estoy entrando en el nivel de tabla REPARAR, OPTIMIZAR, COMPROBAR y ANALIZAR, ya que este tutorial cubre la optimización de consultas con phpMyAdmin. Nuevamente, esto está un poco fuera del alcance de este tutorial..
Finalmente, voy a ver a WordPress como un ejemplo del mundo real. Seré el primero en decirle que no soy un experto en WordPress, pero puedo ver las consultas generadas con el mejor de ellos. Por lo que he visto, la base de datos con WordPress está bien indexada, pero una vez que comencemos a agregar cosas que están fuera de esos archivos principales, esos índices podrían no ser los mejores para lo que necesitamos..
"La optimización lleva tiempo. A los gerentes, clientes y compañeros, no les gusta escuchar que un proyecto está retrasado debido a la optimización".
La respuesta corta es sí.
La respuesta larga es que phpMyAdmin nos da la oportunidad de ver si necesitamos optimizar nuestras consultas y qué tanto necesitamos para optimizarlas. Me imagino que has visto esta pantalla más de una vez si has usado phpMyAdmin:
Es la pantalla de inicio estándar para phpMyAdmin. A menos que esté buscando formas de optimizar, puede ir directamente a sus mesas en el menú de la izquierda y nunca ver el menú de pestañas en la parte superior. Ese menú, específicamente las pestañas Estado y Variables es donde vamos a comenzar.
Comencemos con la pantalla de estado, que podría ser la herramienta más importante que proporciona phpMyAdmin:
Esta es la parte superior de la pantalla de estado. Si bien tiene algunos datos interesantes, si nunca ha pasado por debajo del pergamino, se ha perdido alguna información muy importante. En aras de la brevedad, quiero ver dos valores de contador muy simples con los que me obsesiono, el primero de mi entorno de prueba:
Los dos valores a los que se debe prestar mucha atención son Handler_read_rnd y Handler_read_rnd_next. Si esos dos valores están en rojo, entonces hay algunas consultas que deben verificarse, ya que cuando MySQL hace un SELECTO, está leyendo la tabla completa. En algunos casos, esto puede ser por diseño, ya que cuando coloca un índice en una tabla, se tarda un poco más en escribir y ocupa un poco más de espacio. Sin embargo, si ves algo como esto:
las posibilidades son, esto no fue por diseño. 141 millones de solicitudes para leer una fila en una posición fija, y 16 millones de solicitudes para leer la siguiente fila, probablemente significa que nos falta un índice o dos (miles). Obviamente, este número crece en función del número de solicitudes, por lo que cuanto más un motor de búsqueda indexa su sitio o más visitantes tiene, mayor es el índice perdido. Las exploraciones de la tabla completa son el enemigo, y esto te da una forma rápida de detectar qué tan cerca está el enemigo de las puertas..
Otra gran tabla para verificar el rendimiento de las consultas analiza las selecciones e índices directamente:
Esta tabla presta especial atención a tus uniones. Una combinación peligrosa no es usar e indexar en ninguna de las tablas, porque los escaneos de la tabla completa aumentan exponencialmente en la cantidad de combinaciones que usa. Cuanto más normalizadas estén sus tablas, más deberá prestar atención a sus índices, así como a la definición de los campos a los que se une..
Finalmente, dependiendo de una variable global, también querrá verificar esta tabla de variables:
Si está registrando sus consultas lentas, este contador variable muestra el número que se ha identificado para la observación, dependiendo de la configuración del tiempo de consulta largo. Esas variables se pueden encontrar en la pestaña de variables. Una mirada rápida en mi entorno de prueba muestra esta configuración (por ahora):
Estas dos pestañas muestran bastante más información, algunas de las cuales son absolutamente vitales para ajustar su servidor MySQL. PhpMyAdmin hace que sea muy fácil incluso para el principiante detectar un problema y tener una comprensión básica de lo que podría ser ese problema. Si un valor es verde, estamos bien. Si es rojo, necesita algo de atención. También nos permite entender que hicimos algunos progresos. Cuando reiniciamos nuestro servidor, todas estas variables de sesión se borran. Si hemos hecho cambios, podemos ver de inmediato si hicimos algún impacto.
Ahora que hemos identificado que necesitamos hacer algunas optimizaciones, echemos un vistazo a algunas de las herramientas que vamos a utilizar antes de encontrar nuestros problemas. La primera de las herramientas, y probablemente la más útil es usar EXPLAIN. Explicar básicamente nos da nuestro plan de ejecución de consultas. Esto nos dice qué planea hacer MySQL con esta consulta antes de que se ejecute.
Sin leer sobre EXPLAIN, la salida podría no significar mucho para ti. Usando una tabla que creé para un tutorial anterior, veamos un plan de ejecución no optimizado. Mi tabla solo tiene dos campos en este caso, uno es sales_id y el otro es sale_amount. Aquí está la consulta con la que estoy trabajando:
SELECCIONE sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
En la superficie, esta es una consulta muy simple. Sin embargo, al ser una tabla de ventas, la tabla crecerá y crecerá y crecerá. Generé 200 registros para el tutorial anterior, y al hacer un SELECT simple con una cláusula ORDER BY, en realidad tomó un poco más de lo que hubiera esperado:
Esa consulta con solo 200 registros nos costó .15 segundos. Usemos EXPLAIN para entender cómo ve MySQL esta consulta. Simplemente haga clic en el enlace "Explicar SQL" para ver los resultados:
Como la mayoría de las cosas, esto no tiene mucho sentido a menos que entiendas lo que se dice. Para alguien que nunca ha ejecutado un EXPLAIN en una consulta, esto también podría estar escrito en jeroglíficos. A ver si podemos traducir a algo un poco más comprensible..
El select_type nos dice que MySQL ve este SELECT como un simple, vaya a una tabla y procese. Si hubiera una unión o una subconsulta, esto mostraría a qué parte de la declaración SELECT llamaría. Por ejemplo, si creo una consulta que tiene una subconsulta:
SELECCIONA sale_amount como cantidad DE ventas WHERE sales_id IN (SELECCIONA sales_id DE sales_force WHERE sales_id = 4)
Obtenemos una EXPLICACIÓN de esto:
Lo que nos dice acerca de la consulta en sí. En este caso, nuestro select_type ha cambiado para decir que la primera consulta es la principal, y luego MySQL va a salir y realizar la subconsulta, que es una vista, por lo que hay otra subconsulta que realizar, por lo que terminamos con las tres ids El manual de referencia de MySQL da todos los valores posibles:
De vuelta a nuestro ejemplo original:
Es el tipo al que se debe prestar atención, ya que le dice si MySQL va a escanear toda la tabla o si usará un índice para encontrar rápidamente los resultados. Esta es la columna principal a considerar cuando está optimizando sus consultas. Del orden bueno al malo, los valores son:
Donde quiere comenzar es obtener una optimización de cualquier consulta que sea del tipo de índice o todos. Si puede eliminar su aplicación de estos dos tipos, su rendimiento mejorará. Este mis amigos, es donde empiezas..
El resto de las columnas se ocupan de los índices que utilizará MySQL y la cantidad de filas que tendrá que escanear antes de que pueda ver si hay un resultado válido. Al deshacerse de los tipos "índice" y "todos", estos son útiles para entender exactamente qué índice está usando MySQL para ejecutar esta consulta. Para mover una consulta hacia arriba en la escalera, comienza a ajustar sus índices para mejorar el rendimiento. A modo de ilustración, me limitaré a eliminar "todos" o escaneos de tabla completa.
La columna final es la columna "extra". La columna adicional le brinda información sobre la consulta, si se usa o no una cláusula WHERE, si es o no imposible WHERE, lo que significa que esta consulta siempre devolverá un valor NULL porque la cláusula WHERE hace que sea imposible de ejecutar. El único valor al que debemos prestar mucha atención y de lo que debemos deshacernos de él es el "Uso del conjunto de archivos" que tenemos en nuestro ejemplo. Cuando vea ese valor, MySQL tiene que hacer otra pasada a través de los resultados para ordenar los valores. Entonces, en el caso de nuestra consulta original:
SELECCIONE sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
MySQL no solo explora toda la tabla, sino que también la analiza dos veces para ordenar los resultados debido a nuestra declaración ORDER BY. Esto, obviamente, es doblemente malo. Optimizaremos esta consulta y muchas más en las siguientes secciones..
En MySQL 5.0.37, otra herramienta estuvo disponible para que la usáramos en la optimización, y ese es el perfilador de MySQL. Además, phpMyAdmin agregó soporte para esta característica en la versión 2.11, por lo que si tiene ambas versiones disponibles, tenemos otra herramienta para agregar a la optimización..
Lo que hace MySQL Profiler es dar información sobre los cuellos de botella de nuestras consultas. Nos permite ver qué pasa. durante la ejecución real de nuestras consultas, lo que EXPLAIN hace, que es el plan de ejecución antes de. Veamos qué información podemos obtener de phpMyAdmin de mi consulta original errónea:
Si hacemos clic en la casilla de verificación "Perfilado" debajo de nuestra consulta, se abrirá un nuevo mundo con:
phpMyAdmin proporciona los tiempos de ejecución reales de la consulta que se proporcionó. Ahora podemos ver los cuellos de botella en los que deben abordarse nuestras consultas, o incluso la estructura a nivel de tabla. Tal vez, vemos la necesidad de los archivos de registro en los que esta tabla realmente no se escribe tanto como se lee, por lo que en lugar de InnoDB, ahora podemos cambiarla a MyISAM.
Hay un pequeño inconveniente en el uso de phpMyAdmin cuando se usa el Perfilador de MySQL, y es que el generador de perfiles se basa en la sesión, y phpMyAdmin destruye la sesión en cada visita de página ... El problema que esto nos presenta es que no tenemos una manera para mantener un total acumulado de los datos de creación de perfiles, pero hay una manera de engañar a phpMyAdmin, aunque de una manera poco elegante:
SET perfilado = 1; SELECCIONE sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; MOSTRAR los perfiles;
Lo que resulta en:
Dado que estamos ejecutando varias consultas, debe utilizar el delimitador. Esto mostrará que mi consulta es query_id 1. Cada vez que ejecuto esta consulta, sigue siendo query_id 1 ya que mi sesión se está destruyendo al inicio. No estoy seguro de si esto es por diseño, un error o ignorancia de mi parte que phpMyAdmin destruye la sesión con el comando QUIT, pero podemos solucionar este problema un poco. MySQL tiene una maravillosa información sobre el uso del generador de perfiles por Robin Schumacher, y voy a usar un poco de la consulta de Robin para obtener el número de operaciones en phpMyAdmin:
SET perfilado = 1; SELECCIONE sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; SELECCIONE min (seq) como secuencia, estado, recuento (*) como operaciones, redondee (suma (duración), 5) como duración DESDE information_schema.profiling DONDE query_id = 1 GROUP por estado ORDER por seq;
Una vez más, no es ideal con phpMyAdmin, pero al final conseguimos lo que queremos:
Antes de poner todo lo que hemos aprendido juntos, también echemos un vistazo a cómo capturar consultas mediante el uso de los archivos de registro de MySQL. Podemos capturar todas las consultas que MySQL ejecuta en la tabla mysql.general_log. Al ejecutar este comando:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Ahora podemos tener un registro para todas las consultas que se ejecutan, independientemente de la fuente. Si bien esta operación es costosa y no la ejecutaría en un entorno de producción, nos brinda un método claro y conciso para obtener todas nuestras consultas y el orden de su ejecución desde nuestras aplicaciones. En resumen, esta podría ser la herramienta de optimización de consultas SQL más valiosa que tiene en su caja de herramientas. Al establecer estos dos vars GLOBAL, tenemos el paso final para obtener algunas técnicas prácticas de optimización.
Aquí hay una salida abreviada de la tabla mysql.general_log usando esta consulta:
SELECCIONAR event_time, command_type, argumento FROM mysql.general_log ORDER BY event_time
produce esto:
Básicamente tengo mi consulta, junto con todo lo que phpMyAdmin ha estado haciendo en el fondo. Si vacío la tabla antes de cada comando nuevo, tengo algo con lo que puedo trabajar en cada vista de página, o llamada AJAX que hago desde mis aplicaciones. Para vaciar el registro, simplemente TRUNCIAMOS la tabla de esta forma:
TRUNCATE mysql.general_log
Truncate es una declaración mucho mejor para usar aquí que DELETE FROM, ya que la instrucción DELETE elimina fila por fila, donde TRUNCATE vacía toda la tabla a la vez.
Una vez que haya terminado con su optimización, simplemente debe desactivar el registro de consultas con este comando:
SET GLOBAL general_log = 'OFF';
El registro general se vuelve costoso con el tiempo y, ciertamente, ralentiza el rendimiento de su aplicación. Lo mantengo apagado entre mis optimizaciones simplemente para poder tener una sensación orgánica del rendimiento de lo que estoy escribiendo. Dicho esto, en el desarrollo, siempre mantengo el registro de consultas lento activado ya que quiero ver mis consultas más lentas como una herramienta de optimización rápida. Puedes hacerlo fácilmente:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
y podemos verificarlo desde nuestra pestaña Variables desde nuestra página de bienvenida:
Para ver el resultado, solo necesitamos revisar mysql.slow_log o podemos usar una consulta como esta:
SELECCIONAR sql_text DE mysql.slow_log
Lo que me da las consultas reales que se registraron como lento:
Ahora podemos poner todo esto y usar phpMyAdmin como una herramienta de optimización de consultas relativamente decente. Comencemos con el primer ejemplo de consulta:
EXPLICAR SELECCIONAR sales_id, sale_amount DESDE tutorial.sales ORDER BY sale_amount
Lo que produce una salida de:
Sabemos que necesitamos obtener al menos un ÍNDICE en esta tabla. Paremos y pensemos cómo se usa esta tabla. Es una tabla de búsqueda simple para unirse a una tabla sales_force para decirnos que hicieron una venta por la cantidad registrada. Si todo lo que hacemos es unirnos a esta tabla en el sales_id, entonces eso es lo que necesitamos indexar haciendo clic en el enlace de detalles:
Entonces podemos definir ese índice de la siguiente manera:
Nuestra consulta original aún nos brinda un análisis completo, pero en una aplicación práctica:
SELECCIONE sfn.first_name, sfn.last_name, s.sale_amount DE sales_force_normalized sfn INNER JOIN sales s ON sfn.sales_id = s.sales_id
A ver si esto es mejor:
Ahora estamos llegando a alguna parte. Sin embargo, si hacemos algo como esto:
SELECCIONE max (sale_amount) DE ventas
Luego estamos de vuelta en el mismo barco de hacer un escaneo completo de la tabla. En este caso, solo podemos editar el índice y agregar la cantidad_venta:
Lo que nos mejora de muy mal a simplemente mal:
O podemos agregar un nuevo índice solo en la cantidad:
Y tenemos el maravilloso resultado de:
Lo que significa que MySQL ni siquiera tiene que abrir la tabla, ya que solo tiene que mirar el índice. Ahora hemos alcanzado el nivel óptimo absoluto para esta función COUNT. Echa un vistazo a lo que tardó en ejecutar esta consulta ahora:
Y por si acaso, vamos a hacer clic en la casilla de verificación de perfiles en la consulta para ver los cuellos de botella ahora:
Hemos estado jugando con consultas ficticias y bases de datos ficticias, pero pongamos a prueba este tutorial. Tengo un programa de instalación de WordPress, con solo el complemento Lorem Ipsum para agregar aproximadamente 5000 publicaciones y 11,000 comentarios, por lo que podemos poner un poco de esfuerzo en MySQL cuando hacemos nuestras selecciones..
Comencemos a registrar nuestras consultas nuevamente desde phpMyAdmin y también truncaremos los registros lentos y generales para que podamos ver lo que sucede cuando cargamos una página desde WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
Habrá algunos artefactos en general_log, ya que phpMyAdmin provoca alguna actividad en MySQL, pero deberíamos poder tener todo en orden cuando recargue mi página de índice de WordPress en este punto, y si usamos una condición LIKE, puede obtener principalmente resultados de WordPress ya que las tablas tienen el prefijo wp_:
SELECCIONAR event_time, command_type, argumento FROM mysql.general_log DONDE argumento LIKE "% wp_%" ORDER BY event_time
Lo que nos da un resultado razonable de:
Ahora, sabemos que WordPress simplemente nos da 11 consultas sobre la carga de la página de índice con una bonita instalación de vainilla. Vamos a encontrar algo para optimizar que puedan haber perdido. Si tomamos la primera consulta que se ejecuta cada vez que se carga WordPress:
EXPLICAR SELECCIONAR option_name, option_value FROM wp_options DONDE autoload = 'yes'
Encontramos que esto no está optimizado:
Echemos un vistazo a lo que hicieron a través de phpMyAdmin:
Vemos que hay un índice en option_name, pero no hay un índice en autoload, que es la condición especificada en la página de índice. Añadámoslo y veamos si podemos optimizar un poco la instalación central de WordPress:
Dado que, la carga automática es varchar y ya sea "sí" o "no" por lo que veo, puedo limitar mi valor de índice a 1. Es decir, ahora ve ya sea "y" o "n", lo que reduce nuestro tiempo aún más. Veamos el EXPLAIN después de que hayamos optimizado:
Hemos pasado de muy mal, al cuarto mejor tipo. No está mal para un par de minutos de trabajo. Por supuesto, WordPress no se estaba ahogando con este valor, pero dependiendo de la carga de tu blog, cada poco ayuda. Concedido ahora, las escrituras toman más tiempo, porque tenemos que indexar nuestra "y" o "n" para cada línea que está escrita.
Si vamos un poco más lejos, también podemos ver el Perfilador de MySQL en acción simplemente marcando la casilla "Perfilar". Ahora vemos que nuestra consulta está realmente llena de rumores:
La optimización no es fácil, ni es realmente muy divertida. Sin embargo, cuando ignora este paso del desarrollo, siempre vuelve para atormentarlo. Creo que es relativamente fácil usar las herramientas en phpMyAdmin para obtener una buena optimización de sus aplicaciones. Dicho esto, hay nuevas herramientas agregadas todo el tiempo, como Jet Profiler, que hace que lo que acabo de hacer sea en tiempo real, y de naturaleza gráfica..
.
Accentsconagua