Co to jest SQL? Lingua franca analizy danych

Obecnie Structured Query Language jest standardowym sposobem manipulowania i odpytywania danych w relacyjnych bazach danych, choć z zastrzeżonymi rozszerzeniami produktów. Łatwość i wszechobecność SQL doprowadziła nawet twórców wielu „NoSQL” lub nierelacyjnych magazynów danych, takich jak Hadoop, do przyjęcia podzbiorów SQL lub wymyślenia własnych języków zapytań podobnych do SQL.

Ale SQL nie zawsze był „uniwersalnym” językiem dla relacyjnych baz danych. Od samego początku (około 1980 r.) SQL miał pewne ataki. Wielu badaczy i programistów w tamtym czasie, w tym ja, uważało, że narzut SQL uniemożliwi jego praktyczne zastosowanie w produkcyjnej bazie danych.

Najwyraźniej myliliśmy się. Ale wielu nadal uważa, że ​​pomimo całej łatwości i dostępności SQL, cena wymagana w wydajności środowiska wykonawczego jest często zbyt wysoka.

Historia SQL

Zanim pojawił się SQL, bazy danych miały ścisłe, nawigacyjne interfejsy programistyczne i zazwyczaj były projektowane w oparciu o schemat sieci zwany modelem danych CODASYL. CODASYL (Komitet Języków Systemów Danych) to konsorcjum odpowiedzialne za język programowania COBOL (od 1959 r.) I rozszerzenia języka baz danych (od 10 lat).

Podczas programowania w bazie danych CODASYL przechodziłeś do rekordów za pomocą zestawów, które wyrażają relacje jeden do wielu. W starszych hierarchicznych bazach danych rekord może należeć tylko do jednego zestawu. Sieciowe bazy danych umożliwiają przynależność rekordu do wielu zestawów.

Powiedzmy, że chcesz wyświetlić listę uczniów zapisanych w CS 101. Najpierw znajdziesz "CS 101"w Courseszestawie według nazwy, ustawisz go jako właściciela lub rodzica Enrolleeszestawu, znajdź pierwszego członka ( ffm) Enrolleeszestawu, który jest Studentrekordem i wyświetl to. Następnie przechodzisz do pętli: znajdź następnego członka ( fnm) i wyświetl go. Gdy się fnmnie powiedzie, wyjdziesz z pętli.

Może się wydawać, że programista bazodanowy ma dużo pracy, ale był bardzo wydajny w czasie wykonywania. Eksperci tacy jak Michael Stonebraker z Uniwersytetu Kalifornijskiego w Berkeley i Ingres wskazali, że wykonanie tego rodzaju zapytania w bazie danych CODASYL, takiej jak IDMS, zajęło mniej więcej połowę czasu procesora i mniej niż połowę pamięci w porównaniu z tym samym zapytaniem w relacyjnej bazie danych używającej SQL .

Dla porównania równoważne zapytanie SQL zwracające wszystkich uczniów w CS 101 byłoby czymś w rodzaju 

SELECT student.name FROM kursy, zapisani, studenci WHERE course.name

Ta składnia implikuje relacyjne sprzężenie wewnętrzne (właściwie dwa z nich), jak wyjaśnię poniżej, i pomija niektóre ważne szczegóły, takie jak pola używane do sprzężeń.

Relacyjne bazy danych i SQL

Dlaczego miałbyś zrezygnować z dwukrotnego zwiększenia szybkości wykonywania i wykorzystania pamięci? Były dwa ważne powody: łatwość programowania i przenośność. Wydawało mi się, że w 1980 roku żaden z nich nie miał większego znaczenia w porównaniu z wydajnością i wymaganiami dotyczącymi pamięci, ale w miarę jak sprzęt komputerowy był coraz lepszy i tańszy, ludzie przestali przejmować się szybkością wykonywania i pamięcią i bardziej martwili się kosztami rozwoju.

Innymi słowy, prawo Moore'a zabiło bazy danych CODASYL na rzecz relacyjnych baz danych. Tak się złożyło, że skrócenie czasu rozwoju było znaczące, ale przenośność SQL okazała się mrzonką.

Skąd wziął się model relacyjny i SQL? EF „Ted” Codd był informatykiem w IBM San Jose Research Laboratory, który opracował teorię modelu relacyjnego w latach sześćdziesiątych i opublikował ją w 1970 roku. IBM powoli wdrażał relacyjną bazę danych, starając się chronić dochody jego baza danych CODASYL IMS / DB. Kiedy IBM w końcu rozpoczął projekt System R, zespół programistów (Don Chamberlin i Ray Boyce) nie był pod kierownictwem Codda i zignorowali relacyjny język językowy Alpha Codda z 1971 r., Aby zaprojektować własny język, SEQUEL (Structured English Query Language). W 1979 roku, jeszcze zanim IBM wydał swój produkt, Larry Ellison włączył język do swojej bazy danych Oracle (używając przedpremierowych publikacji IBM SEQUEL jako swojej specyfikacji). SEQUEL wkrótce stał się SQL, aby uniknąć naruszenia międzynarodowego znaku towarowego.

„Tom-tomy bijące dla SQL” (jak to ujął Michael Stonebraker) pochodziły nie tylko od Oracle i IBM, ale także od klientów. Nie było łatwo zatrudnić ani przeszkolić projektantów i programistów baz danych CODASYL, więc SEQUEL (i SQL) wyglądały znacznie atrakcyjniej. SQL był tak atrakcyjny w późnych latach osiemdziesiątych, że wielu producentów baz danych zasadniczo zszywało procesor zapytań SQL na swoich bazach danych CODASYL, ku wielkiemu przerażeniu Codda, który uważał, że relacyjne bazy danych muszą być projektowane od podstaw, aby były relacyjne.

Czysta relacyjna baza danych, zaprojektowana przez Codda, jest zbudowana na krotkach pogrupowanych w relacje, zgodnie z logiką predykatów pierwszego rzędu. Relacyjne bazy danych świata rzeczywistego zawierają tabele zawierające pola, ograniczenia i wyzwalacze, a tabele są powiązane za pomocą kluczy obcych. SQL jest używany do deklarowania danych do zwrócenia, a procesor zapytań SQL i optymalizator zapytań przekształcają deklarację SQL w plan zapytania wykonywany przez silnik bazy danych.

SQL zawiera język podrzędny służący do definiowania schematów, język definicji danych (DDL), a także język podrzędny służący do modyfikowania danych, język manipulacji danymi (DML). Oba mają korzenie we wczesnych specyfikacjach CODASYL. Trzeci język podrzędny w SQL deklaruje zapytania poprzez SELECTinstrukcję i sprzężenia relacyjne.

SELECTInstrukcja SQL 

SELECTOświadczenie informuje optymalizator kwerendy danych do powrotu, co szukać w tabelach, co relacje do naśladowania, a co by nałożyć na zwracanych danych. Optymalizator zapytań musi sam ustalić, jakich indeksów użyć, aby uniknąć skanowania tabeli sił brutalnych i osiągnąć dobrą wydajność zapytań, chyba że dana baza danych obsługuje wskazówki dotyczące indeksów.

Część sztuki projektowania relacyjnych baz danych opiera się na rozsądnym wykorzystaniu indeksów. Jeśli pominiesz indeks przy częstym zapytaniu, cała baza danych może spowolnić przy dużym obciążeniu odczytem. Jeśli masz zbyt wiele indeksów, cała baza danych może spowolnić przy dużym obciążeniu zapisu i aktualizacji.

Kolejną ważną sztuką jest wybór dobrego, unikalnego klucza głównego do każdego stołu. Musisz nie tylko wziąć pod uwagę wpływ klucza podstawowego na typowe zapytania, ale także sposób, w jaki będzie on grał w złączeniach, gdy pojawi się jako klucz obcy w innej tabeli, i jak wpłynie na lokalność odniesienia danych.

W zaawansowanym przypadku tabel bazy danych, które są podzielone na różne woluminy w zależności od wartości klucza podstawowego, zwanego fragmentowaniem poziomym, należy również wziąć pod uwagę, jak klucz podstawowy wpłynie na fragmentowanie. Wskazówka: chcesz, aby tabela była równomiernie rozłożona na woluminy, co sugeruje, że nie chcesz używać znaczników daty ani kolejnych liczb całkowitych jako kluczy podstawowych.

Dyskusje na temat SELECTstwierdzenia mogą zacząć się od prostych, ale szybko mogą stać się zagmatwane. Rozważać:

WYBIERZ * OD klientów;

Proste, prawda? Pyta o wszystkie pola i wszystkie wiersze Customerstabeli. Załóżmy jednak, że Customerstabela ma sto milionów wierszy i sto pól, a jedno z pól jest dużym polem tekstowym przeznaczonym na komentarze. Jak długo zajmie ściągnięcie wszystkich tych danych przez połączenie sieciowe o przepustowości 10 megabitów na sekundę, jeśli każdy wiersz zawiera średnio 1 kilobajt danych?

Może powinieneś zmniejszyć, ile przesyłasz przez drut. Rozważać:

WYBIERZ TOP 100 firmName, lastSaleDate, lastSaleAmount, totalSalesAmount od klientów

GDZIE państwo i miasto

ZAMÓWIENIE WEDŁUG lastSaleDate DESCENDING;

Teraz ściągniesz dużo mniej danych. Poprosiłeś bazę danych o podanie tylko czterech pól, aby uwzględnić tylko firmy w Cleveland i podać tylko 100 firm z ostatnią sprzedażą. Jednak aby zrobić to najskuteczniej na serwerze bazy danych, Customerstabela potrzebuje indeksu state+citydla WHEREklauzuli i indeksu lastSaleDatedla klauzul ORDER BYi TOP 100.

Nawiasem mówiąc, TOP 100jest ważny dla SQL Server i SQL Azure, ale nie dla MySQL lub Oracle. W MySQL użyjesz LIMIT 100po WHEREklauzuli. W Oracle użyłbyś powiązania ROWNUMjako części WHEREklauzuli, tj WHERE... AND ROWNUM <=100. Niestety, standardy ANSI / ISO SQL (a jest ich do tej pory dziewięć, rozciągających się od 1986 do 2016 roku) sięgają tylko tak daleko, poza to każda baza danych wprowadza własne zastrzeżone klauzule i funkcje.

Złącza SQL 

Do tej pory opisałem SELECTskładnię pojedynczych tabel. Zanim wyjaśnię  JOINklauzule, musisz zrozumieć klucze obce i relacje między tabelami. Wyjaśnię to na przykładach w DDL, używając składni SQL Server.

Krótka wersja tego jest dość prosta. Każda tabela, której chcesz użyć w relacjach, powinna mieć ograniczenie klucza podstawowego; może to być pojedyncze pole lub kombinacja pól zdefiniowanych przez wyrażenie. Na przykład:

UTWÓRZ TABELĘ Osoby (

    PersonID int NOT NULL PRIMARY KEY,

    Znak PersonName (80),

    ...

Każda tabela, do której ma się odnosić, Personspowinna mieć pole odpowiadające Personskluczowi głównemu, a aby zachować integralność relacyjną, to pole powinno mieć ograniczenie klucza obcego. Na przykład:

CREATE TABLE Zamówienia (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int KLUCZOWE ODNIESIENIA Osoby (PersonID)

);

Istnieją dłuższe wersje obu instrukcji, które używają CONSTRAINTsłowa kluczowego, które pozwala nazwać ograniczenie. To właśnie generuje większość narzędzi do projektowania baz danych.

Klucze podstawowe są zawsze indeksowane i unikalne (wartości pól nie mogą być powielane). Inne pola mogą być opcjonalnie indeksowane. Często przydatne jest tworzenie indeksów dla pól klucza obcego i pól, które pojawiają się w klauzulach WHEREi ORDER BY, chociaż nie zawsze, ze względu na potencjalne obciążenie związane z zapisami i aktualizacjami.

Jak napisałeś zapytanie, które zwróci wszystkie zamówienia złożone przez Johna Doe?

SELECT PersonName, OrderID FROM Persons

INNER JOIN Zamówienia NA Persons.PersonID = Orders.PersonID

WHERE PersonName;

W rzeczywistości istnieją cztery rodzaje JOIN: INNER, OUTER, LEFT, i RIGHT. Jest INNER JOINto wartość domyślna (możesz pominąć to słowo INNER) i zawiera ona tylko wiersze zawierające pasujące wartości w obu tabelach. Jeśli chcesz wymienić osoby bez względu na to, czy mają zamówienia, czy nie, możesz użyć LEFT JOINna przykład:

SELECT PersonName, OrderID FROM Persons

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

ZAMÓWIENIE WEDŁUG PersonName;

Kiedy zaczynasz wykonywać zapytania, które łączą więcej niż dwie tabele, używają wyrażeń lub wymuszają typy danych, składnia może na początku trochę się skomplikować. Na szczęście istnieją narzędzia programistyczne do tworzenia baz danych, które mogą generować poprawne zapytania SQL, często poprzez przeciąganie i upuszczanie tabel i pól z diagramu schematu do diagramu zapytań.

Procedury składowane SQL

Czasami deklaratywny charakter SELECToświadczenia nie prowadzi Cię tam, gdzie chcesz. Większość baz danych ma funkcję zwaną procedurami składowanymi; niestety jest to obszar, w którym prawie wszystkie bazy danych wykorzystują zastrzeżone rozszerzenia standardów ANSI / ISO SQL.

W SQL Server początkowym dialektem dla procedur składowanych (lub procs składowanych) był Transact-SQL, czyli T-SQL; w Oracle był to PL-SQL. Do obu baz danych dodano dodatkowe języki dla procedur składowanych, takie jak C #, Java i R. Prosta procedura składowana T-SQL może być tylko sparametryzowaną wersją SELECTinstrukcji. Jego zalety to łatwość obsługi i wydajność. Procedury składowane są optymalizowane podczas zapisywania, a nie za każdym razem, gdy są wykonywane.

Bardziej skomplikowana procedura składowana T-SQL może używać wielu instrukcji SQL, parametrów wejściowych i wyjściowych, zmiennych lokalnych, BEGIN...ENDbloków, IF...THEN...ELSEwarunków, kursorów (przetwarzanie zestawu wiersz po wierszu), wyrażeń, tabel tymczasowych i całego szeregu innych składnia proceduralna. Oczywiście, jeśli językiem procedur składowanych jest C #, Java lub R, będziesz używać funkcji i składni tych języków proceduralnych. Innymi słowy, pomimo faktu, że motywacją dla SQL było użycie standardowych zapytań deklaratywnych, w prawdziwym świecie można zobaczyć wiele procedur programowania serwerów specyficznych dla bazy danych.

To nie do końca przenosi nas z powrotem do starych, złych czasów programowania bazy danych CODASYL (chociaż kursory są blisko), ale odchodzi od idei, że instrukcje SQL powinny zostać ujednolicone i że problemy z wydajnością należy pozostawić optymalizatorowi zapytań do bazy danych . W końcu podwojenie wydajności to często zbyt wiele, aby zostawić na stole.

Naucz się SQL

Witryny wymienione poniżej mogą pomóc w nauce języka SQL lub w odkrywaniu dziwactw różnych dialektów SQL.