Оптимизатор запросов. Вторая часть

23.01.20

База данных - HighLoad оптимизация

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

Оптимизатор запросов. Часть вторая.     Оптимизатор. Часть первая

Автор: Искаков Данис. Рецензент: Стрекаловский Иван.

Выражаю особую благодарность Ивану Стрекаловскому за помощь и терпение. Я очень надеюсь, что наше сотрудничество продолжится. Я понимаю, что это отнимает немало времени и нужно не просто читать, а, буквально, перелопачивать материал. И все же, я рад и благодарен вам за вашу поддержку. Эта статья – наш совместный труд.

Конечно, тема оптимизатора – это одновременно и просто, и сложно. Понять его работу, в принципе, не так сложно. Многие это умеют (или думают, что умеют). Но стоит спросить: «А почему выбрана такая стратегия?» - То ответом может стать ступор даже у бывалых зубров. Почему так происходит? - Дело в том, что сам выбор стратегии – это область вероятностного программирования. Ключевое слово – «вероятность». Оптимизатор выбирает ВЕРОЯТНОСТЬ лучшего решения. Представьте, если вас спросят: «Кто выиграет чемпионат мира по футболу?» и дадут информацию о командах. Оптимизатор бы ответил, примерно так: Команда «Торпедо» может победить с ВЕРОЯТНОСТЬЮ, допустим, 70%, Команда «Спартак» - с ВЕРОЯТНОСТЬЮ 20% (ничего не имею против команды Спартак – это просто пример) и т.д. Обратите внимание на то, что ВЕРОЯТНОСТЬ – это не УВЕРЕННОСТЬ, а предположение. А у нашего, горячо любимого, оптимизатора, помимо предположений, есть еще полное незнание структуры базы данных, с которыми он работает. Все данные об используемых данных, оптимизатор получает на входе, а на выходе выдает предполагаемый план запроса. Если бы оптимизатора не было, то это не значит, что мы не могли бы делать выборки. Мы могли бы это делать, только эффективность была бы потеряна. Чтобы лучше понять, давайте представим, что нам нужно хранить и выбирать данные. Для простоты представим, что мы храним таблицы в файлах данных: один файл данных – это одна таблица. Каждая строка файла – это запись таблицы. Теперь, если нам нужно выбрать строку по определенному условию, что мы сделаем? Скорее всего, переберем все строки файла данных в цикле и когда дойдем до нужной строки, прервем цикл. А если условие содержит несколько значений? Тогда сделаем таблицу, куда будем записывать подходящие строки и, пройдя циклом по строкам файла данных, будем отбрасывать неподходящие по условию строки, а подходящие, запишем в нашу таблицу выборки.

Псевдокод 1с:

ТЗвыборки = Новый ТаблицаЗначений;

Выборка = ФайлДанных1.Выбрать();

Пока Выборка.Следующий() Цикл

     Если Выборка.Код = ПодходитПодУсловие Тогда

         НоваяСтрока = ТЗвыборки.Добавить();

         ЗаполнитьЗначенияСвойств(НоваяСтрока, Выборка);

     КонецЕсли;

КонецЦикла;

Помучившись так несколько раз, мы начнем искать способ улучшить наше приложение. Какие тут есть пути? В СУБД используют файлы индексов. Давайте поступим также. Создаем файл индекса (допустим, по полю «Код»), где у нас записаны упорядоченные значения кодов и номера строк нашего файла данных. Теперь, чтобы найти нужную строку в нашем файле данных с определенным кодом, мы сначала переходим к определенной строке файла индекса. БЕЗ ЦИКЛА! По номеру! А получив строку (в индексном файле), получаем номер строки нашего файла данных. Теперь остается только получить найденную строку в файле данных. Это, конечно, грубый пример, но он позволяет увидеть схему работы.

Псевдокод 1с:

ТЗВыборки = Новый ТаблицаЗначений;

ВыборкаИндекс = ФайлИндекса1.ПолучитьСтроку(НомерКода);

НомерСтрокиФД = ВыборкаИндекс.НомерСтрокиФайлаДанных;

Если НомерСтрокиФД <> Неопределено Тогда

      НоваяСтрока = ТЗвыборки.Добавить();

      СтрокаФайлаДанных = ФайлДанных1.ПолучитьСтроку(НомерСтрокиФД)

      ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаФайлаДанных);  

КонецЕсли;

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

Псевдокод 1с:

Тз1 = Новый ТаблицаЗначений;

Тз2 = Новый ТаблицаЗначений;

Тз3 = Новый ТаблицаЗначений;

ВыборкаИзФайлаДанных1 = ФайлДанных1.Выбрать();

Пока ВыборкаИзФайлаДанных1.Следующий() Цикл

      ВыборкаИзФайлаДанных2 = ФайлДанных2.Выбрать();

      Пока ВыборкаИзФайлаДанных2.Следующий() Цикл

            Если ВыборкаИзФайлаДанных1.Код = ВыборкаИзФайлаДанных2.КодПартнера Тогда 

                  НоваяСтрокаТЗ = Тз3.Добавить();

                  ЗаполнитьЗначенияСвойств(НоваяСтрокаТЗ, ВыборкаИзфайлаДанных2);

             КонецЕсли;

       КонецЦикла;

КонецЦикла;

Обратите внимание. Когда мы написали, что выборка начинается с файла данных номер 1, а затем перебор всех записей файла данных 2, мы загнали себя в угол. А что, если я поставлю отбор на выборку из файла данных 2? Не лучше ли будет, сначала выбрать по отбору, а потом запустить цикл сначала из отобранных записей? Какое тут может быть решение? - Можно написать еще несколько выборок, учитывая различные возможности отбора, и потом, исходя из задачи, выбирать нужную. Давайте попробуем. Допустим, что мы написали кучу выборок на все случаи жизни. У нас их получилось, ну пусть, миллион. К нам начали поступать запросы, и мы как давай перебирать весь свой миллион выборок для поиска подходящего. Веселое занятие? «Очень веселое». Повеселившись так определенное время, приходим к выводу: Надо что-то делать. Хорошенько подумав, принимаем решение. Надо написать программу, которая, исходя из входных параметров, будет выбирать НАИБОЛЕЕ подходящую выборку. Что значит - решили автоматизировать поиск выборки. Как это сделать? Самый примитивный путь – это создать соответствия между запросами с параметрами и выборками. То есть, все входящие параметры нам известны изначально. Доложили шефу радостную новость. Шеф от радости уволил половину. Оставшиеся снова задумались. Приходим к мнению: нужно запросы и входящие параметры представить в виде определенных числовых значений. Тоже самое надо сделать с выборкой. И сопоставляя числовые значения параметров и выборок, выбирать самые большие или самые маленькие (а может и средние). Они то, и будут искомые. Вот и появилась первая версия оптимизатора.

Псевдокод 1с:

ВходящиеПараметрыЧисловоеЗначение = ПолучитьЧисловоеЗначение(ВхПарам);

ТЗчислЗначВыборок = Новый ТаблицаЗначений;

Для Каждого ТекущаяВыборка из Выборки Цикл

      НоваяСтрока = ТЗчислЗначВыборок.Добавить();

      НоваяСтрока.ЧисловоеЗначение = ПолучитьЧисловоеЗначение(ТекушаяВыборка);

      НоваяСтрока.Выборка = ТекущаяВыборка;

КонецЦикла;

ИспользуемаяВыборка = НайтиВыборку(ТЗчислЗначВыборок,ВходящиеПараметрыЧисловоеЗначение); //вот здесь происходит вся магия поиска

Как видите, у нас есть функция НайтиВыборку(ТЗгде ищем, ЧтоИщем). Вот она и должна вернуть нужную нам выборку. Дальше можно улучшать работу с числовыми значениями входящих параметров и выборок, и саму функцию улучшать до бесконечности. Это уже детали. Главное, что нужно знать, так это то, что оптимизатор не является частью БД – это общий механизм выборки наилучшего. Его нужно рассматривать как отдельный модуль, внутри СУБД. И что нам нужно, так это правильно давать входящую информацию. Входящая информация – это все, что мы можем. А дальше мы бессильны. В нашем случае, входящая информация – это наши запросы. Сейчас, я хочу заострить ваше внимание на вышесказанном и прошу понять правильно. Если вы написали один запрос, и он работал плохо, а потом внесли изменения и запрос стал лучше выполняться – это улучшение, это прогресс, это очень хорошо, но это не совершенство и может быть очень далеко от идеала. И то, что мы делаем – это улучшение работы запроса. Я понимаю какой поднимется…, назовем бум, в комментах, когда я скажу простую истину – мы никогда не получим наилучший вариант, сколько бы не улучшали. И тот, кто говорит, что, делая вот так и вот так, мы получаем НАИЛУЧШУЮ выборку – тот лукавит. Мы можем получить лучшее из возможных. Давайте по аналогии с нашим примером, сделаем несколько выборок. Представьте, нам нужно выбрать все данные из файла данных номер 1 и упорядочить по полю «Код». Как вы думаете, нужно здесь использовать индекс? Или лучше пройтись полным сканированием файла данных? – С одной стороны, данные в индексном файле упорядочены, с другой стороны, может выборка из одного файла работает быстрее и стоит не использовать индекс.

Псевдокод 1с:

Делать так:

ТзВыборки = Новый ТаблицаЗначений;

ВыборкаФайлДанных1 = ФайлДанных1.Выбрать();

Пока ВыборкаФайлДанных1.Следующий() Цикл

         НоваяСтрока = ТзВыборки.Добавить();

         ЗаполнитьЗначенияСвойств(НоваяСтрока,ВыборкаФайлДанных1);

КонецЦикла;

ТзВыборки.Сортировать(«Код»);

Или так:

ТзВыборки = Новый ТаблицаЗначений;

ВыборкаИндекса = ФайлИндекса1.Выбрать();

Пока ВыборкаИндекса.Следующий() Цикл

       НоваяСтрока = ТзВыборки.Добавить();

       СтрокаФайлаДанных = ФайлДанных1.ПолучитьСтроку(ВыборкаИндекса.Код);

       ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаФайлаДанных);

КонецЦикла;

Как видите, в первом случае используется сортировка полученной таблицы, а во втором случае – ее нет. Но зато во второй выборке есть постоянные переходы к файлу данных. Какой из этих вариантов лучше? А может есть еще третий вариант, который будет лучше предыдущих? Самое интересное, что мы не можем ОДНОЗНАЧНО ответить на эти вопросы. Нам нужно учитывать сколько записей в файле, вместиться ли вся наша выборка в оперативную память, как работает функция сортировки и много-много еще разных условий. Поэтому для нас важно выбрать НАИБОЛЕЕ подходящую процедуру и использовать ее. Вот еще пример: Допустим, в нашем файле данных номер 1 есть поле с типом «Булево». И мы решили создать индекс по этому полю. Какие тут могут быть варианты? – Ну, я предлагаю сделать два файла индексов, в один из которых мы будем писать номера строк файла данных, соответствующих значению «Истина», а в другой файл индекса - со значением «Ложь». (Помните, это просто пример. Поэтому можем позволить себе все.) Теперь, когда мы будем выбирать данные с отбором по этому полю, мы можем использовать определенный файл индекса. Как вы думаете, это улучшит выборку? Давайте, для начала, взглянем на псевдокод:

Выборка по индексу:

ТзВыборки = Новый ТаблицаЗначений;

ВыборкаИндекса = ФайлИндексаБулевоИстина.Выбрать();

Пока ВыборкаИндекса.Следующий() Цикл

     СтрокаФайлаДанных= ФайлДанных1.ПолучитьСтроку(ВыборкаИндекса.НомерСтроки);

     НоваяСтрока = ТзВыборки.Добавить();

     ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаФайлаДанных);

КонецЦикла;

Выборка без индекса:

ТзВыборки = Новый ТаблицаЗначений;

ВыборкаДанных = ФайлДанных1.Выбрать();

Пока ВыборкаДанных.Следующий() Цикл

    Если ВыборкаДанных.<ПолеБулево> = Ложь Тогда

           Продолжить;

     КонецЕсли;

     НоваяСтрока = ТзВыборки.Добавить();

     ЗаполнитьЗначенияСвойств(новаяСтрока, ВыборкаДанных);

КонецЦикла;

В первом случае, мы постоянно обращаемся к файлу данных, но зато проходим не весь файл, а только нужные записи. Во втором случае, выборка идет из одного файла, но зато перебираются все записи. А что, если у нас записей с признаком «Истина» много (допустим, сто тысяч), а записей с признаком «Ложь» всего несколько штук (к примеру, три)? Еще вопрос: А стоит ли сохранять выбранную стратегию для дальнейшего использования? А что, если данные обновились? Подойдет ли сохраненная стратегия? Попробуйте ответить. Конечно, есть общие правила и рекомендации. И к ним стоить прислушаться. Но принимать все на веру не стоит. Лучше попробовать все самим. Представьте, как бы вы сделали выборку с условием равенства/неравенства, как бы соединяли таблицы и т.д.

Ну, как говорил Ги де Мопассан: «ближе к телу». В прошлой статье за бортом остались некоторые вопросы. Я сейчас постараюсь исправить положение. Быстренько пробежимся по некоторым пунктам, чтобы восполнить пробел. Я не буду слишком углубляться. Информации будет ровно столько, сколько нужно для понимания материала. Ну, а мы переходим к…

I. Хранение информации на диске.

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

 

Каждая строка состоит из полей (типа колонок таблицы), и каждое поле должно иметь свой фиксированный размер – поэтому, когда мы создаем поля, то указываем его тип, ведь по нему движок СУБД определяет количество выделяемой памяти для поля. Если данные поля слишком большие и превышают размер страниц, то в область данных записывается информация о странице, где хранится информация. Каждая страница принадлежит одной таблице в БД. И, соответственно, одна таблица может содержать много страниц. Помимо самих страниц данных есть еще и индексные файлы. Когда мы создаем индекс, то создаются индексные файлы. По структуре (по крайней мере, той, которая нас интересует) они похожи на страницы, где хранятся данные таблиц, только вместо данных таблиц там находятся данные индексов. Индексные файлы связаны с информацией в наших страницах данных. Соответственно, если мы обновляем данные в таблице, то движок СУБД должен обновить и индексные файлы. Когда происходит захват данных в таблицах, мы должны понимать, что на самом деле, произошел захват страниц. И еще нам нужно понять: что все проходы по БД (выборка, обновление, запись и т.д.) – это постоянное перемещение страниц из оперативной памяти на диск и обратно. В рамках оптимизатора – это значит, что процесс оптимизации начинается с проектирования БД. Я понимаю, что работы от проекта ведут не все, чаще всего вам приходится работать с уже готовыми базами. Поэтому мы здесь больше внимания уделим именно этому и следующим важным пунктом для рассмотрения являются индексы.

  1. Индексы. Индексы служат для более удобного доступа к данным. Наиболее удачный пример – это оглавление книги. В книгах есть раздел «Оглавление», где содержится список разделов или глав, с указанием номера страницы, перейдя на которую, мы можем начать читать указанную в «Оглавлении» главу. Другой пример - это закладки, которые можно распределить по книге для быстрого поиска.  Индексы в БД представляются в виде дерева. Ветки дерева – это диапазоны значений полей. Проход по веткам дерева с выходом на конкретную/ые страницу может быть быстрее, чем переход от страницы к странице, хотя это не всегда справедливо (я имею ввиду маленькие таблицы, которые проще сканировать последовательно).

В БД индексы бывают двух типов: кластерные и некластерные.

 

  • Кластерный индекс создается по полю таблицы с уникальными значениями и в листьях своего дерева содержит непосредственно записи таблиц. Если сравнить с оглавлением, то строчка оглавления – это запись индекса, а когда мы переходим к нужной странице, то получаем данные, которые необходимо прочитать (запись таблицы). Такой индекс является физически упорядоченным. На каждую таблицу можно создать один кластерный индекс, так как упорядочить одновременно по нескольким полям невозможно.
  • Некластерный индекс. В отличии от кластерного индекса, некластерный содержит в своих листьях ссылки на записи таблиц. Опять же, если сравнить с книгой, то в книгах, помимо оглавления, бывает еще и алфавитный указатель, содержащий ссылку на главу. Поэтому, чтобы найти эту главу, нам нужно обратиться к оглавлению, а из него к непосредственной странице с данными. Если оглавления нет, то придется просматривать страницу за страницей, в поисках нужной главы. (По крайней мере, так работает движок СУБД)

Вот здесь первое замечание: - При сильном частом обновлении таблицы с кластерным индексом, будет происходить снижение производительности из-за переупорядочивания данных. А при частом удалении таблица будет фрагментирована (содержать пустые строки). Попробуйте сами создать таблицу без индекса и  добавьте/удалите несколько тысяч строк и сделайте тоже с кластерным/некластерным индексом. И чем больше раз вы проделаете операции, тем нагляднее будет пример. Особенно обратите внимание на разницу между кластерным и некластерным индексом. И тоже самое, проделайте с выборкой. С выборкой играть еще интересней. Можно сначала сделать простую выборку, потом выборку с оператором DISTINCT (РАЗЛИЧНЫЕ), с оператором TOP, потом с условием по полю с кластерным индексом, потом с некластерным. Получить их планы запроса и сравнить. Для тех, кто не знает, как получить план запроса, в конце статьи будет описание команд СКЛ в среде Management Studio.

- Кеширование индексов. В SQL Server есть область памяти, предназначенная для хранения планов выполнения запросов. Если к серверу выполняется несколько однотипных запросов, то сервер строит план только для первого запроса и сохраняет его в кэш, а остальные выполняет по уже созданному плану. Это касается параметризированных запросов. Например:

Есть два запроса: SELECT * FROM Zakaz WHERE date_zakaz > ‘01/02/2010’

И второй запрос:  SELECT * FROM Zakaz WHERE date_zakaz > ‘01/02/2012’

Обратите внимание на условие. Здесь используются разные даты в виде констант. Для этих запросов оптимизатор будет строить разные планы, так как ему надо оценить каждую выборку.

Теперь давайте переделаем на параметризированный запрос:

DECLARE @date_parametr = ‘02/02/2010’

SELECT * FROM Zakaz WHERE date_zakaz > @date_parametr  ,

затем запустим еще один похожий запрос

DECLARE @date_parametr = ‘01/02/2000’

SELECT * FROM Zakaz WHERE date_zakaz > @date_parametr  .

В этом случае, план первого запроса уйдет в кэш и второй запрос будет выполнен по этому плану.  А это не всегда гарантирует оптимальность выполнения запроса. И еще одно, очень трудно понять, что запрос будет неэффективно выполняться, если смотреть только на код запроса, то есть при разных параметрах – будут разные результаты.

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

CREATE NONCLUSTERED INDEX i_zakaz

  • ON zakaz (date_zakaz)
  • WHERE date_zakaz > ‘01/01/2018’

GO

Теперь, когда мы будем обращаться к полю date_zakaz данной таблицы, то оптимизатор может использовать наш фильтруемый индекс. Конечно, надо помнить о том, что индекс тоже требует обслуживания. Другими словами, на каждый индекс тратятся ресурсы.

 

  • Индекс, с включенными полями. Еще одним видом индексов является индекс с включенными полями. Часто бывает такая ситуация – надо выбрать определенные столбцы, при этом отбор использует некое третье поле. Примером может служить выборка Склада и Номенклатуры, а в качестве отбора используем поле Организация. Даже если Организация имеет индекс, план запроса вряд ли будет его использовать. Хотя если сделать индекс по полям Склад и Номенклатура, то тут наш индекс попадает в план запроса. При создании таких композитных индексов следует учесть, что порядок указания столбцов играет роль на использование индекса оптимизатором. Если в нашем примере поменять местами поля Номенклатура и Склад, то при выборке такой индекс не будет учитываться. Вообще, роль играет только первое поле. Допустим, мы указали такой порядок: Склад, затем Номенклатура – теперь если выбирать поле Склад или Склад и Номенклатура, то индекс сработает, а вот если выбрать поле Номенклатура или Номенклатура и Склад – индекс не сработает. Можно, конечно, добавить еще один индекс по полям Номенклатура, затем Склад. В этом случае, оптимизатор будет сравнивать, какой индекс более селективный и использовать его. Только такие вещи лучше делать с осторожностью – индексы надо обслуживать.
  • Упорядочивание индексов. При создании индексов вы можете использовать команды упорядочивания по возрастанию/убыванию (ASC/DESC). Такое упорядочивание влияет на группировку и сортировку данных при выполнении запросов. На выборку, правда, это не влияет.
  • Статистика индексов. Как говорилось в прошлой статье, на использование индексов влияет селективность. Если более подробно, то для каждого индекса есть понятие плотность распределения. Это когда определяется как распределены данные таблицы и вычисляется путем 1 / Число уникальных записей. Здесь большое значение имеет ЧИСЛО УНИКАЛЬНЫХ ЗАПИСЕЙ. Так, к примеру, если в нашей таблице все записи уникальны, то мы получим плотность = 1/число записей. А если в нашей таблице пять уникальных записей? То здесь будет плотность = 1 / 5 или 20%. При создании индексов рекомендуется обращать внимание на плотность распределения и если она выше 10% (для некластерного индекса), то создание индекса вызывает большое сомнение. Тут действует правило: чем больше плотность распределения (больше уникальных записей), тем лучше для индекса. Поэтому создание индекса для таблицы из пяти записей - бесполезно. Еще пример: -  Пусть, в таблице 500 строк и всего пять уникальных значений (сто записей – одно значение), то плотность = 1/5 или 20%. Или поле таблицы может содержать миллион строк, но всего два значения. Это, например, может быть, когда наше поле имеет значение типа бит и значения типа: (1 или 0 и т.д.). Селективность у этого поля низкая и поэтому здесь будет сканирование таблицы даже при наличии индекса. Есть некоторые специфические виды индексов – это индексы множеств или кластеров. Например, поле содержит географические данные, и мы решили разбить их по регионам. Одно множество может показывать один регион, другое множество следующий регион и т.д. (ну или кластеры: хорошие покупатели, плохие покупатели; добросовестные кредиторы и недобросовестные; ваши примеры). Эти индексы тоже срабатывают при хорошей селективности.

Вообще, когда нужно представить работу индексов и статистику, то можете использовать следующий подход: вы берете книгу, где вам надо прочитать две главы. Как думаете, использовать оглавление в этом случае? Не спешите отвечать. На самом деле вопрос с подвохом. Если в книге две или три главы, то можно и целиком прочитать. Ведь так? А если там целая куча глав, то тогда выгодней использовать оглавление. Также и работа с некластерными индексами – только здесь, вместо оглавления, алфавитный указатель.

Как можно посмотреть статистику по индексу? - Для просмотра статистики можете воспользоваться командой:

DBCC SHOW_STATISTICS (<your_table>, <your_index_name>), где <your_table> - имя таблицы <your_index_name> - имя индекса   

Вручную обновить Статистику поля индекса можно командой

UPDATE STATISTIC [your_table] [your_index_name]

 Вместо рекомендаций. При использовании индексов обратите внимание на то, по каким полям данные выбираются чаще всего. Если вы используете выборку по Складу и Номенклатуре, то индексы по этим полям могут ускорить выполнение запроса. Обратите внимание на плотность. Если плотность данных мала, то индекс будет, скорее, вредить, чем помогать. Обновление статистики играет немаловажную роль. А вот делать это вручную или средствами СУБД – это уже решать вам. В некоторых случаях, полезно чтобы СУБД само заботилось об обновлении, но иногда вам может понадобится и вручную обновить статистические данные. Допустим, что в таблице, хранящей миллион строк, изменилось сто записей. Стоит ли обновлять статистику? – Думаю, нет, если плотность данных высокая и думаю, да, если плотность данных настолько низка, что измененные записи влияют на количество уникальных записей. Однозначного ответа нет. Понимаю, что многим хотелось бы готовых рецептов, но, к сожалению, их не существует. И об этом далее…

Что влияет на решения оптимизатора. Давайте рассмотрим, что влияет на решения, принимаемые оптимизатором.

Инструкции языка - [inner join, cross join, left/right outer join, full outer join, [not]exist, [not]in, union, intersect];

Местоположение в запросе -[select, from, where, order by];

Хинты (если есть поддержка подсказок для запроса) - [join (loop, hash, merge), table (index), query (force order, loop join, merge join, hash join)];

Количество предикатов -[нет предикатов, один, несколько];

Операции в предикатах - [=, >, <, >=, <=, <>, is, is not, not];

Тип запроса- [select, insert, update, delete, merge, if, cursor];

Как видите, таких множеств много, а комбинаций этих множеств еще больше. Не помню, где, я видел такую картинку. Мне кажется, она в лучшей мере отображает ситуацию

Представьте эти прямоугольники в виде неких валов. Допустим, мы видим только верхнюю строку. Вращая эти валы, мы получаем различные комбинации этой строки. И на основе этой комбинации, строим наилучшую стратегию. Причем, мы можем получать не одно, а несколько строк. Крутанули вал – зафиксировали строку, затем опять – крутим вал и так далее. Результат может быть очень огромным. И как будет действовать оптимизатор не всегда очевидно. Ведь мы можем делать выборку, вложенную в другую выборку, затем повторять эту выборку или соединять по разным условиям. Иногда оптимизатору выгодно сразу что-то рассчитать, иногда лучше выбирать последовательно. И насчет рассчитанных значений. Давайте рассмотрим еще такое понятие, как предикаты и скаляры. Это рассчитанное выражение встречается в плане запроса как Compute Scalar.

Предикаты - выражение, результатом которого является значение булевого типа или неопределенно (множество Операции предикатов). Выражение может состоять из одной или двух частей. Например: НЕ <Проверяемое> (проверяется одна часть) или <Первое> = <Второе> (состоит из двух частей: справа и слева). Еще одно определение, которое нам понадобится – это скаляры. Скаляры – это константы, переменные или арифметические выражения. Они могут стоять в любой части предиката. Скалярные выражения вычисляются, а их значения подставляются в то место запроса, где они объявлены (в плане запроса Compute Scalar).

Вот мы и пробежались по основам. Теперь, я думаю, вы и сами сможете в случае необходимости углубиться в материал. Ну, а мы рассмотрим вызов оптимизатора.

Оптимизатор. Для того, чтобы посмотреть план запроса в среде Management Studio наберите

SET SHOWPLAN_ALL ON;  --включаем показ плана запроса

GO – запуск изменений

--В этом месте располагаем свой запрос

SELECT  <your_fields>  FROM <your_table>

--закончили запрос 

GO  -- запуск запроса

SET SHOWPLAN_ALL OFF; --отключаем показ плана запроса (ОБЯЗАТЕЛЬНО)

GO – запуск изменений

Вместо SHOWPLAN_ALL можно использовать SHOWPLAN_TEXT (правда, вид у него не очень) или SHOWPLAN_XML (а у этого классный вид). Или можно набрать текст запроса и выбрать в меню «Запрос», кнопку «Показать предполагаемый план запроса».

В прошлой статье рассказывалось о соединениях (NESTED LOOP, MERGE JOIN, HASH JOIN). Здесь же посмотрим на такие вещи index scan, index seek, table scan.

Index scan – сканирование по индексу. Выбираем записи, используя индекс.

Index seek – позиционирование внутри выборки по индексу. Сразу переходит к нужной записи. Если по аналогии с книгой, то сразу находит нужную страницу и ставит указатель на нужную строку.

Table scan – сканирование таблицы. Проход по всем страницам последовательно. Так мы читаем книгу. Нас не интересует оглавление, мы читаем от начала до конца.

Когда вы будете получать план запроса, обратите внимание, что выполняется он справа налево. В плане запроса, помимо информации о выбираемых таблицах и методах, еще есть информация о стоимости каждого действия. Чем выше стоимость, тем больше затрат на данное действие. Для эксперимента, вам понадобится не так уж много. Можете создать базу из трех-пяти таблиц. Сделать наполнение. Мы, в свое время, делали так. Из программы выгружали в файл CSV данные, которые загружали потом в тестовую базу. И начинали эксперименты: создашь индекс – посмотришь выборку, удалишь индекс – опять выборка. Сами выборки делали тоже с усложнением. Сначала просто выборку, потом с операторами TOP, DISTINCT, потом условия (разные: на равенство, неравенство, вхождения в диапазон и т.д.), потом соединения с другой таблицей и тоже с разными условиями. И, сравнивая планы запросов и результаты выборки, учились. Если честно, то это заняло не так много времени, как думали изначально.

Теперь немного слов о 1С. К сожалению, управление индексами в рамках 1С, как бы сказать тактичней, сильно упрощено. Настолько сильно, что из конфигуратора, вы можете только указать, что индексировать, но не как это делать. В принципе, можно в самом скуле выставить нужные индексы на базу 1с, но я вам это не рекомендую. Я не уверен, к каким последствиям это может привести. На что я хочу обратить внимание, так это «Внешние источники». Тут есть возможность и рыбку съесть, сорри, свою базу в скуле сделать и с нужными индексами и прочим. И потом обращаться к ним из 1с.

За сим позвольте откланяться. Если есть вопросы, пишите в комментарии.

запрос оптимизатор стратегия индекс СУБД

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2981    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5111    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7641    158    ZAOSTG    68    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5978    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8872    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5105    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16186    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. acanta 24.01.20 01:00 Сейчас в теме
Ничего не понятно, но очень интересно. Спасибо за труд. Возник вопрос - является ли включение необходимых индексов в sql достаточным для того, чтобы они использовались при выполнении команды из 1с?
Или индекс обязательно должен быть описан в конфигурации?
darkdan77; PLAstic; +2 Ответить
2. PerlAmutor 129 24.01.20 06:35 Сейчас в теме
(1) Наличие индексов само по себе не означает, что он будет использоваться. Задача программиста написать запрос таким образом, чтобы он мог использоваться. Даже если запрос написан идеально, то и это не означает, что индекс будет использоваться. Зависит от того, что решит оптимизатор. В некоторых случаях, лишний или неправильно (низкоселективный) добавленный индекс, может даже ухудшить время выполнения запроса. На разном железе одинаковая программная среда на одинаковых данных (с теми же индексами) тоже может показывать разные результаты. Например, если имеет узкое место в производительности процессора. Оптимизатору может не хватить времени на построение плана запроса и он всё бросит и попытается выполнить запрос "как есть", т.к. посчитает, что он выполниться быстрее, чем построится оптимальный план запроса. Для одних и тех же запросов может строится план каждый раз новый, только потому, что в нем используются временные таблицы и SQL сервер глядя каждый раз на "#Temp1", "#Temp2", "#Temp3" будет думать, что это совершенно разные запросы. В итоге у одного и того же пользователя один и тот же отчет сформированный несколько раз может формироваться за разное время. Лишь потому что, когда он формировал день назад этот отчет в таблице было 100к записей и использовалось имя временной таблицы "#Temp1", план запроса попал в кэш, а сегодня там уже 1кк записей и используется имя "#Temp2", где для запроса в кэше план запроса совсем другой...
A_Max; AnatolPopov; fancy; Fox-trot; m_aster; logarifm; sapervodichka; RustIG; acanta; SerVer1C; +10 Ответить
3. darkdan77 339 24.01.20 07:18 Сейчас в теме
(2) Я даже среагировать не успел. Спасибо за ваш ответ. Мне добавить уже нечего.
21. sapervodichka 6697 25.01.20 15:03 Сейчас в теме
(2) Влад, привет, на мой взгляд это мог быть не отдельный комментарий, а статья. У тебя критика всегда несёт ценный поучительный посыл. Пиши!
20. darkdan77 339 25.01.20 14:30 Сейчас в теме
(1) Я бы вам дал приз за самый прикольный комментарий.
33. buganov 200 26.01.20 13:57 Сейчас в теме
(1)кстати, включение новых индексов в SSMS может дать прирост по чтению, но негативно будет влиять на запись. Представьте себе, что у Вас в регистре 5 измерений и на каждое из них Вы сделаете на всякий случай по индексу. В итоге у Вас при записи будет сначала поиск куда вставить, а потом сама вставка. И так 5 раз. Если индексов будет больше, то и вставок больше. Если система высоконагруженная и СУБД в режиме блокировочника, то Вы будете простаивать на блокировках. Потому что пишущая транзакция будет ожидать, если на хотя бы одну запись хотя бы по одному индексу есть пишущая другая транзакция.
Индекс это и хорошо и опасно одновременно
4. strek_ivan 79 24.01.20 09:28 Сейчас в теме
Коллеги,
тема индексов и производительности баз данных весьма ёмкая и очень волнует нас, но вопросов от вас что-то не видно! ))

Я выражаю Данису благодарность за проработку интересного материала, Вам удалось создать простую, понятную статью с полезным наполнением.
34. buganov 200 26.01.20 14:12 Сейчас в теме
(4) а вопросы возникают только тогда, когда в голове четко раскрывается тема статьи, а не просто, как из ведра вываленная на голову информация, которая даже в официальной документации не такая сухая. Плюс забавные ошибки от автора, который заявляет, что занимается давно оптимизацией, при этом заявляет, что запрос исполняется справа налево и что оптимизатор на самом то деле никак не вычисляет сколько данных полетит в том или ином случае, а только ищет их "вероятность". Он ведь не в 21 играет.
Да, труд большой проделан, но, увы, хуже статей по оптимизации я не видел. Да, тот же Короткевич очень быстро говорит на вебинарах, что делает достаточно трудным понимание, что он говорит, но на ютубе можно и замедлить скорость воспроизведения. И кстати, индексы я в полной мере понял, как работают только ему и Богачеву. Именно работают, а не тупо теорию.
35. acanta 26.01.20 14:29 Сейчас в теме
(34) и чем теория отличается от реальной работы индексов?
36. buganov 200 26.01.20 15:04 Сейчас в теме
(35)я не сравниваю теорию и практику, я сравниваю стиль изложения. И тот факт, что прочитав одних авторов сразу все встает на свои места, читая других засыпаешь. Банальный пример, когда я учился программированию в 1С, первым делом прочитал Радченко Хрусталеву, изумительно написанная книга. Потом из нежелтых авторов, только запутался. Вот Вы в первом же сообщении написали, что ничего не понятно. Смысл то статьи был как раз в том, чтобы Вам стало понятно. Рассказать простыми словами с примерами о сложных вещах. Почитайте Пермитина, он тоже пишет про работу MSSQL Server, но блин, ему Пулитцера надо вручить за его статьи.
5. login1020 133 24.01.20 10:15 Сейчас в теме
А можно перевыложить в качестве?
Прикрепленные файлы:
6. darkdan77 339 24.01.20 10:37 Сейчас в теме
(5) Я ее перерисую и выложу в комментариях. Сегодня (24.01.2019),в районе 18.00 - 19.00 по московскому времени
7. darkdan77 339 24.01.20 10:56 Сейчас в теме
(6)Извиняюсь. Дату неправильно поставил 24.01.2020
8. darkdan77 339 24.01.20 11:45 Сейчас в теме
(5) В прикрепленном файле рисунок. Должно быть видно.
Прикрепленные файлы:
user774630; login1020; +2 Ответить
9. logarifm 1117 25.01.20 01:10 Сейчас в теме
Бред же написан:
А у нашего, горячо любимого, оптимизатора, помимо предположений, есть еще полное незнание структуры базы данных, с которыми он работает.


Оптимизатор все прекрасно знает и формирует план запроса опираясь именно на знания того какова статистика у ваших таблиц именно это является основной и не отлемлимой частью на, что опирается оптимизатор.

Почему "ошибается" в этом есть ряд причин. Но то что вы написали, что он ничего не знает - это чушь!
13. darkdan77 339 25.01.20 09:34 Сейчас в теме
(9) Оптимизатор не знает ничего до того, как данные поступят к нему на вход. В разных СУБД используются разные подходы к обработке входящих данных. Где-то это будут генетические алгоритмы, где-то нейронная сеть. И это область вероятностного программирования. Или, скажете, что я не прав? Повторюсь, реализация оптимизатора в разных СУБД различна. Есть только одно общее - это программа для обработки своих входящих данных и выдаче результата. Я участвовал в проекте разработке системы управления для стат. данных (это тоже СУБД) . У нас оптимизатор работал на ГА (генетические алгоритмы). Входные данные для него - это набор числовых значений. Никакой структуры БД там нет. Статистика тоже переведена на язык цифр. Для оптимизатора важно даже не выбрать, а сделать прогноз. Да, я не стал говорить о предполагаемом и фактическим исполнении запроса, но это тема здесь, думаю, не так интересна. Хотя здесь, я может и не прав. Но то, что оптимизатор не знает о структуре БД - это точно, да и не надо это ему.
10. logarifm 1117 25.01.20 01:27 Сейчас в теме
и тут брехня:
Информация на диске хранится в виде страниц.


Хранение организовано в виде иерархических структур:

База данных. Наивысший уровень абстракции для хранилища данных

Файл. Для хранения 1 базы данных по умолчанию используется два файла.

В первом физическом файле базы данных хранится реальная информация. .mdf
Второй файл является вспомогательным файлом базы данных – ее журналом. .ldf, и без него база данных работать не будет.
Экстент. Являет собой основную единицу пространства, выделяемую под таблицу или индекс. Экстент состоит из максимальных восьми смежных страниц данных. В SQL Server при создании таблицы изначально не выделяется ни одной страницы. Они добавляются только при вставке в таблицу новых строк. С этого момента при необходимости дополнительного пространства для хранения данных SQL Server будет выделяться как минимум экстент. Экстенты бывают двух типов:

Разделяемые экстенты. Разделяемые экстенты могут совместно использоваться восемью различными объектами (т. е. каждая страница экстента может принадлежать другому объекту). Все создаваемые таблицы и индексы помещаются в разделяемые экстенты. Как только количество страниц, выделенных под объект, достигает восьми, для объекта будет выделен новый однородный экстент.
Однородные экстенты. Структура однородных экстентов понятна из названия. Каждая страница такого экстента принадлежит одному и тому же объекту.
Страница. Является элементарной единицей пространства выделяемого внутри отдельного экстента. Размер страницы составляет 8 КБ. Страница может рассматриваться как контейнер для хранения и строк таблиц и индексов. Одна строка не может быть разделена между двумя страницами. Страница состоит:

Заголовок страницы (page header); заголовок включает: номер страницы, тип страницы, количество свободного пространства на странице, идентификатор единицы распределения объекта, которому принадлежит страница.
Сами данные
Указатели смещения строк (row offset).

Ребята не читайте - автор как говорится слышал дзвон но не знает где он!!!

https://github.com/Panda-Lewandowski/DataBase/wiki/48.-%D0%9C%D0%B5%D1%82%D0%BE%D0%B4-%D1%84%D0%B8%D0%B7%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%BE%D0%B3­%D0%BE-%D1%85%D1%80%D0%B0%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BD%D0%B0-%D0%B4%D0%B8%D1%81%D0%BA%D0%B5-%D0%B2-SQL-Server

Также можно почитать Вьейру.
14. darkdan77 339 25.01.20 09:45 Сейчас в теме
(10) Вы опять опираетесь на одну СУБД - это первое. Второе - логический и физический уровень хранения у вас сплетены в одно. А вы в курсе, что, помимо экстентов, есть и другие логические виды хранения? Сможете привести их здесь. Согласен с вами в том, что нужно было разделить понятия и объяснить, что такое минимальная единица хранения и во что это можно объединить. И второе, с чем мог бы быть согласен, надо было попробовать придумать свою систему хранения и показать как можно ей распоряжаться, но это уже область создания СУБД, а не только оптимизатора. И еще, у вас же написано, что данные лежат в страницах.
15. darkdan77 339 25.01.20 10:13 Сейчас в теме
(10) Немного об абстрактном. Файлы на диске, когда мы смотрим через проводник, лежат в каталогах (папках). Что такое каталог? - Его можно рассматривать как что-то объединяющее файлы (мы так и видим его) и можно рассматривать как файл, который содержит записи о других файлах (так видит его система). Минимальной единицей хранения для файла будет кластер. Минимальной интересующей нас единицей - будет файл. Мы можем придумать еще разные способы хранения. Например, мы можем сделать файлы, куда запишем записи о файлах распределенных по дате создания. То есть, файл за первое число содержит строки о файлах созданных первого числа. Потом предоставить пользователю календарь, открывая который, он будет видеть созданные файлы за этот день. Это будет другое представление файлов на компьютере. Но единицей хранения все равно будет файл. Я прошу вас разделять понятия и уровни.
11. logarifm 1117 25.01.20 01:29 Сейчас в теме
12. logarifm 1117 25.01.20 01:32 Сейчас в теме
16. logarifm 1117 25.01.20 12:22 Сейчас в теме
Так ваша статья описует какую СУБД - никакую! Опираться надо на определенный вид СУБД, а то есть на статичиски часто используемые. Она абсолютно далека от оракла но немножко приблежена к МС скл и вы такой мне говорите, что абстрактны. В потгресе там вообще все иначе о какой абстракции идет разговор вообще. Если Вы затрагиваете такие темы как Страницы то извольте уже четко описать потому как разные СУБД хранят по разному структуру так заных страниц МС СКЛ в эстентах постгря вообще по другому я уже молчу то, что для ее не желательно НТФС раздел.
18. darkdan77 339 25.01.20 13:36 Сейчас в теме
(16) Верно. Статья использует никакую СУБД. Примеры сделаны для мс скл. Статья рассматривает работу оптимизатора и факторы, на это влияющие. У меня к вам вопрос: Если где то сегмент назовется страницей, что произойдет? Признаю, мне надо было назвать это минимальной интересующей нас единицей информации на диске. Только это долго, а название "страница" очень точно отражает суть.
Странно, что вы не обратили на индексы. Здесь еще больше можно найти претензий. Даю подсказку: Их количество неполное, организация в файлах не такая, как написана. Предлагаю вам. Вместо того, чтобы вести бесполезные споры, напишите, как бы хотели видеть статью.
24. logarifm 1117 25.01.20 15:16 Сейчас в теме
(18)
единицей информации н

Статья даная не дотягивает она сырая - это черновик далек от реального труда. По сему многие думаю меня поддержат. Знающие люди не трогают эту глубину основ ибо она необъятная и все что необходимо уже давно написано в умных книгах я уже написал Роберт Виейра очень много трудов. Вот, что действительно заслуживает внимания. Там все четко сказано как работает оптимизатор, что такое индексы. А статья это публицистика никому ненужная и уж более того путаяющая не экспертов. Потому как человек который не совсем в этом разбирайтется зайдет и воспримет все это за чистую монету. А что скажут те кто уже сотню раз оптимизировал системы. Интересно узнать мнение у настоящих экспертов, что они думают по поводу даной статьи потому как я не знаю на кого она расчитана. Материал изложен абсолютно не полностью да и напутано.
28. darkdan77 339 25.01.20 16:11 Сейчас в теме
(24) Именно потому, что сотню раз оптимизировал системы, я и написал так. Насчет того, что нужна работа по оптимизатору (или ссылки на такую работу) - ссылок у меня нет, а вот продолжение я теперь обязательно напишу.
strek_ivan; acanta; +2 1 Ответить
29. acanta 25.01.20 16:28 Сейчас в теме
(28) спасибо, будем рады увидеть продолжение.
На инфостарте сложная, многослойная аудитория, как консультанты, поверхностно знакомые с платформой и минимально с СУБД, так и администраторы-профессионалы.
Написать что-то, и интересное и понятное сразу всем очень сложно.
Было бы интересно также узнать о ваших выводах из обширной практики по оптимизации.
30. darkdan77 339 25.01.20 17:29 Сейчас в теме
(29) Напишу. Скорее всего, получится несколько статей (если в одну не смогу уложиться). Я вижу, что я неправильно сделал: мне надо разбить на модули все - от запроса и до плана выполнения. То есть, все проходы: разобрали запрос (объект парсер), построили дерево (объект rewriter), отдали на планирование и оптимизацию, построили план и отдали executor-у. Ну зато опыт. Научусь здесь и курсы открою :))) Пользователь, по ником logarifm, прав, что надо все аспекты раскрыть. А то непонятки получаются. Я так понял, он хочет увидеть весь этап, а я только одну часть описал. Вот теперь займусь продолжением (или лучше сказать - дополнением).
17. logarifm 1117 25.01.20 12:23 Сейчас в теме
Оптимизатор на входе , что значит не знает что за чушь непонятная... А причем тут вообще нейросети.
19. darkdan77 339 25.01.20 13:47 Сейчас в теме
(17) А как вы представляете работу оптимизатора? Думаете ему на вход идут названия таблиц, полей, индексов и прочее?
И отвечая на ваш вопрос: причем тут нейросети? - Машину можно обучить выбирать лучшее из возможных на основе нейросетей и, если не ошибаюсь, в том же постгрес ведутся разработки в этом направлении. А может и уже есть. У нас система переводится на нейросеть. Первые модели уже дают результат. Так что нейросеть здесь - очень даже причем. Вы сформулируйте то, что по вашему должна была описать статья. Давайте начнем с этого
22. logarifm 1117 25.01.20 15:05 Сейчас в теме
(19) а на базе чего написана Ваша статья собственных домыслов? А что входит в оптимизатор на вход!? Давайте опишите ссылки труда необходимо предоставить на базе чего все эти домысли.
23. logarifm 1117 25.01.20 15:07 Сейчас в теме
Я предоставил четкие ссылки на опровержение того, что тут понаписано вначале. Насет оптимизатора и насчет структуры файла - это ересь!
26. darkdan77 339 25.01.20 15:55 Сейчас в теме
(23) Возвращаю вам вашу же ссылку: https://github.com/Panda-Lewandowski/DataBase/wiki/48.-%D0%9C%D0%B5%D1%82%D0%BE%D0%B4-%D1%84%D0%B8%D0%B7%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%BE%D0%B3­%D0%BE-%D1%85%D1%80%D0%B0%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BD%D0%B0-%D0%B4%D0%B8%D1%81%D0%BA%D0%B5-%D0%B2-SQL-Server

Страница. Является элементарной единицей пространства выделяемого внутри отдельного экстента. Размер страницы составляет 8 КБ. Страница может рассматриваться как контейнер для хранения и строк таблиц и индексов. Одна строка не может быть разделена между двумя страницами. Страница состоит:

Заголовок страницы (page header); заголовок включает: номер страницы, тип страницы, количество свободного пространства на странице, идентификатор единицы распределения объекта, которому принадлежит страница.
Сами данные
Указатели смещения строк (row offset).
25. acanta 25.01.20 15:23 Сейчас в теме
Кстати ситуация, когда программист 1с приходит из отпуска и ему как бы неплохо внести в конфигурациях изменения на предмет добавления в них индексов, на горячую вставленных сисадмином по результатам работы какого нибудь отчета, вполне реальная и недавно обсуждалась в публикациях.
Есть ли инструмент "для 1с Ника" позволяющий обнаружить несоответствие индексов и полей в конфигурациях и в СУБД? Кроме монопольного тестирования и исправления на копии в базе 24/7?
27. darkdan77 339 25.01.20 16:01 Сейчас в теме
(25) В рамках 1с не знаю. Обычно, для популярных систем есть свои консоли для обслуживания. В MS SQL - Management Studio; Postgres - есть pgAdmin (под виндой тоже работает).
31. buganov 200 26.01.20 13:32 Сейчас в теме
(25)в MSSQL Server есть DMVшки, которые подсказывают, какие индексы хочет субд и какой эффект будет от них
dm_db_missing_index_groups
dm_db_missing_index_group_stats
dm_db_missing_index_details
32. buganov 200 26.01.20 13:51 Сейчас в теме
Можно кинуть в меня ссылкой на документацию, или другой источник, что оптимизатор оперирует вероятностями, а не данными? Точнее не самими данными, а их распределением по статистике.
Насколько мне известно, то оптимизатор как раз таки смотрит какие таблицы используются в запросе, их ёмкость, индексы этих таблиц, условия и дальше старается максимально эффективно сделать первую и последующие извлечения данных из таблиц(индексов), чтобы при первом извлечении данных на вход следующему оператору пошло как можно меньше данных.

Да, статьи по оптимизации всегда должны априори иметь 100500 плюсов, но у Вас есть один огромный недостаток, как по мне. Мне лично просто неинтересно читать. Не потому, что я это все давно знаю, я всегда стараюсь перечитывать одни и те же статьи, чтобы ухватить то, что не уловил в прошлый раз, у Вас просто написано языком, словно Вы лабу защищаете в ВУЗе, очень сухой язык. Тяжело читать.

Для всех желающих, кто хочет углубиться в работу MSSQL может посмотреть цикл вебинаров Короткевича на ютубе. А для понимания принципов работы СУБД, индексов и как 1С взаимодействует с СУБД тут несомненно Богачев будет в лидерах. Хотя, Короткевича стоит посмотреть хотя бы потому, что он спускается на очень низкий уровень раскрытия. Например, почему оптимизатор может выбрать скан кластерного индекса, а не seek по некластерному индексу.

"Когда вы будете получать план запроса, обратите внимание, что выполняется он справа налево"
Вообще то, поток операторов идет слева направо, а поток данных идет справа. Левый оператор передает правому инструкции о том, что ожидает на вход. Но никак не наоборот!

"Для того, чтобы посмотреть план запроса в среде Management Studio"
Интересно, для чего выполнять запрос и получать его план непосредственно в SSMS? Чтобы показать умение SQL? Особенно странно это читать в разрезе 1С. Гораздно кошернее было бы снять трассу теми же расширенными событиями и получить планы без особого вреда системе.

В целом, почитайте статьи на ИТС, у Андрея Бурмистрова, Юрия Пермитина. Даже ИТС написана более изящным языком, что ли? Курсы-1с-рф в разделе статьи тоже не мешало бы.

И отдельный минус рецензенту за допуск ошибок в статье.
triviumfan; logarifm; +2 Ответить
37. logarifm 1117 26.01.20 15:25 Сейчас в теме
(32)Почитайте мои комментарии я также бросал в автора статьи камнем но народ странный и еще лайкает это творение. Хуже статьи не видел на самом деле. Более того эта стаья вообще не относиться ни коем образом к оптимизаии и уж более чем к оптимизатору запросов, чем автор сам заявил, что все его высказывания это собственные домысли и не базируется ничем кроме как его собственных. Нет никакой документации ничего - так что продолжайте плюсовать !!
38. acanta 26.01.20 15:31 Сейчас в теме
(37) вы не правы. Логика есть всегда, а понимать заказчика это жизненно необходимый скилл.
Вы же не собираетесь конкурировать с вашими заказчиками? Так почему же столь требовательны к вашим коллегам?
Любое техническое задание или инструкции пользователя могут быть написаны в авторском стиле, не говоря уже о многих нетленках.
40. buganov 200 26.01.20 15:40 Сейчас в теме
(38) ТС написал статью с ошибками. Человек, начавший изучение связку 1С-СУБД может выбрать неверный путь по изучению, как следствие, будет сформировано неверное представление о работе СУБД и такой программист просто либо будет посмешищем, либо будет переучиваться. Ошибочные статьи гораздо вреднее, чем чтение сухой документации. К тому же, эта статья написана таким же невероятно скучным языком.
logarifm; +1 Ответить
41. buganov 200 26.01.20 15:41 Сейчас в теме
(38)К тому же, автору предложили подтвердить его тезисы документацией. Он ее не привел
39. darkdan77 339 26.01.20 15:34 Сейчас в теме
(32) Планировщик, а в некоторых СУБД экзекьютор знают о таблицах, разложенных в деревья запросах, индексах (чаще всего, это пути и их стоимость. Если не ошибаюсь, то постгрес даже функции называет как путевые). Оптимизатор, как часть всего процесса, использует вероятности. Если система управления дает выбор на основе переключателя (операторы switch case), то это значит, что оптимизатор не сработал (не до конца сработал, или, вообще, его нет).
42. buganov 200 26.01.20 15:42 Сейчас в теме
(39) Можно документацию по вероятностям и оптимизатору? Любую, постгре, MSSQL, да хоть оракл.
И укажите, пожалуйста, какой литературой Вы пользовались, когда писали статью
43. darkdan77 339 26.01.20 16:12 Сейчас в теме
(42) Постгрес. Документация к 11.1.1 Часть седьмая.Внутреннее устройство. Глава 57.Генетический оптимизатор запросов
44. buganov 200 26.01.20 16:38 Сейчас в теме
(43)В процедуре планирования в GEQO используется код стандартного планировщика, который строит планы сканирования отдельных отношений. Затем вырабатываются планы соединений с применением генетического подхода. Как было сказано выше, каждый план соединения представляется последовательностью чисел, определяющей порядок соединений базовых отношений. На начальной стадии код GEQO просто случайным образом генерирует несколько возможных последовательностей. Затем для каждой рассматриваемой последовательности вызывается функция стандартного планировщика, оценивающая стоимость запроса в случае выбора этого порядка соединений.

ТО есть по сути, он от балды набрасывает планы, а дальше планировщик их оценивает. Тут вероятности только при предложении планОВ планирову, который так же пойдет к статистике, индексам, чтобы оценить какой план наиболее шустрый. Если успеет, конечно же=)
logarifm; acanta; +2 Ответить
45. darkdan77 339 26.01.20 17:49 Сейчас в теме
(44) Не от балды. От лучшей из особей, в случае ГА. Поймите, мы можем сейчас долго спорить и показывать чье кунгфу круче.Вы сейчас даете повод зацепиться за ваши слова
и я тоже могу кричать: не вводите людей в заблуждение, а то они будут думать, что оптимизатор на основе фигни работает. Но я лучше соглашусь и продолжу. От балды :) может быть вычисленные значения для мутации или для скрещивания, или начальных значений. В постгрес оптимизатор работаем на уровне соединений, а это значит, что планировщик - либо на уровне выборки значений, либо на уровне переключателей (в статье - по соответствиям, в постгресе по стоимости путей). Оптимизатор - это часть процесса, а не весь процесс. Согласен, что в статье я это не выделил. И еще согласен с тем, что надо было указать, что ЧИТАЕТСЯ запрос справа налево, а не выполняется. Уж, простите. Тут моя оговорка. Есть еще одна ошибка: вместо "План запроса" я написал "Оптимизатор" - это в строке, где рассказывается как можно получить план запроса. Материал сухо изложен... Что же, я постараюсь как-то по другому излагать.
46. VmvLer 27.01.20 10:45 Сейчас в теме
(45) че, первый раз тролля видите?
по (37) очевидно же, что даже смотреть в ту сторону не логично.
47. logarifm 1117 27.01.20 11:13 Сейчас в теме
(46)что там не логично смотреть? Документацию МС СКЛ не логично смотреть официальную от майкрософт. О да тут еще по хлеще троли имеются. Или книги Роберта Вьейры не логично смотреть хотя о чем я хотя б потрудились узнать что это вообще такое.!
48. VmvLer 27.01.20 11:16 Сейчас в теме
(47) курсовую вчера сдали на 5?
если нет, то мне не о чем говорить с троешником!
49. logarifm 1117 27.01.20 11:19 Сейчас в теме
(48) какую курсовую о чем Вы? Хотя б на возраст в инфо можна было б посмотреть!
50. logarifm 1117 27.01.20 11:24 Сейчас в теме
Автору советую если уж решили продолжать. То постарайтесь писать статьи развернуто как например делает это Пермитин Юрий четко изложен материал абсолютно с примерами 1С , оптимизация, план запроса и т.д. К такому материалу тяжело даже вопрос задать. А к вашему материалу тяжело не то что его читать он теряет связку. Подумайте над изложением своих мыслей и все-таки превратите домыслы в факты. А если это Ваши домыслы то хотя бы не лишайте аудиторию новичков знать об этом - эксперты и так с Вами сходу не согласяться во многом. Хотя знаяэтих людей они эту публицистику крайне редко читают, а если и читают то просто обойдут комментарии из-за тактичности или мягко намекнут автору в том, что он вот тут немножко не прав.
51. acanta 27.01.20 11:33 Сейчас в теме
(50) представьте себе, что в файловой версии 1с этого нет. Написание такого же функционала по источникам МС или даже Юрия Пермитина (при всем моем уважении) это прямое нарушение авторских прав МС.
А МС это не Вася с инфостарта, тем более, когда 1с хочет как то выходить на международный рынок.
Поэтому основой для новых разработок может быть только мат часть и статьи именно вот такого, авторского наполнения, чему собственно говоря и учат в современных вузах.
52. logarifm 1117 27.01.20 12:35 Сейчас в теме
(51) файловая база только для вузов и пригодится больше не начто советую учиться в вузах , что такое клиень-серверный вариант!
53. acanta 27.01.20 12:49 Сейчас в теме
(52) и что же такое клиент-серверный вариант?
В контексте разработок фирмы 1с?
54. logarifm 1117 27.01.20 13:59 Сейчас в теме
(53)Я абсолютно утратил интерес к даной и что главное безсмысленной беседе.
56. buganov 200 28.01.20 03:44 Сейчас в теме
(51) на самом деле у двух топовых СУБД, с которыми работает 1С очень схож функционал и в некотором роде даже алгоритмы. Да, дьявол кроется в деталях, но по сути, что в MSSQL, что в PG есть данные в таблицах, есть индексы, есть статистики, есть планировщик и оптимизатор запросов. И последние оперируют как раз данными, а если быть точнее, то распределением этих данных в статистике. По крайней мере пока план по этому запросу не закеширован. Потом parameter sniffing сделает свое черное дело.

И, если взять одну и ту же базу, разместить на MSSQL и PG, выполнив некоторые запросы, то в большинстве случаев можно заметить, что планы очень похожи, за некоторым исключением.

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

И претензия к автору не в том, что он не Юрий Пермитин, а, лично у меня, к стилю изложения, очень спорные моменты, которые никак не подтверждены документацией. Хотя, достаточно было бы развернутых наглядных и исчерпывающих примеров. Тогда и споров бы было меньше. И опять же, написав статью, надо быть готовым, что набегут 1Сники и будут задавать вопросы
57. acanta 28.01.20 03:47 Сейчас в теме
(56) а по этой статье у 1сников вопросы возникают, но какие-то не такие? Не относящиеся к теме обсуждения...
58. buganov 200 28.01.20 04:34 Сейчас в теме
(57) что именно не по теме?
55. VmvLer 27.01.20 14:33 Сейчас в теме
что ж, абсолютно не обязательно что-то знать о клиенте специалистам с низкой социальной ответственностью.

пожалуй, можно продолжить беседу в контексте сервера.
logarifm; +1 Ответить
59. FIGOR 28.01.20 10:47 Сейчас в теме
Всегда удивляли попытки переделать программиста 1С в программиста не только 1С.
Еще больше интересует вопрос: почему то, что нам вроде бы надо, отсутствует в 1С?
Оставьте свое сообщение