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

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

Чем больше значение параметра Selectivity, тем меньше избирательность (число отбрасываемых строк, т.е. тех, что не попадут в итоговый резалт-сет) данного предиката для данной колонки. Предикат селективности 0.883 вернет больше строк, чем он же с селективностью 0.001, т.к. в первом случае избирательность меньше. Понимаю, что фраза «чем больше селективность, тем меньше избирательность», с учетом того что оба термина являются эквивалентным переводом слова Selectivity, звучит примерно как «чем мощнее процессор, тем медленней работает компьютер». И при всем при этом фраза — верна! Ну да, у нас тут тоже свои парадоксы, знаете ли... И еще, имейте в виду при чтении литературы/форумов/статей/и т.п., что там выражение high level of selectivity/хорошая селективность (обычно относящееся к тому или иному индексу, но иногда и к предикату, как в нашем примере) обычно означает ситуацию когда в выходной резалт-сет попадает мало записей. Т.е. авторы имеют в виду не селективность (которая чем выше — тем хуже), а как раз избирательность (которая наоборот). «Засада» тут в том, что «математическое» и «бытовое» значение одного и того же слова селективность прямо противоположна. Математически, чем она больше тем больше и число строк на «выходе». А в бытовом, интуитивном плане, селективный — синоним разборчивости, придирчивости. И чем более «придирчивый» индекс/предикат, тем меньше у него строк на «выходе».

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

Мы уже знаем, что раздел статистики хранящий значения плотностей колонок называется DENSITY_VECTOR. Именно этот параметр мы указываем в команде DBCC SHOW_STATISTICS дабы просмотреть плотность интересующей нас колонки. Селективности же отдан другой раздел статистики называемый гистограммой. Что бы увидеть значения из этого раздела параметр той же команды меняется на HISTOGRAM. Давайте посмотрим как все это выглядит на практике.

Если у вас от прошлых экспериментов сохранилась наша тест-таблица TT — «дропайте» ее сейчас. Создадим таблицу с тем же именем, но иной структуры и своими данными:

На этот раз колонка будет всего одна, зато записей в ней будет целых 10 тысяч. Каждая запись (т.е. каждая ячейка таблицы) представляет собой строку из четырех символов следующего вида:

Т.е. все возможные сочетания префиксов в диапазоне A_-L_ и суффиксов в диапазоне 0-99 через подчеркивание. А всего 10(префиксов)*100(суффиксов)=1000 строк, причем каждая из них уникальна. И еще дополнительно эти 1000 строк дублируются 10 раз. По итогу у нас в колонке X будет, как уже упоминалось, 10'000 записей всего и среди них: 10 записей значением 'A_0', 10 записей 'F_21', 10 записей 'B_68', 10 записей 'D_99' и т.д. вплоть до 10 записей 'L_99' . Надеюсь вы «ухватили» шаблон генерации данных.

Хорошо, сгенерируем теперь статистику для нашей единственной колонки. Снова не станем прибегать к индексам, а напишем команду:

Т.е. создадим статистику по имени X для одноименной же колонки таблицы TT. Обратите внимание, что статистика всегда создается вся, т.е. у нас нет возможности (да и необходимости тоже) заказать просчет и сохранение значения плотности без просчета селективности, равно как и наоборот. Так же и в нашем примере — в статистике по имени X хранятся и плотность (которую вы можете посмотреть самостоятельно, правильный параметр команды DBCC SHOW_STATISTICS вам известен), и селективность, к анализу значений которой мы приступаем:

Полученный резалт-сет (только первые 5 записей):

Наверно, будет небезынтересно выяснить — а почему этот раздел статистики назвали красивым словом гистограмма? Ведь это, кажется, такой интересный график, только не с ломанной линией, а со столбиками разной высоты? Действительно, гистограмма показывает насколько часто попадает тот или иной элемент (в нашем случае — значение колонки) в соответствующий интервал группировки (в нашем случае «интервал»=шаг гистограммы, термин, определение для которого вы найдете ниже). Это не только похоже на смысл определения понятия селективность — последний резалт-сет (содержащий, как выяснится опять-таки далее, ее числовое выражение) без проблем отрисовывается в самую настоящую графическую гистограмму. Жаль только делать это придется «ручками», встроенного механизма перевода табличных значений селективности в реальную гистограмму нет. Как бы там ни было — вот вам графическое представление первых пяти записей последнего резалт-сета:

Итак, как же все это получилось и как все эти значения можно интерпретировать? Прежде всего — обсуждаемая гистограмма строится на базе т.н. опорных точек. Опорная точка — это отобранное значение из колонки для которой создается гистограмма. Т.е. сначала все значения колонки упорядочиваются по возрастанию, а затем неким алгоритмом некоторые из них назначаются опорными. Каждая последующая опорная точка имеет значение больше предыдущей (т.е. совпадающие опорные точки исключены), а «некий алгоритм» пытается избегнуть ситуации, когда в одном месте колонки опорных точек будет «густо», а в другом — «пусто», т.е. пытается распределить их более-менее равномерно по всему диапазону значений колонки. Значения назначенные опорными копируются в статистику «как они есть». Это объясняет почему отказались от идеи сделать каждое уникальное значение колонки опорным. Если представить себе таблицу из одной колонки с ограничением типа первичного ключа содержащего 4 млн. записей, то при реализации такой идеи статистика по такой таблице занимала бы место больше, чем сама таблица (все значения такой колонки+аналитика по ним). Что, мягко говоря, не умно. А поэтому приняли такое волевое решение: сколь много бы уникальных значений не содержала колонка число опорных точек для нее никогда не будет больше 200. Например, у нас в таблице TT уникальных значений 1000, а число записей в последнем резалт-сете (который мы сейчас обсуждаем) — где-то 197-200. Т.е. очень близко к 200-м, но точно не более этой цифры. Но, кстати, если исходная колонка будет содержать менее двухсот уникальных значений, то вполне реально, что каждое из них станет опорным (хотя это и не гарантируется, с другой стороны; например имея всего 10 уникальных значений в колонке движок запросто может назначить опорными лишь каждое второе), и такая статистика будет наиболее точно отражать распределение данных в колонке. При превышении этого числа неизбежна некоторая усредненность и, как следствие, потеря точности в статистике с которой приходится мириться.

Так вот, из резалт-сета мы видим, что первые 5 опорных точек выбранные движком сервера из нашей таблицы это A_0, A_15, A_19, A_22, A_26. На графической гистограмме они отложены по оси X, на которой расположились вообще все уникальные значения колонки X. На графике они выделены более крупным шрифтом и цветом. Обычным цветом и стандартным шрифтом показаны значения не попавшие в список опорных, но присутствующие в колонке. Помните, что построение гистограммы движок начинает с упорядочивания всех значений колонки по возрастанию, как это отражено на графике. Поскольку X имеет строковый тип, то значение 'A_19' будет впереди 'A_2', т.к. код символа 1 меньше. Так же отметьте себе, что расстояние между двумя ближайшими опорными точками называется шагом гистограммы. Как вы уже несомненно заметили, первая же колонка анализируемого нами в данный момент резалт-сета, а именно — RANGE_HI_KEY, содержит не что иное, как значения опорных точек. Проанализируем прочие колонки того же резалт-сета:

• RANGE_ROWS — ориентировочное число строк колонки, значения которых находятся в пределах соответствующего шага гистограммы и не являются опорными. Звучит убийственно, а на самом деле не так страшно. Смотрите, берем, для примера шаг гистограммы A_0-A_15. Какие в пределах этого шага имеются обычные (не опорные) значения? Очевидно (см. график): A_1, A_10, A_11, A_12, A_13, A_14. Нарисуем от каждого из этих значений зеленый пунктирный столбик «вверх», по оси Y. Высота столбика равна числу строк с таким значением в колонке. Ось Y у нас вообще по своему происхождению обязана показывать количество строк со значением взятым со оси X (т.е. короткое правило: X—само значение, Y—число таких значений). Поскольку у нас в таблице TT любого значения ровно 10 зеленые столбики получились уж очень «прямоугольно-причесанными». В реальной таблице тут будет полный разброд: тут вам и столбики крошечные, и «нормальной» высоты, и гиганты уходящие в совсем уж неведомые выси... Ну а RANGE_ROWS есть суть сумма высот всех этих зеленых столбцов. Для нашего примера: 6(промежуточные значения внутри шага)*10(число строк с каждым из этих значений)=60. Сходится? Осталось прояснить слово «ориентировочное» в определении колонки. Разумеется, в момент построения статистики число здесь будет абсолютно точное. Все-равно каждое значение колонки надо оценить, дабы правильно отсортировать его, так что не посчитать его заодно было бы непросительным разгильдяйством. Действительно, как видите цифра верна «до копейки». НО! Что если спустя 0.1 сек после создания статистики мы вставим еще одно значение A_12? Да ничего! Цифра этой колонки как была 60, так и останется. А по факту-то там должно быть 61. А когда это изменение случится? Правильно — когда статистика будет обновлена любым способом. А какие способы для этого есть? Ну мы же договорились!
• EQ_ROWS — ориентировочное число строк колонки, значения которых совпадают с данным опорным значением. Ну это совсем просто — высота оранжевого столбика на графике, т.е. сколько строк содержат именно такое опорное значение. Снова у нас слишком все красиво (опорных значений у нас снова по 10 каждого), а в реальности... Вы понимаете. И слово «ориентировочное» тоже не стало откровением, мы могли вставить A_12, как в предыдущем абзаце, но ведь могли и A_15.
• DISTINCT_RANGE_ROWS — ориентировочное число значений лежащих в пределах соответствующего шага гистограммы и не являющихся опорными. Хм... звучит как дубликат определения колонки RANGE_ROWS чуть выше? Тогда читайте внимательней, я не зря выделил слово «значений» жирным да еще и подчеркнул его. Там — высота зеленых столбиков, а тут — их количество. Например, в том же шаге A_0-A_15 — сколько промежуточных значений? Правильно, уже считали и буквально только что — 6 их. Сходится? Кстати, не могу не поделиться своим наблюдением. Я давно подозреваю что в Microsoft сидит глубоко законспирированный отдел, сотрудники которого строго следят за тем, что бы работникам IT-отрасли (т.е. нам с вами и нашим коллегам во всех странах) жизнь малиной не казалась. И предпринимают соответствующие шаги к этому. Вот, опять! Ну зачем было в название этой колонки фигачить суффикс _ROWS?! Ну не считаются тут строки, НЕ СЧИ-ТА-ЮТ-СЯ! _VALUES должен быть суффикс!
• AVG_RANGE_ROWS — среднее число строк колонки, значения которых находятся в пределах соответствующего шага гистограммы и не являются опорными. А по факту — средняя высота всех зеленых столбцов в пределах данного шага гистограммы. Помните, мы говорили что в реальных таблицах у нас будут промежуточные значения и очень скромные по своему количеству, и «нормальные», и «гиганты»... Рассматриваемая колонка проводит единую горизонтальную линию (на нашем графике — синий пунктир), усредняя все эти высоты. «Уровень» этой линии будет, конечно, свой для каждого шага (у нас, опять-таки, слишком искусственный случай). Однако, отметьте, что в пределах шага это будет именно горизонтальная линия, т.е. одно конкретное число по оси Y. Кстати, при наличии значений из предыдущих колонок формула расчета элементарна: AVG_RANGE_ROWS = RANGE_ROWS/DISTINCT_RANGE_ROWS. А «по-русски»: (сумма высот столбцов)/(количество этих столбцов). Получаем как раз среднюю высоту или среднее количество промежуточных значений. Еще говорят, что значение этой колонки отражает плотность шага (step density), поскольку чем больше это самое среднее количество промежуточных значений — тем более «плотно» идут записи в данном шаге, тем их элементарно больше числом.

Осталось разобраться, что будет в последней из колонок, AVG_RANGE_ROWS, если у нас нет ни одного промежуточного значения. Например в колонке X таблицы TT могло бы быть 5 (всего) уникальных значений и все бы были назначены опорными. Тогда DISTINCT_RANGE_ROWS (число зеленых столбцов) будет, очевидно, 0 для всех шагов и расчет AVG_RANGE_ROWS выглядит несколько проблематичным. С учетом, что при отсутствии самих столбцов сумма их высот (читай — RANGE_ROWS) будет, очевидно, тоже 0, то мы и вовсе получаем этакий дзенский коан, открытый для постижения пытливому разуму: AVG_RANGE_ROWS = 0/0 = ??? Создатели движка решили этот коан легко и изящно, проставив для подобной ситуации в эту колонку вполне формальную единицу. Впрочем, с тем же успехом они могли поставить и -1, и 5000. Если у нас в шаге нет промежуточных значений практический смысл имеет лишь одна колонка — EQ_ROWS, т.е. число опорных значений.

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