Что такое 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? Э. Ф. «Тед» Кодд был специалистом по информатике в исследовательской лаборатории IBM в Сан-Хосе, который разработал теорию реляционной модели в 1960-х годах и опубликовал ее в 1970 году. IBM не спешила внедрять реляционную базу данных, чтобы защитить доходы компании. его база данных CODASYL IMS / DB. Когда IBM, наконец, запустила свой проект System R, команда разработчиков (Дон Чемберлин и Рэй Бойс) не руководила Коддом, и они проигнорировали статью Кодда 1971 года по реляционному языку Alpha, чтобы разработать свой собственный язык, SEQUEL (Structured English Query Language). В 1979 году, еще до того, как IBM выпустила свой продукт, Ларри Эллисон включил этот язык в свою базу данных Oracle (используя предварительные публикации IBM SEQUEL в качестве спецификации). SEQUEL вскоре стал SQL, чтобы избежать нарушения международного товарного знака.

«Томаты для SQL» (как выразился Майкл Стоунбрейкер) исходили не только от Oracle и IBM, но и от заказчиков. Было непросто нанять или обучить разработчиков и программистов баз данных CODASYL, поэтому SEQUEL (и SQL) выглядели намного привлекательнее. В конце 1980-х годов SQL был настолько привлекательным, что многие поставщики баз данных по существу встраивали обработчик запросов SQL поверх своих баз данных CODASYL, к великому разочарованию Кодда, который считал, что реляционные базы данных должны разрабатываться с нуля, чтобы быть реляционными.

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

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

SQL  SELECTзаявление

Этот SELECTоператор сообщает оптимизатору запросов, какие данные возвращать, в каких таблицах искать, каким отношениям следовать и какой порядок наложить на возвращаемые данные. Оптимизатор запросов должен сам определить, какие индексы использовать, чтобы избежать сканирования таблиц методом грубой силы и добиться хорошей производительности запросов, если конкретная база данных не поддерживает подсказки индекса.

Часть искусства проектирования реляционных баз данных зависит от разумного использования индексов. Если вы опустите индекс для частого запроса, вся база данных может замедлиться при больших нагрузках чтения. Если у вас слишком много индексов, вся база данных может замедлиться при больших нагрузках на запись и обновление.

Еще одно важное искусство - это выбор хорошего уникального первичного ключа для каждой таблицы. Вы должны учитывать не только влияние первичного ключа на общие запросы, но и то, как он будет играть в соединениях, когда он появляется в качестве внешнего ключа в другой таблице, и как это повлияет на локальность ссылок на данные.

В расширенном случае таблиц базы данных, которые разделены на разные тома в зависимости от значения первичного ключа, называемого горизонтальным сегментированием, вы также должны учитывать, как первичный ключ повлияет на сегментирование. Подсказка: вы хотите, чтобы таблица была равномерно распределена по томам, что говорит о том, что вы не хотите использовать штампы даты или последовательные целые числа в качестве первичных ключей.

Обсуждение SELECTутверждения может начинаться с простого, но быстро может сбить с толку. Рассматривать:

ВЫБРАТЬ * ОТ клиентов;

Все просто, правда? Он запрашивает все поля и все строки Customersтаблицы. Однако предположим, что в Customersтаблице есть сто миллионов строк и сто полей, и одно из полей является большим текстовым полем для комментариев. Сколько времени потребуется, чтобы загрузить все эти данные через сетевое соединение со скоростью 10 мегабит в секунду, если каждая строка содержит в среднем 1 килобайт данных?

Возможно, вам стоит сократить количество отправлений по сети. Рассматривать:

ВЫБЕРИТЕ TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount ОТ клиентов

ГДЕ штат И город

ЗАКАЗАТЬ ПО lastSaleDate ПО убыванию;

Теперь вы собираетесь получить намного меньше данных. Вы попросили базу данных предоставить вам только четыре поля, чтобы рассмотреть только компании в Кливленде и предоставить вам только 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 NOT NULL PRIMARY KEY,

    Символ PersonName (80),

    ...

Каждая таблица, к которой необходимо относиться, Personsдолжна иметь поле, соответствующее Personsпервичному ключу, и для сохранения реляционной целостности это поле должно иметь ограничение внешнего ключа. Например:

СОЗДАТЬ ТАБЛИЦЫ Заказы (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int ИНОСТРАННЫЕ КЛЮЧЕВЫЕ ССЫЛКИ Лица (PersonID)

);

Существуют более длинные версии обоих операторов, в которых используется CONSTRAINTключевое слово, позволяющее указать имя ограничения. Это то, что генерирует большинство инструментов проектирования баз данных.

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

Как бы вы написали запрос, который возвращает все заказы, размещенные Джоном Доу?

ВЫБЕРИТЕ PersonName, OrderID ОТ людей

INNER JOIN Заказы НА Persons.PersonID = Orders.PersonID

ГДЕ PersonName;

На самом деле, существует четыре вида JOIN: INNER, OUTER, LEFT, и RIGHT. Это INNER JOINзначение по умолчанию (вы можете опустить слово INNER), и оно включает только строки, содержащие совпадающие значения в обеих таблицах. Если вы хотите перечислить людей, есть ли у них заказы, вы можете использовать LEFT JOIN, например:

ВЫБЕРИТЕ PersonName, OrderID ОТ людей

LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID

ЗАКАЗАТЬ ПО PersonName;

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

Хранимые процедуры SQL

Иногда декларативный характер SELECTзаявления не приводит вас туда, куда вы хотите. В большинстве баз данных есть средство, называемое хранимыми процедурами; к сожалению, это область, где почти все базы данных используют проприетарные расширения стандартов ANSI / ISO SQL.

В SQL Server исходным диалектом для хранимых процедур (или хранимых процедур) был 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.