Optimizar las operaciones de computación de las consultas

En este documento se describen las prácticas recomendadas para optimizar el rendimiento de las consultas.

Cuando ejecutas una consulta, puedes ver el plan de consulta en la consola Google Cloud . También puedes solicitar detalles de la ejecución mediante las vistas INFORMATION_SCHEMA.JOBS* o el método de la API REST jobs.get.

El plan de consultas incluye detalles sobre las fases y los pasos de la consulta. Estos detalles pueden ayudarte a identificar formas de mejorar el rendimiento de las consultas. Por ejemplo, si observa una fase que escribe mucho más que otras, puede que tenga que filtrar antes en la consulta.

Para obtener más información sobre el plan de consultas y ver ejemplos de cómo puede ayudarte a mejorar el rendimiento de las consultas, consulta Obtener información valiosa sobre el rendimiento de las consultas. Después de abordar las estadísticas de rendimiento de las consultas, puede optimizar aún más su consulta realizando las siguientes tareas:

Reducir los datos procesados

Puedes reducir la cantidad de datos que se deben procesar mediante las opciones que se describen en las siguientes secciones.

Evita SELECT *

Práctica recomendada: Controla la proyección consultando solo las columnas que necesites.

Proyección hace referencia al número de columnas que lee tu consulta. Proyectar columnas en exceso conlleva E/S y materialización adicionales (desperdiciadas).

  • Utiliza las opciones de vista previa de datos. Si estás experimentando con datos o explorándolos, utiliza una de las opciones de vista previa de datos en lugar de SELECT *.
  • Consulta columnas específicas. Aplicar una cláusula LIMIT a una consulta SELECT * no afecta a la cantidad de datos leídos. Se te factura por leer todos los bytes de la tabla completa y la consulta se tiene en cuenta para tu cuota del nivel gratuito. En su lugar, consulta solo las columnas que necesites. Por ejemplo, usa SELECT * EXCEPT para excluir una o varias columnas de los resultados.
  • Usa tablas con particiones. Si necesitas hacer consultas en todas las columnas de una tabla, pero solo en un subconjunto de datos, ten en cuenta lo siguiente:

  • Usa SELECT * EXCEPT. Consultar un subconjunto de datos o usar SELECT * EXCEPT puede reducir considerablemente la cantidad de datos que lee una consulta. Además de ahorrar costes, el rendimiento mejora al reducir la cantidad de E/S de datos y la cantidad de materialización que se requiere para los resultados de la consulta.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Evitar un número excesivo de tablas comodín

Práctica recomendada: Cuando envíes consultas a tablas comodín, debes usar el prefijo más granular.

Usa comodines para consultar varias tablas con instrucciones SQL concisas. Las tablas comodín son una unión de tablas que coinciden con la expresión comodín. Las tablas comodín son útiles si tu conjunto de datos contiene los siguientes recursos:

  • Varias tablas con nombres similares y esquemas compatibles
  • Tablas fragmentadas

Cuando consultas una tabla comodín, especifica un comodín (*) después del prefijo de tabla común. Por ejemplo, FROM bigquery-public-data.noaa_gsod.gsod194* consulta todas las tablas de la década de 1940.

Los prefijos más granulares funcionan mejor que los más cortos. Por ejemplo, FROM bigquery-public-data.noaa_gsod.gsod194* tiene un mejor rendimiento que FROM bigquery-public-data.noaa_gsod.* porque hay menos tablas que coinciden con el comodín.

Evitar las tablas fragmentadas por fecha

Práctica recomendada: no uses tablas fragmentadas por fecha (también llamadas tablas con nombre de fecha) en lugar de tablas con particiones por tiempo.

Las tablas con particiones tienen un mejor rendimiento que las tablas con nombres de fecha. Cuando creas tablas particionadas por fecha, BigQuery debe mantener una copia del esquema y los metadatos de cada tabla con nombre de fecha. Además, cuando se usan tablas con nombres de fecha, es posible que BigQuery tenga que verificar los permisos de cada tabla consultada. Esta práctica también aumenta la sobrecarga de las consultas y afecta al rendimiento de las consultas.

Evitar el particionado excesivo de tablas

Práctica recomendada: No crees demasiados fragmentos de tabla. Si vas a particionar tablas por fecha, usa tablas particionadas por tiempo.

La fragmentación de tablas consiste en dividir grandes conjuntos de datos en tablas independientes y añadir un sufijo al nombre de cada tabla. Si vas a particionar tablas por fecha, usa tablas particionadas por tiempo.

Debido al bajo coste del almacenamiento de BigQuery, no es necesario que optimices tus tablas para reducir los costes, como harías en un sistema de base de datos relacional. Crear un gran número de particiones de tabla tiene un impacto en el rendimiento que supera cualquier ventaja económica.

Las tablas fragmentadas requieren que BigQuery mantenga el esquema, los metadatos y los permisos de cada fragmento. Debido a la sobrecarga adicional necesaria para mantener la información de cada partición, la partición excesiva de tablas puede afectar al rendimiento de las consultas.

La cantidad y la fuente de los datos que lee una consulta pueden influir en el rendimiento y el coste de la consulta.

Eliminar consultas con particiones

Práctica recomendada: Cuando consultes una tabla con particiones, para filtrar con particiones en tablas con particiones, usa las siguientes columnas:

  • En el caso de las tablas con particiones por hora de ingestión, usa la pseudocolumna _PARTITIONTIME.
  • En las tablas con particiones, como las basadas en columnas de unidades de tiempo y las de intervalos de números enteros, usa la columna de partición.

En las tablas con particiones por unidades de tiempo, si filtras los datos con _PARTITIONTIME o con la columna de partición, puedes especificar una fecha o un intervalo de fechas. Por ejemplo, la siguiente cláusula WHERE usa la pseudocolumna _PARTITIONTIME para especificar particiones entre el 1 y el 31 de enero del 2016:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

La consulta solo procesa los datos de las particiones indicadas en el intervalo de fechas. Al filtrar las particiones, se mejora el rendimiento de las consultas y se reducen los costes.

Reducir los datos antes de usar un JOIN

Práctica recomendada: Reduce la cantidad de datos que se procesan antes de una cláusula JOIN realizando agregaciones.

Usar una cláusula GROUP BY con funciones de agregación requiere muchos recursos computacionales, ya que estos tipos de consultas usan shuffle. Como estas consultas requieren muchos recursos computacionales, solo debe usar la cláusula GROUP BY cuando sea necesario.

En las consultas con GROUP BY y JOIN, realiza la agregación antes en la consulta para reducir la cantidad de datos procesados. Por ejemplo, la siguiente consulta realiza una JOIN en dos tablas grandes sin aplicar ningún filtro previamente:

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Esta consulta preagrega los recuentos de comentarios, lo que reduce la cantidad de datos leídos para JOIN:

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

Usa la cláusula WHERE

Práctica recomendada: Usa una cláusula WHERE para limitar la cantidad de datos que devuelve una consulta. Si es posible, utilice las columnas BOOL, INT64, FLOAT64 o DATE en la cláusula WHERE.

Las operaciones en las columnas BOOL, INT64, FLOAT64 y DATE suelen ser más rápidas que las operaciones en las columnas STRING o BYTE. Cuando sea posible, usa una columna que utilice uno de estos tipos de datos en la cláusula WHERE para reducir la cantidad de datos que devuelve la consulta.

Usar vistas materializadas

Práctica recomendada: usa vistas materializadas para precalcular los resultados de una consulta y así aumentar el rendimiento y la eficiencia.

Las vistas materializadas son vistas precalculadas que almacenan en caché periódicamente los resultados de una consulta para mejorar el rendimiento y la eficiencia. BigQuery aprovecha los resultados precalculados de las vistas materializadas y, siempre que sea posible, solo lee los cambios de las tablas base para calcular los resultados actualizados. Las vistas materializadas se pueden consultar directamente o el optimizador de BigQuery puede usarlas para procesar consultas en las tablas base.

Usar BI Engine

Práctica recomendada: Usa BigQuery BI Engine para acelerar las consultas almacenando en caché los datos que usas con más frecuencia.

Añade una reserva de BI Engine al proyecto en el que se calculan las consultas. BigQuery BI Engine usa un motor de consultas vectorizado para acelerar el SELECT rendimiento de las consultas.

Usar índices de búsqueda

Práctica recomendada: Usa índices de búsqueda para buscar filas de forma eficiente cuando necesites encontrar filas de datos concretas en tablas grandes.

Un índice de búsqueda es una estructura de datos diseñada para permitir búsquedas muy eficientes con la función SEARCH, pero también puede acelerar las consultas que usan otros operadores y funciones, como los operadores de igualdad (=), IN o LIKE, y determinadas funciones de cadena y JSON.

Optimizar las operaciones de las consultas

Puede optimizar las operaciones de consulta mediante las opciones que se describen en las siguientes secciones.

Evita transformar los datos repetidamente

Práctica recomendada: Si usas SQL para realizar operaciones de ETL, evita las situaciones en las que transformes los mismos datos repetidamente.

Por ejemplo, si usas SQL para recortar cadenas o extraer datos mediante expresiones regulares, es más eficiente materializar los resultados transformados en una tabla de destino. Las funciones como las expresiones regulares requieren cálculos adicionales. Consultar la tabla de destino sin la sobrecarga de transformación añadida es mucho más eficiente.

Evitar varias evaluaciones de las mismas CTEs

Práctica recomendada: Usa lenguaje de procedimiento, variables, tablas temporales y tablas que caduquen automáticamente para conservar los cálculos y usarlos más adelante en la consulta.

Cuando tu consulta contiene expresiones de tabla comunes (CTEs) que se usan en varios lugares de la consulta, es posible que se evalúen cada vez que se haga referencia a ellas. El optimizador de consultas intenta detectar partes de la consulta que se podrían ejecutar solo una vez, pero no siempre es posible. Por lo tanto, usar una CTE puede no ayudar a reducir la complejidad de las consultas internas y el consumo de recursos.

Puede almacenar el resultado de una CTE en una variable escalar o en una tabla temporal en función de los datos que devuelva la CTE.

Evita las combinaciones y las subconsultas repetidas

Práctica recomendada: Evita unir repetidamente las mismas tablas y usar las mismas subconsultas.

En lugar de combinar los datos repetidamente, puede que sea más eficiente usar datos repetidos anidados para representar las relaciones. Los datos repetidos anidados te ahorran el impacto en el rendimiento del ancho de banda de comunicación que requiere una unión. También te ahorra los costes de E/S que se generan al leer y escribir los mismos datos repetidamente. Para obtener más información, consulta Usar campos anidados y repetidos.

Del mismo modo, repetir las mismas subconsultas afecta al rendimiento mediante el procesamiento repetitivo de consultas. Si usas las mismas subconsultas en varias consultas, te recomendamos que materialices los resultados de la subconsulta en una tabla. A continuación, usa los datos materializados en tus consultas.

Materializar los resultados de las subconsultas mejora el rendimiento y reduce la cantidad total de datos que BigQuery lee y escribe. El pequeño coste de almacenar los datos materializados compensa el impacto en el rendimiento de las operaciones de E/S y el procesamiento de consultas repetidos.

Optimizar los patrones de combinación

Práctica recomendada: En las consultas que combinan datos de varias tablas, optimiza los patrones de combinación empezando por la tabla más grande.

Cuando creas una consulta con una cláusula JOIN, ten en cuenta el orden en el que vas a combinar los datos. El optimizador de consultas de GoogleSQL determina qué tabla debe estar a cada lado de la combinación. Como práctica recomendada, coloque primero la tabla con el mayor número de filas, seguida de la tabla con el menor número de filas y, a continuación, coloque las tablas restantes por orden de tamaño decreciente.

Si tienes una tabla grande a la izquierda de la JOIN y una pequeña a la derecha, se creará una combinación de difusión.JOIN Una combinación de difusión envía todos los datos de la tabla más pequeña a cada ranura que procesa la tabla más grande. Es recomendable realizar la unión de la emisión primero.

Para ver el tamaño de las tablas de tu JOIN, consulta Obtener información sobre las tablas.

Especificar restricciones de clave principal y clave externa

Práctica recomendada: especifica restricciones de clave en el esquema de la tabla cuando los datos de la tabla cumplan los requisitos de integridad de datos de las restricciones de clave principal o de clave externa. El motor de consultas puede usar las restricciones de clave para optimizar los planes de consultas.

BigQuery no comprueba automáticamente la integridad de los datos, por lo que debe asegurarse de que sus datos cumplan las restricciones especificadas en el esquema de la tabla. Si no mantiene la integridad de los datos en las tablas con restricciones especificadas, los resultados de las consultas podrían ser imprecisos.

Optimizar la cláusula ORDER BY

Práctica recomendada: Cuando uses la cláusula ORDER BY, asegúrate de seguir estas prácticas recomendadas:

  • Usa ORDER BY en la consulta más externa o en cláusulas de ventana. Envía las operaciones complejas al final de la consulta. Colocar una cláusula ORDER BY en medio de una consulta afecta considerablemente al rendimiento, a menos que se utilice en una función de ventana.

    Otra técnica para ordenar tu consulta es enviar las operaciones complejas, como las expresiones regulares y las funciones matemáticas, al final de la consulta. Esta técnica reduce los datos que se deben procesar antes de realizar las operaciones complejas.

  • Usa una cláusula LIMIT. Si vas a ordenar un número muy elevado de valores, pero no necesitas que se devuelvan todos, usa una cláusula LIMIT. Por ejemplo, la siguiente consulta ordena un conjunto de resultados muy grande y genera un error Resources exceeded. La consulta ordena los resultados por la columna title en mytable. La columna title contiene millones de valores.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Para eliminar el error, usa una consulta como la siguiente:

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Usa una función de ventana. Si vas a ordenar un número muy elevado de valores, usa una función de ventana y limita los datos antes de llamar a la función de ventana. Por ejemplo, la siguiente consulta muestra los diez usuarios más antiguos de Stack Overflow y su clasificación, donde la cuenta más antigua ocupa el puesto más bajo:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    Esta consulta tarda unos 15 segundos en ejecutarse. Esta consulta usa LIMIT al final de la consulta, pero no en la función de ventana DENSE_RANK() OVER. Por este motivo, la consulta requiere que todos los datos se ordenen en un solo nodo de trabajo.

    En su lugar, debes limitar el conjunto de datos antes de calcular la función de ventana para mejorar el rendimiento:

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    Esta consulta tarda aproximadamente 2 segundos en ejecutarse y devuelve los mismos resultados que la consulta anterior.

    Sin embargo, la función DENSE_RANK() clasifica los datos por años, por lo que, si quieres clasificar datos de varios años, estas consultas no te darán los mismos resultados.

Dividir las consultas complejas en otras más pequeñas

Práctica recomendada: Aprovecha las funciones de consultas con varias instrucciones y los procedimientos almacenados para realizar las operaciones que se diseñaron como una consulta compleja en varias consultas más pequeñas y sencillas.

Las consultas complejas, las funciones REGEX y las subconsultas o las combinaciones en capas pueden ser lentas y consumir muchos recursos. Por ejemplo, intentar incluir todos los cálculos en una enorme instrucción SELECT, como para convertirla en una vista, a veces es un antipatrón y puede dar lugar a una consulta lenta que consuma muchos recursos. En casos extremos, el plan de consulta interno se vuelve tan complejo que BigQuery no puede ejecutarlo.

Dividir una consulta compleja permite materializar los resultados intermedios en variables o tablas temporales. Después, puede usar estos resultados intermedios en otras partes de la consulta. Es cada vez más útil cuando estos resultados se necesitan en más de un lugar de la consulta.

A menudo, te permite expresar mejor la verdadera intención de partes de la consulta, y las tablas temporales son los puntos de materialización de los datos.

Usar campos anidados y repetidos

Para obtener información sobre cómo desnormalizar el almacenamiento de datos mediante campos anidados y repetidos, consulta Usar campos anidados y repetidos.

Usar tipos de datos INT64 en uniones

Práctica recomendada: Usa tipos de datos INT64 en las combinaciones en lugar de tipos de datos STRING para reducir los costes y mejorar el rendimiento de las comparaciones.

BigQuery no indexa las claves principales como las bases de datos tradicionales, por lo que cuanto más ancha sea la columna de unión, más tiempo tardará la comparación. Por lo tanto, usar tipos de datos INT64 en las combinaciones es más barato y eficiente que usar tipos de datos STRING.

Reducir los resultados de las consultas

Puedes reducir los resultados de las consultas mediante las opciones que se describen en las siguientes secciones.

Materializar conjuntos de resultados de gran tamaño

Práctica recomendada: materializa conjuntos de resultados de gran tamaño en una tabla de destino. Escribir conjuntos de resultados de gran tamaño tiene un impacto en el rendimiento y en los costes.

BigQuery limita los resultados almacenados en caché a aproximadamente 10 GB comprimidos. Las consultas que devuelven resultados de mayor tamaño superan este límite y suelen provocar el siguiente error: Response too large.

Este error suele producirse cuando se selecciona un gran número de campos de una tabla con una cantidad considerable de datos. También pueden producirse problemas al escribir resultados almacenados en caché en consultas de tipo ETL que normalizan los datos sin reducción ni agregación.

Para superar la limitación del tamaño de los resultados almacenados en caché, puedes usar las siguientes opciones:

  • Usar filtros para limitar el conjunto de resultados
  • Usa una cláusula LIMIT para reducir el conjunto de resultados, sobre todo si usas una cláusula ORDER BY.
  • Escribir los datos de salida en una tabla de destino

Puedes paginar los resultados mediante la API REST de BigQuery. Para obtener más información, consulta Desplazarse por las páginas de los datos de una tabla.

Evitar antipatrones de SQL

Las siguientes prácticas recomendadas ofrecen directrices para evitar antipatrones de consultas que afecten al rendimiento en BigQuery.

Evita las combinaciones automáticas

Práctica recomendada: En lugar de usar combinaciones automáticas, usa una función de ventana (analítica) o el operador PIVOT.

Por lo general, las autocombinaciones se usan para calcular relaciones dependientes de las filas. El resultado de usar una autocombinación es que se eleva al cuadrado el número de filas de salida. Este aumento de los datos de salida puede provocar un rendimiento deficiente.

Evita las uniones cruzadas

Práctica recomendada: Evita las uniones que generen más resultados que entradas. Cuando se requiere un CROSS JOIN, agrega los datos previamente.

Las combinaciones cruzadas son consultas en las que cada fila de la primera tabla se combina con cada fila de la segunda tabla, con claves no únicas en ambos lados. Una salida será desfavorable cuando el número de filas de la tabla de la izquierda se multiplique por el número de filas de la tabla de la derecha. En casos extremos, es posible que la consulta no finalice.

Si la tarea de consulta se completa, la explicación del plan de consulta muestra las filas de salida en comparación con las filas de entrada. Puedes confirmar un producto cartesiano modificando la consulta para que imprima el número de filas de cada lado de la cláusula JOIN agrupadas por la clave de unión. También puedes consultar las estadísticas de rendimiento en el gráfico de ejecución de consultas de una unión de alta cardinalidad.

Para evitar problemas de rendimiento asociados a combinaciones que generan más salidas que entradas:

  • Usa una cláusula GROUP BY para pre-agregar los datos.
  • Usa una función de ventana. Las funciones de ventana suelen ser más eficientes que las combinaciones cruzadas. Para obtener más información, consulta funciones de ventana.

Evita las instrucciones DML que actualicen o inserten filas individuales

Práctica recomendada: Evita las declaraciones DML que actualicen o inserten filas individuales. Agrupa las actualizaciones e inserciones.

Usar instrucciones DML específicas de un punto es un intento de tratar BigQuery como un sistema de procesamiento de transacciones online (OLTP). BigQuery se centra en el procesamiento analítico online (OLAP) mediante análisis de tablas, no búsquedas puntuales. Si necesitas un comportamiento similar al de OLTP (actualizaciones o inserciones de una sola fila), considera usar una base de datos diseñada para admitir casos prácticos de OLTP, como Cloud SQL.

Las declaraciones de DML de BigQuery están pensadas para hacer actualizaciones masivas. Las instrucciones UPDATE y DELETE de DML en BigQuery están orientadas a la reescritura periódica de los datos, no a las mutaciones de una sola fila. La instrucción INSERT DML se debe usar con moderación. Las inserciones consumen las mismas cuotas de modificación que las tareas de carga. Si su caso práctico implica insertar filas individuales con frecuencia, le recomendamos que transmita sus datos.

Si la agrupación por lotes de tus instrucciones UPDATE genera muchas tuplas en consultas muy largas, es posible que te acerques al límite de longitud de las consultas, que es de 256 KB. Para evitar el límite de longitud de las consultas, plantéate si tus actualizaciones se pueden gestionar en función de un criterio lógico en lugar de una serie de sustituciones directas de tuplas.

Por ejemplo, puede cargar su conjunto de registros de sustitución en otra tabla y, a continuación, escribir la instrucción DML para actualizar todos los valores de la tabla original si las columnas no actualizadas coinciden. Por ejemplo, si los datos originales están en la tabla t y las actualizaciones se almacenan en la tabla u, la consulta sería la siguiente:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Usar nombres de alias para columnas con nombres similares

Práctica recomendada: Usa alias de columnas y tablas cuando trabajes con columnas que tengan nombres similares en varias consultas, incluidas las subconsultas.

Los alias ayudan a identificar las columnas y las tablas a las que se hace referencia, además de la referencia inicial de la columna. Usar alias puede ayudarte a entender y solucionar problemas en tu consulta SQL, como encontrar las columnas que se usan en las subconsultas.

Siguientes pasos