Cargando

Ir a contenido



Ver Más! Que no te lo cuenten, es mejor VERLO 👀 !! En este vídeo verás una buena forma de entender cómo trabajamos para ayudarte. Únete, y danos tu Like 👍🏻 para ayudarnos, GRACIAS!


 


Las 20 mejores recomendaciones para MySQL

No te pierdas las 20 mejores prácticas donde aprenderás una buena serie de trucos para programar y también gestionar MySQL de forma óptima.


Escrito por el may 21 2016 21:49 mysql


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.

 

¿Qué herramienta usaremos?
Consola de comando MySQL']MySQL tiene un programa, que se llama con el mismo nombre de la base de datos (mysql) que sirve para gestionar la base datos por línea de comandos.

 

Windows
Se encuentra en un directorio como:

 

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.

 

Linux
Ver el siguiente enlace:

 

Acceder MySQL desde Linux

 

Mac
Los ordenadores con sistema Mac OS X tienen el terminal de línea de comandos integrada entre las aplicaciones disponibles. Para acceder se utiliza el mismo comando que en Linux.

 

phpMyAdmin
Es una herramienta de software libre escrito en PHP , la cual se utiliza para administrar MySQL a través de Internet. Si posees un entorno de desarrollo local instalado como XAMPP o WAMP, ya traerá esta herramienta instalada. Si posees un plan de hosting con algún panel administrativo la mayoria ofrece esta herramienta como administrador de base de datos MySQL.

 

 

recomendaciones-mysql-2.jpg

phpMyAdmin desde XAMPP

 

 

phpMyAdmin desde CPanel

 

 

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.

 

Reglas Generales
Ten en cuenta las siguientes reglas para evitar problemas.
  • 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.

 

Reglas para los nombres de base de datos
Siga todas las reglas generales anteriores.
  • 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.

 

Reglas para los nombres de las tablas
Use minúscula para los nombres de las tablas: MySQL es usualmente alojado en servidores Linux, es sensible a mayúsculas por lo que mejor práctica es poner los nombres de las tablas en minúsculas.
  • 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.

 

Nombre de los campos
Utilice todas las reglas anteriores, es decir, usar minúsculas, no utilizar espacios en blanco, no usar números, y evitar 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: order, 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.

 

Ejemplo
El uso de varchar(20) para almacenar valores de fecha y hora, en lugar de DATETIME esto puede dar errores durante los cálculos de los tiempos relacionados con la fecha y también es posible en el caso de almacenamiento de datos no válidos.

 

 


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) {
   // …
}
Recomendación LIMIT

 


9. Uso de ORDER BY


El uso del ORDER BY puede ralentizar el tiempo de respuesta en entornos multiusuario. Por lo que recomendamos que se use la cláusula ORDER 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')>0
A 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 ORDER 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 ORDER 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.

 


Tutoriales Relacionados


Sin comentarios, sé el primero!

No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!
10
VOTA
5
100%
4
0%
3
0%
2
0%
1
0%

  Información

  •   Publicado may 21 2016 21:49
  •   Actualizado dic 20 2016 20:39
  •   Visitas 11.3K
  •   Nivel
    Avanzado