Cinco lecciones para paginar tablas de pedidos gigantes en una base de datos distribuida

La paginación es uno de los patrones de consulta más comunes en aplicaciones en línea. Para tablas pequeñas, rara vez se convierte en un cuello de botella de rendimiento. Pero en tablas de pedidos grandes en una base de datos distribuida, las consultas de paginación enfrentan una serie de desafíos: selección de índices inestable, costosas búsquedas en tablas y escaneos de particiones desperdiciados que crean consultas lentas de cola larga.

Este análisis se basa en casos de producción reales para compartir cinco conocimientos de optimización para consultas paginadas en tablas de pedidos grandes en PolarDB-X, la base de datos SQL distribuida de Alibaba Cloud. Las estrategias se construyen alrededor de un esquema de particionamiento de dos niveles: particionamiento KEY de primer nivel por ID de usuario, y particionamiento RANGE de segundo nivel por un campo de tiempo, una arquitectura común en plataformas financieras, de comercio electrónico y SaaS.

Las optimizaciones cubren toda la cadena, desde poda de particiones, utilización de índices y reducción de búsquedas en tablas hasta estabilidad de rendimiento. Antes de profundizar, una nota: los esquemas de tabla y las sentencias SQL aquí han sido anonimizados y no representan datos comerciales reales, aunque los números de rendimiento provienen de escenarios de usuarios reales.

La anatomía de una consulta de paginación lenta

Una tabla de pedidos típica en PolarDB-X es amplia, 60+ columnas, con índices covering diseñados para reducir búsquedas en tablas. El esquema de particionamiento asegura que todos los datos de un usuario determinado caigan en la misma partición de primer nivel (por ID de usuario), luego subparticiones por fecha (por una columna de timestamp pt).

Considere una consulta que obtiene pedidos modificados recientemente para un inquilino grande:

SELECT * FROM t_order
WHERE uid = 12345678
  AND pt >= '2024-08-01 00:00:00.000'
  AND update_ts > '2024-11-13 14:20:00.000'
  AND update_ts < '2024-11-13 14:25:00.000'
  AND origin != 33
ORDER BY id DESC
LIMIT 0, 100;

La condición pt >= '2024-08-01' obliga a la consulta a escanear cuatro subparticiones: p202408, p202409, p202410 y p202411. Pero el filtro update_ts restringe los resultados a pedidos modificados en los últimos minutos, que son casi siempre pedidos recientes, lo que significa que las filas realmente calificadas caen todas en la partición p202411. Las otras tres particiones se escanean sin motivo. En la partición de un inquilino grande, cada escaneo desperdiciado lee esencialmente todos los datos en esa partición, convirtiéndose en una consulta lenta de cola larga que determina la latencia general.

Perspectiva 1: poda de particiones mediante predicados de filtro

Cuando una columna de filtro se correlaciona con la clave de partición, aquí, los pedidos modificados recientemente tienden a tener valores de pt recientes, la base de datos puede explotar esa correlación para saltar subparticiones innecesarias. El optimizador de PolarDB-X puede podar particiones dinámicamente al reconocer que el rango efectivo de pt implicado por el filtro update_ts es mucho más estrecho que el rango literal pt >= '2024-08-01'. En el escenario de usuario real, esta optimización redujo una consulta de 5 segundos a 0.01 segundos.

Perspectiva 2: poda de particiones mediante columna de ordenamiento

Un escenario más común es cuando el SQL del usuario contiene solo uid como filtro, sin predicados de tiempo. Sin poda de particiones, la consulta debe escanear todas las subparticiones bajo la partición de primer nivel correspondiente. Bajo la lógica de combinación original, todas las sentencias SQL físicas de las particiones deben completarse antes de que pueda proceder la ordenación por combinación, lo que significa que la partición más lenta (a menudo una más antigua con datos masivos) determina la latencia general.

PolarDB-X aborda esto con poda dinámica basada en la columna de ordenamiento. Debido a que la columna de ordenamiento (id o update_ts) es aproximadamente monótona a lo largo del tiempo, las particiones más nuevas tienen IDs más altos o timestamps más recientes, el optimizador puede dejar de escanear particiones tan pronto como se encuentren suficientes filas. Esto redujo una consulta de 3 segundos a 0.05 segundos en producción.

Perspectiva 3: ordenamiento de índices para condiciones OR/IN

La clave para una paginación rápida en almacenes de filas es la terminación temprana: en lugar de filtrar primero y luego ordenar para seleccionar los K superiores, la consulta debe explotar el ordenamiento del índice para filtrar y seleccionar los K superiores simultáneamente. Los predicados de igualdad hacen esto de forma natural, pero las condiciones OR rompen el ordenamiento del índice.

La solución es dividir las condiciones OR/IN en múltiples subconsultas ordenadas y luego combinarlas:

SELECT * FROM (
    (SELECT * FROM t_swap WHERE asset_a = 'TOKEN26'
     ORDER BY id LIMIT 100)
    UNION
    (SELECT * FROM t_swap WHERE asset_b = 'TOKEN50'
     ORDER BY id LIMIT 100)
) t
ORDER BY id
LIMIT 100;

Cada rama utiliza su propio índice para la terminación temprana. En el escenario de usuario, esto transformó una consulta de 10 segundos en una de 0.001 segundos.

Perspectiva 4: reducir búsquedas en tablas con materialización tardía

Para tablas amplias (60+ columnas), SELECT * con búsquedas en tablas es extremadamente costoso. Los índices covering ayudan, pero consumen almacenamiento significativo. Cuando los índices covering no son factibles, la materialización tardía es la respuesta: primero recuperar solo las claves primarias a través de un índice covering (obteniendo solo el número LÍMITE de filas), luego hacer una búsqueda precisa de clave primaria para esas pocas filas.

SELECT t_order0.*
FROM (
    SELECT id, uid, pt
    FROM t_order 
    WHERE uid = 1
      AND channel_id = 0
      AND sub_id IN (0)
      AND id > 123456789
    ORDER BY update_ts DESC
    LIMIT 20
) AS t3
INNER JOIN t_order AS t_order0
  ON t3.id = t_order0.id
  AND t3.uid = t_order0.uid
  AND t3.pt = t_order0.pt
ORDER BY t_order0.update_ts DESC
LIMIT 20;

Esto redujo una consulta de 10 segundos a 0.6 segundos. Más allá de las optimizaciones a nivel SQL, PolarDB-X también optimiza las búsquedas en tablas a nivel del motor de almacenamiento (adivinación de Guess Primarykey Pageno y direccionamiento físico) para reducir la amplificación de E/S y mejorar las tasas de acierto de caché.

Perspectiva 5: selección determinista de índices

Para tablas de pedidos grandes, la memoria nunca es suficiente. Si se elige el índice incorrecto, grandes volúmenes de datos se cargan en el buffer pool, contaminando la caché y desencadenando fallos en cascada. La selección de índices de la base de datos debe seguir una metodología determinista que se alinee con los diseños de índices recomendados por IA.

La lista de verificación estándar: indexar la columna de ordenamiento (evitar filesort), colocar columnas WHERE de igualdad antes que las columnas ORDER BY en índices compuestos, usar índices covering cuando sea posible, poner columnas de alta selectividad primero, evitar funciones en columnas indexadas y mantener la consistencia de tipos. Cuando el comportamiento de la base de datos coincide con el diseño de índice recomendado, se garantiza la estabilidad. En producción, PolarDB-X mantuvo una selección de índices estable, mientras que una base de datos distribuida competidora mostró un comportamiento inestable, lo que llevó a un pico de QPS de 4K (con colapsos) frente a 60K (escalable linealmente) para PolarDB-X.

Tabla resumen de mejoras del mundo real

Las ideas centrales se pueden resumir como: podar agresivamente, explotar el ordenamiento de índices para la terminación temprana, minimizar las búsquedas en tablas con índices covering o materialización tardía, y asegurar que la selección de índices sea determinista. Para equipos que ejecutan tablas de pedidos grandes en bases de datos distribuidas, aplicar estas cinco lecciones sistemáticamente puede prevenir los errores de rendimiento de paginación más comunes.