SQL Query Optimization Techniques

Julkaissut Platon

seuraajia: 0

SQL-kyselyn optimointitekniikat
Kuva tekijältä

Aloittelijatasolla keskitymme vain SQL-kyselyiden kirjoittamiseen ja suorittamiseen. Emme välitä siitä, kuinka paljon aikaa sen suorittaminen vie tai pystyykö se käsittelemään miljoonia tietueita. Mutta keskitasolla ihmiset odottavat, että kyselysi on optimoitu ja sen suorittaminen vie mahdollisimman vähän aikaa.

Optimoidun kyselyn kirjoittaminen suuriin sovelluksiin, joissa on miljoonia tietueita, kuten verkkokauppaalustoilla tai pankkijärjestelmissä, on välttämätöntä. Oletetaan, että omistat verkkokauppayrityksen, jolla on yli miljoona tuotetta, ja asiakas haluaa etsiä tuotetta. Entä jos taustaohjelmassa kirjoittamasi kyselyn hakeminen tietokannasta kestää yli minuutin? Luuletko, että asiakkaat ostavat tuotteita verkkosivustoltasi?

Sinun on ymmärrettävä SQL-kyselyn optimoinnin merkitys. Tässä opetusohjelmassa näytän sinulle vinkkejä ja temppuja SQL-kyselysi optimoimiseksi ja niiden suorittamiseksi nopeammin. Ensisijainen edellytys on, että sinulla on perustiedot SQL:stä.

Voit tarkistaa, onko taulukossa tietty elementti, käyttämällä EXIST() avainsanan sijaan COUNT() suorittaa kyselyn optimoidulla tavalla.

Käyttäminen COUNT(), kyselyn on laskettava kaikki kyseisen elementin esiintymät, jotka voivat olla tehottomia, kun tietokanta on laaja. Toisaalta, EXIST() tarkistaa vain kyseisen elementin ensimmäisen esiintymisen ja pysähtyy sitten, kun se löytää ensimmäisen esiintymän. Tämä säästää paljon aikaa.

Lisäksi olet kiinnostunut vain selvittämään, onko tietty elementti läsnä vai ei. Et ole kiinnostunut tapausten lukumäärästä. Siksi myös EXIST() on parempi.

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

Yllä oleva kysely palaa 1 jos vähintään yksi taulukon rivi sisältää merkinnän, jossa sarake on nimeltään myColumn on yhtä suuri kuin val. Muuten se tulee takaisin 0.

molemmat char ja varchar tietotyyppejä käytetään merkkijonojen tallentamiseen taulukkoon. Mutta varchar on paljon muistitehokkaampi kuin char.

Char-tietotyyppi voi tallentaa vain määritellyn pituisen merkkijonon. Jos merkkijonon pituus on pienempi kuin kiinteä pituus, se täyttää tyhjät kohdat niin, että sen pituus on yhtä pitkä kuin asetettu pituus. Tämä tuhlaa tarpeettomasti muistia täyteen. Esimerkiksi,CHAR(100) vie 100 tavua muistia, vaikka yksi merkki olisi tallennettu.

Toisaalta varchar-tietotyyppi tallentaa muuttuvan pituisen merkkijonon, jonka pituus on pienempi kuin määritetty enimmäispituus. Se ei täytä tyhjiä välilyöntejä ja vie vain merkkijonon todellista pituutta vastaavan muistin. Esimerkiksi, VARCHAR(100) vie vain 1 tavun muistia, kun tallennetaan yksi merkki.

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

Yllä olevassa esimerkissä taulukko myTable luodaan kahdella sarakkeella, charCol ja varcharCol joissa on vastaavasti char- ja varchar-tietotyypit. charCol vie aina 10 tavua muistia. Verrattuna, varcharCol vie muistia, joka on sama kuin siihen tallennetun merkkijonon todellinen koko.

Meidän on vältettävä käyttämästä alikyselyitä WHERE-lauseen sisällä SQL-kyselyn optimoimiseksi. Koska alikyselyt voivat olla kalliita ja vaikeita suorittaa, kun ne palauttavat suuren määrän rivejä.

Alikyselyn käyttämisen sijaan voit saada saman tuloksen käyttämällä liitostoimintoa tai kirjoittamalla korreloitua alikyselyä. Korreloitu alikysely on alikysely, jonka sisäinen kysely riippuu ulkoisesta kyselystä. Ja ne ovat erittäin tehokkaita verrattuna korreloimattomiin alikyselyihin.

Alla on esimerkki näiden kahden välisen eron ymmärtämiseksi.

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

Ensimmäisessä esimerkissä alikysely kerää ensin kaikki asiakastunnukset, jotka kuuluvat Intiaan, ja sitten ulompi kysely saa kaikki valittujen asiakastunnusten tilaukset. Ja toisessa esimerkissä olemme saavuttaneet saman tuloksen liittymällä customers ja orders taulukoita ja valitse sitten vain tilaukset, joissa asiakkaat kuuluvat INTIAsta.

Tällä tavalla voimme optimoida kyselyn välttämällä alikyselyjen käyttöä WHERE-lauseen sisällä ja tekemällä niistä helpompia lukea ja ymmärtää.

Sovellus JOIN operaatio suuremmasta taulukosta pienempään taulukkoon on yleinen SQL-optimointitekniikka. Koska liittäminen suuremmasta taulukosta pienempään taulukkoon nopeuttaa kyselyn suorittamista. Jos haemme a JOIN SQL-koneemme on etsittävä suuremmasta taulukosta vastaavia rivejä. Tämä on enemmän resursseja ja aikaa vievää. Mutta toisaalta, jos JOIN sovelletaan suuremmasta taulukosta pienempään taulukkoon, niin SQL-koneen on etsittävä pienemästä taulukosta vastaavia rivejä.

Tässä on esimerkki, jotta ymmärrät paremmin.

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

Toisin kuin LIKE lauseke, regexp_like käytetään myös kuviohakuun. The LIKE lauseke on perusmallinsovitusoperaattori, joka voi suorittaa vain perustoimintoja, kuten _ or %, joita käytetään vastaamaan yhtä merkkiä tai vastaavasti minkä tahansa määrän merkkejä. The LIKE lausekkeen on skannattava koko tietokanta löytääkseen tietyn mallin, mikä on hidasta suurille taulukoille.

Toisaalta, regexp_like on tehokkaampi, optimoitu ja tehokkaampi kuvionhakutekniikka. Se käyttää monimutkaisempia säännöllisiä lausekkeita löytääkseen tiettyjä kuvioita merkkijonosta. Nämä säännölliset lausekkeet ovat tarkempia kuin yksinkertainen jokerimerkkihaku, koska niiden avulla voit etsiä täsmälleen löytämämme mallin. Tämän ansiosta etsittävän tiedon määrä vähenee ja kysely suoritetaan nopeammin.

Huomaa, että regexp_like ei välttämättä ole kaikissa tietokannan hallintajärjestelmissä. Sen syntaksi ja toiminnallisuus voivat vaihdella muissa järjestelmissä.

Tässä on esimerkki, jotta ymmärrät paremmin.

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

Yllä olevia kyselyitä käytetään etsimään elementtejä, joiden nimi alkaa kirjaimella A tai B. Ensimmäisessä esimerkissä LIKE käytetään etsimään kaikkia nimiä, jotka alkavat A:lla tai B:llä. A% tarkoittaa, että ensimmäinen merkki on A; sen jälkeen merkkejä voi olla mikä tahansa määrä. Toisessa esimerkissä regexp_like käytetään. Sisällä ^[AB], ^ tarkoittaa, että symboli vastaa merkkijonon alkua, [AB] tarkoittaa, että aloitusmerkki voi olla A tai B, ja .* edustaa kaikkia hahmoja sen jälkeen.

Käyttäminen regexp_like, tietokanta voi nopeasti suodattaa pois rivit, jotka eivät vastaa mallia, mikä parantaa suorituskykyä ja vähentää resurssien käyttöä.

Tässä artikkelissa olemme keskustelleet erilaisista menetelmistä ja vihjeistä SQL-kyselyn optimoimiseksi. Tämä artikkeli antaa sinulle selkeän käsityksen tehokkaiden SQL-kyselyjen kirjoittamisesta ja niiden optimoinnin tärkeydestä. On monia muita tapoja optimoida kyselyitä, kuten mieluummin kokonaislukuarvojen käyttö merkkien sijaan tai Union All -ominaisuuden käyttäminen Unionin sijaan, kun taulukossasi ei ole kaksoiskappaleita jne.

Aryan Garg on B.Tech. Sähkötekniikan opiskelija, parhaillaan opintojensa viimeistä vuotta. Hänen kiinnostuksensa kohdistuu verkkokehitykseen ja koneoppimiseen. Hän on ajanut tätä kiinnostusta ja on innokas työskentelemään enemmän näiden suuntien eteen.