[Explained] Πώς να δημιουργήσετε ένα ευρετήριο βάσης δεδομένων σε SQL

από
Tweet
Share
Share
Pin

Θέλετε να επιταχύνετε τα ερωτήματα της βάσης δεδομένων; Μάθετε πώς να δημιουργείτε ένα ευρετήριο βάσης δεδομένων χρησιμοποιώντας SQL και να βελτιστοποιείτε την απόδοση των ερωτημάτων και να επιταχύνετε την ανάκτηση δεδομένων.

Όταν ανακτάτε δεδομένα από έναν πίνακα βάσης δεδομένων, θα πρέπει να φιλτράρετε με βάση συγκεκριμένες στήλες πιο συχνά.

Ας υποθέσουμε ότι γράφετε ένα ερώτημα SQL για να ανακτήσετε τα δεδομένα με βάση συγκεκριμένες συνθήκες. Από προεπιλογή, η εκτέλεση του ερωτήματος εκτελεί μια σάρωση πλήρους πίνακα μέχρι να βρεθούν όλες οι εγγραφές που ικανοποιούν τη συνθήκη και στη συνέχεια να επιστρέψει τα αποτελέσματα.

Αυτό μπορεί να είναι εξαιρετικά αναποτελεσματικό όταν πρέπει να ρωτήσετε έναν μεγάλο πίνακα βάσης δεδομένων με πολλά εκατομμύρια σειρές. Μπορείτε να επιταχύνετε τέτοια ερωτήματα δημιουργώντας ένα ευρετήριο βάσης δεδομένων.

Τι είναι ο δείκτης βάσης δεδομένων;

Όταν θέλετε να βρείτε έναν συγκεκριμένο όρο σε ένα βιβλίο, θα κάνετε μια σάρωση πλήρους βιβλίου —τη μία σελίδα μετά την άλλη— αναζητώντας τον συγκεκριμένο όρο; Λοιπόν, δεν το κάνετε.

Αντίθετα, θα αναζητήσετε το ευρετήριο για να μάθετε ποιες σελίδες αναφέρονται στον όρο και θα μεταβείτε κατευθείαν σε αυτές τις σελίδες. Ένα ευρετήριο σε μια βάση δεδομένων λειτουργεί πολύ όπως τα ευρετήρια σε ένα βιβλίο.

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

Δημιουργία ευρετηρίου βάσης δεδομένων σε SQL

Τώρα που γνωρίζουμε τι είναι το ευρετήριο βάσης δεδομένων και πώς μπορεί να επιταχύνει την ανάκτηση δεδομένων, ας μάθουμε πώς να δημιουργήσουμε ένα ευρετήριο βάσης δεδομένων σε SQL.

  Πώς να εγκαταστήσετε το Beautiful Soup και να το χρησιμοποιήσετε για έργα απόξεσης Ιστού;

Όταν εκτελείτε λειτουργίες φιλτραρίσματος —καθορίζοντας τη συνθήκη ανάκτησης χρησιμοποιώντας έναν όρο WHERE—μπορεί να θέλετε να υποβάλετε ερωτήματα σε μια συγκεκριμένη στήλη πιο συχνά από άλλες.

CREATE INDEX index_name ON table (column)

Εδώ,

  • index_name είναι το όνομα του ευρετηρίου που θα δημιουργηθεί
  • Ο πίνακας αναφέρεται στον πίνακα στη σχεσιακή βάση δεδομένων
  • στήλη αναφέρεται στο όνομα της στήλης στον πίνακα της βάσης δεδομένων στην οποία πρέπει να δημιουργήσουμε το ευρετήριο.

Μπορείτε επίσης να δημιουργήσετε ευρετήρια σε πολλές στήλες—ένα ευρετήριο πολλών στηλών—ανάλογα με τις απαιτήσεις. Ακολουθεί η σύνταξη για να το κάνετε αυτό:

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

Τώρα ας προχωρήσουμε σε ένα πρακτικό παράδειγμα.

Κατανόηση των κερδών απόδοσης του δείκτη βάσης δεδομένων

Για να κατανοήσουμε το πλεονέκτημα της δημιουργίας ενός ευρετηρίου, πρέπει να δημιουργήσουμε έναν πίνακα βάσης δεδομένων με μεγάλο αριθμό εγγραφών. Τα παραδείγματα κώδικα είναι για SQLite. Αλλά μπορείτε επίσης να χρησιμοποιήσετε άλλα RDBMS της επιλογής σας, όπως PostgreSQL και MySQL.

Συμπλήρωση πίνακα βάσεων δεδομένων με εγγραφές

Μπορείτε επίσης να χρησιμοποιήσετε την ενσωματωμένη τυχαία ενότητα της Python για να δημιουργήσετε και να εισαγάγετε εγγραφές στη βάση δεδομένων. Ωστόσο, θα χρησιμοποιήσουμε Απατεών για να συμπληρώσετε τον πίνακα της βάσης δεδομένων με ένα εκατομμύριο σειρές.

Το ακόλουθο σενάριο Python:

  • Δημιουργεί και συνδέεται με τη βάση δεδομένων customer_db.
  • Δημιουργήστε έναν πίνακα πελατών με τα πεδία: first_name, last_name, city και num_orders.
  • Δημιουργεί συνθετικά δεδομένα και εισάγει δεδομένα —ένα εκατομμύριο εγγραφές— στον πίνακα πελατών.

Μπορείτε επίσης να βρείτε τον κωδικό στο GitHub.

# 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()

Τώρα μπορούμε να αρχίσουμε να ρωτάμε.

  Είναι μόνιμες οι απαγορεύσεις Grindr;

Δημιουργία ευρετηρίου στη στήλη της πόλης

Ας υποθέσουμε ότι θέλετε να λάβετε τις πληροφορίες του πελάτη φιλτράροντας με βάση τη στήλη της πόλης. Το ερώτημά σας SELECT θα μοιάζει με αυτό:

SELECT column(s) FROM customers
WHERE condition;

Ας δημιουργήσουμε λοιπόν το city_idx στη στήλη της πόλης στον πίνακα πελατών:

CREATE INDEX city_idx ON customers (city);

⚠ Η δημιουργία ενός ευρετηρίου απαιτεί έναν μη αμελητέο χρόνο και είναι μια λειτουργία εφάπαξ. Ωστόσο, τα οφέλη απόδοσης όταν χρειάζεστε μεγάλο αριθμό ερωτημάτων—φιλτράροντας στη στήλη της πόλης—θα είναι σημαντικά.

Διαγραφή ευρετηρίου βάσης δεδομένων

Για να διαγράψετε ένα ευρετήριο, μπορείτε να χρησιμοποιήσετε τη δήλωση DROP INDEX ως εξής:

DROP INDEX index_name;

Σύγκριση χρόνων ερωτημάτων με και χωρίς ευρετήριο

Εάν θέλετε να εκτελέσετε ερωτήματα μέσα σε ένα σενάριο Python, μπορείτε να χρησιμοποιήσετε το προεπιλεγμένο χρονόμετρο για να λάβετε τους χρόνους εκτέλεσης για τα ερωτήματα.

Εναλλακτικά, μπορείτε να εκτελέσετε τα ερωτήματα χρησιμοποιώντας τον πελάτη γραμμής εντολών sqlite3. Για να εργαστείτε με τον customer_db.db χρησιμοποιώντας τον πελάτη γραμμής εντολών, εκτελέστε την ακόλουθη εντολή στο τερματικό:

$ sqlite3 customer_db.db;

Για να λάβετε τους κατά προσέγγιση χρόνους εκτέλεσης, μπορείτε να χρησιμοποιήσετε τη λειτουργία .timer που είναι ενσωματωμένη στο sqlite3 όπως:

sqlite3 > .timer on
        > <query here>

Επειδή έχουμε δημιουργήσει ένα ευρετήριο στη στήλη της πόλης, τα ερωτήματα που περιλαμβάνουν φιλτράρισμα με βάση τη στήλη της πόλης στον όρο WHERE θα είναι πολύ πιο γρήγορα.

Πρώτα, εκτελέστε τα ερωτήματα. Στη συνέχεια, δημιουργήστε το ευρετήριο και εκτελέστε ξανά τα ερωτήματα. Σημειώστε τους χρόνους εκτέλεσης και στις δύο περιπτώσεις. Να μερικά παραδείγματα:

QueryTime χωρίς IndexTime με IndexSELECT * ΑΠΟ πελάτες
ΟΠΟΥ η πόλη ΣΑΝ ‘Νέο%’
LIMIT 10;0,100 s0,001 sSELECT * ΑΠΟ πελάτες
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * ΑΠΟ πελάτες
WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0,247 s0,003 s

Βλέπουμε ότι οι χρόνοι ανάκτησης με ευρετήριο είναι αρκετές παραγγελίες ταχύτεροι από αυτούς χωρίς ευρετήριο στη στήλη της πόλης.

  10 Εργαλεία Οπτικοποίησης Cloud για AWS, Azure, GCP και άλλα

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

Θα πρέπει πάντα να ελέγχετε εάν τα κέρδη απόδοσης είναι μεγαλύτερα από τα γενικά έξοδα δημιουργίας ενός ευρετηρίου βάσης δεδομένων. Ακολουθούν ορισμένες βέλτιστες πρακτικές που πρέπει να έχετε κατά νου:

  • Επιλέξτε τις σωστές στήλες για να δημιουργήσετε ένα ευρετήριο. Αποφύγετε τη δημιουργία πάρα πολλών ευρετηρίων λόγω των σημαντικών γενικών εξόδων.
  • Κάθε φορά που ενημερώνεται μια στήλη με ευρετήριο, θα πρέπει να ενημερώνεται και το αντίστοιχο ευρετήριο. Έτσι, η δημιουργία ενός ευρετηρίου βάσης δεδομένων (αν και επιταχύνει την ανάκτηση) επιβραδύνει σημαντικά τις εργασίες εισαγωγής και ενημέρωσης. Επομένως, θα πρέπει να δημιουργήσετε ευρετήρια σε στήλες που υποβάλλονται συχνά ερωτήματα αλλά σπάνια ενημερώνονται.

Πότε δεν πρέπει να δημιουργήσετε ευρετήριο;

Μέχρι τώρα θα πρέπει να έχετε μια ιδέα για το πότε και πώς να δημιουργήσετε ένα ευρετήριο. Αλλά ας αναφέρουμε επίσης πότε το ευρετήριο βάσης δεδομένων μπορεί να μην είναι απαραίτητο:

  • Όταν ο πίνακας της βάσης δεδομένων είναι μικρός και δεν περιέχει μεγάλο αριθμό σειρών, η σάρωση πλήρους πίνακα για την ανάκτηση δεδομένων δεν είναι τόσο δαπανηρή.
  • Μην δημιουργείτε ευρετήρια σε στήλες που χρησιμοποιούνται σπάνια για ανάκτηση. Όταν δημιουργείτε ευρετήρια σε στήλες που δεν υποβάλλονται συχνά ερωτήματα, το κόστος δημιουργίας και διατήρησης ενός ευρετηρίου υπερτερεί των κερδών απόδοσης.

Ανακεφαλαίωση

Ας δούμε τι μάθαμε:

  • Όταν υποβάλλετε ερώτημα σε μια βάση δεδομένων για την ανάκτηση δεδομένων, ίσως χρειαστεί να φιλτράρετε με βάση ορισμένες στήλες πιο συχνά. Ένα ευρετήριο βάσης δεδομένων σε τέτοιες στήλες με συχνά ερωτήματα μπορεί να βελτιώσει την απόδοση.
  • Για να δημιουργήσετε ένα ευρετήριο σε μία στήλη, χρησιμοποιήστε τη σύνταξη: CREATE INDEX index_name ON πίνακα (στήλη). Εάν θέλετε να δημιουργήσετε ένα ευρετήριο πολλών στηλών, χρησιμοποιήστε: CREATE INDEX index_name ON πίνακα (στήλη_1, στήλη_2,…,στήλη_κ)
  • Κάθε φορά που τροποποιείται μια στήλη με ευρετήριο, θα πρέπει να ενημερώνεται και το αντίστοιχο ευρετήριο. Επομένως, επιλέξτε τις σωστές στήλες—που υποβάλλονται συχνά ερωτήματα και πολύ λιγότερο συχνά ενημερώνονται—για να δημιουργήσετε ένα ευρετήριο.
  • Εάν ο πίνακας της βάσης δεδομένων είναι σχετικά μικρότερος, το κόστος δημιουργίας, διατήρησης και ενημέρωσης ενός ευρετηρίου θα είναι μεγαλύτερο από τα κέρδη απόδοσης.

Στα περισσότερα σύγχρονα συστήματα διαχείρισης βάσεων δεδομένων, υπάρχει ένα εργαλείο βελτιστοποίησης ερωτημάτων που ελέγχει εάν ένα ευρετήριο σε μια συγκεκριμένη στήλη θα κάνει το ερώτημα να εκτελείται πιο γρήγορα. Στη συνέχεια, ας μάθουμε τις βέλτιστες πρακτικές για το σχεδιασμό της βάσης δεδομένων.