Какво е SQL? Lingua franca за анализ на данни

Днес структурираният език за заявки е стандартното средство за манипулиране и заявки за данни в релационни бази данни, макар и със собствени разширения сред продуктите. Лекотата и повсеместността на SQL дори са накарали създателите на много „NoSQL“ или нерелационни хранилища на данни, като Hadoop, да приемат подмножества на SQL или да измислят свои собствени SQL-подобни езици за заявки.

Но SQL не винаги е бил „универсалният“ език за релационни бази данни. От самото начало (около 1980 г.) SQL имаше определени удари срещу него. Много изследователи и разработчици по това време, включително и аз, мислеха, че режийните разходи на SQL ще го предпазят от практическа работа в производствена база данни.

Явно сме сгрешили. Но мнозина все още вярват, че при цялата лекота и достъпност на SQL цената, наложена при изпълнението, често е твърде висока.

SQL история

Преди да е имало SQL, базите данни са имали тесни интерфейси за навигационно програмиране и обикновено са били проектирани около мрежова схема, наречена CODASYL модел на данни. CODASYL (Комитет по езиците на системите за данни) е консорциум, който отговаря за езика за програмиране COBOL (започвайки през 1959 г.) и разширенията на езика на базата данни (започвайки 10 години по-късно).

Когато сте програмирали срещу база данни CODASYL, сте се придвижвали до записи чрез набори, които изразяват взаимоотношения един към много. По-старите йерархични бази данни позволяват запис да принадлежи само на един набор. Мрежовите бази данни позволяват на записа да принадлежи към множество набори.

Да речем, че искате да изброите учениците, записани в CS 101. Първо ще намерите "CS 101"в Coursesнабора по име, задайте го като собственик или родител на Enrolleesнабора, намерете първия член ( ffm) на Enrolleesнабора, който е Studentзапис, и списък то. След това ще влезете в цикъл: Намерете следващия член ( fnm) и го избройте. Когато се fnmпровали, ще излезете от цикъла.

Това може да изглежда като много изследователска работа за програмиста на базата данни, но беше много ефективна по време на изпълнение. Експерти като Майкъл Стоунбракър от Калифорнийския университет в Бъркли и Ингрес посочиха, че извършването на този вид заявка в база данни CODASYL като IDMS отнема приблизително половината време на процесора и по-малко от половината памет като същата заявка в релационна база данни, използваща SQL .

За сравнение, еквивалентната SQL заявка за връщане на всички ученици в CS 101 би била нещо подобно 

ИЗБЕРЕТЕ student.name ОТ курсове, записващи се, студенти КЪДЕ course.name

Този синтаксис предполага релационно вътрешно съединение (всъщност две от тях), както ще обясня по-долу, и оставя някои важни подробности, като полетата, използвани за обединенията.

Релационни бази данни и SQL

Защо бихте се отказали от двукратно подобрение в скоростта на изпълнение и използването на паметта? Имаше две големи причини: лекота на разработка и преносимост. Не мислех, че нито един от тях няма особено значение през 1980 г. в сравнение с изискванията за производителност и памет, но тъй като компютърният хардуер се подобряваше и поевтиняваше, хората престанаха да се грижат за скоростта на изпълнение и паметта и се тревожеха повече за разходите за разработка.

С други думи, законът на Мур убива базите данни CODASYL в полза на релационните бази данни. В крайна сметка подобрението във времето за разработка беше значително, но преносимостта на SQL се оказа мечта.

Откъде идват релационният модел и SQL? EF “Ted” Codd беше компютърен учен в изследователската лаборатория на IBM Сан Хосе, който разработи теорията на релационния модел през 60-те години и го публикува през 1970 г. IBM бавно внедрява релационна база данни в опит да защити приходите на неговата база данни CODASYL IMS / DB. Когато IBM най-накрая стартира своя проект System R, екипът за разработка (Дон Чембърлин и Рей Бойс) не беше под управлението на Codd и те игнорираха референтния език за алфа от 1971 г. на Codd, за да проектират свой собствен език SEQUEL (Structured English Query Language). През 1979 г., преди IBM дори да пусне своя продукт, Лари Елисън включи езика в своята база данни на Oracle (използвайки публикациите на IBM преди стартирането SEQUEL като негова спецификация). SEQUEL скоро се превърна в SQL, за да избегне международно нарушение на търговската марка.

„Tom-toms бият за SQL“ (както се изрази Майкъл Стоунбракър) идваше не само от Oracle и IBM, но и от клиентите. Не беше лесно да се наемат или обучат дизайнери и програмисти на бази данни на CODASYL, така че SEQUEL (и SQL) изглеждаха много по-привлекателни. SQL беше толкова привлекателен през по-късните 80-те години, че много доставчици на бази данни по същество скрепиха процесор за SQL заявки върху своите бази данни CODASYL, за голямо ужас на Codd, който смяташе, че релационните бази данни трябва да бъдат проектирани от нулата, за да бъдат релационни.

Чистата релационна база данни, както е проектирана от Codd, е изградена върху кортежи, групирани в релации, в съответствие с предикатната логика от първи ред. Релационните бази данни в реалния свят имат таблици, които съдържат полета, ограничения и задействания, а таблиците са свързани чрез външни ключове. SQL се използва за деклариране на данните, които трябва да бъдат върнати, а процесор на SQL заявки и оптимизатор на заявки превръщат SQL декларацията в план за заявки, който се изпълнява от механизма на базата данни.

SQL включва под-език за дефиниране на схеми, езика за дефиниране на данни (DDL), заедно с под-език за промяна на данни, езика за манипулиране на данни (DML). И двете имат корени в ранните спецификации на CODASYL. Третият подезик в SQL декларира заявки чрез SELECTоператора и релационни съединения.

SQL  SELECTоператор

В SELECTизявлението казва оптимизатора на заявки какви данни да възвръщаемост, това, което таблици, за да гледам в какво отношения да се следват, и това, за да се налагат на върнатите данни. Оптимизаторът на заявки трябва сам да разбере какви индекси да използва, за да избегне сканиране на таблица с груба сила и да постигне добра производителност на заявката, освен ако конкретната база данни не поддържа подсказки за индекс.

Част от изкуството на релационния дизайн на базата данни зависи от разумното използване на индексите. Ако пропуснете индекс за честа заявка, цялата база данни може да се забави при големи натоварвания при четене. Ако имате твърде много индекси, цялата база данни може да се забави при големи натоварвания при запис и актуализация.

Друго важно изкуство е изборът на добър, уникален първичен ключ за всяка маса. Не само трябва да вземете предвид въздействието на първичния ключ върху често срещаните заявки, но как той ще играе в обединенията, когато се появи като чужд ключ в друга таблица, и как ще повлияе на местоположението на референтните данни.

В разширения случай на таблици с бази данни, които са разделени на различни томове в зависимост от стойността на първичния ключ, наречен хоризонтално рязане, вие също трябва да обмислите как първичният ключ ще повлияе на заличаването. Съвет: Искате таблицата да бъде разпределена равномерно по томове, което предполага, че не искате да използвате печати с дата или последователни цели числа като първични ключове.

Обсъжданията на SELECTизявлението могат да започнат просто, но бързо могат да станат объркващи. Обмисли:

ИЗБЕРЕТЕ * ОТ клиенти;

Просто, нали? Той иска всички полета и всички редове на Customersтаблицата. Да предположим обаче, че Customersтаблицата има сто милиона реда и сто полета, а едно от полетата е голямо текстово поле за коментари. Колко време ще отнеме изтеглянето на всички тези данни с 10 мегабита в секунда мрежова връзка, ако всеки ред съдържа средно 1 килобайт данни?

Може би трябва да намалите колко изпращате по жицата. Обмисли:

ИЗБЕРЕТЕ ТОП 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount ОТ клиенти
КЪДЕ щат и град
ПОРЪЧАЙТЕ ПО LastSaleDate DESCENDING;

Сега ще изтеглите много по-малко данни. Поискахте базата данни да ви даде само четири полета, да разгледате само компаниите в Кливланд и да ви даде само 100-те компании с най-новите продажби. За да се направи това най-ефективно на сървъра на базата данни, обаче, Customersтаблицата се нуждае от индекс за state+cityза WHEREклаузата и индекс за lastSaleDateза ORDER BYи TOP 100клаузите.

Между другото, TOP 100важи за SQL Server и SQL Azure, но не и за MySQL или Oracle. В MySQL бихте използвали LIMIT 100след WHEREклаузата. В Oracle, който ще използвате за граница на ROWNUMкато част от WHEREклаузата, т.е. WHERE... AND ROWNUM <=100. За съжаление, стандартите ANSI / ISO SQL (а към днешна дата има девет от тях, простиращи се от 1986 до 2016 г.) стигат само толкова далеч, след което всяка база данни въвежда свои собствени клаузи и функции.

SQL се присъединява 

Досега описах SELECTсинтаксиса за единични таблици. Преди да мога да обясня  JOINклаузите, трябва да разберете външните ключове и връзките между таблиците. Ще обясня това, като използвам примери в DDL, като използвам синтаксиса на SQL Server.

Кратката версия на това е доста проста. Всяка таблица, която искате да използвате в отношенията, трябва да има ограничение за първичен ключ; това може да бъде едно поле или комбинация от полета, дефинирани от израз. Например:

СЪЗДАВАНЕ НА МАСА Лица (
    PersonID int НЕ НУЛЕН ОСНОВЕН КЛЮЧ,
    Име на човек char (80),
    ...

Всяка таблица, към която трябва да се свързва, Personsтрябва да има поле, което съответства на Personsпървичния ключ, а за запазване на релационната цялост това поле трябва да има ограничение за външен ключ. Например:

СЪЗДАЙТЕ ТАБЛИЦА Поръчки (
    OrderID int НЕ НУЛЕН ОСНОВЕН КЛЮЧ,
    ...
    PersonID int ВЪНШНИ КЛЮЧОВИ РЕФЕРЕНЦИИ Persons (PersonID)
);

Има по-дълги версии на двата твърдения, които използват CONSTRAINTключовата дума, която ви позволява да назовете ограничението. Това генерират повечето инструменти за проектиране на бази данни.

Първичните ключове винаги са индексирани и уникални (стойностите на полетата не могат да бъдат дублирани). Други полета могат по желание да бъдат индексирани. Често е полезно да се създават индекси за полета с външен ключ и за полета, които се появяват в WHEREи ORDER BYклаузи, макар и не винаги, поради потенциалните режийни разходи от записи и актуализации.

Как бихте написали заявка, която връща всички поръчки, направени от Джон Доу?

ИЗБЕРЕТЕ PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
КЪДЕ PersonName;

В действителност, има четири вида JOIN: INNER, OUTER, LEFT, и RIGHT. Това INNER JOINе по подразбиране (можете да пропуснете думата INNER) и това е, което включва само редове, които съдържат съвпадащи стойности в двете таблици. Ако искате да изброите хората, независимо дали имат поръчки или не, бихте използвали LEFT JOINнапример:

ИЗБЕРЕТЕ PersonName, OrderID FROM Persons
НАЛЯВО СЕ ПРИСЪЕДИНЯВА Поръчки НА Persons.PersonID = Поръчки.PersonID
ПОРЪЧКА ПО ИМЕ НА ЛИЦА;

Когато започнете да правите заявки, които се присъединяват към повече от две таблици, които използват изрази или принуждават типове данни, синтаксисът може да стане малко космат в началото. За щастие има инструменти за разработка на бази данни, които могат да генерират правилни SQL заявки за вас, често чрез плъзгане и пускане на таблици и полета от схемата в диаграма на заявката.

SQL съхранени процедури

Понякога декларативният характер на SELECTизявлението не ви отвежда там, където искате да отидете. Повечето бази данни имат съоръжение, наречено съхранени процедури; за съжаление това е област, в която почти всички бази данни използват собствени разширения на ANSI / ISO SQL стандартите.

В SQL Server първоначалният диалект за съхранени процедури (или съхранени procs) беше Transact-SQL, известен още като T-SQL; в Oracle това беше PL-SQL. И двете бази данни са добавили допълнителни езици за съхранени процедури, като C #, Java и R. Една проста T-SQL съхранена процедура може да бъде само параметризирана версия на SELECTизявление. Неговите предимства са лекота на използване и ефективност. Съхранените процедури се оптимизират, когато се записват, а не всеки път, когато се изпълняват.

По-сложна T-SQL съхранена процедура може да използва множество SQL изрази, входни и изходни параметри, локални променливи, BEGIN...ENDблокове, IF...THEN...ELSEусловия, курсори (обработка на ред по ред), изрази, временни таблици и цял набор от други процедурен синтаксис. Очевидно, ако езикът на съхранената процедура е C #, Java или R, ще използвате функциите и синтаксиса на тези процедурни езици. С други думи, въпреки факта, че мотивацията за SQL беше да се използват стандартизирани декларативни заявки, в реалния свят виждате много специфично процедурно сървърно програмиране на база данни.

Това съвсем не ни връща към лошите стари времена на програмирането на базата данни CODASYL (въпреки че курсорите се приближават), но се връща от идеите, че SQL изявленията трябва да бъдат стандартизирани и че опасенията за производителността трябва да бъдат оставени на оптимизатора на заявките към базата данни . В крайна сметка удвояването на представянето често е твърде много, за да остане на масата.

Научете SQL

Изброените по-долу сайтове могат да ви помогнат да научите SQL или да откриете странностите на различни SQL диалекти.