MySQL bajo carga real: índices, planes y separar lecturas

MySQL tiene mala prensa en cierto sector del mundo backend, y buena parte de esa mala prensa viene de sistemas donde el problema no era MySQL. Bien modelada e indexada, aguanta mucho más de lo que se suele asumir antes de necesitar nada exótico.

Lo que sigue es lo que de verdad ha marcado la diferencia en sistemas con carga real.

El plan de ejecución no es opcional

La diferencia entre alguien que optimiza consultas y alguien que prueba cosas es que el primero lee el plan de ejecución.

EXPLAIN te dice qué va a hacer el motor: qué índice piensa usar, cuántas filas estima examinar y en qué orden va a resolver los joins. Sin eso, optimizar es cambiar cosas a ver si baja el tiempo, y cuando baja no sabes por qué, así que tampoco sabes si volverá a subir.

Las señales que más veces han apuntado al problema real:

• Recorrido completo de tabla donde esperabas un índice. O no existe el índice, o la consulta está escrita de forma que impide usarlo.
• Estimación de filas desproporcionada respecto a lo que la consulta devuelve. El motor está examinando mucho para filtrar después.
• Uso de tabla temporal o de ordenación en disco. Suele venir de un ORDER BY o un GROUP BY que no puede apoyarse en ningún índice.

El orden de las columnas de un índice lo es casi todo

Este es probablemente el punto que más veces he visto malinterpretado. Un índice sobre (a, b, c) no es lo mismo que tres índices sobre a, b y c, y tampoco sirve igual para todas las consultas.

Un índice compuesto se puede usar de izquierda a derecha, y solo así. Sirve para filtrar por a, por a y b, o por los tres. No sirve para filtrar solo por b.

De ahí una consecuencia práctica: el orden debe seguir cómo consultas, no cómo piensas el modelo. Es muy frecuente ver índices ordenados según la lógica conceptual de la entidad, que resultan inservibles para las consultas que realmente ejecuta la aplicación.

Y el reverso: antes de añadir un índice nuevo, mirar si ya hay uno que empiece por las mismas columnas. Los índices no son gratis —encarecen cada escritura y ocupan memoria— y las tablas con diez índices casi siempre tienen tres que sobran.

Escribir consultas que puedan usar el índice

Un índice perfecto no sirve de nada si la consulta impide usarlo. Los casos más habituales:

• Aplicar una función a la columna filtrada. En cuanto envuelves la columna en una función, el motor ya no puede buscar por el índice, porque el índice guarda el valor original, no el transformado. Casi siempre se puede reescribir como un rango sobre el valor tal cual está almacenado.
• Comodín por delante en una búsqueda de texto. Buscar algo que empiece por un patrón puede usar el índice; buscar algo que lo contenga en cualquier posición, no.
• Comparar tipos distintos. Si una columna es numérica y le pasas una cadena, o si dos columnas de un join tienen colaciones distintas, puede haber una conversión implícita que invalide el índice. Este es especialmente traicionero porque la consulta funciona: solo va lenta.

Separar lecturas de escrituras

Cuando el volumen de consulta supera con mucho al de escritura —que es exactamente lo que ocurre en un motor de disponibilidad, donde por cada reserva hay miles de búsquedas—, la replicación master-slave permite repartir esa carga: las escrituras van al primario y las lecturas a las réplicas.

Ahora bien, esto introduce una propiedad nueva en el sistema: el retraso de replicación. Una réplica va siempre un poco por detrás. Normalmente milisegundos, pero bajo carga puede crecer.

El fallo clásico es escribir un dato y leerlo inmediatamente después desde una réplica que todavía no lo tiene. El usuario guarda algo, se le recarga la pantalla y ve el valor anterior.

La regla que evita casi todos estos casos: lo que acabas de escribir, léelo del primario. Todo lo demás puede ir a réplica. No es elegante, pero es explícito y funciona.

Y antes de todo esto

La optimización con más retorno casi nunca está en la base de datos: está en no llegar a ella. La consulta más rápida es la que no se ejecuta.

Antes de invertir tiempo afinando índices, merece la pena preguntarse cuántas de esas consultas piden algo que ya se pidió hace un segundo, o algo que se podía haber descartado antes por reglas de negocio. En sistemas con carga real, ahí suele haber más margen que en cualquier índice.