Prácticas recomendadas de SQL

diciembre 26, 2021Articles

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

Las instrucciones SQL de ejemplo que se muestran en esta página utilizan el esquema de ejemplo que se muestra a continuación:

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 SQL completa, consulte sintaxis de declaración, Funciones y operadores, y estructura y sintaxis léxicas.

Use parámetros de consulta para acelerar las consultas ejecutadas con frecuencia

Las consultas parametrizadas son una técnica de ejecución de consultas que separa una cadena de consultas de los valores de los parámetros de consulta. Por ejemplo, supongamos que su aplicación necesita recuperar cantantes que han lanzado álbumes con ciertos títulos en un año determinado. Puede escribir una instrucción SQL como el siguiente ejemplo para recuperar todos los álbumes titulados «Love» que se lanzaron en 2017:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

En otra consulta, puede cambiar el valor del título del álbum a»Paz»:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Si su aplicación necesita ejecutar muchas consultas similares a esta, en las que solo cambia un valor literal en consultas posteriores, debe usar el marcador de posición aparameter para ese valor. La consulta paramétrica resultante se puede almacenar y reutilizar, lo que reduce los costes de compilación.

Por ejemplo, la consulta reescrita a continuación reemplaza Love con un parámetro llamadotitle:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Notas sobre el uso de parámetros de consulta:

Una referencia de parámetro en la consulta utiliza el carácter @ seguido del nombre del parámetro, que puede contener cualquier combinación de letras, números y puntuaciones inferiores.
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 declaración SQL.
Especifique el parámetro de consulta y el valor al que se vinculará en el campo paramsde la API de solicitud ExecuteSQL oExecuteStreamingSQL.
Obtenga más información sobre la estructura y Sintaxis léxicas de parámetros de consulta syntaxinSQL.

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

Planes pre-optimizados: Las consultas que usan parámetros se pueden ejecutar más rápido invocación de una sola vez porque la parametrización facilita que forCloud Spanner almacene en caché el plan de ejecución.
Composición de consulta simplificada: no es necesario escapar de los valores de cadena al proporcionarlos 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 sus consultas sean más seguras al protegerlo de varios ataques de inyección SQL. Esta protección es especialmente importante para las consultas que se construyen a partir de la entrada del usuario.

Comprenda cómo Cloud Spanner ejecuta consultas

Cloud Spanner le permite consultar bases de datos mediante estados SQL declarativos que especifican qué datos desea recuperar. Si también desea comprender cómo obtiene los resultados Cloud Spanner, debe usar planes de ejecución de consultas. Un plan de ejecución muestra el coste computacional asociado a cada paso de la consulta. Utilizando esos costos, puede depurar los problemas de rendimiento de las consultas y optimizar su consulta.

Puede recuperar planes de ejecución de consultas a través de la consola en la nube o las bibliotecas de clientes.

Para obtener un plan de consultas mediante la consola en la nube:

Abra la página Instancias de Cloud Spanner.

Ir a instancias de Cloud Spanner
Haga clic en el nombre de la instancia de Cloud Spanner y la base de datos que desea consultar.
Haz clic en Consulta.
Escriba la consulta en el campo de texto y, a continuación, haga clic en Ejecutar consulta.
Haga clic en Explicación.
La consola en la nube muestra un plan de ejecución visual para su consulta.

Para obtener más información sobre los planos visuales, consulte Ajuste de una consulta mediante el visualizador de plan de consultas.

Para la referencia completa del plan de consulta, consulte Planes de ejecución de la consulta.

Use índices secundarios para acelerar consultas comunes

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

Por ejemplo, supongamos que desea obtener los identificadores de todos los cantantes con un apellido particular. Una forma de escribir una consulta SQL es:

SELECT s.SingerIdFROM Singers AS sWHERE s.LastName = 'Smith';

Esta consulta devolvería los resultados que esperas, pero podría llevar mucho tiempo devolverlos. El tiempo dependería del número de filas en la tabla Singers y cuántas satisfacen el predicadoWHERE s.LastName = 'Smith'. Si no hay un índice secundario que contenga la columna LastName para leer, el plan de consulta leería la tabla entire Singers para encontrar filas que coincidan con el predicado. La lectura del entiretable se denomina exploración de tabla completa, y una exploración de tabla completa es una vía costosa para obtener los resultados si la tabla contiene solo un pequeño porcentaje deSingers con ese apellido.

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

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, Cloud Spanner puede obtener todos los datos de la tabla de índice mucho más pequeña en lugar de escanear la tabla completa Singers.

En este escenario, Cloud Spanner probablemente usaría automáticamente el índice secundario SingersByLastName al ejecutar la consulta. Sin embargo, lo mejor es indicar explícitamente a Cloud Spanner que use ese índice especificando una directiva de índice en la cláusula FROM :

SELECT s.SingerIdFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Ahora supongamos que también quisieras buscar el nombre de pila del cantante además de theID. Aunque la columna FirstName no esté contenida en el índice, debe especificar la directiva index como antes:

SELECT s.SingerId, s.FirstNameFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Aún así, obtiene un beneficio de rendimiento al usar el índice porque Cloud Spanner no necesita realizar un análisis completo de la tabla al ejecutar el plan de consultas. En su lugar, selecciona el subconjunto de filas que satisfacen el predicado del índice SingersByLastName, luego hace una búsqueda desde la tabla base Singers para obtener el primer nombre solo para ese subconjunto de filas.

Si desea evitar que Cloud Spanner tenga que obtener filas de la tabla base, opcionalmente puede almacenar una copia de la columna FirstName en el índice mismo:

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

Usar una cláusula STORING como esta cuesta almacenamiento adicional, pero proporciona las siguientes ventajas para consultas y llamadas de lectura utilizando el índice:

Las consultas SQL que utilizan las columnas index y select almacenadas en la cláusula STORINGno requieren una unión adicional a la tabla base.
Las llamadas de lectura que utilizan el índice pueden leer columnas almacenadas en la cláusula STORING.

Los ejemplos anteriores ilustran 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 utilizando el índice secundario. Otro escenario en el que los índices secundarios pueden ofrecer beneficios de rendimiento es para ciertas consultas que devuelven resultados ordenados. Por ejemplo, supongamos que desea recuperar todos los títulos de los álbumes y sus fechas de lanzamiento y devolverlos en orden ascendente de la fecha de lanzamiento y orden descendente por título del álbum. Podrías escribir una consulta SQL como esta:

SELECT a.AlbumTitle, a.ReleaseDateFROM Albums AS aORDER 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. Puede acelerar la ejecución de la consulta definiendo este índice secundario:

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

A continuación, reescriba la consulta para utilizar el índice secundario:

SELECT a.AlbumTitle, a.ReleaseDateFROM [email protected]{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Tenga en cuenta que esta consulta y la definición de índice cumplen los dos criterios siguientes:

La lista de columnas de la cláusula ORDER BY es un prefijo de la lista de claves de índice.
Todas las columnas de la tabla utilizadas en la consulta están cubiertas por el índice.

Dado que se cumplen ambas condiciones, el plan de consulta resultante elimina el paso de ordenación y se ejecuta más rápido.

Mientras que los índices secundarios pueden acelerar consultas comunes, tenga en cuenta que agregar índices secundarios puede agregar latencia a sus operaciones de confirmación, porque cada índice secundario generalmente requiere la participación de un nodo adicional en cada confirmación. Para la mayoría de las cargas de trabajo, tener unos pocos índices secundarios está bien. Sin embargo, debe considerar si le importa más la latencia de lectura o escritura, y considerar qué operaciones son más críticas para su carga de trabajo. También debe comparar su carga de trabajo para asegurarse de que funciona como espera.

Para la referencia completa de índices secundarios, consulte Índices secundarios.

Escribir consultas eficientes para la búsqueda de claves de rango

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

Estas son las mejores prácticas para escribir consultas eficientes al obtener datos mediante un rango de teclas:

Si la lista de claves es escasa y no adyacente, utilice parámetros de consulta yUNNEST para construir su consulta.

Por ejemplo, si su lista de claves es {1, 5, 1000}, escriba la consulta de la siguiente manera:
```
SELECT *FROM Table AS tWHERE t.Key IN UNNEST (@KeyList)
```
Notas:
- El operador array UNNEST aplana un array input en filas de elementos.
- @KeyList es un parámetro de consulta, que puede acelerar su consulta como se discutió en las mejores prácticas anteriores.
Si la lista de claves está adyacente y dentro de un rango, especifique el límite inferior y el límite superior del rango de claves en la cláusula WHERE.

Por ejemplo, si su lista de claves es {1,2,3,4,5}, construya la consulta como esta:
```
SELECT *FROM Table AS tWHERE t.Key BETWEEN @min AND @max
```
Donde @min y @max son parámetros de consulta vinculados a los valores 1 y 5, respectivamente.

Tenga en cuenta que esta consulta solo es más eficiente si las claves del rango de claves son adyacentes. En otras palabras, si su lista de claves es {1, 5, 1000}, no debe especificar los límites inferior y superior como en la consulta anterior, ya que la consulta de consulta de salida escanearía cada valor entre 1 y 1000.

Escribir consultas eficientes para uniones

Las operaciones de unión pueden ser costosas. Esto se debe a que JOINs puede aumentar significativamente el número de filas que su consulta necesita escanear, lo que da lugar a consultas más lentas. Además de las técnicas a las que está acostumbrado a usar en otras bases de datos relacionales para optimizar las consultas de unión, aquí hay algunas prácticas recomendadas para una UNIÓN más eficiente al usar SQL de llave en la nube:

Si es posible, unir datos en tablas intercaladas por clave principal. Por ejemplo:
```
SELECT s.FirstName, a.ReleaseDateFROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
Se garantiza que las filas de la tabla intercalada Albums estén almacenadas físicamente en las mismas divisiones que la fila principal en Singers, tal y como se discute en el Esquema y el modelo de datos. Por lo tanto, JOINs puede completarse localmente sin enviar muchos datos a través de la red.
Utilice la directiva join si desea forzar el orden de JOIN. Por ejemplo:
```
SELECT *FROM Singers AS s [email protected]{FORCE_JOIN_ORDER=TRUE} Albums AS aON s.SingerId = a.SingeridWHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
```
La directiva join @{FORCE_JOIN_ORDER=TRUE} indica a Cloud Spanner que utilice el orden de unión especificado en la consulta (es decir, Singers JOIN Albums, noAlbums JOIN Singers). Los resultados devueltos son los mismos, independientemente del orden que elija Cloud Spanner. Sin embargo, es posible que desee usar esta directiva de unión si observa en el plan de consulta que Cloud Spanner ha cambiado el orden de unión y ha causado resultados indeseables, como resultados intermedios más grandes, o ha perdido oportunidades para buscar filas.
Utilice una directiva de unión para elegir una implementación de unión. Elegir el algoritmo de unión adecuado para su consulta puede mejorar la latencia, el consumo de memoria y otros aspectos. Esta consulta muestra la sintaxis para usar una directiva de unión con la sugerencia JOIN_METHODpara elegir un HASH JOIN:
```
SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId
```
Si está utilizando una cláusula HASH JOIN o APPLY JOIN y tiene una cláusula WHERE que es altamente selectiva en un lado de su JOIN, coloque la tabla que produce el menor número de filas como la primera tabla en la cláusula FROM de la combinación. Esto se debe a que actualmente en HASH JOIN, Cloud Spanner siempre selecciona la mesa lateral izquierda como build y la mesa lateral derecha asprobe. Del mismo modo, para APPLY JOIN, Cloud Spanner elige la mesa lateral izquierda y la mesa lateral derecha como interior. Vea más información sobre estos tipos de join: Unión Hash y unión Apply.

Evitar lecturas grandes dentro de transacciones de lectura y escritura

Las transacciones de lectura y escritura permiten una secuencia de cero o más cabezas o consultas SQL, y pueden incluir un conjunto de mutaciones, antes de una llamada a comprometerse. Con el fin de mantener la consistencia de sus datos, Cloud Spanner adquiere bloqueos al leer y escribir filas en sus tablas e índices (más detalles sobre el bloqueo en la vida útil de las lecturas y escrituras).

Debido a la forma en que funciona el bloqueo en Cloud Spanner, realizar una lectura o SqlQuery que lee un gran número de filas (por ejemplo SELECT * FROM Singers)significa que ninguna otra transacción puede escribir en las filas que ha leído hasta que su transacción se confirme o se cancele. Además, debido a que su transacción procesa un gran número de filas, es probable que tome más tiempo que una transacción que lee un rango mucho más pequeño de filas (por ejemplo, SELECTLastName FROM Singers WHERE SingerId = 7), lo que exacerba aún más el problema y reduce el rendimiento del sistema.

Por lo tanto, debe tratar de evitar lecturas grandes (por ejemplo: análisis de tablas completas o operaciones de unión masivas) dentro de sus transacciones, a menos que esté dispuesto a aceptar un menor rendimiento de escritura. En algunos casos, el siguiente patrón puede producir mejores resultados:

Haga su lectura grande dentro de una transacción de solo lectura. (Nótese que las transacciones de solo lectura no utilizan bloqueos y, por lo tanto, permiten un rendimiento de mayor volumen.)
Si necesita hacer algún procesamiento de los datos que acaba de leer, hágalo.
Iniciar una transacción de lectura y escritura.
Verifique que las filas críticas que le interesan no hayan cambiado los valores desde el momento en que realizó la transacción de solo lectura en el paso 1.
1. Si las filas han cambiado, revierta la transacción y comience de nuevo en el paso 1.
2. Si todo se ve bien, confirme sus mutaciones.

Una forma de asegurarse de que está evitando grandes lecturas dentro de las transacciones de lectura-escritura es mirar los planes de ejecución que generan sus consultas.

Use ORDER BY para garantizar el orden de sus resultados SQL

Si espera un orden determinado para los resultados de una consulta SELECT, debe incluir explícitamente la cláusula ORDER BY. Por ejemplo: Si desea incluir a todos los cantantes en orden de clave primaria, utilice esta consulta:

SELECT * FROM SingersORDER BY SingerId;

Tenga 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, considere esta consulta sin la ORDERBY:

SELECT * FROM Singers;

Cloud Spanner no garantiza que los resultados de esta consulta sean de orden de clave inicial. Además, el orden de los resultados podría cambiar en cualquier momento y no se garantiza que sea coherente de invocación a invocación.

Use STARTS_WITH en lugar de LIKE para acelerar las consultas SQL parametrizadas

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

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

No se recomienda:

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

Recomendado:

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