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

Не знаю как у вас, дорогие читатели блога, а у вашего автора (т.е. меня) сейчас довольно большие планы по наполнению сайта на котором вы находитесь содержимым (чаще тут употребляют модное нынче слово «контент»). Уже имеется целый ряд статей и заметок, часть из которых почти завершена и требует лишь веб-обработки для публикации, другая (и большая) часть завершена наполовину, и совсем уж огромный список тем находится в состоянии «есть идея» и «а вот еще можно обсудить». Очередная идея была такой: если окинуть самым общим взглядом все мои ближайшие планы (коих громадье, как вы уже поняли) — нет ли там какого «общего знаменателя»? Т.е. не будет ли каких-то терминов, понятий, определений и т.п. на которые я буду ссылаться снова, и снова, и снова и (на всякий случай) пояснять значение каждого из них (тоже, соответственно, снова и снова)? А если таковые есть — не будет ли грамотным шагом вынести их (плюс короткие поясняющие примеры) в отдельные заметки (может и одну, может и больше — это уж насколько у автора хватит способностей к выделению общего из частного), а в статьях узкоспециализированных, посвященных тому или иному конкретному вопросу, уже давать эти термины-понятия-определения вообще без всяких пояснений, но оформлять как ссылки на такие вот «базовые заметки»? Судя по тому, что от зарождения идеи до формирования заголовка первой из таких заметок прошло буквально полминуты идея кажется очень, очень здравой. Итак, сегодня мы рассмотрим трех, не побоюсь этого слова, «китов» оказывающих громадное влияние на буквально каждую операцию выполняемую движком сервера. Вот они: Density, Selectivity и, конечно же, Cardinality.

Прежде всего — действительно ли эти «очень важные вещи» столь важны, что заслуживают ваших часа-полутора внимания? Даже не сомневайтесь, несомненно заслуживают и значительно большего времени, если оно вам потребуется (а если вы будете аккуратно выполнять все упражнения всех частей этой статьи, отвечать на проверочные вопросы, «вникать» в материал, то рассчитывайте что вам нужно иметь в распоряжении часа 2.5-3 на полную статью; с другой стороны — вовсе не обязательно осваивать материал всех частей разом, вполне можно составить расписание занятий типа «одна часть в день», для того статья на них и разбита). Так вот по поводу «большой важности» упомянутых выше концепций:

• какое влияние оказывают они на работу Query Optimizer-а (оптимизатора запросов)? Да определяющее! Именно исходя из значений этих сущностей оптимизатор сформирует или очень хороший план или никуда не годный;
• должны ли они учитываться при построении индекса на существующей таблице? Да в первую очередь! Например многим известен совет: «при создании многоколоночных индексов указывайте первой ту, что содержит наибольшее количество уникальных значений». А откуда взялся этот совет? Почему именно так, а не иначе? А если у меня 3 колонки равны по этому параметру и все должны быть включены в индекс — какую указать первой? И если этот совет верен — не включать ли в любой составной индекс первой колонкой колонку первичного ключа, ибо в ней гарантировано наибольшее из возможных уникальных значений? Наши сегодняшние герои если и не отвечают впрямую на все эти вопросы и на ряд сопутствующих, то определенно указывают «вектор» к этим ответам приводящий;
• а какое отношение они имеют к такой важной вещи как статистика (statistics), о которой вы, несомненно, как минимум слышали и знаете что это «что-то очень важное/нужное»? Да очень простое — они и есть эта самая статистика. OK, хорошо, в статистике еще есть вещи обслуживающие ее инфраструктуру (различные методики обновления, довольно продвинутые алгоритмы определения ее актуальности и т.д.), но несомненно что ядро статистики, ее информационная составляющая, именно они;
• наконец, то что они мгновенно проявились как кандидаты в заметку, на которую автор планирует ссылатся в будущем из многих новых (да и существующих) своих статей — тоже о чем то, да говорит, не правда ли?

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

Density

Итак — плотность записей или просто Density. Если говорить о простейшей ее разновидности — плотности значений в одной колонке (а как будет показано далее можно измерять и совокупную плотность значений нескольких колонок), то все несложно: плотность есть ни что иное как показатель частоты обнаружения дублирующихся (не уникальных, иными словами) значений в анализируемой колонке. Больше дубликатов — выше плотность. Т.е. «физическая» (как я ее условно называю) формула вычисления плотности (обозначим ее, кстати, просто D) будет:

D = Число дубликатов в колонке / Общее число записей в таблице

На практике же почти всегда применяют иную, «практическую» (как я, опять же, ее называю) формулу:

D = 1/Число УЗ в колонке,

где УЗ есть такие значения, которые встречаются в колонке ровно единожды (уникальные значения). Только обратите внимание — не «строки с таким уникальным значением встречаются единожды», а «само значение встречаются единожды» — это разные вещи! Если у нас в таблице товаров, всего 50 из них имеют цену 11.95, а остальные имеют цену иную, то при «ручном» подсчете плотности для колонки Price мы прибавляем знаменателю (УЗ) 1, а не 50. Для цены 45.50 мы добавляем еще 1, не важно единственный ли у нас товар по такой цене или их сотни. Вот число таких УЗ подсчитывают и берут обратную величину. «Практичная» формула практична потому, что переводится в элементарный T-SQL запрос:

где [ColumnName] — имя колонки плотность значений которой мы желаем узнать, [TableName] — имя таблицы к коей данная колонка относится. Как видите все действительно очень даже практично.

Итак, еще раз физический смысл значения параметра Density:

Правило «большого пальца»: движок сервера «любит» таблицы с колонками с низким Density (ему больше по нраву уникальные значения) и «не любит» колонки с высоким значением этого параметра (т.к. это не позволяет одним махом избавляться от большого числа «лишних», для данного запроса, строк). Однако, даже не столь важно что там ему нравится, а что нет (поскольку мы практически не можем влиять на характеристику повторяемости данных вносимых пользователями в готовую систему), сколь важно поддерживать актуальное значение этого параметра. Ибо он отнюдь не вычисляется при каждом запросе (а равно и модификации) данных, а хранится как объект известный под названием статистика. Та самая, что была упомянута в вводной части заметки. Правильное создание статистики и, особенно, ее обновление является отдельной, большой и интересной темой лежащей полностью за рамками этой базовой статьи. Пока же резюмируем: не столь важно добиваться низких значений плотности (хотя если путь к этому более чем очевиден и может быть реализован одной командой — делайте, конечно), как важно поддерживать это значение в актуальном состоянии! Помните, что плотность меняется также легко как и сами данные, только происходит это неявно.

Теперь, как говаривал тот крот из мультфильма — «А не посчитать ли нам»? Сейчас самое для этого время. Вот наша «тест-таблица»:

Все предельно просто — 10 строк, 3 колонки. Можете ли вы в уме вычислить плотность для столбца A? Не сомневаюсь что да. Очевидно в этом столбце 10 значений и все — уникальны. Плотность, соответственно, равна 1/10=0.1. По аналогии — плотность B? D(B)=1/5=0.2. Ну и для комплекта D(C)=1/2=0.5. А теперь такой вопрос: если, как мы только что посчитали, D(B)=0.2, то как имея эту цифру на руках посчитать количество уникальных значений в той же колонке (обозначим такой параметр U(B))? Иными словами, сколько не повторяющихся значений имеется в колонке B? Посчитали? Только помните, для этого задания у нас есть исключительно значение плотности (0.2), код вставки строк мы как бы временно забыли! Действительно, если как мы уже знаем D(B)=1/U(B), то, надо полагать, U(B)=1/D(B), и знать реальные значения в столбце для ответа на вопрос нам ни к чему. Получается, что уникальных значений в B будет 1/0.2=5 — ровно 5 штук, согласны? Проверьте этот факт по скрипту создания таблицы, а заодно просчитайте U(A) и U(C). Если все получилось — смело двигаемся к вопросу номер 2: имея на руках снова лишь значение плотности (D(B)=0.2) ответить на вопрос о количестве дубликатов в таблице. Точнее вопрос спрашивает нас вот о чем: сколько в каждой группе дубликатов членов в среднем? Снова не ясно? OK — пример: некая таблица из одной колонки содержит в ней значения вида

Тогда в группе дубликатов со значением 100 — 4 члена, 101 — 2 члена, 102 — 3 члена. А в среднем, сколько дубликатов на таблицу? Очевидно (4+2+3)/3=3. Итого, ответ: в такой таблице в среднем каждое значение дублируется 3 раза. Вот эту самую тройку (или уж сколько там получится) нам теперь нужно вычислить для столбца B нашей тест-таблицы не видя исходных строк и зная только значение плотности этой самой колонки. Вопрос посложнее предыдущего и с небольшим подвохом... Подумайте! Получилось? Подвох же заключался в том, что только знание о плотности не позволяет ответить на вопрос, хотя сами данные по прежнему не нужны для корректного ответа. А нужный второй параметр — общее количество строк в таблице. Какие именно они данные содержат — без разницы, произведение плотности и этого второго параметра даст нам нужную цифру. Итого, если обозначить среднее количество дубликатов столбца B как AVG_DBL(B), и принять во внимание, что всего в тест-таблице 10 строк, то AVG_DBL(B)=0.2*10=2. Каждое значение в столбце B повторяется по 2 раза — согласны? Как насчет просчитать (и проверить по факту!) AVG_DBL для A и C, справитесь?

Теперь — снова к плотности. Мы посчитали ее для всех 3-х столбцов таблицы «на листочке». Как сделать тоже самое запросом в студии? С учетом что ответ уже фактически приведен в начале данного раздела и требует лишь адаптации под конкретную таблицу/колонку — сразу же код:

Верю, что код для двух других колонок вы сможете написать сами. Просто убедитесь, ради интереса, что «ручные» и «студийные» результаты совпадают, разумеется, с точностью до любой цифры после запятой. Гораздо более интересный вопрос к автору — если Density это, как он обещал, такое прям «ядро статистики», то нельзя ли его увидеть непосредственно в этой самой статистике, без каких-либо вычислений, хоть прямых, хоть косвенных? Можно, уверяю вас! И прямо сейчас мы этим и займемся.

Прежде всего — какая команда позволит нам эту статистику увидеть? Команда не сложна:

Здесь TT и A — имена таблицы и колонки по которой мы хотим увидеть статистику (помните, что значение статистики вообще и плотности в частности будет своим для каждой колонки). Ну а DENSITY_VECTOR — опция команды DBCC SHOW_STATISTICS сообщающая последней что мы желаем видеть не всю статистику, а лишь тот ее «срез», который поведет речь именно о плотности и ни о чем ином. Однако не торопитесь ее запускать! Скажем если вы попробуете это сделать сразу же после вставки 10 новых строк (до каких-либо обращений к таблице) вы получите:

Если вы попробуете это сделать после вставки и выполнения команды вида

то получите ту же самую ошибку 2767. А вот если вы после вставки выполните запрос вида

или даже код, который вы, скорей всего, уже выполнили:

то вы получите вполне себе приятный отчет по требуемому срезу статистики. Как несложно догадаться, для колонки B история повторяется по второму кругу, для C — по третьему. Почему так? Это все потому, что DBCC SHOW_STATISTICS показывает существующую (иными словами — созданную и/или модифицированную до момента вызова этой команды) статистику. Если статистики еще нет (не создавалась) — чего еще можно ожидать? Error 2767, будьте любезны. Почему же некоторые запросы приводят к возникновению статистики, а другие, хоть и похожие, этот механизм не запускают? Это снова та самая «большая-интересная-но-отдельная» тема коей я обещал в этой «базовой заметке» не касаться. Но уж совсем одним словом — статистика создается тогда, когда движок понимает, что дальше так (без статистики) «жить нельзя». И, как вы только что могли убедиться, момент этот может быть довольно-таки удален по временной шкале от точки в которой была создана таблица. Т.е. вполне может произойти десятка полтора запросов/модификаций таблицы, а статистики по одной (или даже всем) из колонок все еще может не быть. В целом — это совершенно нормальное поведение и почти никогда не требуется показывать движку «кто здесь за главного» форсируя создание статистики. Но в данном случае нам, просто что бы двигаться дальше по нашему примеру, статистика нужна именно «здесь и сейчас», и причем по всем трем колонкам нашей тест-таблицы.

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