Čo je to SQL? Jazyková analýza údajov

Dnes je jazyk štruktúrovaných dotazov štandardným prostriedkom na manipuláciu a dopytovanie údajov v relačných databázach, aj keď s proprietárnymi rozšíreniami medzi produktmi. Ľahkosť a všadeprítomnosť SQL dokonca viedli tvorcov mnohých „NoSQL“ alebo nerelačných dátových skladov, ako je Hadoop, k tomu, aby si osvojili podmnožiny SQL alebo prišli s vlastnými dotazovacími jazykmi podobnými SQL.

SQL však nebol vždy „univerzálnym“ jazykom pre relačné databázy. Od začiatku (okolo roku 1980) mala spoločnosť SQL určité štrajky proti nej. Veľa vtedajších výskumníkov a vývojárov, vrátane mňa, si myslelo, že réžia systému SQL zabráni tomu, aby bol v produkčnej databáze niekedy praktický.

Je zrejmé, že sme sa mýlili. Mnoho ľudí však stále verí, že pre všetku ľahkosť a dostupnosť SQL je cena požadovaná za behu modulu často príliš vysoká.

História SQL

Pred zavedením jazyka SQL mali databázy úzke navigačné programovacie rozhrania a zvyčajne sa navrhovali okolo sieťovej schémy nazývanej dátový model CODASYL. CODASYL (Výbor pre jazyky dátových systémov) bolo konzorcium zodpovedné za programovací jazyk COBOL (od roku 1959) a rozšírenia databázových jazykov (od 10 rokov neskôr).

Keď ste programovali proti databáze CODASYL, prechádzali ste k záznamom prostredníctvom množín, ktoré vyjadrujú vzájomné vzťahy. Staršie hierarchické databázy umožňujú, aby záznam patril iba do jednej množiny. Sieťové databázy umožňujú, aby záznam patril do viacerých sád.

Povedzme, že chcete uviesť zoznam študentov zaradených do CS 101. Najprv by ste našli „CS 101“ v Kurzy nastaviť podľa mena, nastaviť ako vlastníka alebo rodiča súboru Zapísaní set, nájdi prvého člena (ffm) z Zapísaní set, čo je a Študent zaznamenať a uviesť ich v zozname. Potom by ste išli do slučky: Nájsť ďalšieho člena (fnm) a uveďte ich. Kedy fnm zlyhalo, opustili by ste slučku.

Pre databázového programátora sa to môže javiť ako veľa práce, ale v čase vykonania bolo veľmi efektívne. Odborníci ako Michael Stonebraker z Kalifornskej univerzity v Berkeley a Ingres poukázali na to, že vykonanie tohto druhu dotazu v databáze CODASYL, ako je IDMS, trvalo zhruba polovicu času CPU a menej ako polovicu pamäte ako rovnaký dopyt v relačnej databáze pomocou SQL .

Pre porovnanie, ekvivalentný dotaz SQL na vrátenie všetkých študentov v CS 101 by bol niečo ako

VYBERTE student.name z kurzov, prihlásených, študentov WHERE course.name

Táto syntax implikuje relačné vnútorné spojenie (v skutočnosti dva z nich), ako vysvetlím nižšie, a vynecháva niektoré dôležité podrobnosti, napríklad polia použité na spojenie.

Relačné databázy a SQL

Prečo by ste sa mali vzdať dvojnásobného zlepšenia v rýchlosti vykonávania a využití pamäte? Boli to dva veľké dôvody: ľahký vývoj a prenosnosť. V roku 1980 som si nemyslel, že by na jednom z nich záležalo v porovnaní s požiadavkami na výkon a pamäť, ale keď sa hardvér počítača zlepšil a stal sa lacnejším, ľudia sa prestali zaujímať o rýchlosť a pamäť vykonávania a viac sa obávajú nákladov na vývoj.

Inými slovami, Moorov zákon zabil databázy CODASYL v prospech relačných databáz. Ako sa stalo, zlepšenie času vývoja bolo výrazné, ale prenosnosť SQL sa ukázala ako sen potrubia.

Odkiaľ sa vzal relačný model a SQL? EF „Ted“ Codd bol počítačový vedec vo výskumnom laboratóriu IBM San Jose, ktorý v 60. rokoch 20. storočia vypracoval teóriu relačného modelu a publikoval ju v roku 1970. IBM implementovala relačnú databázu pomaly v snahe chrániť príjmy z svoju databázu CODASYL IMS / DB. Keď spoločnosť IBM konečne zahájila svoj projekt System R, vývojový tím (Don Chamberlin a Ray Boyce) nebol pod Coddom a ignorovali Codtovu knihu o relačných jazykoch z roku 1971 Alpha, aby navrhli svoj vlastný jazyk, SEQUEL (Structured English Query Language). V roku 1979, predtým ako spoločnosť IBM uviedla na trh svoj produkt, začlenil Larry Ellison jazyk do svojej databázy Oracle (ako špecifikáciu použil publikácie SEQUEL pred uvedením na trh). SEQUEL sa čoskoro stal SQL, aby nedošlo k porušeniu medzinárodnej ochrannej známky.

„Tom-tomy bitie pre SQL“ (ako povedal Michael Stonebraker) prichádzali nielen od spoločností Oracle a IBM, ale aj od zákazníkov. Nebolo ľahké najať alebo vyškoliť návrhárov a programátorov databáz CODASYL, takže SEQUEL (a SQL) vyzerali oveľa atraktívnejšie. V neskorších 80. rokoch bol SQL natoľko atraktívny, že mnoho predajcov databáz v podstate našlo nad svojimi databázami CODASYL procesor dotazov SQL, a to na veľké zdesenie Codda, ktorý cítil, že relačné databázy musia byť koncipované úplne od začiatku, aby boli relačné.

Čistá relačná databáza navrhnutá Coddom je postavená na n-ticiach zoskupených do vzťahov, v súlade s predikátovou logikou prvého rádu. Reálne databázy reálneho sveta obsahujú tabuľky, ktoré obsahujú polia, obmedzenia a spúšťače, a tabuľky súvisia prostredníctvom cudzích kľúčov. Na deklaráciu údajov, ktoré sa majú vrátiť, sa používa program SQL a procesor dotazov SQL a optimalizátor dotazov urobia z deklarácie SQL dotazový plán, ktorý vykoná databázový stroj.

SQL obsahuje podjazyk na definovanie schém, jazyk definície údajov (DDL), spolu s podjazdom na úpravu údajov, jazyk na manipuláciu s údajmi (DML). Oba majú korene v raných špecifikáciách CODASYL. Tretí podjazyk v jazyku SQL deklaruje dotazy prostredníctvom servera VYBERTE vyhlásenie a relačné spojenia.

SQL`VYBERTE` vyhlásenie

The VYBERTE Príkaz hovorí optimalizátoru dotazov, aké údaje sa majú vrátiť, aké tabuľky sa majú pozrieť, aké vzťahy sa majú riadiť a aké poradie uložiť vráteným údajom. Optimalizátor dotazov musí sám zistiť, aké indexy sa majú použiť, aby sa zabránilo skenovaniu tabuľky hrubej sily a dosiahol sa dobrý výkon dotazu, pokiaľ konkrétna databáza nepodporuje tipy na index.

Časť umenia návrhu relačnej databázy závisí od uvážlivého používania indexov. Ak pri častých dotazoch vynecháte index, môže sa celá databáza spomaliť pri vysokom načítaní. Ak máte príliš veľa indexov, môže sa celá databáza spomaliť pri vysokom zaťažení zápisu a aktualizácií.

Ďalším dôležitým umením je výber dobrého, jedinečného primárneho kľúča pre každú tabuľku. Musíte nielen vziať do úvahy vplyv primárneho kľúča na bežné dotazy, ale aj to, ako bude hrať v spojeniach, keď sa v inej tabuľke objaví ako cudzí kľúč, a ako to ovplyvní referenčnú lokalitu údajov.

V rozšírenom prípade databázových tabuliek, ktoré sú rozdelené na rôzne zväzky v závislosti od hodnoty primárneho kľúča, nazývaného horizontálne rozdelenie, musíte tiež zvážiť, ako primárny kľúč ovplyvní rozdelenie. Tip: Chcete, aby bola tabuľka distribuovaná rovnomerne medzi zväzky, čo naznačuje, že ako primárne kľúče nechcete používať dátumové pečiatky alebo po sebe idúce celé čísla.

Diskusie o VYBERTE vyhlásenie môže začať jednoduché, ale môže byť rýchlo mätúce. Zvážte:

VYBERTE * OD ZÁKAZNÍKOV;

Jednoduché, však? Pýta sa na všetky polia a všetky riadky súboru Zákazníci stôl. Predpokladajme však, že Zákazníci tabuľka má sto miliónov riadkov a sto polí a jedným z polí je veľké textové pole pre komentáre. Ako dlho bude trvať, kým sa všetky tieto údaje načítajú cez sieťové pripojenie s rýchlosťou 10 megabitov za sekundu, ak každý riadok obsahuje priemerne 1 kilobajt dát?

Možno by ste mali znížiť množstvo, ktoré pošlete cez drôt. Zvážte:

VYBERTE TOP 100 názov spoločnosti, lastSaleDate, lastSaleAmount, totalSalesAmount OD ZÁKAZNÍKOV

KDE štát a mesto

OBJEDNAŤ PODĽA lastSaleDate POPIS;

Teraz budete zhromažďovať oveľa menej údajov. Požiadali ste databázu, aby vám poskytla iba štyri polia, aby ste vzali do úvahy iba spoločnosti v Clevelande a aby vám ponúkli iba 100 spoločností s najnovším predajom. Najefektívnejšie to však je na databázovom serveri Zákazníci tabuľka potrebuje index na štát + mesto pre KDE doložka a index na lastSaleDate pre ZORADIŤ PODĽA a TOP 100 doložky.

Mimochodom, TOP 100 je platný pre SQL Server a SQL Azure, ale nie pre MySQL alebo Oracle. V MySQL by ste použili OBMEDZENIE 100 po KDE doložka. V Oracle by ste použili viazané na ROWNUM ako súčasť KDE klauzula, t.j. KDE ... A ROWNUM <= 100. Bohužiaľ, štandardy ANSI / ISO SQL (a je ich doposiaľ deväť, siahajúcich od roku 1986 do roku 2016) zachádzajú iba tak ďaleko, nad rámec ktorých každá databáza zavádza svoje vlastné doložky a vlastnosti.

SQL sa pripája

Doteraz som popisoval VYBERTE syntax pre jednotlivé tabuľky. Skôr ako to vysvetlímPRIPOJTE SA klauzuly, musíte rozumieť cudzím kľúčom a vzťahom medzi tabuľkami. Vysvetlím to na príkladoch v DDL pomocou syntaxe servera SQL Server.

Krátka verzia je pomerne jednoduchá. Každá tabuľka, ktorú chcete použiť vo vzťahoch, by mala mať obmedzenie primárneho kľúča; môže to byť buď jedno pole, alebo kombinácia polí definovaných výrazom. Napríklad:

VYTVORIŤ TABUĽKU Osoby (
    PersonID int NIE NULL PRIMÁRNY KLÍČ,
    Char. Meno osoby (80),
    ...

Každá tabuľka, ktorej sa treba týkať Osoby by mal mať pole, ktoré zodpovedá Osoby primárny kľúč a aby sa zachovala relačná integrita, malo by mať toto pole obmedzenie cudzím kľúčom. Napríklad:

VYTVORIŤ TABUĽKOVÉ objednávky (
    IDObjednávky NIE NULL PRIMÁRNY KLÍČ,
    ...
    PersonID int ZAHRANIČNÉ KĽÚČOVÉ REFERENCIE Osoby (PersonID)
);

Existujú dlhšie verzie oboch príkazov, ktoré používajú znak OBMEDZENIE kľúčové slovo, ktoré vám umožní pomenovať obmedzenie. To generuje väčšina nástrojov na návrh databázy.

Primárne kľúče sú vždy indexované a jedinečné (hodnoty polí nemožno duplikovať). Ostatné polia môžu byť voliteľne indexované. Často je užitočné vytvárať indexy pre polia cudzích kľúčov a pre polia, ktoré sa vyskytujú v KDE a ZORADIŤ PODĽA doložky, aj keď nie vždy, z dôvodu možnej réžie zápisov a aktualizácií.

Ako by ste napísali dopyt, ktorý vráti všetky objednávky, ktoré zadal John Doe?

VYBERTE meno_osoby, ID_objednávky od osôb

INNER JOIN Objednávky ON Persons.PersonID = Objednávky.PersonID

KDE Meno osoby;

V skutočnosti existujú štyri druhy PRIPOJTE SA: VNÚTORNÉ, VONKAJŠIE, VĽAVOa SPRÁVNY. The VNÚTORNÉ PRIPOJENIE je predvolené (slovo môžete vynechať VNÚTORNÉ) a je to ten, ktorý obsahuje iba riadky, ktoré obsahujú zhodné hodnoty v oboch tabuľkách. Ak chcete uviesť zoznam osôb bez ohľadu na to, či majú alebo nemajú objednávky, použijete a VĽAVO SA PRIPOJTE, napríklad:

VYBERTE meno osoby, ID objednávky od osôb

LEFT JOIN Objednávky ON Persons.PersonID = Objednávky.PersonID

OBJEDNAŤ PODĽA Meno osoby;

Keď začnete robiť dotazy, ktoré spájajú viac ako dve tabuľky, používajú výrazy alebo nútia dátové typy, syntax môže byť spočiatku trochu chlpatá. Našťastie existujú nástroje na vývoj databáz, ktoré za vás dokážu vygenerovať správne dotazy SQL, a to často pretiahnutím tabuliek a polí zo schémy do schémy dotazov.

Uložené procedúry SQL

Niekedy má deklaratívny charakter VYBERTE vyhlásenie vás nedostane tam, kam chcete. Väčšina databáz má zariadenie s názvom uložené procedúry; toto je bohužiaľ oblasť, kde takmer všetky databázy používajú proprietárne rozšírenia štandardov ANSI / ISO SQL.

Na serveri SQL Server bol počiatočným dialektom pre uložené procedúry (alebo uložené procs) Transact-SQL, alias T-SQL; v Oracle to bol PL-SQL. Obe databázy pridali ďalšie jazyky pre uložené procedúry, napríklad C #, Java a R. Jednoduchá uložená procedúra T-SQL môže byť iba parametrizovanou verziou VYBERTE vyhlásenie. Jeho výhodou je jednoduché použitie a efektívnosť. Uložené procedúry sa optimalizujú, keď sa uložia, nie vždy, keď sa vykonajú.

Komplikovanejšia uložená procedúra T-SQL môže používať viac príkazov SQL, vstupné a výstupné parametre, lokálne premenné, ZAČAŤ ... KONIEC bloky, AK ... POTOM ... INDE podmienky, kurzory (spracovanie množiny po riadkoch), výrazy, dočasné tabuľky a celý rad ďalších procedurálnych syntaxí. Je zrejmé, že ak je jazykom uloženej procedúry C #, Java alebo R, budete používať funkcie a syntax týchto procedurálnych jazykov. Inými slovami, napriek skutočnosti, že motiváciou pre SQL bolo použitie štandardizovaných deklaratívnych dotazov, v reálnom svete vidíte veľa procedurálneho programovania servera špecifického pre databázu.

To nás celkom nevráti do starých zlých čias programovania databázy CODASYL (hoci sa kurzory blížia), ale vychádza to z myšlienok, že by príkazy SQL mali byť štandardizované a že problémy s výkonom by mali byť ponechané na optimalizátor databázových dotazov . Nakoniec je zdvojnásobenie výkonu často príliš veľa na to, aby ste ho nechali na stole.