Cos'è SQL? La lingua franca dell'analisi dei dati

Oggi, Structured Query Language è il mezzo standard per manipolare e interrogare i dati nei database relazionali, sebbene con estensioni proprietarie tra i prodotti. La facilità e l'ubiquità di SQL hanno persino portato i creatori di molti archivi di dati "NoSQL" o non relazionali, come Hadoop, ad adottare sottoinsiemi di SQL oa creare i propri linguaggi di query simili a SQL.

Ma SQL non è sempre stato il linguaggio "universale" per i database relazionali. Fin dall'inizio (circa 1980), SQL ha avuto alcuni attacchi contro di essa. Molti ricercatori e sviluppatori all'epoca, me compreso, pensavano che il sovraccarico di SQL avrebbe impedito che fosse mai pratico in un database di produzione.

Chiaramente, ci sbagliavamo. Ma molti credono ancora che, nonostante tutta la facilità e l'accessibilità di SQL, il prezzo richiesto per le prestazioni di runtime sia spesso troppo alto.

Storia SQL

Prima che esistesse SQL, i database avevano interfacce di programmazione di navigazione strette e in genere erano progettati attorno a uno schema di rete chiamato modello di dati CODASYL. CODASYL (Committee on Data Systems Languages) era un consorzio responsabile del linguaggio di programmazione COBOL (a partire dal 1959) e delle estensioni del linguaggio del database (a partire da 10 anni dopo).

Quando hai programmato su un database CODASYL, stavi navigando verso i record attraverso i set, che esprimono relazioni uno-a-molti. I database gerarchici più vecchi consentono solo a un record di appartenere a un set. I database di rete consentono a un record di appartenere a più set.

Supponiamo che tu voglia elencare gli studenti iscritti a CS 101. Per prima cosa dovresti trovare "CS 101"nel Coursesset per nome, impostarlo come proprietario o genitore del Enrolleesset, trovare il primo membro ( ffm) del Enrolleesset, che è un Studentrecord, e esso. Quindi andresti in un ciclo: Trova il membro successivo ( fnm) ed elencalo. Se fnmfallito, usciresti dal ciclo.

Questo può sembrare un sacco di lavoro per il programmatore di database, ma è stato molto efficiente al momento dell'esecuzione. Esperti come Michael Stonebraker dell'Università della California a Berkeley e Ingres hanno sottolineato che eseguire quel tipo di query in un database CODASYL come IDMS richiedeva circa la metà del tempo della CPU e meno della metà della memoria della stessa query su un database relazionale utilizzando SQL .

Per confronto, la query SQL equivalente per restituire tutti gli studenti in CS 101 sarebbe qualcosa di simile 

SELEZIONA student.name FROM corsi, iscritti, studenti WHERE course.name

Questa sintassi implica un inner join relazionale (in realtà due di essi), come spiegherò di seguito, e tralascia alcuni dettagli importanti, come i campi utilizzati per i join.

Database relazionali e SQL

Perché rinunciare a un fattore di due miglioramenti nella velocità di esecuzione e nell'uso della memoria? C'erano due grandi ragioni: facilità di sviluppo e portabilità. Non pensavo che nessuno dei due importasse molto nel 1980 rispetto alle prestazioni e ai requisiti di memoria, ma man mano che l'hardware del computer migliorava e diventava più economico, le persone smisero di preoccuparsi della velocità di esecuzione e della memoria e si preoccuparono maggiormente dei costi di sviluppo.

In altre parole, la legge di Moore ha ucciso i database CODASYL a favore dei database relazionali. Come è successo, il miglioramento del tempo di sviluppo è stato significativo, ma la portabilità SQL si è rivelata un sogno irrealizzabile.

Da dove vengono il modello relazionale e SQL? EF "Ted" Codd era un informatico presso l'IBM San Jose Research Laboratory che ha elaborato la teoria del modello relazionale negli anni '60 e l'ha pubblicata nel 1970. IBM è stata lenta nell'implementare un database relazionale nel tentativo di proteggere i ricavi di il suo database CODASYL IMS / DB. Quando IBM ha finalmente avviato il suo progetto System R, il team di sviluppo (Don Chamberlin e Ray Boyce) non era sotto Codd e hanno ignorato il documento in linguaggio relazionale Alpha del 1971 di Codd per progettare il proprio linguaggio, SEQUEL (Structured English Query Language). Nel 1979, prima ancora che IBM avesse rilasciato il suo prodotto, Larry Ellison incorporò il linguaggio nel suo database Oracle (utilizzando le pubblicazioni SEQUEL pre-lancio di IBM come sua specifica). SEQUEL divenne presto SQL per evitare una violazione del marchio internazionale.

Il "tam-tam che batte per SQL" (come disse Michael Stonebraker) proveniva non solo da Oracle e IBM, ma anche dai clienti. Non è stato facile assumere o addestrare progettisti e programmatori di database CODASYL, quindi SEQUEL (e SQL) sembravano molto più attraenti. SQL era così attraente negli ultimi anni '80 che molti fornitori di database fondamentalmente attaccavano un processore di query SQL sopra i loro database CODASYL, con grande sgomento di Codd, che sentiva che i database relazionali dovevano essere progettati da zero per essere relazionali.

Un database relazionale puro, come progettato da Codd, è costruito su tuple raggruppate in relazioni, coerenti con la logica dei predicati del primo ordine. I database relazionali del mondo reale hanno tabelle che contengono campi, vincoli e trigger e le tabelle sono correlate tramite chiavi esterne. SQL viene utilizzato per dichiarare i dati da restituire e un Query Processor SQL e un Query Optimizer trasformano la dichiarazione SQL in un piano di query eseguito dal motore di database.

SQL include un linguaggio secondario per la definizione degli schemi, il linguaggio di definizione dei dati (DDL), insieme a un linguaggio secondario per la modifica dei dati, il linguaggio di manipolazione dei dati (DML). Entrambi hanno radici nelle prime specifiche CODASYL. La terza lingua secondaria in SQL dichiara le query, tramite l' SELECTistruzione e le join relazionali.

SQL  SELECTdichiarazione

L' SELECTistruzione indica a Query Optimizer quali dati restituire, quali tabelle cercare, quali relazioni seguire e quale ordine imporre ai dati restituiti. Query Optimizer deve capire da solo quali indici utilizzare per evitare scansioni di tabelle a forza bruta e ottenere buone prestazioni di query, a meno che il database specifico non supporti i suggerimenti sugli indici.

Parte dell'arte della progettazione di database relazionali dipende dall'uso giudizioso degli indici. Se ometti un indice per una query frequente, l'intero database può rallentare sotto carichi di lettura pesanti. Se si dispone di troppi indici, l'intero database può rallentare con carichi pesanti di scrittura e aggiornamento.

Un'altra arte importante è scegliere una buona chiave primaria unica per ogni tavolo. Non solo devi considerare l'impatto della chiave primaria sulle query comuni, ma come giocherà nei join quando appare come chiave esterna in un'altra tabella e come influenzerà la località di riferimento dei dati.

Nel caso avanzato di tabelle di database suddivise in volumi diversi a seconda del valore della chiave primaria, chiamato partizionamento orizzontale, devi anche considerare come la chiave primaria influenzerà il partizionamento orizzontale. Suggerimento: si desidera che la tabella sia distribuita uniformemente tra i volumi, il che suggerisce che non si desidera utilizzare indicatori di data o numeri interi consecutivi come chiavi primarie.

Le discussioni sulla SELECTdichiarazione possono iniziare in modo semplice, ma possono rapidamente creare confusione. Ritenere:

SELEZIONA * DA Clienti;

Semplice, vero? Richiede tutti i campi e tutte le righe della Customerstabella. Si supponga, tuttavia, che la Customerstabella contenga cento milioni di righe e cento campi e uno dei campi sia un grande campo di testo per i commenti. Quanto tempo ci vorrà per estrarre tutti quei dati su una connessione di rete da 10 megabit al secondo se ogni riga contiene una media di 1 kilobyte di dati?

Forse dovresti ridurre quanto invii sul filo. Ritenere:

SELEZIONA TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers

DOVE stato E città

ORDER BY lastSaleDate DESCENDING;

Ora tirerai giù molti meno dati. Hai chiesto al database di fornirti solo quattro campi, di considerare solo le società di Cleveland e di darti solo le 100 società con le vendite più recenti. Per fare ciò in modo più efficiente sul server del database, tuttavia, la Customerstabella necessita di un indice state+cityper la WHEREclausola e un indice lastSaleDateper le clausole ORDER BYe TOP 100.

A proposito, TOP 100è valido per SQL Server e SQL Azure, ma non per MySQL o Oracle. In MySQL, useresti LIMIT 100dopo la WHEREclausola. In Oracle, useresti un limite ROWNUMcome parte della WHEREclausola, ad es WHERE... AND ROWNUM <=100. Sfortunatamente, gli standard ANSI / ISO SQL (e ce ne sono nove fino ad oggi, dal 1986 al 2016) vanno solo così lontano, oltre il quale ogni database introduce le proprie clausole e funzionalità proprietarie.

SQL si unisce 

Finora, ho descritto la SELECTsintassi per le singole tabelle. Prima che possa spiegare le  JOINclausole, è necessario comprendere le chiavi esterne e le relazioni tra le tabelle. Lo spiegherò utilizzando esempi in DDL, utilizzando la sintassi di SQL Server.

La versione breve di questo è abbastanza semplice. Ogni tabella che vuoi usare nelle relazioni dovrebbe avere un vincolo di chiave primaria; può essere un singolo campo o una combinazione di campi definiti da un'espressione. Per esempio:

CREA TABELLA Persone (

    PersonID int NOT NULL PRIMARY KEY,

    PersonName char (80),

    ...

Ogni tabella a cui è necessario fare riferimento Personsdovrebbe avere un campo che corrisponde alla Personschiave primaria e per preservare l'integrità relazionale quel campo dovrebbe avere un vincolo di chiave esterna. Per esempio:

CREA TABELLA Ordini (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int RIFERIMENTI CHIAVE ESTERI Persone (PersonID)

);

Esistono versioni più lunghe di entrambe le istruzioni che utilizzano la CONSTRAINTparola chiave, che consente di denominare il vincolo. Questo è ciò che genera la maggior parte degli strumenti di progettazione di database.

Le chiavi primarie sono sempre indicizzate e univoche (i valori dei campi non possono essere duplicati). Altri campi possono essere facoltativamente indicizzati. È spesso utile creare indici per i campi della chiave esterna e per i campi che compaiono nelle clausole WHEREe ORDER BY, sebbene non sempre, a causa del potenziale sovraccarico di scritture e aggiornamenti.

Come scrivereste una query che restituisca tutti gli ordini effettuati da John Doe?

SELEZIONA PersonName, OrderID FROM Persons

INNER JOIN Orders ON Persons.PersonID = Orders.PersonID

WHERE PersonName;

In realtà, ci sono quattro tipi di JOIN: INNER, OUTER, LEFT, e RIGHT. Il INNER JOINè l'impostazione predefinita (è possibile omettere la parola INNER), ed è quella che include solo le righe che contengono i valori corrispondenti in entrambe le tabelle. Se desideri elencare le persone indipendentemente dal fatto che abbiano o meno ordini, dovresti utilizzare un LEFT JOIN, ad esempio:

SELEZIONA PersonName, OrderID FROM Persons

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

ORDER BY PersonName;

Quando inizi a eseguire query che uniscono più di due tabelle, che usano espressioni o che costringono i tipi di dati, la sintassi può diventare un po 'complicata all'inizio. Fortunatamente, esistono strumenti di sviluppo di database in grado di generare query SQL corrette, spesso trascinando e rilasciando tabelle e campi dal diagramma dello schema in un diagramma di query.

Procedure memorizzate SQL

A volte la natura dichiarativa SELECTdell'affermazione non ti porta dove vuoi andare. La maggior parte dei database dispone di una funzionalità denominata stored procedure; sfortunatamente questa è un'area in cui quasi tutti i database utilizzano estensioni proprietarie agli standard ANSI / ISO SQL.

In SQL Server, il dialetto iniziale per le stored procedure (o stored procedure) era Transact-SQL, noto anche come T-SQL; in Oracle, era PL-SQL. Entrambi i database hanno aggiunto linguaggi aggiuntivi per le procedure memorizzate, come C #, Java e R. Una semplice procedura memorizzata T-SQL potrebbe essere solo una versione parametrizzata di SELECTun'istruzione. I suoi vantaggi sono la facilità d'uso e l'efficienza. Le stored procedure vengono ottimizzate quando vengono salvate, non ogni volta che vengono eseguite.

Una procedura memorizzata T-SQL più complicata potrebbe utilizzare più istruzioni SQL, parametri di input e output, variabili locali, BEGIN...ENDblocchi, IF...THEN...ELSEcondizioni, cursori (elaborazione riga per riga di un set), espressioni, tabelle temporanee e tutta una serie di altri sintassi procedurale. Ovviamente se il linguaggio della procedura memorizzata è C #, Java o R, utilizzerai le funzioni e la sintassi di quei linguaggi procedurali. In altre parole, nonostante il fatto che la motivazione per SQL fosse quella di utilizzare query dichiarative standardizzate, nel mondo reale si vede un sacco di programmazione server procedurale specifica del database.

Questo non ci riporta ai vecchi tempi della programmazione di database CODASYL (sebbene i cursori si avvicinino), ma torna indietro dall'idea che le istruzioni SQL dovrebbero essere standardizzate e che i problemi di prestazioni dovrebbero essere lasciati all'ottimizzatore di query del database . Alla fine, un raddoppio delle prestazioni è spesso troppo da lasciare sul tavolo.

Impara SQL

I siti elencati di seguito possono aiutarti a imparare l'SQL o scoprire le stranezze di vari dialetti SQL.