Vad är SQL? Lingua franca för dataanalys

Idag är Structured Query Language det vanliga sättet att manipulera och fråga data i relationsdatabaser, men med egna tillägg bland produkterna. SQL-lättheten och allestädes närvarande har till och med lett skaparna av många ”NoSQL” eller icke-relationella datalagrar, som Hadoop, att anta delmängder av SQL eller komma med sina egna SQL-liknande frågespråk.

Men SQL var inte alltid det ”universella” språket för relationsdatabaser. Från början (cirka 1980) hade SQL vissa strejker mot det. Många forskare och utvecklare vid den tiden, inklusive jag, trodde att SQL-omkostnaderna skulle förhindra att det någonsin skulle vara praktiskt i en produktionsdatabas.

Vi hade helt klart fel. Men många tror fortfarande att priset som krävs i runtime-prestanda ofta är för högt för alla SQL: s enkelhet och tillgänglighet.

SQL-historik

Innan det fanns SQL hade databaser snäva gränssnitt för navigationsprogrammering och utformades vanligtvis kring ett nätverksschema som kallades CODASYL-datamodellen. CODASYL (Committee on Data Systems Languages) var ett konsortium som ansvarade för COBOL-programmeringsspråket (från 1959) och databasens språkutvidgningar (från 10 år senare).

När du programmerade mot en CODASYL-databas navigerade du till poster genom uppsättningar som uttrycker en-till-många-relationer. Äldre hierarkiska databaser tillåter bara en post att tillhöra en uppsättning. Nätverksdatabaser tillåter en post att tillhöra flera uppsättningar.

Anta att du ville lista de studenter som är inskrivna i CS 101. Först skulle du hitta "CS 101"i Coursesuppsättningen efter namn, ange att som ägare eller förälder för Enrolleesuppsättningen, hitta den första medlemmen ( ffm) i Enrolleesuppsättningen, vilket är en Studentpost, och lista Det. Då skulle du gå in i en slinga: Hitta nästa medlem ( fnm) och lista den. När det fnmmisslyckades skulle du lämna slingan.

Det kan verka som mycket scutarbete för databasprogrammeraren, men det var mycket effektivt vid körningstid. Experter som Michael Stonebraker vid University of California i Berkeley och Ingres påpekade att det att göra den typen av frågor i en CODASYL-databas som IDMS tog ungefär hälften av CPU-tiden och mindre än hälften av minnet som samma fråga i en relationsdatabas med SQL .

Som jämförelse skulle motsvarande SQL-fråga för att returnera alla studenter i CS 101 vara ungefär som 

VÄLJ student.namn FRÅN kurser, anmälare, studenter VAR kurs.namn

Den syntaxen innebär en relationell inre koppling (faktiskt två av dem), som jag kommer att förklara nedan, och lämnar några viktiga detaljer, till exempel fälten som används för sammanfogningarna.

Relationsdatabaser och SQL

Varför skulle du ge upp en faktor på två förbättringar i körningshastighet och minnesanvändning? Det fanns två stora skäl: enkel utveckling och bärbarhet. Jag trodde inte att någon av dem spelade så stor roll 1980 jämfört med prestanda och minneskrav, men när datorhårdvaran förbättrades och blev billigare slutade folk bry sig om körningshastighet och minne och oroade sig mer för kostnaden för utveckling.

Med andra ord dödade Moores lag CODASYL-databaser till förmån för relationsdatabaser. När det hände var förbättringen av utvecklingstiden betydande, men SQL-portabilitet visade sig vara en rördröm.

Var kom relationsmodellen och SQL ifrån? EF “Ted” Codd var en datavetare vid IBM San Jose Research Laboratory som arbetade fram teorin om relationsmodellen på 1960-talet och publicerade den 1970. IBM var långsam med att implementera en relationsdatabas i ett försök att skydda intäkterna från dess CODASYL-databas IMS / DB. När IBM äntligen startade sitt System R-projekt var utvecklingsteamet (Don Chamberlin och Ray Boyce) inte under Codd, och de ignorerade Codds 1971 Alpha relationella språkpapper för att utforma sitt eget språk, SEQUEL (Structured English Query Language). 1979, innan IBM ens hade släppt sin produkt, införlivade Larry Ellison språket i sin Oracle-databas (med IBM: s pre-launch SEQUEL-publikationer som hans specifikation). SEQUEL blev snart SQL för att undvika ett internationellt varumärkesintrång.

”Tom-tomsna som slår för SQL” (som Michael Stonebraker uttryckte det) kom inte bara från Oracle och IBM utan också från kunder. Det var inte lätt att anställa eller utbilda CODASYL-databasdesigners och programmerare, så SEQUEL (och SQL) såg mycket mer attraktivt ut. SQL var så attraktivt under senare 1980-talet att många databasleverantörer i huvudsak häftade en SQL-frågeprocessor ovanpå sina CODASYL-databaser, till stor oro för Codd, som ansåg att relationsdatabaser måste utformas från grunden för att vara relationella.

En ren relationsdatabas, designad av Codd, bygger på tuplar grupperade i relationer, i överensstämmelse med första ordens predikatlogik. Verkliga relationsdatabaser har tabeller som innehåller fält, begränsningar och utlösare, och tabeller är relaterade via främmande nycklar. SQL används för att deklarera data som ska returneras, och en SQL-frågeprocessor och frågaoptimerare gör SQL-deklarationen till en frågeplan som körs av databasmotorn.

SQL innehåller ett underspråk för att definiera scheman, datadefinitionsspråket (DDL), tillsammans med ett underspråk för att modifiera data, datainformationsspråket (DML). Båda dessa har rötter i tidiga CODASYL-specifikationer. Det tredje delspråket i SQL förklarar frågor genom SELECTuttalandet och relationella sammanfogningar.

SQL-  SELECTuttalande

Det SELECTuttalandet berättar Frågeoptimeraren vilka data att återvända, vad tabeller att titta i, vad relationer att följa, och vad för att ålägga de returnerade data. Frågaoptimeraren måste själva ta reda på vilka index som ska användas för att undvika brute force-tabellskanningar och uppnå bra frågeprestanda, såvida inte den specifika databasen stöder indextips.

En del av konsten att relationell databasdesign hänger på den förnuftiga användningen av index. Om du utelämnar ett index för en frekvent fråga kan hela databasen sakta ner under stora läsbelastningar. Om du har för många index kan hela databasen sakta ner vid kraftiga skriv- och uppdateringsbelastningar.

En annan viktig konst är att välja en bra, unik primärnyckel för varje bord. Du måste inte bara överväga den primära nyckelns inverkan på vanliga frågor, utan hur den kommer att spela samman förenas när den visas som en främmande nyckel i en annan tabell och hur det kommer att påverka datans referensplats.

I det avancerade fallet med databastabeller som är uppdelade i olika volymer beroende på värdet på den primära nyckeln, kallad horisontell sharding, måste du också överväga hur den primära nyckeln kommer att påverka shardingen. Tips: Du vill att tabellen ska fördelas jämnt över volymer, vilket antyder att du inte vill använda datumstämplar eller på varandra följande heltal som primära nycklar.

Diskussioner om SELECTuttalandet kan börja enkelt, men kan snabbt bli förvirrande. Överväga:

VÄLJ * FRÅN KUNDER;

Enkelt, eller hur? Den ber om alla fält och alla rader i Customerstabellen. Antag dock att Customerstabellen har hundra miljoner rader och hundra fält, och att ett av fälten är ett stort textfält för kommentarer. Hur lång tid tar det att dra ner all den informationen över en 10 megabit per sekund nätverksanslutning om varje rad innehåller i genomsnitt 1 kilobyte data?

Du kanske borde skära ner hur mycket du skickar över kabeln. Överväga:

VÄLJ TOPP 100 företagsnamn, lastSaleDate, lastSaleAmount, totalSalesAmount FRÅN KUNDER

VAR stat OCH stad

BESTÄLLNING EFTER sista försäljningsdatum FÖRFALL;

Nu kommer du att dra ner mycket mindre data. Du har bett databasen att ge dig bara fyra fält, att bara beakta företagen i Cleveland och att ge dig bara de 100 företagen med den senaste försäljningen. För att göra det mest effektivt på databasservern Customersbehöver tabellen dock ett index på state+cityför WHEREklausulen och ett index på lastSaleDateför ORDER BYoch TOP 100klausuler.

Gäller förresten TOP 100SQL Server och SQL Azure, men inte MySQL eller Oracle. I MySQL skulle du använda LIMIT 100efter WHEREklausulen. I Oracle, skulle du använda en gräns för ROWNUMsom en del av WHEREklausulen, det vill säga WHERE... AND ROWNUM <=100. Tyvärr går ANSI / ISO SQL-standarderna (och det finns nio av dem hittills, sträcker sig från 1986 till 2016) bara så långt, utöver vilka varje databas introducerar sina egna klausuler och funktioner.

SQL går med 

Hittills har jag beskrivit SELECTsyntaxen för enstaka tabeller. Innan jag kan förklara  JOINklausuler måste du förstå främmande nycklar och relationer mellan tabeller. Jag ska förklara detta med hjälp av exempel i DDL, med SQL Server-syntax.

Den korta versionen av detta är ganska enkel. Varje tabell som du vill använda i relationer bör ha en primär nyckelbegränsning; detta kan antingen vara ett enda fält eller en kombination av fält definierade av ett uttryck. Till exempel:

SKAPA TABELL Personer (

    PersonID int INTE NULL PRIMÄR NYCKEL

    PersonName char (80),

    ...

Varje tabell som måste relateras till Personsbör ha ett fält som motsvarar den Personsprimära nyckeln och för att bevara relationsintegriteten bör det fältet ha en främmande nyckelbegränsning. Till exempel:

SKAPA TABELL Beställningar (

    OrderID int INTE NULL PRIMÄR NYCKEL,

    ...

    PersonID int UTLÄNDSKA NYCKELHÄNVISNINGAR Personer (PersonID)

);

Det finns längre versioner av båda påståendena som använder CONSTRAINTnyckelordet, vilket låter dig namnge begränsningen. Det är vad de flesta databasdesignverktyg genererar.

Primära nycklar är alltid indexerade och unika (fältvärdena kan inte dupliceras). Andra fält kan valfritt indexeras. Det är ofta användbart att skapa index för främmande nyckelfält och för fält som visas i WHEREoch ORDER BYklausuler, men inte alltid, på grund av den potentiella omkostnaden från skrivningar och uppdateringar.

Hur skulle du skriva en fråga som returnerar alla beställningar som gjorts av John Doe?

VÄLJ Personnamn, OrderID FRA personer

INNER JOIN Beställningar PÅ Persons.PersonID = Orders.PersonID

VAR Personnamn;

I själva verket finns det fyra typer av JOIN: INNER, OUTER, LEFT, och RIGHT. Det INNER JOINär standard (du kan utelämna ordet INNER), och det är den som endast innehåller rader som innehåller matchande värden i båda tabellerna. Om du vill lista personer oavsett om de har beställningar eller inte, använder du LEFT JOINtill exempel:

VÄLJ Personnamn, OrderID FRA personer

VÄNSTER JOIN Beställningar på Persons.PersonID = Orders.PersonID

BESTÄLLA PÅ Personnamn;

När du börjar göra frågor som går med i mer än två tabeller, som använder uttryck eller som tvingar datatyper, kan syntaxen bli lite hårig först. Lyckligtvis finns det databasutvecklingsverktyg som kan generera korrekta SQL-frågor åt dig, ofta genom att dra och släppa tabeller och fält från schemat till ett frågediagram.

SQL-lagrade procedurer

Ibland tar uttalandets deklarativa karaktär SELECTdig inte dit du vill åka. De flesta databaser har en anläggning som kallas lagrade procedurer; tyvärr är detta ett område där nästan alla databaser använder egna tillägg till ANSI / ISO SQL-standarderna.

I SQL Server var den ursprungliga dialekten för lagrade procedurer (eller lagrade procs) Transact-SQL, aka T-SQL; i Oracle var det PL-SQL. Båda databaserna har lagt till ytterligare språk för lagrade procedurer, till exempel C #, Java och R. En enkel lagrad T-SQL-procedur kan bara vara en parametrerad version av ett SELECTuttalande. Dess fördelar är användarvänlighet och effektivitet. Lagrade procedurer optimeras när de sparas, inte varje gång de körs.

En mer komplicerad lagrad T-SQL-procedur kan använda flera SQL-satser, in- och utmatningsparametrar, lokala variabler, BEGIN...ENDblock, IF...THEN...ELSEvillkor, markörer (rad för rad-bearbetning av en uppsättning), uttryck, tillfälliga tabeller och en hel rad andra procedurell syntax. Uppenbarligen, om det lagrade procedurspråket är C #, Java eller R, kommer du att använda funktionerna och syntaxen för dessa procedurspråk. Med andra ord, trots att motivationen för SQL var att använda standardiserade deklarativa frågor, i den verkliga världen ser du massor av databasspecifik procedurell serverprogrammering.

Det tar oss inte riktigt tillbaka till de dåliga gamla dagarna med CODASYL-databasprogrammering (även om markörer kommer nära), men det går tillbaka från idéerna att SQL-uttalanden ska standardiseras och att prestationsproblem bör överlåtas till databasfrågeoptimeraren . I slutändan är en fördubbling av prestanda ofta för mycket att lämna på bordet.

Lär dig SQL

Webbplatserna som listas nedan kan hjälpa dig att lära dig SQL eller upptäcka konstigheterna i olika SQL-dialekter.