Librería para que MySQL devuelva XML

ByLuis

Ya hace tiempo comentamos como hacer para que MySQL devolviera los datos en formato XML mediante procedimientos almacenados. En este caso se trata de la librería lib_mysqludf_xql que permite mediante un conjunto de funciones mostrar la salida de las consultas en formato XML, sin necesidad de utilizar lenguajes de programación tipo PHP o Ruby.

Esta librería usa xmlwriter de libxml2, la cual es una librería muy rápida sin caché, además escapa directamente las entidades XML y trata de forma muy eficiente la memoria cuando el contenido crece.

Un ejemplo de uso sería el siguiente:

SELECT xql_agg('classes' AS `xql:root`,
xql_element('class', null, concat(grade, suffix) AS name, teacher))
FROM schoolclass;

Y el resultado sería el siguiente:

<classes>
<class name="1A" teacher="Pullen"/>
<class name="2A" teacher="Berg"/>
<class name="3A" teacher="Heijden"/>
<class name="3B" teacher="Ven"/>
<class name="4A" teacher="Theunissen"/>
<class name="4B" teacher="Huizingh"/>
<class name="4C" teacher="Groot"/>
<class name="5A" teacher="Brongers"/>
<class name="5B" teacher="Zijlstra"/>
<class name="6A" teacher="Brommer"/>
<class name="7A" teacher="Nimwegen"/>
<class name="7B" teacher="Lede"/>
<class name="8A" teacher="Spaansen"/>
<class name="8B" teacher="Wiersma"/>
</classes>

lib_mysqludf_xql

Similar Posts

Tareas típicas para el mantenimiento de MySQL

ByLuis

Tareas que se deberían ejecutar para el mantenimiento de la base de datos de MySQL:

  • Backup: hay que realizarlo de los ficheros de datos, ficheros de logs de transacciones, ficheros de configuración y logs binarios.
  • Limpiar los logs binarios: Se puede hacer mediante my.conf usando expire_logs_days o mediante la sentencia PURGE MASTER LOGS. Eso sí, no purgar los logs que necesiten los esclavos, ni borrarlos mediante un comando de un fichero (rm bin-log.*).
  • Optimizar las tablas: después de inserts y updates las tablas se llenan de espacio inútil, por lo que es necesario optimizar las tablas usando OPTIMIZE TABLE.
  • Vaciar la caché: cuando se ejecutan selects con tamaños diferentes de resultados, el cache se defragmenta, lo cual hace necesario que se vacie la cache (FLUSH QUERY CACHE).
  • Rotar los logs binarios: los logs binarios solo se rotan por tamaño, si quieres hacerlo en un determinado momento se debería usar la sentencia FLUSH LOGS.

Typical automated MySQL maintenance jobs

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

20 recursos MySQL

ByLuis

Una buena lista de aplicaciones, enlaces, recursos, tutoriales sobre MySQL que nos pueden ayudar a trabajar con esta base de datos.

Herramientas de diseño y administración

  • MyDB Studio: aplicación para administracion de MySQL con alguna utilidad para diseño.
  • SQLYog: excelente GUI para diseño y administración de bases de datos MySQL.
  • WWW SQL Designer: diseñador Ajax, tiene muchas características que normalmente solo están disponibles en aplicaciones offline.
  • OpenOffice.org Base: aunque es inusual, realmente tiene un buen soporte para trabajar con MySQL.
  • AutoMySQLBackup: para la creación de scripts para realizar backups.
  • PHPMyAdmin: el más famoso de los administradores de MySQL vía web.

Herramientas de seguridad

  • SQLIer: Herramienta para SQL Injection que indicando una URL, intenta hacer todo lo posible para realizar SQL Injection.
  • SQLMap: Otra utilidad para SQL Injection que admite MySQL, PostgreSQL y MSSQL.
  • Absinthe: utilidad disponible para Linux y Windows que intenta forzar tu servidor con ataques SQL injection.
  • SQID: SQL Injection mediante línea de comandos creado con Ruby.

Optimización

Conocimientos

20 Indispensible MySQL Resources

Vía / dzone

|

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

MySQL 5.5 Milestone 2

ByLuis

MySQL ha sacado la primera versión Milestone (o RC) de la 5.5, la cual trae algunas novedades interesantes:

  • Replicación semisíncrona: la cual se asegura de que al menos un esclavo confirme la transacción, habrá que ver que tal es esto para el rendimiento.
  • Nueva sentencia LOAD XML
  • Particionamiento por rangos y listas, que por cierto, yo desconocía que la versión Community de MySQL admitía particionamiento.
  • Versión mejorada del plugin de InnoDB

What is new in MySQL 5.5

| | |

Nueva versión de WAMP5

ByLuis

wamp.pngHa salido una nueva version de WAMP 1.6.4, una aplicación que te instala Apache, PHP5, MySQL, PHPmyadmin y SQLitemanager en tu ordenador, la cual recomiendo sin lugar a dudas.
La nueva versión permite limitar el acceso al servidor web desde internet, que solo se pueda acceder desde localhost, y hacer lo mismo para PHPmyadmin y SQLitemanager. También añade las Zend Optimizer add-ons.
WAMP5
Vía / dzone