La base de datos MySQL se ha convertido en la base de datos relacional de código abierto más popular del mundo debido a su gran rendimiento, consistencia, alta fiabilidad y facilidad de uso. Sin embargo estos beneficios que nos ofrece, muchas veces se ven afectados por la forma en que trabajamos en ella.
En este tutorial aprenderás una serie de tips los cuales serán de mucha utilidad y nos permitirá sacar el mayor provecho tanto desde el punto de vista del programador como del administrador de base de datos.
C:\Archivos de programa\MySQL\MySQL Server 5.6\bin
El directorio puede variar, por ejemplo, puede estar localizado en la raíz del disco C:, o en cualquier otro lugar donde podamos haber instalado MySQL. Para acceder a la consola de MySQL en Windows tendremos que estar situados dentro de ese directorio.
[color=rgb(169,169,169)]phpMyAdmin desde XAMPP[/color]
[color=#a9a9a9]phpMyAdmin desde CPanel[/color]
Teniendo estas 2 herramientas a la mano podremos comenzar a probar todas estas buenas prácticas que mostramos a continuación.
Estas son las 20 mejores prácticas MySQL:
1. Convención de Nomenclatura
Existen normas de codificación para cada lenguaje de programación, pero en términos de MySQL no hemos encontrado ninguna práctica de codificación universal que todo el mundo siga. Sin embargo revisando varios frameworks de código abierto basados en PHP, filtramos algunas normas de aplicación general lo cual nos ayudará a escribir consultas SQL de forma más rápida, eliminar confusiones y conflictos, tanto en la consulta como en el lenguaje de programación que usemos.
- Utiliza minúsculas ya que le ayuda en la velocidad de la escritura, evitará errores en caso de funciones sensibles a minúsculas y mayúsculas, etc.
- No uses espacios en blanco, use el guion bajo (_) en su lugar.
- No uses números en los nombres, solo caracteres alfabéticos ingleses.
- Utiliza nombres comprensibles válidos.
- Los nombres deben explicarse por sí mismo.
- Los nombres no deben contener más de 64 caracteres.
- Evita usar prefijos.
- El nombre puede ser a la vez singular y plural, pero la base de datos representan una base de datos por lo que debe ser singular en lo posible.
- Evitar prefijos en lo posible.
- Los nombres de las tablas deben ir en singular: la tabla es una sola entidad así como lo es el modelo por lo que es extraño tener el nombre de la tabla en plural.
- Prefijos en el nombre de la tabla: Hemos visto muchas veces que las tablas tienen como prefijo el nombre de la base de datos o el nombre del proyecto. Algunas veces esto es necesario cuando en nuestro proyecto tenemos muchas bases de datos para superar la limitación de algunos proveedores de hosting. Pero si no es necesario y nuestro proyecto es pequeño evite usar prefijos.
- Usa una o dos palabras cortas en lo posible.
- Los nombres de los campos deben ser capaces de entenderse por ejemplo: precio, nombre_empresa, etc
- Nombre de la columna primaria: La clave principal puede tener el nombre de id o el nombre de la tabla _id. Eso dependerá de la elección.
- Evita el uso de palabras reservadas en los campos: *****, date, etc. En estos casos es preferible usar prefijos como registro_date, etc.
- Evita el uso de nombre de columnas con el mismo nombre de la tabla. Esto puede causar confusión al escribir las consultas.
- Evita nombres en siglas abreviadas o concatenadas.
2. Utilices siempre el tipo de datos adecuado
Usa tipos de datos en base a la naturaleza de los datos. Si utilizas tipos de datos irrelevantes esto puede consumir más espacio o puede dar lugar a errores.
3. Uso CHAR (1) sobre VARCHAR (1)
Si almacenaras una sola cadena de caracteres, utiliza CHAR(1) en lugar de VARCHAR(1) porque VARCHAR(1) se llevará un byte adicional para almacenar información. Con lo que tenerlo en cuenta a la hora de gestionar estos carcateres.
4. CHAR para datos fijos
Use datos tipo CHAR para almacenar solo los datos de longitud fija
Ejemplo:
Usando CHAR(1000) en lugar de VARCHAR(1000) consume más espacio si la longitud de los datos es inferior a 1000.
5. Evite el uso de formatos de fechas regionales
Cuando se utilizan tipos de datos:
- DATETIME
- DATE
Utilice siempre el formato AAAA-MM-DD o el formato de fecha ISO que se adapte a su motor de SQL. Otros formatos regionales como DD-MM-YYY, DD-MM-AAAA no serán almacenados correctamente.
6. Optimiza tus peticiones para el caché
La mayoría de servidores MySQL tienen habilitado el sistema de caché. Es uno de los métodos más efectivos para mejorar el rendimiento, que vienen de la mano del motor de base de datos. Cuando la misma petición se ejecuta varias veces, el resultado se obtiene de la caché, que resulta mucho más rápida.
El siguiente ejemplo es en PHP:
// Caché NO funciona
$r = mysql_query("SELECT nombre FROM usuarios WHERE registro >= CURDATE()");
// Caché SÍ funciona
$hoy = date("Y-m-d");
$r = mysql_query("SELECT nombre FROM usuarios WHERE registro >= ‘$hoy’");
La razón por la que no funciona en el primer caso es por el uso de CURDATE(). Puede aplicarse a todas las funciones no deterministas, como NOW() y RAND(). Dado que el resultado retornado por la función puede cambiar, MySQL decide deshabilitar la caché en esa consulta.
7. Evite el uso de “SELECT *” en sus consultas
Como regla general, cuanto más se leen los datos de las tablas, más lento se hace una consulta. Teniendo en cuenta que algunas tablas de producción pueden contener decenas de columnas, algunas de las cuales se compone de grandes tipos de datos, sería imprudente seleccionarlas todas.
Es un buen hábito especificar las columnas necesarias en su instrucción SELECT.
8. Usa LIMIT 1 Cuando sólo quieras una única fila
A veces, cuando estás realizando consultas a tus tablas, y sabes que sólo necesitas una única fila. En estos casos debes solicitar a la base de datos un único resultado, o de lo contrario comprobará todos y cada uno de las coincidencias de la cláusula WHERE.
En estos casos, añadir LIMIT 1 a tu query puede mejorar significativamente la velocidad. De esta forma la base de datos dejará de escanear resultados en el momento que encuentre uno, en lugar de recorrer toda la tabla o un índice.
// Tengo usuarios de Madrid?
// lo que NO hay que hacer:
$r = mysql_query("SELECT * FROM user WHERE ciudad = ‘Madrid’");
if (mysql_num_rows($r) > 0) {
// …
}
// mucho mejor:
$r = mysql_query("SELECT 1 FROM user WHERE ciudad = ‘Madrid’ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}[color=#a9a9a9]Recomendación LIMIT[/color]
9. Uso de ***** BY
El uso del ***** BY puede ralentizar el tiempo de respuesta en entornos multiusuario. Por lo que recomendamos que se use la cláusula ***** BY solo cuando sea necesario.
No abuses de su uso.
10. Elija Motor de base adecuada
Si desarrollas una aplicación que lee los datos con más frecuencia que la de escritura.
(por ejemplo: motor de búsqueda), selecciona el motor de almacenamiento MyISAM.
Si desarrollas una aplicación que escribe datos con más frecuencia que la de lectura
(por ejemplo: transacciones bancarias en tiempo real), elegir el motor de almacenamiento InnoDB.
La elección del motor de almacenamiento incorrecto afectará el rendimiento de sus consultas.
11. Use la cláusula EXISTS siempre que sea necesario
Si desea comprobar la existencia de datos, no utilice:
If (SELECT count(*) from Table WHERE col='algún valor')>0A cambio use la cláusula EXISTS:
If EXISTS(SELECT * from Table WHERE col='algún valor')Que es más rápido en el tiempo de respuesta.
12. Usa EXPLAIN en tus consultas SELECT
Utilizar la palabra clave EXPLAIN te dará muchos detalles internos de lo que hace MySQL para ejecutar tu consulta. Esto te puede ayudar a detectar los cuellos de botella y otros problemas con tu consulta o la estructura de la tabla.
El resultado de una consulta EXPLAIN te mostrará los índices que se están utilizando, cómo se está explorando la tabla, cómo se está ordenando, etc,
Selecciona una consulta SELECT (preferiblemente una compleja, con uniones), y añade la palabra EXPLAIN al principio de todo. Te devolverá los resultados en una sencilla tabla. Por ejemplo, supongamos que me he olvidado de poner un índice a una columna, nos mostraría la siguiente pantalla:
Después de añadir el índice a la tabla estado quedaría así:
13. Indexa, y utiliza el mismo tipo de columna para los Join
Si tu aplicación contiene muchas sentencias JOIN debes asegurarte de que las columnas que unes están indexadas en ambas tablas. Esto afecta en cómo MySQL optimiza internamente las operaciones JOIN.
Además, las columnas que vas a unir deben ser del mismo tipo. Por ejemplo, si estás uniendo una columna de tipo DECIMAL con una columna de tipo INT de otra tabla, MySQL no será capaz de usar al menos uno de los dos índices. Incluso la codificación de caracteres necesita ser del mismo tipo para las columnas de tipo String.
// buscando compañias en mi ciudad
$r = mysql_query("SELECT nombre_companyia FROM usuarios
LEFT JOIN companyias ON (usuarios.ciudad = companyias.ciudad)
WHERE usuarios.id = $user_id");Ambas columnas ciudad deben estar indexadas y ambas deberían ser del mismo tipo y codificación de caracteres o MySQL tendrá que hacer un escaneo total de las tablas.
14. Usa NOT NULL si puedes
A no ser que tengas una razón específica para usar el valor NULL, deberías establecer siempre tus columnas como NOT NULL.
En primer lugar, pregúntate a tí mismo si habría alguna diferencia entre tener una cadena vacía y un valor NULL (o para campos INT: 0 contra NULL). Si no hay problema entre los dos valores, no necesitas un campo NULL. Las columnas NULL necesitan espacio adicional y pueden añadir complejidad a tus sentencias de comparación. Simplemente evítalas siempre que puedas.
En cualquier caso, entendemos que en algunos casos muy específicos haya razón para usar columnas NULL, lo cual no es siempre algo malo.
15. Las tablas de tamaño fijo (Estáticas) son más rápidas
Cuando cada una de las columnas en una tabla es de tamaña fijo (“fixed-length”), la tabla entera se considera “estática” o de “tamaño fijo”.
Algunos ejemplos de tipos de columna que NO son de tamaño fijo son:
- VARCHAR
- TEXT
- BLOB
Si incluyes sólo uno de estos tipos de columna, la tabla dejará de ser de tamaño fijo y tendrá que ser tratada de forma distinta por el motor de MySQL.
Las tablas de tamaño fijo pueden incrementar la productividad porque para el motor de MySQL es más rápido buscar entre sus registros. Cuando quiere leer una fila en concreto de la tabla, puede calcular rápidamente la posición que ocupa. Si el tamaño de fila no es fijo, cada vez que tiene que buscar, ha de consultar primero el índice de la clave primaria.
También resultan más sencillas de cachear, y de reconstruir después de un accidente. Pero por otra parte también podrían ocupar más espacio.
16. Particionado Vertical
El particionado vertical es el acto de separar la estructura de tu tabla de forma vertical por razones de optimización.
Ejemplo 1:
Seguramente tendrás una tabla de usuarios que contiene una dirección postal, la cual no se utiliza muy a menudo. Aquí podrías dividir la tabla y almacenar las direcciones en una tabla separada. De esta forma tu tabla de usuarios principal tendría un tamaño más ajustado. Como sabes, cuantas más pequeñas más rápidas son las tablas.
Ejemplo 2:
Tienes un campo de “ultimo_acceso” en tu tabla. Se actualiza cada vez que un usuario accede a tu página. Pero cada acceso hace que la cache de consultas de esa tabla se libere. Lo que puedes hacer es colocar este campo en otra tabla para que las modificaciones en tu tabla de usuarios se mantengan al mínimo.
Pero también tienes que asegurarte de que no necesitas juntar las dos tablas constantemente después del particionado o sufrirás una caída en el rendimiento, justo lo contrario a lo que buscábamos.
17. Almacena las direcciones IP como UNSIGNED INT
Muchos programadores crearían un campo VARCHAR(15) sin darse cuenta de que pueden almacenar las direcciones IP como números enteros. Cuando usas un INT sólo haces uso de 4 bytes en la memoria, y cuenta además con un tamaño fijo en la tabla.
Pero hay que asegurarse de que la columna sea UNSIGNED INT (entero sin signo) porque las direcciones IP hacen uso de todo el rango de 32 bits sin signo.
En tus consultas puedes utilizar la función INET_ATON() para convertir una dirección IP en entero, e INET_NTOA() para hacer lo contrario. También existen funciones parecidas en PHP llamadas ip2long() y long2ip().
18. Crea Vistas para simplificar el uso común en las tablas
Las vistas ayudan a simplificar tanto los esquemas complejos, como la implementación de seguridad. Una manera en la que contribuyen a la parte de seguridad es que permite ocultar los nombres de los campos a los desarrolladores.
También se puede usar para filtrar columnas no indexadas, dejando solo los campos que se muestran más rápido en la búsqueda.
19. No uses ***** BY RAND()
Éste es uno de esos trucos que suenan muy bien a primera vista, y donde muchos programadores novatos suelen caer. Puede que no hayas caído en la cuenta del increíble cuello de botella que se puede provocar si utilizas esta técnica en tus peticiones.
Si en verdad necesitas tablas aleatorias para tu resultado, hay formas mucho mejores de hacerlo. Está claro que ocuparán más código, pero estarás previniendo un posible embotellamiento que aumenta exponencialmente a medida que tu contenido crece.
El problema es que MySQL tendrá que ejecutar RAND() (que requiere de potencia de procesado) para cada una de las filas antes de ordenarlas y devolver una simple fila.
// la forma de NO hacerlo:
$r = mysql_query("SELECT nombreusuario FROM usuario ***** BY RAND() LIMIT 1");
// mucho mejor:
$r = mysql_query("SELECT count(*) FROM usuario");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] – 1);
$r = mysql_query("SELECT nombreusuario FROM usuario LIMIT $rand, 1");De forma que seleccionas un número aleatorio inferior a la cantidad de resultados y lo usas como el desplazamiento en la cláusula LIMIT.
20. Optimizar la cláusula WHERE
Estos son algunos consejos para optimizar la cláusula WHERE:
- Eliminar los paréntesis innecesarios. Por ejemplo:
DE : (a<b AND b=c) AND a=5 A: b>5 AND b=c AND a=5
- COUNT(*) esta optimizado para devolver un SELECT de manera mucho más rápida, siempre y cuando sea a una tabla y sin usar WHERE. Por ejemplo:
SELECT COUNT(*) FROM tabla.
- La opción SQL_SMALL_RESULT, puede usarse con GROUP BY o DISTINCT para indicar que el conjunto de resultados es pequeño. En este caso, MySQL utiliza tablas temporales muy rápidas para almacenar la tabla resultante en lugar de usar ordenación.
