Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 2
Індекси
Індекс дозволяє значно підвищити швидкість звернення до бази за рахунок прискореної обробки команд, які містять порівняльні операції. Створення індекса забезпечує команда:
CREATE [ UNIQUE ] INDEX індекс ON таблиця [ USING тип ] ( поле [ клас ] [. ...] )
Тут:
індекс – ім’я створюваного індекса;
таблиця – ім’я таблиці, для якої будується індекс;
поле – ім’я індексованого поля.
тип – необов’язковий параметр дозволяє вибрати потрібну реалізацію індекса;
клас – описує операторний клас, який слід використати для сортування даних. У PostgreSQL операторні класи зберігаються в полі pg_opclass. Використовуйте цей параметр лише в тому випадку, якщо ви досконально знаєтеся на всіх тонкощах операторних класів.
Тип індекса задає необов’язкова секція USING. У PostgreSQL 7.1.x підтримуються такі три типи індексів:
У першому варіанті використовуються алго ритми В-дерева Лемана-Яо (Lehman-Yao). Реалізація R-дерева, заснована на квадратичному розбитті за алгоритмом Гуттмана (Guttman), застосовується головним чином при операціях з геометричними значеннями даних. Реалізація хеша заснована на алгоритмах лінійного хешування Литвина (Litwin), які традиційно використовуються для індексів з частою перевіркою рівності (мають оператор =).
Ключове слово UNIQUEу поєднанні з секцією USING може використовуватися лише для індексів, реалізованих у вигляді В-дерева.Тип індекса задається в секції USING за допомогою ключовихслів BTREE, RTREE і HASH. За замовчуванням використовується тип BTREE. Це найпоширеніший спо сіб індексації, він володіє найбільшими можливостями.
Команда може містити список з декількох індексованих полів, розділених комами; в цьому випадку індекс будується для всіх перерахованих полів. Складені індекси використовуються в PostgreSQL тільки при виконанні команд SQL, які здійснюють пошук по всіх індексованих полях з об’єднанням умов ключовим словом AND. У стандартній установці PostgreSQL складений індекс містить не більше 16 полів і реалізується тільки у вигляді В-дерева.
Інформація, що зберігається в індексах про розміщення даних в одному або декількох полях таблиці підвищує ефективність пошуку записів при умовній вибірці (наприклад, з використанням секції WHERE).
Однак, при зміні даних у індексному полі індекс доводиться оновлювати, тому знижується швидкодія системи. Може статися так, що затрати часу на підтримку індексів перевищують економію від їх використання, якщо вони рідко використовується. Як правило, індекси визначаються тільки для полів, які часто вказуються в умовах пошуку.
Перед побудовою індекса слід вирішити які поля найчастіше потрібні при пошуку. Нехай, наприклад, таблиця books проіндексована по полю id (первинний ключ), а поле title також часто перевіряється в умовах WHERE. Включення вторинного індекса за полем title помітно прискорить роботу команд SQL, в яких значення цього поля порівнюється з деякою величиною.
Побудова індекса показана в прикладі 1, де також переглядається структура таблиці books за допомогою команди \d psql. Окрім типів полів ця команда також виводить імена індексів таблиці.
Приклад 1 – Створення індекса
CREATE INDEX books_title_idx ON books (title);
\d books
Table "books"
Attribute | Type -- | Modifier
---------------+------------+----------
id | integer | not null
title | text | not null
author_id | integer |
subject_id | integer |
Indices: books_id_pkey, books_title_idx
Встановлення деяких обмежень (перш за все, обмеження PRIMARY KEY і UNIQUE) також спричиняє автоматичну побудову індекса. У прикладі 2 при створенні таблиці authors встановлюється обмеження первинного ключа (PRIMARY KEY) для поля id.
Приклад 2 – Автоматична побудова індекса
CREATE TABLE authors (id integer PRIMARY KEY, last_name text, first_name text);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'authors_id _pkey' for
table 'authors'
Після виконання команди CREATE виводиться повідомлення з інформацією про успішне створення таблиці. Крім того, в повідомленні NOTICE йдеться про те, що при створенні таблиці був побудований прихований індекс authors_id _pkey.
Індекс, побудований у прикладі 2, дозволяє швидко перевіряти унікальність первинного ключа для всіх нових записів, які заносяться в таблицю. Крім того, індекс підвищує швидкість виконання запитів, у яких поле id вказане в умові пошуку.
Унікальні індекси створюють за допомогою ключового слова UNIQUE. Такий індекс є унікальним, тобто індексоване поле (або поля) не може містити значень, які повторюються. Фактично створення унікального індекса еквівалентне створенню таблиці з обмеженням унікальності.
У прикладі 3 для поля name таблиці publishers створюється унікальний індекс unique_pubisher_idx. Це означає, що в таблиці не можуть бути присутніми два видавництва з однаковими назвами.
Приклад 3 – Створення унікального індекса
CREATE UNIQUE INDEX unique_publisher_idx ON publishers (name);
\d publishers
Table "publishers"
Attribute | Type | Modifier
-------------+----------+----------
id | integer | not null
name | text |
address | text |
Indices: publishers_pkey, unique_publisher_idx
Оскільки псевдозначення NULL формально не збігається ні з одним реальним значенням, у полі з унікальним індексом допускаються багатократні входження NULL. У цьому й полягає головна практична відмінність між унікальним індексом і індексом, що автоматично створюється при установці обмеження PRIMARY KEY, яке взагалі забороняє присутність значень NULL.
Функціональні індекси створюються тією ж командою CREATE INDEX. Функціональний індекс дозволяє індексувати дані не за значеннями поля, а за деякою функцією цих значень. Єдина відмінність цієї команди від описаної вище полягає в тому, що індекс будується за результатами застосування функції.
Команда створення функціонального індекса має такий вигляд:
CREATE [UNIQUE] INDEX індекс ON табпиця
[USING тип] (функція(поле [, ...] )[клас])
Функціональні індекси часто будуються для полів, значення яких прохо дять попередню обробку перед порівнянням у команді SQL. Наприклад, при порівнянні літерних даних без урахування регістра символів часто використовується функція upper(). Створенняфункціонального індекса з функцією upper() покращує ефективність таких порівнянь. У прикладі 4 показано побудову індекса upper_tit1e для таблиці books. Дані індексуються за результатам застосування функції upper() до поля titlе. Потім виконується запит SQL, який завдяки наявності функціональ ного індекса виконується ефективніше.
Приклад 4 – Побудова функціонального індекса
CREATE INDEX upper_title_idx ON books (upper(title));
SELECT title FROM books WHERE upper(title) = 'DUNE';
title
-------
Dune
(1 row)
Відновлення індексів у таблицях забезпечує команда:
REINDEX { TABLE | DATABASE | INDEX } об’єкт [ FORCE ]
Вона має такі параметри:
Команда REINDEX відновлює (будує заново) пошкоджені індекси. Можливість відновлення особливо важлива при пошкодженні системних індексів, тоді вживається параметр DATABASE. У прикладі 5 відновлюються всі індекси для таблиці book.
Приклад 5 – Відновлення індексів таблиці
REINDEX TABLE book;
Видалення індексів з таблиці виконується командою:
DROP INDEX індекс [, ...]
Допускаєт ься одночасне видалення декількох індексів, перерахованих через кому. У прикладі 6 видаляється індекс upper_tit1e_idx, створений у прикладі 5.
Приклад 6 – Видалення індекса
DROP INDEX upper_title_idx;
Видалення обмежень. У розділі про обмеження вже було сказано про те, що PostgreSQL 7.1.x не підтримує їх прямого видалення. Для цього треба створити нову копію таб лиці, скопіювати в неї дані, після чого таблицю перейменувати командою ALTER TABLE. Якщо така таблиця має індекси (в т.ч. автоматично створені), то спочатку доцільно їх видалити, що дозволяє зберегти їхні первинні імена.
Кластеризація таблиці пo заданому індексу виконується командою:
CLUSTER індекс ON таблиця
В процесі кластеризації вміст таблиці упорядковується так, як того вимагає індекс. Під час кластеризації записи таблиці копіюються в тимчасову таблицю в порядку, заданому індексом, після чого тимчасова таблиця перейменовується в початкову. Таким чином, під час кластеризації знищується решта індексів. Кластеризація є разовою операцією, для повторної кластеризації команду CLUSTER необхідно виконати заново. У прикладі 7 показаний вплив кластеризації на таблицю gazpr .
Приклад 7 – Кластеризація таблиці gazpr за індексом gazpr_idx
CREATE INDEX gazpr_idx ON gazpr (kilnyt);
select * from gazpr;
CLUSTER gazpr_idx ON gazpr;
select * from gazpr;
1 "Україна " 1 1400
2 "Ямпіль-Ужгород-держкордон " 2 1400
3 "Острогожськ-Гадяч " 2 1000
5 "Єлецьк-Десна-Кривий Ріг " 5 1600
4 "Єлецьк-Орел " 7 1200
У прикладі 7 таблиця була проіндексована за полем kilnyt. Її кластеризація змінила порядок записів відповідно до умов індекса gazpr_idx (сортування за зростанням).
Для обслуговування індексів та інших об’єктів бази даних PostgreSQL має нестандартну команду SQL COMMENT, за допомогою якої можна їх документувати.
Використовуючи цю команду з таблицею, індексом, функцією, оператором та ін., можна ввести коментарій, який зберігається в системній таблиці pg_description. Всі коментарі зв’язуються як з базою даних, так і з конкретним користувачем. Кожен користувач може читати тільки свої коментарі.
Створення та знищення коментара для індекса nafta_idx демонструє приклад 8. Тут текст коментара містить рядок, узятий в одинарні лапки.
Приклад 8 – Створення та знищення коментара
COMMENT ON INDEX nafta_idx IS 'Унікальний індекс';
COMMENT ON INDEX nafta_idx IS NULL;
Оцінка доцільності індексування таблиці. Індекси застосовуються для прискорення пошукових операцій під час виготовлення вибірки та підвищення ефективності функціонування бази даних [1]. Він являє собою набір певним чином упорядкованих, найчастіше посортованих у порядку зростання, даних таблиці. Його можна представити у вигляді додаткової, супутної таблиці, яка має два поля, в першому з яких міститься упорядкована копія даних заданого поля основної таблиці, а в другому – порядковий номер (адреса) запису основної таблиці, де знаходяться ці та решта його даних. Тоді під час виконання пошукових операцій, наприклад, при виготовленні вибірки за допомогою команди SELECT з секцією WHERE, пошук потрібного значення ведеться в індексі, а дані решти полів беруться з основної таблиці за номером запису, вказаним у індексі. Одна таблиця може мати декілька індексів – за кількістю полів.
Вигода від застосування індексів очевидна. Нехай, наприклад, маємо таблицю, індексне поле якого містить набір n випадкових чисел з рівномірним законом розподілу. Тоді в найневигіднішому випадку (перегляд усіх записів) пошуку числа, яке задовільняє певну задану умову, максимально можлива кількість операцій порівняння k дорівнюватиме n, тобто
k=n. (1)
Прискорення пошуку даних в індексі відбувається за рахунок їх упорядкованості. Якщо вони посортовані за зростанням, то доцільно застосувати метод дихотомії. Його суть полягає в тому, що в циклі область пошуку ділиться на дві частини, з яких для подальшого ділення вибирається та, в якій знаходиться потрібний елемент. Таким чином, кожного разу область пошуку скорочується вдвоє. Процес продовжується доти, поки не буде знайдено пошукуване або найближче до нього значення. При використанні цього методу
2k=n. (2)
Прологарифмувавши вираз (2), одержимо
. (3)
Порівнявши значення k, одержане за різними формулами (1) і (3), бачимо, що в останньому випадку воно набагато менше, причому різниця між ними зростає зі збільшенням числа n. Ця різниця й становитиме економію машинного часу. Кількість записів таблиць у базах даних газонафтопромислового комплексу часто сягає тисяч та більше, що говорить про перевагу використання індексів.
Однак, застосування індексів вимагає додаткових затрат машинного часу [1], причому в окремих випадках вони можуть переважати ті, які тратяться на пошукові операції без них. Питання про наявність індексів у кожному конкретному випадку вирішується переважно емпіричним шляхом і часто залежить від кваліфікації обслуговуючого персоналу. Спробуємо кількісно оцінити ту межу, за якою індексування таблиць стає невигідним.
Оскільки обслуговування індекса пов’язане з перестановкою даних під час їх сортування, будемо вважати, що одна перестановка при сортуванні вимагає однієї операції пошуку плюс три операції присвоєння. Тоді за затратами часу одна операція перестановки буде приблизно рівноцінною 4-м операціям порівняння.
На сьогодні відомо більше десятка методів сортування [2], їх ефективність багато в чому залежить від вигляду даних, тому неможливо надати якомусь із них перевагу. Виберемо метод вставок, він порівняно простий і може виявитися кращим у випадку рівномірномірного закону розподілу даних. Відповідно до цього методу максимальне число перестановок дорівнює n2/2, а із врахуванням висловлених вище зауважень відповідна їм кількість порівнянь дорівнюватиме 2n2.
Для того, щоб компенсувати затрати на одне сортування необхідно використати індекс, як мінімум m разів, нехай цей показник і буде обмежувати його застосування. Отже,
. (4)
Звідси
. (5)
Рисунок 1 – Результати обчислень числа m
# include<stdio.h> /* Програма для обчислення числа m за формулою (5) */
# include<math.h>
main()
{float n, m;
int k;
clrscr(); puts("");
for(k=1; k<10000; k+=500)
{
n=k;
m=2*n*n/(n-log(n)/log(2));
printf("n=%4d m=%7.2f\n", k, m);
}
getch();
}
Результати обчислень за формулою (5), виконані на ЕОМ, подані на рисунку 1. Аналізуючи їх, знаходимо, що мінімальна кількість використань індекса після кожного його пересортування повинна не менше, ніж приблизно в 2 рази, перевищувати число даних таблиці, причому ця закономірність практично не залежить від кількості даних. Інакше індексування таблиці буде збитковим.
Література
LISTEN publisher_deletion;
UNLISTEN publisher_update;
NOTIFY