Τι είναι το SQL; Το lingua franca της ανάλυσης δεδομένων

Σήμερα, το Structured Query Language είναι το τυπικό μέσο χειρισμού και αναζήτησης δεδομένων σε σχεσιακές βάσεις δεδομένων, αν και με ιδιόκτητες επεκτάσεις μεταξύ των προϊόντων. Η ευκολία και η πανταχού παρουσία του SQL οδήγησαν ακόμη και τους δημιουργούς πολλών "NoSQL" ή μη σχετικών καταστημάτων δεδομένων, όπως το Hadoop, να υιοθετήσουν υποσύνολα SQL ή να δημιουργήσουν τις δικές τους γλώσσες ερωτήσεων τύπου SQL.

Αλλά η SQL δεν ήταν πάντα η «καθολική» γλώσσα για σχεσιακές βάσεις δεδομένων. Από την αρχή (περίπου το 1980), η SQL είχε ορισμένες απεργίες εναντίον της. Πολλοί ερευνητές και προγραμματιστές εκείνη την εποχή, συμπεριλαμβανομένου και εγώ, πίστευαν ότι τα γενικά έξοδα της SQL δεν θα μπορούσαν ποτέ να είναι πρακτικά σε μια βάση δεδομένων παραγωγής.

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

Ιστορικό SQL

Προτού υπάρξει SQL, οι βάσεις δεδομένων είχαν σφιχτές διεπαφές προγραμματισμού πλοήγησης και συνήθως σχεδιάστηκαν γύρω από ένα σχήμα δικτύου που ονομάζεται μοντέλο δεδομένων CODASYL. Η CODASYL (Επιτροπή Γλωσσών Συστημάτων Δεδομένων) ήταν μια κοινοπραξία που ήταν υπεύθυνη για τη γλώσσα προγραμματισμού COBOL (από το 1959) και τις επεκτάσεις γλώσσας βάσης δεδομένων (ξεκινώντας 10 χρόνια αργότερα).

Όταν προγραμματίσατε σε μια βάση δεδομένων CODASYL, πλοηγηθήκατε σε εγγραφές μέσω συνόλων, τα οποία εκφράζουν σχέσεις one-to-many. Οι παλαιότερες ιεραρχικές βάσεις δεδομένων επιτρέπουν μόνο μια εγγραφή να ανήκει σε ένα σύνολο. Οι βάσεις δεδομένων δικτύου επιτρέπουν σε μια εγγραφή να ανήκει σε πολλά σύνολα.

Ας πούμε ότι θέλατε να απαριθμήσετε τους μαθητές που εγγράφηκαν στο CS 101. Πρώτα θα βρείτε "CS 101" στο ΚΥΚΛΟΣ ΜΑΘΗΜΑΤΩΝ ορίστε το όνομα, ορίστε το ως κάτοχος ή γονέας του Εγγραφείς σετ, βρείτε το πρώτο μέλος (ffm) απο Εγγραφείς σύνολο, το οποίο είναι ένα Μαθητης σχολειου εγγραφή και λίστα. Τότε θα μπείτε σε έναν βρόχο: Βρείτε το επόμενο μέλος (fnm) και παραθέστε το. Πότε fnm απέτυχε, θα βγείτε από το βρόχο.

Αυτό μπορεί να μοιάζει πολύ δουλειά για τον προγραμματιστή βάσεων δεδομένων, αλλά ήταν πολύ αποτελεσματικό κατά την εκτέλεση. Εμπειρογνώμονες όπως ο Michael Stonebraker του Πανεπιστημίου της Καλιφόρνια στο Μπέρκλεϊ και ο Ingres επεσήμαναν ότι η πραγματοποίηση αυτού του είδους ερωτήματος σε μια βάση δεδομένων CODASYL όπως το IDMS χρειάστηκε περίπου το μισό χρόνο της CPU και λιγότερο από τη μισή μνήμη με το ίδιο ερώτημα σε μια σχεσιακή βάση δεδομένων χρησιμοποιώντας SQL .

Για σύγκριση, το αντίστοιχο ερώτημα SQL για την επιστροφή όλων των μαθητών στο CS 101 θα ήταν κάτι σαν

ΕΠΙΛΕΞΤΕ μαθητής.name ΑΠΟ μαθήματα, enrollees, μαθητές WHERE course.name

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

Σχεσιακές βάσεις δεδομένων και SQL

Γιατί θα εγκαταλείπατε έναν παράγοντα δύο βελτιώσεων στην ταχύτητα εκτέλεσης και στη χρήση μνήμης; Υπήρχαν δύο μεγάλοι λόγοι: ευκολία ανάπτυξης και φορητότητα. Δεν πίστευα ότι κανένα είχε μεγάλη σημασία το 1980 σε σύγκριση με τις απαιτήσεις απόδοσης και μνήμης, αλλά καθώς το υλικό του υπολογιστή βελτιώθηκε και έγινε φθηνότερο, οι άνθρωποι σταμάτησαν να νοιάζονται για την ταχύτητα εκτέλεσης και τη μνήμη και ανησυχούν περισσότερο για το κόστος ανάπτυξης.

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

Από πού προέρχονται το σχεσιακό μοντέλο και η SQL; Ο EF "Ted" Codd ήταν επιστήμονας υπολογιστών στο IBM San Jose Research Laboratory που επεξεργάστηκε τη θεωρία του σχεσιακού μοντέλου στη δεκαετία του 1960 και το δημοσίευσε το 1970. Η IBM καθυστέρησε να εφαρμόσει μια σχεσιακή βάση δεδομένων σε μια προσπάθεια προστασίας των εσόδων του τη βάση δεδομένων CODASYL IMS / DB. Όταν η IBM ξεκίνησε επιτέλους το έργο System R, η ομάδα ανάπτυξης (Don Chamberlin και Ray Boyce) δεν ήταν υπό τον Codd και αγνόησαν το σχετικό έγγραφο γλώσσας 1971 του Codd για να σχεδιάσουν τη δική τους γλώσσα, SEQUEL (Structured English Query Language). Το 1979, προτού ακόμη κυκλοφορήσει το IBM το προϊόν της, ο Larry Ellison ενσωμάτωσε τη γλώσσα στη βάση δεδομένων του Oracle (χρησιμοποιώντας τις εκδόσεις SEQUEL πριν από την κυκλοφορία της IBM ως προδιαγραφή του). Η SEQUEL έγινε σύντομα SQL για την αποφυγή παραβίασης διεθνούς εμπορικού σήματος.

Το “tom-toms beat for SQL” (όπως το έθεσε ο Michael Stonebraker) προερχόταν όχι μόνο από την Oracle και την IBM, αλλά και από πελάτες. Δεν ήταν εύκολο να προσλάβετε ή να εκπαιδεύσετε σχεδιαστές και προγραμματιστές βάσεων δεδομένων CODASYL, οπότε η SEQUEL (και η SQL) φαινόταν πολύ πιο ελκυστική. Η SQL ήταν τόσο ελκυστική στα τέλη της δεκαετίας του 1980 που πολλοί προμηθευτές βάσεων δεδομένων συσσώρευαν ουσιαστικά έναν επεξεργαστή ερωτημάτων SQL πάνω από τις βάσεις δεδομένων CODASYL, με τη μεγάλη ανησυχία του Codd, ο οποίος θεώρησε ότι οι σχεσιακές βάσεις δεδομένων έπρεπε να σχεδιαστούν από το μηδέν για να είναι σχεσιακές.

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

Η SQL περιλαμβάνει μια υπο-γλώσσα για τον καθορισμό σχημάτων, τη γλώσσα ορισμού δεδομένων (DDL), μαζί με μια υπο-γλώσσα για την τροποποίηση δεδομένων, τη γλώσσα χειρισμού δεδομένων (DML). Και τα δύο έχουν ρίζες στις πρώτες προδιαγραφές CODASYL. Η τρίτη υπο-γλώσσα στο SQL δηλώνει ερωτήματα, μέσω του ΕΠΙΛΕΓΩ δήλωση και σχετικές συνδέσεις.

SQL`ΕΠΙΛΕΓΩ` δήλωση

ο ΕΠΙΛΕΓΩ Η δήλωση λέει στο εργαλείο βελτιστοποίησης ερωτημάτων ποια δεδομένα πρέπει να επιστραφούν, σε ποιους πίνακες πρέπει να κοιτάξετε, σε ποιες σχέσεις θα ακολουθήσετε και ποια σειρά πρέπει να επιβάλλετε στα δεδομένα που επιστρέφονται. Το εργαλείο βελτιστοποίησης ερωτήσεων πρέπει να καταλάβει από μόνος του τι ευρετήρια θα χρησιμοποιήσει για να αποφύγει τη σάρωση brute force table και να επιτύχει καλή απόδοση ερωτήματος, εκτός εάν η συγκεκριμένη βάση δεδομένων υποστηρίζει υποδείξεις ευρετηρίου.

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

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

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

Συζητήσεις του ΕΠΙΛΕΓΩ Η δήλωση μπορεί να ξεκινήσει απλή, αλλά μπορεί γρήγορα να προκαλέσει σύγχυση. Σκεφτείτε:

ΕΠΙΛΟΓΗ * ΑΠΟ Πελάτες.

Απλό, σωστά; Ζητά όλα τα πεδία και όλες τις σειρές του Οι πελάτες τραπέζι. Ας υποθέσουμε, ωστόσο, ότι το Οι πελάτες Ο πίνακας έχει εκατό εκατομμύρια σειρές και εκατό πεδία και ένα από τα πεδία είναι ένα μεγάλο πεδίο κειμένου για σχόλια. Πόσο καιρό θα χρειαστεί να κατεβάσετε όλα αυτά τα δεδομένα μέσω σύνδεσης δικτύου 10 megabit ανά δευτερόλεπτο εάν κάθε σειρά περιέχει κατά μέσο όρο 1 kilobyte δεδομένων;

Ίσως πρέπει να μειώσετε το ποσό που στέλνετε μέσω του καλωδίου. Σκεφτείτε:

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount ΑΠΟ Πελάτες
ΠΟΥ είναι το κράτος ΚΑΙ η πόλη
ΠΑΡΑΓΓΕΛΙΑ ΑΠΟ ΤΟ LastSaleDate ΚΑΤΑΡΓΗΣΗ

Τώρα πρόκειται να κατεβάσετε πολύ λιγότερα δεδομένα. Ζητήσατε από τη βάση δεδομένων να σας δώσει μόνο τέσσερα πεδία, να εξετάσετε μόνο τις εταιρείες στο Κλίβελαντ και να σας δώσει μόνο τις 100 εταιρείες με τις πιο πρόσφατες πωλήσεις. Ωστόσο, για να το κάνετε πιο αποτελεσματικά στο διακομιστή βάσης δεδομένων, το Οι πελάτες ο πίνακας χρειάζεται ένα ευρετήριο πολιτεία + πόλη για το ΟΠΟΥ ρήτρα και ένα ευρετήριο lastSaleDate για το ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ και Κορυφή 100 ρήτρες.

Παρεμπιπτόντως, Κορυφή 100 ισχύει για SQL Server και SQL Azure, αλλά όχι για MySQL ή Oracle. Στο MySQL, θα το χρησιμοποιούσατε ΟΡΙΟ 100 μετά το ΟΠΟΥ ρήτρα. Στο Oracle, θα χρησιμοποιούσατε ένα δεσμευμένο ROWNUM ως μέρος του ΟΠΟΥ ρήτρα, δηλ. ΠΟΥ ... ΚΑΙ ROWNUM <= 100. Δυστυχώς, τα πρότυπα ANSI / ISO SQL (και υπάρχουν εννέα από αυτά μέχρι σήμερα, που εκτείνονται από το 1986 έως το 2016) προχωρούν μόνο, πέρα από το οποίο κάθε βάση δεδομένων εισάγει τις δικές της ιδιόκτητες ρήτρες και δυνατότητες.

Η SQL συμμετέχει

Μέχρι στιγμής, έχω περιγράψει το ΕΠΙΛΕΓΩ σύνταξη για μεμονωμένους πίνακες. Πριν μπορώ να εξηγήσωΣΥΜΜΕΤΟΧΗ ρήτρες, πρέπει να καταλάβετε ξένα κλειδιά και σχέσεις μεταξύ πινάκων. Θα το εξηγήσω χρησιμοποιώντας παραδείγματα στο DDL, χρησιμοποιώντας τη σύνταξη του SQL Server.

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

ΔΗΜΙΟΥΡΓΙΑ ΠΙΝΑΚΑΣ ατόμων (

PersonID int NOT NULL PRIMARY KEY,

Προσωπικό όνομα char (80),

...

Κάθε πίνακα που πρέπει να σχετίζεται Άτομα πρέπει να έχει ένα πεδίο που αντιστοιχεί στο Άτομα πρωτεύον κλειδί και για τη διατήρηση της σχεσιακής ακεραιότητας το πεδίο αυτό πρέπει να έχει περιορισμό ξένου κλειδιού Για παράδειγμα:

ΔΗΜΙΟΥΡΓΙΑ ΠΙΝΑΚΑΣ Παραγγελιών (
    OrderID int NOT NULL PRIMARY KEY,
    ...
    PersonID int FOREIGN KEY REFERENCES Άτομα (PersonID)
);

Υπάρχουν μεγαλύτερες εκδόσεις και των δύο δηλώσεων που χρησιμοποιούν το ΠΕΡΙΟΡΙΣΜΟΣ λέξη-κλειδί, η οποία σας επιτρέπει να ονομάσετε τον περιορισμό. Αυτό δημιουργούν τα περισσότερα εργαλεία σχεδίασης βάσεων δεδομένων.

Τα πρωτεύοντα κλειδιά είναι πάντα ευρετηριασμένα και μοναδικά (οι τιμές πεδίου δεν μπορούν να αναπαραχθούν). Άλλα πεδία μπορούν προαιρετικά να ευρετηριαστούν. Είναι συχνά χρήσιμο να δημιουργείτε ευρετήρια για πεδία ξένου κλειδιού και για πεδία στα οποία εμφανίζονται ΟΠΟΥ και ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ ρήτρες, αν και όχι πάντα, λόγω των πιθανών γενικών εξόδων από τις εγγραφές και τις ενημερώσεις.

Πώς θα γράφατε ένα ερώτημα που θα επιστρέφει όλες τις παραγγελίες του John Doe;

ΕΠΙΛΕΞΤΕ Όνομα Προσωπικού, Παραγγελία ΑΠΟ Άτομα

INNER JOIN Παραγγελίες ON Persons.PersonID = Orders.PersonID

ΠΟΥ ΟΝΟΜΑ ΟΝΟΜΑ;

Στην πραγματικότητα, υπάρχουν τέσσερα είδη ΣΥΜΜΕΤΟΧΗ: ΕΣΩΤΕΡΙΚΟΣ, ΕΞΩΤΕΡΙΚΟΣ, ΑΡΙΣΤΕΡΑ, και ΣΩΣΤΑ. ο ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ είναι η προεπιλογή (μπορείτε να παραλείψετε τη λέξη ΕΣΩΤΕΡΙΚΟΣ) και είναι αυτή που περιλαμβάνει μόνο σειρές που περιέχουν αντίστοιχες τιμές και στους δύο πίνακες. Εάν θέλετε να παραθέσετε άτομα ανεξάρτητα από το αν έχουν παραγγελίες ή όχι, θα πρέπει να χρησιμοποιήσετε ένα ΑΡΙΣΤΕΡΑ ΕΓΓΡΑΦΗ, για παράδειγμα:

ΕΠΙΛΕΞΤΕ Όνομα Προσωπικού, Παραγγελία ΑΠΟ Άτομα

LEFT JOIN Orders ON Persons.PersonID = Παραγγελίες.Προσωπικό

ΠΑΡΑΓΓΕΛΙΑ ΑΝΑ PersonName.

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

Αποθηκευμένες διαδικασίες SQL

Μερικές φορές η δηλωτική φύση του ΕΠΙΛΕΓΩ Η δήλωση δεν σας οδηγεί εκεί που θέλετε να πάτε. Οι περισσότερες βάσεις δεδομένων έχουν μια εγκατάσταση που ονομάζεται αποθηκευμένες διαδικασίες. Δυστυχώς, αυτός είναι ένας τομέας όπου σχεδόν όλες οι βάσεις δεδομένων χρησιμοποιούν ιδιόκτητες επεκτάσεις στα πρότυπα ANSI / ISO SQL.

Στον SQL Server, η αρχική διάλεκτος για αποθηκευμένες διαδικασίες (ή αποθηκευμένες διαδικασίες) ήταν Transact-SQL, γνωστός και ως T-SQL. στο Oracle, ήταν PL-SQL. Και οι δύο βάσεις δεδομένων έχουν προσθέσει επιπλέον γλώσσες για αποθηκευμένες διαδικασίες, όπως C #, Java και R. Μια απλή αποθηκευμένη διαδικασία T-SQL μπορεί να είναι μόνο μια παραμετροποιημένη έκδοση ενός ΕΠΙΛΕΓΩ δήλωση. Τα πλεονεκτήματά του είναι η ευκολία χρήσης και η αποτελεσματικότητα. Οι αποθηκευμένες διαδικασίες βελτιστοποιούνται όταν αποθηκεύονται, όχι κάθε φορά που εκτελούνται.

Μια πιο περίπλοκη αποθηκευμένη διαδικασία T-SQL μπορεί να χρησιμοποιεί πολλές δηλώσεις SQL, παραμέτρους εισόδου και εξόδου, τοπικές μεταβλητές, ΞΕΚΙΝΗΣΤΕ ... ΤΕΛΟΣ μπλοκ, ΕΑΝ ... ΤΟ ... ΑΛΛΟ συνθήκες, δρομείς (επεξεργασία σειράς προς σειρά ενός συνόλου), εκφράσεις, προσωρινοί πίνακες και πλήθος άλλων διαδικαστικών σύνταξης. Προφανώς, εάν η αποθηκευμένη γλώσσα διαδικασίας είναι C #, Java ή R, θα χρησιμοποιήσετε τις συναρτήσεις και τη σύνταξη αυτών των διαδικαστικών γλωσσών. Με άλλα λόγια, παρά το γεγονός ότι το κίνητρο για τη SQL ήταν η χρήση τυποποιημένων δηλωτικών ερωτημάτων, στον πραγματικό κόσμο βλέπετε πολλούς προγραμματισμένους διαδικτυακούς προγραμματιστές διακομιστών.

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