Consejos para optimizar MySQL

ByLuis

Buenos consejos para optimizar MySQL, ordenados por distintas categorías.

Queries

  • Usa EXPLAIN para comprender la ejecución de la query
  • Usa Slow Query Log
  • No uses DISNTINCT cuando usas o puedes usar GROUP BY
  • Crea INSERT o REPLACE en procesos batch y usa LOAD DATA antes que INSERT
  • LIMIT m,n no es tan rápido como parece
  • No uses ORDER BY RAND() si tienes más de dos mil registros
  • Usa SQL_NO_CACHE cuando selecciones datos que se actualizan con frecuencia o son abundantes
  • Evita usar caracteres especiales en los LIKE
  • Evita usar subconsultas correlacionadas en el SELECT y el WHERE
  • No hagas comparaciones calculadas
  • ORDER BY y LIMIT funcionan mejor con
  • No pongas text/blobs en los resultados si no vas a necesitarlos
  • Subqueries en el FROM pueden ser útiles para recuperar BLOBs sin necesidad de ordenarlos. Si haces un join de una tabla consigo misma, puede ser bueno si en la primera obtienes el indice y en segunda obtienes el resto de los datos.
  • ALTER TABLE … ORDER BY puede tomar los datos ordenados cronologicamente y reordenarlos mediante un campo diferente, esto podría hacer que las consultas vayan más rapidas por ese campo, aunque quizás fuera necesario que estuviera en el índice.
  • Es bueno saber cuando dividir una query compleja en la unión de otras más pequeñas
  • Elimina pequeñas cuentas si puedes
  • Haz queries similares para que el cache las use
  • Usa estándares
  • No uses características deprecated
  • Cambia a OR en índices de múltiples campos para que la UNION mejore la velocidad (> mysql 5.0)
  • No uses COUNT (*) en tablas Innodb para cada búsqueda. Para ello deberías usar SQL_CALC_FOUND_ROWS y SELECT FOUND_ROWS()
  • Usa INSERT … ON DUPLICATE KEY update (INSERT IGNORE) para evitar el SELECT

Escalado

  • Usa benchmarking
  • Aisla las cargas de trabajo, no dejes que las tareas administrativas interfieran en el rendimiento (por ejemplo, backups)
  • Testea en vez de hacer debug
  • A la vez que los datos crecen los índices cambian. Haz tu esquema tan modular como tu código.

Rendimiento de red

  • Minimiza el tráfico recuperando solo lo que necesitas.
  • Accede directamente a la paginación con LIMIT no recuperando los anteriores.
  • No uses SELECT *
  • No uses muchas queries rápidas y una algo más compleja puede ser más eficiente.

Mejoras en el sistema operativo

  • Usa particiones en los datos
  • Manten el servidor tan limpio como sea posible
  • Crea un entorno de test

Servidor MySQL

  • innodb_flush_commit=0 puede ayidar en el retraso del slave
  • Optimiza los tipos de datos, se consistente. Usa PROCEDURE ANALYSE() para ayudarte a determinar el tamaño de los datos que necesitas.
  • Si puedes comprime text y blobs
  • Comprime datos estáticos
  • No hagas backup de datos estáticos con frecuencia
  • Activa e incrementa el cache de las queries y el budder si es apropiado
  • Comprueba SHOW STATUS y SHOW VARIABLES
  • Defragmenta tablas, reconstruye índices, haz mantenimiento
  • Aumenta myisam_sort_buffer_size para optimizar inserta largos

Estos son los que me han parecido más interesantes, el resto los podéis encontrar en:

Top 1000 SQL Performance Tips

Gracias David por el aviso

Similar Posts

|

10 consejos para mejorar tus queries MySQL

ByLuis

10 interesantes consejos a seguir para mejorar nuestras sentencias en MySQL, algunas de ellas para poder optimizar las ya creadas.

  • Usa el comando explain: el comando explain te puede ayudar a identificar los índices que se usan y además te proporciona más información útil.
  • Usa permisos menos complejos: usar permisos más sencillos evita que se comprueben muchas condiciones en cada ejecución de una sentencia.
  • Comando benchmark: este comando nos permite saber cuánto tiempo tarda en ejecutarse una expresión.
  • Optimiza tus cláusulas: ya sea quitando paréntesis innecesarios, usando count(*) sin condiciones en el where ya que tira de tablas internas de MyQSL o usando la opción SQL_SMALL_RESULT para usar tablas temporales en memoria.
  • Ejecuta optimize table: defragmenta una tabla después de varios borrados, el acceso a disco es algo importante a tener en cuenta.
  • Evita campos de longitud variable: en tablas de frecuente actualización evita campos como VARCHAR, BLOB o TEXT.
  • Usa insert delayed: solo cuando sea no importe cuando se inserten los datos.
  • Prioridades en las sentencias: usa INSERT LOW_PRIORITY para darle a las consultas mayor prioridad, o usa SELECT HIGH_PRIORITY para ejecutar una consulta aunque haya otros clientes esperando.
  • Haz múltiples inserciones en una única sentencia: en vez de varias sentencias de una única inserción.
  • Sincroniza tipos de datos: mismos datos en tablas diferentes deben ser del mismo tipo de datos.

Yo siempre he tenido una duda, que no he resuelto por pereza, sobre el caso de ejecutar count(*) o count(1). La leyenda urbana dice que es mejor count(1), ya que de la otra forma obtienes que enviar todas las columnas, al igual que haces en un select *. Un consultor en Oracle nos dijo que era falsa esa afirmación porque tardaba lo mismo, yo creo que también, entre otras cosas porque seguro que ese punto está optimizado, pero claro, que sea consultor o que sea de Oracle no quiere decir nada, y menos aún si hablamos de otras bases de datos.

10 tips for optimizing mysql queries

Vía / Alex Sancho

Recuperar datos en InnoDB

ByLuis

Data Recovery Toolkit for InnoDB es un conjunto de herramientas que comprueba los tablespaces de InnoDB y recuperan datos de un tablespaces dañado o de tablas que han sufrido drop o truncate.

Entre las características que ofrece nos encontramos con:

  • Soporte para REDUNDANT (anterior a MySQL 5.0) y COMPACT (posterior a MySQL 5.0).
  • Funciona con un tablespace único o un tablespaces de un fichero por tabla.
  • Recupera datos incluso cuando una página InnoDB has sido reasignada a otra tabla o fue parcialmente destruida.
  • Permite todos los tipos de datos de MySQL excepto BLOBs, SETs y BITs, aunque está previsto para la próxima versión.
  • Tiene un conjunto de filtros de datos para definir rangos de datos (para números), longitud de campos (para campos de longitud variable), juegos de caracteres (para strings), periodos de datos, etc…
  • Viene acompañado de una herramienta que permite definir tablas InnoDB, así no es necesario implementarlas directamente mediante sentencias SQL.
  • Los datos se devuelven en formato CSV por lo que luego pueden ser importados facilmente.

Data Recovery Toolkit for InnoDB

Vía / Homo-Adminus Blog

Curiosidades sobre TIMESTAMP en MySQL

ByLuis

Interesante lo que descubre uno sobre el TIMESTAMP en MySQL:

  • Por defecto TIMESTAMP es NOT NULL, insertar un NULL hace que se almacene el DATETIME actual
  • TIMESTAMP puede tener un valor entre ‘1970-01-01 00:00:01′ y ‘2038-01-19 03:14:07′
  • Solo la primera columna con NOT NULL puede tener por defecto la propiedad CURRENT_TIMESTAMP
  • Cuando CURRENT_TIMESTAMP se indica como ON UPDATE esa columna no se actualiza si no hay cambios significativos (por ejemplo al hacer dos updates iguales)

Interesting things about TIMESTAMP data type in MySQL

|

Búsquedas geolocalizadas en WordPress

ByLuis

Este es un ejemplo raro de uso de WordPress, pero el otro día una persona lo preguntaba en el foro de soporte de WordPress. El usuario tenía los posts geolocalizados (latitud/longitud), supongo con postmetas correspondientes.

Para poder realizar este tipo de búsquedas es necesario usar MySQL procedures, claro que no todos los hosting lo permiten:

CREATE PROCEDURE geodist (IN mylat decimal(18,12), IN mylon decimal(18,12), IN dist float)
BEGIN
declare lon1 float;
declare lon2 float;
declare lat1 float;
declare lat2 float;
set lon1 = mylon-dist/abs(cos(radians(mylat))*69);
set lon2 = mylon+dist/abs(cos(radians(mylat))*69);
set lat1 = mylat-(dist/69);
set lat2 = mylat+(dist/69);
SELECT p.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((mylat -lat.meta_value) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(lat.meta_value * pi()/180) *POWER(SIN((mylon - lon.meta_value) * pi()/180 / 2), 2) )) as distance FROM wp_posts as p, wp_postmeta lat, wp_postmeta lon WHERE p.ID = lat.post_id and lat.meta_key = 'latitude' and p.ID = lon.post_id and lon.meta_key = 'longitude' and lon.meta_value between lon1 and lon2 and lat.meta_value between lat1 and lat2
and post_status = 'publish'
having distance < dist ORDER BY Distance;
END

Una vez creado el procedure deberemos llamarlo para obtener los resultados:

global $wpdb;
$posts = $wpdb->query($wpdb->prepare('call geodist(?, ?, ?)', $lat, $lon, $dis));

Acceder al código en Gist

Análisis de MySQL Workbench

ByLuis

mysql_workbench.pngEl otro día me dio por instalar MySQL Workbench 1.0.6 beta, una aplicación para diseñar bases de datos en MySQL. Nos recuerda mucho a DBDesigner 4, incluso importa modelos creados por esta aplicación. Mi primera impresión es que efectivamente se trata de una beta, pero muy beta.

Lo que más llama la atención es los errores gráfico que hay, aunque no son los más importantes, porque no repercuten en el trabajo, ni cierran la aplicación. Por ejemplo, la típica pantalla que te recuerda a los juegos en MS-DOS cuando cambiaban de modo gráfico:
1.png
O que cuando se minimiza el modelo, hasta que no obtiene el foco, las letras no se dimensionan correctamente:
2.png

Muchas de las opciones del menú no son accesibles, como por ejemplo exportar a imagen, importar de un script SQL y imprimir, cosas que no son importantes, simplemente no las puedes usar y listo. El problema viene cuando la funcionalidad que está implementada no funciona correctamente (o a mí no me ha funcionado):

  • Cuando se realiza ingeniería inversa no relaciona las tablas según las foreing keys (FK).
  • Al exportar el modelo a un script SQL falla la hora de ejecutarlo, si falta algún dato debería indicarlo, no crear un script incorrecto.
  • Puede conectarse a otras BD (Oracle, MS Access, …), lo cual está muy bien para obtener el modelo de datos de ella por si queremos migrar de BD, pero cuando lo he intentado probar con Oracle no me admitía el jar que solicitaban.

Aún tiene que mejorar (y mucho), por ahora sigo con DBDesigner 4, pero tengo confianza en que las próximas versiones van a ir mejorando considerablemente el producto.

|

PHP Mini Admin para MySQL

ByLuis

phpminiadmin es una alternativa muy ligera y reducida de phpMyAdmin, en vez de instalar unos 10 megas solo sería cerca de 10k.
phpminiadmin.png
Inicialmente pide el login a la BD y una vez se ha accedido, se pueden realizar cualquier tipo de consulta (select, insert, update, delete, explain, …).
Lógicamente esta aplicación no tiene la capacidad que tiene phpMyAdmin, pero cada aplicación tiene su lugar y esta nos puede ser muy útil en determinadas circunstancias, como por ejemplo integrarla en una aplicación propia.
phpminiadmin