Szeretné felgyorsítani az adatbázis-lekérdezéseket? Ismerje meg, hogyan hozhat létre adatbázis-indexet SQL használatával, és hogyan optimalizálhatja a lekérdezés teljesítményét – és felgyorsíthatja az adatok visszakeresését.
Amikor adatokat kér le egy adatbázistáblából, gyakrabban kell szűrnie bizonyos oszlopok alapján.
Tegyük fel, hogy ír egy SQL-lekérdezést az adatok lekéréséhez meghatározott feltételek alapján. Alapértelmezés szerint a lekérdezés futtatása teljes tábla vizsgálatot hajt végre, amíg a feltételnek megfelelő összes rekordot meg nem találja, majd visszaadja az eredményeket.
Ez rendkívül hatástalan lehet, ha egy nagy, több millió soros adatbázistáblát kell lekérdeznie. Az ilyen lekérdezéseket felgyorsíthatja egy adatbázis-index létrehozásával.
Tartalomjegyzék
Mi az az adatbázis-index?
Ha egy adott kifejezést szeretne megtalálni egy könyvben, végignézi-e a teljes könyvet – egyik oldalt a másik után – és megkeresi az adott kifejezést? Nos, nem.
Ehelyett keresse meg az indexet, hogy megtudja, mely oldalak hivatkoznak a kifejezésre, és közvetlenül azokra az oldalakra ugorjon. Az adatbázisban lévő index nagyjából úgy működik, mint egy könyv indexei.
Az adatbázis-index a tényleges adatokra mutató mutatók vagy hivatkozások halmaza, de úgy vannak rendezve, hogy az adatok gyorsabban lehessen visszakeresni. Belsőleg egy adatbázis-index megvalósítható adatstruktúrák, például B+ fák és hash táblák használatával. Ezért az adatbázis-index javítja az adat-visszakeresési műveletek sebességét és hatékonyságát.
Adatbázis-index létrehozása SQL-ben
Most, hogy tudjuk, mi az adatbázis-index, és hogyan gyorsíthatja fel az adatok visszakeresését, tanuljuk meg, hogyan hozhat létre adatbázis-indexet SQL-ben.
Szűrési műveletek végrehajtásakor – a visszakeresési feltétel WHERE záradékkal történő megadásával – előfordulhat, hogy egy adott oszlopot gyakrabban szeretne lekérdezni, mint másokat.
CREATE INDEX index_name ON table (column)
Itt,
- index_name a létrehozandó index neve
- táblázat a relációs adatbázis táblájára utal
- oszlop az adatbázistábla oszlopának nevére utal, amelyen az indexet létre kell hoznunk.
A követelményektől függően több oszlopon is létrehozhat indexeket – egy többoszlopos indexet. Íme a szintaxis ehhez:
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
Most térjünk át egy gyakorlati példára.
Az adatbázis-index teljesítménynövekedésének megértése
Ahhoz, hogy megértsük az index létrehozásának előnyeit, létre kell hoznunk egy nagy számú rekordot tartalmazó adatbázistáblát. A kódpéldák erre valók SQLite. De használhat más RDBMS-t is, mint például a PostgreSQL és MySQL.
Adatbázistábla feltöltése rekordokkal
A Python beépített véletlenszerű modulját is használhatja rekordok létrehozására és beillesztésére az adatbázisba. Azonban használjuk Hamisító hogy az adatbázistáblát millió sorral töltsük fel.
A következő Python szkript:
- Létrehozza és csatlakozik a customer_db adatbázishoz.
- Hozzon létre egy vevőtáblát a következő mezőkkel: keresztnév, vezetéknév, város és rendelések száma.
- Szintetikus adatokat generál, és adatokat – egymillió rekordot – szúr be az ügyfelek táblájába.
A kódot is megtalálod a GitHubon.
# main.py
# imports
import sqlite3
from faker import Faker
import random
# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()
# create table
db_cursor.execute('''CREATE TABLE customers (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
city TEXT,
num_orders INTEGER)''')
# create a Faker object
fake = Faker()
Faker.seed(27)
# create and insert 1 million records
num_records = 1_000_000
for _ in range(num_records):
first_name = fake.first_name()
last_name = fake.last_name()
city = fake.city()
num_orders = random.randint(0,100)
db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))
# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()
Most elkezdhetjük a lekérdezést.
Index készítése a város oszlopon
Tegyük fel, hogy a város oszlop alapján történő szűréssel szeretné megkapni az ügyféladatokat. A SELECT lekérdezése így fog kinézni:
SELECT column(s) FROM customers WHERE condition;
Tehát hozzuk létre a city_idx értéket a város oszlopában az ügyfelek táblázatában:
CREATE INDEX city_idx ON customers (city);
⚠ Az index létrehozása nem elhanyagolható időt vesz igénybe, és egyszeri művelet. De a teljesítmény előnyökkel jár, ha nagyszámú lekérdezésre van szüksége – a város oszlopon szűrve –, jelentős lesz.
Adatbázis-index törlése
Egy index törléséhez használhatja a DROP INDEX utasítást, például:
DROP INDEX index_name;
A lekérdezési idők összehasonlítása indexszel és index nélkül
Ha lekérdezéseket szeretne futtatni egy Python-szkripten belül, akkor az alapértelmezett időzítőt használhatja a lekérdezések végrehajtási idejének lekéréséhez.
Alternatív megoldásként a lekérdezéseket az sqlite3 parancssori kliens használatával is futtathatja. A customer_db.db parancssori kliens használatával történő kezeléséhez futtassa a következő parancsot a terminálon:
$ sqlite3 customer_db.db;
A hozzávetőleges végrehajtási idők meghatározásához használhatja az sqlite3-ba beépített .timer funkciót, például:
sqlite3 > .timer on
> <query here>
Mivel létrehoztunk egy indexet a város oszlopban, sokkal gyorsabbak lesznek azok a lekérdezések, amelyek a WHERE záradék város oszlopa alapján szűrést igényelnek.
Először futtassa a lekérdezéseket. Ezután hozza létre az indexet, és futtassa újra a lekérdezéseket. Mindkét esetben jegyezze fel a végrehajtási időt. Íme néhány példa:
QueryTime IndexTime nélkül IndexSELECT * FROM ügyfelektől
AHOL város MINT „ÚJ%”
LIMIT 10;0,100 s0,001 sSELECT * ügyfelektől
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * FROM vásárlók
WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0,247 s0,003 s
Látjuk, hogy az indexes lehívási idők több rendeléssel gyorsabbak, mint a város oszlopban index nélküliek.
Adatbázis-indexek létrehozásának és használatának bevált gyakorlatai
Mindig ellenőrizze, hogy a teljesítménynövekedés nagyobb-e, mint az adatbázis-index létrehozásának költsége. Íme néhány bevált gyakorlat, amit érdemes szem előtt tartani:
- Válassza ki a megfelelő oszlopokat az index létrehozásához. Ne hozzon létre túl sok indexet a jelentős többletköltség miatt.
- Minden alkalommal, amikor egy indexelt oszlop frissül, a megfelelő indexet is frissíteni kell. Tehát az adatbázis-index létrehozása (bár felgyorsítja a visszakeresést) jelentősen lelassítja a beillesztési és frissítési műveleteket. Ezért érdemes indexeket létrehozni a gyakran lekérdezett, de ritkán frissített oszlopokon.
Mikor ne készítsen indexet?
Mostanra már rendelkeznie kell azzal, hogy mikor és hogyan kell indexet létrehozni. De mondjuk azt is, hogy mikor nem szükséges az adatbázisindex:
- Ha az adatbázistábla kicsi, és nem tartalmaz nagy számú sort, a teljes tábla vizsgálata az adatok lekéréséhez nem olyan drága.
- Ne hozzon létre indexeket olyan oszlopokon, amelyeket ritkán használnak lekérésre. Ha olyan oszlopokon hoz létre indexeket, amelyeket nem gyakran lekérdeznek, az index létrehozásának és karbantartásának költsége meghaladja a teljesítménynövekedést.
Összegezve
Tekintsük át a tanultakat:
- Amikor lekérdez egy adatbázist adatok lekéréséhez, előfordulhat, hogy bizonyos oszlopok alapján gyakrabban kell szűrni. Egy adatbázis-index az ilyen gyakran lekérdezett oszlopokon javíthatja a teljesítményt.
- Ha egyetlen oszlopon szeretne indexet létrehozni, használja a következő szintaxist: CREATE INDEX index_neve ON táblán (oszlopon). Ha többoszlopos indexet szeretne létrehozni, használja: CREATE INDEX index_name ON táblában (oszlop_1, oszlop_2,…, oszlop_k)
- Amikor egy indexelt oszlopot módosítanak, a megfelelő indexet is frissíteni kell. Ezért válassza ki a megfelelő oszlopokat – gyakran lekérdezett és sokkal ritkábban frissített – index létrehozásához.
- Ha az adatbázistábla viszonylag kisebb, az index létrehozásának, karbantartásának és frissítésének költsége nagyobb lesz, mint a teljesítménynövekedés.
A legtöbb modern adatbázis-kezelő rendszerben van egy lekérdezésoptimalizáló, amely ellenőrzi, hogy egy adott oszlopban lévő index gyorsabbá teszi-e a lekérdezést. Ezután ismerkedjünk meg az adatbázis-tervezés legjobb gyakorlataival.