Kaj je SQL? Lingua franca analize podatkov

Danes je jezik strukturiranih poizvedb standardno sredstvo za manipulacijo in poizvedovanje po podatkih v relacijskih zbirkah podatkov, čeprav ima lastniške razširitve med izdelki. Enostavnost in razširjenost SQL sta celo ustvarila ustvarjalce številnih "NoSQL" ali nerelacijskih shramb podatkov, kot je Hadoop, da so sprejeli podnabore SQL ali pripravili svoje poizvedbene jezike, podobne SQL.

Toda SQL ni bil vedno "univerzalni" jezik za relacijske baze podatkov. Od samega začetka (približno leta 1980) je imel SQL določene stavke. Številni raziskovalci in razvijalci v tistem času, vključno z mano, so menili, da zaradi obremenitve SQL ne bo nikoli praktičen v produkcijski bazi podatkov.

Jasno je, da smo se zmotili. Toda mnogi še vedno verjamejo, da je zaradi vse enostavnosti in dostopnosti SQL cena, ki jo zahtevajo zmogljivosti med izvajanjem, pogosto previsoka.

Zgodovina SQL

Preden je obstajal SQL, so imele baze podatkov tesne navigacijske programske vmesnike in so bile običajno zasnovane okoli omrežne sheme, imenovane podatkovni model CODASYL. CODASYL (Odbor za jezike podatkovnih sistemov) je bil konzorcij, ki je bil odgovoren za programski jezik COBOL (od leta 1959) in razširitve jezikov baz podatkov (od 10 let kasneje).

Ko ste programirali proti zbirki podatkov CODASYL, ste se do zapisov pomikali prek nizov, ki izražajo razmerja ena proti več. Starejše hierarhične zbirke podatkov omogočajo, da zapis pripada samo enemu nizu. Omrežne podatkovne baze omogočajo, da zapis pripada več naborom.

Recimo, da ste želeli našteti študente, vpisane v CS 101. Najprej bi našli "CS 101" v Tečaji nastavite z imenom, nastavite kot lastnika ali nadrejenega Vpisani nastavite, poiščite prvega člana (ffm) od Vpisani niz, ki je a Študent zapis in ga navedite. Potem bi šli v zanko: Poiščite naslednjega člana (fnm) in ga navedite. Kdaj fnm če ni uspelo, bi zapustili zanko.

To se morda zdi veliko preiskovalnega dela za programerja baz podatkov, vendar je bilo v času izvajanja zelo učinkovito. Strokovnjaki, kot sta Michael Stonebraker z Kalifornijske univerze v Berkeleyju in Ingres, so poudarili, da je takšna poizvedba v bazi podatkov CODASYL, kot je IDMS, vzela približno polovico CPU-ja in manj kot polovica pomnilnika kot ista poizvedba v relacijski bazi podatkov z uporabo SQL .

Za primerjavo bi bila enakovredna poizvedba SQL za vrnitev vseh študentov v CS 101 podobna

IZBERI ime študenta.Iz tečajev, vpisanih, študentov KJE tečaj.ime

Ta sintaksa pomeni relacijsko notranje združevanje (pravzaprav dve od njih), kot bom razložil spodaj, in izpušča nekatere pomembne podrobnosti, na primer polja, uporabljena za združevanja.

Relacijske baze podatkov in SQL

Zakaj bi se odrekli dvakratnemu izboljšanju hitrosti izvajanja in uporabe pomnilnika? Bila sta dva velika razloga: enostavnost razvoja in prenosljivost. Leta 1980 nisem mislil, da je bil eden pomemben v primerjavi z zahtevami glede zmogljivosti in pomnilnika, toda ko se je računalniška strojna oprema izboljševala in postala cenejša, so ljudje prenehali skrbeti za hitrost izvajanja in pomnilnik ter bolj skrbeli za stroške razvoja.

Z drugimi besedami, Mooreov zakon je ubil baze podatkov CODASYL v korist relacijskih baz podatkov. Kot se je zgodilo, je bilo izboljšanje časa razvoja veliko, toda prenosljivost SQL se je izkazala za sanjske sanje.

Od kod relacijski model in SQL? EF “Ted” Codd je bil računalničar v raziskovalnem laboratoriju IBM San Jose, ki je teorijo relacijskega modela razvil v šestdesetih letih in objavil leta 1970. IBM je počasi uvajal relacijsko bazo podatkov, da bi zaščitil prihodke svojo bazo podatkov CODASYL IMS / DB. Ko je IBM končno začel s projektom System R, razvojna skupina (Don Chamberlin in Ray Boyce) ni bila pod vodstvom Codda in so ignorirali Codd-ov referativni jezikovni jezik Alfa iz leta 1971, da bi oblikovali svoj jezik SEQUEL (Structured English Query Language). Leta 1979, preden je IBM sploh izdal svoj izdelek, je Larry Ellison vključil jezik v svojo bazo podatkov Oracle (za specifikacijo je uporabil IBM-ove publikacije SEQUEL pred zagonom). SEQUEL je kmalu postal SQL, da bi se izognil mednarodni kršitvi blagovne znamke.

"Tom-tomsovi bitki za SQL" (kot je rekel Michael Stonebraker) niso prihajali samo od Oracle in IBM, ampak tudi od strank. Ni bilo lahko najeti ali usposobiti oblikovalcev in programerjev baz podatkov CODASYL, zato je bil SEQUEL (in SQL) videti veliko bolj privlačen. SQL je bil v poznih osemdesetih letih tako privlačen, da so številni prodajalci baz podatkov na svoje baze podatkov CODASYL v bistvu speli procesor poizvedb SQL, kar je na veliko razočaranje Codda, ki je menil, da je treba relacijske baze podatkov zasnovati iz nič, da bi bile relacijske.

Čista relacijska baza podatkov, kot jo je zasnoval Codd, je zgrajena na naborih, združenih v relacije, v skladu s predikatno logiko prvega reda. Realne relacijske baze podatkov imajo tabele, ki vsebujejo polja, omejitve in sprožilce, tabele pa so povezane s tujimi ključi. SQL se uporablja za razglasitev podatkov, ki jih je treba vrniti, in procesor poizvedb SQL in optimizator poizvedb pretvorijo izjavo SQL v načrt poizvedbe, ki ga izvede mehanizem baze podatkov.

SQL vključuje podjezik za definiranje shem, jezik definicije podatkov (DDL), skupaj s podjezikom za spreminjanje podatkov, jezikom za obdelavo podatkov (DML). Oba imata korenine v zgodnjih specifikacijah CODASYL. Tretji podjezik v SQL izjavi poizvedbe prek IZBERI izjava in relacijski združevanja.

SQL`IZBERI` izjavo

The IZBERI stavek pove optimizatorju poizvedb, katere podatke naj vrne, katere tabele naj si ogleda, katere relacije naj sledi in kakšen vrstni red naj namesti vrnjenim podatkom. Orodje za optimiziranje poizvedb mora sam ugotoviti, katere indekse naj uporabi, da se izogne skeniranju tabel surove sile in doseže dobro zmogljivost poizvedbe, razen če določena baza podatkov podpira namige indeksa.

Del umetnosti relacijskega oblikovanja baz podatkov temelji na razumni uporabi indeksov. Če izpustite indeks za pogosto poizvedbo, se lahko celotna baza podatkov upočasni pri velikih obremenitvah branja. Če imate preveč indeksov, se lahko celotna baza podatkov upočasni pri velikih obremenitvah pisanja in posodabljanja.

Druga pomembna umetnost je izbira dobrega, edinstvenega primarnega ključa za vsako mizo. Upoštevati morate ne samo vpliv primarnega ključa na pogoste poizvedbe, temveč tudi to, kako se bo predvajal v združevanju, ko se v drugi tabeli pojavi kot tuji ključ, in kako bo vplival na referenčno lokacijo podatkov.

V naprednem primeru tabel baz podatkov, ki so razdeljene na različne nosilce, odvisno od vrednosti primarnega ključa, imenovanega vodoravno ostrenje, morate razmisliti tudi o tem, kako bo primarni ključ vplival na ostrenje. Namig: želite, da se tabela enakomerno porazdeli po zvezkih, kar pomeni, da ne želite uporabljati datumskih žigov ali zaporednih celih števil kot primarnih ključev.

Razprave IZBERI izjava se lahko začne preprosto, vendar lahko hitro postane zmedena. Razmislite:

IZBERITE * OD kupcev;

Preprosto, kajne? Zahteva vsa polja in vse vrstice Stranke tabela. Recimo, da Stranke tabela ima sto milijonov vrstic in sto polj, eno od polj pa je veliko besedilno polje za komentarje. Koliko časa bo trajalo, da se vsi ti podatki zberejo prek omrežne povezave 10 megabitov na sekundo, če vsaka vrstica vsebuje povprečno 1 kilobajt podatkov?

Morda bi morali zmanjšati, koliko pošljete po žici. Razmislite:

IZBERITE TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount OD STRANK

KJE država in mesto

NAROČI PO LastSaleDate PO POPUSTU;

Zdaj boste potegnili veliko manj podatkov. Zbirko podatkov ste prosili, da vam posreduje samo štiri polja, da upošteva samo podjetja v Clevelandu in samo 100 podjetij z najnovejšo prodajo. Za najučinkovitejše izvajanje tega na strežniku baz podatkov pa Stranke tabela potrebuje indeks na država + mesto za KJE klavzula in indeks na lastSaleDate za NAROČITE PO in TOP 100 klavzule.

Mimogrede, TOP 100 velja za SQL Server in SQL Azure, ne pa tudi za MySQL ali Oracle. V MySQL bi uporabili OMEJITEV 100 Po KJE klavzulo. V Oracle bi uporabili vezavo naprej VREDNOST kot del KJE klavzulo, tj. KJE ... IN ŠIRINA <= 100. Na žalost standardi ANSI / ISO SQL (in doslej jih je devet, ki segajo od leta 1986 do 2016) segajo le tako daleč, preko katerih vsaka baza podatkov uvaja svoje lastniške klavzule in funkcije.

SQL se pridruži

Do zdaj sem opisal IZBERI sintaksa za posamezne tabele. Preden lahko razložimPRIDRUŽITE SE določbe, morate razumeti tuje ključe in odnose med tabelami. To bom razložil s primeri v DDL z uporabo sintakse SQL Server.

Kratka različica tega je dokaj preprosta. Vsaka tabela, ki jo želite uporabiti v odnosih, mora imeti omejitev primarnega ključa; to je lahko eno polje ali kombinacija polj, ki jih definira izraz. Na primer:

USTVARI TABELO Osebe (
    PersonID int NOT NULL PRIMARNI KLJUČ,
    Ime osebe (80),
    ...

Vsaka tabela, na katero se je treba povezati Osebe mora imeti polje, ki ustreza Osebe primarni ključ, in da se ohrani relacijska celovitost, mora to polje imeti omejitev tujega ključa. Na primer:

USTVARI NAROČILA Naročila (
    OrderID int NOT NULL PRIMARNI KLJUČ,
    ...
    PersonID int ZUNANJI KLJUČNI LITERATURI Persons (PersonID)
);

Obstajajo daljše različice obeh stavkov, ki uporabljata OMEJITEV ključna beseda, ki vam omogoča poimenovanje omejitve. To ustvarja večina orodij za oblikovanje baz podatkov.

Primarni ključi so vedno indeksirani in enolični (vrednosti polj ni mogoče podvojiti). Druga polja lahko poljubno indeksiramo. Pogosto je koristno ustvariti indekse za polja tujih ključev in polja, ki se pojavijo v KJE in NAROČITE PO klavzule, čeprav ne vedno, zaradi morebitnih režijskih stroškov zaradi zapisov in posodobitev.

Kako bi napisali poizvedbo, ki vrne vsa naročila, ki jih je dal John Doe?

IZBERI Ime osebe, ID naročila od oseb

INNER JOIN Naročila na Persons.PersonID = Naročila.PersonID

KJE Ime osebe;

V resnici obstajajo štiri vrste PRIDRUŽITE SE: NOTRANJOST, ZUNANJI, LEVO, in PRAV. The INNER JOIN je privzeta (besedo lahko izpustite NOTRANJOST), in to tista, ki vključuje samo vrstice, ki vsebujejo ujemajoče se vrednosti v obeh tabelah. Če želite našteti osebe, ne glede na to, ali imajo naročila ali ne, uporabite a LEVO PRIDRUŽITE, na primer:

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Naročila na Persons.PersonID = Naročila.PersonID
NAROČILO PO OSEBI;

Ko začnete izvajati poizvedbe, ki združujejo več kot dve tabeli, ki uporabljajo izraze ali prisilne tipe podatkov, lahko sintaksa najprej postane nekoliko poraščena. Na srečo obstajajo orodja za razvoj baz podatkov, ki lahko za vas ustvarijo pravilne poizvedbe SQL, pogosto tako, da tabele in polja povlečete in spustite iz diagrama sheme v diagram poizvedb.

Shranjene procedure SQL

Včasih izjava o IZBERI izjava vas ne pripelje tja, kamor želite iti. Večina baz podatkov ima objekt, imenovan shranjene procedure; na žalost je to področje, kjer skoraj vse baze podatkov uporabljajo lastniške razširitve standardov ANSI / ISO SQL.

V strežniku SQL Server je bilo začetno narečje shranjenih postopkov (ali shranjenih procesov) Transact-SQL, aka T-SQL; v Oracle je bil PL-SQL. V obe zbirki podatkov sta dodani dodatni jeziki za shranjene procedure, kot so C #, Java in R. Preprosta shranjena procedura T-SQL je lahko le parametrizirana različica IZBERI izjavo. Njegove prednosti so enostavnost uporabe in učinkovitost. Shranjeni postopki so optimizirani, ko se shranijo, ne vsakič, ko se izvedejo.

Bolj zapleteni shranjeni postopek T-SQL lahko uporablja več stavkov SQL, vhodne in izhodne parametre, lokalne spremenljivke, ZAČNITE ... KONEC bloki, ČE ... TEDA ... DRUGAČE pogoji, kazalci (obdelava nabora po vrstah), izrazi, začasne tabele in celo vrsto drugih procesnih sintaks. Če je jezik shranjenega postopka C #, Java ali R, boste očitno uporabili funkcije in sintakso teh proceduralnih jezikov. Z drugimi besedami, kljub dejstvu, da je bila motivacija za SQL uporaba standardiziranih deklarativnih poizvedb, v resničnem svetu vidite veliko postopkovnega programiranja strežnikov, specifičnega za bazo podatkov.

To nas sicer ne vrne v slabe stare čase programiranja baz podatkov CODASYL (čeprav se kazalci približujejo), vendar se oddaljuje od idej, da bi morali biti stavki SQL standardizirani in da bi bilo treba pomisleke glede učinkovitosti prepustiti optimizatorju poizvedb po zbirki podatkov. . Na koncu je podvojitev zmogljivosti pogosto preveč, da bi jo pustili na mizi.