Итак, нам срочно нужна статистика по колонкам A, B и C нашей тест-таблицы. Форсируем ее создание однако, напомню, что в таблицах реальных такой процедуры умышленно делать не приходится почти никогда. Оптимизатор отлично выбирает момент когда статистика уже совершенно точно нужна и в этот самый момент ее создает. Ну а мы вынудим его сделать это прямо сейчас. Для достижения этой цели есть множество путей, часть из которых мы еще увидим в дальнейшем материале статьи. Сейчас же прибегнем к простейшему варианту:
1 2 3 | SELECT (1.0 / COUNT(DISTINCT A)) FROM TT SELECT (1.0 / COUNT(DISTINCT B)) FROM TT SELECT (1.0 / COUNT(DISTINCT C)) FROM TT |
Такой пакет из 3-х команд не только посчитает нам плотности для трех колонок (что не очень интересно, мы эти значения уже знаем), но и, самое главное, заставит движок создать требуемый набор статистики. Хороший отдельный вопрос — а можно ли в этом убедиться? Как увидеть, что статистика реально появилась да еще по всем трем колонкам? Все решаемо:
1 2 3 4 | SELECT s.name AS statistics_name,c.name AS column_name,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID('TT'); |
Данный скрипт выводит абсолютно всю статистику коей движок располагает для объекта указанного в последней строке скрипта. Допустим у автора после принудительного создания статистики предпоследним пакетом из трех команд такой скрипт показал:
_WA_Sys_00000001_117F9D94 A 1
_WA_Sys_00000002_117F9D94 B 1
_WA_Sys_00000003_117F9D94 C 1
Видим, что для каждой нашей колонки создано ровно по одной статистике. Кстати, префикс _WA в начале их (статистик) имен говорит о том, что они были созданы при посредничестве опции AUTO_CREATE_STATISTICS. Это та самая опция которая следит — можно пока еще «жить» без статистики или уже пора потратить пару-другую миллисекунд на ее создание. Она находится в состоянии ON по умолчанию и Microsoft заявляет (и можете ему поверить), что подавляющее число баз (а эта опция, кстати, выставляется на уровне отдельной БД) не требуют перевода значения этой опции в противоположное вообще никогда на протяжении всей своей жизни. А проще говоря — предоставьте эту опцию ей самой.
Итак, как только мы убедились что статистика имеет место быть — можно с полным правом начать ее просмотр командой приведенной ранее. Статистику чьи параметры мы желаем просмотреть указать можно двояко. Можно указать имя целевого столбца, и мы поступили именно так (указали A). А можно было бы заменить имя столбца именем самой статистики для того же столбца, и вместо A написать _WA_Sys_00000001_117F9D94 (см. колонку statistics_name в последнем резалт-сете чуть выше). В любом из вариантов команда DBCC SHOW_STATISTICS отработает совершенно идентично. Выход этой команды при указании ей опции DENSITY_VECTOR очень прост, состоит из трех колонок и, как правило, одной строки (правда для многоколоночной статистики число строк будет большим, но о таком продвинутом варианте статистики речь у нас впереди). Так вот колонки эти следующие:
- All Density — прошу любить и жаловать, наша плотность, ради которой весь сыр-бор и этот раздел статьи в том числе. Для колонки A вы совсем не удивитесь обнаружив здесь цифру 0.1. Впрочем и для колонок B, C, да и вообще любой цифра будет вполне предсказуемой, и мы даже знаем как ее считать «руками».
- Average Length — средний размер данных хранящихся в колонке. Выражается в байтах, а средний он для колонок переменной ширины (varchar/nvarchar/и т.д.). Для колонок чей тип фиксирован «по ширине» мы увидим здесь именно что «ширину» этого типа. Например A имеет тип int. Сколько для нее будет средняя (а на самом деле постоянная) длинна у каждого значения? Верно — 4. Что и наблюдаем. Снова, для многоколоночной статистики все не столь кристально очевидно, но не будем забегать вперед.
- Columns — имя колонки (колонок) исходной таблицы, к которой относится информация из первых двух колонок обсуждаемого резалт-сета. Для простейшей, одноколоночной статистики, лишено практического смысла. Раз мы запросили статистику для колонки A — что еще мы можем здесь увидеть кроме той же самой буквы? Приобретает гораздо большую информативность в, опять-таки, многоколоночной статистике.
Собственно и все. Разумеется «архиважной» здесь является первая колонка. Именно тут мы наблюдаем значение параметра Density. Вторые две колонки носят скорее сопутствующий характер.
Как увидеть важность параметра Density?
Хорошо, на текущий момент кажется, что значения плотности довольно важно и играет заметную роль в эффективном извлечении данных. Как нам перевести это предположение в разряд неоспоримых фактов? Очень просто. Откройте три вкладки в редакторе студии и в первой напишите код:
1 2 | SELECT COUNT(*) FROM TT GROUP BY A |
В двух других вкладках код будет полностью аналогичен, только группировку сделайте по колонкам B и C, соответственно. Не исполняя ни один из указанных скриптов вызовите для всех трех их предполагаемые планы исполнения (estimated execution plan; меню Query→Display Estimated Execution Plan). Вы увидите несложные планы исполнения примерно как на иллюстрации справа (кликабельно). Для нас интерес представляют два шага (оператора) в этих планах: Table Scan и Stream Aggregate. Первый сканирует всю таблицу (это логично, у нас нет никаких индексов по которым можно оптимизировать нахождение повторяющихся значений в колонке A), второй занимается агрегацией извлеченных строк (поскольку мы заказали группировку). «Завесьте» мышиный курсор над каждой из двух иконок и проанализируйте самый важный для нашего разговора показатель — Estimated Number of Rows (показывает сколько строк планируется получить за каждый шаг выполнения). Скажем у автора показатель для Table Scan команды группировки по столбцу A такой:
А для той же команды но шага Stream Aggregate — такой:
Проанализируем тоже самое для двух оставшихся команд и составим такую табличку:
группировка по столбцу A 10 10
группировка по столбцу B 10 5
группировка по столбцу C 10 2
Что мы видим? Что изначально, при любой группировке, извлекаются все и каждая из 10-ти имеющихся строк. Тут ничего удивительного. А вот дальше, когда дело до группировки доходит, оптимизатор на удивление точно предсказывает число строк которое мы увидим в выходном резалт-сете. Шаман? 
Ведь код-то не исполнялся! Разумеется, вы все уже поняли — оптимизатор (а он у нас очень, очень умный) «сгонял» за статистикой и посмотрел на уже полюбившуюся вам плотность, которая Density. Собственно говоря, число в колонке шаг Stream Aggregate из таблицы выше есть ничто иное, как U(A/B/C) подсчитанные нами самими чуть ранее, в первой части статьи. А так ли уж важно оптимизатору правильно знать это число — предполагаемое количество строк — на каждом из шагов плана? Важно до чрезвычайности! Потому что методы работы (те самые шаги-операторы из плана, точнее их типы) отлично показывающие себя для 20-ти строк, никуда не годятся для работы с 20-ю тыс. строк. И, разумеется, наоборот — что отлично работает с большими массивами, будет крайне неэффективно при массивах малых. Как вы увидите в самом конце данной статьи, все что мы обсудили до этого момента и продолжим обсуждать далее направлено и предназначено для одной простой (на первый взгляд) цели: из списка альтернативных операторов для данного шага плана выбрать наиболее «правильный», т.е. эффективный.
Что если колонок больше одной?
Мы довольно подробно рассмотрели вычисление и работу с плотностью в ее приложении к одной отдельно взятой колонке. А что если у нас сочетание колонок? К такому набору этот термин применим? Используется ли он в таком случае? Если вы по аналогии с индексами (которые уж совершенно точно прекрасно себя чувствуют в многоколоночном окружении) предположите что, должно быть, можно создавать и многоколоночную статистику (а уж в ней, разумеется, будет и плотность в том числе) — то окажетесь совершенно правы, многоколоночная статистика в MS SQL сервере в полном порядке. Однако тут будут свои тонкости и нюансы с которыми и попробуем разобраться.
Для начала — освежим узнанное ранее: каков физический смысл плотности для одной колонки? Верно: мера оценки не уникального «контента» этой самой колонки. Зачем нужна плотность оптимизатору? Снова верно: чтобы на каждом шаге исполнения плана делать адекватные предположения о том сколько строк еще не отброшены условиями фильтрации/группировки/соединения и, таким образом, продолжают быть «в работе». Мы уже видели как четко, без выполнения кода, оптимизатор предсказал что при группировке по колонке C на выходе у нас будет всего 2 строки. А что если мы будем группировать по колонкам C и A? Сможет ли оптимизатор снова показать чудеса прозорливости? Увы — нет, тут из него «астролог» так себе получится. А все потому, что никто не удосужился измерить степень не уникальности сочетания значений этих двух колонок. Иными словами у нас (и уж тем более у оптимизатора) нет цифры для D(C,A) (плотность двух колонок). Ну а может он будет столь любезен, что после выполнения запроса с группировкой по нескольким колонкам автоматически создаст требуемую многоколоночную статистику? Опция упомянутая ранее — AUTO_CREATE_STATISTICS, не может ли и тут проявить себя? И снова увы — вот тут-то эта опция будет бессильна. А все потому, что автоматически создается статистика по отдельным столбцам (и только по отдельным!). Т.е. если у вас будет SELECT с группировкой по B, C, A то статистика-то создана будет (если этого не произошло ранее), но это будет не статистика сочетания, а три независимых статистики по отдельным колонкам. Ровно те же 3 статистики что мы видели ранее с именами начинающимися с префикса _WA в начале их имен. Что, конечно же, устроить нас никоим образом не может: плотности по сочетанию значений как не было, так и нет. А как же быть? А очень просто: взять «управление на себя», т.е. именно форсировать создание подобной многоколоночной статистики — вот здесь уже действительно иных вариантов не остается. Сделать это можно двумя путями:
- создать индекс в который войдут интересующие нас колонки. В этом случае статистика создается вместе с индексом и нам беспокоиться просто не о чем;
- если индекса нет и создавать его в обозримом будущем мы не планируем — к нашим услугам команда CREATE STATISTICS, которой мы и воспользуемся прямо сейчас.
Дабы начать эксперимент с «чистого листа» «дропните» нашу тест-таблицу:
1 | DROP TABLE TT |
Пересоздайте ее и вновь наполните содержимым. Теперь строк будет не 10, а 14:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | create table TT (A int, B int, C char(2)) GO INSERT INTO TT VALUES (1, 501, 'QQ') INSERT INTO TT VALUES (2, 501, 'RR') INSERT INTO TT VALUES (3, 503, 'QQ') INSERT INTO TT VALUES (4, 503, 'RR') INSERT INTO TT VALUES (5, 505, 'QQ') INSERT INTO TT VALUES (6, 505, 'RR') INSERT INTO TT VALUES (7, 507, 'QQ') INSERT INTO TT VALUES (8, 507, 'RR') INSERT INTO TT VALUES (9, 509, 'QQ') INSERT INTO TT VALUES (10, 509, 'RR') INSERT INTO TT VALUES (98, 888, 'VV') INSERT INTO TT VALUES (99, 888, 'VV') INSERT INTO TT VALUES (98, 999, 'VV') INSERT INTO TT VALUES (99, 999, 'VV') GO |
А целью нашей будет оптимизация работы команды выполняющей группировку сразу по трем столбцам:
1 2 | SELECT COUNT(*) FROM TT GROUP BY B,C,A |
Выполните этот последний «много-группировочный» запрос, а затем уже знакомым нам скриптом запросите всю статистику имеющуюся для таблицы TT. Как вы можете видеть — все в точности как я и обещал: три независимые статистики никоим образом не способные помочь оптимизации последнего запроса. Что бы окончательно в этом убедиться вызовите предполагаемый план выполнения для этой команды и в шаге Stream Aggregate посмотрите число Estimated Number of Rows. В случае наличия оптимальной статистики там бы было 14 ровно (у нас нет ни одного дубликата в сочетании значений всех колонок). А там, скорей всего, будет 13 и какие-то цифры после точки. Это потому, что оптимизатор «гадает», раз уж мы не удосужились предоставить ему точную плотность. Кстати — а как он этим «гаданием» занимается? А вот как: у него есть D(A)=1/12=0.08333334 (можете увидеть эту цифру командой DBCC SHOW_STATISTICS, как мы это делали ранее). Стало быть, рассуждает наш умный оптимизатор, даже при группировке только по A число строк будет как минимум 1/0.08333334=12. Ну а каждое новое сочетание лишь понижает плотность увеличивая число возвращаемых строк. Таким образом вывод оптимизатора: число строк будет 12-14 (всего строк в таблице 14), причем гораздо ближе к верхней границе поскольку и B, и C достаточно уникальны даже сами по себе (снова оптимизатор смотрит их плотности), а уж в сочетании... Но это легко «умничать» когда диапазон 12-14, а когда он станет 12-140 000? То-то и оно...
Что ж, давайте создадим «статистику сочетанием» вот такой простой командой:
1 | CREATE STATISTICS B_C_A ON TT(B,C,A) |
Вновь все тем же скриптом запросите всю статистику имеющуюся теперь для таблицы TT. Вы увидите примерно следующее:
1 2 3 4 5 6 7 | statistics_name column_name stats_column_id _WA_Sys_00000003_0EA330E9 C 1 _WA_Sys_00000002_0EA330E9 B 1 _WA_Sys_00000001_0EA330E9 A 1 B_C_A B 1 B_C_A C 2 B_C_A A 3 |
Ну строки с префиксом _WA нам уже знакомы, а вот три других... По названию статистики понятно что эта та самая «статистика сочетанием», но выглядит все вместе так, будто бы для каждой отдельной колонки просто был сделан двойной расчет: автоматически и принудительно. Однако цифры колонки stats_column_id последнего резалт-сета говорят нам что мы, в случае статистики B_C_A, имеем дело именно с набором столбцов. А не с каждым столбцом в отдельности. И более того — мы имеем дело с набором статистик, это вдобавок к набору столбцов. Не понятно? Сейчас все разъяснится.