MySQL es el segundo sistema de gestión de bases de datos relacionales de código abierto más popular del mundo. Se utiliza en todo el mundo debido a su rendimiento rápido constante, alta confiabilidad y facilidad de uso. Este artículo presenta algunas de las mejores prácticas de MySQL.

Con ellos, puedes practicar SQL y plantearte algunos ejercicios de SQL, aprender sobre el límite de SQL y la práctica de SQL en línea, así como sobre los problemas de práctica de SQL más comunes.

Le ayudará a comprender SQL de número de fila, SQL superior, compensación de límite de MySQL y práctica en línea de SQL.

Si está buscando una manera agradable y fácil de mostrar una gran cantidad de filas y columnas a los visitantes de su sitio web de WordPress con tablas y gráficos interactivos coloridos, informativos y receptivos, puede usarlo con el complemento wpDataTables, verifique el precio y todos los funcionalidades que da aquí.

1 Utilice siempre el tipo de datos adecuado

Una de las mejores prácticas más importantes de MySQL es usar tipos de datos basados en la naturaleza de los datos. El uso de tipos de datos irrelevantes puede consumir más espacio o generar errores.

Por ejemplo: el uso de varchar (20) en lugar del tipo de datos DATETIME para almacenar valores de fecha y hora generará errores en los cálculos relacionados con la fecha y la hora. Además, es posible que se almacenen datos no válidos.

2 Use CHAR (1) sobre VARCHAR (1)

VARCHAR (1) toma bytes adicionales para almacenar información, por lo que si ingresa un solo carácter, es mejor usar CHAR (1).

3 Use el tipo de datos CHAR para almacenar solo datos de longitud fija

Por ejemplo: si la longitud de los datos es inferior a 1000, usar char (1000) en lugar de varchar (1000) consumirá más espacio.

4 Evite el uso de formatos de fecha regionales

Cuando utilice el tipo de datos DATETIME o DATE, utilice siempre el formato de fecha AAAA-MM-DD o el formato de fecha ISO adecuado para su motor SQL. Los formatos regionales como DD-MM-YYYY o MM-DD-YYYY no se almacenarán correctamente.

5 columnas de clave de índice

Es deseable que la consulta devuelva el resultado rápidamente, así que asegúrese de indexar las columnas que se usan en las cláusulas JOIN.

En caso de que use la declaración UPDATE que involucre más de una tabla, asegúrese de indexar todas las columnas que se usan para unir las tablas.

6 No use funciones sobre columnas indexadas…

… porque entonces el índice pierde su propósito.

Por ejemplo, suponga que desea obtener datos donde los dos primeros caracteres del código de cliente son AK. Escribe:

SELECT columns FROM table WHERE customer_code like 'AK%'

y no escribas

SELECT columns FROM table WHERE left (customer_code,2)='AK’

¿Por qué? El primer ejemplo hará uso del índice, lo que resultará en un tiempo de respuesta más rápido.

**7 Use SQL SELECT * solo si es necesario**

Siguiendo la lista de las mejores prácticas de MySQL, no use ciegamente SELECT * en el código. Si la tabla tiene muchas columnas, se devolverán todas. Esto ralentizará el tiempo de respuesta, especialmente si envía el resultado a una aplicación front-end.

En su lugar, escriba explícitamente los nombres de las columnas que realmente se necesitan.

Nota: recuerde que todas las instrucciones SELECT requieren una cláusula WHERE.

8 Use la cláusula ORDER BY solo si es necesario

Si desea mostrar el resultado en la aplicación frontal, deje que ORDENE el conjunto de resultados. Si hace esto en SQL, el tiempo de respuesta puede ralentizarse en el entorno multiusuario.

9 Elija un motor de base de datos adecuado

Si desarrolla una aplicación que lee datos con más frecuencia que escribe (por ejemplo, un motor de búsqueda), elija el motor de almacenamiento MyISAM.

Elegir el motor de almacenamiento incorrecto afectará el rendimiento.

10 Utilice la cláusula EXISTS siempre que sea necesario

Para verificar la existencia de datos, use la cláusula EXISTS, que es más rápida en el tiempo de respuesta. Por ejemplo, utilice:

If EXISTS(SELECT * from Table WHERE col=’some value’)

No utilice :

If (SELECT count(*) from Table WHERE col=’some value’)>0

11 EXPLICA tus consultas SELECT

Si usa la palabra clave EXPLAIN, puede obtener información sobre lo que MySQL está haciendo para ejecutar su consulta. Esto puede ayudarlo a detectar problemas con sus consultas o estructuras de tablas (por ejemplo, cuellos de botella).

Una consulta EXPLAIN muestra qué índices se están utilizando, cómo se escanea, clasifica la tabla, etc.

Todo lo que tiene que hacer es agregar la palabra clave EXPLICAR delante de una consulta SELECCIONAR (preferiblemente una consulta compleja con uniones). Además, si usa phpmyadmin para esto, sus resultados se mostrarán en una buena tabla.

12 Use LIMIT 1 cuando obtenga una fila única

A veces, sabe de antemano que está buscando solo una fila al consultar sus tablas. Por ejemplo, es posible que obtenga un registro único o simplemente verifique la existencia de cualquier número de registros que satisfagan su cláusula WHERE.

En tales casos, querrá usar la función de límite de MySQL para aumentar el rendimiento. Esta es otra de las mejores prácticas de MySQL: simplemente agregue LIMIT 1 a su consulta. De esta forma, el motor de la base de datos no tendrá que recorrer toda la tabla o el índice. Dejará de escanear cuando encuentre solo 1 registro de lo que está buscando.

// do I have anyusersfrom Alabama?

// qué NO hacer:
$r = mysql_query(«SELECCIONAR * DESDE usuario DONDE estado = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// mucho mejor:
$r = mysql_query(«SELECCIONE 1 DEL usuario DONDE estado = ‘Alabama’ LÍMITE 1");
si (mysql_num_rows($r) > 0) {
// …
}

13 Indexe y use los mismos tipos de columna para las uniones

Otro consejo vital de las mejores prácticas de MySQL: si su aplicación tiene muchas consultas JOIN, asegúrese de que las columnas por las que se une estén indexadas en ambas tablas. Esto afecta la optimización interna de la operación de combinación de MySQL.

Además, las columnas unidas deben ser del mismo tipo. Por ejemplo, si une una columna DECIMAL a una columna INT de otra tabla, MySQL no podrá usar ninguno de los índices. Incluso las codificaciones de caracteres deben ser del mismo tipo para las columnas de tipo cadena.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// ambas columnas de estado deben estar indexadas
// y ambas deben ser del mismo tipo y codificación de caracteres
// o MySQL podría realizar exploraciones de tablas completas

14 Ocultar MySQL de Internet

Los administradores de bases de datos experimentados y el personal de seguridad lo saben: nunca aloje la base de datos en la raíz del servidor web.

Para las aplicaciones habilitadas para la Web, MySQL debe estar oculto detrás de un firewall. La comunicación debe habilitarse solo entre los servidores de aplicaciones y sus servidores web.

Otra opción es usar MySQL skip-networking. Cuando está habilitado, MySQL solo escucha las conexiones de socket locales e ignora todos los puertos TCP.

15 Utilice los tipos de datos más pequeños posibles

Dejame contarte una historia. Cuando asistía a la universidad, la filosofía era que «la memoria es escasa». Esos eran los días de los discos duros de 256 MB. Hoy en día, a nadie parece importarle un ápice la memoria o el espacio en el disco duro. La nueva filosofía es que «la memoria es barata». Puede que sea cierto en términos de dólares, pero la lectura de tipos de datos grandes lleva más tiempo que la lectura de tipos más pequeños. Los tipos de datos grandes requieren que se lean más sectores de disco en la memoria.

La moraleja es, ignore la tentación de saltar inmediatamente al tipo de datos más grande cuando diseñe sus tablas. Piense en usar un int en lugar de un bigint.

Además, evite los campos de texto de caracteres grandes (255) cuando un carácter varchar o más pequeño es suficiente.

Si utiliza el tipo de datos correcto, cabrán más registros en la memoria o en el bloque de claves de índice. Esto conduce a menos lecturas y un rendimiento más rápido.

16 Aproveche el almacenamiento en caché de consultas

El almacenamiento en caché de consultas es uno de los métodos más eficaces para mejorar el rendimiento. La mayoría de los servidores MySQL lo tienen habilitado por defecto.

La caché de consultas almacena el texto de una declaración SELECT junto con el conjunto de resultados correspondiente. Si el servidor recibe más tarde una declaración idéntica, recuperará los resultados de la caché de consultas en lugar de analizar y ejecutar la declaración nuevamente. La caché de consultas se comparte entre sesiones, por lo que un conjunto de resultados generado por un cliente puede enviarse en respuesta a la misma consulta emitida por otro cliente.

Sin embargo, por grandioso que sea, el almacenamiento en caché de consultas tiene sus limitaciones. Tome la siguiente declaración:

El problema aquí es que las consultas contienen ciertas funciones no deterministas, como NOW() y RAND(). MySQL no puede calcular tales funciones por adelantado, por lo que terminan sin almacenarse en caché.

Afortunadamente, hay una solución fácil para eso: puede almacenar los resultados de la función en una variable.

17 No editar archivos de volcado

Los archivos de volcado son muy engañosos y pueden causar corrupción. ¿Por qué? Si alguna vez ha visto los archivos de volcado creados por mysqldump, estará de acuerdo en que parecen archivos de texto normales e inofensivos. Es por eso que la mayoría de las personas los editan en un editor de texto estándar, lo que hace que aparezcan corrupciones.

Si alguna vez ha intentado editar archivos de volcado, rápidamente aprendió que son cualquier cosa menos un archivo de texto. Por lo tanto, la única forma garantizada de evitar problemas es dejar los archivos de volcado en paz.

18 Use la configuración de tamaño de bloque de MyISAM

La configuración de los tamaños de bloque en los índices de las tablas MyISAM es una de las mejores prácticas de MySQL. Se puede encontrar en los archivos .MYI en el búfer clave, así como en el disco. La configuración se ve así: myisam_block_size.

Tiene un valor predeterminado de 1k. Es bastante pequeño para ser óptimo en un sistema moderno. La mayoría de los sistemas de archivos utilizan bloques de mayor tamaño. Y sabemos que escribir un solo bloque de índice requiere una lectura y luego una escritura. El sistema operativo nunca tendrá que escribir en el disco subyacente siempre que el tamaño del bloque sea igual o mayor que el tamaño del bloque del sistema de archivos.

19 Activar delay_key_write

Delay_key_write está desactivado de forma predeterminada. Hay una razón para eso. Si experimentó un bloqueo en medio del proyecto, su base de datos podría corromperse.

Entonces, ¿por qué querrías encenderlo? La razón es simple. Debido a que delay_key_write garantiza que la base de datos no vacíe el archivo de clave MyISAM después de cada escritura. Por lo tanto, si está haciendo otra escritura en el futuro cercano, se ahorrará bastante tiempo.

Aquí hay otro consejo interesante que elegimos de las mejores prácticas de MySQL: Activar delay_key_write es diferente para cada versión. Para ver cómo activarlo en una versión específica, consulte el manual del sitio oficial de MySQL.

20 Use Stack Trace para aislar errores

Siguiendo estas mejores prácticas de MySQL, este consejo se tomó prestado de Sky SQL porque es demasiado simple y conveniente para omitirlo.

MySQL stack_trace se puede usar para aislar varios errores. En lugar de profundizar en todos los usos posibles, es posible que el programador quiera tomar nota de la facilidad con la que un puntero nulo puede arruinar su código.

Con este consejo de Sky SQL, la detección, el seguimiento y la reparación se vuelven mucho más fáciles.

21 MySQL cambiando la contraseña ROOT

Cambiar la contraseña de ROOT puede parecer básico, pero saber cómo hacerlo es igualmente importante para el sistema operativo de su hogar y para los servidores MySQL.

A veces sucede que los principiantes demasiado entusiastas y los usuarios experimentados distraídos no pueden entender por qué no pueden cambiar ciertas configuraciones y están devolviendo errores. Puede ser tan simple como darte acceso ROOT. Un usuario ni siquiera debería tener que buscarlo en Google.

Aquí se explica cómo configurar la contraseña ROOT y cómo cambiar la contraseña ROOT de un usuario:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Repara tus archivos de configuración

MySQL Tuner es un script de Perl que de alguna manera puede optimizar su rendimiento al sugerir cambios en sus archivos de configuración.

Si algunos consejos y trucos en MySQL son increíblemente convenientes, herramientas como MySQL Tuner son una bendición que merece estar en una categoría propia.

Es algo que pueden usar tanto principiantes como profesionales. MySQL Tuner no es un consejo específico para MySQL, por lo que hay una variedad de ajustes y modificaciones que se pueden aplicar. Cuanto más lo use, más ajustes podrá aprender a aplicar para su propio uso.

Puede parecer intimidante al principio, pero es por eso que puede encontrar el manual –man db, así como todas las notas, lecturas necesarias y configuraciones en la página de inicio oficial del proyecto. Con todo eso, MySQL Tuner debería convertirse rápidamente en su herramienta preferida para acelerar MySQL y probar sus archivos de configuración.

Pensamientos finales sobre las mejores prácticas de MySQL

En el mundo de la informática, MySQL es sin duda uno de los programas más importantes e influyentes que jamás haya aparecido. Es tan complicado y versátil, que justo cuando parece que se han descubierto todos los trucos y atajos posibles, alguien más interviene con una nueva forma de usar un nuevo argumento o escenario.

Hemos esbozado solo las mejores prácticas de MySQL que todos deberían conocer. Pero, cuanto más use MySQL, más usará estos. Puede practicar SQL en línea y establecer usted mismo pruebas de SQL. Luego, desarrollará sus propios trucos y encontrará sus propios consejos principales en los que puede confiar, como limitar el SQL, el número de fila de SQL y comprender por qué debe seleccionar MySQL en primer lugar.

Si disfrutó leyendo este artículo sobre las mejores prácticas de MySQL, también debería leer estos:

Fuente de grabación: wpdatatables.com