Cómo trabajar con bases de datos grandes en MySQL
4.5 (90%) 2 votos

Primero que todo, quiero aclarar que por bases de datos “grandes” me refiero a aquellas que tienen más de 100.000 filas.

Una base de datos muy grande puede generar bastantes problemas, como por ejemplo que un sitio se demore una eternidad en cargar o incluso que deje de responder. Pero el problema no es la base de datos ni el motor. Dicen que cuando hay que trabajar con grandes cantidades de datos debe dejar de usarse MySQL y usar otros sistemas, pero finalmente una base de datos MySQL puede funcionar tan bien como una SQL si está bien configurada.

En base a mi experiencia, realmente lo complicado de las bases de datos grandes no son la cantidad de filas que tengan, sino la cantidad de datos que deben cruzarse.

Voy a poner un ejemplo práctico. Yo tengo una base de datos que tiene 3 tablas:

ciudades: 2.650.000 filas
regiones: 3850 filas
paises: 250 filas

Como se imaginarán, al tener tantos datos, las consultas serán lentas, así que vamos a ver cómo podríamos optimizarlas:

Una de las formas más eficientes de mejorar las consultas son los “índices” (no voy a entrar en detalle en este minuto acerca de los índices, pero hay muchísima información dando vueltas por ahí), para lo cual se deben definir las columnas que se indexarán. Lo que hacen los índices es generar una especie de “libro” donde se agrupan los valores de la columna definida y de esta manera es más rápido para la base de datos encontrar lo que busca. De esta forma, las consultas irán primero al índice y verán cuales son los elementos que cumplen con esas caracteríscticas y recién ahí irán a consultar esos datos (de esa manera no busca en toda la base de datos, sino en los que dice el índice).

Otra manera es utilizando LIMIT. Si estamos consultando algo que sabemos que retornará 1 sólo dato (por ejemplo cuando buscamos algo por ID), al ponerle LIMIT 1 se reducen considerablemente los tiempos. Esto es lógico pues al consultar dentro de 2.650.000 filas, si el sistema encuentra el dato en la fila n°4 (por ejemplo), al no tener un “LIMIT”, continuará consultando hasta el final de la base de datos. Por el contrario, si tiene el LIMIT, dejará de consultar apenas obtenga la cantidad de resultados especificada.

Otra cosa a considerar es que a utilizar LIKE (‘%xxx%’), el largo del string xxx influye mucho en los tiempos de respuesta. Por ejemplo, si busco (en la base de datos de las ciudades) alguna que contenga “%s%”, la consulta tarda 0,0022 seg. Si busco alguna que contenga “%santiago de %”, la consulta tarda 3,877.

Y finalmente, el GRAN problema es cuando se cruzan datos. La tabla paises tiene 3 datos: ID, nombre, region. La región es un ID que va asociado al ID de la tabla regiones (de ahí sacaré los nombres de cada región). Y la región pertenece a un país, cuyo nombre está en la tabla de paises (ID, nombre pais).

Si quiero pedir el nombre de la ciudad, el nombre de la región y el nombre del país de todas las ciudades que comiencen con “santiag%”, tendría una consulta como la siguiente:

SELECT ciudad.nombre, region.nombre, pais.nombre FROM ciudades, regiones, paises WHERE ciudad.nombre LIKE ("santiag%") AND ciudad.region=region.id AND region.pais=pais.id

Esta consulta, al tener que cruzar datos entre tablas demorará mucho tiempo, ya que obligaremos al motor de base de datos a tener en memoria los nombres de todas las ciudades, regiones y países y hacerlos coincidir. Mientras más WHERE apliquemos, más lenta se tornará la consulta.

¿Qué hacemos en casos como este, en que la consulta demore, por ejemplo, 35 segundos?… claramente no podemos dejar esperando a nuestros clientes todo ese tiempo. Para casos como estos, la solución es tan simple como hacer 3 consultas a la base de datos:

  1. Primero, se consulta el listado de paises y se guarda en un array asociativo, donde el Key sea el ID del pais y el valor sea el Nombre del pais.
  2. Luego se hace lo mismo con las regiones
  3. Finalmente se hace la consulta SOLAMENTE de las ciudades que partan con “santiag%” (sin cruce de datos). Entonces, cuando queramos mostrar los datos del pais y la región haremos algo como:
<? echo $regiones[ID_CIUDAD]; ?>, <? echo $paises[ID_CIUDAD]; ?>

Lo cual mostraría en pantalla algo como “Región Metropoitana, Chile”.

Esta manera, en algunos casos es mucho más eficiente que esperar 35 segundos por consulta, ya que como son 3 consultas simples, demorarían menos de 0,5 seg. cada una.

Estas son algunas formas de optimizar las consultas a la base de datos. Aunque se diga lo contrario por ahí, SI es posible manejar millones de datos en MySQL sin tener problemas de tiempos de respuesta. Sólo basta con configurar y optimizar las consultas.

Acerca del autor
Sebastián Barría Garcés Me dedico al desarrollo Web y programación hace más de 10 años, enfocándome en temas como HTML, CSS, Javascript, MySQL, PHP, Actionscript y manejo de servidores.

Originally posted 2015-12-22 14:38:13.

Cómo trabajar con bases de datos grandes en MySQL
Etiquetado en:        

4 thoughts on “Cómo trabajar con bases de datos grandes en MySQL

  • 22/12/2015 a las 15:35
    Enlace permanente

    Cuando anidas queryes, lo más recomendable y por temas de caché y rendimiento es utilizar Joins en lugar de los típicos “where’s”. Otra cosa recomendable es cachar la base de datos con algun sistema, cassandra de apache por poner un ejemplo.

    Responder
  • 12/10/2016 a las 18:52
    Enlace permanente

    Uffff….. Yo tenía un sistema donde hacía las consultas de unas tablas relacionadas, por separado (Como en el último ejemplo). Lo acabo de pasar a una sola consulta, compuesta, porque leo por todos lados que es mejor hacer una sola consulta.
    ¿Al final cómo es? 🙁
    En este caso se recomienda separarlas por ser LIKE ? O por ser una tabla grande? O qué?

    ¿Todo lo que me costó conseguir que funcione (porque son más de 3 tablas) y ahora resulta que era más eficiente antes?

    Responder
  • 10/03/2017 a las 17:47
    Enlace permanente

    me dio risa lo de hacer 3 query es vez de usar join, o al menos me esperaba “usa memcache” o algo productivo
    jajaja menos eso todo fue un buen consejo

    Responder
    • 13/04/2017 a las 08:33
      Enlace permanente

      Muy buenas, esa consulta se resuelve en un instante si el esquema esta bien diseñado. Esto quiere decir que las tablas tienes sus correspondientes claves primarias y claves foráneas. El plan de ejecución de esa consulta es un acceso por escaneo sobre la tabla ciudades con un dos accesos únicos sobre las tablas región y país.

      El problema viene dado cuando no se indexan las claves foráneas y no se dispone de las clave primarias apropiadas, es decir el diseño de base de datos es malo.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

¿Quieres recibir el contenido V.I.P de Preceptos Digitales?

¿Quieres recibir el contenido V.I.P de Preceptos Digitales?

Ingresa tu correo y te enviaremos contenidos especiales para quienes escuchan el Podcast!



Te has suscrito exitosamente! Nos hablamos!