Индексы пакостят?

← →
korvin © (2005-01-06 09:50) [0]

Уважаемые мастера. Вот буквально недавно решил начать использование индексов в своих базах. Создал примерно по одинаковому принцыпу просто (IBExpert хотя как - наверно не существенно) 2-3 штуки (отдельно) на каждую большую таблицу (в дополнение того, что создан автоматически по ID полю).
Вот тут и начались казусы - простые запросы теперь выдают вроде бы всё отлично и раз в 6 быстрее, а те, которые имеют соединение с другими таблицами (поля по которым устанавливается соединение используются в индексах обеих таблиц) показывают далеко не все данные. Более того, записи добавленные после создания индексов вообще в запросах перестали учавствовать.
Пришлось большинство индексов убрать ... но какой тогда вообще от них толк? Подскажите, где я наступил на грабли?

← →
Fay © (2005-01-06 09:56) [1]

Давай DDL таблиц и индексов, примеры запросов, точные траблы.

← →
korvin © (2005-01-06 10:14) [2]

DLL не используется. Структуры таблиц слишком широки для описания. Но в общем виде всё стандартно, но "вырву" кусок:
Таблица TTN1:
ID_TTN1 FLOAT - (индекс появился автоматом при создании таблицы)
KLIENT_KOD VARCHAR (4)
TTN1_SUMA NUMERIC (8,2)
Создаю индекс по полю: create index I_TTN1_KLIENT_KOD on TTN1 (KLIENT_KOD)

Таблица KLIENT:
ID_KLIENT - (индекс появился автоматом при создании таблицы)
KLIENT_KOD VARCHAR(4)
KLIENT_NAME VARCHAR (70)
Создаю индекс по полю: create index I_KLIENT_KLIENT_KOD on KLIENT (KLIENT_KOD)

Запрос в общем виде выглядит ... хотя бы так:
select * from TTN1, KLIENT
where KLIENT.KLIENT_KOD=TTN1.KLIENT_KOD
and KLIENT.KLIENT_KOD>10
and TTN1_SUMA=4000
order by KLIENT.KLIENT_KOD

Я конечно сильно упростил реальную картину, но даже этот минимум работает с погрешностью.

← →
Fay © (2005-01-06 10:26) [3]

1) Можно было дать нормальные скрипты для создания таблиц.
2) Где план запроса?
3) Где тестовые данные, результат и ожидаемый результат?

← →
Fay © (2005-01-06 10:26) [4]

>> Я конечно сильно упростил реальную картину
Пока только для себя

← →
dolmat (2005-01-06 10:32) [5]

Оптимизация запросов
Оптимизация запросов к БД связана с построением адекватной запросам и оптимальной структуры индексов таблиц БД и оптимизацией собственно текстов запросов.
Оптимальная структура индексов
От структуры индексов таблиц БД в огромной степени зависит эффективность выполнения запросов. При выполнении запросов 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). При отсутствии таких индексов проверяется наличие индексов отдельно по столбцам DATJRASH и KOLVO.
В случае отсутствия таких индексов поиск записей, удовлетворяющих запросу, осуществляется путем перебора всех записей в таблице, т.е. путем последовательного доступа, что обеспечивает наименьшую эффективность выполнения запроса. В случае нескольких индексов, по которым можно осуществить поиск (например, индекс по столбцам DAT_RASH, KOLVO и по столбцам KOLVO, DAT_RASH), выбирается для использования тот, у которого выше показатель полезности индекса (selectivity).
Показатель полезности индекса рассчитывается как число различающихся значений индексных полей внутри индекса, отнесенное к среднему количеству записей. Этот показатель рассчитывается при создании индекса. После внесения изменений в таблицу, по которой построен индекс, меняется степень отличия значений столбцов, по которым построен индекс. Поэтому рассчитанный показатель полезности может не отражать реального состояния индекса и значение показателя рекомендуется принудительно пересчитывать: время от времени - при внесении небольших изменений и всегда - при внесении существенных изменений. Пересчет реализуется оператором
SET STATISTIC INDEX ИмяИндекса
Среднее количество записей - показатель, который рассчитывается всякий Раз при оптимизации запроса как количество страниц БД, занятых этой таблицей, деленное на максимальное число записей на странице. Уменьшение
числа страниц, занятых БД, и уничтожение на них "дыр" ведут к уменьшению показателя среднего числа записей и, как следствие - к повышению показателя полезности индексов. Это еще один аргумент в пользу периодического сжатия БД путем создания резервной копии и восстановления из нее БД.
Для участия в выполнении запроса выбираются индексы с максимальным показателем полезности. Такие индексы обеспечивают более быстрый поиск. Максимальным показателем полезности обладают уникальные индексы, т.е. индексы, построенные по определениям первичных и уникальных ключей.

← →
dolmat (2005-01-06 10:34) [6]

Просмотр плана выполнения запросов
При выполнении запросов к БД в утилите WISQL установим режим показа плана выполнения запроса (выбрав элемент меню Session \ Basic Settings и отметив режим Display Query Plan). Тогда при выполнении запросов будет выводиться и план их выполнения. Под планом выполнения запроса понимается перечень индексов, используемых InterBase при выполнении запроса. Слово NATURAL означает последовательный перебор таблицы. Например,
SELECT RASHOD.*, ТOVARY.ZENA
FROM RASHOD, TOVARY
WHERE RASHOD.TOVAR = TOVARY.TOVAR
PLAN JOIN (RASHOD NATURAL,TOVARY INDEX (RDB$PRIMARY8))
N_RASH DAT_RASH 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
б 20-JAN-1997 30 Сахар Саяны, ИЧП 4
7 20-JAN-1997 20 Кока-кола <null> 3
8 20-JAN-"l997 1000 Кока-кола Адмирал, АО 3
1 10-JAN-1997 100 Кока-кола Адмирал, АО 3
Для принудительного выполнения запроса по тому или иному плану, следует в операторе SELECT использовать предложение
PLAN <план _выполнения_запроса> < план _выполнения_запроса > =
[JOIN | [SORT] MERGE] (<элемент_плана> | < план _выполнения_запроса > [, < элемент_плана > | < план _выполнения_запроса > . . - ] ) <элемент_плана> = {таблица | алиас} NATURAL | INDEX ( <индекс> [, < индекс >...]) I ORDER < индекс >
Синтаксис предложения PLAN относится как к единичной таблице БД, так и к нескольким таблицам. В последнем случае выполняется соединение таблиц для увеличения скорости выполнения запроса, что определяется использованием
необязательного ключевого слова JOIN. В том случае, если не существует индексов, по которым данные таблицы могли бы быть соединены, для увеличения скорости выполнения запроса указывают ключевые слова SORT MERGE.
<элемент_плана> является именем таблицы, в которой производится поиск данных. В том случае, если одна и та же таблица несколько раз участвует в запросе, для сокращения текста плана полезно использовать ее псевдоним, то есть обозначение, указываемое в предложении FROM после имени таблицы. Следующие ключевые слова определяют способ доступа к данным.
NATURAL (по умолчанию) - указывает, что для поиска записей применяется последовательный доступ. Это единственный способ поиска записей в том случае, если нет подходящих индексов.
INDEX - указывает один или несколько индексов, которые должны использоваться для поиска записей, удовлетворяющих условию запроса.
ORDER - указывает, что <элемент_плана> должен быть отсортирован по указанному индексу.

← →
dolmat (2005-01-06 10:34) [7]

Целесообразность создания индексов
Индексы необходимо создавать в случае, когда по столбцу или группе столбцов часто
• производится поиск в БД (столбец или группа столбцов часто перечисляются в предложении WHERE оператора SELECT); строятся объединения таблиц;
производится сортировка НД, возвращаемых в качестве результатов запросов к БД (т.е. столбец или столбцы часто используются в предложении ORDER BY оператора SELECT).
Не рекомендуется строить индексы по столбцам или группам столбцов, которые:
• редко используются для поиска, объединения и сортировки результатов запросов;
содержат часто меняющиеся значения, что приводит к необходимости частого обновления индекса и способно существенно замедлить скорость работы с БД; содержат небольшое количество вариантов значения.
Частичное использование составного индекса
Если запросы часто используют для поиска одни и те же столбцы, следует построить по этим столбцам индекс (если это возможно), так, чтобы чаще используемые столбцы выступали в качестве ведущих полей индекса. Тогда при поиске может быть использована часть индексных полей.

← →
Desdechado © (2005-01-06 11:12) [8]

1. DDL - Data Definition Language, никакого отношения к DLL (Dynamic Link Library) не имеет
2. Странное что-то здесь:
KLIENT_KOD VARCHAR(4)
and KLIENT_KOD>10
Либо тип не тот, либо условие кривое. Индекс здесь как собаке 5-я нога.
3. "индекс создался автоматом" - что-то странное, из каких соображений он мог автоматом появиться?
4. читай ibase.ru - очень полезный ресурс для начинающих (и продвинутых тоже)

Индекс по FLOAT - это вАщЕ вилы 8)

>korvin © (06.01.05 10:14) [2]
>
...
TTN1_SUMA NUMERIC (8,2)Создаю индекс по полю: create index I_TTN1_KLIENT_KOD on TTN1 (KLIENT_KOD)
...
Запрос в общем виде выглядит ... хотя бы так:
select * from TTN1, KLIENT
where KLIENT.KLIENT_KOD=TTN1.KLIENT_KOD
and KLIENT.KLIENT_KOD>10
and TTN1_SUMA=4000

Ууууххх ! Попробуй преобразование CAST в интежер. Я сталкивался с тем, что для флоат иногда вообще нельзя давать условие "="

Имеется в виду строка
and TTN1_SUMA=4000

(тэги глюкнули ;(()

Индексы пакостят? Найти похожие ветки