Hoy les traigo un pequeño aporte de aceleramiento de consultas en bases de datos MySQL. En realidad recién estoy entrando en este campo de optimización, porque nunca había tenido la necesidad de realizar este tipo de procesos pero siempre hay una primera vez.
Bueno les voy a dar como ejemplo mi caso en un proyecto que he estado trabajando con el lenguaje de programación PHP, tenía los siguientes bucles anidados:
$listaEmpresaPartida = $this->gEmpresaPartida->listarEmpresaPartidaSinDetalle();
for($i = 0; $i < sizeOf($listaEmpresaPartida); $i++) {
$idEmpresaPartida = $listaEmpresaPartida[$i][0];
$partida = $listaEmpresaPartida[$i][1];
$empresa = $listaEmpresaPartida[$i][2];
$fechaInicio = $listaEmpresaPartida[$i][3];
$fechaFin = $listaEmpresaPartida[$i][4];
$detalleSinRelacion = $this->gDetalle->listarDetalleSinRelacionPorPartidaEmpresaFechaInicioFechaFin($partida, $empresa, $fechaInicio, $fechaFin);
for($j = 0; $j < sizeOf($detalleSinRelacion); $j++) {
$fob = $detalleSinRelacion->getFob();
$cantidad = $detalleSinRelacion->getCantidad();
$fecha = $detalleSinRelacion->getFecha();
$tipo = $detalleSinRelacion->getTipo();
//Esto lo hago para no repetir registros en la base de datos
if(!$this->gDetalle->existePorIdEmpresaPartidaFechaTipo($idEmpresaPartida, $fob, $cantidad, $fecha, $tipo)) {
...
...
...
Si se fijan trabajo con clases y siempre obtengo arrays de objetos los cuales recorro con bucles for. Bueno vamos contándoles que había detrás.
Contaba con una base de datos de apenas 10 tablas pero 4 tablas contaban con más de medio millón de registros los cuales tenía que consultar y sacar un reporte que me había solicitado mi cliente en excel.
La configuración de mis 10 tablas al comienzo eran InnoDB y mi motor de BD estaba configurado de forma básica.
El pedazo de código que ven arriba para sacar un reporte en excel demoraba en sacar los datos cerca de 40 a 45 minutos, claro como les recuerdo estaba consultando cerca de 2 millones de datos.
Pasos que he seguido para optimizar
1) Configurar mi motor de base de datos, aumentando cache, utilización de memoria entre otros, busquen en Google y te dan varios ejemplos de como configurar tu servidor MySQL.
2) Las 4 tablas (las que cuentan con una gran cantidad de registros) las he pasado de InnoDB a MyISAM, y las relaciones las trabajo por código.
3) Crear índices de los campos que consulto frecuentemente en las consultas (este paso redujo notablemente el tiempo).
Estimados he logrado que mi algoritmo de 45 minutos aproximadamente se reduzca a 6 minutos aproximadamente.
Espero les sirva estos pequeños consejos, su amigo Carlos Zacarías.
Estimado Carlos,
ResponderEliminarSolo tengo un comentario acerca del punto 2: volver de InnoDB a MyISAM y dejar la integridad en manos de tu código, lo veo mas bien como un retroceso. Puede ser un tema filosófico, pero personalmente no haría esa práctica.
Para tu caso lo primero que hubiese intentado hacer es verificar si están las tablas bien normalizadas, y darle una vuelta de tuerca a esto. Otra cosa que hubiese intentado es agregar algunas tablas auxiliares con datos precalculados que, en conjunto con triggers, vayan calculando datos y acumulando resultados en estas tablas. Después, las consultas las haría estas tablas auxiliares. Tuve que resolver de esta manera en una base de datos de más de 200 tablas con varios cientos de millones de registros (toda la bd pesaba ~250 Gb).
Esta solución que te propongo es particularmente útil si lo que debes presentar es un informe consolidado y no un listado. Un típico traspié que puedes encontrar en este escenario es con registros o cálculos que dependen de un rango de tiempo; para ello lo he resuelto intentando crear acumuladores diarios, así cuando hago la consulta en un rango sumo/resto/proceso sobre estos acumuladores.
Espero estos humildes consejos ayuden.
Muchas gracias amigo por el aporte, es bien tomado. Sólo como un ajuste a la descripción del blog, en el tema de normalización no había mucho que hacer, solo son dos tipos de archivos excel (cientos de archivos referidos a estos dos tipos y cada tipo con 10 campos como máximo) que debía ingresar a la base de datos y partir de ellos sacar listados ya filtrados por algún campo en específico.
EliminarLo que si me parece genial y no se me había ocurrido es de tener tablas auxiliares con datos precalculados, esto ayudaría a reducir notablemente el tiempo.
Muchas gracias Grover.