Recomendaciones 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 Spanner a encontrar planes de ejecución eficientes.

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

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 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 búsqueda

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

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 preoptimizados: Las consultas que usan parámetros se pueden ejecutar más rápido en cada invocación, ya que la parametrización facilita que Spanner almacene en caché el plan de ejecución.
• Composición simplificada de la consulta: no necesitas escapar valores de string cuando los proporcionas en parámetros de consulta. Los parámetros de consulta también reducen el riesgo de errores de sintaxis.
• Seguridad: Los parámetros de consulta mejoran la seguridad de tus consultas, 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.

Comprender cómo Spanner ejecuta consultas

Spanner te permite consultar bases de datos mediante instrucciones de SQL declarativas que especifican los datos que deseas recuperar. Si deseas comprender cómo obtiene Spanner los resultados, examina el plan de ejecución de la consulta. 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 mediante Google Cloud Console o las bibliotecas cliente.

Para obtener un plan de consulta con Google Cloud Console, haz lo siguiente:

1. Abre la página de instancias de Spanner.

   Ir a Instancias de Spanner

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

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

4. Escriba la consulta en el campo de texto y, luego, haga clic en Ejecutar consulta.

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

   

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

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

Usa índices secundarios

Al igual que otras bases de datos relacionales, Spanner ofrece índices secundarios, que puedes usar para recuperar datos mediante una instrucción de SQL o la interfaz de lectura de Spanner. La forma más común de recuperar datos de un índice es usar la interfaz de consulta de SQL. El uso de un índice secundario en una consulta de SQL te permite especificar cómo deseas que 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 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);

Debido a que el índice secundario SingersByLastName contiene la columna de tabla indexada LastName y la columna de clave primaria SingerId, 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, Spanner usa el índice secundario SingersByLastName de forma automática cuando ejecuta 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 indicarle de forma explícita a Spanner que use ese índice mediante la especificación de 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 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:

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

El uso del índice te brinda un beneficio de rendimiento, ya que 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 Spanner no tenga que recuperar ninguna fila de la tabla base, puedes almacenar una copia de la columna FirstName en el índice:

GoogleSQL

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

PostgreSQL

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

Usar una cláusula STORING (para el dialecto GoogleSQL) o una cláusula INCLUDE (para el dialecto PostgreSQL), como esta, cuesta almacenamiento adicional, pero proporciona las siguientes ventajas:

• Las consultas de SQL que usan el índice y seleccionan las columnas almacenadas en las cláusulas STORING o INCLUDE no requieren una unión adicional para 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 quieres recuperar todos los títulos de los álbumes y sus fechas de lanzamiento en orden ascendente con respecto a la fecha de lanzamiento 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:

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

• Para quitar el paso de ordenamiento, asegúrate de que la lista de columnas en la cláusula ORDER BY sea un prefijo de la lista de claves del índice.
• A fin de evitar volver a unirse desde la tabla base para recuperar las columnas faltantes, asegúrate de que el índice cubra todas las columnas de la tabla que usa la consulta.

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 como esperas.

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

Optimiza las búsquedas de claves de rango

Un uso común de una consulta de SQL es leer varias filas de Spanner según 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:

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

  • El parámetro de consulta, que es @KeyList para Google SQL 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:

  GoogleSQL

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

  Nota: Aquí, @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: Aquí, $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 rango de claves son adyacentes. En otras palabras, si tu lista de claves es {1, 5, 1000}, no especifiques los límites inferiores y superiores como en la consulta anterior, porque la consulta resultante analizará cada valor entre 1 y 1,000.

Optimice las uniones

Las operaciones de unión pueden ser costosas porque pueden aumentar de forma 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 lograr una JOIN más eficiente cuando se usa Spanner SQL:

• 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 se pueden completar de forma local sin enviar muchos datos a través de la red.

• Usa la directiva de unión si deseas 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 le 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 que se muestran son los mismos, sin importar el orden que elija Spanner. Sin embargo, es posible que desees usar esta directiva de unión si observas en el plan de consulta que Spanner cambió el orden de unión y causó consecuencias no deseadas, 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, Spanner usa de forma automática un método de unión que puede hacer 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 una 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 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 produce la menor cantidad de filas como la primera tabla en la cláusula FROM de la unión. Esta estructura es útil porque, por el momento, en HASH JOIN, Spanner siempre elige la tabla lateral izquierda como compilación y la tabla lateral derecha como sondeo. Del mismo modo, para APPLY JOIN, Spanner selecciona la tabla lateral izquierda como externa y la tabla lateral derecha como interna. Obtén más información sobre estos tipos de unión: Unión de hash y Aplicar unión.

• Para las consultas que son fundamentales en tu carga de trabajo, especifica el método y el orden de unión con mejor rendimiento 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, 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 Ciclo de lectura y escritura.

Debido a la forma en que funciona el bloqueo en 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 que leíste hasta que tu transacción se confirma o anula.

Además, debido a que la 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, evita las lecturas grandes (por ejemplo, análisis completos de tablas 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 patrón siguiente puede producir mejores resultados:

1. Realiza las lecturas grandes dentro de 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 los procesamientos necesarios 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 las 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 explícitamente la cláusula ORDER BY. Por ejemplo, si deseas generar una lista de todos los cantantes en el orden de la clave primaria, 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. En otras palabras, considera esta consulta sin ORDER BY:

SELECT * FROM Singers;

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. Si una consulta tiene una cláusula ORDER BY y Spanner usa un índice que proporciona el orden requerido, Spanner no ordena de forma explícita los datos. Por lo tanto, no te preocupes por el impacto en el rendimiento de incluir esta cláusula. Para verificar si una operación de ordenamiento explícita está incluida en la ejecución, observa el plan de consulta.

Usar STARTS_WITH en lugar de LIKE

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

Cuando un patrón LIKE tenga el formato foo% (por ejemplo, comienza con una string fija y termina con un solo porcentaje de comodín) y la columna se indexa, usa STARTS_WITH en lugar de LIKE. Esta opción permite que Spanner optimice el plan de ejecución de consultas de forma más eficaz.

No se recomienda lo siguiente:

GoogleSQL

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

Nota: En este ejemplo, se da por sentado que @like_clause está vinculado a 'Love%'.

PostgreSQL

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

Nota: En este ejemplo, se da por sentado que $2 está vinculado a 'Love%'.

Recomendado:

GoogleSQL

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

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

PostgreSQL

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

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

Usa marcas de tiempo de confirmación

Si tu aplicación necesita consultar los datos escritos después de un tiempo en particular, 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 diagnóstico de Google SQL o con las bases de datos de dialecto de PostgreSQL.