Курс знакомит слушателей с основными принципами работы со структурированными данными в реляционной модели, учит проектировать данные, описывать объекты базы данных в терминах реальной СУБД, составлять запросы на языке SQL, использовать представления, процедуры, функции и триггеры, создавать индексы, управлять конкурентным доступом к данным и манипулировать механизмом транзакций. Основу курса составляют изучение и применение языка SQL для создания, модификации объектов баз данных и управления данными в произвольной реляционной базе данных. Выполнение практических задач в рамках курса предполагает использование СУБД My SQL. В курсе рассматриваются этапы проектирования реляционных баз данных, правила составления запросов, основные методы индексирования данных. В курсе будут изучены вопросы использования транзакций и прав доступа к данным. Также курс дает обзор современных тенденций в области науки о данных в связи с появлением BigData. В заключении курса будут показаны сферы применения NoSQL баз данных и указаны современные подходы к обработке big data.
Оптимизация выполнения запросов.
Loading...
Из курса от партнера Saint Petersburg State University
Базы данных (Databases)
57 оценки
Из урока
Оптимизация выполнения запросов.
В этой лекции мы поговорим о дополнительных структурах индексирования – пространственных индексах, индексах на основе хеширования и битовых шкал. Также мы обсудим, каким образом строится план выполнения запросов и как надо писать запросы, чтобы они выполнялись быстрее.
Познакомьтесь с преподавателями
Михайлова Еленадоцент, ИО заведующего кафедрой информационно-аналитических систем СПбГУ
Кафедра информационно-аналитических систем СПбГУ
[МУЗЫКА]
[МУЗЫКА] Здравствуйте!
Поговорим с вами о том, каким образом выполняются запросы.
Когда мы пишем запрос, то мы говорим что мы хотим получить, но не определяем как.
То же самое при описании таблиц.
Мы говорим, какие атрибуты и связи между данными нам понадобятся,
но мы не опускаемся на уровень физического хранения, не говорим,
как это должно храниться.
Что может влиять на время выполнения запроса?
Могут влиять, конечно, физические параметры, например,
производительность сервера, на котором установлена СУБД, конфигурация памяти,
особенности операционной системы.
Кроме того, это зависит от внутреннего оптимизатора СУБД.
Это специальный программный компонент, который определяет наиболее эффективный
способ для выполнения реляционного запроса.
Но мы должны помочь серверу и писать запросы таким образом,
чтобы он мог их выполнить наиболее оптимальным способом.
Многие команды могут быть выполнены по-разному, то есть разными способами.
Для них может быть разный план исполнения.
Мы должны иметь возможность оценить стоимость выполнения запроса с
использованием конкретного плана выполнения.
Что может быть учтено в этом?
Можно учитывать количество памяти, которая нам понадобится,
стоимость операций ввода/вывода, время процессора и оперативной памяти,
которая потребуется для выполнения данного плана.
Что требуется сделать?
Требуется по декларативной формулировке запроса построить некую программу,
то есть план выполнения запроса, которая выполнялась бы максимально эффективно.
Для этого может потребоваться построить множество планов, рассмотреть их,
оценить их стоимость и выбрать из них тот, который мы будем называть оптимальным.
Прежде всего для этого нужно обнаружить
все корректные планы выполнения запроса или,
по крайней мере, рассмотреть те, которые могут претендовать на оптимальность.
Нужно как-то сократить пространство планов,
потому что их может быть достаточно много.
И в том пространстве планов, которое мы рассматриваем,
нужно определить наиболее оптимальный и выполнить запрос согласно этого плана.
Нам потребуются какие-то формальные критерии отбора.
Давайте посмотрим пример.
Мы будем искать в таблице «Студент» тех студентов,
которые учатся в 341 группе и которые не старше 19 лет.
Пример этого запроса вы видите на экране.
Предположим, что мы позаботились об эффективности поиска и построили
индексы по полям «Номер группы» и по полю «Дата рождения».
В таком случае для выполнения этого запроса существует как минимум
три способа.
Первый способ — это последовательно проверять все записи таблицы, считывать
их и смотреть, соответствуют ли они введенным критериям или не соответствуют.
Следующая возможность — это воспользоваться индексом по полю «Номер
группы», определить всех студентов требуемой группы и проверить для
них выполнение второго условия.
И третий способ — это воспользоваться индексом по полю «Дата рождения», выбрать
студентов, которые не старше 19 лет, и проверить, в какой группе они учатся.
Разница может быть очень существенной.
Если мы предположим, что наша таблица достаточно большая, то полное сканирование
таблиц потребует считывания всех блоков нашей таблицы, которых может быть много.
Если же мы воспользуемся индексом, то нам нужно будет просто спуститься по
уровням индексного дерева для локализации нужного набора записей.
Цель любой СУБД — это выполнить запрос, введенный пользователем,
причем сделать это наиболее эффективным способом.
План выполнения запроса состоит из последовательности шагов,
каждый из которых извлекает физически данные из
памяти или делает какую-то подготовительную работу.
Построением плана выполнения запроса занимается оптимизатор — специальный
компонент, входящий в состав любой СУБД.
Какие стадии проходит оптимизация запросов?
На первой стадии делается синтаксическая и лексическая проверка,
на второй стадии привязка указанных таблиц столбцов к
физическим объектов, после этого производится логическая оптимизация.
Четвертый шаг — это построение всех возможных планов выполнения запроса.
И на последнем шаге — выполнение запроса оптимальным способом.
Разберем эти шаги чуть подробнее.
Лексический и синтаксический анализ.
На этой стадии запрос разбивается на лексические единицы,
которые называются лексемами.
Лексемы — это наименования полей, таблиц, констант,
знаки операции и команды языка, который мы использовали.
И синтаксический анализатор проверяет синтаксическую корректность запроса.
На следующем шаге оптимизатор создает внутреннее представление запроса.
Оно отражает структуру запроса и содержит информацию,
которая характеризует объекты базы данных, которые были упомянуты в запросе.
Это поля, таблицы, имена таблиц и какие-то константы или переменные,
которые мы использовали.
Информация обо всех объектах базы данных берется из словаря.
Это специальный источник информации, который хранит описание всех объектов,
которые существуют в базе данных.
Третий шаг называется логической оптимизацией.
На этой стадии оптимизатор выполняет различные преобразования,
которые могут улучшить начальные представления запроса.
Например, вы делали вычисление: к полю прибавляли единичку,
а потом вычитали двоечку.
Так вот,
эти два действия могут быть заменены на одно: вычесть из значения поля единичку.
То есть это такие эквивалентные преобразования,
которые выполняются быстрее, но дают ровно тот же самый результат,
который называется семантически эквивалентный начальному запросу.
Следующий шаг — четвертый шаг — это построение возможных планов
выполнения запросов.
Основой для этого шага является информация о существующих путях доступа к данным.
Что такое пути доступа к данным?
Мы можем выполнять полное сканирование таблицы, а можем получить доступ к данным
при помощи индексных структур, которых мы можем построить для таблицы несколько.
Мы можем построить индексы по отдельным полям, по комбинации полей.
И нужно выбрать, каким из методов доступа лучше воспользоваться,
чтобы запрос выполнялся быстрее.
Статистическая информация собирается в специальных файлах,
которые называются статистикой.
Статистика собирается в специальных таблицах базы данных.
Что в ней содержится?
Это общее количество блоков данных, которые заняты таблицей,
длина строк, длина отдельно каждого поля, количество пустых блоков,
которые есть в этой таблице, среднюю длину записи,
если запись имеет переменную длину, среднее количество записей на блок и
также наличие индексов, которые построены для данной таблицы.
Индексы также накапливают статистику.
В статистике по индексам хранится общее количество проиндексированных записей,
максимальное и минимальное проиндексированные значения,
количество различных проиндексированных значений.
Также может быть видно даже распределение значений в столбце,
по которым построен индекс.
Статистика влияет на метод выборки данных: полное сканирование таблицы,
либо поиск по индексу.
Если статистика не была построена до выполнения запроса,
то оптимизатор попробует построить эту статистику.
На последнем шаге выбранный оптимальный по стоимости план выполнения запроса
реализуется, то есть запрос выполняется по выбранному оптимальному плану.
План выполнения запроса сохраняется в разделяемой памяти и может быть
использован повторно при выполнении аналогичного запроса.
То есть перед выполнением запроса система проверяет не выполнялся ли аналогичный
запрос в предыдущее время и нет ли в системе готового плана,
по которому этот запрос может быть выполнен.
И если таковой план исполнения есть,
то система не генерирует заново план выполнения запроса,
а выполняет запрос уже по сохраненному в разделяемой памяти плану.
Таким образом, есть общая рекомендация использовать типовые запросы для того,
чтобы не генерировать план выполнения запроса каждый раз заново,
а использовать уже сгенерированный план, хранящийся в разделяемой памяти.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
