Programmering

Hva er SQL? Lingua franca av dataanalyse

I dag er Structured Query Language standardmetoden for å manipulere og spørre data i relasjonsdatabaser, men med proprietære utvidelser blant produktene. Enkelhet og allestedsnærværende SQL har til og med ført til at skaperne av mange “NoSQL” eller ikke-relasjonelle datalagre, som Hadoop, har tatt i bruk delmengder av SQL eller kommet med sine egne SQL-lignende spørringsspråk.

Men SQL var ikke alltid det "universelle" språket for relasjonsdatabaser. Fra begynnelsen (ca. 1980) hadde SQL visse streik mot seg. Mange forskere og utviklere på den tiden, inkludert meg, trodde at overhead av SQL ville forhindre det i å være praktisk i en produksjonsdatabase.

Vi hadde helt klart feil. Men mange mener fremdeles at prisen som kreves i kjøretidsytelse ofte er for høy, for all enkelhet og tilgjengelighet i SQL.

SQL-historie

Før det var SQL hadde databaser tette navigasjonsprogrammeringsgrensesnitt, og ble vanligvis designet rundt et nettverksskjema kalt CODASYL-datamodellen. CODASYL (Committee on Data Systems Languages) var et konsortium som hadde ansvaret for COBOL-programmeringsspråket (startet i 1959) og utvidelser av databasespråk (startet 10 år senere).

Da du programmerte mot en CODASYL-database, navigerte du til poster gjennom sett, som uttrykker en-til-mange-relasjoner. Eldre hierarkiske databaser tillater bare at en post tilhører ett sett. Nettverksdatabaser tillater at en post tilhører flere sett.

Si at du ønsket å liste opp studentene som var registrert i CS 101. Først ville du finne det "CS 101" i Kurs angitt etter navn, angi det som eier eller forelder for Påmeldte sett, finn det første medlemmet (ffm) av Påmeldte sett, som er en Student ta opp, og oppfør den. Så vil du gå i en løkke: Finn neste medlem (fnm) og oppgi den. Når fnm mislyktes, ville du gå ut av sløyfen.

Det kan virke som mye scut-arbeid for databaseprogrammereren, men det var veldig effektivt på utførelsestidspunktet. Eksperter som Michael Stonebraker fra University of California i Berkeley og Ingres påpekte at å gjøre den slags spørsmål i en CODASYL-database som IDMS tok omtrent halvparten av CPU-tiden og mindre enn halvparten av minnet som den samme spørringen i en relasjonsdatabase ved hjelp av SQL .

Til sammenligning vil det tilsvarende SQL-spørsmålet om å returnere alle studentene i CS 101 være omtrent som 

VELG student.navn FRA kurs, påmeldte, studenter HVOR kurs.navn

Denne syntaksen innebærer en relasjonell indre sammenføyning (faktisk to av dem), som jeg vil forklare nedenfor, og utelater noen viktige detaljer, for eksempel feltene som brukes til sammenføyningene.

Relasjonsdatabaser og SQL

Hvorfor vil du gi opp en faktor på to forbedringer i kjøringshastighet og minnebruk? Det var to store grunner: enkel utvikling og bærbarhet. Jeg trodde ikke at noen av dem hadde noen betydning i 1980 sammenlignet med ytelseskrav og minnekrav, men etter hvert som maskinvaren ble bedre og ble billigere, sluttet folk å bry seg om kjøringshastighet og minne og bekymret seg mer for kostnadene ved utvikling.

Med andre ord drepte Moores lov CODASYL-databaser til fordel for relasjonsdatabaser. Etter hvert som forbedringen i utviklingstid var betydelig, men SQL-portabilitet viste seg å være en rørdrøm.

Hvor kom relasjonsmodellen og SQL fra? EF “Ted” Codd var datavitenskapsmann ved IBM San Jose Research Laboratory som utarbeidet teorien om relasjonsmodellen på 1960-tallet og publiserte den i 1970. IBM var treg med å implementere en relasjonsdatabase i et forsøk på å beskytte inntektene til sin CODASYL-database IMS / DB. Da IBM endelig startet sitt System R-prosjekt, var utviklingsteamet (Don Chamberlin og Ray Boyce) ikke under Codd, og de ignorerte Codds 1971 Alpha relasjonelle språkpapir for å designe sitt eget språk, SEQUEL (Structured English Query Language). I 1979, før IBM til og med hadde gitt ut sitt produkt, inkorporerte Larry Ellison språket i sin Oracle-database (ved å bruke IBMs pre-launch SEQUEL-publikasjoner som spesifikasjon). SEQUEL ble snart SQL for å unngå et internasjonalt brudd på varemerker.

“Tom-toms-bankingen for SQL” (som Michael Stonebraker sa det) kom ikke bare fra Oracle og IBM, men også fra kunder. Det var ikke lett å ansette eller trene CODASYL-databasedesignere og programmerere, så SEQUEL (og SQL) så mye mer attraktivt ut. SQL var så attraktivt på senere 1980-tallet at mange databaseleverandører i hovedsak stiftet en SQL-spørringsprosessor på toppen av sine CODASYL-databaser, til stor forferdelse for Codd, som mente at relasjonsdatabaser måtte designes fra bunnen av for å være relasjonelle.

En ren relasjonsdatabase, designet av Codd, er bygget på tupler gruppert i relasjoner, i samsvar med førsteordens predikatlogikk. Virkelige relasjonsdatabaser har tabeller som inneholder felt, begrensninger og utløsere, og tabeller er relatert gjennom utenlandske nøkler. SQL brukes til å erklære dataene som skal returneres, og en SQL-spørringsprosessor og spørringsoptimerer gjør SQL-erklæringen til en spørringsplan som kjøres av databasemotoren.

SQL inkluderer et underspråk for å definere skjemaer, datadefinisjonsspråket (DDL), sammen med et underspråk for å modifisere data, datalagringsspråket (DML). Begge disse har røtter i tidlige CODASYL-spesifikasjoner. Det tredje delspråket i SQL erklærer spørsmål gjennom Å VELGE uttalelse og relasjonell sammenføyning.

SQLÅ VELGE uttalelse

De Å VELGE uttalelse forteller spørringsoptimereren hvilke data du skal returnere, hvilke tabeller du skal se i, hvilke forhold du skal følge, og hvilken rekkefølge du skal pålegge de returnerte dataene. Søkeoptimalisereren må selv finne ut hvilke indekser som skal brukes for å unngå brute force tabellskanninger og oppnå god spørringsytelse, med mindre den bestemte databasen støtter indekshint.

En del av kunsten til relasjonell databasedesign henger på den fornuftige bruken av indekser. Hvis du utelater en indeks for en hyppig spørring, kan hele databasen avta under tunge lese belastninger. Hvis du har for mange indekser, kan hele databasen avta under tunge skrive- og oppdateringsbelastninger.

En annen viktig kunst er å velge en god, unik hovednøkkel til hvert bord. Du må ikke bare vurdere innvirkningen av primærnøkkelen på vanlige spørsmål, men hvordan den vil spille sammen, blir sammen når den vises som en fremmed nøkkel i en annen tabell, og hvordan den vil påvirke datalevens referanselokalitet.

I det avanserte tilfellet med databasetabeller som er delt opp i forskjellige volumer avhengig av verdien til primærnøkkelen, kalt horisontal sharding, må du også vurdere hvordan primærnøkkelen vil påvirke shardingen. Tips: Du vil at tabellen skal fordeles jevnt over volumer, noe som antyder at du ikke vil bruke datostempler eller påfølgende heltal som primærnøkler.

Diskusjoner om Å VELGE uttalelse kan begynne enkelt, men kan fort bli forvirrende. Ta i betraktning:

VELG * FRA KUNDER;

Enkelt, ikke sant? Den ber om alle felt og alle rader i Kunder bord. Anta imidlertid at Kunder tabellen har hundre millioner rader og hundre felt, og et av feltene er et stort tekstfelt for kommentarer. Hvor lang tid vil det ta å trekke ned alle dataene over en 10 megabit per sekund nettverkstilkobling hvis hver rad inneholder et gjennomsnitt på 1 kilobyte data?

Kanskje du burde kutte ned hvor mye du sender over ledningen. Ta i betraktning:

VELG TOPP 100 firmanavn, lastSaleDate, lastSaleAmount, totalSalesAmount FRA Kunder

HVOR staten og byen

BESTILLE ETTER siste Salgsdato AVHENGENDE;

Nå skal du trekke ned mye mindre data. Du har bedt databasen bare gi deg fire felt, bare vurdere selskapene i Cleveland, og å gi deg bare de 100 selskapene med det siste salget. For å gjøre det mest effektivt på databaseserveren, imidlertid Kunder tabellen trenger en indeks på delstat + by for HVOR klausul og en indeks på lastSaleDate for REKKEFØLGE ETTER og TOPP 100 klausuler.

Forresten, TOPP 100 er gyldig for SQL Server og SQL Azure, men ikke MySQL eller Oracle. I MySQL vil du bruke GRENSE 100 etter HVOR klausul. I Oracle vil du bruke en bundet på ROWNUM som en del av HVOR klausul, dvs. HVOR ... OG ROWNUM <= 100. Dessverre går ANSI / ISO SQL-standardene (og det er ni av dem hittil, som strekker seg fra 1986 til 2016) bare så langt, utover hvilke hver database introduserer sine egne proprietære klausuler og funksjoner.

SQL blir med

Så langt har jeg beskrevet Å VELGE syntaks for enkeltbord. Før jeg kan forklareBLI MED klausuler, må du forstå fremmednøkler og forholdet mellom tabeller. Jeg forklarer dette ved å bruke eksempler i DDL, ved hjelp av SQL Server-syntaks.

Kortversjonen av dette er ganske enkel. Hver tabell du vil bruke i relasjoner, bør ha en primær nøkkelbegrensning; dette kan enten være et enkelt felt eller en kombinasjon av felt definert av et uttrykk. For eksempel:

OPPRETT TABELL Personer (

PersonID int IKKE NULL PRIMÆR NØKKEL,

PersonName røye (80),

    ...

Hvert bord som trenger å forholde seg til Personer skal ha et felt som tilsvarer Personer primærnøkkel, og for å bevare relasjonsintegritet, bør dette feltet ha en utenlandsk nøkkelbegrensning. For eksempel:

OPPRETT TABELL Bestillinger (

OrderID int IKKE NULL PRIMÆR NØKKEL,

    ...

PersonID int UTENLANDSKE NØKKELREFERANSER Personer (PersonID)

);

Det er lengre versjoner av begge utsagnene som bruker BEGRENSNING nøkkelord, som lar deg navngi begrensningen. Det er det de fleste verktøy for databasedesign genererer.

Primære nøkler er alltid indeksert og unike (feltverdiene kan ikke dupliseres). Andre felt kan valgfritt indekseres. Det er ofte nyttig å lage indekser for utenlandske nøkkelfelt og for felt som vises i HVOR og REKKEFØLGE ETTER klausuler, men ikke alltid, på grunn av potensielle omkostninger fra skriving og oppdateringer.

Hvordan vil du skrive et spørsmål som returnerer alle bestillingene som er lagt inn av John Doe?

VELG Personnavn, OrderID FRA personer

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

WHERE PersonName;

Det er faktisk fire typer BLI MED: INDRE, YTRE, VENSTRE, og IKKE SANT. De INNRE MEDLEM er standard (du kan utelate ordet INDRE), og det er den som bare inneholder rader som inneholder samsvarende verdier i begge tabellene. Hvis du vil liste opp personer om de har bestillinger eller ikke, bruker du en VENSTRE BLI MEDLEM, for eksempel:

VELG Personnavn, OrderID FRA personer

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

BESTILL PÅ Personnavn;

Når du begynner å gjøre spørsmål som blir med i mer enn to tabeller, som bruker uttrykk eller som tvinger datatyper, kan syntaksen bli litt hårete i begynnelsen. Heldigvis finnes det databaseutviklingsverktøy som kan generere riktige SQL-spørsmål for deg, ofte ved å dra og slippe tabeller og felt fra skjemadiagrammet til et spørringsdiagram.

SQL lagrede prosedyrer

Noen ganger er den deklarative naturen til Å VELGE uttalelse får deg ikke dit du vil. De fleste databaser har et anlegg som kalles lagrede prosedyrer; dessverre er dette et område der nesten alle databasene bruker proprietære utvidelser til ANSI / ISO SQL-standardene.

I SQL Server var den første dialekten for lagrede prosedyrer (eller lagrede prosesser) Transact-SQL, også kalt T-SQL; i Oracle var det PL-SQL. Begge databasene har lagt til flere språk for lagrede prosedyrer, for eksempel C #, Java og R. En enkel T-SQL-lagret prosedyre kan bare være en parameterisert versjon av en Å VELGE uttalelse. Fordelene er brukervennlighet og effektivitet. Lagrede prosedyrer optimaliseres når de lagres, ikke hver gang de utføres.

En mer komplisert T-SQL lagret prosedyre kan bruke flere SQL-setninger, inngangs- og utdata-parametere, lokale variabler, BEGIN ... SLUT blokker, HVIS ... SÅ ... ANDRE betingelser, markører (rad-for-rad-behandling av et sett), uttrykk, midlertidige tabeller og en hel rekke andre prosessuelle syntakser. Åpenbart hvis det lagrede prosedyrespråket er C #, Java eller R, skal du bruke funksjonene og syntaksen til disse prosessuelle språkene. Med andre ord, til tross for at motivasjonen for SQL var å bruke standardiserte deklarative spørsmål, ser du i den virkelige verden mye databasespesifikk prosessuell serverprogrammering.

Det tar oss ikke helt tilbake til de dårlige gamle dagene med CODASYL-databaseprogrammering (selv om markører kommer nærme), men det går tilbake fra ideene om at SQL-setninger skal standardiseres og at ytelsesproblemer bør overlates til databasespørringsoptimalisereren . Til slutt er en dobling av ytelsen ofte for mye å legge igjen på bordet.

Lær SQL

Nettstedene som er oppført nedenfor kan hjelpe deg med å lære SQL, eller oppdage særegenheter i forskjellige SQL-dialekter.

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