Под оптимальной работой с БД обычно понимают создание таких условий, когда обеспечивается наибольшее быстродействие БД при минимально возможных затратах ресурсов.
Оптимизация зависит от многих факторов, которые можно разбить на три группы:
• оптимизация структуры БД;
оптимизация запросов;
• оптимизация клиентского приложения.
Такой важный фактор, как производительность сервера, не часто (к сожалению) может подвергаться воздействию со стороны разработчиков БД и приложений для работы с БД. Поэтому, декларировав известный факт, что чем мощнее аппаратура сервера, тем лучше, рассмотрим такие факторы, которые поддаются воздействию со стороны разработчиков и администраторов БД.
37.1. Оптимизация структуры БД
Для обеспечения оптимального доступа к информации в БД при проектировании физической и логической структуры БД следует учитывать ряд факторов. Перечислим некоторые из них.
37.1.1. Нормализация таблиц: теория и практика
На быстродействие БД непосредственно влияет то, каким образом была проведена нормализация таблиц, то есть каким образом устранена в таблицах избыточность данных. Часто нормализация ухудшает быстродействие. Приведем пример. Пусть имеется таблица "Сотрудники" и таблица "Оклады":
ФИО |
Должность |
|
Иванов И.И. |
директор |
|
Петров П.П. |
инженер |
|
Яковлев Я.Я. |
инженер |
|
Сотрудники |
Оклады |
|
Должность |
Оклад |
|
директор |
1000 |
|
бухгалтер |
600 |
|
инженер |
650 |
С точки зрения теории, нормализация таблиц приводит к наиболее ясному отображению сущностей из предметной области. Устраняя избыточность данных, она тем самым существенно экономит дисковое пространство. Например, если удалить таблицу "Оклады" и ввести поле "Оклад" в таблицу "Сотрудники", будет налицо явно нерациональное расходование дискового пространства, что всегда критично при больших объемах данных. Например, если в таблице "Сотрудники" присутствуют сведения о 1000 инженерах, одно и то же значение оклада инженера (значение 650) будет дублироваться 999 раз.
С точки зрения практики, и именно на больших объемах данных, оптимизация может существенно уменьшить быстродействие. Например, при обращении к таблице "Сотрудники" часто требуется знать оклад сотрудника. Для этого необходимо обратиться к таблице "Оклады", найти нужную должность и уже затем получить оклад. Как видим, вместо одного обращения (к столбцу уже найденной записи в таблице "Сотрудники") необходимо дополнительно обратиться к таблице "Оклады", осуществить операцию поиска в ней, эффективность которого, вообще говоря, зависит от многих факторов, в первую очередь от правильного построения индексов, и уже затем считать нужное значение оклада. При обращении к одной, пусть большой, таблице БД, затрачивается меньше времени, чем при обращении к нескольким, более мелким таблицам.
Кроме того, на практике высокая степень нормализации таблиц приводит к большому количеству таблиц, в результате чего структура информации в БД не воспринимается разработчиком целостно. Невозможность целостного представления структуры данных в БД является одним из "человеческих факторов", способных внести серьезные ошибки в структуру БД уже на стадии разработки БД, что впоследствии может иметь самые серьезные - и всегда негативные - последствия. Например, БД, состоящая из 50-100 таблиц, каждая из которых имеет минимум 2-3 связи с другими ТБД, уже выходит за рамки человеческого понимания, не говоря о БД в 500 и более таблиц.
Поэтому при проектировании структуры БД следует учитывать как отрицательные, так и положительные стороны нормализации таблиц. Обычно жертвуют дополнительным расходом дисковой памяти для хранения не полностью нормализованных таблиц, стремясь обеспечить максимальное быстродействие, что актуально при возрастании числа пользователей, одновременно работающих с системой.
37.1.2. Частичная зависимость структуры данных от методов доступа к ним
Хотя из теории известно, что структура данных в БД должна быть независима от способов доступа к данным, на практике это обычно не так. Приведем
пример.Пусть в БД, работающей под управлением
InterBase, имеется родительская таблица "Бюджетные лимиты подразделения" и подчиненные ей таблицы "Корректировки лимита", "Суммы сверх лимита", "Расходы лимитированных средств". При каждом расходовании лимитных средств (например, на покупку компьютеров) необходимо вычислить текущий остаток по такой формуле:S1 + S2 +S3 - S4
где
S1 - начальный лимит (ТБД "Бюджетные лимиты подразделения")
;S2 - сумма всех корректировок лимита по данному подразделению (таблица "Корректировки лимита");
S3 - сумма всех записей сверх лимита по данному подразделению (таблица "Суммы сверх лимита");
S4 - сумма всех предыдущих расходов подразделения по данному лимиту (таблица "Расходы лимитированных средств").
Пусть это соответственно значения 10000000 + 1000000 + 400000 - 6000000 = 5400000(рублей).
Пусть требуется записать сведения о расходе 3000000 рублей. Однако нужно быть уверенным в том, что другой пользователь в этот же самый момент не изменит ни одну из записей в указанных таблицах, результатом чего будет изменение остатка лимитных средств 5400000 (наверняка в меньшую сторону).
Чтобы блокировать текущий лимит данного подразделения, добавим в таблицу "Бюджетные лимиты подразделения" поле
STATUS CHAR(l), и каждую транзакцию на изменение таблиц "Бюджетные лимиты подразделения", "Корректировки лимита", "Суммы сверх лимита", "Расходы лимитированных средств" в приложении будем начинать с изменения этого статуса:Database1.StartTransaction;
TQuery1.Params[0] := Code;
// Code
= код записи в ТБД "Бюджетные лимиты подразделения"TRY
TQueryl.ExecSQL;
{Текст запроса вида
UPDATE ... SET STATUS = 'A' WHERE CodeZap = :Par1...}
{Вычисление текущего остатка данного бюджетного лимита по данному подразделению}
{Выполнение действий по изменению записей в дочерних таблицах}
EXCEPT
...
Тогда всякая другая транзакция, желающая изменить записи в дочерних таблицах (подчиненных текущей записи в таблице "Бюджетные лимиты подразделения") при выполнении
TQuery1.ExecSQL возбудит исключение (deadlock) в связи с тем, что запись изменена другой транзакцией, пока не подтвердившей и не отменившей сделанных ею изменений. Однако эта блокировка не распространяется на другие бюджетные лимиты данного подразделения или других подразделений.Как видим, введение дополнительного поля способно обеспечить желаемый режим разграничения доступа к данным.
Таким образом, проектируя логическую структуру данных в БД, невозможно абстрагироваться от того, каким образом эти данные будут обрабатываться на сервере и в клиентском приложении.
Заметим, что зависимость между структурой запросов к БД (в основном в операторе
SELECT) и структурой и составом индексов таблиц также свидетельствует о связи между структурой данных и методами доступа к ним.37.1.3. Физические характеристики БД
Для быстрого доступа к записям таблицы БД необходимо, чтобы таблица физически занимала в БД непрерывный блок страниц. Известно, что при выделении новых страниц в БД
InterBase не делает никаких попыток выделять смежные страницы для хранения одной и той же таблицы БД. Поэтому данные, относящиеся к одной странице в БД, могут быть фрагментированы.Хранение множественных поколений записей также приводит к сильному "загрязнению" БД и замедляет работу с ней. Напомним, что при всяком изменении записи фактически создается новая версия записи. Версии записей, измененные или добавленные транзакциями, которые впоследствии отменяются, из БД не удаляются. Кроме того, при удалении записей из БД не происходит перемещение оставшихся записей с тем, чтобы удалить образовавшиеся "дыры" на страницах БД.
Решением указанных проблем является периодическое создание резервной копии и восстановление из нее БД. При этом:
• собирается "мусор", т.е. версии записей, которые далее не будут востребованы;
• устраняются "дыры" на страницах БД, образовавшиеся после удаления записей;
• каждая таблица размещается в непрерывном блоке страниц.
Немаловажным является и размер самой страницы БД. Запись таблицы БД должна размещаться максимум на одной странице БД. Как известно, чтение-запись в БД
InterBase осуществляются страницами. Поэтому, если размер страницы мал для хранения одной записи и она располагается на более чем одной странице, для чтения такой записи нужно выполнить несколько физических операций чтения. С другой стороны, размер страницы не должен быть слишком велик, поскольку в этом случае будут считываться ненужные записи.Размер буфера ввода-вывода также способен оказывать влияние на быстродействие при работе с базой. Для БД, к которым чаще применяются операции чтения, рекомендуется увеличить размер буфера ввода-вывода. Для БД, в которых чаще выполняются операции записи данных, размер буфера рекомендуется уменьшить.
Оптимизация запросов к БД связана с построением адекватной запросам и оптимальной структуры индексов таблиц БД и оптимизацией собственно текстов запросов.
37.2.1. Оптимальная структура индексов
От структуры индексов таблиц БД в огромной степени зависит эффективность выполнения запросов. При выполнении запросов
InterBase сначала просматривает список индексов, определенных для таблиц, участвующих в запросе. Затем выбирается одна из двух схем выполнения запроса - использовать имеющиеся индексы или последовательно просмотреть таблицы. Оптимизатор InterBase стремится выполнить запрос с максимальным быстродействием и с минимальными накладными расходами. Он всегда оптимизирует запрос при его первом использовании, основываясь на текущем состоянии БД. Повторно параметрические запросы, у которых меняются только значения запросов, не оптимизируются.Происходит лишь предварительное связывание формальных и фактических параметров, после чего запрос выполняется.
Хотя состояние БД может меняться и поэтому полностью предсказать, по какой схеме оптимизатор
InterBase будет выполнять запрос, нельзя, существуют общие положения, которые следует учитывать при проектировании запросов.Эффективность использования индекса при поиске информации в таблице БД сильно зависит от того, построен ли индекс по уникальным значениям и, если нет, насколько отличаются данные, по которым он построен.
Пусть необходимо выбрать из таблицы
RASHOD все записи о расходе товара за 10.01.1997, у которых количество расходуемого товара превышает 300 единиц:SELECT * FROM RASHOD WHERE DAT_RASH = "10/01/1997" AND KOLVO > 300
При выполнении запроса
InterBase определяет - есть ли индексы, построенные одновременно по столбцам DAT_RASH и KOLVO, столбцам KOLVO и DAT_RASH, или индексы, в которые указанные столбцы входят в качестве ведущих (например, индекс, построенный по столбцам DAT_RASH, KOLVO, TOVAR, но не индекс, построенный по столбцам DAT_RASH, TOVAR, KOLVO). При отсутствии таких индексов проверяется наличие индексов отдельно по столбцам DAT_RASH и KOLVO.В случае отсутствия таких индексов поиск записей, удовлетворяющих запросу, осуществляется путем перебора всех записей в таблице, т.е. путем последовательного доступа, что обеспечивает наименьшую эффективность выполнения запроса. В случае нескольких индексов, по которым можно осуществить поиск (например, индекс по столбцам
DATJR.ASH, KOLVO и по столбцам KOLVO, DAT_RASH), выбирается для использования тот, у которого выше показатель полезности индекса (selectivity).Показатель полезности индекса
рассчитывается как число различающихся значений индексных полей внутри индекса, отнесенное к среднему количеству записей. Этот показатель рассчитывается при создании индекса. После внесения изменений в таблицу, по которой построен индекс, меняется степень отличия значений столбцов, по которым построен индекс. Поэтому рассчитанный показатель полезности может не отражать реального состояния индекса и значение показателя рекомендуется принудительно пересчитывать: время от времени - при внесении небольших изменений и всегда - при внесении существенных изменений. Пересчет реализуется операторомSET STATISTIC INDEX
ИмяИндексаСреднее количество записей - показатель, который рассчитывается всякий раз при оптимизации запроса как количество страниц БД, занятых этой таблицей, деленное на максимальное число записей на странице. Уменьшение числа страниц, занятых БД, и уничтожение на них "дыр" ведут к уменьшению показателя среднего числа записей и, как следствие - к повышению показателя полезности индексов. Это еще один аргумент в пользу периодического сжатия БД путем создания резервной копии и восстановления из нее БД.
Для участия в выполнении запроса выбираются индексы с максимальным показателем полезности. Такие индексы обеспечивают более быстрый поиск. Максимальным показателем полезности обладают уникальные индексы, т.е. индексы, построенные по определениям первичных и уникальных ключей.
37.2.3. Просмотр плана выполнения запросов
При выполнении запросов к БД в утилите
WISQL установим режим показа плана выполнения запроса (выбрав элемент меню Session | Bas'ic Settings и отметив режим Display Query Plan). Тогда при выполнении запросов будет выводиться и план их выполнения. Под планом выполнения запроса понимается перечень индексов, используемых InterBase при выполнении запроса. Слово NATURAL означает последовательный перебор таблицы. Например,SELECT RASHOD.*, TOVARY.ZENA
FROM RASHOD, TOVARY
WHERE RASHOD.TOVAR = TOVARY.TOVAR
PLAN JOIN (RASHOD NATURAL,TOVARY INDEX (RDB$PRIMARY8))
NRASH |
DATRASH |
KOLVO |
TOVAR |
POKUP ZENA |
2 |
10-JAN-1997 |
20 |
Сахар |
Лира, ТОО 4 |
3 |
10-JAN-1997 |
509 |
Сахар |
<null> 4 |
4 |
10-JAN-1997 |
3000 |
Ставрида консерв. |
Адмирал, АО 5 |
5 |
10-JAN-1997 |
4000 |
Кока-кола |
Саяны, ИЧП 3 |
6 |
20-JAN-1997 |
30 |
Сахар |
Саяны, ИЧП 4 |
7 |
20-JAN-1997 |
20 |
Кока-кола |
<null> 3 |
8 |
20-JAN-1997 |
1000 |
Кока-кола |
Адмирал, АО 3 |
1 |
10-JAN-1997 |
100 |
Кока-кола |
Адмирал, АО 3 |
Для принудительного выполнения запроса по тому или иному плану, следует в операторе
SELECT использовать предложениеPLAN
<план выполнения_запроса>< план выполнения запроса > =
[JOIN | [SORT] MERGE
] (<элемент_плана> | < план _выполнения_запроса >[, < элемент плана > I < план выполнения запроса > . . . ] )
<элемент_плана> = {таблица | алиас}
NATURAL | INDEX ( <
индекс> [, < индекс >...]) I ORDER < индекс >Синтаксис предложения
PLAN относится как к единичной таблице БД, так и к нескольким таблицам. В последнем случае выполняется соединение таблиц для увеличения скорости выполнения запроса, что определяется использованием необязательного ключевого слова JOIN. В том случае, если не существует индексов, по которым данные таблицы могли бы быть соединены, для увеличения скорости выполнения запроса указывают ключевые слова SORT MERGE.<элемент_плана>
является именем таблицы, в которой производится поиск данных. В том случае, если одна и та же таблица несколько раз участвует в запросе, для сокращения текста плана полезно использовать ее псевдоним, то есть обозначение, указываемое в предложении FROM после имени таблицы. Следующие ключевые слова определяют способ доступа к данным.NATURAL
(по умолчанию) - указывает, что для поиска записей применяется последовательный доступ. Это единственный способ поиска записей в том случае, если нет подходящих индексов.INDEX
- указывает один или несколько индексов, которые должны использоваться для поиска записей, удовлетворяющих условию запроса.ORDER
- указывает, что <элемент_плана> должен быть отсортирован по указанному индексу.37.2.4. Целесообразность создания индексов
Индексы необходимо создавать в случае, когда по столбцу или группе столбцов часто
• производится поиск в БД (столбец или группа столбцов часто перечисляются в предложении
WHERE оператора SELECT);• строятся объединения таблиц;
производится сортировка НД, возвращаемых в качестве результатов запросов к БД (т.е. столбец или столбцы часто используются в предложении
ORDER BY оператора SELECT).Не рекомендуется строить индексы по столбцам или группам столбцов, которые:
• редко используются для поиска, объединения и сортировки результатов запросов;
• содержат часто меняющиеся значения, что приводит к необходимости частого обновления индекса и способно существенно замедлить скорость работы с БД;
• содержат небольшое количество вариантов значения.
37.2.5. Частичное использование составного индекса
Если запросы часто используют для поиска одни и те же столбцы, следует построить по этим столбцам индекс (если это возможно), так, чтобы чаще используемые столбцы выступали в качестве ведущих полей индекса. Тогда при поиске может быть использована часть индексных полей.
Пример. Пусть часто выполняются запросы
SELECT * FROM SOME TABLE WHERE A = : ParamA AND
В = :ParamBSELECT * FROM SOME TABLE
WHERE A = : ParamA AND
В = :ParamB AND С = :ParamCSELECT * FROM SOME TABLE
WHERE A = : ParamA AND
В = :ParamB AND С = :ParamC AND D = :ParamDSELECT * FROM SOMETABLE
ORDER BY
А,ВТогда, построив индекс по столбцам А, В, С, D, мы можем с большой долей уверенности утверждать, что данный индекс будет использован при оптимизации всех четырех запросов. В первом случае будут использовано подмножество индекса, т.е. значения А, В; во втором - значения А, В, С, в третьем - А, В, С, D (то есть все значения индекса); в четвертом - А, В.
Следует помнить, что при использовании в запросах не всех столбцов из индекса, можно использовать только непрерывную последовательность столбцов, что важно для указания порядка сортировки в предложении
ORDER BY. Например, если индекс построен по столбцам А, В, С, D, этот индекс не может использоваться для выполнения запросовSELECT * FROM SOMETABLE ORDER BY
А,СSELECT * FROM SOMETABLE ORDER BY B,D
Порядок следования условий по столбцам в предложении
WHERE оператора SELECT не важен (если условия объединены с помощью AND). Например, для выполнения следующих запросов может использоваться один и тот же индекс:SELECT * FROM SOMETABLE WHERE A = 100 AND
В = 200SELECT * FROM SOMETABLE WHERE
В = 200 AND A = 100Однако при указании уровней сортировки в предложении
ORDER BY оператора SELECT порядок следования столбцов является существенным. Например, для выполнения следующих запросов не может использоваться один и тот же индекс:SELECT * FROM SOMETABLE ORDER BY
А, В, СSELECT * FROM SOMETABLE ORDER BY
А, С, В
37.2.6. Многопоточность поиска по
OR и INПри частом использовании в условной части
WHERE оператора SELECT нескольких столбцов, связанных между собой операцией "или" (OR):SELECT * WHERE A = 100 OR
В = 200 OR С = 300вместо индекса по столбцам А, В, С лучше создать несколько индексов, построенных по каждому из этих полей, поскольку в противном случае будет осуществлен последовательный просмотр всей таблицы. Это неудивительно, так как индексно-последовательный доступ для индексов А, В, С может быть осуществлен только для столбца А; значения столбцов В и С в этом случае спонтанно разбросаны по индексу. Важно помнить, что при использовании оператора
OR в условной части оператора SELECT каждая часть условия влечет за собой отдельное сканирование таблиц, участвующих в запросе. Так, например, при выполнении оператора SELECT с условной частьюWHERE
А = 100 OR В = 200 OR С = 300будет осуществлено три отдельных сканирования таблицы (таблиц) для поиска значений, удовлетворяющих условиям А = 100; В = 200; С = 300.
Отдельный поток поиска порождает и каждый элемент в списке
IN. Например,WHERE A IN (100,200,300)
интерпретируется как
WHERE
А = 100 OR А = 200 OR A = 300.Однако при указании диапазона
BETWEENWHERE A BETWEEN 100 AND 300
этого не происходит. Поэтому там, где возможно, следует заменять
IN на BETWEEN.37.2.7. Уменьшение общего количества индексов
Следует стремиться к уменьшению количества индексов, поскольку при большом их количестве снижается скорость добавления, изменения и удаления записей в таблицах БД. Как правило, в БД определяется два вида индексов : индексы, фактически использующиеся запросами для доступа к данным, и индексы, введенные в БД для обеспечения ссылочной целостности между родительскими и дочерними таблицами БД. Если индексы не используются при доступе к данным, их следует удалять, а ссылочную целостность обеспечивать с использованием триггеров.
37.3. Оптимизация клиентских приложений
От того, каким образом организуется доступ к удаленной БД, во многом зависит, насколько эффективно будет работать с ней данное приложение.
37.3.1. Минимизация соединений с БД
Для соединения с удаленной БД в клиентских Delphi-приложениях используется компонент
TDatabase. Он служит для:• создания постоянного соединения с БД;
• создания локального псевдонима БД;
• изменения параметров соединения, установленных для псевдонима БД (в утилите
BDE Administrator);• управления транзакциями.
Если не использовать компонент
TDatabase, то соединение с БД может, в принципе, осуществлять каждый компонент типа "набор данных" (TTable, TQuery, TStoredProc). Однако следует помнить, что каждое соединение с БД потребляет системные ресурсы и их чрезмерный расход может сказаться на эффективности доступа к БД. Кроме того, при соединении с удаленной БД "напрямую", из компонентов типа "набор данных", невозможно изменять предустановленные параметры соединения.Поэтому рекомендуется снижать число соединений с удаленной БД к минимуму, а в идеале - иметь одно соединение с каждой БД.
Использование TQueryХотя при доступе к таблицам БД может использоваться два компонента типа "набор данных" -
TTable и TQuery, для доступа к удаленным данным рекомендуется использовать комнонент TQuery.ПРИМЕЧАНИЕ.
Компонент TStoredProc используется только для работы с вызываемыми процедурами и не применяется для работы с процедурами выбора, которые также могут возвращать наборы данных. Для работы с процедурами выбора также используется компонент TQuery.Предпочтительность использования
TQuery при доступе к удаленным данным определяется следующими причинами:• при доступе к табличным данным компонент
TTable считывает все записи удаленной таблицы, в то время как TQuery - ровно столько, сколько нужно для текущих целей визуализации, например, для заполнения сетки TDBGrid; при доступе к таблицам большого объема использование TTable может привести к существенным временным задержкам;• компоненты
TTable и TQuery имеют разную природу: TTable ориентирован на навигационный метод доступа к данным, что более характерно для работы с локальными СУБД; TQuery ориентирован на работу с множествами записей, что характерно при доступе к удаленным БД в архитектуре "клиент-сервер"; TTable позволяет обратиться к одной таблице БД, TQuery - к результатам выполнения запроса одновременно к нескольким ТБД; соответственно, подтверждение изменений данных в TTable осуществляется для каждой записи, что существенно увеличивает сетевой трафик; изменение данных при использовании TQuery может производиться сразу над множеством записей с использованием операторов INSERT, UPDATE, DELETE;• при помощи компонента
TQuery можно выполнять разнообразные SQL-операторы, как возвращающие НД (SELECT), так и не возвращающие его (INSERT, и т.д.).37.3.3. Перенос тяжести вычислительной работы на сервер
При работе с удаленными БД следует стремиться перенести всю тяжесть вычислительной работы на сервер, по возможности оставив приложению клиента лишь работу по реализации интерфейса с пользователем, отсылки запросов к серверу и интерпретации полученных от него данных.
Не надо обращаться к серверу с запросом необоснованно большого объема данных, на которые приходится накладывать фильтры в самом клиентском приложении.
Следует максимально использовать возможности сервера, памятуя о том, что он может выполнять большинство требований приложения быстрее и оптимальнее и, кроме того, серверу не нужно пересылать данные самому себе по сети.
Реализуйте ограничения на значения вводимых пользователем данных при помощи аппарата ограничений БД (
CONSTRAINTS), а ссылочную целостность - при помощи триггеров.Запросы, требующие при своем выполнении ветвящихся или циклических алгоритмов, а также всевозможные вычисления значений, основанные на текущих данных из БД, реализуйте при помощи хранимых процедур.
Бизнес-правила, связанные с транзакционными изменениями ряда таблиц, реализуйте при помощи триггеров.
Получайте уникальные значения числовых полей при помощи генераторов.
Повторяющиеся действия, которые могут разделяться различными приложениями и использоваться в SQL-операторах, реализуйте при помощи функций, определенных пользователем (
UDF).Перенос тяжести вычислительной работы на сервер, во-первых, убыстряет работу клиентского приложения, а во-вторых, минимизирует возможность возникновения ошибок.