{"id":619,"date":"2010-06-02T19:04:16","date_gmt":"2010-06-02T18:04:16","guid":{"rendered":"http:\/\/www.sortea2.com\/blog\/?p=619"},"modified":"2013-10-15T19:23:57","modified_gmt":"2013-10-15T18:23:57","slug":"10-consejos-optimizar-mysql","status":"publish","type":"post","link":"https:\/\/www.sortea2.com\/blog\/2010\/06\/10-consejos-optimizar-mysql\/","title":{"rendered":"10 consejos para optimizar MySQL"},"content":{"rendered":"

En un proyecto web medianamente ambicioso llega siempre el momento en que la parte \u00absagrada\u00bb de la aplicaci\u00f3n es la Base de Datos<\/strong>. El problema reside cuando las tablas de nuestra Base de Datos empiezan a tener m\u00e1s de 10.000 registros. A partir de ese momento debemos pensar de diferente manera: los accesos deben optimizarse, el sistema de b\u00fasqueda ya es demasiado lento y a veces llega incluso al l\u00edmite de tiempo de ejecuci\u00f3n, ya no se puede hacer un ALTER TABLE sin tener que tener la tabla bloqueada unos segundos, etc. Cuando nos empezamos a encontrar con estos problemas es cuando no hemos hecho una predicci\u00f3n suficientemente buena y debemos actuar r\u00e1pidamente para remediarlo.<\/p>\n

Optimizar MySQL es una tarea que debemos tener en mente desde el minuto cero, aunque sin obsesionarse. Siempre es mejor avanzar que quedarse atascado.<\/p>\n

A continuaci\u00f3n os mostrar\u00e9 10 consejos que pueden acelerar los accesos a vuestras tablas:<\/p>\n

    \n
  1. \n

    Usa \u00edndices:<\/h3>\n

    Un \u00edndice es un sistema inform\u00e1tico que permite al gestor de base de datos (MySQL) acceder a la informaci\u00f3n (registros de una tabla) de una forma m\u00e1s directa que si tuviera que ir dato a dato comprobando si cumple las condiciones.<\/p>\n

    Los campos que solamos incluir dentro del \u00abWHERE<\/code>\u00bb de las consultas m\u00e1s comunes deben estar indexadas o bien controladas.<\/p>\n

    Los accesos v\u00eda PRIMARY KEY<\/code> son, con diferencia los m\u00e1s veloces. De hecho, cuando utilizamos el motor InnoDB, los datos se encuentran f\u00edsicamente ordenados por el valor de la clave primaria. Esto hace que acceder a los datos sea trivial.<\/p>\n

    Si sabemos que un determinado registro no va a poderse repetir, debemos incluirle un \u00edndice UNIQUE<\/code> y si vamos a realizar b\u00fasquedas sobre un campo de fecha por ejemplo, le podemos a\u00f1adir un INDEX<\/code> simple.<\/p>\n

    Siempre que indexamos un campo disminuimos la velocidad de escritura en la tabla porque cada vez que la manipulamos MySQL tiene que asegurarse de actualizar el \u00edndice. Hay que encontrar el equilibrio que permita a la aplicaci\u00f3n funcionar r\u00e1pidamente. Si una tabla recibe muchas modificaciones quiz\u00e1s haya que sacrificar algo de velocidad en las consultas.<\/p>\n

    Otro dato a tener en cuenta es que MySQL utiliza mayoritariamente \u00edndices B-Tree, en el cual se va creando un \u00e1rbol con las posibles combinaciones. Por ejemplo, en un \u00edndice compuesto por los campos (nombre, apellido1, apellido2) ser\u00e1 agrupado primero por nombre, luego por apellido1 y luego por apellido2. Una consulta que busque por apellido2 \u00fanicamente no utilizar\u00eda el \u00edndice. En cambio, una consulta por nombre ser\u00eda r\u00e1pida. Una consulta consultando por nombre, apellido1 y apellido2 ser\u00eda bastante \u00f3ptima tambi\u00e9n porque podr\u00edamos recorrer el \u00edndice de arriba a abajo para encontrar el dato.<\/li>\n

  2. \n

    Utilizaci\u00f3n de campos binarios<\/h3>\n

    Los tipos de dato binario son BINARY, VARBINARY y BLOB<\/code> que van en consonancia con CHAR, VARCHAR y TEXT<\/code>. Si el texto que vas a almacenar no va a tener una codificaci\u00f3n como UTF-8 es mucho m\u00e1s conveniente utilizar campos binarios, puesto que ocupan mucho menos espacio y las comparaciones se hacen como datos num\u00e9ricos<\/strong> internamente.<\/p>\n

    Por ejemplo, campos que guarden cadenas codificadas como MD5, campos de permalink <\/em>(URL amigable para buscadores), nombres de usuario que sepamos que no permiten caracteres extra\u00f1os, etc. pueden ser puestos como binarios. Por cada unidad de informaci\u00f3n utilizaremos menos bytes y por lo tanto el motor de almacenamiento de MySQL podr\u00e1 manejar los datos con m\u00e1s soltura.<\/li>\n

  3. \n

    Utiliza UNSIGNED<\/h3>\n

    Si al crear una tabla, sabes que un campo solamente contendr\u00e1 valores num\u00e9ricos positivos, utiliza UNSIGNED. Esto aumenta al doble la capacidad m\u00e1xima de los campos sin alterar el n\u00famero de bytes necesarios para almacenar cada dato. Por ejemplo, todos los campos que utilicen un AUTO_INCREMENT<\/code> deben utilizarlo, puesto que de la otra manera tendremos un rango enorme de n\u00fameros que jam\u00e1s utilizaremos.<\/p>\n

    En aplicaciones que manejen gran cantidad de datos es posible que un campo INT se nos quede corto para almacenar un AUTO_INCREMENT. Si estuviera UNSIGNED habr\u00edamos tenido un campo con el doble de vida \u00fatil.<\/li>\n

  4. \n

    Guarda informaci\u00f3n redundante<\/h3>\n

    Con esto nos estar\u00edamos saltando la normalizaci\u00f3n de las tablas. Pero la realidad es que en las aplicaciones web lo m\u00e1s com\u00fan es que por cada inserci\u00f3n de datos tengamos muchas m\u00e1s consultas de datos. Lo habitual es que la informaci\u00f3n cambie cada cierto tiempo pero que sea consultada a todas horas.<\/p>\n

    Puede ser \u00fatil tener campos calculados. Por ejemplo, total de comentarios en una entrada, n\u00famero de opiniones sobre un producto, ID del \u00faltimo comentario, etc.\u00a0 Cada vez que se realice una modificaci\u00f3n en esas tablas tendremos que hacer m\u00e1s trabajo, pero normalmente compensa.<\/li>\n

  5. \n

    No uses tipos de datos gigantes<\/h3>\n

    Me atrever\u00eda a decir que el 99% de las personas jam\u00e1s necesitar\u00e1n un campo LONGTEXT<\/code> a no ser que se trate de un gestor de contenidos o algo por el estilo; incluso habr\u00e1 muy pocas que necesiten un campo BIGINT<\/code> para uso normal. Un BIGINT ocupa cada n\u00famero 8 bytes y puede almacenar un n\u00famero de un 9 seguido de 18 ceros<\/strong> (aproximadamente). El campo LONGTEXT almacena hasta 4 billones y pico de caracteres, que pueden ser mucho m\u00e1s de 50 p\u00e1ginas de Word por ejemplo.<\/p>\n

    Los tipos de dato exageradamente grandes ocupan m\u00e1s espacio en disco y rara vez se utilizan por completo. Es por eso que es m\u00e1s recomendable usar los campos adecuados sin pasarse demasiado.<\/li>\n

  6. \n

    Trata de usar los simplificadores del lenguaje<\/h3>\n

    MySQL proporciona algunas maneras que ayudan a hacer las consultas m\u00e1s cortas y m\u00e1s f\u00e1ciles de entender. Estos m\u00e9todos no solo nos ayudan a nosotros, sino que internamente son mucho m\u00e1s eficientes. Por ejemplo, usar la expresion \" WHERE sorteo = 1 OR sorteo = 2 OR sorteo = 5\"<\/code> es mucho menos eficiente que la siguiente: \" WHERE sorteo IN(1,2,5)\"<\/code>.<\/p>\n

    Algunas de estas expresiones pueden ser BETWEEN, IN, LEAST, GREATEST o INTERVAL.<\/p>\n

    Ten en cuenta adem\u00e1s de que cuanto m\u00e1s cortas sean las consultas, menos bytes ocupar\u00e1n y menos tardar\u00e1 el servidor de MySQL en recibirlas. Sobre todo en conexiones remotas.<\/li>\n

  7. \n

    Intenta unificar consultas<\/h3>\n

    Intenta siempre hacer una consulta cuando normalmente lo har\u00edas con varias. INSERT puede insertar varios registros al mismo tiempo si se separan los \u00abVALUES\u00bb por par\u00e9ntesis y comas, por ejemplo: INSERT INTO sorteo VALUES (1,2, 3),(4,5,6),(7,8,9)<\/code>.<\/p>\n

    DELETE m\u00faltiples se pueden construir en vez de en bucle insertando los valores a borrar dentro de un IN()<\/code> de MySQL.<\/p>\n

    Tambi\u00e9n es posible modificar varios registros desde una consulta. Para ello tendremos que utilizar una sintaxis un tanto peculiar, pero que puede ser \u00fatil. Pod\u00e9is ver esa sintaxis en el siguiente enlace: http:\/\/stackoverflow.com\/a\/84111<\/a><\/p>\n

    Es \u00fatil sobre todo para modificaciones masivas a trav\u00e9s de un script.\u00a0 Por ejemplo, un script que descargue im\u00e1genes para cada producto y luego actualice el campo \u00abimagen\u00bb de la tabla \u00abproducto\u00bb. Ir uno a uno modificando cada registro ser\u00eda muy lento e intensivo. Podr\u00edamos agrupar las modificaciones de 10 en 10 registros para aliviar la carga de la base de datos.<\/p>\n

    Cada vez que mandamos una consulta tenemos que contar con el tiempo de ping, que es lo que tarda el cliente de mysql en \u00abllegar\u00bb al servidor. Si tenemos un ping de 10ms ninguna consulta tardar\u00e1 menos de 10ms porque es el tiempo m\u00ednimo. Si unificamos 100 consultas en una, esos 10ms solo los tenemos que perder una vez.<\/li>\n

  8. \n

    Evita operaciones matem\u00e1ticas<\/h3>\n

    No es buena idea hacer c\u00e1lculos complejos en una SELECT. Siempre que sea posible hay que traer los datos al lenguaje que utilices (PHP por ejemplo) y hacer estos c\u00e1lculos. Por ejemplo, realizar redondeos, medias, divisiones y dem\u00e1s puede resultar problem\u00e1tico y hay que tener siempre en mente que cuanto menos trabajo haga la Base de Datos, m\u00e1s r\u00e1pida ser\u00e1<\/strong>.<\/p>\n

    Normalmente las \u00fanicas operaciones matem\u00e1ticas que compensan son las asociadas a un GROUP BY.<\/li>\n

  9. \n

    Utiliza MyISAM <\/a>si es posible<\/h3>\n

    Un denominador com\u00fan en las aplicaciones web es que casi siempre se hacen m\u00e1s selects que inserts\/updates\/deletes. Si este es nuestro caso, podemos renunciar a las posibilidades relacionales que permite InnoDB <\/a>para tener un gestor m\u00e1s r\u00e1pido en las consultas.<\/p>\n

    Si recibimos muchas consultas de manipulaci\u00f3n de datos MyISAM es insostenible y deja corruptas las tablas con frecuencia. Debemos encontrar el equilibrio. Para un blog MyISAM es ideal. Para un br\u00f3ker online en tiempo real no lo es.<\/li>\n

  10. \n

    Utiliza el PROCEDURE ANALYSE() de MySQL<\/h3>\n

    Esta es una caracter\u00edstica muy desconocida de MySQL pero de gran utilidad. Se a\u00f1ade tras una consulta y lo que hace es procesarla y devolver estad\u00edsticas sobre cada campo seleccionado. Dice m\u00e1ximos, m\u00ednimos, medias y lo m\u00e1s importante, propone el tipo de campo \u00f3ptimo para cada columna.<\/p>\n

    Pru\u00e9balo t\u00fa mismo, ejecuta SELECT * FROM mytabla PROCEDURE ANALYSE(); y comprueba los resultados.<\/p>\n

    Ten en cuenta que a nada que haya menos de 100 posibles valores para un campo ya va a proponerte que utilices un ENUM. No siempre esto ser\u00e1 aplicable a tu aplicaci\u00f3n.<\/p>\n

    Este procedimiento es bastante desconocido pero es muy \u00fatil sobre todo cuando tenemos que empezar a dar soporte a tablas creadas por otras personas. Es posible que as\u00ed identifiquemos campos que en realidad no se usan, tipos de datos que son err\u00f3neos, etc.<\/li>\n<\/ol>\n

    En definitiva todos los puntos se refieren a que hay que tener control total de los datos que tenemos almacenados, siempre teniendo en cuenta las limitaciones y tratando de mantener una integridad de los datos.<\/p>\n

    Fuentes: MySQL Certification Study Guide<\/a>, Building Scalabe Web Sites (The Flickr way)<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

    En un proyecto web medianamente ambicioso llega siempre el momento en que la parte \u00absagrada\u00bb de la aplicaci\u00f3n es la Base de Datos. El problema reside cuando las tablas de nuestra Base de Datos empiezan a tener m\u00e1s de 10.000 registros. A partir de ese momento debemos pensar de diferente manera: los accesos deben optimizarse, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[46,47,44,45],"class_list":["post-619","post","type-post","status-publish","format-standard","hentry","category-tutoriales","tag-consejos","tag-guia","tag-mysql","tag-rendimiento"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/posts\/619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/comments?post=619"}],"version-history":[{"count":19,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/posts\/619\/revisions"}],"predecessor-version":[{"id":629,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/posts\/619\/revisions\/629"}],"wp:attachment":[{"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/media?parent=619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/categories?post=619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sortea2.com\/blog\/wp-json\/wp\/v2\/tags?post=619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}