SQL-suorituskyvyn säätäminen voi olla uskomattoman vaikea tehtävä, erityisesti työskenneltäessä laajamittaisen datan kanssa, jossa pienimmilläkin muutoksilla voi olla dramaattinen (positiivinen tai negatiivinen) vaikutus suorituskykyyn.

Keskisuurissa ja suurissa yrityksissä suurimman osan SQL-suorituskyvyn säätämisestä hoitaa tietokannan ylläpitäjä (DBA). Mutta uskokaa minua, on paljon kehittäjiä jotka joutuvat suorittamaan DBA: n kaltaisia ​​tehtäviä. Lisäksi olen nähnyt sen monissa yrityksissä tehdä Jos sinulla on DBA: ta, he kamppailevat usein toimiakseen hyvin kehittäjien kanssa - kannat vaativat yksinkertaisesti erilaisia ​​ongelmanratkaisumuotoja, mikä voi johtaa työtovereiden väliseen erimielisyyteen.

Suurten tietojen käsittelyssä pienimmilläkin muutoksilla voi olla dramaattinen vaikutus suorituskykyyn.

Tämän lisäksi myös yritysrakenteella voi olla merkitystä. Oletetaan, että DBA-tiimi sijoitetaan 10. kerrokseen kaikkien tietokantojensa kanssa, kun taas kehittäjät ovat 15. kerroksessa tai jopa eri rakennuksessa täysin erillisen raportointirakenteen alla - on varmasti vaikeaa työskennellä sujuvasti yhdessä näissä olosuhteissa.

Tässä artikkelissa haluaisin tehdä kaksi asiaa:

1. Tarjoa kehittäjille joitain kehittäjäpuolen SQL-suorituskyvyn säätötekniikoita.
2. Selitä, kuinka kehittäjät ja DBA: t voivat toimia yhdessä tehokkaasti.

SQL Performance Tuning (Codebasessa): Hakemistot

Jos olet täysin uusi tulokas tietokannoissa ja jopa kysyt itseltäsi 'Mikä on SQL suorituskyvyn viritys ? ”, Sinun tulisi tietää, että indeksointi on tehokas tapa virittää SQL-tietokanta, joka usein jätetään huomiotta kehityksen aikana. Periaatteessa an indeksi on tietorakenne, joka parantaa tiedonhakutoimintojen nopeutta tietokantataulukossa tarjoamalla nopeat satunnaiset haut ja tehokkaan pääsyn tilattuihin tietueisiin. Tämä tarkoittaa, että kun olet luonut hakemiston, voit valita tai lajitella rivejä nopeammin kuin ennen.

Hakemistoja käytetään myös ensisijaisen avaimen tai yksilöllisen indeksin määrittelemiseen, mikä takaa, että muilla sarakkeilla ei ole samoja arvoja. Tietenkin tietokantojen indeksointi on valtava mielenkiintoinen aihe, johon en voi tehdä oikeutta tällä lyhyellä kuvauksella (mutta tässä on yksityiskohtaisempi kirjoitus ).

Jos hakemistot ovat sinulle uusia, suosittelen tämän kaavion käyttöä kyselyjen jäsentämisessä:

Pohjimmiltaan tavoitteena on indeksoida tärkeimmät haku- ja tilaussarakkeet.

Huomaa, että jos INSERT, UPDATE ja DELETE vaikuttavat jatkuvasti taulukoihisi, sinun on oltava varovainen indeksoidessasi - saatat päätyä heikentynyt suorituskyky koska kaikkia hakemistoja on muutettava näiden toimintojen jälkeen.

Lisäksi DBA: t pudottavat usein SQL-hakemistonsa ennen miljoonan plus-rivien eräsijoitusten suorittamista nopeuttaa lisäysprosessia . Kun erä on lisätty, ne luovat indeksit uudelleen. Muista kuitenkin, että hakemistojen pudottaminen vaikuttaa kaikkiin kyseisessä taulukossa suoritettaviin kyselyihin; joten tätä lähestymistapaa suositellaan vain työskenneltäessä yhden, suuren lisäosan kanssa.

SQL-viritys: Suunnitelmat SQL Serverissä

Muuten: SQL Serverin Execution Plan -työkalu voi olla hyödyllinen hakemistojen luomisessa.

Sen päätehtävä on näyttää graafisesti SQL Server-kyselyn optimoijan valitsemat tiedonhakumenetelmät. Jos et ole koskaan nähnyt niitä aiemmin, niin on yksityiskohtainen läpikäynti .

Voit noutaa suoritussuunnitelman (SQL Server Management Studiossa) napsauttamalla 'Sisällytä todellinen suoritussuunnitelma' (CTRL + M) ennen kyselyn suorittamista.

Jälkeenpäin ilmestyy kolmas välilehti nimeltä 'Suunnitelman toteutus'. Saatat nähdä havaitun puuttuvan hakemiston. Luo se napsauttamalla hiiren kakkospainikkeella suoritussuunnitelmaa ja valitsemalla 'Puuttuvat hakemistotiedot ...'. Se on niin yksinkertaista!

( Napsauta zoomataksesi )

SQL-viritys: Vältä silmukoiden koodaamista

Kuvittele skenaario, jossa 1000 kyselyä lyö tietokantasi peräkkäin. Vähän niin kuin:

for (int i = 0; i <1000; i++) { SqlCommand cmd = new SqlCommand('INSERT INTO TBL (A,B,C) VALUES...'); cmd.ExecuteNonQuery(); }

Sinun pitäisi välttää tällaisia ​​silmukoita koodissasi. Voisimme esimerkiksi muuttaa yllä olevan koodinpätkän käyttämällä ainutlaatuista INSERT tai UPDATE lauseke, jossa on useita rivejä ja arvoja:

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Varmista, että WHERE lauseke välttää tallennetun arvon päivittämisen, jos se vastaa olemassa olevaa arvoa. Tällainen triviaali optimointi voi lisätä dramaattisesti SQL-kyselyn suorituskykyä päivittämällä vain satoja rivejä tuhansien sijaan. Esimerkiksi:

UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A @VALUE -- VALIDATION

SQL-viritys: Vältä vastaavia SQL-kyselyitä

TO vastaava alakysely on sellainen, joka käyttää vanhemman kyselyn arvoja. Tällainen SQL-kysely on taipumus suorittaa rivi riviltä , kerran jokaiselle ulomman kyselyn palauttamalle riville ja vähentää siten SQL-kyselyn suorituskykyä. Uudet SQL-kehittäjät joutuvat usein jäsentelemään kyselyjään tällä tavalla - koska se on yleensä helppo reitti.

Tässä on esimerkki korreloidusta alakyselystä:

SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Erityisesti ongelmana on, että sisempi kysely (SELECT CompanyName…) suoritetaan kukin ulkoisen kyselyn palauttama rivi (SELECT c.Name…). Mutta miksi mennä Company uudestaan ​​ja uudestaan ​​jokaiselle riville, jota ulkoinen kysely käsittelee?

Tehokkaampi SQL-suorituskyvyn viritystekniikka olisi korjata vastaava alakysely uudelleen liitoksena:

SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

Tässä tapauksessa käymme läpi Company taulukko vain kerran, alussa, ja LIITY siihen Customer pöytä. Siitä lähtien voimme valita tarvitsemamme arvot (co.CompanyName) tehokkaammin.

SQL-viritys: Valitse harva

Yksi suosikkini SQL-optimointivinkeistä on välttää SELECT *! Sen sijaan sinun tulisi erikseen sisällyttää tarvitsemasi sarakkeet. Jälleen tämä kuulostaa yksinkertaiselta, mutta näen tämän virheen kaikkialla. Harkitse taulukkoa, jossa on satoja sarakkeita ja miljoonia rivejä - jos sovelluksellasi on vain muutama sarake, ei ole mitään järkeä kysellä kaikki tiedot. Se on valtava resurssien tuhlaaminen. ( Jos haluat lisätietoja, katso tässä . )

Esimerkiksi:

SELECT * FROM Employees

vs.

SELECT FirstName, City, Country FROM Employees

Jos tarvitset todella kaikkia sarakkeita, listaa kaikki sarakkeet erikseen. Tämä ei ole niinkään sääntö, vaan pikemminkin keino estää tulevia järjestelmävirheitä ja ylimääräisiä SQL-suorituskyvyn säätöjä. Jos esimerkiksi käytät INSERT... SELECT... ja lähdetaulukko on muuttunut lisäämällä uusi sarake, saatat kohdata ongelmia, vaikka kohdetaulukko ei tarvitsisi kyseistä saraketta, esimerkiksi:

INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Tämänkaltaisten SQL Server-virheiden välttämiseksi sinun on ilmoitettava kukin sarake erikseen:

INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Huomaa kuitenkin, että joissakin tilanteissa SELECT * voisi olla sopiva. Esimerkiksi temp-taulukoilla - mikä johtaa meidät seuraavaan aiheeseen.

SQL-viritys: Väliaikaisten taulukoiden viisas käyttö (#Temp)

Väliaikaiset taulukot yleensä lisää kyselyn monimutkaisuutta. Jos koodisi voidaan kirjoittaa yksinkertaisella ja suoraviivaisella tavalla, suosittelen välttämään temp-taulukoita.

Mutta jos sinulla on tallennettu menettely, jossa on joitain tietojen manipulointeja ei voi Jos haluat käsitellä yhtä kyselyä, voit käyttää välitaulukkoja väliaikaisina taulukoina lopullisen tuloksen luomisessa.

Kun sinun on liityttävä suureen taulukkoon ja mainitussa taulukossa on ehtoja, voit lisätä tietokannan suorituskykyä siirtämällä tietojasi temp-taulukkoon ja tekemällä sitten liitoksen että . Lämpötilataulukossa on vähemmän rivejä kuin alkuperäisessä (suuressa) taulukossa, joten liittyminen päättyy nopeammin!

Päätös ei ole aina suoraviivaista, mutta tämä esimerkki antaa sinulle käsityksen tilanteista, joissa haluat ehkä käyttää temp-taulukoita:

Kuvittele asiakastaulukko, jossa on miljoonia tietueita. Sinun täytyy liittyä tiettyyn alueeseen. Voit saavuttaa tämän käyttämällä SELECT INTO ja sitten liittyminen temp-taulukkoon:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Huomaa: Jotkut SQL-kehittäjät välttävät myös SELECT INTO: n käyttöä luoda temp-taulukoita sanomalla, että tämä komento lukitsee tempdb-tietokannan, estäen muita käyttäjiä luomasta temp-taulukoita. Onneksi näin on kiinteä versiossa 7.0 ja uudemmissa .)

Vaihtoehtona temp-taulukoille kannattaa harkita alikyselyn käyttöä taulukkona:

SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Mutta odota! Toisessa kyselyssä on ongelma. Kuten edellä on kuvattu, meidän tulisi sisällyttää alakyselymme vain tarvitsemamme sarakkeet (ts. Käyttämättä SELECT *). Ottaen huomioon tämän:

SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Kaikki nämä SQL-katkelmat palauttavat samat tiedot. Mutta temp-taulukoiden avulla voimme esimerkiksi luoda indeksin temp-taulukkoon suorituskyvyn parantamiseksi. On hyvä keskustelu tässä väliaikaisten taulukoiden ja alakyselyjen välisistä eroista.

Lopuksi, kun olet valmis temp-taulukkoosi, poista se tempdb-resurssien tyhjentämiseksi eikä vain odota, että se poistetaan automaattisesti (kuten tapahtuu, kun yhteys tietokantaan katkaistaan):

DROP TABLE #temp

SQL-viritys: 'Onko tietoni olemassa?'

Tämä SQL-optimointitekniikka koskee EXISTS(): n käyttöä. Jos haluat tarkistaa tietueen olemassaolon, käytä EXISTS() COUNT(): n sijaan. Vaikka COUNT() skannaa koko taulukon ja laskee kaikki tilaasi vastaavat merkinnät EXISTS() poistuu heti nähdessään tarvitsemansa tuloksen. Tämä antaa sinulle parempi suorituskyky ja selkeämpi koodi .

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

SQL-suorituskyvyn säätäminen SQL Server 2016: lla

Kuten SQL Server 2016: n kanssa työskentelevät DBA: t todennäköisesti tietävät, versio merkitsi merkittävää muutosta oletusarvot ja yhteensopivuuden hallinta . Pääversiossa se tietysti sisältää uusia kyselyn optimointeja, mutta hallintaa niiden käyttämisestä yksinkertaistetaan nyt sys.databases.compatibility_level -palvelun kautta.

SQL Performance Tuning (toimistossa)

SQL-tietokannan järjestelmänvalvojat (DBA) ja kehittäjät ovat usein ristiriidassa tietoihin ja muihin kuin tietoihin liittyvistä ongelmista. Kokemukseni perusteella tässä on muutamia vinkkejä (molemmille osapuolille) siitä, kuinka tulla toimeen ja työskennellä tehokkaasti.

Tweet

Tietokannan optimointi kehittäjille:

1. Jos sovelluksesi lakkaa toimimasta yhtäkkiä, se ei välttämättä ole tietokantaongelma. Esimerkiksi, ehkä sinulla on verkko-ongelma. Tutki vähän ennen kuin syytät DBA: ta!

2. Vaikka olisit ninja SQL -tietomallinnusohjelma, pyydä DBA: ta auttamaan relaatiokaaviossasi. Heillä on paljon jaettavaa ja tarjottavaa.

3. DBA: t eivät pidä nopeista muutoksista. Tämä on luonnollista: heidän on analysoitava koko tietokanta ja tutkittava mahdollisten muutosten vaikutukset kaikista näkökulmista. Yksinkertainen muutos sarakkeessa voi viedä viikon, mutta se johtuu siitä, että virhe voi toteutua yritykselle suurina tappioina. Ole kärsivällinen!

4. Älä pyydä SQL DBA: ta tekemään tietoja muutoksista tuotantoympäristössä. Jos haluat käyttää tuotantotietokantaa, sinun on vastattava kaikista omista muutoksistasi.

SQL Server DBA -tietokannan optimointi:

1. Jos et pidä siitä, että ihmiset kysyvät sinulta tietokantaa, anna heille reaaliaikainen tilapaneeli. Kehittäjät ovat aina epäilyttäviä tietokannan tilasta, ja tällainen paneeli voi säästää kaikille aikaa ja energiaa.

2. Auta kehittäjiä testaus- / laadunvarmistusympäristössä. Tuotantopalvelimen simulointi on helppoa yksinkertaisten testien avulla reaalimaailman tiedoissa. Tämä säästää huomattavasti aikaa sekä muille että itsellesi.

3. Kehittäjät viettävät koko päivän järjestelmiin, joiden liiketoimintalogiikka muuttuu usein. Yritä ymmärtää tämän maailman olevan joustavampi ja pystyä rikkomaan joitain sääntöjä kriittisessä hetkessä.

4. SQL-tietokannat kehittyvät. Tulee päivä, jolloin sinun on siirrettävä tietosi uuteen versioon. Kehittäjät luottavat merkittävästi uusiin toimintoihin jokaisessa uudessa versiossa. Suunnittele eteenpäin ja ole valmis siirtymiseen sen sijaan, että kieltäydyisit hyväksymästä heidän muutoksiaan.

Liittyvät: SQL-indeksit selitetty, Pt. 1

Perustietojen ymmärtäminen

Mitä kyselyn käsittely DBMS: ssä on?

Tietokannan hallintajärjestelmien, kuten SQL Server, on käännettävä antamasi SQL-kyselyt todellisiksi ohjeiksi, jotka heidän on suoritettava tietokannan tietojen lukemiseksi tai muuttamiseksi. Käsittelyn jälkeen tietokantamoottori yrittää myös optimoida kyselyn automaattisesti mahdollisuuksien mukaan.

Mikä on kyselyn optimointi SQL Serverissä?

Kyselyn optimointi on silloin, kun kehittäjä tai tietokantamoottori muuttaa kyselyä siten, että SQL Server pystyy palauttamaan samat tulokset tehokkaammin. Joskus se on yksinkertaista käyttää EXISTS (): ää COUNT (): n sijaan, mutta toisinaan kysely on kirjoitettava uudella lähestymistavalla.

Mitä on suorituskyvyn säätäminen SQL Serverissä?

Suorituskyvyn säätö sisältää kyselyn optimoinnin, SQL-asiakaskoodin optimoinnin, tietokannan hakemistojen hallinnan ja toisessa mielessä paremman koordinoinnin kehittäjien ja DBA: iden välillä.

Mikä on indeksin käyttö SQL: ssä?

Hakemisto seuraa kohdennettua taulukon tietojen alijoukkoa niin, että valinta ja järjestys voidaan tehdä paljon nopeammin, ilman että palvelimen on tarkasteltava taulukon jokaista viimeistä dataa.

Miksi EXISTS () on nopeampi kuin COUNT ()?

EXISTS () lopettaa käsittelyn heti, kun se löytää sopivan rivin, kun taas COUNT (): n on laskettava jokainen rivi riippumatta siitä, tarvitsetko tätä yksityiskohtaa lopulta.