Индексы(и алгоритм) для сложных соединений с фильтрацией

@Jack Famous · Регистрация: 10.05.2021

Студворк — интернет-сервис помощи студентам

Приветствую!
Продолжаю разбираться с правилами построения индексов…

Есть таблица смет С и ресурсов к ним Р (в одной смете один и более ресурсов). Связаны по ключу сметы Ск.

Таблицу смет нужно отфильтровать по некоторым полям, в том числе, соединяя с другими таблицами.
Таблицу ресурсов нужно также отфильтровать по некоторым полям, в том числе, соединяя с другими таблицами, включая Р(Ск) — то есть, учесть фильтр по сметам.

Проблема №1 состоит в том, что, если делать всё "в лоб", то получается каша — сметы-то соединяются с другими таблицами по одному ключу, а вот ресурсы используют Рк ключ ресурса — для связи с другими таблицами, и то Р(Ск) ключ сметы — для связи со сметой. К тому же, ещё нужен такой индекс, который бы и фильтры учёл для обоих таблиц.
Я не могу понять, как правильно в таком случае "разрулить" логику. Что делать сначала, что потом, какие индексы строить — для достижения максимальной скорости.

Проблема №2 в том, что фильтры(переменные) для обоих смет могут переданы все, часть или ни одного.
То есть, как будто, для каждой комбинации фильтров нужно построить некластерный индекс.
Даже, отсеивая те комбинации, когда используются только первые (а не все) ключи индекса (он подходит), всё - равно получается очень много и это ерунда.
Я придумал имитировать фильтр, если он не передан, то есть использовать минимальное количество некластерных индексов, "фильтруя" ключи в них типа Where Field > 0 (для Int полей), чтобы можно было использовать ключи индекса без разрывов.
Вроде, это нормально получается. Интересно ваше мнение.

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

Сейчас делаю через временные таблицы(для смет и ресурсов), но на них время построения индексов уже включается во время работы и портит его.

Например, при простом запросе (отфильтровать каждую таблицу по одному разному полю и соединить между собой) я вижу, что сначала используются индексы для отбора каждой из таблиц (без поля соединения между таблицами) и потом таблицы джойнятся между собой.

@Jack Famous · 13.02.2024, 10:15 **[ТС]**

Сообщение от uaggster

количество индексов можно увеличивать до тех пор, пока это не начнет мешать [работе].

Сообщение от katamoto

создать индексы на PK/FK, включить на базе query store и после накопления статистики добавить индексы под самые тормозные запросы

Смотрите: в таблице ресурсов 20 столбцов, 10 из которых часто участвуют в отборе (включая Primary Key) и 10 — заметно реже. При этом 6 столбцов из частых (без Primary) и 4 из редких участвуют в группировке (нужен отдельный индекс под это). Отбор может осуществляться, как по всем 10 полям, так и по одному их них и в любой комбинации.
Какие индексы я должен построить на это всё добро?
Если я делаю индекс (для упрощения считаем все поля Int от 1 без Null) f1, f2, f3, f4 … f10, то он будет неэффективен для ВСЕХ фильтров без f1.
Ок, Можно подменить фильтр по f1 условием f1 > 0. И тогда будет один бесполезный, но не очень долгий прогон, зато прочие фильтры хорошо отработают. Можно также подменять и другие фильтры, когда их нет. Но, если фильтров 2-3 и это поля в хвосте индекса, то пользы от него уже не будет, а только вред и в сравнении с индексом чётко под поля фильтра скорость будет земля и небо. Наверное, вообще без индекса будет быстрее в этом случае, но, т.к. псевдофильтры стоят, то индекс подхватится и будет делать бесполезную работу.

Ок, пусть будет индекс под каждое из основных полей, но 1ым в нём будет идти Primary Key таблицы, вторым — уже само очередное поле, в Include ничего не пишем, т.к. возвращаться будет только Primary Key, и в Where индекса также не пишем ничего.
Это позволит ступенчато отфильтровывать таблицу, где каждый прогон со 2го фильтрует сначала по отобранным ранее Primary Key и потом уже по самому полю. Порядок отбора (выбор очерёдности полей) должен быть таким, чтобы оставалось минимальное количество ключей. Например, я знаю что, если передан фильтр по сметам (их 20 000), то первым я буду применять его, а фильтр по типу ресурса (всего их 4) я буду применять в конце (или перед фильтром по булевым полям, если такие фильтры есть).
Можно сказать, что нужно выбирать более селективные поля, но, могут быть исключения.
1ый прогон фильтрует только по полю (без Primary Key).

[примерно] Этот вариант я вчера и имел в виду, но он тоже не самый удобный, как мне кажется.
Да — он универсальный. Количество индексов в таблице примерно можно представить и их будет немного (намного меньше, чем индексов комбинаций полей). Добавим индекс(-ы) для группировки и всё. Да, проговорю в который раз, это будет медленнее, чем индекс "под задачу", но, при правильном использовании очерёдности применения фильтров, должно быть достаточно быстро.

Пока я склоняюсь к чему-то среднему: иметь 3-5 индексов с 3-4мя полями + Primary Key. Один из таких индексов будет опорным — в нём будут расположены самые селективные поля из часто встречающихся. Остальные индексы помогут дофильтровать то, что осталось после первого.

P.S.: принудительно индекс навязать умею, но не применяю (только для тестов), актуальный план запроса анализирую, рекомендации плана принимаю к сведению (не бездумно).

@uaggster · 13.02.2024, 11:17

В рассуждениях пропущено понятие "селективность", а именно - насколько разнообразны значения в твоих полях.
Если, например, в каких то полях разнообразие значений не очень велико, типа 0..1, то строить индекс для этих полей особенного смысла не имеет (ну, если только нулей не меньше на пару порядков чем 1. Тогда фильтрованный индекс можно построить, для 0).
Потому что при поиске индекс, скорее всего, использован не будет, а будет использовано именно сканирование PK.

Сообщение от Jack Famous

Какие индексы я должен построить на это всё добро?

Я же говорил. Можно подготовить скрипт, содержащий 100500 типовых запросов к базе (чем больше, тем лучше), и прогнать его через дейта тюннинг адвизор.
Можно снести все индексы, кроме очевидных, и заставит пользователей поработать с БД, записав трейс.
И потом этот трейс, опять же, скормить дейта тюннинг адвизору. И посмотреть, что он посоветует.

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

@Jack Famous · 13.02.2024, 12:43 **[ТС]**

Сообщение от uaggster

В рассуждениях пропущено понятие "селективность"

Сообщение от Jack Famous

Можно сказать, что нужно выбирать более селективные поля, но, могут быть исключения.

Спасибо за варианты.

Сообщение от uaggster

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

поживём-увидим

Сообщение от uaggster

Объемы мизерные, задача типовая, количество индексов - какие-то два десятка.

на таблице уже есть 2 десятка индексов и они занимают в 15 раз больше места, чем сама таблица (15 Гб против 1го).
А список комбинаций далеко не полный. В общем, посмотрим…

@uaggster · 13.02.2024, 12:51

Сообщение от Jack Famous

на таблице уже есть 2 десятка индексов и они занимают в 15 раз больше места, чем сама таблица (15 Гб против 1го).
А список комбинаций далеко не полный. В общем, посмотрим…

А ты их используемость проверь.
Некоторые, возможно, необходимо удалить.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
declare @dbid int
select @dbid = db_id()
select (cast((user_seeks + user_scans + user_lookups) as float) / case user_updates when 0 then 1.0 else cast(user_updates as float) end) * 100 as [%]
    , (user_seeks + user_scans + user_lookups) AS total_usage
    , objectname=object_name(s.object_id), s.object_id
    , indexname=i.name, i.index_id
    , user_seeks, user_scans, user_lookups, user_updates
    , last_user_seek, last_user_scan, last_user_update
    , last_system_seek, last_system_scan, last_system_update
    , 'DROP INDEX ' + i.name + ' ON ' + object_name(s.object_id) as [Command], i.*
from sys.dm_db_index_usage_stats s,
    sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
AND i.name IS NOT NULL
AND i.is_primary_key = 0        --исключаем Primary Key
AND i.is_unique_constraint = 0  --исключаем Constraints
--and object_name(s.object_id) = 'MyBigTable'
order by [%] asc

Ну, опять же, нужно быть внимательным. Т.к. индекс, который не использовался (с момента запуска сервера, запрос анализирует статистику по индексам с момента старта сервера), возможно, используется в редких, но метких случаях.

@Jack Famous · 13.02.2024, 13:09 **[ТС]**

uaggster, спасибо. Да — есть уже у нас запрос для анализа и даже отдельный человек для этого (не я). Но, пока индексы находятся в постоянном изменении, толку от такого анализа мало. Самые очевидные "якори" уже удалили.

@invm · 13.02.2024, 18:55

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

Пример для размышления - а почему не залетало

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
drop table if exists dbo.t1, dbo.t2;
go
 
create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, t1_id int not null, s char(200));
 
insert into dbo.t1 (v)
select top (20000)
    1
from master.dbo.spt_values a
cross join master.dbo.spt_values b;
 
insert into dbo.t2 (t1_id, s)
select
    t1.id, tmp.s
from dbo.t1 as t1
cross apply
(
    select top (10 + cast(rand(checksum(newid(), t1.id)) * 100 as int))
        's'
    from master.dbo.spt_values
) as tmp (s);
 
create index IX_t2 on dbo.t2 (t1_id);
go
 
declare @v int, @s varchar(200);
 
set statistics time, io, xml on;
 
select
    @v = a.v, @s = b.s
from dbo.t1 as a
inner join dbo.t2 as b on b.t1_id = a.id
option (maxdop 1);
 
select
    @v = a.v, @s = b.s
from dbo.t1 as a
inner join dbo.t2 as b with (forceseek) on b.t1_id = a.id
option (maxdop 1);
 
set statistics time, io, xml off;

@Jack Famous · 14.02.2024, 09:29 **[ТС]**

invm, спасибо! Хороший пример!
Подобные указания [пока] не использую. План запроса показывает, что пользователь сошёл с ума

Единственное, что могу сказать по этому поводу это то, что при соединении таблиц, в которой один индекс по PK(Clust), а в другой по NonClust — это то, что для Merge нужно будет выполнить сортировку (из-за Clust) и нередко видел, что это по времени дольше, чем соединять по 2ум NonClust.

Сообщение от invm

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

ну — я так не делал точно) да — когда я занялся БД, я тут увидел гору автоиндексов, но это не моя вина и не моя ответственность (этим другой занимается). Свои индексы я строю чётко под задачу, со всеми нюансами и, по возможности, "схлопываю" несколько индексов в один со временем, если логика это позволяет.

Новые блоги и статьи Все статьи Все блоги /
сукцессия 30. Фосфорный и азотный замки по "честному" закону лимитирования Либиха anaschu 16.07.2026 Основные достижения за последние дни 1. Механизм отложенного высвобождения фосфора Добавлен специальный пул для медленного разложения меланизированных гиф ЕСМ Реализована разная скорость. . .	Запрет пометки на удаление и отмены проведения Maks 16.07.2026 Алгоритм из решения ниже реализован на нетиповом документе "ПутевойЛист" разработанного в КА2. Задача: запретить помечать на удаление и отменять проведения путевого листа при условии, если он сдан. . .	Как надо было делать правильно - вообще не слушать ИИ, его дело - текст. Hrethgir 15.07.2026 А вообще надо было делать не так, чтобы не исправлять ошибку https:/ / www. cyberforum. ru/ blogs/ 223907/ 10996. html , правильно было : удалил кнопку, а потом добавил её. Всё это стресс конечно. Почему?. . .	Знаете, от чего появляется стресс? kumehtar 15.07.2026 Стресс появляется оттого, что вы, зная как поступить правильно, делаете наоборот. Ларри Уингет
Нас бьют, а мы крепчаем? Какая чушь! kumehtar 15.07.2026 Вот это вот бытующее мнение, что человек меняется только под действием проблем, всегда считал вредоносным. Поверь, от проблем у людей появляются не изменения, а нервное расстройство и ПТСР. А. . .	сукцессия 29. Переход от одних деревьев на другие делать более или менее вероятностным? anaschu 12.07.2026 Насколько смена типов микоризы — исключительное событие в двухвековой сукцессии? Оценка вероятности в пространстве параметров В текущей версии модели успешно реализован ключевой механизм. . .	сукцессия 27. Думаю, как переделывать уже написанную статью с планами на сукцессию. anaschu 12.07.2026 Анализ соответствия модели требованиям Реализованные компоненты: Механизм закисления почвы через протонную помпу Конкуренция между типами микориз pH как триггер сукцессии C/ P соотношение. . .	Сукцессия 26. Мат модель создана. anaschu 12.07.2026 Модель смены растительных сукцессий посредством управления грибами работает внутри небольшой ячейки почвы, восстанавливающейся после пожара, где ненадолго бывшее царство хвойных снова захватили. . .

@Jack Famous 933 / 366 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10

	Индексы(и алгоритм) для сложных соединений с фильтрацией 09.02.2024, 12:33. Показов 2024. Ответов 26 Метки filter, index, join (Все метки) Приветствую! Продолжаю разбираться с правилами построения индексов… Есть таблица смет С и ресурсов к ним Р (в одной смете один и более ресурсов). Связаны по ключу сметы Ск. Таблицу смет нужно отфильтровать по некоторым полям, в том числе, соединяя с другими таблицами. Таблицу ресурсов нужно также отфильтровать по некоторым полям, в том числе, соединяя с другими таблицами, включая Р(Ск) — то есть, учесть фильтр по сметам. Проблема №1 состоит в том, что, если делать всё "в лоб", то получается каша — сметы-то соединяются с другими таблицами по одному ключу, а вот ресурсы используют Рк ключ ресурса — для связи с другими таблицами, и то Р(Ск) ключ сметы — для связи со сметой. К тому же, ещё нужен такой индекс, который бы и фильтры учёл для обоих таблиц. Я не могу понять, как правильно в таком случае "разрулить" логику. Что делать сначала, что потом, какие индексы строить — для достижения максимальной скорости. Проблема №2 в том, что фильтры(переменные) для обоих смет могут переданы все, часть или ни одного. То есть, как будто, для каждой комбинации фильтров нужно построить некластерный индекс. Даже, отсеивая те комбинации, когда используются только первые (а не все) ключи индекса (он подходит), всё - равно получается очень много и это ерунда. Я придумал имитировать фильтр, если он не передан, то есть использовать минимальное количество некластерных индексов, "фильтруя" ключи в них типа Where Field > 0 (для Int полей), чтобы можно было использовать ключи индекса без разрывов. Вроде, это нормально получается. Интересно ваше мнение. По каждой таблице я могу построить кластерный индекс под эту задачу. Время построения индексов на этих и связанных с ними таблицах, не учитывается во времени работы. Делается один раз. Сейчас делаю через временные таблицы(для смет и ресурсов), но на них время построения индексов уже включается во время работы и портит его. Например, при простом запросе (отфильтровать каждую таблицу по одному разному полю и соединить между собой) я вижу, что сначала используются индексы для отбора каждой из таблиц (без поля соединения между таблицами) и потом таблицы джойнятся между собой. 0

@Jack Famous 933 / 366 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	13.02.2024, 13:09 [ТС]
	uaggster, спасибо. Да — есть уже у нас запрос для анализа и даже отдельный человек для этого (не я). Но, пока индексы находятся в постоянном изменении, толку от такого анализа мало. Самые очевидные "якори" уже удалили. 0