SQL best practices

a lekérdezés végrehajtási terveiben leírtak szerint a Cloud Spanner SQL fordítója egy SQL utasítást átalakít egy lekérdezés végrehajtási tervévé, amelyet a lekérdezés eredményeinek megszerzésére használnak. Ez az oldal ismerteti a legjobb gyakorlatokat FORCONSTRUCTING SQL utasításokat, hogy segítsen Cloud Spanner megtalálni hatékony executionplans.

az ezen az oldalon bemutatott példa SQL utasítások az alábbi mintasémát használják:

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;

a teljes SQL hivatkozáshoz lásd: kifejezés szintaxisa, függvények és operátorok, valamint lexikai szerkezet és szintaxis.

használja a lekérdezési paramétereket a gyakran végrehajtott lekérdezések felgyorsításához

a paraméterezett lekérdezések a lekérdezés végrehajtásának olyan technikája, amely elválasztja a lekérdezési karakterláncot a lekérdezési paraméterértékektől. Tegyük fel például, hogy az alkalmazásnak szüksége vanolyan énekesek lekérésére, akik egy adott évben bizonyos címekkel rendelkező albumokat adtak ki. Lehet, hogy írjon egy SQL utasítás, mint a következő példa, hogy retrieveall album címe: “Love”, hogy megjelent 2017:

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

egy másik lekérdezésben megváltoztathatja az album címének értékét “béke”értékre:

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

ha az alkalmazásnak sok ehhez hasonló lekérdezést kell végrehajtania,amelyekben csak egy szó szerinti érték változik a következő lekérdezésekben, akkor az értékhez aparameter helyőrzőt kell használnia. A kapott paraméteres lekérdezés lehettárolva és újrafelhasználva, ami csökkenti a fordítási költségeket.

például az alábbi átírt lekérdezés a Love – et egy megnevezett paraméterrel helyettesítititle:

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

Megjegyzések a lekérdezési paraméterek használatáról:

• a lekérdezésben szereplő paraméterhivatkozás a @ karaktert használja, amelyet aparaméter neve követ, amely betűk, számok és alulértékek bármilyen kombinációját tartalmazhatja.
• a paraméterek bárhol megjelenhetnek, ahol szó szerinti érték várható.
• ugyanaz a paraméternév többször is használható egyetlen Sqlstatementben.
• adja meg a lekérdezési paramétert és az ahhoz kötendő értéket a params mezőben a ExecuteSQL vagyExecuteStreamingSQL kérelem API-ban.
• Tudjon meg többet a syntaxinSQL lekérdezési paraméter lexikai szerkezetéről és Szintaxisáról.

összefoglalva, a lekérdezési paraméterek a lekérdezés végrehajtását a következő módon szolgálják:

• előre optimalizált tervek: a paramétereket használó lekérdezések gyorsabban végrehajthatók, mivel a paraméterezés megkönnyíti a forCloud Spanner számára a végrehajtási terv gyorsítótárazását.
• egyszerűsített lekérdezési összetétel: nem kell menekülnie a karakterlánc értékeitől, amikor lekérdezési paraméterekben biztosítja őket. A lekérdezési paraméterek szintén csökkentik aszintax hibák.
• biztonság: a lekérdezési paraméterek biztonságosabbá teszik a lekérdezéseket azáltal, hogy megvédik Önt a különböző SQL injekciós támadásoktól. Ez a védelem különösen fontosa felhasználói bevitelből létrehozott lekérdezésekhez.

annak megértése, hogy a Cloud Spanner hogyan hajtja végre a lekérdezéseket

A Cloud Spanner lehetővé teszi az adatbázisok lekérdezését deklaratív SQL utasításokkalamely meghatározza, hogy milyen adatokat szeretne letölteni. Ha azt is meg akarja érteni, hogyana cloud Spanner megkapja az eredményeket, akkor lekérdezési végrehajtási terveket kell használnia. Az Aquery végrehajtási terv megjeleníti a lekérdezés egyes lépéseihez kapcsolódó számítási költségeket. Ezekkel a költségekkel hibakereshet a lekérdezés teljesítményével kapcsolatos problémákban, és optimalizálhatja a lekérdezést.

lekérdezési végrehajtási terveket a felhő konzolon vagy az ügyfélkönyvtárakon keresztül lehet letölteni.

lekérdezési terv lekérése a Cloud Console használatával:

1. nyissa meg a Cloud Spanner példányok oldalt.

   ugrás a Cloud Spanner példányokhoz

2. kattintson a lekérdezni kívánt Cloud Spanner példány és adatbázis nevére.

3. Kettyenés Lekérdezés.

4. írja be a lekérdezést a szövegmezőbe, majd kattintson a Lekérdezés futtatása parancsra.

5. Kattintson A Magyarázat Gombra.
   a Cloud Console megjelenít egy visualexecution tervet a lekérdezéshez.

   

a vizuális tervekkel kapcsolatos további információkért lásd: lekérdezés hangolása a lekérdezési terv megjelenítőjével.

a teljes lekérdezési terv hivatkozását lásd: lekérdezési végrehajtási tervek.

másodlagos indexek használata a gyakori lekérdezések felgyorsításához

a többi relációs adatbázishoz hasonlóan a Cloud Spanner másodlagos indexeket is kínál, amelyek segítségével adatokat lehet letölteni SQL utasítás vagy a cloud Spanner olvasási felületének használatával. Az anindexből származó adatok lekérésének leggyakoribb módja az SQL lekérdezési felület használata. Egy másodlagos index egy SQL queryenables, hogy adja meg, hogyan szeretné Cloud csavarkulcs az eredmények eléréséhez.A másodlagos index megadása felgyorsíthatja a lekérdezés végrehajtását.

tegyük fel például, hogy az összes énekes azonos nevű azonosítóját szeretné letölteni. Az ilyen SQL lekérdezés írásának egyik módja:

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

ez a lekérdezés a várt eredményeket adná vissza, de időbe telhet az eredmények visszaadása. Az időzítés a Singers táblázatban szereplő sorok számától függ, és attól, hogy hányan felelnek meg aWHERE s.LastName = 'Smith'predikátumnak. Ha nincs olyan másodlagos index, amely a LastName oszlopot tartalmazza, amelyből olvasni kell, akkor a lekérdezési terv elolvassa a Singers táblázatot, hogy megtalálja a predikátumnak megfelelő sorokat. A teljes táblázat beolvasását teljes táblázatvizsgálatnak nevezzük, a teljes táblázat beolvasása pedig drága módszer az eredmények elérésére, ha a táblázat csak kis százalékát tartalmazzaSingers ezzel a vezetéknévvel.

a lekérdezés teljesítményét javíthatja egy másodlagos index meghatározásávala vezetéknév oszlop:

CREATE INDEX SingersByLastName on Singers (LastName);

mivel a SingersByLastName másodlagos index tartalmazza az indexelt tablecolumn LastName és az elsődleges kulcs oszlopot SingerId, a Cloud Spanner a teljes Singers táblázat beolvasása helyett a sokkal kisebb indextáblából származó összes adatot képes leképezni.

ebben az esetben a Cloud Spanner valószínűleg automatikusan használja a másodlagos indexet SingersByLastName a lekérdezés végrehajtásakor. A legjobb azonban, ha explicit módon azt mondja A Cloud Spanner-nek, hogy használja ezt az indexet azáltal, hogy megadja az index irányelvet a FROM záradékban:

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

most tegyük fel, hogy a theID mellett az énekes keresztnevét is el akarta szerezni. Annak ellenére, hogy a FirstName oszlop nem szerepel az indexben, még mindig meg kell adnia az index irányelvet, mint korábban:

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

az index használatával továbbra is teljesítményelőnyhöz jut, mert a Cloud Spannerdoesn-nek nem kell teljes táblázatvizsgálatot végeznie a lekérdezési terv végrehajtásakor. Ehelyett a SingersByLastNameindex predikátumát kielégítő sorok részhalmazát választja ki, majd a Singers alaptáblából keres, hogy az első nevet csak a sorok azon részhalmazához töltse le.

ha el akarja kerülni, hogy a Cloud Spanner-nek egyáltalán sorokat kelljen letöltenie az alaptáblából, opcionálisan tárolhatja a FirstName oszlop másolatát magában az indexben:

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

az ilyen STORING záradék használata extra tárhelyet jelent, de a következő előnyöket biztosítja a lekérdezések és az index használatával történő hívások olvasásához:

• az indexet használó és a STORINGzáradékban tárolt oszlopokat kijelölő SQL-lekérdezések nem igényelnek további csatlakozást az alaptáblához.
• az indexet használó olvasási hívások olvashatják az STORING záradékban tárolt oszlopokat.

az előző példák azt mutatják be, hogy a másodlagos indexek hogyan gyorsíthatják fel a lekérdezéseket, amikor a lekérdezés WHERE záradéka által választott sorok gyorsan azonosíthatók a másodlagos index segítségével. Egy másik forgatókönyv, amelyben a másodlagos indexek kínálhatnaka teljesítmény előnyei bizonyos lekérdezések esetében, amelyek a megrendelt eredményeket adják vissza. Például tegyük fel, hogy az összes albumcímet és megjelenési dátumukat szeretné lekérni, majd a kiadás dátumának növekvő sorrendjében, albumcímek szerint pedig csökkenő sorrendben adja vissza őket. Lehet írni egy SQL lekérdezést, mint ez:

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

másodlagos index nélkül ez a lekérdezés potenciálisan költséges sortingstep-et igényel a végrehajtási tervben. Felgyorsíthatja a lekérdezés végrehajtását azáltal, hogy meghatározza ezt a másodlagos indexet:

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

ezután írja át a lekérdezést a másodlagos index használatához:

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

vegye figyelembe, hogy ez a lekérdezés és az indexdefiníció mindkét alábbi feltételnek megfelel:

• a ORDER BY záradék oszloplistája az indexkulcslista előtagja.
• a lekérdezésben használt táblázat összes oszlopát lefedi az index.

mivel mindkét feltétel teljesül, az eredményül kapott lekérdezési terv eltávolítja a rendezési lépést, és gyorsabban végrehajtja.

míg a másodlagos indexek felgyorsíthatják a gyakori lekérdezéseket, vegye figyelembe, hogy az addingsecondary indexek késleltetést adhatnak a commit műveletekhez, mert az egyes másodlagos indexek általában egy extra csomópontot igényelnek minden commit-ben. Formost munkaterhelés, amelynek néhány másodlagos indexek rendben van. Meg kell azonban fontolnia, hogy jobban törődik-e az olvasási vagy írási késéssel, és fontolja meg, hogy mely műveletek a legkritikusabbak a munkaterhelés szempontjából. Azt is meg kell benchmark yourworkload annak biztosítása érdekében, hogy teljesít, ahogy várod.

a másodlagos indexekre vonatkozó teljes hivatkozáshoz lásd a másodlagos indexeket.

írjon hatékony lekérdezéseket a range key lookup számára

az SQL lekérdezés általános használata az, hogy több sort olvas a Cloud Spanner-ből az ismert kulcsok listája alapján.

ezek a legjobb gyakorlatok a hatékony lekérdezések írására, amikor az adatokat egy kulcstartományban töltik le:

• ha a kulcsok listája ritka és nem szomszédos, használja a lekérdezési paramétereket és aUNNEST értéket a lekérdezés létrehozásához.

  ha például a kulcslista {1, 5, 1000}, írja be a lekérdezést így:

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

  Megjegyzések:

  • a tömb UNNEST operátor egyengeti aninput tömb sorokba elemek.

  • @KeyList egy lekérdezési paraméter, amely felgyorsíthatja a lekérdezést az előző bevált gyakorlat szerint.

• ha a kulcsok listája szomszédos és egy tartományon belül van, adja meg a kulcstartomány alsó és magasabb határát a WHERE záradékban.

  ha például a kulcslista {1,2,3,4,5}, akkor a lekérdezést így állíthatja össze:

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

  ahol a @min és a @max olyan lekérdezési paraméterek, amelyek az 1, illetve az 5 értékekhez vannak kötve.

  vegye figyelembe, hogy ez a lekérdezés csak akkor hatékonyabb, ha a kulcstartományban lévő kulcsok vannakszomszédos. Más szavakkal, ha a kulcslista {1, 5, 1000}, akkor ne adja meg az alsó és a magasabb határokat, mint az előző lekérdezésben, mert az eredményező lekérdezés minden 1 és 1000 közötti értéket átvizsgálna.

írjon hatékony lekérdezéseket az illesztésekhez

a csatlakozási műveletek drágák lehetnek. Ez azért van, mert a JOINs jelentősen megnövelheti a lekérdezés beolvasásához szükséges sorok számát, ami lassú lekérdezéseket eredményez. Amellett, hogy a technikák te megszokta, hogy használja otherrelational adatbázisok optimalizálni csatlakozni lekérdezések, itt van néhány legjobb gyakorlat fora hatékonyabb csatlakozni, ha a Cloud Spanner SQL:

• ha lehetséges, egyesítse az adatokat az átlapolt táblákban elsődleges kulcs szerint. Például:

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

  a Albums interleaved tábla sorai garantáltan ugyanabban a felosztásban lesznek tárolva, mint a Singers szülő sorai, amint azt a séma és az adatmodell tárgyalja. Ezért a JOINs helyileg befejezhető anélkül, hogy sok adatot küldene a hálózaton keresztül.

• használja a join irányelvet, ha kényszeríteni szeretné a JOIN sorrendjét. Például:

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

  a @{FORCE_JOIN_ORDER=TRUE} csatlakozási irányelv azt mondja A Cloud Spanner-nek, hogy használjaa lekérdezésben megadott csatlakozási sorrendet (azaz Singers JOIN Albums, nemAlbums JOIN Singers). A visszaküldött eredmények ugyanazok, függetlenül attól, hogy a Cloud Spanner melyik parancsot választja. Érdemes azonban használni ezt a joindirective-t, ha a lekérdezési tervben azt észleli, hogy a Cloud Spanner megváltoztatta a csatlakozási sorrendet, és nemkívánatos eredményeket okozott, például nagyobb közbenső eredményeket, vagy elmulasztotta a sorok keresésének lehetőségeit.

• használjon join irányelvet, hogy válasszon egy join végrehajtás. A megfelelő joinalgoritmus kiválasztása a lekérdezéshez javíthatja a késleltetést, a memóriafogyasztást, az orboth-ot. Ez a lekérdezés bemutatja a szintaxis usinga JOIN irányelv a JOIN_METHOD tipp, hogy válasszon egy HASH JOIN:

  SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId

• ha HASH JOIN vagy APPLY JOIN záradékot használ, és ha WHEREzáradéka erősen szelektív a JOIN egyik oldalán, akkor tegye a legkisebb számú sort előállító táblázatot az illesztés FROM klauzulájának első táblájává. Ez azért van, mert jelenleg a HASH JOIN-ben a Cloud Spanner mindig a bal oldali táblát választja buildként, a jobb oldali táblát pedig asprobe-ként. Hasonlóképpen, APPLY JOIN esetén a Cloud Spanner a bal oldali asouter-t és a jobb oldali asztalt választja belsőnek. További információ ezekről a típusokról: Hash join and Apply join.

kerülje a nagy olvasást az írás-olvasás tranzakciókban

az írás-olvasás tranzakciók lehetővé teszik a nulla vagy morereads vagy SQL lekérdezések sorozatát, és tartalmazhatnak mutációkat egy hívás előtt. Az adatok konzisztenciájának fenntartása érdekében a Cloud Spanneracquers zárakat szerez, amikor sorokat olvas és ír a táblázatokban és indexekben (további részletek az olvasások és írások életében történő zárolásról).

mivel a zárolás a Cloud Spanner alkalmazásban működik, az olvasás vagy az SQLquery végrehajtása, amely nagyszámú Sort olvas (például SELECT * FROM Singers), azt jelenti, hogy más tranzakciók nem írhatnak az olvasott sorokba, amíg a tranzakció el nem készül vagy meg nem szakad. Továbbá, mivel az öntranzakciója nagyszámú Sort dolgoz fel, valószínűleg hosszabb időt vesz igénybe, mint egy olyan tranzakció, amely sokkal kisebb sortartományt olvas (például SELECTLastName FROM Singers WHERE SingerId = 7), ami tovább súlyosbítja a problémátés csökkenti a rendszer teljesítményét.

ezért meg kell próbálnia elkerülni a nagy olvasásokat (például: teljes táblázat beolvasása vagymasszív csatlakozási műveletek) a tranzakciókon belül, hacsak nem hajlandó alacsonyabb írási teljesítményt elfogadni. Bizonyos esetekben a következő minta hozhatjobb eredményeket:

1. ne a nagy olvasási belül csak olvasható tranzakció. (Megjegyzendő, hogy az írásvédett tranzakciók nem használnak zárakat, és így nagyobb áteresztőképességet tesznek lehetővé.)
2. ha bármilyen feldolgozást kell végeznie az éppen olvasott adatokon, tegye meg.
3. írás-olvasás tranzakció indítása.
4. ellenőrizze, hogy a fontos sorok nem változtak-e az értékek azóta, hogy az 1.lépésben végrehajtotta a csak olvasható tranzakciót.
   1. ha a sorok megváltoztak, húzza vissza a tranzakciót, és kezdje újra az 1.lépésben.
   2. ha minden rendben van, kövesse el a mutációkat.

az egyik módja annak, hogy elkerülje a nagy olvasmányokat az olvasási-írási műveletek belsejében, az, hogy megvizsgálja a yourqueries által generált végrehajtási terveket.

a ORDER BY használatával biztosíthatja az SQL eredmények sorrendjét

ha egy SELECT lekérdezés eredményeire bizonyos sorrendet vár, akkor kifejezetten tartalmaznia kell a ORDER BY záradékot. Például: ha az összes énekest elsődleges kulcssorrendben szeretné felsorolni, használja ezt a lekérdezést:

SELECT * FROM SingersORDER BY SingerId;

vegye figyelembe, hogy a Cloud Spanner csak akkor garantálja az eredményrendelést, ha a ORDER BY clauseis jelen van a lekérdezésben. Más szavakkal, fontolja meg ezt a lekérdezést a ORDERBY:

SELECT * FROM Singers;

A Cloud Spanner nem garantálja, hogy a lekérdezés eredményei elsődleges kulcsrendben lesznek. Ezenkívül az eredmények sorrendje bármikor megváltozhat, és nem garantálható, hogy a meghívástól a meghívásig következetes legyen.

a STARTS_WITH használata a LIKE helyett a paraméterezett SQL lekérdezések felgyorsításához

mivel a Cloud Spanner nem értékeli ki a paraméterezett LIKE mintákat a végrehajtási időig, a Cloud Spanner minden sort el kell olvasnia, és aLIKE kifejezéssel kell kiértékelnie azokat, hogy kiszűrje a nem egyező sorokat.

azokban az esetekben, amikor a LIKE minta olyan egyezéseket keres, amelyek az avalue elején vannak, és az oszlop indexelve van, használja a STARTS_WITHértéket a LIKE helyett. Ezlehetővé teszi a Cloud Spanner számára a lekérdezés végrehajtási tervének hatékonyabb optimalizálását.

nem ajánlott:

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

ajánlott:

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