1. Optimizando las consultas para usar el cache de consulta
La mayoría de los servers MySQL tienen habilitado el chache para consultas. Y esto es uno de los métodos más efectivos para mejorar el rendimiento, y es utilizado por el servidor de manera interna, por lo tanto cuando se utiliza de manera repetida, puede resultar en una mejora al realizar nuestras consultas.
El principal problema es que es invisible al programador, por lo tanto, se suele ignorar, cosa que no debe ser así, ya que podemos mejorar notablemente nuestras consultas a la base de datos.
Por ejemplo.
view plaincopy to clipboardprint?
// el cache de la consulta no funciona
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// Lo recomendable es:
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
La razón por la que el servidor no utiliza el cache, es porque usamos la función CURDATE(). Esto también es válido para funciones como NOW(), RAND(), etc…, porque simplemente el resultado de la función puede cambiar, MySQL decide deshabilitar el cache. Agregando solo una línea de PHP, antes de la consulta podemos solucionarlo y de esa manera mejoraremos nuestras consultas.
2. Usando EXPLAIN para tus consultas SELECT
Si usamos la keyword EXPLAIN podemos mejorar la forma en que MySQL puede ejecutar nuestra consulta. Esto puede ayudarnos a detectar los cuellos de botella y otros problemas con las consultas y la estructura de la tabla.
El resultado de una consulta EXPLAIN nos mostrará de que forma la indexación se usa, cuando la tabla comienza a escanearse y ordenarse, etc…
Tomar una consulta SELECT (preferiblemente una compleja con algún join), y agregar la palabra reservada(keyword) EXPLAIN delante de él, lo cual puedes usar PHPmyAdmin. Esto le mostrará como resultado una agradable tabla.
3. Usar LIMIT 1 cuando solo obtenemos un solo resultado.
Algunas veces cuando consultas tus tablas, ya sabes que vas a buscar un solo resultado. Podrías ser que busques un único registro o verificar la existencia de un cierto número de registros que satisfacen la condición WHERE.
En tal caso, agregar “LIMIT 1” a tus consultas puede incrementar la performance. De esta forma, el servidor dejará de escanear tales registros luego de encontrar 1, en vez de recorrer toda la tabla o índices.
Por ejemplo:
view plaincopy to clipboardprint?
// Lo que intuitivamente hacemos
$r = mysql_query("SELECT * FROM user WHERE state = 'Formosa'");
if (mysql_num_rows($r) > 0) {
// ...
}
// Lo que recomendable:
$r = mysql_query("SELECT 1 FROM user WHERE state = ‘Formosa' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
4. Indexar los campos de búsqueda
Indexar no solo la clave primaria o claves únicas. O sea, si existe alguna columna en la tabla que la utilizará para alguna búsqueda también deberías indexarla.
Esta regla también es válida para un string parcial donde usamos like por ej. “nombre LIKE ‘a%’”. Cuando buscamos desde el inicio del string, MySQL es capaz de utilizar el índice en la columna.
También es importante que tengas presente en que casos no puedes usar la indexación. De hecho, cuando buscas por una palabra (ej. “WHERE contenido LIKE ‘%algo%’”), no verás un beneficio con respecto a una indexación normal. Sin embargo si usas todo el texto mysql buscará o construirá su propia solución de indexación.
5. Indexar y usar el mismo tipo de columnas cuando hacemos JOINs.
Si tu aplicación contiene muchas consultas JOIN, necesitas asegurarte que la columna que vas a unir están indexadas en ambas tablas. Esto afecta como MySQL internamente optimiza la operación JOIN.
También, la columna que están unidas, necesitan ser del mismo tipo. De hecho, si unes una columna DECIMAL, a un columna INT desde otra tabla, MySQL habilitará para usar al menos una de los indexados. Aunque la codificación de los caracteres necesitan ser del mismo tipo para columnas tipo string.
view plaincopy to clipboardprint?
// Buscando Empresas de un pais
$r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");
//Ambas columnas de paices deberían indexarse
//y ambos deberían tener el mismo tipo de codificación
// o MySQL puede hacer una búsqueda total de la tabla.
6. No usar ORDER BY RAND()
Esto es un de esos trucos que suenan lindo, y muchos programadores novatos caen en esta trampa. No sabes el terrible cuello de botella que puedes crear una vez que empiezas a usar esto en tus consultas.
Si necesitas filas al azar para tus resultados, hay otros buenas maneras de hacerlo. Por supuesto que se necesita de código adicional, pero hay garantía de que te evitaras un gran cuello de botella, a medida que tus datos crecen. El problema es que MySQL comenzará la operación RAND() (el cual necesita mas poder de procesamiento) por cada fila en la tabla antes de ordenarla y devolver solo 1 fila.
view plaincopy to clipboardprint?
// Lo que hay que evitar hacer
$r = mysql_query("SELECT nombre FROM usuario ORDER BY RAND() LIMIT 1");
// Optimizado:
$r = mysql_query("SELECT count(*) FROM usuario");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT nombre FROM usuario LIMIT $rand, 1");
Entonces, tomas un número aleatorio menor que el número de resultados y usas lo usas en tu clausula LIMIT.
7. Evitar usar SELECT *
La mayor parte de los datos lo lees desde la tabla, entonces tu consulta se hace lenta e incrementas el tiempo que toma en realizar la operación. Así, cuando el servidor de base de datos está separado del servidor web, tendrás mayor delay en realizar la transferencia entre ambos servidores.
Es un buen habito siempre especificar que columnas necesitas cuando haces un SELECT.
view plaincopy to clipboardprint?
// Lo que hacemos intuitivamente
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// Lo Recomendable:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Bienvenido {$d['username']}";
// Las diferencias son mayores con un grupo más grande de resultados.
8. Por lo menos debe haber un campo ID
En cada tabla hay una columna ID que es la PRIMARY KEY, AUTO_INCREMENT y generalmente INT. Asimismo preferentemente UNSIGNED, desde luego el valor no puede ser negativo.
Aún, si tienes una tabla de usuarios con un único campo de usuario, y esta no debe ser tu clave primaria, ya que los campos como VARCHAR, son muy lentos en las búsquedas y tendrás una mejor estructura en tu código referiendote a todos los usuarios con su id interno.
Existen también operaciones internas propias de MySQL, que usa internamente el campo de clave primaria. El cual es mas importante que configurar la base de datos.
Una posible excepción a la regla son las “tablas asociadas”, usadas por el tipo de asociación mucho-a-mucho entre 2 tablas. Por ejemplo una tabla “posts_tags” que contiene 2 columnas: post_id, tag_id el cual se usa para la relación entre las 2 tablas llamadas “post” y “tags”. Estas tablas pueden tener una PRIMARY_KEY que contiene ambos campos.
9. Mejor ENUM que VARCHAR
Las columnas del tipo ENUM son más rápidas y compactas. Internamente son almacenados como TINYINT, sin embargo pueden contener y mostrar valores string. Esto es ideal para ciertos campos.
Si tienes un campo, que contendrá solo un subconjunto de valores, es conveniente usar ENUM antes que VARCHAR. Por ejemplo, si tienes una columna llamada “estado”, solo contendrá los valores, “activo”, “inactivo”, “pendiente”, etc…
Existe una forma de conseguir una “sugerencia” por parte del mismo MySQL en como estructurar tu tabla. Cuando tienes un campo VARCHAR, puede sugerirte cambiar el tipo de la columna a ENUM. Esto lo realizas usando la llamada PROCEDURE ANALYSE().
10. Conseguir sugerencias con PROCEDURE ANALYSE()
PROCEDURE ANALYSE() le dirá a MySQL que analice la estructura de la columna y los datos actuales en tu tabla, lo cual lo hace con ciertas sugerencias para ti. Por supuesto, esto es útil si los datos de la tabla sean los actuales, porque tienen n rol fundamental en la decisión.
Por ejemplo, si creas un campo INT como clave primaria, pero sin embargo no tienes muchas filas, podrá sugerirte usar MEDIUMINT, o si estas usando un campo VARCHAR, podría sugerir convertirlo a ENUM, si solo hay valores únicos.
También puedes correr clickeando en “Proponer estructura de tabla” de phpMyAdmin, en la vista de tu tabla.
Ten presente que estas son solo sugerencias. Y si tu tabla va a crecer enormente, puede ser que no debas seguir estas sugerencias. La decisión es tuya, si es conveniente o no.
11. De ser posible utilizar NOT NULL
Al menos que tengas una razón específica para usar valores NULL, deberías siempre configurar tus columnas como NOT NULL.
Ante todo, debes preguntarte si hay alguna diferencia entre tener un valor string vacío o un valor NULL (para campos INT:0 vs NULL). Si no hay razón para tener ambas, no necesitas tener un campo NULL. Sabías que Oracle considera NULL y un string vacío como lo mismo?
Las columnas NULL requieren espacio adicional y pueden agregar complejidad a tus sentencias de comparación. Solo los ayuda cuando tu puedes. Sin embargo, entiendo que algunas personas deberían tener una razón específica, para incluir valores Nulos, el cual no es siempre malo.
De la documentación de MySQL:
“Las columnas NULL requieren un espacio adicional en la fila del registro si sus valores son NULL. Para tablas MyISAM, cada columna toma un bit extra, que ronda cerca del byte.
12. Preparando sentencias
Hay múltiples beneficios de usar sentencias preparadas, por razones de performance y seguridad.
Las Sentencias pre configuradas filtrará las variables que se unen a ellos por defecto, el cual es ideal para proteger tu aplicación contra ataques de inyección SQL. Puedes por supuesto filtrar tus variables manualmente, pero estos métodos son más propensos a errores humanos y olvido por parte del programador. Esto es al menos un problema menor cuando usas alguna clase de framework o ORM.
Dado que la atención se centra en el rendimiento, debería mencionar los beneficios en esta área, los cuales son mas significantes cuando la misma consulta comienza a usarse varias veces en la aplicación. Puedes asignar diferentes valores a la misma sentencia pre configurada, sin embargo MySQL solo tendrá que analizar una vez.
También en las últimas versiones de MySQL envía sentencias pre configuradas en forma binaria nativa, el cual es más eficiente y puede ayudar a reducir el delay de red.
Para usar sentencias pre configuradas en PHP puedes consultar la extensión mysqli o usar una base de datos con la capa de abstracción como la DOP.
view plaincopy to clipboardprint?
// Crear la sentencia preparada
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// unir parametros
$stmt->bind_param("s", $state);
// ejecutar
$stmt->execute();
// unir a una variable de resultado
$stmt->bind_result($username);
// buscar valores
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
13. Consultas sin búfer
Normalmente cuando realizas una consulta desde un script, espera hasta que la ejecución finalice antes de continuar. Lo puedes cambiar usando consultas sin búfer.
Hayn una explicación extensa en la documentación de PHP para la función mysql_unbuffered_query():
“mysql_unbuffered_query() envia la consulta SQL a MySQL sin ir a buscar automáticamente y almacenar en búfer las filas resultantes como lo hace mysql_query(). Esto ahora una enorme cantidad de memoria con las consultas SQL que producen un gran número de resultados y puedes empezar a trabajar con los resultados inmediatamente luego de recuperar la primer fila ya que no tienes que esperar hasta que la consulta SQL se haya realizado.”
Sin embargo, esto tiene ciertas limitaciones. Tienes que leer todas las filas o llamar a mysql_free_result() antes de poder realizar otra consulta. Tampoco está permitido usar mysql_num_rows() o mysql_data_seek() en el conjunto de resultados.
14. Almacenar Direcciones IP como UNSIGNED INT
Muchos programadores crean un campo VARCHAR(15) sin darse cuenta que pueden almacenar direcciones IP como un valor entero. Con un INT puedes reducir a solo 4 bytes de espacio, y tener un campo de tamaño fijo en su lugar.
Tienes que asegurarte que tu columna es un UNSIGNED INT, porque la dirección IP usa todo el rango de un entero sin signo de 32 bit.
En tus consultas puedes usar el INET_ATON() para convertir una IP a entero, y INET_NTOA() en sentido contrario. Hay funciones similares en PHP llamadas ip2long() y log2ip().
view plaincopy to clipboardprint?
>$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
15. Tablas (estáticas) fijar la longitud son más rápidas
Cuando todas las columnas en una tabla son de “longitud-fija”, la tabla también es considerada “estática” o “long-fija”. Por ejemplo columnas que no son consideradas de longitud fija son: VARCHAR, TEXT, BLOB. Si incluyes solo 1 de estas columnas, la tabla deja de ser considerada de long-fija y tiene que ser tratada de forma diferente por el motor MySQL.
Las tablas de long-fija pueden mejorar su rendimiento porque el motor MySQL es más rápido al buscar a través de los registros. Cuando quiere leer una fila específica en la tabla, puede calcular rápidamente la posición. Si tiene una fila que no es de longitud fija, cada vez que hace una búsqueda, tiene que consultar el índice de la clave primaria.
También es más fácil para cachear y más fácil reconstruir luego de un accidente. Pero también pueden tener más espacio. Pero también, si conviertes un campo VARCHAR(20) a CHAR(20), tendrás siempre 20 bytes de espacio independientemente de que es lo que contenga.
16. Particionado Vertical
Particionado vertical es el acto de dividir la estructura de la tabla en forma vertical por razones de optimización.
Ejemplo 01: Podrías tener una tabla de usuarios que contiene “dirección”, que no se lee muy a menudo. Puedes elegir dividir la tabla y almacenar la información de la dirección en una tabla separada. De esa forma, la tabla de usuarios principal disminuirá de tamaño. Como es sabido, las tablas pequeñas funcionan más rápidas.
Ejemplo 02: si tienes una campo “ultimo_login” en tu tabla. Se actualiza cada vez que un usuario se loguea en tu página web. Pero cada actualización en la tabla causa que la cache de la tabla se actualice. Puedes poner el campo en otra tabla para actualizar la tabla de usuarios a un mínimo.
Pero también es necesario para asegurarse de que no necesitas contantemente unir estas 2 tablas luego del particionado o podrías sufrir una reducción de rendimiento.
17. Dividir grandes consultas DELETE o INSERT
Si necesitas realizar una gran consulta DELETE o INSERT en un sitio web, necesitas ser cuidados de no perturbar el tráfico de la web. Cuando una gran consulta como esa es realizada, puede bloquear tus tablas y parar la aplicación web.
Apache corre muchos hilos/procesos en paralelo. Por lo tanto trabaja más eficientemente cuando el script finaliza de ejecutarse tan pronto sea posible, para que los servidores no experimenten demasiadas conexiones y procesos abiertas a la vez que consumen recursos, especialmente memoria.
Si el bloqueo de tus tablas finaliza luego de un largo período de tiempo largo (como 30segundos o mas), en un sitio web de alto tráfico, puede causar un error, el cual puede tomas un período prolongado de tiempo para aclarar o aún peor, un accidente en tu sitio web.
Si tienes algún script de mantenimiento que necesita borrar un número grande de filas, solo use la cláusula LIMIT para hacerlo en lotes y así evitar una congestión.
view plaincopy to clipboardprint?
while (1) {
mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) { // borrado listo break; } // incluso puede pausarlo un poco< usleep(50000); } 18. Las columnas pequeñas son más rápidas Con el motor de base de datos, el disco es tal vez el mayor cuello de botella. Conservar cosas pequeñas y mas compactas es usualmente una ayuda en términos de rendimiento para reducir la cantidad de transferencia. La documentación de MySQL tiene una lista de requerimientos de almacenamiento para todos los tipos de datos. Si se espera que una tabla tenga pocas filas, no hay razón para hacer una clave primaria como INT, en lugar de MEDIUMINT, SMALLINT o incluso en algunos casos TINYINT. Si no necesitas la variable tiempo, usar DATE en lugar de DATETIME. Solo asegúrese de dejar espacio razonable para crecer o podría terminar como Slashdot. 19. Elegir el motor correcto de almacenamiento Los dos motores principales en MySQL son MyISAM e InnoDB. Cada uno tiene sus pros y su contra. MyISAM es ideal para aplicaciones grandes lecturas, pero no es escalable cuando hay muchas escrituras. Incluso si acutalizas un campo de una fila, toda la tabla se bloquea, y ningún proceso puede leer de él hasta que la consulta finalice. InnoDB tiende a hacer un motor de almacenamiento más complicado y puede ser más lenta que MyISAM desde la mayoría de las aplicaciones pequeñas. Pero soporta bloqueo de filas, el cual es mejor escalable. Soporta también algunas de las más avanzadas características tales como transacciones. 20. Usar un Objeto Relacional Mapper Al usar un ORM (Objeto Ralacional Mapper), puedes ganar ciertos beneficios en performance. Todo lo que un ORM puede hacer, puede ser codificado manualmente también. Pero esto puede significar más trabajo extra y un alto nivel de experiencia. Los ORM son ideales para “Lectura lenta”. Esto es que puede recuperar valores solo cuando con necesarios. Pero hay que ser cuidadoso con ellos o puedes terminar creando demasiadas mini-consultas que pueden reducir el rendimiento. Los ORM pueden lotear sus consultas en las transacciones, el cual opera mucho más rápido que enviar consultas individuales a la base de datos. 21. Hay que ser cuidadoso con conexiones persistentes Conexiones persistentes significa reducir la sobrecarga de recrear conexiones a MySQL. Cuando una conexión persistente es creada, esta permanecerá aún cuando el script termine de correr. Dado que Apache rehúsa sus procesos hijos, la próxima el proceso corre como un nuevo script, y rehusará la misma conexión MySQL. mysql_pconnect() en PHP Suena fantástico en teoría. Pero puedes llegar a tener serios problemas con limitar las conexiones, el uso de la memoria y así sucesivamente. Apache corre en paralelo, y crea muchos procesos hijos. Esta es la principal razón que las conexiones persistentes no trabajan muy bien en este ambiente. Antes de considerar utilizar la función mysq_pconnect(), debes consultar con tu administrador de sistemas.
Fuente:
http://todounblog.com.ar/tips-para-mysql-buenos-usos
