Optimización de rendimiento de la base de datos de SolerApp

📋Resumen

La base de datos de Soler casi no tenía índices secundarios: unos 7 GB de datos contra 0,08 MB de índices. Las consultas más frecuentes de las aplicaciones recorrían tablas completas en cada ejecución. Tres consultas concentraban el 95,8 % del tiempo de base de datos del periodo medido.

Cifras obtenidas de la estructura exportada el 2026-10-16.

En la madrugada del 2026-10-17 se crearon dos índices en producción, sin bloquear las tablas y con un respaldo previo. Las dos consultas principales dejaron de recorrer la tabla completa optimizando los recursos del servidor y mejorando los tiempos de respuesta.

Indicadores clave de la mejora

Detalle completo y evolución en cada revisión: sección Resultados medidos.

🧭Contexto

Soler opera una cadena de centros de bienestar y salud con cámaras de terapia para la piel. Su sistema se compone de una aplicación Windows (Visual Basic .NET, Windows Forms), una aplicación web (ASP.NET, VB.NET) y una base de datos MySQL llamada bigboss.

El objetivo planteado fue analizar la base de datos porque el sistema se percibe lento y los servidores son costosos, con la idea de evaluar una migración a PostgreSQL. Antes de decidir una migración se midió dónde está realmente el cuello de botella, para no pagar un cambio de motor que no resuelva el problema.

🧪Cómo se hizo el análisis

• Lectura de la estructura de la base de datos (tablas, columnas, índices, motores) desde MySQL Workbench, en solo lectura.
• Lectura de las estadísticas de consultas del servidor (performance_schema), que registran qué consultas consumen más tiempo.
• Revisión del código fuente de las aplicaciones Windows y Web para ubicar el origen de las consultas más pesadas.
• Verificación con EXPLAIN, que muestra cómo MySQL planea ejecutar una consulta sin ejecutarla.

💰Costos del proyecto

Condiciones comerciales

1. Forma de pago: la fase 1 ya está realizada. Para las fases 2 a 5, 50 % del valor de cada fase al aprobarla, como anticipo, y 50 % al publicarla en producción o entregarla.
2. Respaldo y restauración: el respaldo de la información y de la base de datos se realiza de forma automática a través de Acronis. Cada operación sobre la base de datos se hará en horario no laboral, después de las 9 p. m. hora Colombia, y Transversa-IT hará antes la copia de la base de datos.
   En caso de requerir restaurar la base de datos en producción, se informará el incidente con antelación, se detendrá la ejecución de la aplicación para evitar que se modifique información, y Transversa-IT hará la recuperación de la base de datos y volverá a habilitar la aplicación.
3. Los valores no incluyen: soporte sobre el servidor, reinstalación del aplicativo, recuperación de datos, respaldo de información del sistema, respaldo de información de la base de datos, requerimientos adicionales, manual de usuario, capacitaciones a usuario final, instalaciones sobre los servidores y dispositivos, ni soporte a usuario final.
4. Fuera del alcance definido: está por fuera cualquier análisis y ejecución de migraciones del aplicativo y la base de datos.
5. Requerimientos adicionales: los que estén fuera del alcance definido podrán generar horas adicionales, que se avisan y se aprueban antes de continuar.
6. Código fuente y documentación: los valores en pesos colombianos de este documento incluyen el código fuente de las aplicaciones y la documentación técnica generada durante la ejecución del proyecto, junto con los scripts de medición.
7. Soporte y garantía: garantía sobre las funcionalidades y modificaciones realizadas durante el periodo en que el proyecto esté en ejecución y un mes después de finalizado.
8. Desplazamiento, estadía y alimentación derivados de cualquier actividad por fuera de Medellín y el Área Metropolitana corren por cuenta del cliente, además de los servicios adicionales a los especificados en esta propuesta.

⏱️Consultas que más pesan

Ventana medida: de 2026-10-14 07:47 a 2026-10-16 12:32 (unas 52,7 horas). Se acumularon 507.244 segundos de trabajo de base de datos, es decir, en promedio unas 2,7 consultas ejecutándose todo el tiempo. De 2,40 millones de ejecuciones del top 100, 1,38 millones no usaron ningún índice.

Estas tres consultas suman el 95,8 % del tiempo medido. La consulta 1 examinó en promedio unas 483.000 filas por ejecución para devolver, en total, solo 237 filas durante toda la ventana: casi siempre la respuesta era "no hay nada pendiente", pero se leía toda la tabla para saberlo.

🔬Causas verificadas en el código y con EXPLAIN

• bbf_facturas solo tenía la llave primaria (ID_FACTURA, ID_SEDE). Filtrar por sede no puede usar ese índice, porque la sede es la segunda columna, y MySQL leía las ~378 mil filas.
• La condición DATE_FORMAT(IMPRESO, …) = '1900/01/01' aplica una función a la columna y evita que un índice sobre IMPRESO se aproveche por completo.
• bbf_tasks solo tenía la llave primaria (intId), sin índice por sede ni por estado.

📏Línea base con EXPLAIN (2026-10-16 12:47, producción, solo lectura)

🔎Hallazgos iniciales

📌Hallazgos principales

1. Falta casi total de índices secundarios. Los filtros y cruces por persona, sede, factura o fecha recorren la tabla completa. Es la causa más probable de la lentitud y no depende del motor de base de datos.
2. Integridad referencial casi inexistente. Solo 2 llaves foráneas declaradas. Las relaciones entre tablas viven en el código de las aplicaciones.
3. 40 tablas sin llave primaria y 5 con llave primaria de tipo decimal (bbf_acceso, bbf_personas, bbf_productos, er_facturacion_electronica_log, maquinaslog).
4. Tipos inconsistentes para la misma clave. Por ejemplo, ID_PERSONA aparece como texto, decimal y entero según la tabla.
5. Dos tablas de registro concentran cerca del 84 % del volumen: maquinaslog (≈ 15,4 millones de filas) y er_facturacion_electronica_log (≈ 7,0 millones de filas, ≈ 5,1 GB).
6. 18 tablas en MyISAM, un motor sin transacciones ni integridad.
7. Codificación de caracteres mezclada (latin1, utf8 y utf8mb4), un riesgo para tildes y eñes en una futura migración.
8. Documentación casi nula en el esquema: 25 de 4.441 columnas tienen comentario.

🏁Fase 1. Ejecución inicial

En esta fase Transversa-IT revisó la base de datos de Soler, creó dos índices para que las consultas más pesadas dejaran de recorrer tablas completas y canceló cinco consultas que llevaban horas abiertas. No se modificó ningún dato. Es un resumen de lo ejecutado; el detalle de cada paso, con sus scripts, está en la sección siguiente.

Qué se hizo

Los dos índices creados

Ninguno requiere cambios en el código de las aplicaciones.

🔒Salvaguardas aplicadas en producción

• Ventana de baja actividad: la ejecución se hizo de madrugada, entre las 02:09 y las 02:22.
• Respaldo previo: Transversa-IT hizo una copia completa de la base de datos bigboss del servidor 200.118.190.188 antes de cualquier cambio, y se confirmó que terminó.
• Creación en línea: ALGORITHM=INPLACE, LOCK=NONE. MySQL construye el índice mientras las aplicaciones siguen leyendo y escribiendo la tabla.
• Tiempo máximo de espera de 5 segundos para el bloqueo breve de metadatos. El servidor tenía configurado un tiempo de espera de un año; sin esta guarda, una operación bloqueada habría dejado en cola a todas las consultas posteriores.
• Un índice a la vez, empezando por la tabla pequeña, con verificación después de cada uno.

🛠️Ejecución paso a paso

Fecha y hora en formato aaaa-mm-dd hh:mm:ss, hora de Colombia, tomadas del registro de acciones de MySQL Workbench y del registro de estadísticas del servidor. Cada script está oculto: presiona "Ver script" para abrirlo. Se muestra tal como se ejecutó.

SELECT
  (SELECT COUNT(*) FROM information_schema.innodb_trx)                           AS trx_abiertas,
  (SELECT COALESCE(MAX(TIMESTAMPDIFF(SECOND,trx_started,NOW())),0)
     FROM information_schema.innodb_trx)                                         AS trx_max_seg,
  (SELECT COUNT(*) FROM information_schema.processlist WHERE command<>'Sleep')   AS activas_incl_esta,
  (SELECT COALESCE(MAX(time),0) FROM information_schema.processlist
     WHERE command<>'Sleep')                                                     AS activa_max_seg,
  (SELECT COUNT(*) FROM information_schema.processlist
     WHERE command LIKE 'Binlog Dump%')                                          AS replicas_conectadas,
  @@log_bin AS log_bin, @@server_id AS server_id,
  @@lock_wait_timeout AS lock_wait, @@innodb_online_alter_log_max_size AS alter_log_max;

SELECT id, user, db, command, time, state, LEFT(info,100) AS consulta
FROM information_schema.processlist
WHERE command<>'Sleep' ORDER BY time DESC;

SET SESSION lock_wait_timeout = 5;
ALTER TABLE bbf_tasks
  ADD INDEX idx_tasks_sede_done (intId_Sede, dteDone),
  ALGORITHM=INPLACE, LOCK=NONE;
SHOW INDEX FROM bbf_tasks WHERE Key_name = 'idx_tasks_sede_done';

Entre la creación del índice 1 (P3) y su verificación (P4) se cancelaron tres consultas atascadas, el 2026-10-17 a las 02:14:39. Ese paso, con su script, está documentado en Consultas atascadas.

EXPLAIN SELECT T.intId FROM bbf_tasks AS T
 WHERE T.dteDone IS NULL AND T.intId_Sede = 1;

SET SESSION lock_wait_timeout = 5;
ALTER TABLE bbf_facturas
  ADD INDEX idx_facturas_sede_business_impreso (ID_SEDE, intId_Business, IMPRESO),
  ALGORITHM=INPLACE, LOCK=NONE;
SHOW INDEX FROM bbf_facturas WHERE Key_name = 'idx_facturas_sede_business_impreso';

EXPLAIN SELECT F.ID_FACTURA FROM bbf_facturas AS F
 WHERE (F.IMPRESO IS NULL OR DATE_FORMAT(F.IMPRESO,'%Y/%m/%d')='1900/01/01')
   AND F.FECHA >= '2018/11/19' AND F.intId_Business='1' AND F.ID_SEDE='1';

↩️Cómo revertir

Si fuera necesario, cada índice se elimina en segundos y la base queda como estaba, sin pérdida de datos.

SET SESSION lock_wait_timeout = 5;
ALTER TABLE bbf_tasks    DROP INDEX idx_tasks_sede_done,                   ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE bbf_facturas DROP INDEX idx_facturas_sede_business_impreso,    ALGORITHM=INPLACE, LOCK=NONE;

🛑Consultas atascadas

Una consulta atascada es una búsqueda que lleva horas sin terminar. Mientras sigue abierta consume procesador, retiene bloqueos sobre las tablas que lee y puede impedir cambios como la creación de un índice. Esta sección registra cada consulta que se detectó, desde dónde se inicia, cuánto duró y cómo corregirla. Todas las horas son del servidor, hora de Colombia, formato de 24 horas.

Registro de cancelaciones

El inicio de 1306 y 1179 se calculó con la lectura de las 03:02:38, justo después de confirmar que seguían en la misma consulta (48.462 y 65.511 caracteres, estado statistics). Esa lectura corrige en 90 s la hora de inicio estimada antes. Tras el KILL, una nueva lectura de la lista de procesos no devolvió ninguna fila para los hilos 1306 y 1179, ni otras consultas de más de 10 minutos: las conexiones ya no aparecen.

Qué tienen en común

• Es la misma consulta de búsqueda de personas (procedimiento find_Person): SELECT * FROM (SELECT P.ID_PERSONA, …. Es de solo lectura y no modifica datos.
• Su texto es enorme. Mide 48.462 caracteres (hilo 1306) y 65.511 (hilo 1179). Una búsqueda normal es mucho más corta.
• Arma una subconsulta por cada palabra escrita. En 1306 se ve hasta CRITERIO18, es decir, al menos 18 subconsultas unidas entre sí. Por el tamaño del texto, 1179 tendría cerca de 24. Cada una compara el criterio con unas 30 columnas usando LIKE '%…%', que no puede usar índices. El criterio visible en 1306 es '%co%', de solo dos letras, que coincide con casi cualquier nombre.
• MySQL las muestra en estado statistics durante horas, no en executing. Es consistente con que el servidor siga eligiendo el orden de las uniones. La configuración actual lo permite: optimizer_search_depth = 62 (el máximo, que prueba combinaciones casi sin límite) y max_execution_time = 0 (sin límite de duración para ninguna consulta).
• En uso normal la búsqueda es rápida. La estadística del servidor (desde 2026-10-14 07:48) registra 7.632 ejecuciones y 114.023,66 s acumulados. Las tres canceladas suman al menos 111.159 s, así que las otras 7.629 ejecuciones promediaron menos de 0,4 s.

Desde dónde se inicia

Consultas, una por una

📊Resultados medidos

Comparación de filas que MySQL estima leer por ejecución, con la misma consulta y los mismos parámetros, antes y después.

📈Evolución medida del servidor

Tiempo real por ejecución de las tres consultas más pesadas. Cada vez que revisemos el servidor se agrega una columna con el mismo método: la misma consulta SQL y dos lecturas separadas unos 15 minutos, de las que se calcula el promedio de ese periodo.

⚙️Impacto operativo

• Ninguna tabla se bloqueó para lectura o escritura durante la creación de los índices.
• No se modificó ningún dato ni ninguna estructura de las aplicaciones.
• Las consultas de más de 10 horas se cancelaron sin cerrar las conexiones de las aplicaciones.
• Los índices añaden un costo pequeño a cada inserción o actualización de esas dos tablas. Para este volumen se considera despreciable frente a la lectura completa que evitan.

⏳Pendiente de validar en 24 a 48 horas

Para cerrar la mejora con evidencia sobre el uso real, se repetirá la exportación de las consultas más costosas con el mismo método de la línea base (2026-10-16 12:32) y se comparará.