30Ago
Desarrollo backend

Optimizar consultas en MySQL

Os reporto algunos consejos para optimizar nuestras consultas select de bases de datos MySQL.

  • Para que exista una buena optimización de las consultas, es fundamental partir de una buena base. Y que mejor base para una base de datos (valga la redundancia) que diseñar un modelo entidad relación para transformarlo posteriormente en un modelo relacional, y ya de este último obtener la base de datos SQL (LDD, LMD).
  • INDEXAR: Es quizás el apartado más importante, es primordial que nuestro modelo E/R > Relacional sea correcto, para deducir cuales serán las columnas de clave primaria y foránea que el gestor tomará como índices de consultas.
  • No menospreciar el valor de las claves foráneas, son de mucha importancia para optimización de consultas y relaciones.
  • Elegir el motor de almacenamiento adecuado (tipo de tabla): innoDB, MyISAM, etc, todo dependerá de lo que busquemos y en función de esto podemos optimizar bastante nuestra base de datos.
  • Las tablas con row_format, de tipo fixed son más rápidas en lectura que las dynamic. Este punto esta íntegramente relacionado con el punto 9.
  • En los select, solo se deben extraer los datos que necesitemos, cuanto menos empleo de “ * ”, mejor.
  • manu ha dado con un tip muy certero (en los comentarios), y es que si trabajamos con PHP y MySQL se debe usar siempre un SELECT COUNT(*) antes que un mysql_num_rows para contar los resultados.
  • No abusar del empleo de like. Si obligas al gestor de base de datos a examinar campos blob, text, la velocidad de la consulta descenderá notoriamente.
  • Los datos numéricos son lógicamente examinados y extraídos más rápido que los datos textuales.
  • Los campos Not Null agilizan la lectura, por tanto no se deben usar de forma desproporcionada los valores nulos.
  • Usar lo menos posibles group by y order by.
  • Los campos de longitud fija char son más rápidos en lectura (aunque mayores en peso), que los de longitud variable varchar.
  • Para las consultas multi-tabla (con relaciones), es preferible el empleo de uniones por medio de inner join en el from, que establecer uniones de índices como condiciones del where.
  • El orden de las cosas importa. Cuesta un poco pero es bueno reflexionar y buscar una consulta que basada en la lógica extraiga los datos recorriendo el menor número de posibilidades.
  • Probar nuestras consultas con EXPLAIN. Sobre este asunto hay información muy valiosa en MySQL Hispano (explain).
  • Usar datos reales, o que se aproximen. Este punto es también muy importante, me ha pasado de darle mil vueltas a una consulta (con explain) y ver porque no acababa de ser optimizada. Finalmente descubrí que el gestor en determinados casos prefiere leer TODO a usar índices, creyendo (acertadamente) que leyendo todo resolverá la situación antes que recuperando los índices. Esto sucede por ejemplo cuando en una tabla tenemos menos de 8 tuplas (filas).

El gestor incorpora un optimizador de consulta (muy muy bueno), que se encarga de analizar la consulta que has enviado para trasformarla en otra, de forma que se procese lo más rápido posible. Al final la idea es hacer trabajar poco a este optimizador.

Compartir:

20 Comments

  • igayoso

    Leí hace poco en el libro de O’Reilly titulado: «mySQL Advanced» que para una mejor optimización en las consultas multi-tabla, mejor usar la cláusula WHERE frente al INNER/LEFT/RIGHT JOIN.

    He probado las dos y no varía nada, por los menos en mis BBDD, no sé si dependerá de otros factores está optimización…

  • PerroVerd

    1. No siempre el modelo relacional es el más óptimo. Un recurso habitual que rompe este modelo consiste en partir una tabla (con una relación 1:1) con muchos campos en dos tablas, una con datos pequeños muy utilizados y otra con los datos menos utilizados.

    2. El modelo relacional no nos indica cuales pueden ser los indices fulltext, muy útiles para busquedas en campos de texto. Relacionado con 7, utilizar indices fulltext en estos casos.

    4. Normalmente es mejor utilizar un único modelo de tabla para poder ajustar más los valores del query_cache y similares. MyIsam siempre es más rápida en lecturas y innodb es más rápida en escrituras.

    5 y 11. Tampoco es correcto siempre, en muchos casos el overhead de datos que transmitimos es superior al de la busqueda.

    12. Un inner join es similar a un where, pero en el caso de los left y right join solo los podrías realizar con subqueries y no estoy tan seguro de que den un resultado más eficiente.

  • javiaurea

    Hola PerroVerd.

    1.- El modelo relacional es capaz de contemplar lo que dices. De todas formas hay que tener en cuanta las reglas de formalización.

    2.- El modelo entidad-relación, y el relacional es digno de muchisimo estudio. En muchos casos no se profundiza en el, pero en relacional también es posible representar claves conjuntas.
    De todas formas, la misión del modelo e/r es abstraer y contemplar el MUNDO que se desea representar, indistintamente del gestor de base de datos. No todos los motores de bases de datos incorporar tipos espaciales como full-text. Y pensar en localizar esos datos entonces sería negativo en niveles superiores.

    Por otro lado los full-text son un tipo especial de indices que es incluido (entre otros) en MyISAM de MySQL. Generalmente contiene un registro por cada palabra en cada campo indexado, y esto puede hacer que sean lentos. El libro que menciono igayoso explica los puntos bajos de los full-text.

    4.- Si es lo mismo que se ha dicho. Estoy de acuerdo.

    5 y 11.- Esto son consideraciones que no siempre se pueden cumplir, dependen de muchisimos factores. Pero la base es que los datos apoyados en tablas fijas son leídos más rápidamente que en tablas de tamaño dinámico, puesto que se ha de emplear un tiempo extra en calcular la mejor opción para ese tamaño dinámico.
    Y a nivel de varchar, char, sucede lo mismo. Con longitud dinámica mejoramos en que se resta peso, pero se pierde en velocidad.

    En las bases de datos siempre ha existido y existirá esa dualidad: PESO-VELOCIDAD, el trabajo de optimizando es variable y cada uno debe emplear su lógica. Es por eso que es normal las discrepancias.

    12.- He probado justamente esa cuestión y parecen siempre estar casi igual… Quizás en mysql 4 la diferencia es a favor del subquery, sin embargo en mysql 5 sucede lo contrario.

    Y en cuestión de legibilidad creo que es mas fácil de leer uniones para extraer 4 campos de tablas distintas que 4 subquerys.

  • igayoso

    @Javi: si, es el libro de Jeremy & Derek. Está muy bien, aunque lo compré para ver temas de replicación con ndb y no trae nada 🙁

    A mi me gusta más WHERE porque puedo ver a simple vista que tablas y schemas uso, ya que con el JOIN es un poco menos intuitivo y como de momento no noto la diferencia en las consultas 🙂

    Por aquí todo bien, aún con mucho calor y síndrome post-vacacional, espero que por allí todo bien…

  • javiaurea

    Si yo también prefería query hasta hace aproximadamente 15 días que me replantee esto en un proyecto y le di vueltas y vueltas. Ahora dejo el where solo para condiciones, pero todos mis trabajos anteriores tienen el where para uniones y van de maravilla 😀

    Lo de intuitivo, te doy la razón. No se si es por costumbre, pero el caso es que si es mas entendible una unión con where.

    Aquí lo del calor, pues en determinados días. Ya sabes como se pone Mieres y Ujo cuando hace calor jejeje (impresionante). Aunque en Madrid, cuando iba hacer examenes, es algo horrible, encima del aire cargado de la gran urbe 😀

    Que te sea leve el Post-Vacacional 😉
    Recuerdos para Vicent!

  • fesja

    Respecto al punto #10, supongo que es mejor hacer un order by, que luego ordenar el array. ¿O cuál es la mejor forma si no?

  • javiaurea

    Esa misma duda la tengo yo y como tu muchísima gente… No se porque razón no hay mucha información al respecto 🙁

    Yo en principio me inclino por usar el order by antes que delegar esa operación a PHP.

    Un saludo fesja.

  • david

    El orderby siempre en la consulta, a ver si no como paginas sin meter todo un array

  • javiaurea

    David, hay casos en los que se necesita de un group by funcionando conjuntamente con un order by, y probablemente inner, where, funciones, etc. Y sin necesidad de paginar los resultados.

    Son esos los casos a los que me refiero que hay poca información sobre que soluciones son más eficientes a la hora de construir la consulta.

  • Patricio

    Quería contar lo que sé de los INNER JOINS y los WHERE haciendo la misma función. A todos los efectos son idénticas. He hecho pruebas y siempre dan los mismos tiempos (creo que la traducción resulta en exactamente lo mismo para la base de datos), además de que también pueden hacerse LEFT JOINS y RIGHT JOINS en la cláusula WHERE sin mas que añadir un signo mas += o =+.

  • javiaurea

    Interesante Patricio.

    Esta claro que sobre este tema de where e inner jois hay mucho que debatir jejeje 😀

    Es probable que el propio motor de base de datos MySQL y su optimizador, transformen las consultas de where a inner jois o viceversa según interprete lo que es mejor, y al final es por esto que los resultados en tiempos son iguales.

    Un saludo y gracias por tu aportación.

  • Seo Valencia

    Leyendo todo esto, creo que lo mejor es hacer pruebas de todo tipo antes de mostrar el proyecto al público. Lástima que casi siempre andemos tan justos de tiempo.

  • manu

    Algo más, si trabajamos con PHP y MySQL, usar siempre un SELECT COUNT(*) antes que un mysql_num_rows para contar los resultados.
    La diferencia cuando trabajamos en tablas de muchos registros es notable.

    Buen artículo, gracias por compartirlo.

  • dani

    Hola, muy bueno el post.
    tengo una duda, la verdad no econtre respuesta navegando por la documentación de mysql
    ejemplo:
    tengo una tabla colid,col1,col2,col3,col4_longtext
    los indice busca(col1,col2,col3)

    ahora esto quiero saber, cuando hago un select * from mi_tabla where col1=x AND col2=y AND col3=z OR col3=a

    en el caso anterior usaria el indice ‘busca’?
    mi duda viene a causa de que uso el OR al final
    eso evita que use el indice ‘busca’?

  • dani

    Hola Javi,
    gracias por la respuesta, perdona no revise lo que escribí, pero en fin el post esta bueno y me sirvió mucho … ahora termine de optimizar unas tablas y veo que el server responde mas rápido

    Gracias!!!

  • javiaurea

    Hola dani, me alegro que te guste, muchas gracias.

    Respecto a tu pregunta, la verdad es que no la entiendo muy bien 🙁 de todas formas únicamente decirte que siempre que puedas usar índices úsalos! puesto que incrementan la velocidad. Creo que solo hay una excepción al uso de indices y es en el caso de que las tablas sean pequeñas, por lo cual no merece la pena indexar y al final se puede ver como un arma de doble filo.

  • manu

    Con respecto a lo que menciona -fesja- , alguno tiene idea? Lo pudo comprobar?
    Necesito realizar una consulta y traer muchísimos resultados, mas de 10000.
    El tema que hay una tabla vínculo many-many, que tiene mas de 10.000 registros.
    Al realizar un explain, mysql me devuelve que recorrió todos los resultados de la tabla. Ahí se genera un tiempo considerable, pero es mayor aún cuando tengo que hacer el ORDER BY.
    Utilizando el ORDER BY, la consulta tarda un 100% más, muchísimo :S!

Comments are closed for this post