Programmering

Hvad er SQL? Lingua franca af dataanalyse

I dag er Structured Query Language standardmetoden til at manipulere og spørge data i relationelle databaser, dog med proprietære udvidelser blandt produkterne. SQL's lethed og allestedsnærværelse har endda ført skaberne af mange “NoSQL” eller ikke-relationelle datalagre, såsom Hadoop, til at vedtage undersæt af SQL eller komme med deres egne SQL-lignende forespørgselssprog.

Men SQL var ikke altid det "universelle" sprog for relationsdatabaser. Fra starten (omkring 1980) havde SQL visse strejker mod det. Mange forskere og udviklere på det tidspunkt, inklusive mig, troede, at omkostningerne ved SQL ville forhindre det i at være praktisk i en produktionsdatabase.

Vi tog helt klart fejl. Men mange mener stadig, at den pris, der kræves i runtime-ydeevne, ofte er for høj på grund af SQL's lethed og tilgængelighed.

SQL-historie

Før der var SQL, havde databaser stramme navigationsprogrammeringsgrænseflader og blev typisk designet omkring et netværksskema kaldet CODASYL-datamodellen. CODASYL (Committee on Data Systems Languages) var et konsortium, der var ansvarlig for COBOL-programmeringssproget (startende i 1959) og databasesprogudvidelser (startende 10 år senere).

Når du programmerede mod en CODASYL-database, navigerede du til poster gennem sæt, der udtrykker en-til-mange-forhold. Ældre hierarkiske databaser tillader kun, at en post tilhører et sæt. Netværksdatabaser tillader, at en post tilhører flere sæt.

Sig, at du ville liste de studerende, der var tilmeldt CS 101. Først ville du finde "CS 101" i Kurser angivet efter navn, skal du angive det som ejer eller forælder til Tilmeldte sæt, find det første medlem (ffm) af Tilmeldte sæt, hvilket er en Studerende optag, og noter det. Derefter går du ind i en løkke: Find næste medlem (fnm) og anfør det. Hvornår fnm mislykkedes, ville du forlade sløjfen.

Det kan virke som meget scut-arbejde for databaseprogrammereren, men det var meget effektivt på eksekveringstidspunktet. Eksperter som Michael Stonebraker fra University of California i Berkeley og Ingres påpegede, at udførelse af den slags forespørgsel i en CODASYL-database som IDMS tog omtrent halvdelen af ​​CPU-tiden og mindre end halvdelen af ​​hukommelsen som den samme forespørgsel på en relationsdatabase ved hjælp af SQL .

Til sammenligning ville den tilsvarende SQL-forespørgsel om at returnere alle eleverne i CS 101 være noget lignende 

VÆLG student.navn FRA kurser, tilmeldte, studerende HVOR kursusnavn

Denne syntaks indebærer en relationel indre sammenføjning (faktisk to af dem), som jeg vil forklare nedenfor, og udelader nogle vigtige detaljer, såsom de felter, der bruges til sammenføjningerne.

Relationsdatabaser og SQL

Hvorfor ville du opgive en faktor på to forbedringer i eksekveringshastighed og hukommelsesforbrug? Der var to store grunde: let udvikling og bærbarhed. Jeg troede ikke, at nogen havde noget meget i 1980 sammenlignet med ydeevne og hukommelseskrav, men da computerhardware forbedredes og blev billigere, stoppede folk med at bekymre sig om udførelseshastighed og hukommelse og bekymrede sig mere om omkostningerne ved udvikling.

Med andre ord dræbte Moores lov CODASYL-databaser til fordel for relationsdatabaser. Da det skete, var forbedringen i udviklingstid betydelig, men SQL-bærbarhed viste sig at være en rørdrøm.

Hvor kom relationsmodellen og SQL fra? EF “Ted” Codd var en computerforsker ved IBM San Jose Research Laboratory, der udarbejdede teorien om relationsmodellen i 1960'erne og offentliggjorde den i 1970. IBM var langsom med at implementere en relationsdatabase i et forsøg på at beskytte indtægterne fra dets CODASYL-database IMS / DB. Da IBM endelig startede sit System R-projekt, var udviklingsteamet (Don Chamberlin og Ray Boyce) ikke under Codd, og de ignorerede Codds 1971 Alpha relationelle sprogpapir for at designe deres eget sprog, SEQUEL (Structured English Query Language). I 1979, inden IBM overhovedet havde frigivet sit produkt, inkorporerede Larry Ellison sproget i sin Oracle-database (ved hjælp af IBMs præ-lancering af SEQUEL-publikationer som hans spec). SEQUEL blev hurtigt SQL for at undgå en international varemærkeovertrædelse.

De “tom-toms, der slår for SQL” (som Michael Stonebraker udtrykte det) kom ikke kun fra Oracle og IBM, men også fra kunder. Det var ikke let at ansætte eller uddanne CODASYL-databasedesignere og -programmerere, så SEQUEL (og SQL) så meget mere attraktivt ud. SQL var så attraktivt i de senere 1980'ere, at mange databaseleverandører i det væsentlige hæftede en SQL-forespørgselsprocessor oven på deres CODASYL-databaser til stor forfærdelse for Codd, der mente, at relationsdatabaser skulle designes fra bunden for at være relationelle.

En ren relationsdatabase, som designet af Codd, er bygget på tupler grupperet i relationer, i overensstemmelse med førsteordens prædikatlogik. Virkelige relationsdatabaser har tabeller, der indeholder felter, begrænsninger og udløsere, og tabeller er relateret via udenlandske nøgler. SQL bruges til at erklære data, der skal returneres, og en SQL-forespørgselsprocessor og forespørgselsoptimeringsværktøj gør SQL-erklæringen til en forespørgselsplan, der udføres af databasemotoren.

SQL inkluderer et undersprog til at definere skemaer, datadefinitionssproget (DDL) sammen med et undersprog til ændring af data, datalagringssprog (DML). Begge disse har rødder i tidlige CODASYL-specifikationer. Det tredje undersprog i SQL erklærer forespørgsler gennem VÆLG erklæring og relationel sammenføjning.

SQLVÆLG udmelding

Det VÆLG erklæring fortæller forespørgselsoptimeringsværktøjet, hvilke data der skal returneres, hvilke tabeller man skal se i, hvilke forhold der skal følges, og hvilken rækkefølge de skal returnere på de returnerede data. Forespørgselsoptimeringsværktøjet skal i sig selv finde ud af, hvilke indekser der skal bruges for at undgå brute force-tabel-scanninger og opnå god forespørgsel, medmindre den bestemte database understøtter indeks-tip.

En del af kunsten med relationel databasedesign hænger sammen med den fornuftige brug af indekser. Hvis du udelader et indeks for en hyppig forespørgsel, kan hele databasen blive langsommere under tunge læse belastninger. Hvis du har for mange indekser, kan hele databasen blive langsommere under tunge skrive- og opdateringsbelastninger.

En anden vigtig kunst er at vælge en god, unik primær nøgle til hvert bord. Du skal ikke kun overveje den primære nøgles indvirkning på almindelige forespørgsler, men hvordan den vil spille sammen, når den vises som en fremmed nøgle i en anden tabel, og hvordan det vil påvirke datas referenceplads.

I det avancerede tilfælde af databasetabeller, der er opdelt i forskellige volumener afhængigt af værdien af ​​den primære nøgle, kaldet vandret sharding, skal du også overveje, hvordan den primære nøgle vil påvirke sharding. Tip: Du ønsker, at tabellen skal fordeles jævnt på diskenheder, hvilket antyder, at du ikke vil bruge datostempler eller fortløbende heltal som primære nøgler.

Diskussioner af VÆLG udsagn kan starte simpelt, men kan hurtigt blive forvirrende. Overveje:

VÆLG * FRA KUNDER;

Simpelt, ikke? Det beder om alle felter og alle rækker i Kunder bord. Antag dog, at Kunder tabellen har hundrede millioner rækker og hundrede felter, og et af felterne er et stort tekstfelt til kommentarer. Hvor lang tid tager det at trække alle disse data ned over en 10 megabit per sekund netværksforbindelse, hvis hver række indeholder et gennemsnit på 1 kilobyte data?

Måske skal du skære ned, hvor meget du sender over ledningen. Overveje:

VÆLG TOP 100 firmanavn, lastSaleDate, lastSaleAmount, totalSalesAmount FRA KUNDER

HVOR staten og byen

ORDRE EFTER sidste Salgsdato FALDENDE;

Nu vil du trække meget mindre data ned. Du har bedt databasen kun give dig fire felter, kun overveje virksomhederne i Cleveland og give dig kun de 100 virksomheder med det seneste salg. At gøre det mest effektivt på databaseserveren dog Kunder tabellen har brug for et indeks på stat + by til HVOR klausul og et indeks til sidste salgsdato til BESTIL og TOPP 100 klausuler.

I øvrigt, TOPP 100 er gyldig til SQL Server og SQL Azure, men ikke MySQL eller Oracle. I MySQL vil du bruge GRÆNSE 100 efter HVOR klausul. I Oracle vil du bruge en bundet til ROWNUM som en del af HVOR klausul, dvs. HVOR ... OG ROWNUM <= 100. Desværre går ANSI / ISO SQL-standarderne (og der er ni af dem til dato, der strækker sig fra 1986 til 2016), kun så langt, ud over hvilke hver database introducerer sine egne proprietære klausuler og funktioner.

SQL slutter sig til

Indtil videre har jeg beskrevet VÆLG syntaks for enkeltborde. Før jeg kan forklareTILSLUTTE klausuler, skal du forstå fremmede nøgler og forholdet mellem tabeller. Jeg forklarer dette ved hjælp af eksempler i DDL ved hjælp af SQL Server-syntaks.

Den korte version af dette er ret enkel. Hver tabel, som du vil bruge i relationer, skal have en primær nøglebegrænsning; dette kan enten være et enkelt felt eller en kombination af felter defineret af et udtryk. For eksempel:

Opret TABEL Personer (

PersonID int IKKE NULL PRIMÆR NØGLE,

PersonName char (80),

    ...

Hvert bord, der skal relateres til Personer skal have et felt, der svarer til Personer primærnøgle og for at bevare relationsintegritet skal dette felt have en fremmed nøglebegrænsning. For eksempel:

Opret bordordrer (

OrderID int IKKE NULL PRIMÆR NØGLE,

    ...

PersonID int UDENLANDSKE KEY REFERENCER Personer (PersonID)

);

Der er længere versioner af begge udsagn, der bruger KONTRAST nøgleord, som lader dig navngive begrænsningen. Det er, hvad de fleste databasedesignværktøjer genererer.

Primære nøgler er altid indekseret og entydige (feltværdierne kan ikke duplikeres). Andre felter kan valgfrit indekseres. Det er ofte nyttigt at oprette indekser for udenlandske nøglefelter og for felter, der vises i HVOR og BESTIL klausuler, men ikke altid, på grund af den potentielle omkostning ved skrivning og opdatering.

Hvordan ville du skrive en forespørgsel, der returnerer alle ordrer, der er afgivet af John Doe?

VÆLG Personnavn, OrderID FRA personer

INNER JOIN Bestillinger PÅ Persons.PersonID = Orders.PersonID

HVOR Personnavn;

Faktisk er der fire slags TILSLUTTE: INDRE, YDRE, VENSTREog RET. Det INDRE MEDLEM er standard (du kan udelade ordet INDRE), og det er den, der kun indeholder rækker, der indeholder matchende værdier i begge tabeller. Hvis du vil liste personer, uanset om de har ordrer eller ej, skal du bruge en VENSTRE MEDLEM, for eksempel:

VÆLG Personnavn, OrderID FRA personer

VENSTRE JOIN Bestillinger PÅ Persons.PersonID = Orders.PersonID

BESTIL EFTER Personnavn;

Når du begynder at lave forespørgsler, der slutter sig til mere end to tabeller, der bruger udtryk, eller som tvinger datatyper, kan syntaksen blive lidt behåret i starten. Heldigvis er der databaseudviklingsværktøjer, der kan generere korrekte SQL-forespørgsler for dig, ofte ved at trække og slippe tabeller og felter fra skemadiagrammet til et forespørgselsdiagram.

SQL lagrede procedurer

Undertiden den erklærende karakter af VÆLG erklæring får dig ikke, hvor du vil hen. De fleste databaser har en facilitet kaldet lagrede procedurer; desværre er dette et område, hvor næsten alle databaser bruger proprietære udvidelser til ANSI / ISO SQL-standarder.

I SQL Server var den oprindelige dialekt til lagrede procedurer (eller lagrede procs) Transact-SQL, også kaldet T-SQL; i Oracle var det PL-SQL. Begge databaser har tilføjet yderligere sprog til lagrede procedurer, såsom C #, Java og R. En simpel T-SQL-lagret procedure er muligvis kun en parametreret version af en VÆLG udmelding. Dens fordele er brugervenlighed og effektivitet. Gemte procedurer optimeres, når de gemmes, ikke hver gang de udføres.

En mere kompliceret T-SQL-lagret procedure kan bruge flere SQL-sætninger, input- og outputparametre, lokale variabler, BEGIN ... SLUT blokke, HVIS ... SÅ ... ANDET betingelser, markører (række for række behandling af et sæt), udtryk, midlertidige tabeller og en lang række andre proceduremæssige syntakser. Hvis det gemte proceduresprog er C #, Java eller R, skal du selvfølgelig bruge funktionerne og syntaksen for disse proceduremæssige sprog. Med andre ord, på trods af at motivationen for SQL var at bruge standardiserede deklarative forespørgsler, ser du i den virkelige verden masser af databasespecifik proceduremæssig serverprogrammering.

Det bringer os ikke helt tilbage til de dårlige gamle dage med CODASYL-databaseprogrammering (selvom markører kommer tæt på), men det går tilbage fra ideerne om, at SQL-sætninger skal standardiseres, og at præstationshensyn skal overlades til databaseforespørgseloptimering . I sidste ende er en fordobling af ydeevnen ofte for meget til at efterlade på bordet.

Lær SQL

Nedenstående websteder kan hjælpe dig med at lære SQL eller opdage særhederne i forskellige SQL-dialekter.

$config[zx-auto] not found$config[zx-overlay] not found