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

@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

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

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

Новые блоги и статьи Все статьи Все блоги /
Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Jin X 06.09.2026 Скрипты Tampermonkey для CyberForum, ChatGPT, Claude и пр. Работая с форумом и нейросетями в браузере часто хочется что-то подкорректировать или добавить какого-то функционала. Ниже прикреплён. . .	Программа опроса у.з. расходомера SLS-720F Argus19 02.09.2026 Программа опроса у. з. расходомера SLS-720F Программа опрашивает один раз в минуту три ультразвуковых расходомера SLS-720F через интерфейс RS-485 по протоколу Modbus RTU. Опрашиваются регистры. . .	Hyper-V: Компьютер должен поддерживать доверенный платформенный модуль 2.0. Maks 31.08.2026 При установке Windows 11 на виртуальную машину Hyper-V 2-го поколения вылезла такая ошибка: Решение: в параметрах виртуальной машины, в разделе "Безопасность" (Security) активировать флаг. . .	Архитектура биовида Стива в Майнкрафте: Зачем бонобо кубический каннибализм anaschu 30.08.2026 Кубический Вагинокапитализм в Minecraft: Математический инвариант ОДУ и рок Стивов-бонобо Главная задача разработанной «Модели Всего» — наглядно продемонстрировать наличие системной «судьбы». . .
Оттачиваю умение писать js программы. russiannick 30.08.2026 Проектом выходного дня стало написание Книги шифров Виженера. Итогом стала версия 200, синий туман. Синий туман назван так, потому что замораживает текст под собой. Нажатие синих кнопок управляют. . .	мат медиц модель 30. презентация проекта anaschu 27.08.2026 хоп хоп хоп хидахоп, а я кладую))	Как у меня протекала болезнь zorxor 27.08.2026 Здравствуйте, друзья! Эта запись блога предназначена именно для вас - для моих дорогих друзей, которые знали меня лично. Чтобы ответить на вопрос - а что же со мной произошло на самом деле? Я учился. . .	Нашел вот забавное видео о измерениях. Лучшее что я видел на эту тему kumehtar 26.08.2026 ILETXiw9bMQ Основная суть и тезисы по измерениям: 0D (Нулевое измерение): точка, не имеющая длины, ширины, высоты или объема. Объект не может перемещаться в 0D. 1D (Первое измерение):. . .

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

	Индексы(и алгоритм) для сложных соединений с фильтрацией 09.02.2024, 12:33. Показов 2082. Ответов 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