«Оптимизация запросов в SQL»

Возникнув как фирменный стандарт, SQL по факту приобрел статус международного языка. Его первая полноценная версия известна как стандарт ANSI SQL-89. Этот стандарт оказался далек от совершенства, имел чрезвычайно общий характер и допускал очень широкое толкование. Опыт использования SQL позволил дополнить первый стандарт новыми возможностями, необходимыми для реализации языка в составе коммерческих СУБД. На этой основе был разработан стандарт ANSI SQL-92, который принято считать истинно реляционным. После его принятия стало возможным говорить про стандартную среду SQL-ориентированной СУБД. В настоящее время этот стандарт поддерживается практически всеми современными реляционными СУБД. Хотя конкретные реализации, диалекты SQL, поддерживая стандарт в главном, отличаются от него, как правило, в сторону расширения. Начинающему пользователю SQL полезно знать возможности стандарта.

SQL можно использовать для извлечения данных из любых баз данных. Чтобы получить один и тот же результат, можно написать различные SQL запросы. Для наилучшей производительности необходимо использовать лучшие, наиболее быстрее и эффективные запросы. Так что необходимо конфигурировать запросы на основании требований пользователей и решаемых задач. Эта статья раскрывает каким же образом SQL запросы могут быть оптимизированы для лучшей производительности. Оптимизация запросов тема очень глубокая, но мы будем стараться охватить наиболее важные моменты. В представленной работе будут представлены простые советы по настройке запросов и приемы, которые могут быть применены, чтобы получить немедленный выигрыш в производительности.

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

Итак, цель настоящей работы – исследование особенностей и способов оптимизации запросов в SQL.

Достижение поставленной цели требует решения ряда задач:

1. Определить основные понятия языка SQL.

2. Провести обзор выполнения запросов с использованием статистики чтения/записи.

3. Рассмотреть особенности оптимизации запросов с учетом особенностей разных баз данных.

4. Привести практические рекомендации по оптимизации запросов.

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

· добавление данных;

· модификация данных;

· удаление данных;

· арифметические вычисления и сравнения;

· присваивание и отображение;

· вычисление функций агрегации, выполняемые над однородными элементами данных (вычисление количества, суммы, среднего, минимального или максимального значения и других).

Наиболее распространенным языком запросов реляционной модели данных в настоящее время является язык структурированных запросов SQL (Structured Query Language).

SQL нельзя отнести к конкретному виду языков. Он содержит в себе возможности и языка реляционного исчисления (исчисления кортежей), и алгебраического языка и несомненно является реляционно полным.

В силу исторических причин SQL стал стандартным реляционным языком и в настоящее время поддерживается практически всеми системами баз данных. Поэтому каждый специалист по базам данных должен быть знаком с ним.

В чистом виде как язык, поддерживающий реляционную модель, наиболее популярен стандарт языка, известный как SQL/92 (International Standard Database Language SQL).

Ни один из коммерческих продуктов не поддерживает в полной мере стандарт SQL/92. Известные версии этого языка, по образному выражению Дейта [2], можно назвать «надмножествами подмножеств» языка SQL/92. Другими словами, любая коммерческая система, не поддерживая некоторые аспекты стандарта, в других отношениях, возможно, превосходит его. Это замечание справедливо и в отношении версии SQL Microsoft Jet, реализованной в популярной СУБД MS Access.

Язык SQL является языком декларативного типа. В нем отсутствуют какие-либо команды управления ходом вычислительного процесса типа IF-THEN-ELSE, SWITCH, WHILE, DO-WHILE, FOR, GO TO и др.

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

Интерактивный SQL используется для функционирования непосредственно в базе данных и реализуется непосредственно при вводе пользователем отдельных команд. При такой форме SQL при вводе команды она сейчас же выполнится и вы сможете увидеть результат немедленно.

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

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

· внедрение SQL-операторов в исходный текст программы с последующей ее компиляцией и компоновкой;

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

В учебном пособии, рассчитанном на неподготовленного пользователя SQL, ограничимся интерактивной формой языка. Это даст возможность обсуждать команды и результаты их выполнения, не заботясь о том, как они связаны с помощью интерфейса с другими языками. Интерактивный SQL — это наиболее полезная для непрограммистов форма. Все, что вы узнаете относительно интерактивного SQL, в основном применимо и к вложенной форме.

В соответствии со стандартом SQL выразительные возможности языка распределены по шести типам подмножеств команд:

· язык определения данных (Data Definition Language — DDL), представлен инструкциями CREATE, ALTER, DROP;

· язык манипулирования данными (Data Manipulation Language — DML), представлен инструкциями INSERT, UPDATE, DELETE;

· язык запросов данных (Data Query Language — DQL), представлен многофункциональной командой SELECT;

· язык управления данными (Data Control Language — DCL), представлен командами GRANT (предоставление привилегий) и REVOKE (отмена привилегий);

· язык обработки транзакций (Transaction Processing Language — TPL), включает команды BEGIN (начать транзакцию), COMMIT (завершить транзакцию), ROLLBACK (откатить транзакцию);

· язык управления курсором (Cursor Control Language — CCL) предназначен для выполнения операций с отдельными строками одной или нескольких таблиц, представлен командами DECLARE CURSOR, OPEN, CLOSE, FETCH INTO, DROP CURSOR.

К сожалению, эти подмножества не используются повсеместно во всех реализациях. Они подчеркиваются ANSI и полезны на концептуальном уровне. В частности, интерактивный Microsoft Jet SQL, реализованный в популярной СУБД MS Access, поддерживает только первые три подмножества.

Изучение SQL дает навыки, необходимые для извлечения информации из любой реляционной базы данных, а также помогает понять механизмы, лежащие в основе графического интерфейса запросов, которые можно найти во многих продуктах СУБД. Знание SQL поможет проектировать сложные запросы и обеспечит навыки работы со сложными базами данных и на разнообразных платформах. Например, если изучен SQL в Microsoft Access 2010, то легко использовать эти знания при переходе на Sybase SQL Server.

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

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

5. Кириллов В. В. Основы проектирования реляционных баз данных [Электронный ресурс]/В. В. Кириллов. СПб.: Изд-во С.-Петерб. гос. ин-та точной механики и оптики (техн. ун-т) Режим доступа: http://citf**um.ru/database/dbguide/. Загл. с экрана.

6. Кириллов В. В. Структуризированный язык запросов (SQL) [Электронный ресурс] : учеб. пособие/В. В. Кириллов, Г. Ю. Громов. СПб.: Изд-во С.-Петерб. гос. ин-та точной механики и оптики (техн. ун-т). Режим доступа: http://cit**rum.ru/database/sql_kg/index.shtml. Загл. с экрана.

7. Когаловский М. Р. Абстракции и модели в системах баз дан-ных [Электронный ресурс]/М. Р. Когаловский//СУБД. 2012. № 4,5. Режим доступа: http://www.o**p.ru/dbms/1998/04–05/13031594/. Загл. с экрана.

8. Кодд Э. Ф. Реляционная модель данных для больших совместно используемых банков данных [Электронный ресурс]/Э. Ф. Кодд; пер. с англ. М. Р. Когаловского//СУБД. 2015. № 1. Режим доступа: http://cit**rum.ru/database/classics/codd/. Загл. с экрана.

9. Коннолли Т. Базы данных: проектирование, реализация и сопровождение. Теория и практика : [пер. с англ.]/Т. Коннолли, К. Бегг, А. Страчан. 2-е изд. М.: Изд. дом «Вильямс», 2013. 1120 с.

10. Кузнецов С. Д. Введение в стандарты языка баз данных SQL [Электронный ресурс]/С. Д. Кузнецов. Б. м.: Центр информационных технологий, 2015. Режим доступа: http://cit**rum.ru/database/sqlbook/index.shtml. Загл. с экрана.

11. Пушников А. Ю. Введение в системы управления базами данных. Часть 1. Реляционная модель данных [Электронный ресурс]: учеб. пособие/А. Ю. Пушников. Уфа: изд. Башкир. ун-та, 2011. 108 с. [Электронный ресурс]. Режим доступа: http://www.cit**rum.ru/database/dblearn/index.shtml. Загл. с экрана.

12. Форта Б. SQL за 10 минут/Б. Форта. 4-е изд. М.: Изд. дом «Вильямс», 2014. 288 с.

13. MSDN-библиотека. Microsoft Access SQL Reference [Электронный ресурс]. Режим доступа: http://msdn.micr**oft.com/enus/library/bb259125 (v=office.12).aspx