Partea 11

Partea 11.7: Indexuri în bazele de date SQLite. Tabele de indexare în SQLite3. Algoritmul arborelui B în bazele de date

Desigur, indexurile din bazele de date sunt implementate de algoritmi mai complexi, unul dintre algoritmii cei mai populari și adesea folosiți pentru indexurile din bazele de date relaționale este algoritmul de lucru cu arborii B. A primit o distribuție atât de largă datorită fiabilității, eficienței și simplității relative, putem verifica acest lucru cu dvs. Indexurile din bazele de date SQLite sunt construite pe baza algoritmului B-tree.

Ce sunt indexurile pentru bazele de date SQlite3?

Indexele legate de subiectul asigurării integrității datelor în bazele de date numai pentru că coloana PRIMARY KEY este, de asemenea, o constrângere la nivel de tabel. și indicele de tabel al oricărei baze de date relaționale. inclusiv SQLite3. Indexurile din bazele de date SQLite sunt obiecte baze de date. acest lucru înseamnă că numele indexului trebuie să fie unic pe întreaga bază de date.

Deoarece indexul este obiectul bazei de date, pot fi aplicate diferite comenzi SQL. De exemplu, comenzile de definire a datelor sunt necesare pentru a crea și șterge indici în baza de date, nu puteți utiliza comenzile de manipulare a datelor în SQLite la indexuri. deși putem aplica cu ușurință aceste comenzi în tabele ale căror coloane sunt indici.

Scopul principal al indexurilor din baza de date este de a accelera procesul de recuperare a datelor din baza de date. Indicele accelerează foarte mult executarea comenzilor SELECT datorită faptului că sunt implementate ca arbori B. dar vom vorbi despre asta puțin mai târziu. Dar, deoarece indicii accelerează eșantionul, sau mai degrabă pentru că sunt implementați sub forma unui arbore B, indiciile încetinesc foarte mult alte operații de manipulare a datelor:

Acest lucru se datorează faptului că aceste operațiuni fac schimbări în arborele B, echilibrat pentru a suporta arborele B în starea curentă. SQLite recalculează nodurile după fiecare schimbare de date în tabele.

Pune pur și simplu, atunci când creați indexul, vom crea un alt tabel care stochează valorile coloanei indexate într-o manieră ordonată și cu baza de date, mai degrabă decât pentru a sorta datele din tabela sursă, utilizați tabelul de index, care stochează o referință la masa inițială, pentru Aceasta explică o reducere a numărului de operații pentru a căuta și a compara valorile cu cea pe care o căutați.

Să realizăm concluzii: indicii din bazele de date accelerează cererile de recuperare a datelor datorită reducerii operațiilor comparând valori, dar încetinesc și alte operații de manipulare a datelor.

Crearea de indexuri în bazele de date SQLite. CREEAȚI INDEX

Indexurile accelerează foarte mult eșantionarea datelor din baza de date utilizând comanda SELECT, dar, în același timp, indicele încetinesc activitatea altor operatori de manipulare a datelor. Să vedem cum să creăm indexuri pentru tabelele de baze de date care rulează SQLite. Deși am creat deja indexuri în baze de date de multe ori, când am declarat constrângerea cheie primară pentru coloana - KEY PRIMARY (chei și atribute cheie).

constrângere cheie primară - o constrângere la nivel de tabel este utilizat pentru a asigura integritatea datelor în baze de date, dar, în același timp, o coloană cheie primară-un index în baza de date, care accelerează semnificativ de recuperare a datelor.

Acum, să ne uităm la sintaxa pentru crearea indexurilor în bazele de date SQlite3:

SQLite are o sintaxă flexibilă pentru crearea de indici. Pentru a spune că vrem să creăm un obiect bază de date, trebuie să folosim comanda CREATE. Apoi, specificăm ce obiect bază de date dorim să creăm, în acest caz INDEX. După aceasta, există un test opțional pentru existența DACĂ NU EXISTĂ. Verificarea este necesară dacă nu sunteți sigur că indicatorul există.

Dacă ați specificat expresia cheie IF IF NOT EXISTS, atunci SQLite va verifica mai întâi numele indexului pentru unicitate și dacă există un index cu acest nume în baza de date, va emite un avertisment și nu o eroare. După verificare, se face numele indexului, în locul căruia puteți utiliza calificatorul sau numele complet al indexului cu baza de date.

Următorul este cuvântul cheie ON, urmat de numele tabelului pentru care va fi creat indexul. După numele tabelului, numele coloanei în paranteze este indicat în paranteze, care vor fi indexate, rețineți că pot exista mai multe coloane indexate, în acest caz ele sunt separate printr-o virgulă.

După numele coloanei, puteți utiliza clauza WHERE, care vă permite să specificați anumite condiții pentru crearea unui index.

Crearea de indexuri multiple pentru un singur tabel într-o bază de date SQLite3

Am menționat că SQLite vă permite să creați mai multe indexuri pentru un tabel din baza de date, să analizăm sintaxa de creare a indexurilor multiple pentru o singură masă, astfel de indici sunt numiți și compuși:

Deoarece indexul este un obiect, numele său trebuie să fie unic pe întreaga bază de date, dar există momente când lucrați simultan cu mai multe baze de date, atunci puteți șterge indexurile utilizând un calificativ sau un nume index complet. Structura IF EXISTS poate verifica existența indexului înainte de a fi șters, iar dacă nu există un index cu acest nume, SQLite va emite un avertisment, altfel va exista o eroare. Doar cererea de a șterge indexul începe cu comanda DROP. și apoi există expresia cheie INDEX, care spune SQLite3 că dorim să ștergem indexul și nu un alt obiect de bază de date.

Exemple de utilizare a indexurilor în bazele de date SQLite

Să ne uităm la câteva exemple de creare a indexurilor în bazele de date SQLite. Mai târziu, vom vedea că indexurile accelerează într-adevăr eșantionarea datelor atunci când luăm în considerare un eșantion de date din baza de date.

Să creăm un tabel în baza de date utilizând comanda CREATE:

Avem două opțiuni pentru re-indexarea tabelelor. dacă specificăm numele tabelului, SQLite va reindeca toate coloanele din tabel care sunt specificate ca un index. Dacă facem reindexarea specificând un nume de index, atunci valorile secvenței vor fi șterse și formate din nou pentru un anumit index.

Când nu aveți nevoie să utilizați indexuri în baze de date

Indicii sunt un instrument foarte util și util în baze de date, inclusiv baze de date care rulează SQLite. Subindexuri în bazele de date create în scopul de a accelera date preluate de operare, de obicei baze de date relaționale a crea un tabel separat în care acestea păstrează valorile coloanelor indexate într-o manieră ordonată, astfel încât crearea indicelui - este întotdeauna extinderea și creșterea în baza de date.

Indicii sunt convenabili și buni, dar există situații în care indicii nu ar trebui utilizați, de exemplu:

Dacă aveți o bază de date mică cu un număr mic de rânduri în tabele, atunci nu ar trebui să se utilizeze indexurile, deoarece nu veți obține niciun avantaj de la indexuri.
Dacă aveți tabele în care efectuați adesea operațiuni de modificare a datelor. atunci indecșii nu ar trebui utilizați, deoarece încetinesc foarte mult operațiile de actualizare efectuate de comanda UPDATE.
Dacă adăugați în mod frecvent rânduri noi la un tabel de bază de date (de multe ori folosind comanda INSERT), apoi tabelele, care de multe interogare SQL INSERT nu este necesar pentru a crea indici, deoarece SQLite va reconfigura permanent tabelul de index, care este motivul pentru care viteza de operare INSERT este foarte redusă.
Dacă tabelul dvs. are coloane cu mai multe valori NULL, atunci este mai bine să nu folosiți astfel de coloane ca indici, valorile NULL vă pot lipsi de avantajele indexurilor din bazele de date.
Dacă există coloane în tabelul cu care efectuați adesea operații de manipulare a datelor, nu le utilizați ca indici. La fiecare schimbare a unei coloane, SQLite, precum și orice alt DBMS, vor recalcula indexurile.

Indici interni în bazele de date SQLite. Coloana ROWID din SQLite3. Structura arborelui B

Fiecare tabel din baza de date SQLite are în mod implicit un index intern, care solicită ROWID. Uneori acest index coincide cu cheia primară a tabelului PRIMARY KEY, uneori nu se potrivește, dar fiecare tabel are un ROWID. Dacă se vorbește pur și simplu, atunci ROWID este o coloană suplimentară pe care orice tabel SQLite pentru fiecare tabel este un index pentru acest tabel.

În plus față de faptul că ROWID este un index, acesta este un alt mod de a menține integritatea datelor. Să ne uităm la indexul ROWID din bazele de date SQLite. ROWID este un număr pe 64 de biți care identifică în mod unic orice rând din baza de date SQLite.

Particularitatea bazelor de date SQLite este că toate rândurile bazei de date sunt stocate sub forma unui arbore B (multe DBMS utilizează algoritmul B-tree). Pe scurt și abstract, copacul B este un copac echilibrat și foarte ramificat, care accelerează foarte mult eșantionarea datelor.

Să ne uităm la ce este un arbore B și să înțelegem cum funcționează indicii în bazele de date SQLite și în alte baze de date relaționale. În primul rând, vă sugerez să priviți imaginea de mai jos, arată structura arborelui B și, în consecință, structura indexurilor din baza de date.

Structura indexurilor din baza de date SQLite. Structura arborelui B

Vedem că structura arborelui B este ramificată. fiecare petală de copac este un nod, în bazele de date SQLite și alte baze de date relaționale, fiecare nod este un tabel. Masa de sus a figurii este rădăcina arborelui B. Arborele B este numit echilibrat, deoarece lungimea căii de la rădăcină la oricare dintre cele două noduri din același nivel coincide.

Rădăcina B-copac - un tabel de referință, care sunt scrise referiri la alte directoare, directoarele doilea nivel se poate referi și la alte directoare, și cele la rândul său, stoca o trimitere la anumite date, de fapt, adâncimea B-copac poate fi foarte mare (pot exista multe nivele).

Când vom executa baza de date SELECT incepe sa faca prea multe date, comparând această valoare cu valorile stocate în baza de date, în cazul în care baza de date a fost un milion de rânduri, baza de date va fi merge peste un milion de rânduri, sau până când a alerga afară șir, sau până când nu ar exista a găsit valoarea.

Copacii B, la fel, ne ajută să evităm căutarea ciudată a șirurilor și să reducem numărul de controale de multe ori, totul depinde de numărul de rânduri din baza de date.

Notă: dacă coloana PRIMARY KEY este declarată diferit, atunci valorile PRIMARY KEY și ROWID nu se vor potrivi. Valoarea coloanei ROWID poate fi modificată utilizând comanda UPDATE, precum și alte valori din tabela de baze de date. Dar, amintiți-vă despre regulile unic și veșnic, care sunt specifice atributele cheie și necesitatea de a asigura integritatea datelor în baza de date, ca și tabelul KEY-limită primară este nivelul, atunci putem presupune că indicele SQLite ROWID intern este o constrângere la nivel de masă.

Tabele WITHOUT ROWID în SQLite, tabele fără indexuri interne

În SQLite, puteți crea tabele fără coloana ROWID. adică fără un indice intern. Această abordare are avantaje minore: uneori, acest lucru accelerează recuperarea datelor din baza de date și reduce ușor dimensiunea bazei de date. Dacă decideți să creați tabelul fără RowId în SQLite, atunci trebuie să faceți două lucruri: În primul rând, masa trebuie să fie întotdeauna o cheie primară, și în al doilea rând, trebuie să utilizați o expresie de acces fără ROWID.

Să creăm un tabel fără o coloană ROWID utilizând WITHOUT ROWID:

Acum tabelul nu va avea o coloană ROWID. O altă caracteristică a tabelelor WITHOUT ROWID este că limita coloanei AUTOINCREMENT nu va funcționa. FĂRĂ cheie primară ROWID a tabelului în nici un caz nu poate fi setat la NULL, dacă încercați să adăugați o coloană cheie primară NULL a tabelului FĂRĂ ROWID, că SQLite va genera o eroare.

Din cauza caracteristicilor speciale ale tabelelor WITHOUT ROWID. dezvoltatorii de SQLite recomandă utilizarea unor astfel de tabele în acele cazuri când:

Utilizați chei primare compuse sau când cheia primară nu este un număr întreg, dar are un alt tip de date.
Dacă trebuie să măriți viteza tabelului cu tasta primară INTEGER, creați un tabel fără un indice intern, aceasta va accelera selecția și va reduce dimensiunea bazei de date. Dacă tabelul are un indice de ROWID și INTEGER PRIMARY KEY, face prea mult SQLite două cicluri: primul ciclu este pe coloana ROWID, al doilea ciclu este coloana INTEGER PRIMARY KEY, deși valorile acestor coloane sunt aceleași.
Dacă în tabele există rânduri cu valori mici, atunci tabelele fără index (fără ROWID) vor funcționa oarecum mai repede decât cu coloana ROWID.

Dacă proiectați o bază de date și doriți să o optimizați, atunci nu trebuie să vă gândiți la tabelele de utilizat: ROWID sau WITHOUT ROWID, la etapa de proiectare și dezvoltare. Testele se efectuează cel mai bine după ce baza de date a fost proiectată și implementată. Deoarece nu există diferențe între tabelele cu și fără ROWID, cu excepția celor descrise mai sus, nu.

Un pic despre cum să creați site-uri web și cum să promovați un site web:

Vă recomandăm să vedeți și să citiți:

Mulțumim, a fost foarte ușor descrisă lucrarea indexurilor din RDBMS și, foarte interesant, a scris despre B-copaci. Toate așezate pe rafturi!

Cyril, un moment bun al zilei!

Înțeleg corect că, dacă nu trebuie să fac o selecție din baza de date, este mai bine să dezactivezi indexurile? Numai baza de date MySQL se rotește, nu SQLite3, așa cum scrieți!

Aici este necesar să treceți din situație: dacă într-adevăr nu aveți nevoie de suport pentru integritatea datelor în baze de date și nu este nevoie să faceți interogări SELECT, atunci este mai bine să nu creați indici, deoarece acesta este un loc ocupat suplimentar.

Pagina anterioară

Pagina următoare