Ευρετήρια βάσεων — επιβεβαίωση με πραγματικό σχέδιο ερωτήματος

Πρακτική ανάγκη

Ένας τοπικός κατάλογος αναζητά σημείωση με ακριβή τίτλο. Χωρίς ευρετήριο στη στήλη τίτλου, η SQLite μπορεί να χρειαστεί να εξετάσει κάθε εγγραφή. Ένα κατάλληλο ευρετήριο επιτρέπει στοχευμένη αναζήτηση και, όταν περιέχει όλες τις απαιτούμενες στήλες, καλυπτόμενη προσπέλαση. Τα ευρετήρια καταναλώνουν χώρο και επιβαρύνουν τις εγγραφές, οπότε προστίθενται βάσει πραγματικού φόρτου και μετρήσεων.

Εκτελέσιμο πείραμα SQLite/Python 3

Αποθήκευσε ως index_lab.py και εκτέλεσε python3 index_lab.py. Δημιουργούνται 200 τεχνητές σημειώσεις. Το EXPLAIN QUERY PLAN εμφανίζει το σχέδιο αναζήτησης πριν και μετά τη δημιουργία ευρετηρίου τίτλου.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE docs(id INTEGER PRIMARY KEY,title TEXT NOT NULL)")
db.executemany("INSERT INTO docs(title) VALUES (?)",
               [(f"title-{i}",) for i in range(200)])
sql = "EXPLAIN QUERY PLAN SELECT title FROM docs WHERE title=?"
before = db.execute(sql, ("title-11",)).fetchall()
db.execute("CREATE INDEX idx_docs_title ON docs(title)")
after = db.execute(sql, ("title-11",)).fetchall()
assert any("SCAN docs" in line[3] for line in before), before
assert any("USING COVERING INDEX idx_docs_title" in line[3]
           for line in after), after
print("PASS: scan before index, indexed search afterward")

Σωστή ερμηνεία σχεδίου

Η ένδειξη SCAN docs σημαίνει ότι η SQLite εξετάζει τον πίνακα. Μετά την CREATE INDEX αναμένεται SEARCH ... USING COVERING INDEX idx_docs_title στο συγκεκριμένο SELECT, επειδή το ευρετήριο περιέχει τον τίτλο που ζητάμε. Αυτό δεν αποτελεί γενική μέτρηση ταχύτητας. Για πολύ μικρό πίνακα ο βελτιστοποιητής μπορεί να προτιμήσει σάρωση, ενώ άλλη συνθήκη ή διαφορετικός κανόνας σύγκρισης μπορεί να μην αξιοποιεί το ίδιο ευρετήριο. Ένα μοναδικό ευρετήριο επιβάλλει επίσης μοναδικότητα· το συνηθισμένο ευρετήριο του παραδείγματος όχι.

Ασκήσεις και έλεγχοι

1. Άλλαξε την επιλογή στηλών σε id,title και δες αν το ευρετήριο εξακολουθεί να καλύπτει την αναζήτηση στους πίνακες rowid.
2. Δοκίμασε LIKE με αρχικό σύμβολο μπαλαντέρ και εξήγησε γιατί ένα συνηθισμένο B-tree δύσκολα επιταχύνει αναζήτηση αυθαίρετου τέλους.
3. Μέτρησε κόστος εισαγωγών με και χωρίς ευρετήριο σε τεχνητά δεδομένα, σε τουλάχιστον τρεις επαναλήψεις και με καταγεγραμμένη έκδοση SQLite.
4. Εξέτασε δεύτερο ερώτημα που ταξινομεί κατά τίτλο. Ξεχώρισε ευρετήριο για επίδοση από περιορισμό ορθότητας.

Παραδοτέα: PASS, σχέδια πριν και μετά, ακριβής συνθήκη αναζήτησης και κόστος συντήρησης ευρετηρίου.