mysql consejos

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

Buenos consejos a tener en cuenta:

• Ponle contraseña al usuario root y luego cámbiale de nombre: lo más normal es ponerle contraseña al usuario root, pero no nos sorprendamos si vemos un root sin password o con algo tan ridículo como root_1. Lo que si no se suele hacer mucho es cambiar el nombre al root y eso es una buena idea que habría que tener siempre en cuenta… pero que tampoco sea admin (como dice el artículo), sino algo más difícil de adivinar.
• Oculta MySQL de Internet: MySQL no necesita estar accesible desde Internet, por lo que mejor evitar esa posibilidad.
• Protege el directorio de instalación de MySQL de otros usuarios
• No almacenes datos binarios: que MySQL lo permita no quiere decir que sea lo mejor hacerlo. MySQL envía los datos de una única vez, esto implica que hasta que no se envíe el campo completo, la aplicación no puede parsear la información. Es preferible almacenar los datos en el sistema de ficheros y almacenar en la BD un ruta hasta el fichero. Aunque yo casi recomendaría guardar en la BD una clave, para que mediante una lógica en la aplicación se pueda recuperar la ruta, así, si tenemos que modificar la ruta no tenemos que modificar todos los registros de la BD, tan solo la lógica del programa.
• Usa SQL standard: MySQL ofrece posibilidades que no son del SQL estándar y que pueden aportarnos muchas ventajas, el problema es que nos limita a exportar nuestra aplicación a otras BD si en un futuro lo necesitamos. La solución es no ejecutar esas sentencias directamente, sino mediante una clase, que extienda de una clase genérica, así podremos simular esas funcionalidades de MySQL en otras BDs creando otras clases para cada motor de BD.
• Crea tu propio generador de auto_increment: el auto_increment nos crea números consecutivos que podemos usar en nuestras tablas, pero con limitaciones, solo existe un auto_increment por tabla y son independientes de las tablas, por lo que distintas tablas pueden tener mismo id, lo cual puede no convenirnos en algunas circunstancias.
• No mezcles código de presentación con el de acceso a BD: es el sistema MVC que siempre debemos usar.
• Normalización y denormalización: la normalización nos permite tener una BD sin datos redundantes. Desafortunadamente, a veces esta pena el rendimiento, para lo cual, una vez normalizada la BD es conveniente denormalizarla.
• Usa un pool de conexiones en el servidor Web o en el servidor de aplicaciones: la conexión a la BD es algo costoso, si compartimos conexión mediante un pool, ganaremos en rendimiento.
• Mejoras tus queries con EXPLAIN SELECT: aunque es un comando difícil de seguir, nos puede ayudar mucho.

Ten MySQL Best Practices

Vía / Sheeri Kritzer Cabral

Buen artículo en el que se nos ofrecen 10 cosas que hay que tener en cuenta cuando vamos a instalar MySQL. En muchas ocasiones la gente se conforma con instalar la base de datos, meter los datos y ya está, cuando realmente hay cosas muy importantes en las que fijarse:

• Comprender el propósito y el crecimiento potencial de la base de datos.
• Determinar una capa física adecuada, los parámetros por defecto no son siempre los mejores.
• Seleccionar un motor de almacenamiento adecuado y pensar que impacto puede tener en el rendimiento y las estrategias de backup y recuperación.
• Planifica tu estrategia de copias de seguridad y recuperación. Elige las herramientas adecuadas para tu entorno.
• Comprende el número de bases de datos que serán creadas y el mantenimiento.
• ¿Cual es el coste y las ramificaciones del entorno?, realiza un análisis adecuado.
• Piensa en la seguridad con la que serán tratados los usuarios y las bases de datos.
• Elige un SQL_MODE adecuado, con la seguridad de que comprendes las consecuencias de esa elección.
• Ten en cuenta la capa física de almacenamiento: limitaciones, escalabilidad y rendimiento de usar discos separados, arrays de discos, …
• Define prácticas correctas para determinar nomenclaturas, herramientas de administración, administración de infraestructuras y documéntalo todo.

Top Ten Things to do before installing MySQL

Una serie de consejos para aquellos que empiezan con MySQL o que vienen de usar otra base de datos.

• Motores de almacenamiento: los storage engines son podríamos decir que tipos de tablas, y la elección de estos puede ser fundamental, ya que algunos poseen características que otros no tienen, por ejemplo InnoDB tiene constraints de claves secundarias.
• Escalado (scale-out vs scape-up): el escalado puede ser de dos tipos scale-out que se refiere a la capacidad de mejorar el servidor, y scale-up que es la capacidad de añadir más servidores. MySQL realiza mejor el scale-out que el scale-up.
• Copias de seguridad: para realizar las copias de seguridad se usa el comando mysqldump, aunque si dispones de Linux puedes usar LVM, o también puedes usar la replicación.
• Permisos: no existen los roles, los permisos se crean a partir del USERID.
• Collations: por defecto, si se comparan strings no son sensibles a mayúsculas minúsculas (a=A).
• Autocommit: activo por defecto.
• Optimización: es recomendable usar los logs de las slow queries para ver qué va mal en nuestro sistema.
• SQL_MODE: si estás preocupado por la integridad de los datos, deberás leer información sobre el SQL_MODE.
• Isolation levels: importante leer sobre los isolation levels ya que tiene distinto comportamiento que en otras bases de datos como Oracle.

tips for MySQL newbies