04.10.2022 00:16
1CUnlimited 17 1400 Источник

Кто он – селективный индекс

В предыдущей статье Партицированная дисциплина программиста в 1С был показан пример запроса на соединение двух таблиц для регистра сведений, и показано как MS SQL выбирает потоки данных для merge join с использованием стандартных  индексов 1С .  В частности было отмечено, что без дополнительных условий в Index seek, в поток для Megre join попадают все записи индекса и приходится указывать дополнительные фильтры для ограничения. Вопрос: почему так происходит? - остался открытым.

Все что описано ниже, это мои выводы на основании анализа поведения оптимизатора MS SQL 2019  в разных условиях. Официально изложенных алгоритмов, которые использует оптимизатор MS SQL я не нашел, если знаете ссылку пишите в комментариях.

Сначала вспомним понятие селективного индекса.

Если сказать кратко – индекс для данного запроса является селективным , если при его использовании можно выбрать

  • Больше уникальных строк

  • С меньшим количеством дублей

  • Наименьшее количество строк на каждую комбинацию ключевых значений

Про селективность хорошо написано тут (правда для Oracle, но это же общая концепция)

Селективный индекс

Возьмем оптимизированный запрос из предыдущей статьи

ВЫБРАТЬ РАЗЛИЧНЫЕ
	СУУ_АгрегированныеДенежныеТранзакции.СвязаннаяОпИдИсхСистемы КАК СвязаннаяОпИдИсхСистемы
ПОМЕСТИТЬ Врем_ИдОперацийИзТранзакций
ИЗ
	РегистрСведений.СУУ_АгрегированныеДенежныеТранзакции КАК СУУ_АгрегированныеДенежныеТранзакции
ГДЕ
	СУУ_АгрегированныеДенежныеТранзакции.Период >= &ДатаНачала

ИНДЕКСИРОВАТЬ ПО
	СвязаннаяОпИдИсхСистемы
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	СУУ_АгрегированнаяСделкаКП.Период,
	СУУ_АгрегированнаяСделкаКП.ИсходнаяСистема,
	СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы КАК ИдИсхСистемы,
	СУУ_АгрегированнаяСделкаКП.ОсновнойСчет,
	СУУ_АгрегированнаяСделкаКП.НогаСделки
ПОМЕСТИТЬ РезультатВыбранныеВерсииСделок
ИЗ
	РегистрСведений.СУУ_АгрегированнаяСделкаКП КАК СУУ_АгрегированнаяСделкаКП
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Врем_ИдОперацийИзТранзакций КАК Врем_ИдОперацийИзТранзакций
		ПО СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы = Врем_ИдОперацийИзТранзакций.СвязаннаяОпИдИсхСистемы
ГДЕ
	СУУ_АгрегированнаяСделкаКП.Период >= ДОБАВИТЬКДАТЕ(&ДатаНачала, МЕСЯЦ, -3)

//И опять его запустим со стандартными индексами 1С. MS SQL нас интересует последний запрос


INSERT INTO #tt3 WITH(TABLOCK) (_Q_001_F_000, _Q_001_F_001RRef, _Q_001_F_002, _Q_001_F_003RRef, _Q_001_F_004RRef) SELECT
T1._Period,
T1._Fld18861RRef,
T1._Fld18865,
T1._Fld18863RRef,
T1._Fld19363RRef
FROM dbo._InfoRg18860 T1 WITH(NOLOCK)
INNER JOIN #tt2 T2 WITH(NOLOCK)
ON (T1._Fld18865 = T2._Q_000_F_000)
WHERE ((T1._Fld628 = @P1)) AND ((T1._Period >= @P2))',N'@P1 numeric(10),@P2 datetime2(3)

Смотрим общую цену, которую зафиксировал оптимизатор -  в попугаях 7767

План получается с Index Seek по типовому индексу _InfoR18860_ByDims18897_STRRRR

Структура индекса

План запроса ниже, видно что основная тяжесть ввода вывода идет на Index Seek и операции Merge

Вроде все хорошо, по правилам и предсказуемо, но давайте добавим ему другой индекс в котором, убрано поле _Fld628 . Это поле содержит 0 поскольку в типовой конфигурации есть, но не используются

Смотрим результат. Неожиданно – SQL сервер выбрал новый индекс сам , даже при том что _Fld628 (разделителя) там вообще нет! Хотя есть индекс _InfoR18860_ByDims18897_STRRRR который формально удовлетворяет всем условиям.

План при этом получился лучше, но не намного

Разница

1)      По стандартному индексу идет  |--Index Seek(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), SEEK:([T1].[_Fld628]=[@P1]),  WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>=[@P2]) ORDERED FORWARD)

2)      По нестандартному индексу идет скан с проверкой всех условий |--Index Scan(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_MySuperWithout_Fld628_ByDims18897_STRRRR] AS [T1]),  WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Fld628] as [T1].[_Fld628]=[@P1] AND [MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>=[@P2]) ORDERED FORWARD)

Мы выбираем селективный индекс, а оптимизатор выбирает …

Почему MS SQL так сделал? Скорее всего поле, где _Fld628 = 0 в каждой записи, убивает всю селективность индекса и как только появляется достойная альтернатива и MS SQL сам бежит к ней

Вопрос А в какое место индекса, тогда поставить это поле _Fld628 (ОбластьДанныхОсновныеДанные) , которое 1С по умолчанию ставит в начало (префикс) всех индексов?

Это сложный вопрос. Если ОбластьДанныхОсновныеДанные используется хотябы с несколькими значениями, селективность повысится в стандартном индексе, учитывая условия на равенства которые добавляет 1С  T1._Fld628 = @P1 (равенство всегда в приоритете у оптимизатора нежели >= <=)

Но все очень зависит от СУБД . Например, тут описаны мифы о селективных индексах причем с планами для разных СУБД

Use index luke

“The myth is extraordinarily persistent in the SQL Server environment and appears even in the official documentation. The reason is that SQL Server keeps a histogram for the first index column only. But that means that the recommendation should read like “uneven distributed columns first” because histograms are not very useful for evenly distributed columns anyway.”

Т.е. первая колонка в индексе решает все и ее количество уникальных значений. Если там один 0 работа с остальными полями идет уже менее эффективно. Если 0 1 2, то это тоже сильно ситуацию не исправляет, поскольку для Merge без доп условий пойдет поток данных по всему T1._Fld628 = @P1

В целом лучше жить без поля ОбластьДанныхОсновныеДанные , чем с ним, но если без него нельзя то улучшить ситуацию можно только альтернативным построением запроса самой платформы, а это уже другая  история для следующих статей.  Буду рад видеть Вас на нашем канале  ???? t.me/Chat1CUnlimited

Комментарии (17)


  1. frrrost
    04.10.2022 00:53
    #24786352
    +2

    Что-то мне кажется, разница во времени объясняется банальным кэшированием - после первого запроса создания нового индекса его данные остались в оперативке. Число строк, выбираемых из обоих индексов одинаковое, а сами индексы по объёму почти не отличаются - чудес быть не должно. Можно дополнительно проверить, включив set statistics io on - число чтений скорее всего в обоих прогонах будет одинаковым.

    Но вопрос, конечно, был в другом. И выбор второго индекса для меня, если честно, неожиданный - по всем формальным признакам он должен постоять "в стороне".

    Подробнее про оптимизатор можно почитать у Дмитрия Пилюгина http://www.queryprocessor.ru/optimizer_unleashed_1/ . Он очень глубоко разбирается в механике работы QP. Какие-то знания, наверняка устарели, но в статье по ссылке - подробный разбор этапов формирования плана. Если включить специальные флаги трассировки (у него всё указано), можно посмотреть, какие варианты оцениваются и понять, почему "побеждает" итоговый план.


    1. 1CUnlimited Автор
      04.10.2022 23:39
      #24788994

      Я перед каждым прогоном делал

      DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); 

      DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

      DBCC FREEPROCCACHE ;

      DBCC DROPCLEANBUFFERS

      За ссылку спасибо почитаю

      В целом как я понял оптимизатор ориентируется на цену плана, и при сравнении важно не столько время исполнения запроса (оно может отличаться на разных машинах) , а показатели IO в плане и CPU . Из плана видно что "нетрадиционный" индекс побеждает по цене плана . Могу приложить текстовые файлы из профайлера если хочется оценить подробнее.

      Возможно просто MS SQL использует особые алгоритмы обхода дерева индекса

      The Balanced Search Tree (B-Tree) in SQL Databases (use-the-index-luke.com)


      1. unfilled
        05.10.2022 06:29
        #24789306

        Покажите, пожалуйста, вывод set statistics io on для обоих запросов.

        Кажется, что убрав одно поле из индекса, вы выиграли мегабайт 350 на 70 млн записей - этим не может объясняться разница в скорости выполнения


        1. 1CUnlimited Автор
          05.10.2022 20:02
          #24791644
          +1

          В первой картинке обычный индекс 1С с Fld_628 и полем номеров

          Во второй картике индекс без Fld_628 разница в количестве чтений


          1. unfilled
            05.10.2022 20:20
            #24791674

            А каков процент фрагментации "родного" 1Совского индекса и средняя заполненность страницы? Не могу понять чем может быть вызвана разница в 2 миллиона чтений для индексов, единственное различие между которыми - это 5 байтовая константа в ключе.

            В вашем предыдущем посте данные из родного индекса возвращались также быстро, как из нового индекса в этом, даже когда на выходе были все 210 миллионов записей.


            1. 1CUnlimited Автор
              05.10.2022 21:06
              #24791768

              На Duration нет смысла смотреть, так как файловые группы на тестовой системе в HDD и там могут другие пользователи работать, как следствие на таком запросе время может плавать. Фрагментация родного индекса прилагается.


              1. unfilled
                05.10.2022 21:33
                #24791812

                Предполагаю, что если вы сделаете ребилд индекса, количество чтений практически сравняется. Разница должна быть около 150 тысяч - 5 байт * 210 млн строк / 8192 (размер страницы) - столько сэкономлено за счёт описанных изменений ключа индекса.


                1. 1CUnlimited Автор
                  05.10.2022 21:39
                  #24791840

                  Попробую, но это не объяснит - почему оптимизатор выбирает нетрадиционный индекс. Врядли он смотрит на фрагментацию это не быстро


                  1. unfilled
                    05.10.2022 21:46
                    #24791854

                    Новый индекс уже существующего, читать меньше. Условие на равенство бессмысленно, он это знает из статистики. Поле есть в индексе - видимо оно в составе ключа кластерного индекса (или вы включили его в included).

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

                    Проверьте, кстати, fill factor у обоих индексов, если он ниже у родного индекса - это тоже будет влияние на разницу в чтении.


                    1. 1CUnlimited Автор
                      06.10.2022 23:41
                      #24795074

                      Да Ваше предположение верно, переиндексация "_InfoR18860_ByDims18897_STRRRR" вернула предпочтения MS SQL обратно на селективный индекс. Количество чтений в нем гораздо меньше стало

                      Но если сделать эмуляцию запроса 1С и принудительно задать хинт

                      План исполнения нетрадиционного индекса всеравно лучше чем у традиционного 5972 против 6710

                      SELECT 
T1._Period,
T1._Fld18861RRef,
T1._Fld18865,
T1._Fld18863RRef,
T1._Fld19363RRef
 INTO #tt_RESULT
FROM dbo._InfoRg18860 T1 WITH(NOLOCK,INDEX=_InfoR18860_MySuperWithout_Fld628_ByDims18897_STRRRR)
INNER JOIN #tt_alternative T2 WITH(NOLOCK)
ON  (T1._Fld628 =0 ) AND (T1._Fld18865 = T2._Q_000_F_000) 
WHERE T1._Period>= DATEADD(month, -3,@BeginPer);


  1. unfilled
    04.10.2022 10:00
    #24786872
    +1

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

    Разница в скорости выполнения тоже кажется вызванной банальным кешированием.


  1. unfilled
    04.10.2022 10:17
    #24786920
    +1

    В частности было отмечено, что без дополнительных условий в Index seek, в поток для Megre join попадают все записи индекса и приходится указывать дополнительные фильтры для ограничения. Вопрос: почему так происходит? - остался открытым.

    Пропустил тот пост, сейчас прочитал - и так происходит потому что так работает merge join. Помогите ему, вместо фильтра по дате фильтр по ИдИсхСистемы >= (выбрать мин(СвязаннаяОпИдИсхСистемы) из Врем_...), если 1С так может (не помню уже). С вашим новым индексом, да и со старым тоже - это должно работать не хуже фильтра по дате


    1. 1CUnlimited Автор
      04.10.2022 23:48
      #24789014

      Я так пробовал, с фильтром ограничения по номеру он работает но примерно также как и ограничением по дате, план могу приложить. Я использовал фильтр по дате чтобы продемонстрировать что MS SQL склонен лезть в нижние ветки индекса. Казалось бы при Join у него уже должна быть информация о макс мин значениях которые он соединяет, и он может взять это и из статистики и из индекса. Но на практике пока не укажешь ограничения с низу - будет сканироваться весь индекс


      1. unfilled
        05.10.2022 06:23
        #24789304

        Что вы имеете в виду под "нижние ветки индекса"? В любом случае он будет опускаться на листовой уровень, т.к. только оттуда можно получить данные, которые нужно вывести.


        1. 1CUnlimited Автор
          05.10.2022 16:43
          #24791078

          Я имею ввиду b-tree traversal как описано тут The Balanced Search Tree (B-Tree) in SQL Databases (use-the-index-luke.com)


  1. nikweter
    04.10.2022 17:34
    #24788168

    Вроде МС ушли из РФ, проблематично купить. Может стоит рассматривать альтернативный продукты?


    1. 1CUnlimited Автор
      04.10.2022 23:53
      #24789026

      В реляционных СУБД везде все похоже, поскольку у них одинаковая база computer science (реляционная алгебра). В начале 2000х я сертифицировался как Oracle dba, мне этих знаний до сих пор хватает чтобы понимать MS SQL . Когда идешь в более "маленькие" СУБД типа MySQL понимаешь что там все похоже. Просто задаю себе вопрос - а вот у СУБД Х есть такое как у Oracle? И уже знаешь, где искать в документации СУБД Х ответ. Хочется наоборот не грузится как работает оптимизатор у конкретной СУБД, использовать общие практики (типа селективного индекса) но не получается :)