Prácticas recomendadas sobre SQL

Como se describe en la página sobre los planes de ejecución de consultas, el compilador de SQL de Cloud Spanner 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.

En las instrucciones de SQL de ejemplo que se muestran en esta página, se usa el esquema de muestra siguiente:

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) 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.

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 la del ejemplo siguiente para recuperar todos los álbumes titulados “Love” que se lanzaron en 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, debes usar 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 escrita de nuevo reemplaza Love por un parámetro llamado title:

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

Notas sobre el uso de los parámetros de consulta:

  • 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.
  • 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.
  • Especifica el parámetro de consulta y el valor para vincularlo en el campo params de la solicitud a la API ExecuteSQL o ExecuteStreamingSQL.
  • Obtén más información sobre la sintaxis del parámetro de consulta en Estructura léxica y sintaxis.

En resumen, estas son las maneras en las que la ejecución de consultas se beneficia de los parámetros de consulta:

  • 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 el escape de valores de string cuando se proporcionen 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 por 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 también quieres comprender cómo Cloud Spanner obtiene los resultados, debes usar 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 Cloud Console o de las bibliotecas cliente.

Para obtener un plan de consulta con Cloud Console, sigue estos pasos:

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

    Ir a Instancias de Cloud Spanner

  2. Haz clic sobre el nombre de la instancia y la base de datos de Cloud Spanner que deseas consultar.

  3. Haz clic en Consulta.

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

  5. Haz clic en Explicación (Explanation).
    Cloud Console muestra un plan de ejecución visual para tu consulta:

    Captura de pantalla de la consola de Explicación (Explanation) en la IU

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 quieres recuperar los ID de todos los cantantes con un apellido en particular. 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, y es una forma costosa de obtener los resultados si la tabla solo contiene 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 este caso, es probable que Cloud Spanner use de manera automática el índice secundario SingersByLastName cuando ejecute la consulta. Sin embargo, es mejor establecer de forma explícita que Cloud Spanner use ese índice mediante la especificación de una directiva de índice en la cláusula FROM:

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:

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 su lugar, 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 en ese subconjunto de filas.

Si deseas evitar que Cloud Spanner tenga que recuperar filas de la tabla base, de forma opcional, puedes almacenar una copia de la columna FirstName en el índice:

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

Usar una cláusula STORING como esta tiene un costo adicional de almacenamiento, pero proporciona las ventajas siguientes 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 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.

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 quieres recuperar todos los títulos de los álbumes y sus fechas de lanzamiento para luego mostrarlos en orden ascendente según la fecha de lanzamiento y en orden descendente por título. Puedes escribir una consulta de SQL como la que se muestra a continuación:

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:

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

Ten en cuenta que esta definición de índice y consulta cumple con los criterios siguientes:

  • 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 usadas en la consulta.

Debido a que se cumplen ambas condiciones, el plan de consulta resultante quita el paso de clasificación y se ejecuta más rápido.

Aunque los índices secundarios pueden acelerar las consultas comunes, ten en cuenta que agregar índices secundarios puede agregar latencia a tus operaciones de confirmación, ya que cada índice secundario suele requerir el involucramiento 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. También debes comparar tu carga de trabajo para asegurarte de que su rendimiento sea el esperado.

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 la consulta de SQL es leer varias filas de Cloud Spanner según una lista de claves conocidas.

Estas son las prácticas recomendadas para 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:

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

    Notas:

    • El operador de arreglo UNNEST compacta un arreglo de entrada en filas de elementos.

    • @KeyList es un parámetro de consulta, que 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 el límite inferior y el límite superior del rango de clave en la cláusula WHERE.

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

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

    En el ejemplo anterior, @min y @max son parámetros de consulta que están vinculados a los valores 1 y 5, respectivamente.

    Ten en cuenta que 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 debes especificar los límites inferior y superior como en la consulta anterior porque la consulta resultante analizará todos los valores entre 1 y 1,000.

Escribe consultas eficientes para uniones

Las operaciones de unión pueden ser costosas. Esto se debe a que las operaciones de tipo JOIN pueden aumentar la cantidad de filas que tu consulta necesita analizar de forma significativa, lo que genera 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 lograr una operación JOIN más eficiente cuando se usa SQL en 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 operaciones JOIN se pueden completar de forma local sin enviar muchos datos a través de la red.

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

    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=TRUE} 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, puedes usar esta directiva de unión si observas en el plan de consulta que Cloud Spanner cambió el orden de unión y generó resultados no deseados, como oportunidades perdidas para buscar filas o resultados intermedios más grandes.

  • Usa una directiva de unión para elegir una implementación de unión. Elegir el algoritmo de unión correcto para tu consulta 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 para elegir un HASH JOIN:

    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 tienes una cláusula WHERE que es muy selectiva a un lado de tu JOIN, coloca la tabla que produzca el menor número de filas como la tabla primera en la cláusula FROM de la unión. Esto se debe a que, en este momento, 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 selecciona la tabla del lado izquierdo como externa y la del lado derecho como interior. Consulta más información sobre estos tipos de uniones en las secciones sobre la unión de hash y la unión de aplicación.

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 (obtén más información acerca del bloqueo en la página sobre ciclos de vida de operaciones de lectura y escritura).

Debido a la forma en la que funciona el bloqueo en Cloud Spanner, realizar una consulta de lectura o de SQL que lea una gran cantidad de filas (por ejemplo, SELECT * FROM Singers) significa que ninguna otra transacción puede escribir en las filas leídas hasta que tu transacción se confirme o se anule. 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 el problema y reduce la capacidad de procesamiento del sistema.

Por lo tanto, debes intentar evitar las lecturas extensas (por ejemplo, los análisis completos de tablas o las operaciones de unión masivas) dentro de tus transacciones, a menos que estés dispuesto a aceptar una capacidad de procesamiento de escritura menor. En algunos casos, el patrón siguiente puede producir mejores resultados:

  1. Haz una lectura grande dentro de una transacción de solo lectura. (Ten en cuenta que las transacciones de solo lectura no usan bloqueos y, por lo tanto, permiten una capacidad de procesamiento de agregación mayor).
  2. Si necesitas procesar los datos que acabas de leer, hazlo (opcional).
  3. Inicia una transacción de lectura y escritura.
  4. Verifica que los valores de las filas críticas que te interesan no hayan cambiado desde el momento en el que realizaste la transacción de solo lectura en el paso 1.
    1. Si las filas cambiaron, revierte la transacción y comienza de nuevo en el paso 1.
    2. Si todo sale bien, confirma tus mutaciones.

Una forma de evitar las lecturas grandes dentro de las transacciones de lectura y escritura es observar los planes de ejecución que generaron tus consultas.

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

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

SELECT * FROM Singers
ORDER BY SingerId;

Ten en cuenta que Cloud Spanner solo garantiza el orden de los resultados 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 podría 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.

En los casos en los que un patrón LIKE busque coincidencias que estén al principio de un valor y la columna esté indexada, usa STARTS_WITH en lugar de LIKE. Esto permite que Cloud Spanner optimice de manera más eficiente el plan de ejecución de consultas.

No se recomienda lo siguiente:

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

Se recomienda lo siguiente:

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