Wat is SQL? De lingua franca van data-analyse

Tegenwoordig is Structured Query Language de standaardmethode voor het manipuleren en opvragen van gegevens in relationele databases, maar met eigen extensies tussen de producten. Het gemak en de alomtegenwoordigheid van SQL hebben er zelfs toe geleid dat de makers van vele "NoSQL" of niet-relationele gegevensopslagplaatsen, zoals Hadoop, subsets van SQL hebben overgenomen of hun eigen SQL-achtige zoektalen hebben bedacht.

Maar SQL was niet altijd de "universele" taal voor relationele databases. Vanaf het begin (circa 1980) had SQL er bepaalde aanvallen tegen. Veel onderzoekers en ontwikkelaars in die tijd, waaronder ik, dachten dat de overhead van SQL zou voorkomen dat het ooit praktisch zou worden in een productiedatabase.

We hadden het duidelijk mis. Maar velen zijn nog steeds van mening dat, ondanks het gemak en de toegankelijkheid van SQL, de prijs die wordt gevraagd voor runtime-prestaties vaak te hoog is.

SQL-geschiedenis

Voordat er SQL was, hadden databases strakke, navigatie-programmeerinterfaces en waren ze meestal ontworpen rond een netwerkschema dat het CODASYL-gegevensmodel wordt genoemd. CODASYL (Committee on Data Systems Languages) was een consortium dat verantwoordelijk was voor de programmeertaal COBOL (beginnend in 1959) en databasetaaluitbreidingen (beginnend 10 jaar later).

Wanneer u programmeerde tegen een CODASYL-database, navigeerde u naar records door sets, die een-op-veel relaties uitdrukken. Bij oudere hiërarchische databases kan een record slechts tot één set behoren. Met netwerkdatabases kan een record tot meerdere sets behoren.

Stel dat u een lijst wilt maken van de studenten die zijn ingeschreven in CS 101. Eerst zoekt u "CS 101"in de Coursesset op naam, stelt u dat in als de eigenaar of ouder van de Enrolleesset, zoekt u het eerste lid ( ffm) van de Enrolleesset, dat een Studentrecord is, en geeft u een lijst het. Dan zou je in een lus gaan: Vind volgend lid ( fnm) en vermeld het. Als dit fnmniet lukte, zou u de lus verlaten.

Dat lijkt misschien veel scut-werk voor de databaseprogrammeur, maar het was erg efficiënt tijdens de uitvoering. Deskundigen zoals Michael Stonebraker van de University of California in Berkeley en Ingres wezen erop dat het uitvoeren van dat soort query's in een CODASYL-database zoals IDMS ongeveer de helft van de CPU-tijd en minder dan de helft van het geheugen kostte als dezelfde query op een relationele database met behulp van SQL .

Ter vergelijking: de equivalente SQL-query om alle leerlingen in CS 101 te retourneren zou er ongeveer zo uitzien 

SELECTEER student.naam VAN cursussen, ingeschreven personen, studenten WAAR cursusnaam

Die syntaxis impliceert een relationele innerlijke join (eigenlijk twee), zoals ik hieronder zal uitleggen, en laat enkele belangrijke details weg, zoals de velden die voor de joins worden gebruikt.

Relationele databases en SQL

Waarom zou u een factor twee verbetering in uitvoeringssnelheid en geheugengebruik opgeven? Er waren twee belangrijke redenen: ontwikkelingsgemak en draagbaarheid. Ik dacht dat een van beide in 1980 niet veel uitmaakte in vergelijking met de prestatie- en geheugenvereisten, maar naarmate de computerhardware verbeterde en goedkoper werd, gaven mensen niet meer om uitvoeringssnelheid en geheugen en maakten ze zich meer zorgen over de ontwikkelingskosten.

Met andere woorden, de wet van Moore heeft CODASYL-databases gedood ten gunste van relationele databases. Zoals het gebeurde, was de verbetering in ontwikkeltijd aanzienlijk, maar SQL-portabiliteit bleek een droom.

Waar kwamen het relationele model en SQL vandaan? EF "Ted" Codd was een computerwetenschapper bij het IBM San Jose Research Laboratory die de theorie van het relationele model in de jaren zestig uitwerkte en het in 1970 publiceerde. IBM was traag met het implementeren van een relationele database in een poging om de inkomsten van zijn CODASYL-database IMS / DB. Toen IBM eindelijk met zijn System R-project begon, stond het ontwikkelingsteam (Don Chamberlin en Ray Boyce) niet onder Codd, en negeerden ze Codds Alpha relationele taalartikel uit 1971 om hun eigen taal te ontwerpen, SEQUEL (Structured English Query Language). In 1979, voordat IBM zijn product had uitgebracht, nam Larry Ellison de taal op in zijn Oracle-database (met behulp van IBM's pre-launch SEQUEL-publicaties als zijn specificatie). SEQUEL werd al snel SQL om een ​​internationale handelsmerkschending te voorkomen.

De "tom-toms beating for SQL" (zoals Michael Stonebraker het uitdrukte) kwamen niet alleen van Oracle en IBM, maar ook van klanten. Het was niet gemakkelijk om CODASYL-databaseontwerpers en -programmeurs in te huren of op te leiden, dus SEQUEL (en SQL) zagen er veel aantrekkelijker uit. SQL was zo aantrekkelijk in de late jaren tachtig dat veel databaseleveranciers in wezen een SQL-queryprocessor nieten bovenop hun CODASYL-databases, tot grote ontsteltenis van Codd, die vond dat relationele databases vanaf nul moesten worden ontworpen om relationeel te zijn.

Een pure relationele database, zoals ontworpen door Codd, is gebouwd op tupels gegroepeerd in relaties, in overeenstemming met de eerste orde predikatenlogica. Real-world relationele databases hebben tabellen die velden, beperkingen en triggers bevatten, en tabellen zijn aan elkaar gerelateerd via externe sleutels. SQL wordt gebruikt om de te retourneren gegevens te declareren, en een SQL-queryprocessor en query-optimalisator zetten de SQL-declaratie om in een queryplan dat wordt uitgevoerd door de database-engine.

SQL bevat een subtaal voor het definiëren van schema's, de data definition language (DDL), samen met een subtaal voor het wijzigen van data, de data manipulation language (DML). Beide hebben hun oorsprong in vroege CODASYL-specificaties. De derde subtaal in SQL declareert query's via de SELECTinstructie en relationele joins.

SQL-  SELECTinstructie

De SELECTinstructie vertelt de query-optimizer welke gegevens moeten worden geretourneerd, welke tabellen moeten worden bekeken, welke relaties moeten worden gevolgd en welke volgorde aan de geretourneerde gegevens moet worden opgelegd. De query-optimizer moet zelf uitzoeken welke indexen hij moet gebruiken om tabelscans met brute kracht te vermijden en goede queryprestaties te bereiken, tenzij de specifieke database index-hints ondersteunt.

Een deel van de kunst van het ontwerpen van relationele databases hangt af van het oordeelkundig gebruik van indexen. Als u een index weglaat voor een frequente zoekopdracht, kan de hele database langzamer worden onder zware leesbelastingen. Als u te veel indexen heeft, kan de hele database langzamer worden onder zware schrijf- en updatebelastingen.

Een andere belangrijke kunst is het kiezen van een goede, unieke primaire sleutel voor elke tafel. U moet niet alleen rekening houden met de impact van de primaire sleutel op veelvoorkomende zoekopdrachten, maar ook hoe deze wordt afgespeeld in joins wanneer deze als een externe sleutel in een andere tabel wordt weergegeven, en hoe deze de referentielocatie van de gegevens beïnvloedt.

In het geavanceerde geval van databasetabellen die zijn opgesplitst in verschillende volumes, afhankelijk van de waarde van de primaire sleutel, horizontale sharding genaamd, moet u ook overwegen hoe de primaire sleutel de sharding beïnvloedt. Tip: u wilt dat de tabel gelijkmatig over de volumes wordt verdeeld, wat suggereert dat u geen datumstempels of opeenvolgende gehele getallen als primaire sleutels wilt gebruiken.

Discussies over de SELECTverklaring kunnen eenvoudig beginnen, maar kunnen al snel verwarrend worden. Overwegen:

SELECTEER * UIT klanten;

Simpel toch? Het vraagt ​​om alle velden en alle rijen van de Customerstabel. Stel echter dat de Customerstabel honderd miljoen rijen en honderd velden heeft, en een van de velden is een groot tekstveld voor opmerkingen. Hoe lang duurt het om al die gegevens over een netwerkverbinding van 10 megabit per seconde te halen als elke rij gemiddeld 1 kilobyte aan gegevens bevat?

Misschien moet u verminderen hoeveel u over de draad verzendt. Overwegen:

SELECTEER TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount van klanten

WAAR staat EN stad

BESTEL OP lastSaleDate AFDALEND;

Nu ga je veel minder gegevens ophalen. U hebt de database gevraagd om u slechts vier velden te geven, om alleen de bedrijven in Cleveland te beschouwen, en om u alleen de 100 bedrijven met de meest recente verkopen te geven. Om dat echter het meest efficiënt te doen op de databaseserver, heeft de Customerstabel een index nodig state+cityvoor de WHEREclausule en een index lastSaleDatevoor de ORDER BYen- TOP 100clausules.

Geldt overigens TOP 100voor SQL Server en SQL Azure, maar niet voor MySQL of Oracle. In MySQL zou je LIMIT 100na de WHEREclausule gebruiken. In Oracle zou je een bound on gebruiken ROWNUMals onderdeel van de WHEREclausule, dwz WHERE... AND ROWNUM <=100. Helaas gaan de ANSI / ISO SQL-standaarden (en er zijn er tot nu toe negen, die zich uitstrekken van 1986 tot 2016) maar zo ver, dat elke database zijn eigen propriëtaire clausules en functies introduceert.

SQL sluit zich aan 

Tot nu toe heb ik de SELECTsyntaxis voor enkele tabellen beschreven. Voordat ik JOINclausules kan uitleggen  , moet u de externe sleutels en relaties tussen tabellen begrijpen. Ik zal dit uitleggen door voorbeelden in DDL te gebruiken, met behulp van de SQL Server-syntaxis.

De korte versie hiervan is vrij eenvoudig. Elke tabel die u in relaties wilt gebruiken, moet een primaire sleutelbeperking hebben; dit kan een enkel veld zijn of een combinatie van velden die zijn gedefinieerd door een uitdrukking. Bijvoorbeeld:

TABEL MAKEN Personen (

    PersonID int NOT NULL PRIMARY KEY,

    PersonName char (80),

    ...

Elke tabel waarnaar moet worden verwezen, Personsmoet een veld hebben dat overeenkomt met de Personsprimaire sleutel, en om de relationele integriteit te behouden, moet dat veld een beperking met een externe sleutel hebben. Bijvoorbeeld:

CREATE TABLE Orders (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int FOREIGN KEY REFERENCES Personen (PersonID)

);

Er zijn langere versies van beide instructies die het CONSTRAINTsleutelwoord gebruiken, waarmee u de beperking een naam kunt geven. Dat is wat de meeste database-ontwerptools genereren.

Primaire sleutels zijn altijd geïndexeerd en uniek (de veldwaarden kunnen niet worden gedupliceerd). Andere velden kunnen optioneel worden geïndexeerd. Het is vaak handig om indexen te maken voor velden met externe sleutels en voor velden die voorkomen in WHEREen ORDER BYclausules, hoewel niet altijd, vanwege de mogelijke overhead van schrijfbewerkingen en updates.

Hoe zou u een vraag schrijven die alle bestellingen retourneert die door John Doe zijn geplaatst?

SELECTEER PersonName, OrderID FROM Personen

INNER JOIN Orders ON Persons.PersonID = Orders.PersonID

WAAR PersonName;

In feite zijn er vier soorten JOIN: INNER, OUTER, LEFT, en RIGHT. Dit INNER JOINis de standaardwaarde (u kunt het woord weglaten INNER), en het is degene die alleen rijen bevat die overeenkomende waarden in beide tabellen bevatten. Als u personen wilt vermelden, ongeacht of ze bestellingen hebben of niet, gebruikt u LEFT JOINbijvoorbeeld een:

SELECTEER PersonName, OrderID FROM Personen

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

ORDER BY PersonName;

Wanneer u query's begint uit te voeren die meer dan twee tabellen samenvoegen, expressies gebruiken of gegevenstypen afdwingen, kan de syntaxis in het begin een beetje harig worden. Gelukkig zijn er tools voor databaseontwikkeling die de juiste SQL-query's voor u kunnen genereren, vaak door tabellen en velden van het schemadiagram naar een querydiagram te slepen en neer te zetten.

SQL opgeslagen procedures

Soms brengt de declaratieve aard van de SELECTverklaring u niet waar u heen wilt. De meeste databases hebben een voorziening die opgeslagen procedures wordt genoemd; Helaas is dit een gebied waar bijna alle databases eigen uitbreidingen van de ANSI / ISO SQL-standaarden gebruiken.

In SQL Server was het aanvankelijke dialect voor opgeslagen procedures (of opgeslagen procedures) Transact-SQL, ook bekend als T-SQL; in Oracle was het PL-SQL. Beide databases hebben extra talen toegevoegd voor opgeslagen procedures, zoals C #, Java en R. Een eenvoudige opgeslagen T-SQL-procedure is mogelijk alleen een geparametriseerde versie van een SELECTinstructie. De voordelen zijn gebruiksgemak en efficiëntie. Opgeslagen procedures worden geoptimaliseerd wanneer ze worden opgeslagen, niet elke keer dat ze worden uitgevoerd.

Een meer gecompliceerde opgeslagen T-SQL-procedure kan meerdere SQL-instructies, invoer- en uitvoerparameters, lokale variabelen, BEGIN...ENDblokken, IF...THEN...ELSEvoorwaarden, cursors (rij-voor-rij-verwerking van een set), expressies, tijdelijke tabellen en een hele reeks andere procedurele syntaxis. Het is duidelijk dat als de opgeslagen proceduretaal C #, Java of R is, je de functies en syntaxis van die proceduretalen gaat gebruiken. Met andere woorden, ondanks het feit dat de motivatie voor SQL was om gestandaardiseerde declaratieve query's te gebruiken, zie je in de echte wereld veel database-specifieke procedurele serverprogrammering.

Dat brengt ons niet helemaal terug naar de slechte oude tijd van CODASYL-databaseprogrammering (hoewel cursors in de buurt komen), maar het loopt terug van de ideeën dat SQL-statements moeten worden gestandaardiseerd en dat prestatieproblemen moeten worden overgelaten aan de databasequery-optimizer . Uiteindelijk is een verdubbeling van de prestatie vaak te veel om op tafel te laten liggen.

Leer SQL

De onderstaande sites kunnen u helpen SQL te leren, of de eigenaardigheden van verschillende SQL-dialecten te ontdekken.