• Другие части статьи:
• 1
• 2
• 3
• 4
• 5
• 6
• вперед »

Cardinality

Если вы полностью разобрались с предыдущими двумя сущностями (плотностью и селективностью), то раздел посвященный сущности третьей и последней станет для вас почти развлекательным чтением. Все формулы уже знакомы, где и что расположено в статистике известно, и, по сути, с Cardinality вопрос остается всего один: подобрать этому термину адекватный русский перевод. Тут, прямо скажем, «в товарищах согласья нет». Читая статьи и книги на родном для нас с вами языке вы можете встретить и «кратность связи атрибутов таблиц», и «количество связей», и «кратность связи», и «мощность множества», и просто «мощность»... Еще десятка полтора вариантов и под-вариантов я просто опущу.

Поскольку ни один из этих вариантов перевода не отражает сути этого термина (как минимум — не отражается суть термина при его применении в контексте обсуждения вопросов связанных со статистикой SQL сервера), я, лично, предпочитаю буквальный перевод — кардинальность, который тоже широко разошелся по статьям, книгам и форумам. В конце концов, не суть какой «лейбл» у нас будет на коробке, суть что там внутри. Внутри же, как выясняется, наша кардинальность проста как апельсин: кардинальность столбца=(селективность столбца) * (всего строк в таблице). Хм, постойте, а это не предполагаемое ли число строк получится у нас, т.е. тот самый Estimated Number of Rows? Вы абсолютно правы, как раз оно и получится. Вот и суть кардинальности проявилась — назвать термин «количество строк возвращаемых данным шагом (оператором) плана исполнения» одним словом. Не сложно?

Число строк каждого шага бывает предполагаемое (именно с такими мы работали на протяжении всей статьи), а бывает актуальное. Второе число можно видеть в актуальном же плане исполнения и только после действительного исполнения запроса. Два упомянутых числа иногда совпадают, но могут (и достаточно часто) отличаться. Точно так же различают и кардинальность — предполагаемую (estimated cardinality) и актуальную (actual cardinality). Как смотреть их в графическом представлении планов вы уже знаете. Если вы хотите сопоставить эти две кардинальности есть удобный способ: включить т.н. профиль инструкции и выполнить саму эту инструкцию. Скажем, в нашем случае с таблицей TT можно выполнить такой пакет:

Тогда вы получите 2 резалт-сета: обычный (от команды SELECT) и второй, в котором как раз и будет находиться профиль. В резалт-сете профиля каждая строка представляет собой отдельный шаг (оператор) плана исполнения. Колонки каждой строки — характеристики этого шага. Так вот колонка Rows будет содержать как раз actual cardinality данного шага, а колонка EstimateRows — estimated cardinality для него же. Для запроса выше оба значения будут 25, что показывает идеально просчитанную предполагаемую кардинальность. К сожалению, так будет совсем и далеко не всегда.

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

• Попытка-1: если значение предиката совпадает с одним из опорных значений гистограммы — нам повезло, можно сказать. Кардинальность просто берется из колонки EQ_ROWS и будет близка к идеальной.
• Попытка-2: если значение предиката совпадает с одним из промежуточных значений гистограммы — нам снова повезло, но гораздо меньше. Кардинальность берется из колонки колонки AVG_RANGE_ROWS соответствующего шага гистограммы и точность ее скорее удовлетворительная, чем хорошая.
• Попытка-3: если значение предиката могло бы быть одним из промежуточных значений гистограммы, но в реальности отсутствует в таблице. Здесь, как мы знаем, кардинальность вновь берется из колонки колонки AVG_RANGE_ROWS соответствующего шага гистограммы и точность ее уже скорее плохая, чем удовлетворительная.
• Попытка-4: если значение предиката вообще не представлено в гистограмме, т.е. меньше ее самой левой опорной точки или больше самой правой. Кардинальность элементарно вычисляется из значения колонки All Density (не забыли про плотность?). Точность при этом будет очень плавающей: от идеальной (реже) до никуда не годной (чаще).
• Попытка-5: в весьма редких случаях у оптимизатора не получается воспользоваться хотя бы значением плотности (по счастью, практически, такое случается только при написании запроса с совсем уж никудышным дизайном; элементарно «вылечивается» по крайней мере до уровня Попытки-4 переписыванием того же запроса «другими словами»). Тогда в дело вступает «магия», и кардинальность рассчитывается как, например, 0.05 * (всего строк в таблице). Но не потому, что 0.05 было определено как наиболее вероятная плотность/селективность, а просто потому что именно эта цифра была жёстко закодирована в сам оптимизатор. Учтите, что 0.05 взято для примера, точная цифра автору неизвестна. Скорей всего и тут срабатывает какой-то алгоритм что бы не брать такое значение совсем уж «с потолка», но по итогу оно берется почти оттуда. Про точность вычисления кардинальности при таком подходе можно даже не упоминать.

Теперь у вас должна сложиться в голове полная картина из доселе разрозненных фрагментов: все, о чем мы с вами говорили в ходе статьи — плотность, селективность, гистограммы с их опорными значениями, и т.д. — все направлено к одной цели — повысить качество (т.е. точность) расчета предполагаемой кардинальности запроса. Стоят ли столь титанические усилия столь незначительного (на первый взгляд) результата? Совершенно точно стоят! А все потому, что, к примеру возможный оператор плана Hash Join хорошо работает при слиянии двух больших, неупорядоченных резалт-сетов. И гораздо хуже на малых наборах строк. Ну а его «коллега-конкурент» Loop Join работает ровно наоборот (в плане эффективности). А для плана-то надо выбрать одного из них, и до того как этот план отправится на исполнение! И неверно оценив объемы входных строк оптимизатор может попытаться применить второй тип оператора к огромному (как выяснится только в ходе исполнения запроса) набору строк. А может и вообще заняться дополнительно сортировкой этого «крошечного» (по расчетам оптимизатора, но не в реальности) набора, намереваясь применить Merge Join, что будет для нашего запроса уже просто приговором. Итого, финальное резюме:

Заключение

Несмотря на то, что итоговый объем этой полновесной статьи, изначально планируемой как «базовая заметка», превысил объем планируемый в разы, автор более чем уверен, что ни его время, ни время читателей данного материала не потрачено впустую. Знание основ в деталях — тот фундамент на котором можно возводить здание с почетной вывеской «Я — SQL профессионал». Зафиксируем же основные моменты и идеи о которых мы узнали по ходу чтения:

• план исполнения состоит из «кирпичиков», или «шагов», ну или официально — операторов плана;
• почти каждый оператор имеет альтернативу, а то и не одну;
• корни наличия альтернативных операторов лежит в алгоритмах: те из них, что хорошо работают с большими массивами данных, как правило «лажают» с объемами малыми, и наоборот. Не подумайте что этот «баг» присущ исключительно SQL-миру, отнюдь. Во-первых, это не баг, а, разумеется, «фича». А во-вторых, она имеет место быть в любой IT-отрасли и направлении. Базовая-то математика одна для всех. Допустим, в том же дот-нете, который СУБД ни разу не является, есть классы-коллекции (коллекцию можно считать очень простой двухколоночной таблицей): Dictionary и ListDictionary. Казалось бы — зачем им 2 когда суть и функционал обоих практически идентичны? Да потому что производительность разная. Первый хорошо работает на коллекциях из 10 (примерно) и более строк (в дот-нете — элементы коллекции), и плохо в обратном случае. Ну а второй — с точностью до наоборот. Чувствуете аналогию?
• оптимизатор должен выбрать альтернативу наиболее релевантную сложившимся обстоятельствам, причем сделать это до исполнения того запроса под который создается план;
• отсюда — ключ к правильному выбору альтернативы заключается в корректном предсказании «объема» входящих наборов строк;
• «научное» название числа ожидаемых строк — Cardinality;
• просчитано же это число может быть или из селективности колонки (лучший вариант), или из ее плотности (худший);
• селективность и плотность колонки хранятся в одном и том же «контейнере» который называют статистикой, но в разных ее «секциях». Первая в гистограмме (HISTOGRAM), а вторая в векторе плотности (DENSITY_VECTOR); статистику можно (и нужно) создавать, обновлять, а иногда и удалять, однако чаще всего этим занимается сам сервер, освобождая нас от этой рутины;
• несмотря на то, что в целом плотность обычно дает предсказание значительно меньшей точности чем селективность у нее есть одно вполне ощутимое преимущество перед последней — плотность можно рассчитывать (и снова сохранять в «контейнере»-статистике) для сочетания значений нескольких колонок; при этом для подобной «статистики сочетанием» селективность будет рассчитана только для самого левого столбца в наборе, т.е. не будет отличаться ничем от расчета ее для этого столбца персонально;
• однако, что бы от такой плотности (рассчитанной для нескольких колонок) был реальный «профит», необходим запрос, предикат которого будет содержать или все колонки для которых был произведен расчет плотности, или их поднабор, причем последний может быть только «лево-ориентированным» по отношению к набору исходному (тому что был сохранен в статистику).

Автор благодарит всех своих читателей и надеется что чтение статьи было интересным и познавательным. Всем удачи и новых SQL-открытий, заглядывайте на наш блог!

• Другие части статьи:
• 1
• 2
• 3
• 4
• 5
• 6
• вперед »