Testauksen jälkeen suoritettiin ehdollinen kysely taulukolle, joka sisälsi yli 4 miljoonaa tietuetta, ja kyselyaika oli jopa 40 sekuntia. Siksi SQL-lausekyselyn tehokkuuden parantaminen on erittäin tärkeää. Seuraavassa on useita kyselylauseen optimointimenetelmiä, joita levitetään laajasti Internetissä:
Ensinnäkin, kun datavolyymi on suuri, kannattaa välttää koko taulukon skannaamista ja harkita indeksien rakentamista sarakkeille missä ja järjestyksessä, mikä voi nopeuttaa datan hakemista huomattavasti. Kuitenkin on tilanteita, joissa indeksointi ei toimi:
1. Yritä välttää !=- tai <>-operaattoreiden käyttöä where-lauseessa, muuten moottori luopuu indeksien käytöstä ja suorittaa täyden taulukon skannauksen.
2. Yritä välttää null-arvon arviointia kentille where-lauseessa, muuten moottori luopuu indeksien käytöstä ja suorittaa täydellisen taulukon skannauksen, kuten:
Valitse id T:stä, missä num on nolla
Voit asettaa num-arvon oletusarvoksi 0, varmistaa ettei num-sarakkeessa ole nollaarvoa taulukossa, ja sitten tehdä kyselyn näin:
Valitse id t:stä, missä num=0
3. Yritä välttää OR:n käyttöä where-lausekkeessa ehtojen yhdistämiseen, muuten moottori lopettaa indeksin käytön ja suorittaa täydellisen taulukon skannauksen, kuten:
Valitse id t:stä, missä num=10 tai num=20
Voit kysyä näin:
Valitse ID t:stä, missä num=10
Union all
Valitse ID t:stä, missä num=20
4. Seuraava kysely johtaa myös täydelliseen taulukkoskannaukseen:
Valitse ID T:stä, missä nimi kuten '%ABC%'
Tehokkuuden parantamiseksi harkitse kokotekstihakua.
5. Sisään ja ei sisään tulee myös käyttää varoen, muuten se johtaa täydelliseen taulukkoskannaukseen, kuten:
Valitse id t:stä, missä num in(1,2,3)
Jatkuville arvoille, jos voit käyttää väliä, älä käytä in:
Valitse ID T:stä, missä numero välissä 1–3
6. Jos käytät missä-lausekkeen parametria, koko taulukko skannataan. Koska SQL ratkaisee paikalliset muuttujat vain ajonaikaisesti, mutta optimointiohjelma ei voi siirtää pääsysuunnitelmien valintaa ajonaikaiselle; Se täytyy valita käännösvaiheessa. Kuitenkin, jos käyttösuunnitelma perustetaan käännösvaiheessa, muuttujan arvo on edelleen tuntematon, eikä sitä siksi voida käyttää syötteenä indeksin valinnassa. Seuraavat lausunnot skannataan kokonaisuudessaan:
Valitse id t:stä, missä num=@num
Voit pakottaa kyselyn käyttämään indeksiä sen sijaan:
Valitse id t:stä (index(index(index name)), missä num=@num
7. Yritä välttää kenttien ilmaisemista where-lauseessa, mikä saa moottorin lopettamaan indeksin käytön ja suorittamaan täyden taulukon skannauksen. Esimerkiksi:
Valitse ID t:stä, missä num/2=100
tulisi muuttaa muotoon:
Valitse ID t:stä, missä num=100*2
8. Yritä välttää funktiooperaatioiden tekemistä where-lausekkeen kentille, jolloin moottori lopettaa indeksien käytön ja suorittaa täyden taulukon skannauksen. Esimerkiksi:
Valitse id t:stä, missä substring(name,1,3)='abc' – name id, joka alkaa abc:llä
Valitse id t:stä, missä datediff(päivä, luotu,'2005-11-30′)=0–'2005-11-30′ generoitu ID
tulisi muuttaa muotoon:
Valitse ID T:stä, missä nimi on 'ABC%'
Valitse id t:stä, missä createdate>='2005-11-30′ ja createdate<'2005-12-1′
9. Älä suorita funktioita, aritmeettisia operaatioita tai muita lausekkeiden operaatioita "=":n vasemmalla puolella missä -lauseessa, muuten järjestelmä ei välttämättä pysty käyttämään indeksiä oikein.
10. Kun indeksikenttää käytetään ehtona, jos indeksi on yhdistetty indeksi, indeksin ensimmäinen kenttä on käytettävä ehtona varmistamaan, että järjestelmä käyttää indeksiä, muuten indeksiä ei käytetä, ja kenttien järjestys tulee olla mahdollisimman yhdenmukainen indeksin järjestyksen kanssa.
11. Älä kirjoita merkityksettömiä kyselyitä, kuten luo tyhjää taulukkorakennetta:
Valitse col1,col2 #t t:stä, missä 1=0
Tämä koodityyppi ei anna tulosjoukkoa, mutta kuluttaa järjestelmän resursseja, joten se tulisi vaihtaa tällaiseksi:
luo taulukko #t(...)
12. Monesti on hyvä käyttää olemassa sen sijaan, että käyttäisi in:
Valitse num a:sta, missä num sisään (valitse num b:stä)
Korvaa seuraavalla lauseella:
Valitse num a:sta, missä on olemassa (valitse 1 b:stä, missä num=a.num)
Asioita, joihin kannattaa kiinnittää huomiota indeksiä rakentaessa:
1. Kaikki indeksit eivät ole päteviä kyselyille, SQL perustuu taulukon dataan kyselyn optimoimiseksi; kun indeksisarakkeessa on paljon datan päällekkäisyyttä, SQL-kyselyt eivät välttämättä käytä indeksiä, esimerkiksi taulukossa on kenttiä sukupuoli, mies, nainen, lähes puolet kutakin, jolloin vaikka indeksi perustuisi sukupuoleen, sillä ei ole merkitystä kyselyn tehokkuudessa.
2. Mitä enemmän indeksejä ei ole, sitä parempi, indeksi voi varmasti parantaa vastaavan valinnan tehokkuutta, mutta se myös vähentää lisäyksen ja päivityksen tehokkuutta, koska indeksi voidaan rakentaa uudelleen lisäyksen tai päivityksen yhteydessä, joten indeksin rakentamista on harkittava tarkasti tilanteen mukaan. On parasta, ettei taulukossa ole enempää kuin 6 indeksiä, ja jos niitä on liikaa, harkitse, onko tarpeen rakentaa indeksejä joihinkin harvoin käytettyihin sarakkeisiin.
3. Vältä klusteroitujen indeksitietojen sarakkeiden päivittämistä mahdollisimman paljon, koska klusteroitujen indeksoitujen datasarakkeiden järjestys on taulukkotietueiden fyysinen tallennusjärjestys, ja kun sarakkeen arvo muuttuu, se johtaa koko taulutietueiden järjestyksen säätämiseen, mikä kuluttaa huomattavasti resursseja. Jos sovellusjärjestelmän täytyy päivittää klusteroituja indeksisarakkeita usein, sen on harkittava, tulisiko indeksi rakentaa klusteroiduksi indeksiksi.
Muita huomionarvoisia seikkoja:
1. Yritä käyttää numeerisia kenttiä, äläkä suunnittele kenttiä, jotka sisältävät pelkästään numeerista tietoa merkkeinä, mikä heikentää kyselyjen ja yhteyksien suorituskykyä ja lisää tallennuskustannuksia. Tämä johtuu siitä, että moottori vertaa jokaista merkkijonon merkkiä yksi kerrallaan kyselyitä ja liitoksia käsitellessään, kun taas numeerisissa tyypeissä sitä tarvitsee verrata vain kerran.
2. Älä käytä valitse * from t mistään muusta, korvaa "*" tietyllä kenttälistalla äläkä palauta käyttämättömiä kenttiä.
3. Yritä käyttää taulukkomuuttujia väliaikaisten taulukoiden sijaan. Jos taulukkomuuttuja sisältää suuren määrän dataa, huomaa, että indeksi on hyvin rajallinen (vain ensisijainen avainindeksi).
4. Vältä väliaikaisten taulukoiden toistuvaa luomista ja poistamista järjestelmätaulujen resurssien vähentämiseksi.
5. Väliaikaiset taulut eivät ole käyttökelvottomia, ja niiden oikea käyttö voi tehdä tietyistä rutiineista tehokkaampia, esimerkiksi kun sinun täytyy toistuvasti viitata suureen taulukkoon tai tietoaineistoon yleisesti käytetyssä taulukossa. Kertaluonteisissa tapahtumissa on kuitenkin parasta käyttää vientitaulukkoa.
6. Väliaikaista taulua luota, jos kerralla syötetyn datan määrä on suuri, voit käyttää select-tiedostoa create-taulukon sijaan välttääksesi suuren lokimäärän nopeutta; Jos datamäärä ei ole suuri, järjestelmätaulun resurssien helpottamiseksi sinun tulisi ensin luoda taulu ja sitten lisätä se.
7. Jos käytetään väliaikaista taulua, poista kaikki väliaikaiset taulut tallennetun prosessin lopussa, katkaise ensin taulu ja poista sitten taulu, jotta järjestelmätaulun pitkä lukko vältyy.
8. Yritä välttää kohdistimen käyttöä, koska kursorin tehokkuus on heikko; jos kursorin ohjaama data ylittää 10 000 riviä, kannattaa harkita uudelleenkirjoittamista.
9. Ennen kursoripohjaisen menetelmän tai väliaikaisen taulukon menetelmän käyttöä kannattaa ensin etsiä joukkopohjaisia ratkaisuja ongelman ratkaisemiseksi, ja joukkopohjainen menetelmä on yleensä tehokkaampi.
10. Kuten väliaikaisissa taulukoissa, kursori ei ole käyttökelvoton. FAST_FORWARD kursorien käyttö pienille aineistoille on usein parempi kuin muut rivikohtaiset käsittelymenetelmät, erityisesti jos sinun täytyy viitata useisiin tauluihin saadaksesi tarvittavan datan. Rutiinit, jotka sisältävät "total" tulosjoukkoon, ovat yleensä nopeampia kuin ne, jotka suoritetaan kursorilla. Jos kehitysaika sallii, voidaan kokeilla sekä kursori- että joukkopohjaisia menetelmiä, jotta nähdään, kumpi toimii paremmin.
11. Aseta SET NOCOUNT PÄÄLLE kaikkien tallennettujen proseduurien ja laukaisujen alussa, ja aseta SET NOCOUNT POIS lopuksi. Ei ole tarvetta lähettää DONE_IN_PROC viestejä asiakkaalle jokaisen tallennetun proseduurin ja laukaisun suorittamisen jälkeen.
12. Yritä välttää suurten tietojen palauttamista asiakkaalle; jos datamäärä on liian suuri, sinun tulisi harkita, onko vastaava kysyntä kohtuullinen.
13. Yritä välttää suuria transaktiotoimintoja ja parantaa järjestelmän rinnakkaiskykyä.
|
Julkaistu 11.5.2018 13.57.06
|
|
|
Julkaistu 17.5.2018 10.12.27