Индексы

← →
korvin (2004-07-23 09:09) [0]

Прошу объяснить в двух словах:
1. Что я выигрываю при использовании индексов;
2. Как создать индексы через консоль и из программы
3. Как часто нужно реиндексировать таблицы и какой командой?

← →
Sandman25 © (2004-07-23 09:11) [1]

1. скорость чтения
2. create index см. SQL
3. зависит от задачи. update statistics см. SQL

← →
korvin (2004-07-23 09:13) [2]

А не подскажешь ли заодно литературку в электронном виде по хранимым процедурам и SQL вообще.

← →
stud © (2004-07-23 09:16) [3]

www.ibase.ru

← →
korvin (2004-07-23 09:19) [4]

Чёрт, я готов похоронить ресурс по данным тобой координатам. 20 раз перекапывал и так и не нашёл :(

← →
bushmen © (2004-07-23 09:24) [5]

> korvin (23.07.04 09:19) [4]

А yandex"ом не научился еще пользоваться?

← →
korvin (2004-07-23 09:31) [6]

> bushmen © (23.07.04 09:24) [5]
Яндексом нет, только Гоогле ... Ладно, я думал это не такая уж проблема - найти литературу, но если нет, так нет.

← →
stud © (2004-07-23 09:50) [7]

http://www.ibase.ru/ib6.htm
раздел документация почти внизу страницы. там все что нужно и даже больше.

← →
Fay © (2004-07-23 09:50) [8]

Купи книжку типа "Базы данных для блондинок".
Прочитай.
Удачи.

← →
Соловьев © (2004-07-23 09:57) [9]

http://www.krista.ru/ib/

← →
korvin (2004-07-23 18:25) [10]

> Fay © (23.07.04 09:50) [8]
Спасибо, но я не крашусь в блондина ради того, чтобы читать только одну книгу :)

> stud © (23.07.04 09:50) [7]
Мда, как ни странно всё нашлось, спасибо. Прошу прощения за свою невнимательность.

← →
VID © (2004-07-23 22:31) [11]

Korvin, дам тебе один хороший совет: когда у тебя возникает необхоимость узнать как программно создать/изменить таблицу, индекс, или другие метаданные (т.е. по сути нужен грамотный sql-скрипт), просто открой IBExpert (я надеюсь ты им пользуешься?), отобрази окно SQL-монитора, и создай нужный тебе индекс вручную, средствами IBExpert. В окне SQL-монитора, будет отображён нужный тебе SQL-скрипт, который ты сможешь использовать в дальнейшем, немного модифицировав.

← →
Fay © (2004-07-24 18:36) [12]

2VID © (23.07.04 22:31) [11]
Совет тебе. В IBExpert скрипт можно посмотеть ещё до выполнения.

← →
VID © (2004-07-24 21:47) [13]

Fay © (24.07.04 18:36) [12]
ты о вкладке "Скрипт", который есть в любом редакторе метаданных ? если да, то ответь мне плиз, как несведующему челу, узнать например следующее:

"Я хочу програмно получить список полей таблицы T1. Плиз, скажите как это сделать?",

ну и где ты ему предложишь найти вкладку "Скрипт" ?

А если воспользоваться методом, который я указал, то достаточно открыть окно СКЛ-монитора, а потом просто открыть таблицу T1 в редакторе (два раза щёлкнуть на имени таблицы будет достаточно), и скрипт получения списка полей этой таблицы (из системной таблицы, естесно), а таже многой другой интересной инфы, у нас в кармане.

ага ?

← →
Fay © (2004-07-25 03:41) [14]

2VID © (24.07.04 21:47) [13]
Дя-а-а... Невдолбенно програмный способ. 8)

← →
kaif © (2004-07-25 04:46) [15]

1. Без индексов вообще жить нельзя.

Как минимум у каждой таблицы должен быть первичный ключ (PRIMARY KEY), желательно, суррогатный (просто поле ID INTEGER), значения которого формируются генератором. Этот первичный ключ - тоже индекс. Все обновления и удаления одиночных записей следует делать, используя в условии WHERE этот первичный ключ.
Создание первичного ключа:
ALTER TABLE <имя_таблицы> ADD CONSTRAINT <имя_констрейнта> PRIMARY KEY (<имя_поля, или группы полей через запятую>)

Кроме первичных ключей очень полезны внешние ключи FOREIGN KEY. Внешние ключи позволяют поддерживать декларативную ссылочную целостность между двумя таблицами, например, ссылку таблицы-документа на таблицу-справочник.
Создание внешнего ключа:
ALTER TABLE <имя_таблицы> ADD CONSTRAINT <имя_констрейнта> FOREIGN KEY (<имя_поля, или группы полей через запятую>) REFERENCES <имя_другой_таблицы> (<имя_поля, или группы полей через запятую>)
И те и другие констрейнты (ограничения) имеют соответствующие индексы с дурацкими названиями, о которых лучше не знать, а использовать имена самих констрейнтов. Типичный пример использования имени констрейнта - удаление ключа:
ALTER TABLE <имя_таблицы> DROP CONSTRAINT <имя_констрейнта>
Любой ключ (первичный или внешний) так можно удалить.

Наконец, есть третий полезный вид констрейнтов или просто ключей - уникальные индексы. Их также можно создать похожей командой:
ALTER TABLE <имя_таблицы> ADD CONSTRAINT <имя_констрейнта> UNIQUE (<имя_поля, или группы полей через запятую>)

Если все эти принципиальные вещи сделаны, то остаются еще просто индексы, служащие для ускорения SQL-запросов. Я довольно редко создаю такие индексы, так как обычно индексов, созданных с констрейнтами бывает в 90% случаев достаточно при правильном построении системы таблиц. Так, в базе данных с 400 внешних ключей у меня всего около 5 обычных индексов для ускорения выборок.
Но тем не менее такие индексы иногда могут очень помочь ускорить запросы, иногда в тысячи раз. Их нужно создавать с умом. Бывают индексы по возрастанию и индексы по убыванию. Создаются они все командой
CREATE INDEX <имя_индекса> ON <имя таблицы> (<имя_поля, или группы полей через запятую>)
Если нужно создать индекс "с убыванием" значений используется слово DESCENDING.
Например:
CREATE DESCENDING INDEX PERSON_BIRTH_DATE ON PERSON(BIRTH_DATE )

2. Создавать индексы лучше всего руками, вводя те команды, что я показал. Хорошо бы для этого иметь какую-нибудь простую консоль ISQL (интерактивного SQL) с запоминанием истории введенных команд. Можно пользоваться IBEpert или что-то подобное. Можно даже написать свою консоль. Из программы SQL-команды можно послать из любого компонента палитры InterBase.
IBQuery.ExecSQL
IBDataSet.ExecSQL
IBSQL.ExecQuery
предварительно в свойство SQL следует вписать команду:
IBSQL.SQL.Text := "CREATE INDEX AAA_BBB ON AAA(BBB)"
транзакции нужно стартовать и подтверждать после каждой такой команды. Такие команды называют DDL (data definition language). Не рекомендуется сочетать в одной транзакции команды DDL с командами DML (обычными SELECT, INSERT, DELETE или UPDATE - data manipulation language), так как изменение структур и изменения данных в одной транзакции без Commit могут приводить к противоречиям.

3. Реиндексировать для большинства задач в IB ничего не нужно. Нужно периодически делать backup/restore базы данных. Это устраняет мусор, сокращает размер файла и строит все индексы заново.

Прежде всего рекомендую достать родную документацию по IB в формате pdf. Там несколько книг. Самая полезная - Language Reference. Там есть синтаксис всех команд, о которых я говорил.

← →
VID © (2004-07-25 10:24) [16]

А в очередь "1а" я порекомендую книгу
"Мир Interbase: 2-ое издание", авторы: А.Н.Ковязин, С.М.Востриков.

← →
bushmen (2004-07-25 14:13) [17]

> kaif © (25.07.04 04:46) [15]

Открою тебе страшную тайну: иногда можно прекрасно жить и без индексов, и даже больше - бывают случаи, когда индесы противопоказаны :)

← →
Fay © (2004-07-26 02:31) [18]

2bushmen (25.07.04 14:13) [17]
Вот это действительно страшная тайна. А ещё кто-нибудь о ней знает? 8)

← →
Deniz © (2004-07-26 07:30) [19]

> Fay © (26.07.04 02:31) [18]
Есть определенные ситуации, когда создание индекса неэффективно, например при достаточно малом кол-ве различных значений(пол "М"/"Ж" и т.д.), а в остальном см. kaif © (25.07.04 04:46) [15]

← →
Fay © (2004-07-26 08:58) [20]

2Deniz © (26.07.04 07:30) [19]
Для таких ситуаций есть специальные индексы (во всяком случае в Oracle). И они весьма эффективны.

>> в остальном см. kaif © (25.07.04 04:46) [15]
Спасибо, конечно... А что, создаётся впечатление, что мне это необходимо? 8)

← →
bushmen © (2004-07-26 09:56) [21]

> Для таких ситуаций есть специальные индексы

А на like тоже есть спец. индексы? :)

2bushmen © (26.07.04 09:56) [21]
Странный вопрос. Честно говоря, неожиданный. Вы же не хотите сказать, что "индесы противопоказаны :)", если некоторое поле, помимо всего прочего, упоминается в where в like?

> если некоторое поле, помимо всего прочего

А если нет прочего? :)

>Fay © (26.07.04 08:58) [20]
>Для таких ситуаций есть специальные индексы (во всяком случае в Oracle). И они весьма эффективны.

если база оттюнингована правильно :)

Индекс не нужен если в запросе обрабатываются все записи таблицы.
Но не одними такими запросами ведь живём :)

Давайте отделим ключи и индексы.
Ключи - это способы уникальной идентификации строки таблицы.
Индексы - это просто упрорядоченные списки, ускоряющие поиск каких-то значений за счет того, что в упорядоченном списке можно искать значение делением списка пополам, а в неупорядоченном - только перебором. соответсвенно, при миллионе записей в урпорядоченном списке нужно всего 20 шагов, чтобы найти нужное значение, а в неупорядоченном - в среднем полмиллиона.
Без ключей далеко не уедешь в SQL. Так вот поддерживать ключи, не имея индексов, было бы крайне тормозно. Поэтому начинать надо с ключей и требований уникальности и ссылочной целостности. Обычно в ключах разнообразие бывает большим, так что и индексы там эффектино работают. Я рекомендую создавать максимальное количество FOREIGN KEY и UNIQUE, сколько возможно. Многие авторы боятся или избегают FOREIGN KEY. Возможно, это пришло из MS SQL. Не знаю. Но конкретно в IB механизм FK очень хорош.
А использование множества индексов "исключительно для целей ускорения выборок" чаще свидетельствует о плохой нормализации базы данных. В хорошо нормализованной базе кроме индексов, поддерживающих первичные, альтернативные (предметные уникальные) и внешние ключи, ИМХО, практически других индексов и не бывает нужно иметь. Разве что в редких случаях.
Возьмем простой справочнки, скажем "виды бытовой техники"

1.Первичный ключ: ID INTEGER (суррогатный)
2.Альтернативный уникальный ключ: NAME VARCHAR(50)
3.Внешний ключ на эту таблицу со стороны таблицы, которая использует ссылку на этот ID вместо того, чтобы хранить имена видов техники у себя в столбце явно.

Что еще нужно?
Если база нормализована хорошо (3-я нормальная форма и выше), то практически вся база состоит из ссылок. И ключи поставляют подавляющее большинство полезных индексов, на которые будет обращать внимание оптимизатор запросов.
Кстати, любопытно, что часто обычные индексы, которые я "добавил" для ускорения выборок оптимизатор не видит и мне приходится их указывать в планах запросов явно. Такого никогда не бывакет с индексами, которые имеют прямое отношение к ключам PK, FK, UK.
вот я вчера в система allegro написал маленькую конфигурацию для учета плавки металла. Посмотрел на индексы. Их оказалось 150 штук FOREIGN KEY. Все нормально работает. Так что если кто-то будет пугать и говорить, что нужно экономить на FK - не слушайте. Это может быть верно при миллионах записей. Даже при сотнях тысяч записей экономить на индексах не нужно. Экономить нужно свое время и время пользователя, который будет ждать результатов запросов.

[29] kaif © (26.07.04 14:39)

А использование множества индексов "исключительно для целей ускорения выборок" чаще свидетельствует о плохой нормализации базы данных. В хорошо нормализованной базе кроме индексов, поддерживающих первичные, альтернативные (предметные уникальные) и внешние ключи, ИМХО, практически других индексов и не бывает нужно иметь. Разве что в редких случаях.

По полю типа Date почти всегда нужен ускоряющий индекс. Или это и есть тот самый "редкий случай"?

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