12 måder at optimere SQLite-databaser på – prøv dem nu!

Afsløring: Din support hjælper med at holde webstedet kørt! Vi tjener et henvisningsgebyr for nogle af de tjenester, vi anbefaler på denne side.


SQLite er et SQL-baseret relationsdatabasestyringssystem (RDBMS) implementeret som et integreret bibliotek. Det gemmer databaser som diskrete filer i stedet for at stole på klient-servermodel.

SQLite bruges ofte på tre måder:

  • Dens brugervenlighed er ideel til testning og prototype af databaserede-understøttede applikationer.
  • Da alt gemmes lokalt, og selve biblioteket kan integreres i en applikation, bruges SQLite ofte også som det primære datalager til små applikationer, der køres lokalt af en enkelt bruger. Dette inkluderer applikationer som adressebøger, huskelister eller endda e-mail-læsere.
  • Endelig bruges SQLite-databaser ofte som et applikationsspecifikt filformat. Dette er især nyttigt i applikationer, hvor en gemt fil er et komplekst projekt snarere end et relativt simpelt dokument. I dette tilfælde er hver fil oprettet af applikationen faktisk en hel SQLite-database.

Behovet for optimering

Det meste af tiden, når det bruges til test og prototype, er ikke meget vigtigt at optimere det til hastighed. I disse tilfælde er det heller ikke altid muligt, da du muligvis planlægger at køre din applikation med en anden database i produktion. SQLite her bruges simpelthen som en stand-in til noget andet som PostgreSQL eller MySQL.

Men når SQLite bruges “i produktion” som i de to andre tilfælde, er ydelsen vigtig. Ved anvendelse af et par enkle teknikker kan det virkelig påvirke hastigheden af ​​databaseopdateringer og forespørgsler.

Her er nogle praktiske tip til forbedring af SQLite-ydeevne i dine applikationer. Nogle af dem er SQL-forespørgseloptimering, der kan hjælpe med at fremskynde ethvert SQL-databasesystem. Andre er især vigtige for at optimere SQLite.

Da SQLite er meget en populær datastore i Android-apps, har vi også inkluderet nogle specifikke tip til optimering af SQLite-ydelse på Android.

Brug en transaktion

Det allerførste råd, som alle giver for at fremskynde SQLite, er “brug en transaktion.”

Alle siger dette, fordi det er en rigtig god idé. Men du spekulerer måske på, hvordan du bruger en transaktion i SQL.

Lad os sige, at du har samlet en masse data i en eller anden iterable struktur som en liste eller array. Du kan blive fristet til at gå igennem dine data og indsætte dem i din SQLite-database på hver iteration af løkken.

/ ************************************************* ***
Hent for- og efternavne fra en fane-afgrænset fil.
Indsæt dem derefter i SQLlite-databasen.
************************************************** ** /

/ * Sørg for at definere disse i det virkelige liv…
#define DATABASE = // databasens navn //
#define FILE_OF_NAMES = // sti til fil //
#define CREATE_TABLE = // SQL-sætning for at oprette navne-tabel //
* /

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

pFile = fopen (FILE_OF_NAMES,"r");
mens (! feof (pFile)) {

fgets (sInputBuf, BUFFER_SIZE, pFile);

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

sprintf (sSQL, "INDSÆT I NAVNE VÆRDIER (NULL, ‘% s’, ‘% s’,)", sFirstName, sLastName, s);
sqlite3_exec (db, sSQL, NULL, NULL, &sErrMsg);

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

Dette er en dårlig idé. Dette forstøver hvert insert i en enkelt transaktion – hver med sit eget overhead. Ikke en stor aftale, hvis du kun har et par indsættelser, men selv i hurtigkørende C-kode kan dette bremse dig ned til under 100 indsatser pr. Sekund. Hvis du bruger SQLite som et programfilformat, kan dette muligvis betyde, at brugerne oplever flere sekunders forsinkelse hver gang de gemmer et komplekst dokument eller projekt.

I stedet for at indsætte dit datasæt individuelt, skal du sammenlægge alle dine indsæt i en enkelt transaktion. Dette fremskynder dine indsatser markant. Og det er en ekstremt nem ændring at foretage.

/ * før loopen – start transaktionen * /
sqlite3_exec (db, "BEGIN TRANSAKTION", NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"r");
mens (! feof (pFile)) {
.
.
.
}

fclose (pFile);

/ * efter loop – afslutte transaktionen * /
sqlite3_exec (db, "SLUT TRANSAKTION", NULL, NULL, &sErrMsg);

Du udfører stadig INSERT-erklæringen inde i løkken, men de opdaterer ikke databasen på hver iteration. I stedet gemmer SQLite alle dine udsagn i en cache og kører dem derefter alle på én gang som en enkelt handling, når du SLUKER TRANSAKTION.

Da indsatserne alle er gemt i cachen, skal du muligvis hæve din cache-størrelse for at få hurtig fordelen ved at bruge transaktioner på denne måde.

/ * efter åbning af db-forbindelsen,
før transaktion startes * /
sqlite3_exec (db, "PRAGMA cache_størrelse = 10000", NULL, NULL, &sErrMsg);

Transaktioner i Android

Android’s indbyggede SQLite API gør brug af transaktioner endnu lettere.

// for at starte en transaktion
db.beginTransaction ();

// for at afslutte en transaktion
db.endTransaction ();

Du vil måske også tjekke for at se, om der var undtagelser, før transaktionen blev foretaget, og logg derefter fejlen, hvis der var et problem. Det er også let i Android.

prøve {
db.beginTransaction ();

/ * Gør ting i en løkke. * /

db.setTransactionSuccessful (); // Dette forpligter transaktionen, hvis der ikke var nogen undtagelser

} fangst (undtagelse e) {
Log.w ("Undtagelse:", e);
} endelig {
db.endTransaction ();
}

Forbered dig og bind

I det sidste eksempel blev SQL-sætningen gendannet under udførelsen af ​​hver loop. Dette betyder, at det også blev analyseret af SQLite hver gang. Denne analysering har nogle beregningsomkostninger, der bremser tingene ned med hver iteration.

Du kan fremskynde tingene ved at forberede din SQL-sætning uden for loopen og derefter binde dine data til dem, hver gang du bruger dem.

/ * inden transaktionen startes * /
sprintf (sSQL, "INDSÆT I NAVNE VÆRDIER (NULL, @FirstName, @LastName)");
sqlite3_prepare_v2 (db, sSQL, BUFFER_SIZE, &stmt, &hale);

sqlite3_exec (db, "BEGIN TRANSAKTION", NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"r");
mens (! feof (pFile)) {

fgets (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, "SLUT TRANSAKTION", NULL, NULL, &sErrMsg);
sqlite3_close (db);

Denne strategi kan også bruges uden for sløjfer. Hvis du har en forespørgsel i en funktion, kan du forberede den en gang og derefter binde den hver gang den bruges.

Forberedte erklæringer i Android

Android SQLite API giver SQLiteStatement klassen til at gøre dette let.

// skriv forespørgslen, med? for værdier at indsætte
String sql = "INDSÆT I NAVNE VÆRDIER (?,?)";

// udarbejde erklæringen
SQLiteStatement-erklæring = db.compileStatement (sql);

/ ** loop gennem poster ** /

/ ** hente navnene fra filen og tildele fornavn og efternavn ** /

// bind
statement.bindString (1, firstName);
statement.bindString (2, efternavn);

// exec
Lang række_id = statement.executeInsert ();

Synkroniser ikke til disk efter hver indsætning

SQLite venter som standard på, at operativsystemet skriver til disken efter udstedelse af hver af disse indsatser. Du kan slå denne pause fra med en simpel kommando.

sqlite3_exec (db, "PRAGMA synkron = FRA", NULL, NULL, &sErrMsg);

Placer dette, når du har åbnet forbindelsen til databasen, men inden transaktionen starter. Du skal også vide, at dette kan forårsage en database korruption i tilfælde af et styrt eller strømafbrydelse. Så du vil veje den øgede hastighed her mod eventuelle risici.

Opbevar rollback-journal i hukommelsen

Hvis du allerede lever farligt med PRAGMA synkron = FRA, og du prøver at skubbe alle de ekstra millisekunder ud, kan du også gemme rollback-journal i hukommelsen i stedet for at gemme den på disken. Kombineret med den forrige optimering er dette lidt risikabelt.

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

Du kan også indstille journal_mode til OFF, hvis du prøver at vinde en hurtigkonkurrence eller noget. (Dette anbefales ikke til brug i det virkelige liv.)

Advarsel om journaltilstand til Android

SQLites journaltilstand administreres af Android’s metoden enableWriteAheadLogging (). Som dokumentationen til udførelse af rå SQL-kommandoer siger:

Indstil ikke journal_mode ved hjælp af "PRAGMA journal_mode" erklæring, hvis din app bruger enableWriteAheadLogging ().

Kun indeks, når du virkelig har brug for det

Naive databaseudviklere kan godt lide at oprette en masse indekser “for at fremskynde tingene.” Men at gøre det tilfældigt eller indeksere bogstaveligt talt alt kan være kontraproduktivt. Indeksering af indholdet af en tabel efter en bestemt række gør læser hurtigere og skriver langsommere. Og det gør det kun hurtigere at læse for forespørgsler, der søger baseret på den kolonne.

Så hvis brugerne aldrig vil søge indholdet af en tabel baseret på en bestemt kolonne, skal du ikke indeksere den. Hvis brugerne endvidere kun sandsynligvis søger i en bestemt kolonne sjældent, skal du ikke indeksere den. Selvom de sandsynligvis søger ofte, er du stadig nødt til at overveje, om tabellen vil blive skrevet til eller søgte fra oftere. Hvis det skrives oftere end søgte, eller hvis skrivehastighederne er specielt kritiske, skal du ikke indeksere det.

Ofte dikterer applikationstypen disse behov. SQLite bruges ikke ofte til store datalagre, der har brug for at understøtte en lang række operationer. Hvis det bruges som en programfiltype, er muligheden for en bruger til hurtigt at gemme et projekt, mens han arbejder, sandsynligvis mere vigtig end at kunne søge indholdet i et arbejdsdokument så hurtigt som muligt. På den anden side kan en datalagringsapp, der normalt har manuelle opdateringer med en enkelt indgang (som et kontakter eller en liste over to-do), sandsynligvis have lidt langsommere skrivninger, men skal understøtte meget hurtig søgning.

Indeks efter indsætning af bulk

Når du opretter et indeks på en tabel, tager hver indsættelse derefter tid til at indeksere det nye indhold. Hvis din tabel initialiseres med en stor indsætning af bulkdata (måske første gang et nyt projekt eller dokument gemmes, eller når du importerer data til en ny bruger), kan du fremskynde det første store insert ved at vente på at oprette indekset indtil efter indsatsen.

Andre PRAGMA-indstillinger

Der er et antal PRAGMA-indstillinger, der kan hjælpe med at forbedre din SQLite-ydelse.

Cache-størrelse

Som kort nævnt ovenfor skal du muligvis øge din cache_størrelse. Store transaktioner bliver kun fremskyndet, hvis hele transaktionen kan gemmes i cachen.

Hukommelse, der bruges til cachen, tildeles når det er nødvendigt, så der er ingen overhead til at indstille den for høj. Du kan også justere dynamisk – hæve det for at optimere for bestemte forespørgsler og derefter sænke det, når det ikke er nødvendigt.

sqlite3_exec (db, "PRAGMA cache_størrelse = 100000", NULL, NULL, &sErrMsg);

Midlertidig tabelopbevaring

Du kan bede SQLite om at gemme midlertidige tabeller i hukommelsen. Dette vil fremskynde mange læsehandlinger, der er afhængige af midlertidige tabeller, indekser og visninger.

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

Android- og Pragma-indstillinger

Du kan bruge execSQL () -metoden til at udføre rå SQL mod din SQL-database. Det er den mest direkte måde at ændre nogen af ​​PRAGMA-indstillingerne på. Nogle af dem (som journal_mode nævnt ovenfor) administreres dog af andre klasser eller hjælpere i API’en.

Hurtigere forespørgsler – Filtrer hurtigere

Hvis du laver en forespørgsel baseret på flere kriterier, kan du ofte fremskynde den ved at omarrangere, hvordan dine kriterier er bestilt. Hvis den første WHERE-klausul returnerer færrest antal resultater, vil hver efterfølgende have færre poster at håndtere.

I en forespørgsel, der har et stort antal parametre, kan du prøve at eksperimentere med flere forskellige permutationer i rækkefølgen for at se, hvilken der har den bedste gennemsnitlige hastighed.

Uanset følsomme indeks for LIKE

LIKE-klausulen til sammenligning af tekst er ikke-følsom over for små bogstaver. Indekser er som standard store og små bogstaver. Hvis de eneste forespørgsler, som dine indekser optimerer til, er LIKE-forespørgsler, kan du spare tid på indsatser og på forespørgsler ved at gøre dit indeks-sælsensitivt.

Opret INDEX sLastName ON NAMES (KEY COLLATE NOCASE);

Brug den nyeste version, hvis det er muligt

Hver større version af SQLite indeholder forbedringer af ydelsen. Nogle udgivelser har dramatisk øget hastigheden. Så hvis du bruger en mindre version, der er et par år gammel (eller værre, stadig bruger v2), er den nemmeste rute til hurtigere udførelse blot at opgradere.

Opret ikke en ny database

Dette er en stor tænkningsændring for folk, der kommer fra andre RDBMS-systemer.

Overvej tilfældet med at bruge SQLite som et programfilformat. Hver gang du gemmer et nyt projekt (fil) for første gang i appen, er der behov for en ny databaseeksempel.

Du kan oprette en ny database og udføre en række SQL-sætninger for at tilføje de relevante tabeller og indekser. Dette er hvad du gerne vil gøre, hvis du bygger en implementerbar applikation med (for eksempel) PostgreSQL – du ville skrive koden for at opsætte databasen og få den kørt ved installation.

Men der er en hurtigere måde.

Da en SQLite-database er en diskret fil, er det relativt trivielt at klone en database – den kopierer simpelthen en fil. Dette betyder, at det normalt ikke er nødvendigt at oprette en ny database og derefter udføre alle de nødvendige SQL-sætninger. Normalt kan du bare lave en kopi.

På Android kan du bruge SQLite Asset Helper til at administrere databaser som aktiver, når du gør dette.

Overvej at denormalisere

Hvis du har erfaring med relationelle databasesystemer, er du måske interesseret i at normalisere dine data. Der er meget mere end dette, men essensen af ​​dataanormalisering er: en enkelt kilde til sandhed.

I en normaliseret relationsdatabase er ethvert stykke data, uanset hvor trivielt, repræsenteret nøjagtigt én gang. Så for eksempel kan en post, der repræsenterer en bog, muligvis henvise til posten, der repræsenterer forfatteren – men den ville bestemt ikke stave forfatterens navn direkte.

Dette sparer plads og er mere elegant. Men det gør også læsning fra databasen mere tidskrævende. Hvis du vil finde alle bøger fra en forfatter, skal du søge på forfattertabellen for at få id, og derefter søge i bogstabellen og samle posterne.

Du kan fremskynde denne form for læsning ved at duplikere forfatterens navn i alle bøgeregistreringer. Dette forbedrer ydeevnen, men ofrer normalisering. Dette har to mulige ulemper, ud over inelegance:

  • Applikationslogik bliver ansvarlig for at opretholde dataintegritet (det vil sige sandhed eller nøjagtighed).
  • Databasen skal være større for at gemme den samme mængde information.

Denormalisering i SQLite er særlig god

Alle disse bekymringer og tradeoffs er til stede, når du arbejder med ethvert RDBMS-system, ikke kun SQLite. I SQLite er der dog nogle potentielt afbødende faktorer, der gør datanormalisering mindre problematisk og mere nyttig.

  • En SQLite-applikation har typisk en mindre kompliceret datamodel (skema) end et meget stort program, der har brug for en databaseserver. Dette gør applikationskompleksiteten, der er nødvendig for at understøtte denormaliserede data, mindre belastende.
  • Datasæt, der understøttes af SQLite-databaser, er typisk mindre end dem, der er gemt i andre databasesystemer. Dette betyder, at stigningen i størrelse fra duplikerede data er mindre problematisk. Derudover er omkostningerne til ekstra størrelse i skala fra de fleste SQLite-applikationer (lokal fillagring) ubetydelige.
  • I modsætning til store databaseservere (især dem, der vedligeholdes af organisationer), er det usandsynligt, at en anden applikation vil forsøge at oprette forbindelse til SQLite-databasefiler oprettet af din applikation. Derfor behøver du ikke beskytte dig mod utilsigtet datakorruption og problematisk teamdynamik.
  • På samme måde, fordi SQLite typisk bruges som en integreret database, er der ofte en tæt kobling mellem applikationen og databasestrukturen. Dette betyder, at ulemperne ved håndtering af dataintegritet inden for applikationen generelt er lavere, end de ville være, når en database og applikation er løst par fysisk, men meget indbyrdes afhængige i virkeligheden.
  • Endelig er nogle af de normaliseringsbevarende ydelsesforbedringer, der er tilgængelige i andre databasesystemer – såsom materialiserede visninger – ikke tilgængelige i SQLite.

Af disse grunde er denormalisering af ydeevne en meget mere almindelig praksis med SQLite end med andre relationelle databaser.

Resumé

Disse tip til optimering af SQLite-ydelse er netop det – tip. Dette er ikke en nøjagtig plan at følge, og du vil ikke fremskynde din SQLite-applikation ved blot at tilføje hvert element fra denne liste til din kode. Det er ideer, der kan hjælpe, hvis de bruges korrekt. Så tænk på din ansøgning og se om nogen af ​​dem kan hjælpe. Og test. Du skal finde ud af, hvad der gør din ansøgning langsom, inden du kan gøre den hurtig.

Yderligere læsning og ressourcer

Vi har flere guider, tutorials og infografik relateret til kodning og udvikling:

  • SQL Resources: vores generelle SQL-ressource, der er kritisk for alle relationelle databaseudviklere.
  • MySQL Introduktion og ressourcer: et andet meget populært databasesystem.
  • PostgreSQL introduktion og ressourcer: et populært databasesystem alene, SQLite er delvis baseret på det.

Ultimate guide til webhosting

Se vores ultimative guide til webhosting. Det vil forklare alt hvad du har brug for at vide for at træffe et informeret valg.

Ultimate guide til webhosting
Ultimate guide til webhosting

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