Повышение производительности запросов: Кластеры
Самой медленной операцией, выполняемой СУБД, является операция чтения данных с диска или запись данных на диск. Если существует возможность уменьшить в несколько раз число таких операций, то общая производительность базы данных может заметно увеличиться.
Следует помнить, что СУБД считывает с диска или записывает на диск за один раз одну физическую страницу данных, размер которой колеблется в зависимости от аппаратной платформы от 512 байт до 4 Кб. Таким образом, если можно физически хранить данные, к которым часто происходит совместное обращение, на одной и той же странице диска или на страницах, физически близко расположенных друг к другу, то скорость доступа к этим данным повышается.
Кластеризация (Clustering) - это способ физического размещения рядом, на одной физической странице данных, строк, доступ к которым осуществляется при помощи одинакового значения колонки (ключа) с целью увеличения производительности. Такой ключ называется кластерным ключом. Значением кластерного ключа являются значения одинаковых по смыслу колонок строк кластеризуемых таблиц. Ключ может быть либо хэш-ключом, либо индексным ключом. Если ключ является хэш-ключом, то физическое размещение определяется функцией преобразования ключа (хэширования) и мы имеем дело с уже известной нам из предыдущих разделов таблицей хэширования или хэш-кластером. Если это индексный ключ, то для идентификации страницы данных в кластере используется индекс со структурой B-Tree, в котором сроки, имеющие одинаковые значения ключа, размещаются либо в одной странице, либо в смежных стран ицах индекса. Такой кластер называется индексным кластером. Строки, которые хранятся в индексном кластере, не обязательно должны принадлежать одной таблице. Таким образом, кластеры являются одним из методов хранения таблиц данных, поддерживаемых СУБД. Кластер - это группа таблиц, которая разделяет общие физические страницы данных при совместном использовании в запросах общих колонок этих таблиц.
На практике индексный кластер создается для совместного хранения строк, связанных ограничением внешнего и первичного ключей.
Совместное хранение строк родительской и дочерней таблиц может значительно ускорить выполнение соединения этих таблиц.
Пример. Рассмотрим таблицы DEPARTAMENT и EMPLOYEE нашей учебной базы данных. Они некластеризованы и хранятся каждая на своих физических страницах. Предположим, что анализ запросов показывает, что в 80% запросов эти таблицы используются совместно, при этом соединение выполняется по колонке DEPNO. Проектировщик базы данных может решить построить кластер для этих двух таблиц. На рисунке ниже показана концептуальная сторона такого решения.
До кластеризации строки из таблиц сохраняются отдельно в своих физических областях на диске.
| DEPARTMENT | |||
| DEPNO | DNAME | LOC | … |
| 10 | Торговля | Москва | |
| 20 | Консалтинг | Черноголовка | |
| EMPLOYEE | |||
| EMPNO | ENAME | LNAME | DEPNO |
| 996 | Козырев | Сергей | 10 |
| 997 | Сапегин | Алексей | 20 |
| CLUSTER | ||||
| DEPNO | ||||
| 10 | DNAME | LOC | … | |
| Торговля | Москва | … | ||
| … | … | … | ||
| EMPNO | ENAME | LNAME | … | |
| 996 | Козырев | Сергей | … | |
| … | … | … | ||
| 20 | DNAME | LOC | … | |
| Консалтинг | Черноголовка | … | ||
| EMPNO | ENAME | LNAME | … | |
| 997 | Сапегин | Алексей | … | |
| … | … | … | … | |
Кластеризация может существенно ускорить работу с соединениями. Однако, планируя использование кластеров, проектировщик базы данных должен учитывать следующие факторы:
- кластеризация затрагивает физическое размещение данных в файлах базы данных. Поэтому рекомендуется выполнить кластеризацию таблицы только по одной колонке или комбинации колонок;
- кластеризация замедляет выполнение операций, в которых нужно просканировать всю таблицу, так как она может вызвать разброс строк одной таблицы по множеству физических страниц;
- кластеризация может замедлить ввод данных;
- кластеризация может замедлить модификацию данных в тех колонках, которые помещены в кластер.
В силу вышеперечисленных обстоятельств кластеры не рекомендуется создавать для таблиц с интенсивным обновлением данных. Для того чтобы таблица была хорошим кандидатом для ее кластеризации, должны выполняться по крайней мере следующие условия:
- Значения колонок кластерных ключей распределены равномерно и плотно, а их размер почти всегда меньше размера физической страницы (иначе будут образовываться кластерные цепочки).
- В случае индексного кластера на каждый кластерный ключ приходится больше одной выбираемой строки, а для хэш-кластера - одна строка. Альтернативное решение - индексация таблицы.
- Все данные для заданного кластерного ключа выбираются при каждом доступе по кластерному ключу. Альтернативное решение - индексация таблицы.
- Интенсивность обращений операций вставки, обновления и удаления не очень велика, иначе общая производительность базы данных может уменьшиться. Обратный отрицательный эффект.
В силу вышеперечисленных обстоятельств кластеры не рекомендуется создавать для таблиц с интенсивным обновлением данных. Для того чтобы таблица была хорошим кандидатом для ее кластеризации, должны выполняться по крайней мере следующие условия:
- Значения колонок кластерных ключей распределены равномерно и плотно, а их размер почти всегда меньше размера физической страницы (иначе будут образовываться кластерные цепочки).
- В случае индексного кластера на каждый кластерный ключ приходится больше одной выбираемой строки, а для хэш-кластера - одна строка. Альтернативное решение - индексация таблицы.
- Все данные для заданного кластерного ключа выбираются при каждом доступе по кластерному ключу. Альтернативное решение - индексация таблицы.
- Интенсивность обращений операций вставки, обновления и удаления не очень велика, иначе общая производительность базы данных может уменьшиться. Обратный отрицательный эффект.
Из этого следует, что существуют две основные причины использования кластеров: это необходимость а) обеспечить прямой доступ к строке за одну операцию чтения; и б) сократить число операций ввода/вывода при доступе к часто совместно используемым данным путем размещения их в близко расположенных физических страницах базы данных.
С физической точки зрения кластер находится отдельно от таблиц. Он создается с указанием параметров хранения, а затем в нем последовательно создаются кластеризованные таблицы. При описании кластера нужно указать колонки или колонку, для которых СУБД сформирует кластер, и таблицы, которые будут включены в его состав. При обработке данных СУБД будет размещать строки, содержащие одинаковые значения в колонках кластера, физически максимально близко. В результате строки таблицы могут быть распределены среди нескольких дисковых страниц, но первичные и внешние ключи обычно располагаются на одной странице.
Пример. Вернемся к нашей учебной базе данных и напишем фрагмент скрипта для создания кластера для таблиц DEPARTAMENT и EMPLOYEE. Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид
Хэш- кластер является альтернативной техникой создания таблиц данных по отношению к индексному кластеру или некластеризованной таблице.
Пример. Рассмотрим нашу учебную базу данных с целью создания хэш-кластера для таблицы EMPLOYEE. На рис. 11.3 ниже показано, как будет выполняться доступ к записям таблицы до и после кластеризации.
Рис. 11.3. Доступк строке таблицы EMPLOYEE через индекс по колонке EPMNO
SELECT * FROM EMPLOYEE WHERE EMPNO= 997;
До кластеризации по колонке EPMNO доступ будет выполняться через индекс, и согласно рисунку 11.3 потребуется 4 операции ввода/вывода, чтобы получить результирующую строку.
После кластеризации по колонке EPMNO строки таблицы EMPLOYEE будут сохраняться в структуре, которая условно приведена на рисунке ниже. После хэширования ключа потребуется одна операция ввода/вывода, чтобы получить результирующую строку, если нет цепочек переполнения.
| CLUSTER | ||||
| Хэш-ключ | Кластерный ключ | |||
| 110 | EMPNO | ENAME | LNAME | … |
| 996 | Козырев | Сергей | … | |
| … | … | … | ||
| 120 | EMPNO | ENAME | LNAME | … |
| 997 | Сапегин | Алексей | … | |
Пример. Создадим хэш-кластер для таблицы EMPLOYEE нашей учебной базы данных. Фрагмент скрипта приведен ниже.
CREATE CLUSTER PERSONNEL (EMPNO integer) SIZE 512 HASHKEYS 500 -- STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10) ;
Число уникальных значений хэш-ключа задается параметром HASHKEYS, после достижения этого значения в таблицы будут возникать коллизии - ситуации, когда разные хэшированные ключи должны будут размещаться в одном блоке. Это приводит к созданию при вставке строк так называемых цепочек переполнения, из-за которых увеличивается число доступов при выборке результирующей строки.
Параметр SIZE определяет максимальное число хэш-ключей, размещаемое на физической странице базы данных. Он равен оценке общего пространства в байтах, требуемого для сохранения среднего числа строк, связанного с каждым значением хэш-ключа. Если доступного пространства на странице - 1600 байт, а значение параметра - 512 байт, то три значения хэш-ключа будут распределяться на физической странице.
С помощью предложения HASH IS вы можете переопределить хэш-функцию, которую СУБД Oracle использует по умолчанию.
Пример. Если у нас есть хэш-кластер для таблицы EMPLOYEE и кластерный ключ определен как код домашнего адреса сотрудника, то вероятно, что будет случаться много коллизий в хэш-кластере, если городок, где живут сотрудники, невелик. Для того чтобы избежать такой коллизии, можно переопределить встроенную хэш-функцию Oracle в команде CREATE CLUSTER, добавив предложение HASH IS, как показано ниже.
CREATE CLUSTER personnel (home_area_code number, home_prefix number ) HASHKEYS 20 HASH IS MOD(home_area_code + home_suffix_tel, 101);
В примере добавлено некоторое число к коду домашнего адреса, чтобы изменить распределение значений хэш-ключа с целью избежать коллизий. В качестве такого числа взяты две последние цифры домашнего телефона.
В заключение отметим следующее. Несмотря на то, что СУБД Oracle, так же как и СУБД SQLBase, интенсивно использует кластеры для доступа к системным таблицам базы данных, автор настоящего курса рекомендует проектировщикам базы данных проявлять осторожность при принятии решения о кластеризации таблиц при создании новой базы данных. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Проектирование кластеров - штучная работа. Очень полезно знать статистику использования аналогичного кластера при эксплуатации аналогичной базы данных, чтобы построить высокопроизводительный кластер. Придерживайтесь следующих эмпирических правил:
- До 1000 записей СУБД не имеет больших преимуществ перед последовательным файлом.
- От 1000 до 10000 записей это преимущество незначительно.
- От 10000 до 100000 записей между настольными и промышленными СУБД не ощущается разницы в производительности.
- От 100000 до 1000000 записей промышленные СУБД обеспечивают приемлемую производительность без специальных способов ее повышения.
- От 1000000 записей надо начинать думать о повышении производительности.
Литература: [7], [14], [20], [23], [45].
