Распределенные базы данных, высокопроизводительные вычисления, грид и облачные технологии СУБД: лекция 6 Поиск данных и оптимизация запросов Желенкова О. П.
Поиск данных
Манипуляции с данными в среде хранения СУБД При добавлении записи: v поиск свободного места для размещения записи в пространстве памяти; v выделение необходимого объема памяти под запись; v размещение записи в отведенном месте памяти; v формирование связей с другими записями (зависит от модели данных). При поиске записи: v поиск места размещения записи в пространстве памяти по заданным значениям атрибутов; v выборка записи для обработки в оперативную память (в буфер данных). При изменении атрибутов записи: v поиск записи и считывание её в оперативную память ; v изменение значений атрибута (атрибутов) записи; v сохранение записи на диск. При удалении записи: v удаление записи с освобождением памяти (физическое удаление) или без освобождения (логическое удаление); v разрушение связей с другими записями (механизм зависит от модели данных).
Файловые системы Каждая файловая система поддерживает некоторую иерархическую структуру, включающую чаще всего неограниченное количество уровней в представлении во внешней памяти. Для каждого файла хранится следующая информация: • имя файла; • тип файла; • размер записи; • количество занятых физических блоков; • базовый начальный адрес; • ссылка на сегмент расширения; • способ доступа (код защиты)
Классификация файлов, используемых СУБД v Механизмы буферизации и управления данными в системах управления файлами ОС не приспособлены для решения задач собственно СУБД. v Произошел переход от базовых файловых структур к непосредственному управлению самой СУБД размещением и управлением данных на внешних носителях. v Механизмы, применяемые в файловых системах, перешли во многом и в системы организации данных во внешней памяти, называемые чаще страничными системами хранения информации
Способы доступа к данным в БД Основные способы доступа к данным: v Последовательная обработка области БД. Областью БД может быть файл или другое множество страниц (блоков) памяти. Последовательная обработка предполагает, что система последовательно просматривает страницы, пропускает пустые участки и выдаёт записи в физической последовательности их хранения. v Доступ по ключу базы данных (КБД). КБД определяет местоположение записи в памяти ЭВМ. Зная его, система может извлечь нужную запись за одно обращение к памяти. v Доступ по ключу (в частности, первичному). Если система обеспечивает доступ по ключу, то этот ключ также может использоваться при запоминании записи (для определения места размещения записи в памяти). В базах данных применяются такие способы доступа по ключу, как индексирование, хеширование и кластеризация. По другим ключам поиска используются инвертируемые списки. v Доступ по структуре: используется в иерархических, сетевых и объектно-реляционных БД.
Метод доступа - хэширование Прямой доступ: (+) Файлы с прямым доступом обеспечивают наиболее быстрый способ доступа. (-) Не всегда можно хранить информацию в виде файлов прямого доступа. (-) Доступ по номеру записи в базах данных неэффективен. В базах данных необходим поиск по ключу (первичному, возможному или внешним ключам). Во всех случаях мы знаем значение ключа, но не знаем номера записи. (+)В некоторых очень редких случаях возможно построение функции, которая по значению ключа однозначно вычисляет адрес. (-) Часто бывает, что значения ключей разбросаны по нескольким диапазонам. Не всегда удается построить взаимно-однозначное соответствие между значениями ключа и номерами записей.
Метод доступа - хэширование Суть методов хэширования состоит в том, что берется значения ключа и вычисляется некоторая хэш-функция h(k). Ее полученное значение берем в качестве адреса. В этом случае не требуется полное взаимно-однозначное соответствие, уменьшается количество операций. Мы допускаем, что нескольким разным ключам может соответствовать одно значение хэш-функции (то есть один адрес). Подобные ситуации называются коллизиями. Значения ключей, которые имеют одно и то же значение хэш-функция, называются синонимами. Поэтому при использовании хэширования как метода доступа необходимо принять два независимых решения: v выбрать хэщ-функцию; v выбрать метод разрешения коллизий.
Метод доступа - хэширование Существует множество различных стратегий разрешения коллизий. Рассмотрим две достаточно распространенные: v стратегия с областью переполнения (I). область хранения разбивается на 2 части: основную область и область переполнения v свободное размещение (II). (I) запись заносится в основную область в соответствии с хэш-значением. Если новая запись имеет это же значение, она заносится в область переполнения на первое свободное место. В записи-синониме делается ссылка на адрес вновь размещенной записи. Цепочка синонимов не разрывается, но новую запись располагаем на второе место в цепочке синонимов. При таком алгоритме время размещения новой записи составляет не более двух обращений к диску, с учетом того, что номер первой свободной записи в области переполнения хранится в виде системной переменной.
Метод доступа - хэширование Поиск: вычисляется значение хэш-функции, считывается первая запись в цепочке синонимов, которая расположена в основной области, если она не соответствует первой в цепочке синонимов, то поиск происходит перемещением по цепочке синонимов, пока не будет обнаружена требуемая запись. Скорость поиска зависит от длины цепочки синонимов. Хорошим результатом считается <10 синонимов в цепочке. Удаление: определяется место расположения записи. если удаляемой является 1 -ая запись в цепочке синонимов, то на ее место в основной области заносится следующая запись в цепочке синонимов, при этом все ссылки на синонимы сохраняются, если удаляемая запись находится в середине цепочки синонимов, то необходимо провести корректировку указателей. В записи, предшествующей удаляемой в цепочке, ставится указатель из удаляемой записи. Если это последняя запись в цепочке, то в предшествующую запись заносится признак отсутствия следующей записи в цепочке, который ранее хранился в последней записи.
Метод доступа - хэширование При стратегии свободного размещения (II) файловое пространство не разделяется на области, но для каждой записи добавляется 2 указателя: указатель на предыдущую запись в цепочке синонимов и указатель на следующую запись в цепочке синонимов. Отсутствие соответствующей ссылки обозначается специальным символом, например нулем. Добавление: для каждой новой записи вычисляется значение хэш-функции, и если данный адрес свободен, то она попадает туда и становится первой в цепочке синонимов, если адрес, соответствующий полученному значению хэш-функции, занят, то по наличию ссылок определяется, является ли запись, расположенная по указанному адресу, первой в цепочке синонимов. если да, то новая запись располагается на первом свободном месте и для нее устанавливаются соответствующие ссылки: она становится второй в цепочке синонимов, на нее ссылается первая запись, а она ссылается на следующую, если таковая есть.
Метод доступа - хэширование Если запись, которая занимает требуемое место, не является первой записью в цепочке синонимов, значит, она занимает данное место «незаконно» и при появлении «законного владельца» должна быть перемещена на новое место. Механизм перемещения аналогичен занесению новой записи, которая уже имеет синоним: ищется первое свободное место, корректируются соответствующие ссылки - в записи, которая является предыдущей в цепочке синонимов для перемещаемой записи, заносится указатель на новое место перемещаемой записи, указатели же в самой перемещаемой записи остаются прежние. После перемещения «незаконной» записи вновь вносимая запись занимает свое законное место и становится первой записью в новой цепочке синонимов. Механизмы удаления записей во многом аналогичны механизмам удаления в стратегии с областью переполнения.
Хеширование: достоинства и недостатки Достоинства: Ø обращение к данным по значению ключа происходит за одну операцию ввода/вывода; Ø не нужно создавать никаких дополнительных структур (типа индекса) и тратить память на их хранение. Недостатки: Ø количество данных и распределение значений ключа должны быть известны заранее; Ø записи обычно неупорядочены по значению ключа, что приводит к дополнительным затратам, например, при выполнении сортировки; Ø сложности подбора хеш-функции.
Метод доступа - индексирование Не всегда удается найти соответствующую хэш-функцию, поэтому при доступе по первичному ключу широко используются индексные файлы. Они состоят из двух частей (не обязательно в одном файле). В большинстве случаев индексная область образует отдельный индексный файл, а основная область образует файл, для которого создается индекс. Индекс – это структура, которая определяет соответствие значения ключа записи (атрибута или группы атрибутов) и местоположения этой записи – КБД (ключ базы данных). Каждый индекс связан с определённой таблицей, но обычно хранится отдельно от неё.
Индексирование данных Индекс обычно хранится в отдельном файле или отдельной области памяти. 1. Пустые значения атрибутов (NULL) не индексируются. 2. Индексирование используется для ускорения доступа к записям по значению ключа и не влияет на размещение данных этой таблицы. Ускорение поиска данных через индекс обеспечивается за счёт: • упорядочивания значений индексируемого атрибута. Это позволяет просматривать в среднем половину индекса при линейном поиске; • индекс занимает меньше страниц памяти, чем сама таблица, поэтому система тратит меньше времени на чтение индекса, чем на чтение таблицы. 3. Индексы поддерживаются динамически. 4. Каждый индекс относится к одной таблице, на одну таблицу можно создать несколько индексов.
Индексирование данных Индексы бывают: ü Первичные (уникальные) и вторичные (неуникальные). Большинство СУБД автоматически строят индекс по первичному ключу и по уникальным столбцам. ü Плотные и неплотные. В плотных для каждого значения ключа имеется отдельная запись индекса, указывающая место размещения конкретной записи. Неплотные (разреженные) индексы строятся в предположении, что на каждой странице памяти хранятся записи, отсортированные по значениям индексируемого атрибута. Тогда для каждой страницы в индексе задаётся диапазон значений ключей хранимых в ней записей, и поиск записи осуществляется среди записей на указанной странице. ü Сжатые и несжатые. ü Одиночные и составные. ü Линейные и многоуровневые.
Индексирование данных Так как индексные файлы строятся для первичных ключей, однозначно определяющих запись, то в них не может быть двух записей, имеющих одинаковые значения первичного ключа. В индексных файлах с плотным индексом для каждой записи в основной области существует одна запись из индексной области. Длина доступа к произвольной записи оценивается в количестве обращений к устройству внешней памяти, которым обычно является диск. Именно обращение к диску является наиболее длительной операцией по сравнению со всеми обработками в оперативной памяти. Наиболее эффективным алгоритмом поиска на упорядоченном массиве является логарифмический, или бинарный, поиск. Максимальное количество шагов поиска определяется двоичным логарифмом от общего числа элементов в искомом пространстве поиска: Tn=log 2 N, N – число элементов.
Индексирование данных Добавление: запись в конец основной области, в индексной области занесение информации производится в конкретное место, чтобы не нарушать упорядоченности. При начальном заполнении индексной области в каждом блоке остается свободная область (процент расширения). При добавлении новых записей процент расширения постоянно уменьшается. Когда исчезает свободная область, возникает переполнение индексной области: перестроить заново индексную область, организовать область переполнения для индексной области, в которой будут храниться не поместившиеся в основную область записи. При проектировании физической базы данных так важно заранее как можно точнее определить объемы хранимой информации, спрогнозировать ее рост и предусмотреть соответствующее расширение области хранения.
Индексирование данных Неплотный индекс: Значение ключа первой записи блока : : номер блока с этой записью
Многоуровневые индексы Построение B-деревьев связано с простой идеей построения индекса над уже построенным индексом. Сама индексная область может быть рассмотрена как основной файл, над которым надо снова построить неплотный индекс, а потом снова над новым индексом строим следующий и так до того момента, пока не останется всего один индексный блок.
Многоуровневые индексы Структура B-дерева имеет следующие преимущества: v B-дерево автоматически поддерживается в сбалансированном виде. v Все блоки-листья в дереве расположены на одном уровне, следовательно, поиск любой записи в индексе занимает примерно одно и то же время. v B-деревья обеспечивают хорошую производительность для широкого спектра запросов, включая поиск по конкретному значению и поиск в открытом и закрытом интервалах (благодаря ссылкам между блоками -листьями). v Модификация данных таблицы выполняется достаточно эффективно, т. к. в блоках индекса обычно есть свободное место для размещения новых значений, а полная перестройка дерева выполняется достаточно редко. v Производительность B-дерева одинаково хороша для маленьких и больших таблиц, и не меняется существенно при росте таблицы.
Оптимизация запросов Запрос - это языковое выражение, которое описывает данные, подлежащие выборке из базы данных. Оптимизация запросов направлена на минимизацию времени отклика системы. Эта общая цель допускает ряд различных операционных целевых функций. Время отклика является разумной целью только при предположении, что время пользователя является наиболее важным критическим ресурсом. В противном случае можно стремиться к непосредственной минимизации стоимости потребления технических ресурсов. К счастью, обе цели являются в большой степени взаимно дополнительными; возникающие конфликты целей обычно разрешаются путем назначения ограничений на доступные технические ресурсы
Оптимизация запросов Общая стоимость, подлежащая минимизации, складывается из следующих компонентов: Стоимость коммуникаций Стоимость доступа к вторичной памяти Стоимость хранения Стоимость вычислений
Оптимизация запросов Каждая команда языка манипулирования данными может быть выполнена разными способами. Определение наиболее оптимального плана выполнения запроса называется оптимизацией. Выбором этого плана занимается оптимизатор, являющийся частью СУБД.
Работа оптимизатора состоит из следующих 5 стадий. I. II. Лексический и синтаксический анализ. Запрос преобразуется во внутреннее представление, которое содержит информацию, характеризующую объекты базы данных из запроса (отношения, поля и константы). Информация о хранимых в базе данных объектах выбирается из каталогов базы данных (словаря-справочника данных). Логическая оптимизация. Применяются различные преобразования запроса. Среди них могут быть эквивалентные преобразования, после проведения которых получается внутреннее представление, семантически эквивалентное начальному (например, приведение запроса к некоторой канонической форме). Преобразования могут быть и семантическими, когда получаемое представление не является семантически эквивалентным начальному, но гарантируется, что результат выполнения преобразованного запроса совпадает с результатом запроса в начальной форме.
Работа оптимизатора состоит из следующих 5 стадий. III. Третий этап обработки запроса состоит в выборе на основе информации, которой располагает оптимизатор, набора альтернативных процедурных планов выполнения данного запроса IV. Далее по внутреннему представлению наиболее оптимального плана выполнения запроса формируется процедурное представление плана. V. Наконец, на последнем, пятом этапе обработки запроса происходит его реальное выполнение в соответствии с выполняемым планом запроса.
процедурные планы выполнения запроса Основой является информация о существующих путях доступа к данным. Единственный путь доступа, который возможен в любом случае, – это последовательное чтение. Возможность использования других путей доступа зависит от способов размещения данных в памяти (например, кластеризация данных), наличия индексов и формулировки самого запроса. На этом же этапе для каждого плана оценивается предполагаемая стоимость выполнения запроса по этому плану. При оценках используется либо доступная оптимизатору статистическая информация о состоянии базы данных, либо информация о механизмах реализации различных путей доступа. Из полученных альтернативных планов выбирается наиболее оптимальный с точки зрения некоторого (заранее выбранного или заданного) критерия.
Два основных вида оптимизаторов Оптимизатор, основанный на анализе заданных правил (rule-based optimizer). Оптимизатор, основанный на анализе затрат (cost-based optimizer).
rule-based optimizer. v Этот оптимизатор выбирает методы доступа на основе предположения о статичности СУБД v Такой оптимизатор учитывает иерархическое старшинство операций. v Если для какой-либо операции существует более одного пути ее выполнения, то выбирается тот путь, чей ранг выше, т. к. в большинстве случаев он выполняется быстрее, чем путь с более низким рангом. v План выполнения запроса формируется из выбранных путей доступа с максимальными рангами.
Ранжирование методов доступа в Oracle Ранг Пути доступа 1 Одна строка по ROWID* 2 Одна строка по кластерному соединению 3 Одна строка по хеш-кластеру с уникальным или первичным ключом 4 Одна строка по уникальному или первичному ключу 5 Кластерное соединение 6 Ключ хеш-кластера 7 Ключ индексного кластера 8 Составной индекс 9 Индекс по одиночному столбцу (по условию равенства) 10 Индексный поиск по закрытому интервалу 11 Индексный поиск по открытому интервалу 12 Сортировка-объединение 13 MAX и MIN по индексированному столбцу 14 ORDER BY по индексированному столбцу 15 Полный просмотр таблицы
cost-based optimizer v v При использовании этого метода оптимизатор сначала строит несколько возможных планов выполнения запроса. При этом он применяет некоторые эвристики, т. е. правила, полученные опытным путем. Эти правила позволяют сузить пространство поиска оптимального плана благодаря тому, что неэффективные планы отбрасываются в самом начале и не рассматриваются. Для каждого из построенных планов рассчитывается его стоимость.
Стоимость выполнения Стоимость (затраты)– это оценка ожидаемого времени выполнения запроса с использованием конкретного плана выполнения. Оптимизатор может учитывать количество необходимых ресурсов памяти, стоимость операций ввода-вывода, времени процессора и оперативной памяти, необходимой для выполнения плана.
Порядок оптимизации выполнения запроса Вычисление выражений и условий, содержащих константы. Преобразование сложной команды в эквивалентную ей с использованием соединения (проводится не всегда). Если команда выполняется над представлением, то оптимизатор обычно объединяет запрос на создание представления и запрос к этому представлению в одну команду. Выбор метода оптимизации. Выбор путей доступа к таблицам, к которым обращается запрос. Выбор порядка соединения (если в запросе соединяются несколько таблиц, то оптимизатор определяет, какие две таблицы будут соединяться первыми, какая таблица следующей будет подключаться в результату и т. д. ). Выбор операции соединения для каждой команды соединения.
Оптимизация приложений В ОП хранятся все результаты ранее выполненных запросов до тех пор, пока эта память не потребуется для записи результатов последующих запросов. Подготовленные к исполнению SQL-операторы обычно помещаются в разделяемую SQL-область. Перед началом выполнения запроса система проверяет, есть ли в этой области аналогичный запрос: если есть, то он отправляется на выполнение минуя стадию предварительной обработки (компиляции). Составляя запросы таким образом, чтобы они совпадали в уже имеющимися в SQL-области, можно исключить предобработку запроса, что является важным моментом оптимизации приложений.
Скачать презентацию Распределенные базы данных высокопроизводительные вычисления грид и облачные
62b0c73a0c7bb0a233bf4489593cbed1.ppt