Друзья! Подскажите, плиз, как лучше индексы организовать. Есть три поля f1, f2, f3, комбинация которых определяет условие SQL-запроса, т.е. грубо говоря может быть вот так:
WHERE f1
WHERE f2
WHERE f3
WHERE f1, f2
WHERE f1, f3
WHERE f2, f3
WHERE f1, f2, f3
Хочется обойтись минимальным количеством индексов, чтобы сэкономить время на инсертах. Записей будет много. Какие лучше сделать индексы и сколько? БД, если важно, SQLite.
Здравствуйте, cargo, Вы писали:
C>Друзья! Подскажите, плиз, как лучше индексы организовать. Есть три поля f1, f2, f3, комбинация которых определяет условие SQL-запроса, т.е. грубо говоря может быть вот так: C>Хочется обойтись минимальным количеством индексов, чтобы сэкономить время на инсертах. Записей будет много. Какие лучше сделать индексы и сколько? БД, если важно, SQLite.
Задачи ортогональные, чтобы сделать максимально быстрый поиск нужно 6 индексов:
f1, f2, f3
f1, f3, f2
f2, f1, f3
f2, f3, f1
f3, f2, f1
f3, f1, f2
Теперь, для ускорения инсертов из них следует выкинуть те, которые будут давать наименьшую пользу. Потом, по результатам, убрать третье и возможно второе поле из оставшихся.
Здравствуйте, Ziaw, Вы писали:
Z>Задачи ортогональные, чтобы сделать максимально быстрый поиск нужно 6 индексов:
Что-то я ступил достаточно трех, остальные рекомендации в силе
f1, f2, f3
f2, f3
f3, f1
cargo пишет:
> > WHERE f1 > WHERE f2 > WHERE f3 > WHERE f1, f2 > WHERE f1, f3 > WHERE f2, f3 > WHERE f1, f2, f3 > > Хочется обойтись минимальным количеством индексов, чтобы сэкономить > время на инсертах. Записей будет много. Какие лучше сделать индексы и > сколько? >
Индексы такие ( в первом приближении ):
f1, f2, f3
f1, f3
f2, f3
f3
Но вообще ещё нужно знать данные какие. А именно,
какие значения есть (или будут) в каждом поле и
насколько они различны.
Например, если в поле f1 будет всего два или три возможных
значения, то индекс (f1, f2, f3) лучше переделать на
(f2, f3, f1) или даже (f2, f3).
Вообще, в индексе поля лучше располагать таким образом,
чтобы поля с наиболее большим кол-вом возможных значений
шли бы в начале.
Здравствуйте, MasterZiv, Вы писали:
MZ>Например, если в поле f1 будет всего два или три возможных MZ>значения, то индекс (f1, f2, f3) лучше переделать на MZ>(f2, f3, f1) или даже (f2, f3).
MZ>Вообще, в индексе поля лучше располагать таким образом, MZ>чтобы поля с наиболее большим кол-вом возможных значений MZ>шли бы в начале.
А зачем? Чего хочем добиться?
У Кайта говорится, что это миф.
На эффективность выборки по индексу это не будет сказываться, потому что при поиске значения в индексе сравнение идет сразу целым вектором (f1, f2, f3), а не по каждому полю в отдельности.
В Oracle наоборот — иногда имеет смысл указывать в индексе первыми полями те, которые имеют наименьшее количество значений. Это позволяет более эффективно использовать возможность сжатия ключа индекса, т.е. уменьшить физический размер индекса на диске.
Здравствуйте, Овощ, Вы писали:
О>А зачем? Чего хочем добиться? О>У Кайта говорится, что это миф.
У Кайта о другом, а именно: при условии where f1, f2, индекс по (f1, f2) также эффективен, как и (f2, f1) — вне зависимости от селективности f1 и f2.
Но это неверно, например, при where f2.
Чтобы индекс лучше работал и давал большую селективность записей.
> У Кайта говорится, что это миф.
Я такого не знаю.
> На эффективность выборки по индексу это не будет сказываться, потому что > при поиске значения в индексе сравнение идет сразу целым вектором (f1, > f2, f3), а не по каждому полю в отдельности.
Да, целым вектором. Но в дереве -то ты рыщешь от корня к листам,
если пол-таблицы будет в записях "Ж", то тебе все эти записи придётся
просмотреть (бинарным поиском, конечно), пока не найдёшь "Надежда" и "Иванова".
Это медленнее.
Ну и если поиск будет только по одному полю, f1, индекс будет практически
безполезен.
(это конечно если индекс B+tree).
> В Oracle наоборот — иногда имеет смысл указывать в индексе первыми > полями те, которые имеют наименьшее количество значений. Это позволяет > более эффективно использовать возможность сжатия ключа индекса, т.е. > уменьшить физический размер индекса на диске.
Глупость IMHO. Сжатие индексов кстати очень много где есть.
cargo пишет:
> А возможно обойтись одним индексом f1, f2, f3 и искусственно вводить > недостающие поля, например для
А что , они у тебя все имеют константные значения ?
Тогда зачем они тебе нужны ?
> > WHERE f3 > > делать > > WHERE f1 IS NOT NULL AND f2 IS NOT NULL AND f3 = value ?
При таком условии B+tree индекс (f1, f2, f3) применяться
для позиционирования по индексу не может.
> > Я забыл уточнить, в выражении логика всегда AND.
Это понятно, иначе индекс тебе бы вообще не помог (составной).
Здравствуйте, cargo, Вы писали:
C>Друзья! Подскажите, плиз, как лучше индексы организовать. Есть три поля f1, f2, f3, комбинация которых определяет условие SQL-запроса, т.е. грубо говоря может быть вот так:
C>WHERE f1 C>WHERE f2 C>WHERE f3 C>WHERE f1, f2 C>WHERE f1, f3 C>WHERE f2, f3 C>WHERE f1, f2, f3
Добавь в эту табличку еще относительную частоту запросов и процент строк, удовлетворяющих данному условию. Тогда можно будет давать советы.
А поскольку сделать это до начала эксплуатации проблематично , то начинай без индексов. Когда количество данных в таблице станет существенно большим, можно вернуться к этому вопросу.
Здравствуйте, MasterZiv, Вы писали:
>> На эффективность выборки по индексу это не будет сказываться, потому что >> при поиске значения в индексе сравнение идет сразу целым вектором (f1, >> f2, f3), а не по каждому полю в отдельности.
MZ>Да, целым вектором. Но в дереве -то ты рыщешь от корня к листам, MZ>если пол-таблицы будет в записях "Ж", то тебе все эти записи придётся MZ>просмотреть (бинарным поиском, конечно), пока не найдёшь "Надежда" и "Иванова". MZ>Это медленнее.
Не так. Сравнение идет целым вектором, т.е. включая сразу все поля индекса и все поля из запроса.
Если ищется в индексе (Surname, FirstName) значение ("Иванова", "Надежда"), то это не означает, что вначале ищется значение "Иванова", а лишь найдя его переходим к поиску "Надежда".
Все поля ("Иванова", "Надежда") — это одно значение. И мы его можем целиком, с учетом порядка полей, сравнить с другими подобными значениями (например взятыми из индекса):
("Иванова", "Надежда") < ("Петрова", "Евгения"). (потому что Иванова < Петрова).
("Иванова", "Надежда") > ("Иванова", "Евгения"). (потому что Иванова = Иванова, но Надежда > Евгения).
Алгоритм поиска по дереву индекса по одному полю совпадает с алгоритмом поиска по дереву индеска с несколькими полями. Разница будет лишь в момент сравнения. В первом случае нужно просто сравнить два простых значения, во втором случае также идет сравнение двух значений, но комплексных. Но сравниваются они сразу целиком.
Именно благодаря такому "сравнению вектором", нам безразличен порядок следования полей в индексе (естественно для случая когда мы ищем по ключу, содержащему все проиндексированные поля).
Индексы (Surname, FirstName) и (FirstName, Surname) будут в данном случае одинаково эффективны.
MZ>Глупость IMHO. Сжатие индексов кстати очень много где есть.
Только оно имеет смысл тогда, когда у соседних строк в индексе совпадают префиксы (начальные поля).
И чем больше идет подряд строк с одинаковым префиксом — тем лучше для сжатия.
И наилучший резултат будет тогда, когда мы вынесем на первое место поля, с наименьшим количеством значений.
Овощ пишет:
> Не так. Сравнение идет целым вектором, т.е. включая сразу все поля > индекса и все поля из запроса. > Если ищется в индексе (Surname, FirstName) значение ("Иванова", > "Надежда"), то это не означает, что вначале ищется значение "Иванова", а > лишь найдя его переходим к поиску "Надежда".
Ну, и да, и нет. Ты B+tree представляешь себе ?
Если представляешь, то хорошо.
Одно могу сказать, что НЕ использовать неселективные поля
в индексе, а особенно в первых полях индекса -- это общая
расхожая рекомендация.
Здравствуйте, MasterZiv, Вы писали:
MZ>Да в общем-то всё равно, и какая СУБД, и какая версия. MZ>Главное, чтобы индекс был B+tree.
Ладно, до определенной степени все равно, если рассуждать о структуре B+tree и алгоритмах на ней, но фантазировать о деталях реализации, вроде "сравнения целым вектором", совершенно некорректно и по-дилетантски.
Другое дело, например, взять конкретную реализацию, протестить разные варианты, и сделать выводы, подкрепленные экспериментом. Это, по крайней мере, будет иметь практическую ценность.
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, MasterZiv, Вы писали:
MZ>>Да в общем-то всё равно, и какая СУБД, и какая версия. MZ>>Главное, чтобы индекс был B+tree.
W>Ладно, до определенной степени все равно, если рассуждать о структуре B+tree и алгоритмах на ней, но фантазировать о деталях реализации, вроде "сравнения целым вектором", совершенно некорректно и по-дилетантски.
W>Другое дело, например, взять конкретную реализацию, протестить разные варианты, и сделать выводы, подкрепленные экспериментом. Это, по крайней мере, будет иметь практическую ценность.
Привет. У тебя наверняка есть под рукой одна из книжек Кайта по внутреннему устройству Oracle. Там в конце главы о индексах есть пункт:
Миф: столбцы с максимальным количеством
разных значений должны указываться
первыми
Кажется, это следует из соображений здравого смысла. Если предполагается созда-
ние индекса по столбцам С1, С2 таблицы со 100000 строк, при этом столбец С1 имеет
100000 уникальных значений, а столбец С2 — 25000, индекс создается по столбцам
Т(С1,С2). Это означает, что столбец С1 должен указываться первым, что соответствует
"здравому смыслу". Фактически при сравнении векторов данных (пара значений Cl, C2
задает вектор) порядок столбцов не имеет значения.
Там же есть и тесты, подтверждающие высказывание.
Говорить в общем случае "о сравнение целым вектором" я считаю вполне возможным, поскольку это относится не к конкретной реализации, а к фундаментальным алгоритмам для структуры данных B+tree.
Или все же ты знаешь пример современной субд, где используются другие алгоритмы?
Здравствуйте, Овощ, Вы писали:
О>Привет. У тебя наверняка есть под рукой одна из книжек Кайта по внутреннему устройству Oracle. Там в конце главы о индексах есть пункт:
Вот так и надо писать — Кайт мол в такой-то книге пишет то-то (цитата), касается это Oracle такой-то версии.
О>Говорить в общем случае "о сравнение целым вектором" я считаю вполне возможным, поскольку это относится не к конкретной реализации, а к фундаментальным алгоритмам для структуры данных B+tree.
Овощ пишет:
> "здравому смыслу". Фактически при сравнении /векторов данных (пара > значений Cl, C2 > задает вектор/) /*порядок столбцов не имеет значения*/.
Так, давайте отбросим все эти премудрости про деревья и индексы,
и будем рассуждать совсем по-тупому.
Вот есть у нас индекс на поля
MyTable (Cl, C2)
Он может использоваться для ускорения поиска по полям
(Cl и C2)
или
C1.
Т.е. будет полезен потенциально в запросах типа
select * from MyTable where Cl = ? and C2 = ?
и
select * from MyTable where Cl = ?
Напомним, если кто забыл, что для запроса
типа
select * from MyTable where C2 = ?
данный индекс будет безполезен.
Теперь предположим, что С1 у нас содержит классически трудное
для индексации поле -- пол человека с двумя значениями 'М' и 'Ж'.
Потенциально для выполения запроса
select * from MyTable where C1 = 'М'
наш индекс применим, потому что первое поле в нём -- наше.
Но такой запрос будет возвращать примерно половину нашей таблицы,
и любой ценовой оптимизатор не будет рассматривать данный индекс
для выполнения этого запроса. Потому что ему придётся читать
и индек (половину), и таблицу (половину). Легче просто прочитать
всю таблицу.
А если бы мы поменяли местами поля в индексе, то
для запроса
select * from MyTable where C2 = ?
наш индекс был бы применим, как и для запроса
select * from MyTable where Cl = ? and C2 = ?
(при предположении, что C2 содержит много разных возможных
значений).
Отчасти этот ваш Кайт конечно прав, потому что если такое поле
C1 типа пола человека переместить на второе место в индексе,
оно тоже не будет особенно помогать искать, т.е. для такого
случая в общем-то всё равно, на каком месте стоит поле C1.
Но совсем не всё равно, на каком месте стоит селективное поле C2.
достаточно трех, остальные рекомендации в силе 
