- •4.5. Упражнения 67
- •Глава 6. Устройство Informix Dynamic Server 165
- •Глава 7. Эксплуатация информационных систем 177
- •Глава 1 Обзор основных архитектур баз данных
- •1.1. Архитектура на основе разделяемых файлов
- •1.2. Архитектура “Хост-терминал”
- •1.3. Архитектура “Клиент-Сервер”
- •1.4. Архитектура с использованием сервера приложений (трехзвенная архитектура)
- •1.5. Упражнения
- •Глава 2 Модели данных
- •2.1. Уровни восприятия данных
- •2.2. Иерархическая модель данных
- •2.3. Сетевая модель данных
- •2.4. Реляционная модель данных
- •2.5. Объектно-реляционная модель данных
- •Глава 3 Реализация информационных систем на основе продуктов Informix Software
- •3.1. Обзор продуктов Informix
- •3.2. Варианты построения систем
- •Internet/Intranet-конфигурация
- •3.3. Выбор оптимальной конфигурации
- •Глава 4 Математические основы реляционных субд
- •4.1. Основные понятия
- •4.2. Ключи
- •4.3. Основные операции над таблицами и их интерпретация
- •4.4. Нормализация
- •4.5. Упражнения
- •Глава 5 Язык sql
- •5.1. Типы данных, доступные в sql
- •5.3. Основные sql-операторы для доступа и модификации данных
- •5.4. Управление транзакциями
- •5.5. Продвинутые варианты оператора поиска
- •5.5.1. Поиск по нескольким таблицам
- •5.5.2. Устранение повторения данных в операторе select
- •5.5.3. Вычисления внутри оператора select
- •5.5.4. Логические выражения в условии sql-операторов
- •5.5.5. Слияние двух выборок
- •5.5.6. Сортировка выборки
- •5.5.7. Вставка в таблицу нескольких строк одновременно
- •5.6. Использование sql в языках программирования
- •5.7. Программирование сервера базы данных
- •5.7.1. Динамический sql
- •5.7.3. Хранимые процедуры
- •5.7.4. Триггеры
- •5.8. Ограничители (задание целостности на уровне схемы)
- •5.9. Разграничение в sql прав пользователей
- •5.9.1. Права доступа
- •5.9.2. Права на уровне базы данных
- •5.9.3. Права на таблицы
- •5.9.4. Права на хранимые процедуры
- •5.9.5. Кто и как следит за соблюдением прав
- •5.9.6. Механизм ролей
- •5.9.7. Псевдотаблицы (view)
- •5.9.7. Синонимы
- •5.10. Управление одновременным доступом к данным
- •5.10.1. Что бывает, когда несколько человек одновременно пытаются обновить одни и теже данные
- •5.10.2. Открытие базы данных только для себя
- •5.10.3. Блокирование таблицы
- •5.10.4. Механизм блокирования записей и уровни изоляции
- •5.10.5. Управление ожиданием снятия блокировок
- •5.10.6. Тупиковые ситуации
- •5.11. Повышение скорости обработки запросов.
- •5.11.1. Индексы
- •5.11.2. Буферизация журнала транзакций
- •5.11.3. Блокировка на уровне записей и страниц
- •5.11.4. Эффективное построение запросов
- •5.11.5. Сортировка и поиск по коротким полям. Классификаторы
- •5.12. Объектное расширение sql в Informix ds/Universal Data Option
- •5.12.1. Зачем нужна поддержка объектов в серверах бд?
- •5.12.3. Внедрение объектно-ориентированной технологии
- •5.12.4. Реализация объектного подхода в Informix
- •Informix ds/Universal Data Option - объектно-реляционная субд
- •5.12.5. Итак…
- •Глава 6. Устройство Informix Dynamic Server
- •6.1. Внутренняя архитектура dsa
- •6.2. Механизм хранения данных
- •6.3. Инсталляция продукта
- •6.4. Запуск и останов сервера
- •6.5. Работа с русским языком
- •Глава 7. Эксплуатация информационных систем
- •Администрирование серверов баз данных
- •7.2. Обеспечение сохранности данных.
- •7.2.1. Технологии постоянного дублирования
- •7.2.2. Архивация
- •7.2.3. Так как же обеспечить сохранность данных?
- •7.3. Архивирование и восстановление данных
- •7.3.1. Что нужно архивировать
- •7.3.2. Утилиты архивации и восстановления
- •7.3.3. Создание архивов утилитой ontape
- •7.3.4. Восстановление из архивов утилитой ontape
- •7.3.5. Как узнать “когда”?
- •7.3.6. Практические советы
- •7.4. Средства контроля за доступом
- •7.4.1 Как работает аудитинг?
- •7.4.2. Конфигурирование списков протоколируемых событий
- •7.4.3. Задание файлов, запуск и остановка механизма аудитинга
- •Анализ протокола
- •7.4.5. Практические советы или Что делать, если вы хотите…
- •7.5. Реагирование на чрезвычайные ситуации
- •7.6. Мониторинг текущего состояния сервера базы данных
- •7.6.1. Кто работает с сервером базы данных
- •7.6.2. Сколько памяти использует сервер бд
- •7.6.3. Сколько свободного места имеется у сервера бд
- •7.7. Достижение требуемой производительности
- •7.7.1. Как узнать, что ждет некоторый запрос
- •7.7.2. Как выяснять причины падения производительности
- •2. Общие принципы предлагаемой технологии
- •3. Как портировать приложение
5.11.2. Буферизация журнала транзакций
Обычно, при завершении транзакции (оператор COMMIT WORK) происходит принудительный сброс обновленной базы данных на внешний носитель (жесткий диск). Причем информация о проведенной транзакции дополнительно записывается в специальный файл - журнал транзакций. Если этот журнал и обновленное содержимое базы данных не сбрасывать на диск по окончании транзакции, а буферизировать в памяти, то в случае падения напряжения питания или сбоя аппаратуры информация о нескольких последних завершенных транзакциях может быть потеряна. Целостность базы данных при этом гарантированно сохранится, однако ее содержимое будет соответсвовать или последней завершенной транзакции, или предпоследней, или пред…предпоследней. Для банковских задач буферизация журнала транзакций, наверное, неприемлима, но для целого ряда других задакч, например регистрации каких-либо параметров, подлежащих статистической обработке. - вполне допустима.
Операции обмена с жестким диском - одно из самых узких мест в современных вычислительных системах (с точки зрения производительности). Поэтому буферизация журнала транзакций может заметно повысить общую производительность системы. Естественно, повышать производительность в ущерб надежности мы не рекомендуем, но если задача допускает буферизацию журнала транзакций, то при создании базы данных это можно указать с помощью оператора CREATE DATABASE:
CREATE DATABASE <имя базы данных> WITH BUFFERED LOG
Буферизация журнала транзакций позволяет в несколько раз повысить скорость исполнения операторов INSERT, UPDATE и DELETE, но при этом практически не влияет на скорость работы оператора SELECT.
5.11.3. Блокировка на уровне записей и страниц
При рассмотрении уровней изоляции предполагалось, что блокировки ставятся на уровне записей (то есть блокируется отдельная запись). На самом деле, сервера Informix Dynamic Server позволяют выбирать уровень блокировки между блокировкой на уровне записи и блокировкой на уровне страницы. Практически во всех SQL‑серверах (в том числе, и Informix Dynamic Server) память под таблицы выделяется порциями фиксированного объема - страницами или группами страниц. Обычно размер страницы составляет 2 или 4 килобайта. В целях повышения производительности можно блокировать записи не по одной, а целыми страницами.
Для того, чтобы при создании той или иной таблицы указать требуемый уровень блокировок, в операторе CREATE TABLE надо указать соответствующую опцию:
CREATE TABLE … LOCK MODE {PAGE | ROW}
Если уровень блокировки для таблицы не указан, то по умолчанию выбирается блокировка на уровне страницы. Примеры создания таблиц с указанием уровня блокировки:
CREATE TABLE companies ( company_id SERIAL UNIQUE, name CHAR(40), address CHAR(40) ) LOCK MODE ROW
CREATE TABLE persons ( person_id SERIAL UNIQUE, company INTEGER, lname CHAR(40), fname CHAR(30), sname CHAR(20), position CHAR(20) ) LOCK MODE PAGE
5.11.4. Эффективное построение запросов
Производительность сервера базы данных при выполнении доступа к информации зависит не только от наличия или отсутствия индексов, буферизации журнала транзакций и т.д., но и от того, какие запросы исполняются. Не секрет, что одно и тоже действие (например, построение списка сотрудников какой-либо фирмы) можно запрограммировать по‑разному. Суммарная скорость исполнения данного действия зависит от того, какими операторами на SQL оно представлено. Рассмотрим пример. Допустим, у нас есть таблицы persons и companies (их структура привевдена выше, в пункте 5.11.3). Требуется построить поименный список сотрудников, работающих в фирме “АО Рога и Копыта”. Первый вариант реализации данного действия выглядит так:
SELECT lname, fname FROM persons, companies WHERE persons.company = companies.company_id AND companies.name = “АО Рога и Копыта”
При исполнении данного запроса при отсутствии индексов сервер базы данных должен будет перебрать все возможные комбинации записей из таблиц persons и companies, и для каждой комбинации проверить, выполняется ли условие из раздела WHERE. Если в таблице persons находится M записей, а в таблице companies - N записей, то всего будет проверено M*N комбинаций.
Другой запрос, функцмионально реализующий тоже самое действие, а именно, формирование списка сотрудников АО Рога и Копыта выглядит следующим образом:
SELECT lname, fname FROM persons WHERE persons.company IN (SELECT company_id FROM companies WHERE name = “АО Рога и Копыта”)
При исполнении этого запроса, сервер вначале просмотрит таблицу companies и найдет одно требуемое значение, затем он просмотрит таблицу persons и сравнит поле company с найденным значением. В итоге, он просмотрит M+N записей. Если значения M и N достаточно велики (порядка сотен или более), то второй запрос будет исполняться много быстрее первого. Естественно, это очень грубая оценка без учета наличия индексов, возможностей сервера по оптимальному исполнению запросов и т.д., но выигрыш в полмиллиона раз раз для таблиц с миллионом записей каждая уже впечатляет.
Приведенный выше пример позволяет сформулировать следующее общее правило: уменьшайте число таблиц, задействованных в одном запросе.
На основании этого правила можно сделать вывод о том, что иногда выгоднее разбить сложный запрос на несколько простых (с использованием временной таблицы), нежели пытаться уместить какое-либо действие в один запрос. Допустим, вас отправляют в командировку в г. Тверь, и вы хотите посетить директоров всех имеющихся в этом городе фирм-партнеров. Если данный запрос представить на SQL, то первый, неоптимизированный вариант запроса мог бы выгшлядеть следующим образом:
SELECT persons.lname, persons.fname, persons.sname, companies.address FROM persons, companies WHERE companies.address MATCHES "*Тверь*" AND companies.companies_id = persons.company AND persons.position = “директор”
Привести данный запрос к виду
SELECT . . . FROM persons WHERE . . .
подобно тому, как мы это делали ранее, невозможно, поскольку поле address из таблицы companies нам нужно в конечном результате. Но число фирм, расположенных в Твери (как и в любом другом городе), по сравнению с общим числом фирм в нашей базе данных невелико. Поэтому следующий набор операторов будет, скорее всего, выполняться быстрее, нежели один первоначальный оператор:
CREATE TEMP TABLE local_companies ( company_id INTEGER, name CHAR(40), address CHAR(40) )
{наполнение временной таблицы списком фирм, распорложенных в Твери} INSERT INTO local_companies(company_id, name, address) SELECT company_id, name, address FROM companies WHERE address MATCHES "*Тверь*"
{получение требуемых данных} SELECT persons.lname, persons.fname, persons.sname, local_companies.address, local_companies.name FROM persons, local_companies WHERE local_companies.companies_id = persons.company AND persons.position = “директор”
DROP TABLE local_companies
Существуют множество рекомендаций, по написанию эффективных запросов, но практически все они основаны или на разделении одного сложного запроса на несколько простых, или на более аккуратном и тщательном написании условия с тем, чтобы оптимизатор смог понять, какие же данные требуются.