SQL cele mai bune practici

decembrie 26, 2021Articles

așa cum este descris în planurile de execuție interogare, Cloud Spanner ‘ SSql compiler transformă o declarație SQL într-un plan de execuție interogare, care este folosit pentru a obține rezultatele interogării. Această pagină descrie cele mai bune practici pentruconstruirea instrucțiunilor SQL pentru a ajuta Cloud Spanner să găsească planuri de execuție eficiente.

instrucțiunile SQL de exemplu prezentate în această pagină utilizează schema eșantion de mai jos:

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;

pentru referința SQL completă, consultați sintaxa,funcțiile și operatorii,precum și structura și sintaxa lexicală.

utilizați parametrii de interogare pentru a accelera interogările executate frecvent

interogările parametrizate sunt o tehnică de execuție a interogării care separă un querystring de valorile parametrilor de interogare. De exemplu, să presupunem că cererea dvs. are nevoiePentru a prelua cântăreți care au lansat albume cu anumite titluri într-un anumit an. S-ar putea scrie o declarație SQL ca următorul exemplu pentru a retrieveall de albume intitulat „Dragoste”, care au fost lansate în 2017:

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

într-o altă interogare, puteți schimba valoarea titlului albumului în „pace”:

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

dacă aplicația dvs. trebuie să execute multe interogări similare cu aceasta,în care numai o valoare literală se modifică în interogările ulterioare, ar trebui să utilizați substituentul aparameter pentru acea valoare. Interogarea parametrică rezultată poate ficached și reutilizat, ceea ce reduce costurile de compilare.

de exemplu, interogarea rescrisă de mai jos înlocuiește Love cu un parametru numittitle:

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

note despre utilizarea parametrilor de interogare:

o referință parametru în interogare utilizează caracterul @ urmat de numele theparameter, care poate conține orice combinație de Litere, Numere, andunderscores.
parametrii pot apărea oriunde se așteaptă o valoare literală.
același nume de parametru poate fi utilizat de mai multe ori într-un singur SQLstatement.
specificați parametrul de interogare și valoarea la care să îl legați în câmpul paramsal API-ului de solicitareExecuteSQL sau ExecuteStreamingSQL.
Aflați mai multe despre parametrul de interogare syntaxinSQL structura lexicală și sintaxa.

în rezumat, parametrii de interogare beneficiază de executarea interogării în următoarele moduri:

planuri pre-optimizate: interogările care utilizează parametrii pot fi executate mai rapid invocare oneach, deoarece parametrizarea face mai ușor forCloud Spanner să cache planul de execuție.
compoziție simplificată a interogării: nu trebuie să scăpați de valorile șirului atunci când le furnizați în parametrii de interogare. Parametrii de interogare reduc, de asemenea, risculerori de sintaxă.
securitate: parametrii de interogare fac interogările mai sigure protejându-vă de diferite atacuri de injecție SQL. Această protecție este deosebit de importantăpentru interogările pe care le construiți din introducerea utilizatorului.

înțelegeți cum execută Cloud Spanner interogări

Cloud Spanner vă permite să interogați baze de date folosind declarații SQL declarativecare specifică ce date doriți să preluați. Dacă doriți să înțelegeți și cumcloud Spanner obține rezultatele, ar trebui să utilizați planuri de execuție a interogărilor. Planul de execuție Aquery afișează costul de calcul asociat cu fiecare pas al interogării. Folosind aceste costuri, puteți depana problemele de performanță a interogării șioptimizați interogarea.

puteți prelua planurile de execuție a interogărilor prin consola Cloud saubibliotecile client.

pentru a obține un plan de interogare utilizând Consola Cloud:

deschideți pagina instanțe Cloud Spanner.

mergeți la instanțele de chei Cloud
Faceți clic pe numele instanței de cheie Cloud și al bazei de date pe care doriți să o interogați.
Faceți Clic Pe Interogare.
tastați interogarea în câmpul text, apoi faceți clic pe executare interogare.
Faceți Clic Pe Explicație.
consola Cloud afișează un plan visualexecution pentru interogarea dvs.

pentru mai multe informații despre planurile vizuale, consultați Reglarea unei interogări utilizând vizualizatorul planului de interogare.

pentru referința completă a planului de interogare, consultați planurile de execuție toQuery.

utilizați indici secundari pentru a accelera interogările comune

ca și alte baze de date relaționale, Cloud Spanner oferă indexuri secundare, pe care le puteți utiliza pentru a prelua date utilizând fie o instrucțiune SQL, fie utilizândinterfața de citire a cloud Spanner. Cea mai obișnuită modalitate de a prelua date din anindex este utilizarea interfeței de interogare SQL. Utilizarea unui index secundar într-o interogare SQL vă permite să specificați modul în care doriți ca Cloud Spanner să obțină rezultatele.Specificarea unui index secundar poate accelera executarea interogării.

de exemplu, să presupunem că doriți să aduceți ID-urile tuturor cântăreților cu nume de familie aparticular. O modalitate de a scrie o astfel de interogare SQL este:

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

această interogare ar returna rezultatele pe care le așteptați, dar ar putea dura ceva timp pentru a returna rezultatele. Momentul ar depinde de numărul de rânduriîn tabelul Singersși câte satisfac predicatulWHERE s.LastName = 'Smith'. Dacă nu există un index secundar care să conțină coloana LastName din care să se citească, planul de interogare ar citi tabelul Singers pentru a găsi rânduri care se potrivesc cu predicatul. Citirea entiretable se numește scanare completă a tabelului, iar o Scanare completă a tabelului este o modalitate costisitoare de a obține rezultatele dacă tabelul conține doar un procent mic deSingers cu acel nume de familie.

puteți îmbunătăți performanța acestei interogări definind un index secundar pe coloana nume de familie:

CREATE INDEX SingersByLastName on Singers (LastName);

deoarece indicele secundar SingersByLastName conține tablecolumn indexate LastName și coloana cheie primară SingerId, nor cheie canfetch toate datele din tabelul index mult mai mici în loc de scanare thefull Singers tabel.

în acest scenariu, Cloud Spanner ar folosi probabil automat secondaryindex SingersByLastName la executarea interogării. Cu toate acestea, cel mai bine este să spuneți Explicit Cloud Spanner să utilizeze acel index specificând o directivă index în clauza FROM :

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

acum, să presupunem că ați vrut, de asemenea, să aduceți prenumele cântăreței în plus față de theID. Chiar dacă coloana FirstName nu este conținută în index, ar trebui să specificați în continuare Directiva index ca înainte:

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

încă obțineți un beneficiu de performanță din utilizarea indexului, deoarece Cloud Spannernu trebuie să faceți o Scanare completă a tabelului atunci când executați planul de interogare. În schimb, itselectează subsetul de rânduri care satisfac predicatul din indexul SingersByLastName , apoi face o căutare din tabelul de bază Singers pentru a prelua primul numepentru numai acel subset de rânduri.

dacă doriți să evitați Cloud Spanner de a avea să-i aducă orice rânduri din tabelul de bază, la toate, puteți stoca opțional o copie a coloanei FirstName înindex în sine:

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

utilizarea unei clauze STORING ca aceasta costă spațiu de stocare suplimentar, dar oferă următoarele avantaje pentru interogări și apeluri citite folosind indexul:

interogările SQL care utilizează coloanele index și selectați stocate în clauza STORING nu necesită o asociere suplimentară la tabelul de bază.
apelurile citite care utilizează indexul pot citi coloanele stocate în clauza STORING.

exemplele precedente ilustrează modul în care indicii secundari pot accelera interogările atunci când rândurile alese de clauza WHERE a unei interogări pot fi identificate rapid folosind indexul secundar. Un alt scenariu în care indicii secundari pot oferibeneficiile de performanță sunt pentru anumite interogări care returnează rezultatele ordonate. De exemplu, să presupunem că doriți să preluați toate titlurile albumelor și datele lor de lansareși să le returnați în ordine crescătoare a datei de lansare și în ordine descrescătoare după albumtitlul. Ai putea scrie o interogare SQL ca aceasta:

SELECT a.AlbumTitle, a.ReleaseDateFROM Albums AS aORDER BY a.ReleaseDate, a.AlbumTitle DESC;

fără un index secundar, această interogare necesită o sortare potențial costisitoare în planul de execuție. Ai putea accelera executarea interogare prin definirea thissecondary index:

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

apoi rescrieți interogarea pentru a utiliza indexul secundar:

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

rețineți că această interogare și definiția indexului îndeplinesc ambele criterii următoare:

lista de coloane din clauza ORDER BY este un prefix al listei de chei index.
toate coloanele din tabelul utilizat în interogare sunt acoperite de index.

deoarece ambele condiții sunt îndeplinite, planul de interogare rezultat elimină etapa de sortare și se execută mai repede.

în timp ce indexurile secundare pot accelera interogările obișnuite, rețineți că indexurile addingsecondary pot adăuga latență operațiunilor de comitere, deoarece fiecare index secundar necesită de obicei implicarea unui nod suplimentar în fiecare comitere. Pentrucele mai multe sarcini de lucru, având câțiva indici secundari este bine. Cu toate acestea, ar trebuiia în considerare dacă vă pasă mai mult de latența de citire sau scriere și luați în considerare care sunt cele mai critice operațiuni pentru volumul dvs. de muncă. De asemenea, ar trebui să vă comparați sarcina de lucru pentru a vă asigura că funcționează așa cum vă așteptați.

pentru referința completă privind indicii secundari, referto indicii secundari.

scrieți interogări eficiente pentru căutarea tastelor de gamă

o utilizare obișnuită a interogării SQL este de a citi mai multe rânduri din cheia Cloud bazată pe o listă de chei cunoscute.

acestea sunt cele mai bune practici pentru scrierea de interogări eficiente la preluarea datelor printr-o gamă de chei:

dacă lista de taste este redusă și nu este adiacentă, utilizați parametrii de interogare șiUNNEST pentru a construi interogarea.

de exemplu, dacă lista de chei este {1, 5, 1000}, scrieți interogarea astfel:
```
SELECT *FROM Table AS tWHERE t.Key IN UNNEST (@KeyList)
```
Note:
- operatorul array UNNEST aplatizează aninput matrice în rânduri de elemente.
- @KeyList este un parametru de interogare, care poate accelera interogarea ca discutatîn cele mai bune practici precedente.
dacă lista de taste este adiacentă și într-un interval, specificați limita inferioară și limita superioară a intervalului de taste în clauza WHERE.

de exemplu, dacă lista dvs. de chei este {1,2,3,4,5}, construiți interogarea likethis:
```
SELECT *FROM Table AS tWHERE t.Key BETWEEN @min AND @max
```
unde @min și @max sunt parametri de interogare care sunt legați la valorile 1 și respectiv 5.

rețineți că această interogare este mai eficientă numai dacă tastele din intervalul de taste sunt adiacente. Cu alte cuvinte, dacă lista dvs. de chei este {1, 5, 1000}, nu ar trebui să specificați limitele inferioare și superioare ca în interogarea precedentă, deoarece interogarea de rezultat ar scana prin fiecare valoare între 1 și 1000.

scrie interogări eficiente pentru se alătură

se alăture operațiuni poate fi costisitoare. Acest lucru se datorează faptului că JOINs poate crește semnificativ numărul de rânduri pe care interogarea dvs. trebuie să le scaneze, ceea ce duce la slowerqueries. În plus față de tehnicile pe care sunteți obișnuiți să le utilizați în alte baze de date naționale pentru a optimiza interogările de asociere, iată câteva bune practici pentru o asociere mai eficientă atunci când utilizați Cloud Spanner SQL:

dacă este posibil, se alăture date în tabele intercalate de cheie primară. De exemplu:
```
SELECT s.FirstName, a.ReleaseDateFROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
rândurile din tabelul intercalat Albums sunt garantate a fi stocate fizic în aceleași împărțiri ca rândul părinte din Singers, după cum se discută în Schema și modelul de date. Prin urmare, JOINs poate fi finalizat local fără a trimite o mulțime de date în rețea.
utilizați Directiva join dacă doriți să forțați ordinea JOIN. De exemplu:
```
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%';
```
Directiva join @{FORCE_JOIN_ORDER=TRUE} îi spune Cloud Spanner să utilizeze ordinea join specificată în interogare (adică Singers JOIN Albums, nuAlbums JOIN Singers). Rezultatele returnate sunt aceleași, indiferent de theorder că Cloud Spanner alege. Cu toate acestea, este posibil să doriți să utilizați acest joindirective dacă observați în planul de interogare că Cloud Spanner a schimbat ordinea de înscriere și a provocat rezultate nedorite, cum ar fi rezultate intermediare mai mari sau a ratat oportunități de căutare a rândurilor.
utilizați o directivă join pentru a alege o implementare join. Alegerea joinalgoritmului potrivit pentru interogarea dvs. poate îmbunătăți latența, consumul de memorie, orboth. Această interogare demonstrează sintaxa pentru directiva usinga JOIN cu JOIN_METHOD indiciu pentru a alege un HASH JOIN:
```
SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId
```
dacă utilizați o HASH JOIN sau APPLY JOINși dacă aveți o clauză WHERE care este foarte selectivă pe o parte a JOIN, puneți tabelul care produce cel mai mic număr de rânduri ca primul tabel din clauza FROM din join. Acest lucru se datorează faptului că, în prezent, în HASH JOIN, Cloud Spanner alwayspicks tabelul din partea stângă ca build și tabelul din partea dreaptă asprobe. În mod similar, pentru APPLY JOIN, Cloud Spanner alege partea stângă asouter și masa din partea dreaptă ca interior. Vedeți mai multe informații despre thesejoin types: Hash join și Apply join.

evitați citirile mari în interiorul tranzacțiilor de citire-scriere

tranzacțiile de citire-scriere permit o secvență de zero sau morereads sau interogări SQL și pot include un set de mutații, înainte de un apel tocommit. Pentru a menține coerența datelor dvs., cloud Spannerachiziționează încuietori la citirea și scrierea rândurilor în tabelele și indexurile dvs. (citiți mai multe detalii despre blocarea în viața citirilor și scrierilor).

din cauza modului în care funcționează blocarea în cloud Spanner, efectuarea unei citiri sau a unui sqlquery care citește un număr mare de rânduri (de exemplu SELECT * FROM Singers)înseamnă că nicio altă tranzacție nu poate scrie pe rândurile pe care le-ați citit până când tranzacția dvs. nu este angajată sau anulată. În plus, deoarece dvs. de transfer este de procesare un număr mare de rânduri, este probabil să dureze mai mult decât o tranzacție care citește o gamă mult mai mică de rânduri (de exemplu SELECTLastName FROM Singers WHERE SingerId = 7), care exacerbează și mai mult problemași reduce tranzitată de sistem.

prin urmare, ar trebui să încercați să evitați citirile mari (de exemplu: scanează tabelul complet saumasive join operations) în interiorul tranzacțiilor dvs., cu excepția cazului în care sunteți dispus să acceptați un debit de scriere mai mic. În unele cazuri, următorul model poate producerezultate mai bune:

faceți citirea mare în interiorul unei tranzacții numai în citire. (Notăcă tranzacțiile numai în citire nu utilizează încuietori și, prin urmare, permit un debit mai mare.)
dacă trebuie să faceți orice prelucrare a datelor pe care tocmai le-ați citit, faceți-o.
începeți o tranzacție de citire-scriere.
verificați dacă rândurile critice care vă interesează nu au schimbat valorile de la momentul în care ați efectuat tranzacția numai în citire la Pasul 1.
1. dacă rândurile s-au schimbat, reveniți la tranzacție și începeți din nou lapasul 1.
2. dacă totul pare în regulă, fă-ți mutațiile.

o modalitate de a vă asigura că evitați citirile mari în interiorul read-writetranzactions este să vă uitați la planurile de execuție generate de yourqueries.

utilizați ORDER BY pentru a asigura ordonarea rezultatelor SQL

dacă așteptați o anumită comandă pentru rezultatele unei interogări SELECT, ar trebui să includeți în mod explicit clauza ORDER BY. De exemplu: dacă doriți să listați toți cântăreții în ordinea cheii primare, utilizați această interogare:

SELECT * FROM SingersORDER BY SingerId;

rețineți că Cloud Spanner garantează doar ordonarea rezultatelor dacă clauza ORDER BY este prezentă în interogare. Cu alte cuvinte, luați în considerare această interogare fără ORDERBY:

SELECT * FROM Singers;

Cloud Spanner nu garantează că rezultatele acestei interogări vor fi înordinea cheie primară. În plus, ordonarea rezultatelor s-ar putea schimba în orice moment și nu este garantată a fi consecventă de la invocare la invocare.

utilizați STARTS_WITH în loc de LIKE pentru a accelera interogările SQL parametrizate

deoarece Cloud Spanner nu evaluează modelele parametrizateLIKE până la timpul de execuție, Cloud Spanner trebuie să citească toate rândurile și să le evalueze în raport cu expresia LIKE pentru a filtra rândurile care nu se potrivesc.

în cazurile în care un model LIKE caută potriviri care sunt la începutul avalue și coloana este indexată, utilizați STARTS_WITHîn loc de LIKE. Acestpermite Cloud Spanner să optimizeze mai eficient planul de execuție a interogării.

nerecomandat:

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

recomandat:

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