Prácticas recomendadas de SQL

Como se describe en Planes de ejecución de consultas, el compilador de SQL transforma una instrucción SQL en un plan de ejecución de consultas, que se usa para obtener los resultados de la consulta. En esta página se describen las prácticas recomendadas para crear declaraciones SQL que ayuden a Spanner a encontrar planes de ejecución eficientes.

Las instrucciones SQL de ejemplo que se muestran en esta página usan el siguiente esquema de ejemplo:

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Para consultar la referencia completa de SQL, consulte Sintaxis de las instrucciones, Funciones y operadores y Estructura léxica y sintaxis.

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Para obtener más información, consulta El lenguaje PostgreSQL en Spanner.

Usar parámetros de consulta

Spanner admite parámetros de consulta para aumentar el rendimiento y ayudar a evitar la inyección de SQL cuando las consultas se crean con entradas de usuario. Puedes usar parámetros de consulta como sustitutos de expresiones arbitrarias, pero no como sustitutos de identificadores, nombres de columna, nombres de tabla u otras partes de la consulta.

Los parámetros pueden aparecer en cualquier lugar en el que se espere un valor literal. El mismo nombre de parámetro se puede usar más de una vez en una sola instrucción SQL.

En resumen, los parámetros de consulta permiten ejecutar consultas de las siguientes formas:

• Planes preoptimizados: las consultas que usan parámetros se pueden ejecutar más rápido en cada invocación porque la parametrización facilita que Spanner almacene en caché el plan de ejecución.
• Composición de consultas simplificada: no es necesario usar caracteres de escape en los valores de cadena cuando se proporcionan en los parámetros de consulta. Los parámetros de consulta también reducen el riesgo de que se produzcan errores de sintaxis.
• Seguridad: los parámetros de consulta protegen tus consultas frente a diversos ataques de inyección de SQL, lo que las hace más seguras. Esta protección es especialmente importante para las consultas que creas a partir de las entradas de los usuarios.

Entender cómo ejecuta Spanner las consultas

Nota: Spanner Studio (antes llamado Consulta en la Google Cloud consola) admite operaciones de SQL, DML y DDL en un solo editor. Para obtener más información, consulta Gestionar tus datos con la consola de Google Cloud

.

Spanner te permite consultar bases de datos mediante instrucciones SQL declarativas que especifican los datos que quieres obtener. Si quieres saber cómo obtiene Spanner los resultados, examina el plan de ejecución de la consulta. Un plan de ejecución de consultas muestra el coste computacional asociado a cada paso de la consulta. Con esos costes, puedes depurar los problemas de rendimiento de las consultas y optimizarlas. Para obtener más información, consulte Planes de ejecución de consultas.

Puedes obtener planes de ejecución de consultas a través de la consola Google Cloud o de las bibliotecas de cliente.

Para obtener un plan de ejecución de una consulta específica mediante la consola Google Cloud , sigue estos pasos:

1. Abre la página Instancias de Spanner.

   Ir a instancias de Spanner

2. Selecciona los nombres de la instancia de Spanner y de la base de datos que quieras consultar.

3. En el panel de navegación de la izquierda, haga clic en Spanner Studio.

4. Escribe la consulta en el campo de texto y haz clic en Ejecutar consulta.

5. Haz clic en Explicación
   . La consola Google Cloud muestra un plan de ejecución visual de tu consulta.

   

Para obtener más información sobre cómo interpretar los planes visuales y usarlos para depurar tus consultas, consulta el artículo Ajustar una consulta con el visualizador de planes de consultas.

También puede ver ejemplos de planes de consulta históricos y comparar el rendimiento de una consulta a lo largo del tiempo para determinadas consultas. Para obtener más información, consulta Ejemplos de planes de consultas.

Usar índices secundarios

Al igual que otras bases de datos relacionales, Spanner ofrece índices secundarios que puedes usar para recuperar datos mediante una instrucción SQL o la interfaz de lectura de Spanner. La forma más habitual de obtener datos de un índice es usar Spanner Studio. Si usas un índice secundario en una consulta de SQL, puedes especificar cómo quieres que Spanner obtenga los resultados. Si especificas un índice secundario, puedes acelerar la ejecución de las consultas.

Por ejemplo, supongamos que quiere obtener los IDs de todos los cantantes con un apellido específico. Una forma de escribir una consulta de SQL de este tipo es la siguiente:

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Esta consulta devolvería los resultados que esperas, pero podría tardar mucho tiempo en hacerlo. El tiempo dependerá del número de filas de la tabla Singers y de cuántas cumplan el predicado WHERE s.LastName = 'Smith'. Si no hay ningún índice secundario que contenga la columna LastName de la que leer, el plan de consulta leerá toda la tabla Singers para buscar las filas que coincidan con el predicado. Leer toda la tabla se denomina búsqueda en la tabla completa. Un análisis completo de la tabla es una forma costosa de obtener los resultados cuando la tabla contiene solo un pequeño porcentaje de Singers con ese apellido.

Puedes mejorar el rendimiento de esta consulta definiendo un índice secundario en la columna de apellidos:

CREATE INDEX SingersByLastName ON Singers (LastName);

Como el índice secundario SingersByLastName contiene la columna LastName de la tabla indexada y la columna de clave principal SingerId, Spanner puede obtener todos los datos de la tabla de índice, que es mucho más pequeña, en lugar de analizar toda la tabla Singers.

En este caso, Spanner usa automáticamente el índice secundario SingersByLastName al ejecutar la consulta (siempre que hayan transcurrido tres días desde la creación de la base de datos; consulta Nota sobre las bases de datos nuevas). Sin embargo, es mejor indicar explícitamente a Spanner que use ese índice especificando una directiva de índice en la cláusula FROM:

GoogleSQL

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

 SELECT s.SingerId
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Supongamos que también quieres obtener el nombre del cantante además del ID. Aunque la columna FirstName no esté incluida en el índice, debes especificar la directiva de índice como antes:

GoogleSQL

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

PostgreSQL

SELECT s.SingerId, s.FirstName
FROM Singers /*@ FORCE_INDEX=SingersByLastName */ AS s
WHERE s.LastName = 'Smith';

Seguirás obteniendo una mejora del rendimiento al usar el índice, ya que Spanner no tendrá que hacer un análisis completo de la tabla al ejecutar el plan de consulta. En su lugar, selecciona el subconjunto de filas que cumplen el predicado del índice SingersByLastName y, a continuación, busca en la tabla base Singers para obtener el nombre solo de ese subconjunto de filas.

Si quieres que Spanner no tenga que obtener ninguna fila de la tabla base, puedes almacenar una copia de la columna FirstName en el propio índice:

GoogleSQL

CREATE INDEX SingersByLastName ON Singers (LastName) STORING (FirstName);

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

Usar una cláusula STORING (en el dialecto GoogleSQL) o una cláusula INCLUDE (en el dialecto PostgreSQL) como esta supone un coste de almacenamiento adicional, pero ofrece las siguientes ventajas:

• Las consultas de SQL que usan el índice y seleccionan columnas almacenadas en la cláusula STORING o INCLUDE no requieren una unión adicional a la tabla base.
• Las llamadas de lectura que usan el índice pueden leer columnas almacenadas en la cláusula STORING o INCLUDE.

En los ejemplos anteriores se muestra cómo los índices secundarios pueden acelerar las consultas cuando las filas elegidas por la cláusula WHERE de una consulta se pueden identificar rápidamente mediante el índice secundario.

Otro caso en el que los índices secundarios pueden ofrecer ventajas de rendimiento es en determinadas consultas que devuelven resultados ordenados. Por ejemplo, supongamos que quieres obtener todos los títulos de los álbumes y sus fechas de lanzamiento en orden ascendente de fecha de lanzamiento y en orden descendente de título del álbum. Podrías escribir una consulta de SQL de la siguiente manera:

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Sin un índice secundario, esta consulta requiere un paso de ordenación potencialmente caro en el plan de ejecución. Puede acelerar la ejecución de consultas definiendo este índice secundario:

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

A continuación, reescribe la consulta para usar el índice secundario:

GoogleSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Esta consulta y definición de índice cumplen los dos criterios siguientes:

• Para quitar el paso de ordenación, asegúrese de que la lista de columnas de la cláusula ORDER BY sea un prefijo de la lista de claves de índice.
• Para evitar volver a la tabla base para obtener las columnas que faltan, asegúrese de que el índice cubra todas las columnas de la tabla que usa la consulta.

Aunque los índices secundarios pueden acelerar las consultas habituales, si añades índices secundarios, se puede añadir latencia a las operaciones de confirmación, ya que cada índice secundario suele requerir la participación de un nodo adicional en cada confirmación. En la mayoría de las cargas de trabajo, no hay problema en tener algunos índices secundarios. Sin embargo, debes plantearte si te importa más la latencia de lectura o de escritura, y considerar qué operaciones son más importantes para tu carga de trabajo. Realiza una prueba comparativa de tu carga de trabajo para asegurarte de que funciona como esperas.

Para obtener una referencia completa sobre los índices secundarios, consulta Índices secundarios.

Optimizar escaneos

Algunas consultas de Spanner pueden beneficiarse del uso de un método de procesamiento orientado a lotes al analizar datos en lugar del método de procesamiento orientado a filas, que es más habitual. Procesar análisis por lotes es una forma más eficiente de procesar grandes volúmenes de datos a la vez, y permite que las consultas alcancen una menor latencia y un menor uso de la CPU.

La operación de análisis de Spanner siempre empieza a ejecutarse con el método orientado a filas. Durante este tiempo, Spanner recoge varias métricas de tiempo de ejecución. A continuación, Spanner aplica un conjunto de heurísticas basadas en el resultado de estas métricas para determinar el método de análisis óptimo. Cuando sea apropiado, Spanner cambiará a un método de procesamiento orientado a lotes para mejorar el rendimiento y el tiempo de respuesta de los análisis.

Casos de uso habituales

Las consultas con las siguientes características suelen beneficiarse del uso del procesamiento orientado a lotes:

• Grandes análisis de datos que no se actualizan con frecuencia.
• Analiza con predicados en columnas de ancho fijo.
• Analiza con un gran número de búsquedas. Una búsqueda usa un índice para recuperar registros.

Casos prácticos sin mejoras de rendimiento

No todas las consultas se benefician del procesamiento orientado a lotes. Los siguientes tipos de consultas funcionan mejor con el procesamiento de análisis orientado a filas:

• Consultas de búsqueda por puntos: consultas que solo obtienen una fila.
• Consultas de análisis pequeñas: análisis de tablas que solo analizan unas pocas filas, a menos que tengan un número elevado de búsquedas.
• Consultas que usan LIMIT.
• Consultas que leen datos con una alta rotación: consultas en las que se actualiza con frecuencia más del 10% de los datos leídos.
• Consultas con filas que contienen valores grandes: las filas con valores grandes son aquellas que contienen valores superiores a 32.000 bytes (antes de la compresión) en una sola columna.

Consultar el método de análisis utilizado por una consulta

Para comprobar si tu consulta usa el procesamiento orientado a lotes o el procesamiento orientado a filas, o si cambia automáticamente entre los dos métodos de análisis, sigue estos pasos:

1. Ve a la página Instancias de Spanner en la consola deGoogle Cloud .

   Ir a la página Instancias

2. Haga clic en el nombre de la instancia que contenga la consulta que quiera investigar.

3. En la tabla Bases de datos, haz clic en la base de datos que contiene la consulta que quieres investigar.

4. En el menú de navegación, haz clic en Spanner Studio.

5. Abre una pestaña nueva haciendo clic en Nueva pestaña del editor de SQL o en Nueva pestaña.

6. Cuando aparezca el editor de consultas, escribe la consulta.

7. Haz clic en Ejecutar.

   Spanner ejecuta la consulta y muestra los resultados.

8. Haz clic en la pestaña Explicación situada debajo del editor de consultas.

   Spanner muestra un visualizador de planes de ejecución de consultas. Cada tarjeta del gráfico representa un iterador.

9. Haz clic en la tarjeta de iterador Búsqueda en tabla para abrir un panel de información.

   El panel de información muestra información contextual sobre la exploración seleccionada. El método de análisis se muestra en esta tarjeta. Automático indica que Spanner determina el método de búsqueda. Otros valores posibles son Batch para el procesamiento orientado a lotes y Row para el procesamiento orientado a filas.

   

Forzar el método de análisis utilizado por una consulta

Para optimizar el rendimiento de las consultas, Spanner elige el método de análisis óptimo para tu consulta. Te recomendamos que utilices este método de análisis predeterminado. Sin embargo, puede haber casos en los que quieras aplicar un tipo específico de método de análisis.

Aplicar el análisis orientado a lotes

Puedes aplicar el análisis orientado a lotes a nivel de tabla y de instrucción.

Para aplicar el método de análisis orientado a lotes a nivel de tabla, usa una sugerencia de tabla en tu consulta:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=BATCH} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=batch */ JOIN t2 on ...)
  WHERE ...

Para aplicar el método de análisis orientado a lotes a nivel de instrucción, usa una sugerencia de instrucción en tu consulta:

GoogleSQL

  @{SCAN_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

Inhabilitar el escaneado automático y forzar el escaneado orientado a las filas

Aunque no recomendamos inhabilitar el método de análisis automático definido por Spanner, puedes hacerlo y usar el método de análisis orientado a filas para solucionar problemas, como diagnosticar la latencia.

Para inhabilitar el método de análisis automático y forzar el procesamiento de filas en la tabla, usa una sugerencia de tabla en tu consulta:

GoogleSQL

  SELECT ...
  FROM (t1@{SCAN_METHOD=ROW} JOIN t2 ON ...)
  WHERE ...

PostgreSQL

  SELECT ...
  FROM (t1/*@ scan_method=row */ JOIN t2 on ...)
  WHERE ...

Para inhabilitar el método de análisis automático y forzar el procesamiento de filas en el nivel de la instrucción, usa una sugerencia de instrucción en tu consulta:

GoogleSQL

  @{SCAN_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ scan_method=row */
  SELECT ...
  FROM ...
  WHERE ...

Optimizar la ejecución de consultas

Además de optimizar los análisis, también puede optimizar la ejecución de consultas aplicando el método de ejecución a nivel de instrucción. Esto solo funciona con algunos operadores y es independiente del método de análisis, que solo utiliza el operador de análisis.

De forma predeterminada, la mayoría de los operadores se ejecutan con el método orientado a filas, que procesa los datos de una fila cada vez. Los operadores vectorizados se ejecutan en el método orientado a lotes para mejorar el rendimiento de la ejecución. Estos operadores procesan los datos de un bloque cada vez. Cuando un operador necesita procesar muchas filas, el método de ejecución orientado a lotes suele ser más eficiente.

Método de ejecución frente a método de análisis

El método de ejecución de consultas es independiente del método de análisis de consultas. Puedes definir uno, ambos o ninguno de estos métodos en tu sugerencia de consulta.

El método de ejecución de consultas hace referencia a la forma en que los operadores de consultas procesan los resultados intermedios y a cómo interactúan entre sí, mientras que el método de análisis hace referencia a la forma en que el operador de análisis interactúa con la capa de almacenamiento de Spanner.

Forzar el método de ejecución utilizado por la consulta

Para optimizar el rendimiento de las consultas, Spanner elige el método de ejecución óptimo para tu consulta en función de varias heurísticas. Te recomendamos que utilices este método de ejecución predeterminado. Sin embargo, puede haber situaciones en las que quieras aplicar un tipo específico de método de ejecución.

Puedes aplicar tu método de ejecución a nivel de instrucción. EXECUTION_METHOD es una sugerencia de consulta, no una directiva. En última instancia, el optimizador de consultas decide qué método usar para cada operador.

Para aplicar el método de ejecución orientado a lotes a nivel de instrucción, usa una sugerencia de instrucción en tu consulta:

GoogleSQL

  @{EXECUTION_METHOD=BATCH}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ execution_method=batch */
  SELECT ...
  FROM ...
  WHERE ...

Aunque no recomendamos inhabilitar el método de ejecución automática definido por Spanner, puede que decida inhabilitarlo y usar el método de ejecución orientado a filas para solucionar problemas, como diagnosticar la latencia.

Para inhabilitar el método de ejecución automática y aplicar el método de ejecución orientado a filas a nivel de instrucción, utilice una sugerencia de instrucción en su consulta:

GoogleSQL

  @{EXECUTION_METHOD=ROW}
  SELECT ...
  FROM ...
  WHERE ...

PostgreSQL

  /*@ execution_method=row */
  SELECT ...
  FROM ...
  WHERE ...

Comprobar qué método de ejecución está habilitado

No todos los operadores de Spanner admiten métodos de ejecución orientados a lotes y a filas. En cada operador, el visualizador del plan de ejecución de consultas muestra el método de ejecución en la tarjeta del iterador. Si el método de ejecución está orientado a lotes, se muestra Lote. Si está orientada a las filas, se muestra Fila.

Si los operadores de tu consulta se ejecutan mediante diferentes métodos de ejecución, los adaptadores de métodos de ejecución DataBlockToRowAdapter y RowToDataBlockAdapter aparecerán entre los operadores para mostrar el cambio en el método de ejecución.

Optimizar las búsquedas de claves de intervalo

Un uso habitual de una consulta de SQL es leer varias filas de Spanner basándose en una lista de claves conocidas.

Las siguientes prácticas recomendadas te ayudarán a escribir consultas eficientes al obtener datos por un intervalo de claves:

• Si la lista de claves es dispersa y no adyacente, utilice parámetros de consulta y UNNEST para crear la consulta.

  Por ejemplo, si tu lista de claves es {1, 5, 1000}, escribe la consulta de esta forma:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST (@KeyList)

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key IN UNNEST ($1)

  Notas:

  • El operador UNNEST de matriz aplana una matriz de entrada en filas de elementos.

  • El parámetro de consulta, que es @KeyList en GoogleSQL y $1 en PostgreSQL, puede acelerar la consulta, tal como se explica en la práctica recomendada anterior.

• Si la lista de claves es adyacente y está dentro de un intervalo, especifica los límites inferior y superior del intervalo de claves en la cláusula WHERE.

  Por ejemplo, si tu lista de claves es {1,2,3,4,5}, crea la consulta de la siguiente manera:

  GoogleSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN @min AND @max

  Nota: En este caso, @min y @max son parámetros de consulta que están vinculados a los valores 1 y 5, respectivamente.

  PostgreSQL

  SELECT *
  FROM Table AS t
  WHERE t.Key BETWEEN $1 AND $2

  Nota: En este caso, $1 y $2 son parámetros de consulta que están vinculados a los valores 1 y 5, respectivamente.

  Esta consulta solo es más eficiente si las claves del intervalo de claves son adyacentes. Es decir, si tu lista de claves es {1, 5, 1000}, no especifiques los límites inferior y superior como en la consulta anterior, ya que la consulta resultante analizaría todos los valores entre 1 y 1000.

Optimizar las uniones

Las operaciones de unión pueden ser costosas porque pueden aumentar significativamente el número de filas que debe analizar tu consulta, lo que da lugar a consultas más lentas. Además de las técnicas que sueles usar en otras bases de datos relacionales para optimizar las consultas JOIN, aquí tienes algunas prácticas recomendadas para usar JOIN de forma más eficiente con Spanner SQL:

• Si es posible, combine los datos de las tablas intercaladas por clave principal. Por ejemplo:

  SELECT s.FirstName, a.ReleaseDate
  FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

  Las filas de la tabla intercalada Albums se almacenan físicamente en las mismas divisiones que la fila principal de Singers, tal como se explica en Esquema y modelo de datos. Por lo tanto, las combinaciones se pueden completar de forma local sin enviar muchos datos a través de la red.

• Usa la directiva join si quieres forzar el orden de la unión. Por ejemplo:

  GoogleSQL

  SELECT *
  FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  PostgreSQL

  SELECT *
  FROM Singers AS s JOIN/*@ FORCE_JOIN_ORDER=TRUE */ Albums AS a
  ON s.SingerId = a.Singerid
  WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

  La directiva de unión FORCE_JOIN_ORDER indica a Spanner que use el orden de unión especificado en la consulta (es decir, Singers JOIN Albums, no Albums JOIN Singers). Los resultados devueltos son los mismos, independientemente del orden que elija Spanner. Sin embargo, puede que quieras usar esta directiva de unión si observas en el plan de consulta que Spanner ha cambiado el orden de la unión y ha provocado consecuencias no deseadas, como resultados intermedios más grandes, o si no ha aprovechado oportunidades para buscar filas.

• Usa una directiva de unión para elegir una implementación de unión. Cuando usas SQL para consultar varias tablas, Spanner utiliza automáticamente un método de unión que probablemente hará que la consulta sea más eficiente. Sin embargo, Google te recomienda que hagas pruebas con diferentes algoritmos de unión. Elegir el algoritmo de unión adecuado puede mejorar la latencia, el consumo de memoria o ambos. Esta consulta muestra la sintaxis para usar una directiva JOIN con la sugerencia JOIN_METHOD para elegir un HASH JOIN:

  GoogleSQL

  SELECT *
  FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
  ON a.SingerId = a.SingerId

  PostgreSQL

  SELECT *
  FROM Singers s JOIN/*@ JOIN_METHOD=HASH_JOIN */ Albums AS a
  ON a.SingerId = a.SingerId

• Si usas HASH JOIN o APPLY JOIN y tienes una cláusula WHERE que es muy selectiva en un lado de JOIN, coloca la tabla que produce el menor número de filas como la primera tabla de la cláusula FROM de la unión. Esta estructura es útil porque, en HASH JOIN, Spanner siempre elige la tabla de la izquierda como tabla de compilación y la tabla de la derecha como tabla de sondeo. Del mismo modo, para APPLY JOIN, Spanner elige la tabla de la izquierda como externa y la tabla de la derecha como interna. Consulta más información sobre estos tipos de combinación: Combinación hash y Combinación de aplicación.

• En las consultas que sean cruciales para tu carga de trabajo, especifica el método de unión y el orden de unión más eficientes en tus instrucciones SQL para obtener un rendimiento más constante.

Optimizar consultas con la inserción de predicados de marca de tiempo

El pushdown de predicados de marca de tiempo es una técnica de optimización de consultas que se usa en Spanner para mejorar la eficiencia de las consultas que usan marcas de tiempo y datos con una política de almacenamiento por niveles basada en la antigüedad. Cuando habilita esta optimización, las operaciones de filtrado en columnas de marca de tiempo se realizan lo antes posible en el plan de ejecución de la consulta. De esta forma, se puede reducir considerablemente la cantidad de datos que se procesan y mejorar el rendimiento general de las consultas.

Con la inserción de predicados de marca de tiempo, el motor de la base de datos analiza la consulta e identifica el filtro de marca de tiempo. Después, "transfiere" este filtro a la capa de almacenamiento para que solo se lean de la unidad SSD los datos pertinentes en función de los criterios de marca de tiempo. De esta forma, se minimiza la cantidad de datos que se procesan y transfieren, lo que da como resultado una ejecución de consultas más rápida.

Para optimizar las consultas de forma que solo accedan a los datos almacenados en SSD, se deben cumplir los siguientes requisitos:

• La consulta debe tener habilitada la inserción de predicados de marca de tiempo. Para obtener más información, consulta Sugerencias de instrucciones de GoogleSQL y Sugerencias de instrucciones de PostgreSQL.
• La consulta debe usar una restricción basada en la edad igual o inferior a la edad especificada en la política de desbordamiento de datos (definida con la opción ssd_to_hdd_spill_timespan en la instrucción DDL CREATE LOCALITY GROUP o ALTER LOCALITY GROUP). Para obtener más información, consulta las instrucciones LOCALITY GROUP de GoogleSQL y las instrucciones LOCALITY GROUP de PostgreSQL.

• La columna que se filtra en la consulta debe ser una columna de marca de tiempo que contenga la marca de tiempo de la confirmación. Para obtener información sobre cómo crear una columna de marca de tiempo de confirmación, consulta Marcas de tiempo de confirmación en GoogleSQL y Marcas de tiempo de confirmación en PostgreSQL. Estas columnas deben actualizarse junto con la columna de marca de tiempo y residir en el mismo grupo de localidad, que tiene una política de almacenamiento por niveles basada en la antigüedad.

  Si, en una fila determinada, algunas de las columnas consultadas residen en SSD y otras en HDD (porque las columnas se actualizan en momentos diferentes y se transfieren a HDD en momentos diferentes), el rendimiento de la consulta puede ser peor cuando se usa la sugerencia. Esto se debe a que la consulta tiene que rellenar los datos de las diferentes capas de almacenamiento. Como resultado de usar la sugerencia, Spanner asigna una antigüedad a los datos a nivel de celda (nivel de granularidad de fila y columna) en función de la marca de tiempo de confirmación de cada celda, lo que ralentiza la consulta. Para evitar este problema, asegúrate de actualizar periódicamente todas las columnas que se consulten con esta técnica de optimización en la misma transacción para que todas las columnas compartan la misma marca de tiempo de confirmación y se beneficien de la optimización.

Para habilitar la inserción de predicados de marca de tiempo a nivel de instrucción, usa una sugerencia de instrucción en tu consulta. Por ejemplo:

GoogleSQL

  @{allow_timestamp_predicate_pushdown=TRUE}
  SELECT s.SingerInfo
  FROM Singers s
  WHERE s.ModificationTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

PostgreSQL

  /*@allow_timestamp_predicate_pushdown=TRUE*/
  SELECT s.SingerInfo
  FROM Singers s
  WHERE s.ModificationTime > CURRENT_TIMESTAMP - INTERVAL '12 hours';

Evita las lecturas grandes en transacciones de lectura y escritura

Las transacciones de lectura y escritura permiten una secuencia de cero o más lecturas o consultas de SQL, y pueden incluir un conjunto de mutaciones antes de una llamada a commit. Para mantener la coherencia de tus datos, Spanner adquiere bloqueos al leer y escribir filas en tus tablas e índices. Para obtener más información sobre el bloqueo, consulta Tiempo de vida de las lecturas y escrituras.

Debido al funcionamiento del bloqueo en Spanner, al realizar una lectura o una consulta SQL que lee un gran número de filas (por ejemplo, SELECT * FROM Singers), ninguna otra transacción puede escribir en las filas que has leído hasta que tu transacción se confirme o se cancele.

Además, como tu transacción procesa un gran número de filas, es probable que tarde más que una transacción que lea un intervalo de filas mucho menor (por ejemplo, SELECT LastName FROM Singers WHERE SingerId = 7), lo que agrava aún más el problema y reduce el rendimiento del sistema.

Por lo tanto, intenta evitar lecturas grandes (por ejemplo, análisis de tablas completas u operaciones de unión masivas) en tus transacciones, a menos que quieras aceptar un menor rendimiento de escritura.

En algunos casos, el siguiente patrón puede dar mejores resultados:

1. Realiza las lecturas grandes dentro de una transacción de solo lectura. Las transacciones de solo lectura permiten un mayor rendimiento agregado porque no usan bloqueos.
2. Opcional: Realiza el procesamiento necesario en los datos que acabas de leer.
3. Inicia una transacción de lectura y escritura.
4. Verifica que los valores de las filas críticas no hayan cambiado desde que realizaste la transacción de solo lectura en el paso 1.
   • Si las filas han cambiado, revierte la transacción y vuelve a empezar en el paso 1.
   • Si todo parece correcto, confirma las mutaciones.

Una forma de asegurarse de que no se realizan lecturas grandes en las transacciones de lectura y escritura es consultar los planes de ejecución que generan las consultas.

Usa ORDER BY para asegurarte de que los resultados de SQL estén ordenados

Si esperas que los resultados de una consulta SELECT se ordenen de una forma determinada, incluye explícitamente la cláusula ORDER BY. Por ejemplo, si quieres mostrar todos los cantantes por orden de clave principal, usa esta consulta:

SELECT * FROM Singers
ORDER BY SingerId;

Spanner garantiza el orden de los resultados solo si la cláusula ORDER BY está presente en la consulta. Es decir, considera esta consulta sin el ORDER BY:

SELECT * FROM Singers;

Spanner no garantiza que los resultados de esta consulta estén ordenados por clave principal. Además, el orden de los resultados puede cambiar en cualquier momento y no se garantiza que sea coherente de una invocación a otra. Si una consulta tiene una cláusula ORDER BY y Spanner usa un índice que proporciona el orden necesario, Spanner no ordena los datos de forma explícita. Por lo tanto, no te preocupes por el impacto en el rendimiento de incluir esta cláusula. Para comprobar si se incluye una operación de ordenación explícita en la ejecución, consulta el plan de consultas.

Usar STARTS_WITH en lugar de LIKE

Como Spanner no evalúa los patrones LIKE parametrizados hasta el tiempo de ejecución, debe leer todas las filas y evaluarlas con la expresión LIKE para filtrar las filas que no coincidan.

Cuando un patrón LIKE tiene la forma foo% (por ejemplo, empieza por una cadena fija y termina con un único comodín de porcentaje) y la columna está indexada, utilice STARTS_WITH en lugar de LIKE. Esta opción permite que Spanner optimice de forma más eficaz el plan de ejecución de consultas.

No recomendado:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Nota: En este ejemplo se da por hecho que @like_clause está enlazado a 'Love%'.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

Nota: En este ejemplo se da por hecho que $2 está enlazado a 'Love%'.

Recomendación:

GoogleSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);

Nota: En este ejemplo se da por hecho que @prefix está enlazado a 'Love'. Esta consulta es más eficiente que la anterior. Se ejecuta más rápido si se define un índice en Albums.AlbumTitle.

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

Nota: En este ejemplo se da por hecho que $1 está enlazado a 'Love'. Esta consulta es más eficiente que la anterior. Se ejecuta más rápido si se define un índice en Albums.AlbumTitle.

Usar marcas de tiempo de confirmación

Si tu aplicación necesita consultar datos escritos después de una hora concreta, añade columnas de marca de tiempo de confirmación a las tablas correspondientes. Las marcas de tiempo de confirmación permiten una optimización de Spanner que puede reducir las operaciones de entrada/salida de las consultas cuyas cláusulas WHERE restringen los resultados a las filas escritas más recientemente que un momento específico.

Consulta más información sobre esta optimización con bases de datos de dialecto GoogleSQL o con bases de datos de dialecto PostgreSQL.