Что такое SQL? Плюсы и минусы языка запросов к базе данных

Андрей Васенин

Андрей Васенин

Автор статьи. Сфера интересов: ИТ-специалист (программирование, администрирование, DBA). Кандидат экономических наук. Подробнее .

Преимущества и недостатки языка SQL для баз данныхSQL - это не самый быстрый и не самый элегантный способ "разговаривать" с базами данных, но это лучший способ, который у нас есть на текущий момент. Вот почему мы так активно его используем!

Сегодня Structured Query Language (SQL) является стандартным средством манипулирования и формирования запроса к данным в реляционных базах данных, хотя с определенными запатентованными расширениями среди коммерческих продуктов (например, Oracle SQL, названный PL/SQL). Стандартизация является одним из главных преимуществ языка. Второй плюс - это простота и вездесущность SQL, что даже заставило создателей многих «NoSQL» или нереляционных хранилищ данных, таких как Hadoop, принять подмножества SQL или создать собственные SQL-подобные языки запросов.

Но SQL не всегда был «универсальным» языком для реляционных баз данных. С самого начала (около 1980 года) SQL имел и негативные мнения в отношении полезности этого языка. Многие исследователи и разработчики в то время, считали, что накладные расходы SQL не позволят ему когда-либо быть практичным в производственной базе данных. Этот агргумент высказывался в качестве главного недостатка языка. Сейчас ясно, что они были неправы. Но многие по-прежнему считают, что при всей легкости и доступности SQL накладные расходы на время исполнения команд зачастую слишком высоки.

 

Эра до появления SQL

До того, как появился SQL, базы данных имели жесткие интерфейсы навигационного программирования и, как правило, были разработаны вокруг сетевой схемы, называемой моделью данных Codasyl. Codasyl (Комитет по языкам систем данных) был консорциумом, который отвечал за язык программирования Cobol (начиная с 1959 года) и расширением языка баз данных, появившийся 10 лет спустя.

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

Скажем, вы хотели перечислить всех студентов, зачисленных в группу «Прог А». Сначала вы найдете «Прог А» в курсах университета, заданных по имени, зададите условие, что в качестве владельца или родителя набора «Абитуриенты» будет  «Прог А» . Затем найдите первый член (ФИО) набора «Абитуриенты» , который является записью типа Студент, и перечислите их. Затем вы перейдете в цикл: найдите следующего члена (ФИО) и перечислите его. Когда очередное ФИО найти не удалось, вы должны выйти из цикла.

Это может показаться излишне большой работой для программиста базы данных, но это было очень эффективно во время выполнения кода программы. Эксперты, такие как Майкл Стоунбрейкер из Калифорнийского университета в Беркли и Ингрес, отметили, что выполнение такого запроса в базе данных Codasyl (на примере IDMS) занимает примерно в половину меньше процессорного времени и в половину меньше объема памяти в сравнении с аналогичным запросом в реляционной базе данных с использованием SQL.

 Для сравнения, эквивалентный SQL-запрос, чтобы вернуть всех студентов в Прог А, будет чем-то вроде:

SELECT student.name FROM courses, enrollees, students WHERE course.name =" Прог А"

Этот синтаксис подразумевает реляционное внутреннее соединение (фактически два из них), как я объясню ниже, и не учитывает некоторые важные детали, такие как поля, используемые для объединений.

 

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

Итак, к чему вам отказываться от удвоения скорости выполнения и высвобождения половины памяти? Существовали две большие причины: простота использования и мобильность. Я думаю, что в 1980 году ни одина из этих причин не имела большого значения по сравнению с требованиями к производительности и памяти, но по мере того, как компьютерное оборудование улучшилось и стало дешевле, люди перестали заботиться о скорости выполнения и памяти, и стали больше беспокоиться о стоимости разработки программных продуктов.

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

Откуда взялась реляционная модель и SQL? Эдгар Кодд (Edgar F. Codd) был компьютерным ученым в исследовательской лаборатории IBM San Jose, которая разработала теорию реляционной модели в 1960-х годах и опубликовала ее в 1970 году. IBM не спешила внедрять реляционную базу данных, чтобы не снижать доходы от продажи своей базы данных Codasyl IMS / DB. Когда IBM наконец запустила свой проект System R, команда разработчиков (Дон Чемберлин и Рэй Бойс) не была в сотрудничестве с Коддом, и поэтому они проигнорировали реляционную документацию Кодда 1971 Alpha при разработке собственного языка Sequel (структурированный английский язык запросов). В 1979 году, до того, как IBM выпустила свой продукт, Ларри Эллисон включил язык в свою базу данных Oracle (используя в качестве своей спецификации публикации IBM pre-launch Sequel). Продолжение вскоре стало именоваться SQL, чтобы избежать нарушения международного товарного знака.

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

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

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

 

Оператор SELECT в SQL

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

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

Другим важным искусством является выбор хорошего, уникального первичного ключа для каждой таблицы. Вы не только должны учитывать влияние первичного ключа на общие запросы, но и то, как он будет «играть» в объединениях (JOIN), когда он будет отображаться как внешний ключ в другой таблице, и как это повлияет на ссылки локальных данных.

В более сложном случае, когда таблиц базы данных разбиваются на разные тома в зависимости от значения первичного ключа, называемого горизонтальным осколком (horizontal sharding), вы также должны учитывать, как первичный ключ влияет на осколки. Подсказка. Вы хотите, чтобы таблица распределялась равномерно по томам, что предполагает не использование дата-меток (date stamps) или целых числ в качестве первичных ключей.

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

SELECT * FROM Customers;

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

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY 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 год) только зашли так далеко, что каждая база данных вводит свои собственные операторы и функции.

 

Реляционные соединения (JOIN) в SQL

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

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

CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    PersonName char(80),
    ...
 

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

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    ...
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

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

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

Как вы напишете запрос, который возвращает все заказы (OrderID), поставленные Джону Доу (John Doe)?

SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";

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

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;

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

 

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

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

В SQL Server начальный диалект для хранимых процедур (или stored procs) был Transact-SQL, aka T-SQL; в Oracle это был PL/SQL. Обе базы данных добавили дополнительные языки для хранимых процедур на C #, Java и R. Простая хранимая процедура T-SQL может быть только параметризованной версией инструкции SELECT. Ее преимущества - простота использования и эффективность. Хранимые процедуры оптимизируются в момент, когда они сохраняются, а не каждый раз, когда они выполняются.

Более сложная хранимая процедура T-SQL может использовать несколько операторов SQL, входные и выходные параметры, локальные переменные, BEGIN ... END блоки, IF ... THEN ... ELSE условия, курсоры (пошаговая обработка set), выражения, временные таблицы и целый ряд других процедурных синтаксисов. Очевидно, что если язык хранимой процедуры - это C #, Java или R, вы будете использовать функции и синтаксис этих процедурных языков. Другими словами, несмотря на то, что мотивация для SQL заключалась в использовании стандартизированных декларативных запросов, в реальном мире вы вынуждены использовать процедурное программирование, специфичное для каждой конкретной базы данных (бренда).

Не то, что бы это был возврат к плохим старым запросам программирования на Codasyl (хотя курсоры приближают нас к этому), но это отход от идей, которые должны были стандартизировать SQL-операторы, и что проблемы с производительностью следует решать на уровне оптимизатора запросов к базе данных. В конце концов, удвоение производительности не так и мало, чтобы навсегда забыть об этой идее. Не так ли? А пока продолжаем активно использовать язык запросов SQL на своих базах данных со всеми его преимуществами и недостатками, со всеми плюсами и минусами....

Вас заинтересует / Intresting for you:

Что такое база данных  и СУБД?
Что такое база данных и СУБД? 9050 просмотров Светлана Mon, 21 Oct 2019, 17:58:45
Языки баз данных: DDL, DML, 4G...
Языки баз данных: DDL, DML, 4G... 13717 просмотров Ирина Светлова Wed, 10 Feb 2021, 17:30:59
База данных как объект правово...
База данных как объект правово... 1567 просмотров Денис Wed, 27 Mar 2019, 03:16:24
Перенос корпоративных баз данн...
Перенос корпоративных баз данн... 2774 просмотров Дэн Fri, 27 Sep 2019, 07:52:18
Войдите чтобы комментировать