Kyselyn optimointi - Query optimization
Kyselyn optimointi on ominaisuus monille relaatiotietokantojen hallintajärjestelmille ja muille tietokannoille, kuten kaaviotietokannoille . Kyselyn Optimizer yrittää määrittää tehokkain tapa suorittaa tietyssä haussa huomioon mahdolliset kyselysuunnitelmien .
Yleensä käyttäjät eivät voi käyttää kyselynoptimointityökalua suoraan: kun kyselyt on lähetetty tietokantapalvelimelle ja jäsennetty jäsennettynä, ne välitetään kyselynoptimointityökalulle, jossa optimointi tapahtuu. Jotkin tietokantamoottorit mahdollistavat kuitenkin kyselynoptimointityökalun ohjaamisen vihjeillä .
Kysely on tietokannan tietopyyntö. Se voi olla niin yksinkertaista kuin "löytää sellaisen henkilön osoite, jolla on sosiaaliturvatunnus 123-45-6789", tai monimutkaisempi, kuten "löytää kaikkien Kaliforniassa työskentelevien naimisissa olevien miesten keskipalkka 30-39-vuotiaiden, jotka ansaitsevat vähemmän kuin heidän puolisonsa. " Kyselyn tulos luodaan käsittelemällä rivit tietokannassa tavalla, joka tuottaa pyydetyt tiedot. Koska tietokantarakenteet ovat monimutkaisia, useimmissa tapauksissa ja erityisesti ei-hyvin yksinkertaisissa kyselyissä, kyselyn tarvittavat tiedot voidaan kerätä tietokannasta käyttämällä sitä eri tavoilla, eri tietorakenteiden kautta ja eri järjestyksissä. Jokainen eri tapa vaatii tyypillisesti eri käsittelyajan. Saman kyselyn käsittelyajat voivat vaihdella suuresti sekunnin murto -osasta tuntiin valitusta menetelmästä riippuen. Kyselyn optimoinnin, joka on automatisoitu prosessi, tarkoitus on löytää tapa käsitellä kysely mahdollisimman lyhyessä ajassa. Suuri mahdollinen ajan vaihtelu oikeuttaa kyselyn optimoinnin, vaikka tarkan optimaalisen kyselysuunnitelman löytäminen kaikkien mahdollisuuksien joukosta on tyypillisesti hyvin monimutkaista, aikaa vievää, voi olla liian kallista ja usein käytännössä mahdotonta. Siten kyselynoptimointi yrittää tyypillisesti lähentää optimia vertailemalla useita maalaisjärkeä vaihtoehtoja, jotta saadaan kohtuullisessa ajassa "riittävän hyvä" suunnitelma, joka tyypillisesti ei poikkea paljon parhaasta mahdollisesta tuloksesta.
Yleisiä huomioita
On kompromissi määrän aika miettiminen parhaan kyselysuunnitelma ja laadun valinta; optimoija ei välttämättä valitse parasta vastausta yksinään. Tietokantojen hallintajärjestelmien eri ominaisuuksilla on erilaisia tapoja tasapainottaa nämä kaksi. Kustannusperusteiset kyselynoptimointityökalut arvioivat eri kyselysuunnitelmien resurssijalanjäljen ja käyttävät tätä suunnitelman valinnan perustana. Nämä määrittävät arvioidun "hinnan" kullekin mahdolliselle kyselysuunnitelmalle ja valitsevat suunnitelman, jolla on pienin kustannus. Kustannuksia käytetään arvioimaan kyselyn arvioinnin ajonaikaiset kustannukset, jotka perustuvat tarvittavien I/O -toimintojen määrään , suorittimen polun pituuteen , levyn puskuritilan määrään, levyn tallennusaikaan ja rinnakkaisyhteyksien käyttöön rinnakkaisyyden ja muiden tietosanakirjasta määritetyt tekijät . Tarkastetut kyselysuunnitelmat muodostetaan tutkimalla mahdollisia pääsyreittejä (esim. Ensisijainen hakemiston käyttö, toissijaisen hakemiston käyttöoikeus, täysi tiedostojen tarkistus) ja erilaisia relaatiotaulukoiden yhdistämistekniikoita (esim. Yhdistäminen , hajautusliitos , tuotteen yhdistäminen ). Hakutila voi kasvaa melko suureksi riippuen SQL -kyselyn monimutkaisuudesta . Optimointia on kahta tyyppiä. Nämä koostuvat loogisesta optimoinnista - joka luo relaatiota algebran sarjan kyselyn ratkaisemiseksi - ja fyysisestä optimoinnista - jota käytetään määrittämään kunkin operaation suorittamisen keinot.
Toteutus
Useimmat kyselynoptimointityökalut edustavat kyselysuunnitelmia "suunnitelmasolmujen" puuna . Suunnitelmasolmu kapseloi yksittäisen operaation, joka tarvitaan kyselyn suorittamiseen. Solmut on järjestetty puuksi, jossa välitulokset virtaavat puun pohjasta ylöspäin. Jokaisessa solmussa on nolla tai useampia alisolmuja - ne ovat solmuja, joiden lähtö syötetään tulona emosolmulle. Esimerkiksi liittymissolmussa on kaksi alisolmua, jotka edustavat kahta yhdistämisoperaania, kun taas lajittelusolmussa olisi yksi alisolmu (lajiteltava tulo). Puun lehdet ovat solmuja, jotka tuottavat tuloksia skannaamalla levyn, esimerkiksi suorittamalla indeksiskannauksen tai peräkkäisen skannauksen.
Liity tilaukseen
Kyselysuunnitelman suorituskyky määräytyy pitkälti taulukoiden yhdistämisjärjestyksen mukaan. Esimerkiksi kun yhdistetään kolme taulukkoa A, B, C, joiden koko on 10 riviä, 10000 riviä ja 1000000 riviä, vastaavasti kyselysuunnitelma, joka ensin yhdistää B ja C, voi kestää useita suuruusluokkia enemmän aikaa kuin yhden liittyy ensin A ja C. Useimmat kyselynoptimointityökalut määrittävät liittymisjärjestyksen dynaamisen ohjelmointialgoritmin kautta, jonka on kehittänyt IBM: n System R -tietokantaprojekti. Tämä algoritmi toimii kahdessa vaiheessa:
- Ensinnäkin lasketaan kaikki tapoja käyttää jokaista kyselyn suhdetta . Kaikkia kyselyn suhteita voidaan käyttää peräkkäisen tarkistuksen avulla. Jos suhteessa on indeksi , jota voidaan käyttää kyselyn predikaattiin vastaamiseen , voidaan käyttää myös indeksiskannausta. Optimoija tallentaa kullekin suhteelle halvin tapa tarkistaa suhde ja halvin tapa tarkistaa yhteys, joka tuottaa tietueita tietyssä lajitellussa järjestyksessä.
- Tämän jälkeen optimoija harkitsee kunkin suhteparin yhdistämistä, joille liittymisehto on olemassa. Kunkin parin kohdalla optimoija ottaa huomioon DBMS: n toteuttamat käytettävissä olevat liitosalgoritmit . Se säilyttää halvimman tavan liittyä jokaiseen suhtepariin, ja lisäksi halvin tapa liittyä jokaiseen suhtepariin, joka tuottaa sen tuotoksen tietyn lajittelujärjestyksen mukaisesti.
- Sitten lasketaan kaikki kolmen suhteen kyselysuunnitelmat yhdistämällä jokainen edellisen vaiheen tuottama kaksisuhteinen suunnitelma kyselyn jäljellä oleviin suhteisiin.
Lajittelujärjestys voi välttää tarpeetonta lajittelutoimintoa myöhemmin kyselyn käsittelyssä. Toiseksi tietty lajittelujärjestys voi nopeuttaa myöhempää yhdistämistä, koska se ryhmittelee tiedot tietyllä tavalla.
Kyselyjen suunnittelu sisäkkäisille SQL -kyselyille
SQL -kysely nykyaikaiseen suhteelliseen DBMS -järjestelmään tekee muutakin kuin valintoja ja yhdistämistä. Erityisesti SQL-kyselyt pesivät usein useita SPJ- lohkojen kerroksia (Select-Project-Join) ryhmittelemällä , on olemassa ja ei ole -operaattoreiden avulla. Joissakin tapauksissa tällaiset sisäkkäiset SQL-kyselyt voidaan tasoittaa select-project-join -kyselyksi, mutta ei aina. Sisäkkäisten SQL -kyselyiden kyselysuunnitelmat voidaan valita myös käyttämällä samaa dynaamista ohjelmointialgoritmia, jota käytettiin liittymien tilaamiseen, mutta tämä voi johtaa valtavaan pidentymiseen kyselyn optimointiajassa. Jotkut tietokannan hallintajärjestelmät käyttävät vaihtoehtoista sääntöpohjaista lähestymistapaa, joka käyttää kyselykaavion mallia.
Kustannusarvio
Yksi kyselyn optimoinnin vaikeimmista ongelmista on arvioida tarkasti vaihtoehtoisten kyselysuunnitelmien kustannukset. Optimoijat maksavat kyselysuunnitelmat käyttämällä kyselyn suorittamiskustannusten matemaattista mallia, joka perustuu suurelta osin kyselysuunnitelman jokaisen reunan läpi kulkeviin kardinaalisuuden tai tupleiden lukumäärän arvioihin . Kardinaalisuuden arviointi puolestaan riippuu kyselyn predikaattien valintatekijän arvioista . Perinteisesti tietokantajärjestelmät arvioivat valikoivuutta melko yksityiskohtaisten tilastojen avulla kunkin sarakkeen arvojen jakautumisesta, kuten histogrammit . Tämä tekniikka toimii hyvin yksittäisten predikaattien selektiivisyyden arvioimiseksi. Kuitenkin monissa kyselyissä on konjunktioita predikaateista, kuten . Kysely -predikaatit ovat usein erittäin korreloituneita (esimerkiksi vihjaavat ), ja on erittäin vaikeaa arvioida sidoksen selektiivisyyttä yleensä. Huonot kardinaalisuusarviot ja saamaton korrelaatio ovat yksi tärkeimmistä syistä siihen, miksi kyselynoptimointityökalut valitsevat huonot kyselysuunnitelmat. Tämä on yksi syy siihen, miksi tietokannan järjestelmänvalvojan tulee päivittää tietokannan tilastot säännöllisesti, etenkin suurten tietojen lataamisen/purkamisen jälkeen.
select count(*) from R where R.make='Honda' and R.model='Accord'model='Accord'make='Honda'
Laajennukset
Klassisessa kyselynoptimoinnissa oletetaan, että kyselysuunnitelmia verrataan yhden kustannusmittarin, yleensä suoritusajan, perusteella ja että kunkin kyselysuunnitelman kustannukset voidaan laskea ilman epävarmuutta. Molempia oletuksia rikotaan joskus käytännössä ja klassisen kyselynoptimoinnin useita laajennuksia on tutkittu kirjallisuudessa, joka voittaa nämä rajoitukset. Nämä laajennetut ongelmavaihtoehdot eroavat toisistaan siinä, miten ne mallinnavat yksittäisten kyselysuunnitelmien kustannuksia ja optimointitavoitteensa.
Parametrinen kyselyn optimointi
Klassinen kyselynoptimointi yhdistää jokaisen kyselysuunnitelman yhteen skalaariseen kustannusarvoon. Parametrinen kyselynoptimointi olettaa, että kyselysuunnitelman hinta riippuu parametreista, joiden arvot eivät ole tiedossa optimointihetkellä. Tällaiset parametrit voivat esimerkiksi edustaa sellaisten kyselypredikaattien selektiivisyyttä, joita ei ole täsmällisesti määritetty optimointiaikana, mutta jotka toimitetaan suoritushetkellä. Parametrinen kyselynoptimointi yhdistää siksi jokaisen kyselysuunnitelman kustannusfunktioon, joka kartoitetaan moniulotteisesta parametriavaruudesta yksiulotteiseen kustannustilaan.
Optimoinnin tavoitteena on yleensä luoda kaikki kyselysuunnitelmat, jotka voisivat olla optimaalisia mille tahansa mahdolliselle parametriarvoyhdistelmälle. Tämä tuottaa joukon asiaankuuluvia kyselysuunnitelmia. Suoritusaikana paras suunnitelma valitaan joukosta heti, kun todelliset parametriarvot ovat tiedossa. Parametrisen kyselynoptimoinnin etuna on, että optimointia (joka on yleensä erittäin kallis toimenpide) vältetään ajon aikana.
Monitahoinen kyselyn optimointi
Suoritusajan lisäksi on usein muita kustannustietoja, jotka ovat merkityksellisiä kyselysuunnitelmien vertailuun. Esimerkiksi pilvipalveluskenaariossa kyselysuunnitelmia tulisi verrata paitsi sen suorittamiseen kuluvan ajan lisäksi myös siihen, kuinka paljon niiden suorittaminen maksaa. Tai likimääräisen kyselynoptimoinnin yhteydessä on mahdollista suorittaa kyselysuunnitelmia satunnaisesti valituille syöttötiedon näytteille, jotta saadaan likimääräiset tulokset pienemmillä suorituskustannuksilla. Tällaisissa tapauksissa vaihtoehtoisia kyselysuunnitelmia on verrattava niiden suoritusajan, mutta myös niiden tuottaman tiedon tarkkuuden tai luotettavuuden kannalta.
Monitavoitteinen kyselynoptimointi mallintaa kyselysuunnitelman kustannukset kustannusvektorina, jossa jokainen vektorin komponentti edustaa kustannuksia eri kustannustietojen mukaan. Klassista kyselyn optimointia voidaan pitää erityistapauksena monen tavoitteen kyselyn optimoinnille, jossa kustannustilan ulottuvuus (eli kustannusvektorikomponenttien lukumäärä) on yksi.
Erilaiset kustannusmittarit voivat olla ristiriidassa keskenään (esim. Pilvipalveluskenaariossa voi olla yksi suunnitelma, jonka toteuttamisaika on minimaalinen, ja toinen suunnitelma, jossa toteutetaan vähimmäisrahat). Siksi optimoinnin tavoitteena ei voi olla kyselysuunnitelman löytäminen, joka minimoi kaikki kustannustiedot, vaan sen on oltava sellainen kyselysuunnitelma, joka toteuttaa parhaan kompromissin eri kustannustietojen välillä. Paras kompromissi riippuu käyttäjien mieltymyksistä (esim. Jotkut käyttäjät saattavat haluta halvempaa suunnitelmaa ja toiset nopeampaa suunnitelmaa pilviskenaariossa). Optimoinnin tavoitteena on siis joko löytää paras kyselysuunnitelma, joka perustuu optimoijalle syötettyihin käyttäjäasetuksiin (esim. Käyttäjät voivat määritellä painot eri kustannustietojen välillä ilmaistakseen suhteellisen tärkeyden tai määritellä kovat kustannusrajat tietyille mittareille) tai luoda likimääräinen arvio Pareto-optimaalisista kyselysuunnitelmista (eli suunnitelmista, jotka eivät ole millään muulla suunnitelmalla paremmat kustannukset kaikkien mittareiden mukaan) siten, että käyttäjä voi valita halutun kustannussuhteen kyseisestä suunnitelmasarjasta.
Monitahoinen parametrinen kyselyn optimointi
Monitavoitteinen parametrinen kyselyn optimointi yleistää parametrisen ja monitavoitteisen kyselyn optimoinnin. Suunnitelmia verrataan useiden kustannusmittarien mukaan, ja suunnitelman kustannukset voivat riippua parametreista, joiden arvot eivät ole tiedossa optimointihetkellä. Kyselysuunnitelman kustannukset mallinnetaan siksi funktiona moniulotteisesta parametriavaruudesta moniulotteiseen kustannustilaan. Optimoinnin tavoitteena on luoda kyselysuunnitelmia, jotka voivat olla optimaalisia jokaiselle mahdolliselle parametriarvojen ja käyttäjäasetusten yhdistelmälle.
Useat työkalut näyttävät kyselyn suorittamissuunnitelmat näyttääkseen, mitkä toiminnot ovat korkeimmat käsittelykustannukset. Microsoft SMS, ApexSQLPlan, Hana ja Tableau ovat esimerkkejä. Näiden suunnitelmien ongelmien korjaaminen voi ajaa kymmeniä prosentteja suoritusajasta ja joissakin tapauksissa leikata kaksiulotteiset haut lineaarisiksi.
Yksi ensisijaisista ja yksinkertaisimmista optimointitarkistuslistoista on käyttää toimintoja, joita useimmat RDMS on suunniteltu suorittamaan tehokkaasti. Katso Sargable .
