12 tapaa optimoida SQLite-tietokantoja – Kokeile heitä nyt!

Disclosure: Tukisi auttaa pitämään sivuston toiminnassa! Ansaitsemme lähetysmaksun joistakin tämän sivun suosittelemista palveluista.


SQLite on SQL-pohjainen relaatiotietokannan hallintajärjestelmä (RDBMS), joka on toteutettu sulautettavana kirjastona. Se tallentaa tietokannat erillisinä tiedostoina sen sijaan, että luottaisi asiakas-palvelinmalliin.

SQLite: tä käytetään yleisesti kolmella tavalla:

  • Sen helppokäyttöisyys on ihanteellinen tietokannalla varustettujen sovellusten testaamiseen ja niiden prototointiin.
  • Koska kaikki tallennetaan paikallisesti ja kirjasto voidaan itse upottaa sovellukseen, SQLiteä käytetään usein myös ensisijaisena tietomuistona pienille sovelluksille, joita yksittäinen käyttäjä käyttää paikallisesti. Tämä sisältää sovelluksia, kuten osoitekirjat, tehtäväluettelot tai jopa sähköpostinlukijat.
  • Lopuksi, SQLite-tietokantoja käytetään usein sovelluskohtaisena tiedostomuodona. Tämä on erityisen hyödyllistä sovelluksissa, joissa tallennettu tiedosto on monimutkainen projekti eikä melko yksinkertainen asiakirja. Tässä tapauksessa jokainen sovelluksen luoma tiedosto on oikeastaan ​​kokonainen SQLite-tietokanta.

Optimoinnin tarve

Suurimman osan ajasta, kun sitä käytetään testaukseen ja prototyyppien määrittämiseen, sen optimointi nopeuden suhteen ei ole kovin tärkeää. Näissä tapauksissa myöskään ei aina ole mahdollista tehdä niin, koska saatat suunnitella sovelluksen käyttämistä tuotannossa olevan toisen tietokannan avulla. Täällä olevaa SQLite-ohjelmaa käytetään yksinkertaisesti stand-in-ominaisuutena jotain muuta, kuten PostgreSQL tai MySQL.

Mutta kun SQLite: tä käytetään “tuotannossa”, kuten kahdessa toisessa tapauksessa, suorituskyvyllä on merkitystä. Muutaman yksinkertaisen tekniikan käyttöönotto voi todella vaikuttaa tietokannan päivitysten ja kyselyiden nopeuteen.

Tässä on käytännön vinkkejä SQLite-suorituskyvyn parantamiseksi sovelluksissasi. Jotkut niistä ovat SQL-kyselyjen optimointi, joka auttaisi nopeuttamaan kaikkia SQL-tietokantajärjestelmiä. Toiset ovat erityisesti SQLiten optimoinnissa.

Koska SQLite on erittäin suosittu tietovarasto Android-sovelluksissa, olemme myös sisällyttäneet joitain erityisiä vinkkejä SQLiten suorituskyvyn optimoimiseksi Androidilla.

Käytä tapahtumaa

Aivan ensimmäinen neuvo, jonka kaikki antavat SQLiten nopeuttamiseksi, on “käytä tapahtumaa”.

Kaikki sanovat tämän, koska se on todella hyvä idea. Mutta saatat ihmetellä, kuinka käyttää tapahtumaa SQL: ssä.

Oletetaan, että olet kerännyt joukon tietoja joihinkin toistettavissa oleviin rakenteisiin, kuten luetteloon tai taulukkoon. Saatat olla houkutus tutkia tietojasi ja lisätä ne SQLite-tietokantaan silmukan jokaisessa iteraatiossa.

/ ************************************************* ***
Hanki etu- ja sukunimet välilehdillä erotetusta tiedostosta.
Lisää sitten ne SQLlite-tietokantaan.
************************************************** ** /

/ * muista määritellä nämä tosielämässä…
#define DATABASE = // tietokannan nimi //
#define FILE_OF_NAMES = // polku tiedostoon //
#define CREATE_TABLE = // SQL-käsky luodaksesi nimitaulukon //
* /

sqlite3_open (tietokanta, &db);
sqlite3_exec (db, CREATE_TABLE, NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"R");
while (! feof (pFile)) {

fgetit (sInputBuf, BUFFER_SIZE, pFile);

sFirstName = strtok (sInputBuf, "T");
sLastName = strtok (NULL, "T");

sprintf (sSQL, "LISÄÄ NIMIEN ARVOT (NULL, ‘% s’, ‘% s’,)", sFirstName, sLastName, s);
sqlite3_exec (db, sSQL, NULL, NULL, &sErrMsg);

n ++;
}
fclose (pFile);
sqlite3_close (db);

Tämä on huono idea. Tämä sumuttaa jokaisen insertin yhdeksi tapahtumaksi – jokaisella on omat yläkulmansa. Ei iso juttu, jos sinulla on vain pari inserttiä, mutta jopa nopeasti käynnissä olevassa C-koodissa tämä voi hidastaa sinua alle 100 inserttiin sekunnissa. Jos käytät SQLite-sovellustiedostomuotoa, tämä saattaa tarkoittaa, että käyttäjät kokevat useita sekunteja viiveestä joka kerta kun tallentavat monimutkaisen asiakirjan tai projektin.

Sen sijaan, että lisättäisit tietojoukot yksittäin, kääri kaikki lisäosat yhteen tapahtumaan. Tämä nopeuttaa merkittävästi inserttejäsi. Ja se on erittäin helppo muuttaa.

/ * ennen silmukkaa – aloita tapahtuma * /
sqlite3_exec (db, "ALOITTAA KAUPPA", NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"R");
while (! feof (pFile)) {
.
.
.
}

fclose (pFile);

/ * silmukan jälkeen – lopeta tapahtuma * /
sqlite3_exec (db, "LOPPUTAPAHTUMA", NULL, NULL, &sErrMsg);

Suoritat edelleen INSERT-käskyä silmukan sisällä, mutta he eivät päivitä tietokantaa jokaisessa iteraatiossa. Sen sijaan SQLite tallentaa kaikki lausekkeesi välimuistiin ja suorittaa ne kaikki kerralla yhtenä operaationa, kun LOPETTAT transaktion.

Koska kaikki lisäosat on tallennettu välimuistiin, saatat joutua nostamaan välimuistin kokoa saadaksesi nopeuden hyödyn käyttämällä tapahtumia tällä tavalla.

/ * db-yhteyden avaamisen jälkeen,
ennen kaupan aloittamista * /
sqlite3_exec (db, "PRAGMA välimuisti_koko = 10000", NULL, NULL, &sErrMsg);

Tapahtumat Androidissa

Androidin sisäänrakennettu SQLite-sovellusliittymä helpottaa tapahtumien käyttöä.

// tapahtuman aloittamiseksi
db.beginTransaction ();

// tapahtuman lopettamiseksi
db.endTransaction ();

Voit myös tarkistaa, onko poikkeuksia ennen tapahtuman tekemistä, ja kirjata sitten virhe, jos ilmenee ongelmia. Se on helppoa myös Androidissa.

yrittää {
db.beginTransaction ();

/ * Tee tavaraa silmukassa. * /

db.setTransactionSuccessful (); // Tämä toteuttaa tapahtuman, ellei poikkeuksia ole

} saalis (poikkeus e) {
Log.w ("poikkeus:", e);
} lopulta {
db.endTransaction ();
}

Valmista ja sido

Viimeisessä esimerkissä SQL-käsky luotiin uudelleen jokaisen silmukan suorittamisen aikana. Tämä tarkoittaa, että SQLite myös parsoi sen joka kerta. Siinä jäsentämisessä on joitain laskennallisia yläpuolella, hidastaen asioita jokaisen iteraation kanssa.

Voit nopeuttaa asioita valmistelemalla SQL-käskysi silmukan ulkopuolelle ja sitomalla tiedot siihen joka kerta, kun käytät sitä.

/ * ennen kaupan aloittamista * /
sprintf (sSQL, "LISÄÄ NIMIEN ARVOT (NULL, @FirstName, @LastName)");
sqlite3_prepare_v2 (db, sSQL, BUFFER_SIZE), &stmt, &tail);

sqlite3_exec (db, "ALOITTAA KAUPPA", NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"R");
while (! feof (pFile)) {

fgetit (sInputBuf, BUFFER_SIZE, pFile);

sFirstName = strtok (sInputBuf, "T");
sLastName = strtok (NULL, "T");

sqlite3_bind_text (stmt, 1, sFirstName, -1, SQLITE_STATIC);
sqlite3_bind_text (stmt, 2, sLastName, -1, SQLITE_STATIC);

sqlite3_step (stmt);

sqlite3_clear_bindings (stmt);
sqlite3_reset (stmt);

n ++;
}
fclose (pFile);
sqlite3_exec (db, "LOPPUTAPAHTUMA", NULL, NULL, &sErrMsg);
sqlite3_close (db);

Tätä strategiaa voidaan käyttää myös silmukoiden ulkopuolella. Jos sinulla on kysely toiminnon sisällä, voit valmistaa sen kerran ja sitätä sen joka kerta, kun sitä käytetään.

Valmistetut lausunnot Androidissa

Android SQLite -sovellusliittymä tarjoaa SQLiteStatement-luokan tämän tekemiseen helposti.

// kirjoitat kyselyn kanssa? arvojen lisäämistä varten
Merkkijono sql = "LISÄÄ NIMIEN ARVOT (?,?)";

// koota lausunto
SQLiteStatement-lause = db.compileStatement (sql);

/ ** silmukka läpi tietueiden ** /

/ ** hae nimet tiedostosta ja määritä etunimi ja sukunimi ** /

// sitoa
lause.bindString (1, etunimi);
lauseke.bindString (2, sukunimi);

// toimeenpano
Pitkä rivi = lause.executeInsert ();

Älä synkronoi levylle jokaisen lisäyksen jälkeen

SQLite odottaa oletusarvoisesti käyttöjärjestelmän kirjoittavan levylle, kun nämä on julkaistu. Voit poistaa tämän tauon käytöstä yksinkertaisella komennolla.

sqlite3_exec (db, "PRAGMA synkroninen = POIS", NULL, NULL, &sErrMsg);

Sijoita tämä, kun olet avannut yhteyden tietokantaan, mutta ennen kuin aloitat tapahtuman. Sinun tulisi myös tietää, että tämä voi aiheuttaa tietokannan vioittumisen, jos kaatuu tai virta katkeaa. Joten sinun kannattaa punnita lisääntynyt nopeus tässä mahdollisille riskeille.

Tallenna palautuspäiväkirja muistiin

Jos elät jo vaarallisesti PRAGMA: n kanssa synkroninen = POIS ja yrität puristaa kaikki ylimääräiset millisekunnit, voit tallentaa palautuspäiväkirjan myös muistiin sen sijaan, että tallentaisit sen levylle. Yhdistettynä edelliseen optimointiin tämä on vähän riskialtista.

sqlite3_exec (db, "PRAGMA journal_mode = MUISTI", NULL, NULL, &sErrMsg);

Voit myös asettaa journal_mode-asetukseksi POIS, jos yrität voittaa nopeuskilpailu tai jotain muuta. (Tätä ei suositella käytettäväksi tosielämässä.)

Päiväkirjatilan varoitus Androidille

SQLiten päiväkirjatilaa hallinnoidaan Androidin sallimalla WriteAheadLogging () -menetelmällä. Joten, kuten raaka-SQL-komentojen suorittamisen dokumentaatiossa sanotaan:

Älä aseta journal_mode-painiketta "PRAGMA journal_mode" lause, jos sovelluksesi käyttää enableWriteAheadLogging ().

Hakeudu vain, kun todella tarvitset sitä

Naiivit tietokantakehittäjät haluavat luoda paljon hakemistoja “asioiden nopeuttamiseksi”. Mutta tekemällä niin sattumanvaraisesti tai indeksoimalla kirjaimellisesti kaikki voi olla haitallista. Taulukon sisällön indeksointi tietyllä rivillä tekee lukemisesta nopeamman ja kirjoittaa hitaammin. Ja se tekee lukemisen nopeammaksi vain kyselyille, jotka hakevat kyseisen sarakkeen perusteella.

Joten jos käyttäjät eivät koskaan aio etsiä taulukon sisältöä tietyn sarakkeen perusteella, älä indeksoi sitä. Jos käyttäjät todennäköisesti tekevät hakuja tietyn sarakkeen perusteella vain harvoin, älä indeksoi sitä. Vaikka he todennäköisesti etsivätkin usein, sinun on silti mietittävä, kirjoitetaanko taulukko useammalle tai etsitäänkö taulukko. Jos se kirjoitetaan useammin kuin etsitään tai jos kirjoitusnopeudet ovat erityisen kriittisiä, älä indeksoi sitä.

Usein sovellustyyppi sanelee nämä tarpeet. SQLite: tä ei käytetä yleisesti suurissa tietovarastoissa, joiden on tuettava monenlaisia ​​toimintoja. Jos sitä käytetään esimerkiksi sovellustiedostotyyppinä, käyttäjän kyky tallentaa projekti nopeasti työskennellessään on todennäköisesti tärkeämpää kuin mahdollisuus etsiä työasiakirjan sisällöstä mahdollisimman nopeasti. Toisaalta tiedontallennussovelluksella, jolla on yleensä manuaalisia, yhden syötteen päivityksiä (kuten yhteystietohakemisto tai tehtäväluettelo), voi todennäköisesti olla hiukan hitaampaa kirjoittaa, mutta sen pitäisi tukea erittäin nopeaa hakua.

Hakemisto Irtotavaran lisäyksen jälkeen

Kun olet luonut hakemiston taulukolle, jokainen lisäys sen jälkeen vie uuden ajan indeksoinnin. Jos taulukko alustetaan suurella joukkotiedon lisäyksellä (ehkä, kun uusi projekti tai asiakirja tallennetaan ensimmäistä kertaa tai kun tuodaan tietoja uutta käyttäjää varten), voit nopeuttaa ensimmäistä suurta lisäystä odottamalla indeksin luomista. vasta lisäyksen jälkeen.

Muut PRAGMA-asetukset

On olemassa useita PRAGMA-asetuksia, jotka voivat auttaa parantamaan SQLite-suorituskykyäsi.

Välimuistin koko

Kuten edellä lyhyesti mainittiin, saatat joutua nostamaan välimuistisi kokoa. Suuret tapahtumat suoritetaan vain, jos koko tapahtuma voidaan tallentaa välimuistiin.

Välimuistille käytetty muisti allokoidaan tarvittaessa, joten liian korkealle asettamiselle ei ole ylimääräistä tilaa. Voit myös säätää dynaamisesti – nostamalla sitä optimoidaksesi tietyt kyselyt ja laskemalla sitä sitten, kun sitä ei tarvita.

sqlite3_exec (db, "PRAGMA-välimuisti_koko = 100000", NULL, NULL, &sErrMsg);

Väliaikainen taulukkovarastointi

Voit määrätä SQLiten tallentamaan väliaikaiset taulukot muistiin. Tämä nopeuttaa monia lukuoperaatioita, jotka luottavat väliaikaisiin taulukoihin, indekseihin ja näkymiin.

sqlite3_exec (db, "PRAGMA temp_store = MUISTI", NULL, NULL, &sErrMsg);

Android- ja Pragma-asetukset

Voit käyttää execSQL () -menetelmää suorittaaksesi raa’an SQL: n SQL-tietokantaa vastaan. Tämä on suorin tapa muuttaa mitä tahansa PRAGMA-asetusta. Joitakin heistä (kuten yllä mainittua lehden_muotoa) hallinnoivat kuitenkin muut API: n luokat tai avustajat.

Nopeammat kyselyt – suodata enemmän aikaisemmin

Jos teet kyselyn useiden kriteerien perusteella, voit usein nopeuttaa sitä järjestämällä kriteerien järjestyksen uudelleen. Jos ensimmäinen WHERE-lause palauttaa vähiten tuloksia, jokaisella seuraavalla lauseella on vähemmän käsiteltäviä kohteita.

Kokeile kokeilussa kyselyssä, jolla on suuri joukko parametreja, tilauksen useilla erilaisilla permutaatioilla, kummalla on paras keskimääräinen nopeus.

Kirjainkohtaiset LIKE-indeksit

Tekstin vertailun LIKE-lause on kirjainkoollainen. Hakemistot ovat oletuksena kirjainkoolla. Jos indeksisi optimoivat vain LIKE-kyselyt, voit säästää aikaa lisäyksissä ja kyselyissä tekemällä hakemistostasi pienen ja pienen kirjainkoon.

Luo indeksi sLastName NIMIIN (KEY COLLATE NOCASE);

Käytä uusinta versiota, jos mahdollista

Jokainen SQLiten pääversio sisältää suorituskyvyn parannuksia. Jotkut julkaisut ovat nopeuttaneet dramaattisesti. Joten jos käytät pienimuotoista versiota, joka on muutama vuosi vanha (tai mikä pahempaa, silti käytät v2: ta), helpoin reitti nopeampaan suoritukseen on yksinkertaisesti päivitys.

Älä luo uutta tietokantaa

Tämä on iso muutos ajattelussa muista RDBMS-järjestelmistä tulevien ihmisten ajattelussa.

Mieti SQLiten käyttämistä sovellustiedostomuodona. Joka kerta kun tallennat uuden projektin (tiedoston) ensimmäistä kertaa sovelluksessa, tarvitaan uusi tietokanta-ilmentymä.

Voit luoda uuden tietokannan ja suorittaa useita SQL-käskyjä lisätäksesi asianmukaiset taulukot ja hakemistot. Tätä haluaisit tehdä, jos rakennat asennettavaa sovellusta (esimerkiksi) PostgreSQL: llä – kirjoitat koodin tietokannan asentamiseen ja ajaa sen asennuksen yhteydessä.

Mutta siellä on nopeampi tapa.

Koska SQLite-tietokanta on erillinen tiedosto, tietokannan kloonaaminen on suhteellisen triviaalia – se yksinkertaisesti kopioi tiedostoa. Tämä tarkoittaa, että yleensä ei tarvitse luoda uutta tietokantaa ja suorittaa sitten kaikki vaadittavat SQL-lauseet. Yleensä voit vain tehdä kopion.

Androidissa saatat haluta käyttää SQLite Asset Helper -ohjelmaa tietokantojen hallintaan omaisuutena tätä tehtäessä.

Harkitse denormalisointia

Jos sinulla on kokemusta relaatiotietokantajärjestelmistä, saatat olla kiinnostunut paljon tietojen normalisoinnista. Sillä on paljon enemmän kuin tämä, mutta datan normalisoinnin ydin on: totuuden yksi lähde.

Normalisoidussa relaatiotietokannassa mikä tahansa tieto, riippumatta siitä, kuinka triviaalia, esitetään tarkalleen kerran. Joten esimerkiksi kirjaa edustava tietue voi viitata kirjailijaa edustavaan tietueeseen, mutta se ei varmasti selventäisi kirjoittajan nimeä suoraan.

Tämä säästää tilaa ja on tyylikäs. Mutta se tekee myös tietokannasta lukemisen aikaavievämmäksi. Jos haluat löytää kaikki kirjoittajan kirjat, sinun on haettava kirjailijataulusta saadaksesi tunnus ja etsiä sitten kirjataulua ja koota tietueet.

Voit nopeuttaa tällaista lukemista kopioimalla kirjoittajan nimen kaikkiin kirjakirjaan. Tämä parantaa suorituskykyä, mutta uhraa normalisoinnin. Tällä on epäeleganssin lisäksi kaksi mahdollista epäkohta:

  • Sovelluslogiikasta tulee vastuussa tietojen eheyden (ts. Totuuden tai tarkkuuden) ylläpidosta.
  • Tietokannan on oltava suurempi säilyttääksesi saman määrän tietoja.

Denormalization SQLite-sovelluksessa on erityisen hyvä

Kaikki nämä huolenaiheet ja kompromissit ovat läsnä työskennellessäsi minkä tahansa RDBMS-järjestelmän kanssa, ei vain SQLiten kanssa. SQLite-ohjelmassa on kuitenkin joitain mahdollisesti lieventäviä tekijöitä, jotka tekevät tietojen denormalisoinnista vähemmän ongelmallisia ja hyödyllisempiä.

  • Tyypillisesti SQLite-sovelluksella on vähemmän monimutkainen tietomalli (skeema) kuin erittäin suurella sovelluksella, joka tarvitsee tietokantapalvelimen. Tämä tekee denormalisoidun datan tukemiseen tarvittavan sovelluksen monimutkaisuuden vähemmän raskaaksi.
  • SQLite-tietokantojen tukemat tietojoukot ovat tyypillisesti pienempiä kuin muihin tietokantajärjestelmiin tallennetut. Tämä tarkoittaa, että koon lisääminen kopioitujen tietojen perusteella on vähemmän ongelmallista. Lisäksi useimpien SQLite-sovellusten (paikallinen tiedostovarastointi) laajuudessa lisäkokojen kustannukset ovat vähäiset.
  • Toisin kuin suurissa tietokantapalvelimissa (etenkin organisaatioiden ylläpitämissä), on epätodennäköistä, että toinen sovellus yrittää muodostaa yhteyden sovelluksesi luomiin SQLite-tietokantatiedostoihin. Siksi sinun ei tarvitse suojautua vahingossa tapahtuvalta tietojen vioittumiselta ja ongelmalliselta joukkuedynamiikalta.
  • Samoin, koska SQLite: tä käytetään tyypillisesti sulautettuna tietokannana, sovelluksen ja tietokannan rakenteen välillä on usein tiukka kytkentä. Tämä tarkoittaa, että tietojen eheyden käsittelemisen haittapuolit sovelluksen sisällä ovat yleensä alhaisempia kuin mitä se olisi, kun tietokanta ja sovellus ovat fyysisesti löysästi pari, mutta todellisuudessa hyvin toisistaan ​​riippuvaisia.
  • Lopuksi, jotkut muissa tietokantajärjestelmissä käytettävissä olevista normalisointia ylläpitävistä suorituskyvyn parannuksista – kuten toteutuneet näkymät – eivät ole käytettävissä SQLitessa.

Näistä syistä suorituskyvyn denormalisointi on SQLiten kanssa paljon yleisempi käytäntö kuin muiden relaatiotietokantojen kanssa.

Yhteenveto

Nämä vinkit SQLite-suorituskyvyn optimoimiseksi ovat juuri niitä – vinkkejä. Tämä ei ole tarkka noudatettava suunnitelma, etkä nopeuta SQLite-sovellustasi lisäämällä vain jokainen tämän luettelon kohde koodiin. Ne ovat ideoita, jotka voivat auttaa, jos niitä käytetään asianmukaisesti. Joten harkitse sovellustasi ja katso, voisiko jokin niistä auttaa. Ja testi. Sinun on selvitettävä, mikä tekee sovelluksesta hidasta, ennen kuin voit tehdä sen nopeasti.

Lisää lukemista ja resursseja

Meillä on enemmän koodaukseen ja kehittämiseen liittyviä oppaita, oppaita ja infografioita:

  • SQL-resurssit: yleinen SQL-resurssimme, joka on kriittinen kaikille relaatiotietokannan kehittäjille.
  • MySQL-esittely ja resurssit: toinen erittäin suosittu tietokantajärjestelmä.
  • PostgreSQL-esittely ja resurssit: yksinään suosittu tietokantajärjestelmä, SQLite perustuu siihen osittain.

Ultimate Guide to Web Hosting

Tutustu Ultimate Guide to Web Hosting -oppaaseen. Se selittää kaiken, mitä sinun on tiedettävä tietoisen valinnan tekemiseksi.

Ultimate Guide to Web Hosting
Ultimate Guide to Web Hosting

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map