Z1 писал(а) 28. Ноября 2008 :: 16:57:

Видимо, все-таки нужно поговорить на эту тему
Вот для примера ситуация:
- имеем таблицу с полями Склад, Фирма, Товар. Нужно определиться каким образом организовать индекс(ы) и его(их) тип.
Часто видел ситуацию, когда народ расставляет поля в индексе в порядке: Фирма + Склад + Товар (в случае регистра и измерений, соответственно). С одной стороны есть логика: вдруг нужно будет будет получать остатки по Фирме, а иной раз даже еще и по Складу. В большинстве случаев количество Фирм и Складов значительно меньше количества Товарных позиций.

На наборе данных (Фирма - Склад - Товар) с фильтром по Фирма1 + Склад1:
Фирма1 - Склад1 - Товар1
Фирма1 - Склад1 - Товар2
Фирма1 - Склад1 - Товар3
...
Фирма1 - Склад1 - ТоварN
Фирма1 - Склад2 - Товар1
Фирма1 - Склад2 - Товар2
Фирма1 - Склад2 - Товар3
...
Фирма1 - Склад2 - ТоварN
...

поведение ms-sql сервера будет таким:
Оптимизатор видит, что индекс такой Фирма + Склад + Товар, а условие выборки такое Фирма + Склад и строит план, скорее всего, Index Seek. Т.е. наше условие накладывается на индекс полностью. В служебных табличках сервер хранит плотность/распределенность (Statistics) данных в индексе и соответственно знает с какого места(!) нужно начинать перебор индекса (тут, вообще-то, помогает структура хранения индекса - B-tree и IAM). Мы получаем данные просто пройдя индекс и, взяв данные из самого индекса (для случая некластерного индекса).

Как мы знаем, в некластерном индексе организована определенная структура хранения. На листьях (leaf nodes) дерева хранятся данные индекса (т.е. это данные тех полей, которые указаны в индексе, в соответствии со способом сортировки), если же этих данных для выборки недостаточно, то серверу нужно будет спуститься по логической структуре дерева до самих данных (data leaf), которые храняться в куче (heap) через идентификатор (row locator). И если с B-tree все ясно, там поиск нужных значений производится крайне быстро, то в куче мы попадаем в "кучу". Т.е. тут нужно проводить тупой перебор (для начала пройтись по дереву по индексу, а чего нехватает - перебором кучи) - это очень злые Закладки (Bookmarks), довольно-таки тяжелая операция.

А вот теперь меняем изначальные условия:
- набор данных тот же;
- индекс Товар + Склад + Фирма; <<<<-- индекс поменяли
- условие отбора Фирма1 + Склад1 + Товар1

Вот тут на сцену выплывает такое понятие как 'селективность' (selectivity). Вроде термин простой, но объяснить не просто
Например, если в таблице 100 строк и все они уникальны (или почти все), то селективность данных высокая. И наоборот, если  таблице 100 строк и почти все они одинаковые, то селективнолсть низкая (или в виде формулы: X = КоличествоСтрокУдовлетворяющихУсловиюОтбора / КоличествоСтрокВообще, где X ->> 0 - ВысокаяСелективность ).

Теперь коротко о случае, если бы для каждого поля был бы свой индекс:
IX_I(Фирма)
IX_II(Склад)
IX_III(Товар)
многие делают именно такие индексы, и считают, что этого достаточно - бог им судья Есть мнение, что от таких индексов оптимизатору сносит мозг. Он отдельно отбирает по Фирме, отдельно по Складу, отдельно по Товару строки соответствующие критерию отбора, а потом делает сливание этих данных. Для условия Фирма1 + Склад1 + Товар1 и начального набора данных получаем скорее всего такую картину (... не считаем):
1. отбор по Фирма1 = 8 строк;
2. отбор по Склад1 = 4 строки;
3. отбор по Товар1 = 2 строки;
4. а теперь по внутренним индентификаторам сделаем сливание и выбор общих (merge)

А может даже, что будет Scan, не ясно что хужее.

ps: сойдет для пояснения??

JohnyDeath писал(а) 28. Ноября 2008 :: 20:53:

Если есть индекс Товар + Склад + Фирма и необходимо получить данные по по конкретному складу определенной фирмы, то в индекс мы не попадаем. Первым полем у нас идет Товар, а условия накладываются по складу и фирме, следовательно, чтобы серверу получить то, что ты просишь, ему нужно будет просканировать индекс.
Но в примере был акцент на том, что в условиях отбора появился товар. Т.е. нужны остатки по конкретной фирме, конкретному складу и конкретному товару. Лично мне кажется, что в oltp-базе, в которой постоянно проводят расходные накладные, перемещения и прочие документы, которым для проведения нужно получить итоги как раз по такому условию (фирма+склад+товар), будет самым лучшим. В смысле, получение итогов более критичная к скорости, чем просто отчет по остаткам.
Ну и еще есть такое правило для выбора расстановки полей в индексе:
СелективностьПоПолю1 > СелективностьПоПолю2 > СелективностьПоПолю3 > ... > СелективностьПоПолюN

SELECT
    COUNT(*) as ОбщееКоличествоСтрок,
    COUNT(DISTINCT field1) as КоличествоУникальныхСтрокДляПоля1,
    COUNT(DISTINCT field2) as КоличествоУникальныхСтрокДляПоля2
FROM table
 

я конечно не разбираюсь сам так хорошо в ms sql
но думаю так
Без индексов плохо.
Когда индексов много и они "протеворечивы" оптимизатору запросов крышу сносит sql бы уже все простым scanом сделал
а оптимизатор все думает какой индекс все таки для этого
надо использовать ( при этом переберая горы статистики)

сам по себе индекс никому не нужен. индекс должен решать
конкретную Задачу ( главную)
Вон у меня главная задача толучение итогов на ТА а
у kiruha главная задача получение остатков на любую
дату.
и глядя на один и тот же регистр и его индексы я говорю все классно все работает и индексы хорошие.
kiruha - все очень медленно  не могу я так вычислять
остаток бутылки на 15 число.
Кто из нас прав. Да оба мы правы только каждый по своему.
Исходя из этого spock все твои высказывания я плохо понимаю
ну во первых не хватает квалификации во вторых не знаю какую главную задачу ты решаешь.

kiruha - я для твоей постановки задачи придумал индекс
я конечно не очень разбираюсь ну по моему должно все очень быстро работать. В понедельник расскажу.