Prácticas recomendadas sobre SQL

Como se describe en Planes de ejecución de consultas, el compilador de SQL transforma una instrucción de 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 construir instrucciones de SQL que ayuden a Cloud Spanner a encontrar planes de ejecución eficientes.

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

SQL estándar de Google

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 obtener la referencia completa de SQL, consulta Expresiones, funciones y operadores, Estructura léxica y sintaxis y la página sobre la sintaxis de instrucción.

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 Cloud Spanner.

Usa parámetros de consulta para agilizar las consultas que se ejecutan con frecuencia

Las consultas con parámetros son una técnica de ejecución de consultas que separa una string de consulta de los valores del parámetro de consulta. Por ejemplo, supongamos que tu aplicación debe recuperar cantantes que lanzaron álbumes con ciertos títulos en un año determinado. Puedes escribir una instrucción de SQL como el siguiente ejemplo para recuperar todos los álbumes titulados "Love" lanzados desde 2017:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

En otra consulta, puedes cambiar el valor del título del álbum a “Peace”:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Si tu aplicación necesita ejecutar muchas consultas similares a esta, en la que solo cambia un valor literal en consultas posteriores, usa un marcador de posición de parámetro para ese valor. La consulta paramétrica resultante puede almacenarse en caché y volverse a usar, lo que reduce los costos de compilación.

Por ejemplo, la siguiente consulta reescrita reemplaza Love por un parámetro llamado title:

SQL estándar de Google

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Una referencia del parámetro en la consulta usa el carácter @ seguido del nombre del parámetro, que puede contener cualquier combinación de letras, números y guiones bajos. Especifica el parámetro de consulta y el valor para vincularlo en el campo params de la API de solicitud ExecuteSQL o ExecuteStreamingSQL. Obtén más información sobre la sintaxis del parámetro de consulta en Sintaxis y estructura léxica de SQL.

PostgreSQL

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = $1 AND a.ReleaseDate >= '2017-01-01'

Una referencia de parámetro en la consulta usa el carácter $ seguido de un número que representa el parámetro posicional en la declaración.

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

En resumen, los parámetros de consulta admiten la ejecución de consultas de las siguientes maneras:

  • Planes optimizados con anterioridad: las consultas que usan parámetros se pueden ejecutar más rápido en cada invocación porque la parametrización facilita que Cloud Spanner almacene en caché el plan de ejecución.
  • Composición simplificada de la consulta: no es necesario escapar valores de string cuando los proporcionas en los parámetros de consulta. Los parámetros de consulta también reducen el riesgo de errores de sintaxis.
  • Seguridad: Los parámetros de consulta hacen que tus consultas sean más seguras, ya que te protegen de varios ataques de inyección de SQL. Esta protección es importante en particular para las consultas que creas a partir de las entradas del usuario.

Comprende cómo Cloud Spanner ejecuta las consultas

Cloud Spanner te permite consultar bases de datos mediante instrucciones de SQL declarativas que especifican los datos que deseas recuperar. Si deseas comprender también cómo Cloud Spanner obtiene los resultados, usa los planes de ejecución de consultas. Un plan de ejecución de consultas muestra el costo de procesamiento asociado con cada paso de la consulta. Con esos costos, puedes depurar los problemas de rendimiento de las consultas y optimizar tu consulta.

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

Para obtener un plan de consultas con Console, haz lo siguiente:

  1. Abre la página Instancias de Cloud Spanner.

    Ir a Instancias de Cloud Spanner

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

  3. Haz clic en Consulta en el panel de navegación izquierdo.

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

  5. Haz clic en Explicación
    . En la consola, se muestra un plan de ejecución visual para tu consulta.

    Captura de pantalla del plan de ejecución visual en Cloud Console

Para obtener más información sobre los planes visuales, consulta Ajusta una consulta con el visualizador del plan de consulta.

Para obtener la referencia completa del plan de consultas, visita la página sobre planes de ejecución de consultas.

Usa índices secundarios para acelerar las consultas comunes

Al igual que otras bases de datos relacionales, Cloud Spanner ofrece índices secundarios, que puedes usar para recuperar datos mediante una instrucción de SQL o la interfaz de lectura de Cloud Spanner. La forma más común de recuperar datos de un índice es usar la interfaz de consulta de SQL. Usar un índice secundario en una consulta de SQL te permite especificar cómo deseas que Cloud Spanner obtenga los resultados. Especificar un índice secundario puede acelerar la ejecución de consultas.

Por ejemplo, supongamos que deseas recuperar los ID de todos los cantantes con un apellido específico. Se puede escribir una consulta de SQL de este tipo de la siguiente forma:

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

Esta consulta mostrará los resultados que esperas, pero podría tardar mucho tiempo en hacerlo. El tiempo dependerá de la cantidad de filas de la tabla Singers y de cuántas satisfagan el predicado WHERE s.LastName = 'Smith'. Si no hay ningún índice secundario que contenga la columna LastName desde la cual leer, el plan de consulta leerá toda la tabla Singers para encontrar filas que coincidan con el predicado. Leer toda la tabla se denomina análisis completo de la tabla. 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.

Para mejorar el rendimiento de esta consulta, define un índice secundario en la columna de apellido:

CREATE INDEX SingersByLastName on Singers (LastName);

Dado que el índice secundario SingersByLastName contiene la columna de tabla indexada LastName y la columna de clave primaria SingerId, Cloud Spanner puede recuperar todos los datos de la tabla de índice mucho más pequeña en lugar de analizar la tabla Singers completa.

En esta situación, Cloud Spanner usa de forma automática el índice secundario SingersByLastName cuando ejecuta la consulta (siempre que hayan transcurrido tres días desde la creación de la base de datos; consulta Nota sobre bases de datos nuevas). Sin embargo, es mejor indicarle explícitamente a Cloud Spanner que use ese índice mediante la especificación de una directiva de índice en la cláusula FROM:

SQL estándar de Google

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 deseas recuperar el nombre del cantante, además del ID. Aunque la columna FirstName no está incluida en el índice, aún debes especificar la directiva de índice como antes:

SQL estándar de Google

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';

También obtienes un beneficio en el rendimiento por usar el índice, ya que Cloud Spanner no necesita realizar un análisis completo de la tabla cuando ejecuta el plan de consultas. En cambio, selecciona el subconjunto de filas que satisfacen el predicado del índice SingersByLastName y, luego, realiza una búsqueda desde la tabla base Singers a fin de recuperar el nombre solo para ese subconjunto de filas.

Si deseas que Cloud Spanner no tenga que recuperar ninguna fila de la tabla base, puedes almacenar una copia de la columna FirstName en el índice:

SQL estándar de Google

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

PostgreSQL

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

El uso de una cláusula STORING (para el dialecto SQL estándar de Google) o una cláusula INCLUDE (para el dialecto PostgreSQL) como esta cuesta almacenamiento adicional, pero proporciona las siguientes ventajas para las consultas y las llamadas de lectura que usan el índice:

  • Las consultas de SQL que usan el índice y seleccionan las 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 las columnas almacenadas en la cláusula STORING o INCLUDE.

En los ejemplos anteriores, se ilustra cómo los índices secundarios pueden acelerar las consultas cuando las filas que eligió la cláusula WHERE de una consulta se pueden identificar con rapidez mediante el índice secundario.

Otra situación en la que los índices secundarios pueden ofrecer beneficios de rendimiento es para determinadas consultas que muestran resultados ordenados. Por ejemplo, supongamos que deseas recuperar todos los títulos de álbumes y sus fechas de lanzamiento en orden ascendente, y en orden descendente. Puedes 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 clasificación potencialmente costoso en el plan de ejecución. Para acelerar la ejecución de consultas, define este índice secundario:

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

Luego, vuelve a escribir la consulta para usar el índice secundario:

SQL estándar de Google

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 la definición de índice cumplen con los siguientes criterios:

  • La lista de columnas en la cláusula ORDER BY es un prefijo de la lista de claves del índice.
  • El índice abarca todas las columnas de la tabla que usa la consulta.

Debido a que se cumplen estas dos condiciones, el plan de consulta resultante quita el paso de ordenamiento y se ejecuta más rápido.

Aunque los índices secundarios pueden acelerar las consultas comunes, agregar índices secundarios puede agregar latencia a tus operaciones de confirmación, ya que cada índice secundario suele requerir la participación de un nodo adicional en cada confirmación. Para la mayoría de las cargas de trabajo, está bien tener unos pocos índices secundarios. Sin embargo, debes considerar si tienes que preocuparte más por la latencia de lectura o escritura y qué operaciones son más importantes para tu carga de trabajo. Compara tu carga de trabajo para asegurarte de que funcione de la forma esperada.

Para obtener la referencia completa de los índices secundarios, consulta la página sobre los índices secundarios.

Escribe consultas eficientes para la búsqueda por clave de rango

Un uso común de una consulta de SQL es leer varias filas de Cloud Spanner en función de una lista de claves conocidas.

Las siguientes prácticas recomendadas te ayudan a escribir consultas eficientes cuando se recuperan datos mediante un rango de claves:

  • Si la lista de claves es dispersa y no adyacente, usa los parámetros de consulta y UNNEST para construir tu consulta.

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

    SQL estándar de Google

    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 de arreglo UNNEST compacta un arreglo de entrada en filas de elementos.

    • El parámetro de consulta, que es @KeyList para SQL estándar de Google y $1 para PostgreSQL, puede acelerar tu consulta, como se explica en la práctica recomendada anterior.

  • Si la lista de claves es adyacente y está dentro de un rango, especifica los límites inferior y superior del rango 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:

    SQL estándar de Google

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

    PostgreSQL

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

    Esta consulta solo es más eficiente si las claves del rango de claves son adyacentes. En otras palabras, si tu lista de claves es {1, 5, 1000}, no especifiques los límites inferior y superior como en la consulta anterior porque la consulta resultante analizará cada valor entre 1 y 1,000.

Escribe consultas eficientes para uniones

Las operaciones de unión pueden ser costosas porque pueden aumentar de manera significativa la cantidad de filas que tu consulta necesita analizar, lo que da como resultado consultas más lentas. Además de las técnicas que sueles usar en otras bases de datos relacionales a fin de optimizar las consultas de unión, estas son algunas prácticas recomendadas para una JOIN más eficiente cuando se usa SQL de Cloud Spanner:

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

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

    Se garantiza que las filas de la tabla intercalada Albums se almacenarán de manera física en las mismas divisiones que la fila superior en Singers, como se explica en Esquema y modelo de datos. Por lo tanto, las uniones pueden completarse de forma local sin enviar muchos datos a través de la red.

  • Usa la directiva de unión si deseas forzar su orden. Por ejemplo:

    SQL estándar de Google

    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 le indica a Cloud Spanner que use el orden de unión especificado en la consulta (es decir, Singers JOIN Albums, no Albums JOIN Singers). Los resultados que se muestran son los mismos, sin importar el orden que elija Cloud Spanner. Sin embargo, es posible que desees usar esta directiva de unión si observas en el plan de consulta que Cloud Spanner cambió el orden de unión y causó resultados no deseados, como resultados intermedios más grandes, o si perdió oportunidades de buscar filas.

  • Usa una directiva de unión para elegir una implementación de unión. Cuando usas SQL para consultar varias tablas, Cloud Spanner usa de forma automática un método de unión que probablemente haga que la consulta sea más eficiente. Sin embargo, Google recomienda que pruebes con diferentes algoritmos de unión. Elegir el algoritmo de unión correcto puede mejorar la latencia, el consumo de memoria o ambos. En esta consulta, se muestra la sintaxis para usar una directiva JOIN con la sugerencia JOIN_METHOD a fin de elegir un HASH JOIN:

    SQL estándar de Google

    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 un HASH JOIN o APPLY JOIN y si tienes una cláusula WHERE que es muy selectiva a un lado de tu JOIN, coloca la tabla que produce la menor cantidad de filas como la primera tabla en la cláusula FROM de la unión. Esta estructura es útil porque, actualmente, en HASH JOIN, Cloud Spanner siempre elige la tabla lateral izquierda como compilación y la tabla lateral derecha como sondeo. Del mismo modo, para APPLY JOIN, Cloud Spanner elige la tabla lateral izquierda como externa y la tabla derecha como interna. Obtén más información sobre estos tipos de combinación: unión hash y aplicar unión.

  • En el caso de las consultas que son fundamentales para tu carga de trabajo, especifica el método de unión con mejor rendimiento y el orden de unión en tus instrucciones de SQL a fin de obtener un rendimiento más coherente.

Evita lecturas extensas en las 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 para confirmar. Para mantener la coherencia de tus datos, Cloud Spanner adquiere bloqueos cuando lee y escribe filas en tus tablas y en los índices. Para obtener más información sobre el bloqueo, consulta La vida de las lecturas y escrituras.

Debido al modo en que funciona el bloqueo en Cloud Spanner, realizar una consulta de lectura o de SQL que lee una gran cantidad de filas (por ejemplo, SELECT * FROM Singers) significa que ninguna otra transacción puede escribir en las filas que leíste hasta que la transacción se confirma o se anula.

Además, debido a que tu transacción procesa una gran cantidad de filas, es probable que demore más que una transacción que lee un rango de filas mucho más pequeño (por ejemplo, SELECT LastName FROM Singers WHERE SingerId = 7), lo que agrava aún más el problema y reduce la capacidad de procesamiento del sistema.

Por lo tanto, evita las lecturas grandes (por ejemplo, análisis de tablas completos o operaciones de unión masivas) en tus transacciones, a menos que estés dispuesto a aceptar una capacidad de procesamiento de escritura menor.

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

  1. Realiza operaciones de lectura grandes en una transacción de solo lectura. Las transacciones de solo lectura permiten una mayor capacidad de procesamiento agregada porque no usan bloqueos.
  2. [Opcional] Realiza cualquier procesamiento necesario en los datos que acabas de leer.
  3. Inicia una transacción de lectura y escritura.
  4. Verifica que las filas críticas no hayan cambiado los valores desde que realizaste la transacción de solo lectura en el paso 1.
    • Si las filas cambiaron, revierte la transacción y comienza de nuevo en el paso 1.
    • Si todo sale bien, confirma tus mutaciones.

Una forma de asegurarte de evitar grandes lecturas en transacciones de lectura y escritura es observar los planes de ejecución que generan tus consultas.

Usa la cláusula ORDER BY para garantizar el orden de tus resultados de SQL

Si esperas un orden determinado para los resultados de una consulta SELECT, incluye la cláusula ORDER BY de forma explícita. Por ejemplo, si deseas enumerar todos los cantantes en el orden de la clave primaria, usa esta consulta:

SELECT * FROM Singers
ORDER BY SingerId;

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

SELECT * FROM Singers;

Cloud Spanner no garantiza que los resultados de esta consulta estén en el orden de la clave primaria. 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.

Usa STARTS_WITH en lugar de LIKE para acelerar las consultas de SQL con parámetros

Dado que Cloud Spanner no evalúa los patrones LIKE con parámetros hasta el momento de la ejecución, Cloud Spanner debe leer todas las filas y evaluarlas con la expresión LIKE para filtrar las filas que no coinciden.

Cuando un patrón LIKE busca coincidencias que están al principio de un valor y la columna está indexada, usa STARTS_WITH en lugar de LIKE. Esta opción permite que Cloud Spanner optimice de manera más eficaz el plan de ejecución de consultas.

No se recomienda lo siguiente:

SQL estándar de Google

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

PostgreSQL

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

Recomendado:

SQL estándar de Google

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

PostgreSQL

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

Usa marcas de tiempo de confirmación para consultar datos recientes

Si tu aplicación necesita consultar datos escritos después de un momento determinado, agrega columnas de marcas de tiempo de confirmación a las tablas relevantes. Las marcas de tiempo de confirmación habilitan una optimización de Spanner que puede reducir la E/S de las consultas cuyas cláusulas WHERE restringen los resultados a las filas escritas más recientemente que un tiempo específico.

Obtén más información sobre esta optimización con las bases de datos de dialecto SQL estándar de Google o con bases de datos de dialecto de PostgreSQL.