Informix Logo


Informix Magazine Russian Edition Апрель 1998г. 

Определение оптимальной структуры базы данных

Андрей Прохоров,
менеджер компании Informix
 
e-mail andreyp@informix.com

Скорость работы сервера БД определяется рядом факторов и одним из наиболее важных является используемая структура данных. Можно настроить сервер БД Informix, можно перестроить ядро операционной системы, выжимая из нее все, на что она способно, но вносить изменения в структуру данных на этапе эксплуатации, как правило, весьма затруднительно. Поэтому, чем раньше Вы задумаетесь об оптимальности структуры вашей БД, тем меньше времени будет потрачено на мучительные переделки и бессонные ночи в обнимку с клавиатурой. 
 

Использование узких таблиц.

Прежде всего необходимо обратить внимание на ширину таблиц, т. е. на количество байт, которое занимает одна строка. Таблицы, к которым происходит наиболее частое обращение, желательно делать как можно более узкими. Причин для этого несколько, рассмотрим их. 
Во-первых, когда сервер БД не может использовать индекс для ускорения поиска, он вынужден последовательно просматривать одну за другой все строки таблицы. Поэтому, чем длиннее строки таблицы, тем больше времени будет затрачено на поиск. 
Во-вторых, в большинстве случаев с БД приходится работать в режиме журналирования операций, при котором информация о всех ее изменениях помещается в специальный журнал. При изменении даже одного поля в этот журнал попадает вся строка. Следовательно, увеличение длины строки приводит к увеличению числа операций ввода/вывода. 
Рассмотрим пример складской системы, в которой организовано хранение информации о товарах. Каждый тип товара, хранимого на складе, будем характеризовать идентификатором товара, его названием, количеством, находящимся на хранении в настоящий момент, сколько зарезервировано, описание способа хранения, кодом производителя. Таким образом, можно сформировать следующую таблицу. 
 

Идентифи- катор товара Название Количество на складе Зарезер- вировано Описание Способ хранения Код произво- дителя
             

 
Очевидно, что не ко всем полям таблицы придется обращаться одинаково часто. Какая-то информация будет нужна чаще, какая-то реже. В наиболее типичном случае больше всего обращений будет к полям: идентификатор товара, код товара, количество на складе, зарезервировано. Информация из остальных полей запрашивается достаточно редко. Получается, что большую часть из длины строки составляют наименее часто запрашиваемые данные. Поэтому эту большую таблицу имеет смысл разбить на две, как показано далее. 

Таблица с часто запрашиваемыми и изменяемыми данными. 

Идентификатор товара  Код товара Количество на складе Зарезервировано
       

 
Таблица с редко запрашиваемыми и еще реже изменяемыми данными. 

Идентификатор товара  Описание Способ хранения Код производителя
       

 
Безусловно, за улучшения жизни приходится платить. В данном случае, если понадобятся данные сразу из двух новых таблиц, потребуются дополнительные вычисления для их объединения. В первоначальном варианте, когда мы имели дело только с одной таблицей в подобных затратах необходимости не было. Однако, таблица с часто запрашиваемыми и часто изменяемыми данными получилась компактной. В результате мы можем рассчитывать на увеличение скорости работы нашего приложения, несмотря на то, что некоторые, редко встречающиеся запросы, станут выполняться медленнее. 
 

Применение индексов.

После описание структуры таблиц имеет смысл более подробно рассмотреть стратегию применения средств ускорения поиска данных. Одним из самых эффективных средств решения этой задачи являются индексы. В самом общем виде индекс представляет собой некоторую структуру данных и алгоритм, позволяющие по заданному значению поля или нескольких полей определить расположение строки. 
Индексами, как и всяким мощным средством, необходимо пользоваться осторожно, четко представляя, что Вы при этом выигрываете, а что теряете. В противном случае попытка ускорить один тип запросов может привести к недопустимому замедлению других. 
 

Поиск по индексу.

По своему определению индексы призваны ускорять поиск данных. При благоприятных условиях выигрыш в скорости, по сравнению с последовательным перебором, экспоненциально зависит от числа строк в таблице, т. е. чем больше строк, тем эффективнее поиск по индексу. На практике, при удачном использовании индекса скорость поиска практически не зависит от числа строк в таблице. 
Это объясняется структурой индекса, который представляет собой двоичное дерево ( точнее В+-дерево ). В этом случае число операций при поиске пропорционально логарифму от числа строк. При последовательном переборе количество выполняемых операций прямо пропорционально количеству строк. А если быть точнее, то скорость последовательного перебора зависит еще и от ширины таблицы. Индекс при операциях поиска так же мало чувствителен к ширине колонки по которой он построен. В результате ширина таблицы в целом и даже колонки, по которой построен индекс, в большинстве случаев, не оказывают значительного влияния на скорость поиска. 
Но, разумеется, чудес не бывает и свои ограничения есть и у индексов. Эффективность поиска по ним может сильно ухудшаться, если речь идет о колонках с часто повторяющимися значениями. В такой ситуации элементы индексного дерева начинают просматриваться последовательно, поскольку сервер не может отличить один элемент от другого и вынужден просмотреть все элементы с указанным значением. Таким образом приходится возвращаться к той проблеме, от которой пытались уйти. 
В общем случае индекс рекомендуется применять, когда в таблице более сотни записей и каждое значение поля встречается не чаще, чем в 10 процентах строк. 
 

Сцепленный ключ. Когда он необходим.

Как бы не была тяжела какая-либо проблема, всегда существуют пути, если не ее решения, то, по крайней мере, обхода. Есть способы борьбы и с проблемой индексирования часто повторяющихся значений. 
Например, рассмотрим ситуацию, когда необходимо разыскать какую-либо информацию о человеке. Если вы располагаете только его фамилией, то задача с большой вероятностью просто не разрешима. Возможно вам и повезет с какой-нибудь редкой фамилией типа Небывающийстакойфамилией. А если встретится Иванов или Петров? 
В этом случае не обойтись без имени и отчества, зная которые, можно значительно сократить круг поиска. Строится один индекс по нескольким полям, называемый сцепленным индексом. Применительно к данному примеру индекс необходимо создать по полям таблицы, содержащим фамилию, имя и отчество, и в структуре этого индекса появляется значительно больше элементов, чем было ранее. Сервер БД сможет отличить Иванова Ивана Ивановича от Иванова Ивана Васильевича и не перебирать подряд всех однофамильцев. 
Можно спросить, а зачем создавать особый тип индекса, вместо того, чтобы просто взять и объединить три поля в одно? Проблема в том, что тогда мы потеряем возможность по отдельности оперировать каждым из этих трех атрибутов. Потеряется, например, возможность выбрать только имена и фамилии. 
Следует отметить, что сцепленный индекс может применяться для поиска по первым атрибутам, входящим в него, а не только по всем сразу. Например, если индекс будет построен по фамилии, имени и отчеству, то его также можно использовать для поиска по фамилии, по фамилии и имени. А вот, для поиска только по имени или по имени и отчеству он уже не применим. Поэтому, необходимо внимательно относится к последовательности перечисления полей при построении сцепленного индекса. 
 

В каких случаях индексы бессильны.

Увы, классические индексы на базе В+-деревьев имеют и принципиальные ограничения, которые не всегда можно разрешить атакой в лоб. Рассмотрим некоторые из них, а так же возможные способы их решения. 
Когда задается условие поиска, следует избегать использования операторов OR, например, в запросе типа: 

SELECT * FROM some_table WHERE some_code = 1 OR some_code = 2

При выполнении этого запроса будет использоваться последовательный перебор записей таблицы, даже если индекс по полю some_code существует. Выходов из подобной ситуации может быть несколько. Во-первых, можно использовать условие IN. 

SELECT * FROM some_table WHERE some_code IN ( 1, 2 )

Можно также перестроить запрос с помощью UNIOIN. 

SELECT * FROM some_table WHERE some_code = 1 
UNION 
SELECT * FROM some_table WHERE some_code = 2

Результат выполнения всех запросов будет одинаков. Однако, в последних двух случаях сервер БД сможет использовать индекс для ускорения поиска. 

Другим оператором несовместимым с поиском по индексу является оператор неравенства - <>. Его можно с успехом заменить двумя уже указанными способами. Например 

SELECT * FROM some_table WHERE some_code <> 2

можно заменить на 

SELECT * FROM some_table WHERE some_code > 2 
UNION 
SELECT * FROM some_table WHERE some_code < 2

Во всех примерах преобразования одного запроса в другой результаты будут одинаковыми, вопрос только в том, сколько потребуется времени на их выполнение. Возможно требовательный читатель воскликнет в праведном гневе, - <А о чем думает ваш оптимизатор запросов?! Почему он не берет на себя эту почетную обязанность правильного формирования запросов?!>. Как сказал великий Ферма - <Я нашел доказательство, но оно не поместится на этих полях>. Возможно Вы и найдете логичный ответ на данный вопрос, а возможно, создадите свой оптимизатор запросов. 
Но вернемся к нашим индексам. Существуют примеры, когда и методы обхода трудностей себя исчерпывают. Речь идет о поиске по шаблону, примеры которого приведены ниже. 

SELECT * FROM some_table WHERE some_field MATCHES "aa*" 
SELECT * FROM some_table WHERE some_field MATCHES "*aa"

В первом случае, все замечательно, и индекс может быть с успехом применен, то во втором, когда неизвестны первые символы поля, последовательный перебор неизбежен. Когда же можно точно указать позиции в поле, по которым будет вестись поиск, то можно создать новую колонку в таблице, состоящую только из этих полей. 
Например, если известно, что поиск будет вестись с третьего по седьмой символ. При этом имеет смысл либо разбить одну колонку на несколько, либо добавить еще одно поле в таблицу, т. е. вместо: 

CREATE TABLE some_table( 
... 
long_field CHAR(10) 
...

создать либо 

CREATE TABLE some_table( 
... 
long_field CHAR(10), 
addit_field CHAR(5), -- сюда поместить с 3-го по 7-ой символы поля long_field 
...

либо 

CREATE TABLE some_table( 
... 
long_field1 CHAR(2), -- сюда поместить с 1-го по 2-ой символы поля long_field 
long_field2 CHAR(5), -- сюда поместить с 3-го по 7-ой символы поля long_field 
long_field3 CHAR(3), -- сюда поместить с 8-го по 9-ый символы поля long_field 
...

В последних двух случаях мы получим возможность использовать индекс для ускорения поиска. 
 

Отрицательные последствия применения индексов.

Разумеется, за применение индекса придется заплатить. Помимо дополнительного дискового пространства индексное дерево требует модификации при каждом изменении исходных данных. В результате ускорение поиска приводит к существенному замедлению процесса изменения данных. Причем, замедление пропорционально размеру индексного дерева, а значит и числу строк в таблице. На размер индексного дерева также влияет ширина индексируемой колонки. Поэтому, если данные в таблице подвержены частым изменениям, не следует увлекаться построением по ней большого числа индексов. Более того, перед выполнением массовых изменений имеет смысл удалить все имеющиеся индексы для данной таблице, а затем построить их заново. 

 


Украинская баннерная сеть
 

[Home]

Сайт поддерживается группой пользователей Информикс на Украине.

Hosted by NO-more.