Cómo optimizar consultas SQL de forma práctica y efectiva

Cuando una aplicación empieza a ir lenta, muchos miran al servidor, a la red o incluso a la máquina del usuario… pero en un montón de casos el verdadero culpable son consultas SQL mal diseñadas. Un simple cambio en una cláusula WHERE o en un índice puede marcar la diferencia entre una respuesta en milisegundos o en varios segundos.

La parte complicada es que detectar por qué una consulta es lenta puede llevar horas o días: revisar planes de ejecución, probar variaciones, analizar bloqueos, estadísticas, etc. La buena noticia es que hay patrones de diseño muy típicos que casi siempre dan problemas de rendimiento y que, si los reconoces rápido, te ahorran una barbaridad de tiempo de diagnóstico.

Patrones de SQL que suelen dar muy mal rendimiento

Existen ciertos patrones de código SQL que son auténticas bombas de rendimiento y que conviene tener muy vigilados: OR complejos, búsquedas con comodines en cadenas, escrituras masivas, consultas con demasiadas tablas o abusar de hints, entre otros.

El objetivo práctico es que, cuando te encuentres con una aplicación que va a pedales, puedas escanear las consultas y localizar enseguida esos patrones peligrosos para centrar tus pruebas justo donde más probablemente está el problema.

Siempre deberías validar con medidas reales (tiempos de ejecución, lecturas lógicas, consumos de CPU, etc.), pero saber por dónde empezar la investigación acorta muchísimo el proceso.

Problemas con OR en JOIN y WHERE sobre varias columnas

Los operadores lógicos parecen inocentes, pero el uso de OR sobre varias columnas o varias tablas puede destrozar la eficiencia, especialmente en sistemas como SQL Server, aunque la idea aplica también a otros motores.

Mientras que las condiciones unidas con AND son excluyentes y permiten ir reduciendo el conjunto de datos paso a paso, OR es inclusivo y obliga al motor a evaluar cada rama por separado. En términos de plan de ejecución, eso se traduce en más pasadas por las tablas y, muchas veces, en un número de lecturas desorbitado.

El caso más grave se da cuando el OR combina varias columnas o columnas de varias tablas. El optimizador se ve obligado a seguir cada camino del OR y luego unir los resultados. En tablas con cientos de miles de filas, esto puede derivar en millones de lecturas lógicas, incluso aunque las tablas no sean tan grandes.

Una táctica muy habitual para mejorar estas situaciones consiste en eliminar el OR reescribiendo la consulta en varias sentencias, cada una con su propio SELECT y unidas posteriormente con UNION o UNION ALL. Cada SELECT puede entonces ser optimizado individualmente por el motor.

En la práctica, esto implica que consultas simples con OR pueden transformarse en varias consultas un poco más largas, pero a cambio el plan suele ser más estable, con menos lecturas y tiempos de ejecución menores. El coste es que, en ocasiones, estás leyendo las mismas tablas varias veces; aun así, la mejora suele compensar, sobre todo cuando el OR impide que se aprovechen índices adecuados.

La clave es que no hay que fiarse de las condiciones OR en columnas distintas o en múltiples tablas. Si estás auditando consultas lentas y ves OR repartidos por el JOIN o el WHERE, considéralo un candidato principal a culpable y pruébalo separado en varias consultas.

Búsquedas de cadenas con comodines y texto completo

Las búsquedas de texto son otro clásico de los problemas de rendimiento. Buscar subcadenas arbitrarias dentro de columnas de texto es intrínsecamente caro: el motor no tiene forma de “saltar” al punto correcto y acaba escaneando fila a fila y carácter a carácter.

Para columnas de texto que se consultan mucho, conviene plantearse varias cosas muy básicas: ¿hay índices sobre esas columnas?, ¿el patrón de búsqueda permite usar esos índices?, ¿podemos usar índices FULLTEXT o alguna solución alternativa como hashes o n-grams?

En motores como SQL Server o MySQL, colocar el comodín % al principio del patrón rompe el uso de índices B-tree convencionales. Es decir, un WHERE Apellido LIKE ‘%For%’ obliga a un escaneo completo, aunque tengas un índice sobre Apellido. Lo mismo pasa con patrones que terminan en % pero en búsqueda descendente: se pierde la ventaja del orden del índice.

En tablas pequeñas puede no ser un drama, pero en tablas con millones de filas un escaneo secuencial para cada búsqueda es un cuello de botella enorme. De ahí que haya que pensarse muy bien el diseño de estas consultas.

Antes de meterte en optimizaciones complejas, es muy útil replantearse el propio requisito funcional: ¿realmente el usuario necesita buscar en cualquier parte de una cadena? A veces basta con búsquedas por prefijo («For%» en lugar de «%For%»), o se puede obligar a usar otros filtros (por fecha, estado, categoría…) que reduzcan drásticamente el número de filas que hay que revisar.

Además de ajustar el diseño funcional, los índices de texto completo (FULLTEXT) ofrecen una alternativa muy potente cuando las búsquedas de texto son frecuentes o complejas. Permiten localizar palabras, frases y realizar búsquedas lingüísticas más avanzadas usando operadores específicos y, en general, trabajan sobre estructuras de datos optimizadas para texto.

Eso sí, la indexación de texto completo es una característica adicional: hay que instalarla, configurarla y mantenerla. En aplicaciones muy centradas en contenido textual suele ser una inversión muy rentable, pero no deja de añadir complejidad y coste de mantenimiento.

Para cadenas relativamente cortas, como nombres o códigos, también se puede recurrir a técnicas de n-grams: se descompone cada valor en pequeños fragmentos de longitud fija (por ejemplo, de 3 caracteres) que se guardan en una tabla aparte, junto con una referencia a la fila original.

De esta forma, en vez de escanear una tabla enorme de NVARCHAR, se hace una búsqueda exacta en la tabla de n-grams, que sí puede estar bien indexada. Luego se recuperan las filas originales a través del identificador. El precio de esta técnica es doble: por un lado, la tabla de n-grams puede crecer muy rápido, y por otro, hay que mantenerla en cada inserción, actualización o borrado, lo que la hace razonable solo para textos cortos.

En resumen para este bloque: las búsquedas con comodines internos son caras por diseño, y lo mejor que puedes hacer es adaptar el diseño de la aplicación (quitar comodines iniciales, añadir filtros) o usar estructuras especializadas como FULLTEXT o n-grams cuando realmente no haya alternativa.

Índices: tipos, diseño y mantenimiento

Si hay una palanca de rendimiento que se repite una y otra vez es la indexación. La forma más directa de acelerar una consulta es crear índices adecuados sobre las columnas que aparecen en la cláusula WHERE y en las condiciones de JOIN. Pero, como todo en bases de datos, tiene trampa: demasiados índices también son un problema.

Un índice es básicamente una estructura (normalmente un B-tree) que permite localizar rápidamente las filas que cumplen una condición sin tener que leer toda la tabla. MySQL, por ejemplo, almacena la mayoría de sus índices en árboles B: PRIMARY KEY, UNIQUE, INDEX y también FULLTEXT (aunque por dentro tengan particularidades propias).

Además de los índices basados en B-tree, MySQL utiliza R-tree para datos espaciales y hash para tablas en memoria. Cada estructura tiene sus ventajas según el tipo de dato y el patrón de acceso: los B-tree son ideales para rangos y ordenaciones; los R-tree, para consultas espaciales; y los índices hash, para búsquedas de igualdad muy rápidas en memoria.

En cuanto a tipos de índices lógicos, los más habituales son: claves primarias, claves foráneas, índices únicos, índices normales, índices multicolumna, índices FULLTEXT e índices funcionales. Cada uno cubre una necesidad distinta y conviene conocer sus implicaciones tanto en lectura como en escritura.

Por ejemplo, un índice compuesto sobre (apellido_contacto, nombre_contacto) será útil cuando busques por apellido solo o por apellido y nombre a la vez, pero no ayudará si filtras únicamente por nombre. Este tipo de detalles marcan una gran diferencia a la hora de diseñar los índices de una tabla.

También existe la opción de indexar solo un prefijo de una columna de texto, para reducir el tamaño del índice. Si nombre_cliente es VARCHAR(50), quizá baste con indexar los primeros 20 o 25 caracteres, siempre que con esos caracteres ya se distingan de forma razonable la mayoría de valores. Aquí se trata de encontrar un equilibrio entre selectividad y tamaño de índice.

En MySQL a partir de la versión 8.0.13 se pueden crear índices funcionales sobre el resultado de una expresión. Esto es especialmente útil para consultas que usan funciones como YEAR(fecha_pago) en la cláusula WHERE: en lugar de romper el uso del índice de la columna fecha_pago, creas un índice directo sobre YEAR(fecha_pago) y el optimizador puede aprovecharlo.

Los índices se pueden crear con CREATE INDEX, ALTER TABLE o directamente en la definición de la tabla con CREATE TABLE. Cada opción es útil en distintos momentos: creación inicial del esquema, refactorizaciones posteriores o ajustes puntuales de rendimiento.

También es fundamental poder inspeccionar qué índices existen y cómo se están usando. En MySQL puedes apoyarte en SHOW INDEX o DESCRIBE para ver índices y tipos de clave, y en comandos como EXPLAIN para comprobar si una consulta realmente está utilizando esos índices o sigue haciendo un escaneo completo (type = ALL, rows muy alto, etc.).

Por el lado del mantenimiento, cuentas con herramientas como OPTIMIZE TABLE y ANALYZE TABLE. OPTIMIZE ayuda a desfragmentar la tabla y a reorganizar índices, mientras que ANALYZE recalcula la distribución de claves, que es la base de muchas decisiones del optimizador (orden de joins, elección de índices, etc.). Ejecutarlos tras cargas masivas o grandes cambios ayuda a mantener los planes de ejecución razonables.

Eso sí, hay errores habituales que conviene evitar: indexar en exceso una tabla, dejarla casi sin índices o carecer de índice agrupado/clave primaria. Demasiados índices penalizan las escrituras (cada INSERT, UPDATE o DELETE debe actualizar todos ellos) y ocupan mucho espacio en disco y en backups. Muy pocos índices, en cambio, fuerzan lecturas de tabla completas continuamente.

En SQL Server, además, el propio motor te sugiere índices faltantes basándose en los planes de ejecución, bien a través de Management Studio, del XML del plan o de vistas dinámicas. Estas recomendaciones son útiles como punto de partida, pero hay que revisarlas con ojo crítico: muchas veces proponen índices demasiado grandes, con muchas columnas INCLUDE, o duplican índices ya similares.

Antes de aceptar una sugerencia de índice faltante, conviene preguntarse: ¿existe ya un índice parecido que podría ampliarse?, ¿necesito todas las columnas INCLUDE?, ¿cuál es el impacto de mejora estimado?, ¿esa consulta se ejecuta lo bastante a menudo como para justificarlo?

Por último, si tu aplicación tiene tablas sin índice agrupado ni clave primaria, eso debería saltar todas las alarmas. Los heaps puros suelen rendir peor para muchas cargas de trabajo y dificultan la creación de índices no agrupados eficientes. Definir una buena clave primaria y un índice clustered suele ser una prioridad alta antes de entrar en ajustes más finos.

Escrituras masivas, bloqueos y crecimiento del log

No todo es SELECT. Las operaciones de escritura a gran escala también pueden generar problemas serios de rendimiento y contención. Grandes actualizaciones, inserciones o borrados pueden bloquear tablas enteras durante mucho tiempo, disparar el tamaño del log de transacciones y dejar a otros usuarios esperando.

Cada vez que modificas datos, el motor coloca bloqueos para garantizar consistencia y evitar interferencias. Eso es bueno para la integridad, pero cuando una operación tarda demasiado, se transforma en un cuello de botella: otras consultas quedan bloqueadas, aparecen timeouts y las quejas sobre «la base de datos va fatal» no tardan en llegar.

¿Qué se considera una «operación grande»? Depende muchísimo del esquema: número de índices, triggers, claves externas, etc.. En una tabla sencilla quizá 100.000 filas sean asumibles en una sola transacción; en otra tabla con muchas restricciones, 2.000 filas ya pueden ser un problema. La única manera fiable de saberlo es probarlo en condiciones reales o muy parecidas.

Además de los bloqueos, las escrituras masivas provocan un crecimiento rápido del registro de transacciones. Si no vigilas su tamaño, puedes encontrarte con el log o el propio disco llenos. Esto es especialmente crítico durante tareas de mantenimiento, cargas ETL o migraciones, donde se concentran muchas escrituras en poco tiempo.

Un enfoque bastante sensato es partir las operaciones grandes en lotes más pequeños. En lugar de actualizar un millón de filas de golpe, lo haces por paquetes (por ejemplo, de 10.000) con confirmaciones intermedias, reduciendo así la duración de los bloqueos y el tamaño de cada transacción en el log. Para procesos en horario de baja carga (ventanas de mantenimiento), puedes permitirte lotes más grandes; en producción, quizá tengas que ser bastante conservador.

También conviene revisar qué operaciones generan escrituras masivas: añadir y rellenar nuevas columnas, cambiar tipos de datos, importaciones, archivos históricos, limpiezas periódicas. Entender su impacto te ayuda a planificar ventanas, ajustar batch sizes y evitar sorpresas desagradables en medio de un despliegue o un mantenimiento crítico.

Consultas con muchas tablas y explosión de planes

Otro patrón que da bastantes dolores de cabeza es el uso de consultas gigantescas que unen una cantidad enorme de tablas. Los optimizadores de SQL (SQL Server, Oracle, DB2, MySQL, etc.) están pensados para encontrar un «buen» plan rápidamente, pero el espacio de búsqueda crece de forma brutal con cada tabla adicional.

En una consulta con muchas tablas, el optimizador tiene que decidir en qué orden unirlas, qué tipo de join usar en cada caso, cuándo aplicar filtros y agregaciones, etc. El número de posibles planes crece de manera factorial o incluso peor dependiendo de la forma del árbol de joins (más lineal o más ramificada).

Por ejemplo, con unas 12 tablas ya puedes acabar con decenas de miles de millones de posibles planes teóricos si la consulta es muy tupida. Obviamente el optimizador no explora todos, pero tiene que podar muchas opciones rápidamente y a veces se queda con un candidato que no es el mejor, simplemente porque no puede dedicar más tiempo a buscar.

No significa que todas las consultas complejas sean malas, pero sí que cada tabla extra que añades aumenta el riesgo de que el plan elegido no sea óptimo. Además, mantener y depurar SQLs de 20, 30 o 40 tablas es un suplicio para cualquier equipo.

Entre las estrategias para mejorar este escenario están: mover tablas de metadatos o de búsqueda a consultas separadas que vuelcan sus resultados en tablas temporales, eliminar joins innecesarios, dividir una consulta en varias más pequeñas y, en casos de uso muy frecuentes, crear vistas indizadas que precalculen parte del trabajo.

Cuando divides una consulta grande en varias más pequeñas, debes asegurarte de que no haya cambios de datos relevantes entre una y otra que invaliden el resultado. Eso puede requerir uso de transacciones, niveles de aislamiento adecuados o bloqueos explícitos, dependiendo del motor y de la criticidad de los datos.

En muchos casos, sin embargo, es posible reorganizar la obtención de datos en unidades lógicas más pequeñas y comprensibles: primero obtienes un subconjunto clave (por ejemplo, los IDs que cumplen determinadas condiciones) y luego, en una segunda consulta, recuperas los detalles. De paso, puedes limpiar columnas innecesarias y simplificar la lógica.

Buenas prácticas generales de escritura de consultas

Más allá de patrones muy concretos, hay una serie de recomendaciones generales que suelen mejorar el rendimiento de forma bastante consistente. Por ejemplo, evitar SELECT * y seleccionar solo las columnas que realmente necesitas. Cada columna adicional supone más datos moviéndose por la red, más memoria, más carga de E/S y, a veces, impide usar ciertos índices de forma óptima.

Otro punto importante es no abusar de DISTINCT y UNION cuando no son necesarios. Ambos operadores implican operaciones de ordenación o eliminación de duplicados, que son de las partes más costosas de una consulta. En muchas situaciones, UNION ALL (que no elimina duplicados) es suficiente y mucho más rápido.

En lo relativo a JOIN, es preferible usar INNER JOIN cuando no necesitas realmente filas «huérfanas» de una de las tablas. Las uniones externas (LEFT/RIGHT OUTER JOIN) restringen el margen de maniobra del optimizador y a menudo conducen a planes menos eficientes. Además, los predicados de la tabla externa conviene colocarlos en la cláusula ON, no en la WHERE, para que el optimizador los pueda aplicar correctamente.

También es recomendable duplicar condiciones constantes en las columnas unidas de ambas tablas cuando sea posible (por ejemplo, A.id = B.id y A.id IN (10,12) y B.id IN (10,12)). Esto da pistas adicionales al optimizador sobre el rango de valores relevantes en cada tabla y puede mejorar la selección de índices y el orden de ejecución.

La cláusula ORDER BY debe usarse solo cuando realmente necesitas ordenar el resultado. Sin ORDER BY el orden del conjunto devuelto no está garantizado, aunque a veces parezca que sí por casualidad. Pero cada ORDER BY implica una ordenación, y en conjuntos de resultados grandes puede convertirse en uno de los pasos más caros de la consulta.

En Oracle, además, existe la posibilidad de usar expresiones de tabla comunes (CTE) y sintaxis específicas de expresiones regulares para ayudar al optimizador a crear tablas temporales intermedias más eficientes. Reescribir ciertas consultas usando CTE bien pensadas puede permitir que el optimizador «empuje» predicados hacia abajo en las vistas, filtrando datos antes y reduciendo el tamaño de las uniones posteriores.

EXPLAIN, estadísticas y sugerencias de consulta (hints)

Una herramienta que no puede faltar en tu caja es EXPLAIN (y variantes como EXPLAIN ANALYZE). EXPLAIN te muestra cómo el motor planea ejecutar una consulta: qué índices usa, el tipo de acceso (ALL, index, ref, range…), cuántas filas estima leer, el orden de join, etc.

Con esa información puedes ver, por ejemplo, si tu flamante índice sobre pais se está usando o no, si la consulta sigue haciendo un escaneo completo de la tabla (type = ALL), si rows es un número ridículamente alto o si un FULLTEXT está entrando en juego en lugar de una búsqueda por LIKE.

En MySQL, después de crear un índice y volver a lanzar EXPLAIN, deberías ver un cambio en la columna type hacia valores más selectivos (ref, range, etc.) y una reducción notable en la estimación de rows. Ese tipo de comparaciones antes/después son oro puro para comprobar si tu optimización está funcionando.

Otra pieza clave son las estadísticas de distribución de valores (ANALYZE TABLE, actualización automática de estadísticas, etc.). El optimizador decide qué plan elegir en gran medida basándose en esas estadísticas; si están desactualizadas, puedes terminar con planes muy malos. Tras grandes cargas o cambios masivos, conviene actualizar estadísticas explícitamente.

En cuanto a las sugerencias de consulta, o hints, la postura prudente es usarlas como último recurso y con mucha moderación. Un hint es una instrucción explícita al optimizador: fuerza un tipo de join (MERGE, HASH, LOOP), un valor de parámetro para optimizar (OPTIMIZE FOR), un nivel de aislamiento (NOLOCK), que no reutilice el plan (RECOMPILE), etc.

El problema es que un hint que hoy arregla un caso límite puede convertirse mañana en un obstáculo cuando cambien los datos, el esquema o los patrones de uso. Además, pueden ocultar problemas más profundos: falta de índices, volumen de datos innecesario, lógica de negocio mal planteada, etc.

Algunas advertencias típicas: NOLOCK puede devolver datos inconsistentes (lecturas sucias), así que no debería usarse donde la calidad de dato importe; RECOMPILE en una consulta muy frecuente puede generar una sobrecarga brutal; forzar HASH/MERGE/LOOP limita las opciones del optimizador y puede llevar a planes horrorosos a medio plazo; y OPTIMIZE FOR puede quedar obsoleto en cuanto cambian los patrones de uso de la aplicación.

La manera sensata de trabajar es agotar primero las alternativas «limpias» (índices adecuados, reescritura de SQL, ajustes de parámetros, actualización de estadísticas) y solo si no hay otra salida, aplicar un hint muy concreto, bien documentado y revisado periódicamente.

En conjunto, optimizar consultas SQL es una mezcla de entender bien cómo funciona el motor, reconocer patrones de riesgo (OR complicados, comodines mal colocados, exceso de tablas, índices mal diseñados) y apoyarse en herramientas como EXPLAIN y las vistas de sistema para tomar decisiones informadas. Si combinas un diseño de índices razonable, consultas limpias y cierto criterio para evitar los antipatrones típicos, tus bases de datos responderán con mucha más agilidad sin necesidad de hardware monstruoso ni parches milagrosos.