Co je to SQL? Lingua franca analýzy dat

Dnes je Structured Query Language standardním prostředkem pro manipulaci a dotazování na data v relačních databázích, i když s proprietárními rozšířeními mezi produkty. Snadnost a všudypřítomnost SQL dokonce vedla tvůrce mnoha „NoSQL“ nebo nerelačních datových skladů, jako je Hadoop, k přijetí podmnožin SQL nebo k vytvoření vlastních dotazovacích jazyků podobných SQL.

Ale SQL nebyl vždy „univerzálním“ jazykem pro relační databáze. Od začátku (kolem roku 1980) proti němu SQL útočilo. Mnoho vědců a vývojářů v té době, včetně mě, si myslelo, že režie SQL ji v produkční databázi zabrání tomu, aby byla někdy praktická.

Je zřejmé, že jsme se mýlili. Ale mnozí stále věří, že pro veškerou snadnost a dostupnost SQL je cena požadovaná za běhového výkonu často příliš vysoká.

Historie SQL

Předtím, než existoval SQL, měly databáze těsná, navigační programovací rozhraní a obvykle byly navrženy kolem síťového schématu zvaného datový model CODASYL. CODASYL (Výbor pro jazyky datových systémů) bylo konsorcium odpovědné za programovací jazyk COBOL (od roku 1959) a rozšíření databázových jazyků (od 10 let později).

Když jste programovali proti databázi CODASYL, procházeli jste záznamy prostřednictvím sad, které vyjadřují vztahy jedna k mnoha. Starší hierarchické databáze umožňují záznamu patřit pouze do jedné sady. Síťové databáze umožňují záznamu patřit do více sad.

Řekněme, že chcete vypsat seznam studentů zapsaných do CS 101. Nejprve byste našli "CS 101" v Kurzy nastavit podle jména, nastavit jako vlastníka nebo rodiče Zapsaní set, najdi prvního člena (ffm) z Zapsaní set, což je a Student zaznamenat a uvést jej. Pak byste šli do smyčky: Najít dalšího člena (fnm) a uveďte jej. Když fnm selhalo, opustili byste smyčku.

To se může pro programátora databáze zdát jako hodně práce, ale v době provádění to bylo velmi efektivní. Odborníci jako Michael Stonebraker z University of California v Berkeley a Ingres poukázali na to, že provádění tohoto druhu dotazu v databázi CODASYL, jako je IDMS, trvalo zhruba polovinu času CPU a méně než polovinu paměti jako stejný dotaz v relační databázi pomocí SQL .

Pro srovnání by ekvivalentní dotaz SQL, který by vrátil všechny studenty v CS 101, byl něco jako

VYBERTE student.název FROM kurzů, zapsaných, studentů KDE kurz.název

Tato syntaxe implikuje relační vnitřní spojení (ve skutečnosti dva z nich), jak vysvětlím níže, a vynechává některé důležité podrobnosti, například pole použitá pro spojení.

Relační databáze a SQL

Proč byste se vzdali faktoru dvou vylepšení rychlosti provádění a využití paměti? Byly tam dva velké důvody: snadnost vývoje a přenositelnost. V roce 1980 jsem si nemyslel, že by na jednom z nich záleželo ve srovnání s požadavky na výkon a paměť, ale jak se zlepšoval počítačový hardware a stal se levnějším, lidé se přestali zajímat o rychlost a paměť provedení a více se obávají nákladů na vývoj.

Jinými slovy, Mooreův zákon zabil databáze CODASYL ve prospěch relačních databází. Jak se to stalo, zlepšení doby vývoje bylo významné, ale přenositelnost SQL se ukázala jako sen.

Odkud pochází relační model a SQL? EF „Ted“ Codd byl počítačový vědec z IBM San Jose Research Laboratory, který vypracoval teorii relačního modelu v 60. letech a publikoval ji v roce 1970. IBM implementovala relační databázi pomalu ve snaze chránit výnosy jeho databáze CODASYL IMS / DB. Když IBM konečně zahájila svůj projekt System R, vývojový tým (Don Chamberlin a Ray Boyce) nespadal pod Codda a ignorovali Codtovu relační jazykovou verzi z roku 1971 Alpha, aby navrhli svůj vlastní jazyk, SEQUEL (Structured English Query Language). V roce 1979, ještě předtím, než IBM uvedla svůj produkt, Larry Ellison začlenil tento jazyk do své databáze Oracle (jako specifikaci použil publikace SEQUEL před uvedením IBM). SEQUEL se brzy stal SQL, aby nedošlo k porušení mezinárodní ochranné známky.

„Tom-tomy bití pro SQL“ (jak řekl Michael Stonebraker) přicházely nejen od Oracle a IBM, ale také od zákazníků. Nebylo snadné najmout nebo vyškolit návrháře a programátory databází CODASYL, takže SEQUEL (a SQL) vypadaly mnohem atraktivněji. V pozdních osmdesátých letech byl SQL natolik atraktivní, že mnoho prodejců databází v zásadě našlo na svých databázích CODASYL procesor dotazů SQL, a to k velkému zděšení Codda, který cítil, že relační databáze musí být navrženy od začátku, aby byly relační.

Čistá relační databáze, jak ji navrhl Codd, je postavena na n-tice seskupené do relací, konzistentní s predikátovou logikou prvního řádu. Reálné databáze v reálném světě obsahují tabulky, které obsahují pole, omezení a spouštěče, a tabulky jsou příbuzné prostřednictvím cizích klíčů. SQL se používá k deklaraci dat, která mají být vrácena, a procesor dotazu SQL a optimalizátor dotazů změní deklaraci SQL na plán dotazu, který je spuštěn databázovým strojem.

SQL obsahuje subjazyk pro definování schémat, jazyk definice dat (DDL), spolu s subjazykem pro úpravu dat, jazyk manipulace s daty (DML). Oba mají kořeny v časných specifikacích CODASYL. Třetí subjazyk v SQL deklaruje dotazy prostřednictvím VYBRAT prohlášení a relační spojení.

SQL`VYBRAT` prohlášení

The VYBRAT příkaz říká optimalizátoru dotazů, jaká data se mají vrátit, jaké tabulky mají hledat, jaké vztahy mají sledovat a jaké pořadí mají uvalit na vrácená data. Optimalizátor dotazů musí sám zjistit, jaké indexy použít, aby se zabránilo skenování tabulky hrubou silou a dosáhlo dobrého výkonu dotazu, pokud konkrétní databáze nepodporuje tipy na index.

Část umění designu relačních databází závisí na uvážlivém používání indexů. Pokud pro častý dotaz vynecháte index, může se celá databáze zpomalit při velkém zatížení čtení. Pokud máte příliš mnoho indexů, může se celá databáze zpomalit při vysokém zatížení zápisu a aktualizace.

Dalším důležitým uměním je výběr dobrého, jedinečného primárního klíče pro každý stůl. Musíte nejen vzít v úvahu dopad primárního klíče na běžné dotazy, ale také to, jak bude hrát ve spojeních, když se v jiné tabulce objeví jako cizí klíč, a jak to ovlivní referenční lokalitu dat.

V pokročilém případě databázových tabulek, které jsou rozděleny do různých svazků v závislosti na hodnotě primárního klíče, zvané horizontální horizontální dělení, musíte také zvážit, jak primární klíč ovlivní dělení. Tip: Chcete, aby byla tabulka distribuována rovnoměrně mezi svazky, což naznačuje, že nechcete používat jako primární klíče datová razítka nebo po sobě jdoucí celá čísla.

Diskuze o VYBRAT prohlášení může začít jednoduše, ale může se rychle stát matoucím. Zvážit:

VYBERTE * OD ZÁKAZNÍKŮ;

Jednoduché, že? Požádá o všechna pole a všechny řádky souboru Zákazníci stůl. Předpokládejme však, že Zákazníci tabulka má sto milionů řádků a sto polí a jedním z polí je velké textové pole pro komentáře. Jak dlouho bude trvat, než se všechna tato data přenesou přes síťové připojení 10 megabitů za sekundu, pokud každý řádek obsahuje v průměru 1 kilobajt dat?

Možná byste měli snížit, kolik toho přes drát pošlete. Zvážit:

VYBERTE TOP 100 název společnosti, lastSaleDate, lastSaleAmount, totalSalesAmount OD ZÁKAZNÍKŮ

KDE stát a město

OBJEDNAT PODLE lastSaleDate POPIS;

Nyní budete stahovat mnohem méně dat. Požádali jste databázi, aby vám poskytla pouze čtyři pole, zvážila pouze společnosti v Clevelandu a poskytla vám pouze 100 společností s nejaktuálnějším prodejem. Chcete-li to nejúčinněji na databázovém serveru, však Zákazníci tabulka potřebuje index na stát + město pro KDE klauzule a index na lastSaleDate pro SEŘADIT PODLE a TOP 100 doložky.

Mimochodem, TOP 100 je platný pro SQL Server a SQL Azure, ale ne MySQL nebo Oracle. V MySQL byste použili LIMIT 100 po KDE doložka. V Oracle byste použili vázané na ROWNUM jako součást KDE klauzule, tj. KDE ... A ROWNUM <= 100. Normy ANSI / ISO SQL (a je jich doposud devět, sahajících od roku 1986 do roku 2016) bohužel jdou jen tak daleko, nad rámec čehož každá databáze zavádí své vlastní klauzule a funkce.

SQL se připojí

Zatím jsem popsal VYBRAT syntaxe pro jednotlivé tabulky. Než to vysvětlímPŘIPOJIT klauzule, musíte porozumět cizím klíčům a vztahům mezi tabulkami. Vysvětlím to pomocí příkladů v DDL pomocí syntaxe serveru SQL Server.

Krátká verze je poměrně jednoduchá. Každá tabulka, kterou chcete použít ve vztazích, by měla mít omezení primárního klíče; může to být buď jedno pole, nebo kombinace polí definovaných výrazem. Například:

VYTVOŘIT TABULKU Osoby (
    PersonID int NOT NULL PRIMARY KEY,
    PersonName char (80),
    ...

Každá tabulka, které se musí týkat Osoby by měl mít pole, které odpovídá Osoby primární klíč a pro zachování relační integrity by toto pole mělo mít omezení cizího klíče. Například:

VYTVOŘIT TABULKU Objednávky (
    OrderID int NOT NULL PRIMARY KEY,
    ...
    PersonID int ZAHRANIČNÍ KLÍČOVÉ REFERENCE Osoby (PersonID)
);

Existují delší verze obou příkazů, které používají OMEZENÍ klíčové slovo, které vám umožní pojmenovat omezení. To generuje většina nástrojů pro návrh databází.

Primární klíče jsou vždy indexovány a jedinečné (hodnoty polí nelze duplikovat). Další pole lze volitelně indexovat. Často je užitečné vytvářet indexy pro pole cizích klíčů a pro pole, která se objevují v KDE a SEŘADIT PODLE klauzule, i když ne vždy, kvůli potenciální režii zápisů a aktualizací.

Jak byste napsali dotaz, který vrátí všechny objednávky zadané Johnem Doem?

VYBERTE PersonName, OrderID FROM Persons

INNER JOIN Objednávky ON Persons.PersonID = Objednávky.PersonID

KDE PersonName;

Ve skutečnosti existují čtyři druhy PŘIPOJIT: VNITŘNÍ, VNĚJŠÍ, VLEVO, ODJET, a ŽE JO. The VNITŘNÍ SPOJENÍ je výchozí (slovo můžete vynechat VNITŘNÍ) a je to ten, který obsahuje pouze řádky, které obsahují odpovídající hodnoty v obou tabulkách. Chcete-li zobrazit seznam osob bez ohledu na to, zda mají nebo nemají objednávky, použijete a VLEVO SE PŘIPOJTE, například:

VYBERTE PersonName, OrderID FROM Persons

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

OBJEDNAT PODLE PersonName;

Když začnete dělat dotazy, které spojují více než dvě tabulky, které používají výrazy nebo vynucují datové typy, může být syntaxe zpočátku trochu chlupatá. Naštěstí existují nástroje pro vývoj databází, které vám mohou generovat správné dotazy SQL, často přetažením tabulek a polí ze schématu do schématu dotazu.

SQL uložené procedury

Někdy deklarativní povaha VYBRAT prohlášení vás nedostane tam, kam chcete. Většina databází má zařízení zvané uložené procedury; bohužel v této oblasti téměř všechny databáze používají proprietární rozšíření standardů ANSI / ISO SQL.

Na serveru SQL Server byl počáteční dialekt pro uložené procedury (nebo uložené procs) Transact-SQL, alias T-SQL; v Oracle to bylo PL-SQL. Obě databáze přidaly další jazyky pro uložené procedury, jako je C #, Java a R. Jednoduchá uložená procedura T-SQL může být pouze parametrizovanou verzí VYBRAT prohlášení. Jeho výhodou je snadné použití a účinnost. Uložené procedury jsou optimalizovány, když jsou uloženy, ne pokaždé, když jsou provedeny.

Složitější uložená procedura T-SQL může používat více příkazů SQL, vstupní a výstupní parametry, lokální proměnné, ZAČNĚTE ... KONEC bloky, JESTLIŽE PAK JINAK podmínky, kurzory (zpracování sady po řádcích), výrazy, dočasné tabulky a celá řada dalších procedurálních syntaxí. Je zřejmé, že pokud je jazykem uložené procedury C #, Java nebo R, budete používat funkce a syntaxi těchto procedurálních jazyků. Jinými slovy, navzdory skutečnosti, že motivací pro SQL bylo používat standardizované deklarativní dotazy, v reálném světě vidíte spoustu procedurálního programování serveru specifického pro databázi.

To nás docela nevrací zpět do starých špatných časů programování databáze CODASYL (i když se kurzory blíží), ale vrací se zpět od myšlenek, že příkazy SQL by měly být standardizovány a že problémy s výkonem by měly být ponechány optimalizátoru databázových dotazů . Nakonec je zdvojnásobení výkonu často příliš mnoho na to, abychom ho nechali na stole.