SQL Server Технические статьи

Columnstore индексы для быстрого запроса хранилища данных, обработки в SQL Server 11.0

Автор: Эрик Николаевич ХАНСОН

Технический рецензент: цена Сьюзен

Опубликовано: ноября 2010

Применимо к: SQL Server 11.0, под кодовым названием "Denali"

Предложенная статья является машинным переводом оригинала.

Резюме: SQL Server 11.0 релиз (под кодовым названием "Denali") представляет новую функцию ускорение склад запрос данных, основанный на новый тип индекса под названием columnstore. Новый индекс в сочетании с расширенными функциями обработки запросов повышает производительность выполнения запросов к хранилищам данных в некоторых случаях в сотни и тысячи раз, в среднем же он позволяет ускорить выполнение широкого спектра запросов, необходимых для принятия решений, в десять раз. Это позволяет пользователям получать дополнительную ценность от имеющихся данных путем их быстрого и интерактивного исследования. ИТ-специалисты могут снизить затраты на разработку и время на создание ETL, поскольку индексы columnstore сокращают или даже устраняют потребность в использовании встроенных статистических функций, включая определяемые пользователем сводные таблицы и индексированные (материализованные) представления. Кроме того, индексы columnstore могут значительно улучшить производительность ROLAP, делая ROLAP более привлекательным.

Авторское право

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

Этот документ является только в информационных целях. КОРПОРАЦИЯ МАЙКРОСОФТ НЕ ДАЕТ НИКАКИХ ГАРАНТИЙ, ЭКСПРЕСС, ПОДРАЗУМЕВАЕМЫХ ИЛИ УСТАНОВЛЕННЫХ ЗАКОНОМ, В ОТНОШЕНИИ ИНФОРМАЦИИ В НАСТОЯЩЕМ ДОКУМЕНТЕ

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

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

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

© Корпорация Майкрософт, 2010. Все права защищены.

Выпуск SQL Server 11.0 (под кодовым названием "Denali") представляет новую функцию ускорения склад запрос данных, основанный на новый тип индекса под названием columnstore. Этот новый индекс, в сочетании с оптимизации и выполнения функции расширенной запрос , улучшает производительность запрос к хранилищу данных, сотниами тысяч раз в некоторых случаях и обычно может дать десятикратное увеличение скорости на широкий диапазон запросов, уместно сценарий, для которого он был разработан. Он делает все это в рамках знакомого языка запрос T-SQL и среды программирования и системы управления SQL Server. Таким образом полностью совместим с все отчетности решения, которые выполняются как клиенты SQL Server, включая SQL Server Reporting Services.

Индекс columnstore хранит каждый столбец в отдельный набор страниц диск, вместо хранения нескольких строк на странице данных традиционно был сохранен. Мы используем термин «строка магазин» для описания кучи или сбалансированного дерева, содержащего несколько строк на странице.  Разница между столбец и строк хранилища подходы иллюстрируется ниже:

Рисунок 1: сравнение между хранилища строк и столбец хранят макета данных

Столбцы C1…C6, хранятся в различных группах страниц в индекс columnstore. Преимущества этого являются:

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

Индекс columnstore в SQL Server использует запатентованную технологию Vertipaq ™ Microsoft, которую он делит с PowerPivotи SQL Server служб Analysis Services. SQL Server columnstore индексы не обязательно совпадение в основной памяти, но они могут эффективно использовать столько памяти, доступных на сервере. Части столбцов перемещаются из памяти по требованию.

SQL Server Денали columnstore индексы являются «чистыми» столбец магазинов, не гибрид, потому что они хранят все данные для отдельных столбцов на отдельных страницах. Это повышает производительность ввода/вывода и буфера хит ставки. SQL Server является первым продуктом крупные базы данных для поддержки чисто columnstore индекс. Другие утверждали, что невозможно использовать технологию чисто columnstore в созданной базе данных продукт с широкий рынок. Мы будем рады доказывают их неправильно, и мы думаем, вы будете рады, что мы сделали!

Использование Columnstore индексов

Для улучшения производительности запрос , все, что вам нужно сделать это создайте индекс columnstore по таблицы фактов в хранилище данных. Если у вас есть очень большие размеры (скажем, более чем 10 миллионов строк), вы можете развить эти измерения индекса columnstore. После этого вы просто отправить запросы на SQL Server, и они могут запустить много, гораздо быстрее.

Например мы создали хранилище данных внутренний тест 1 ТБ. Факт catalog_sales таблица в этой базе данных содержит строки 1,44 млрд. Следующее заявление было использовано для создания columnstore индекс, который включает все столбцы в таблица:

Cstore CREATE COLUMNSTORE INDEX на [dbo].[catalog_sales]

           ([cs_sold_date_sk]

           , [cs_sold_time_sk]

           , [cs_ship_date_sk]

           , [cs_bill_customer_sk]

           , [cs_bill_cdemo_sk]

           , [cs_bill_hdemo_sk]

           , [cs_bill_addr_sk]

           , [cs_ship_customer_sk]

           , [cs_ship_cdemo_sk]

           , [cs_ship_hdemo_sk]

           , [cs_ship_addr_sk]

           , [cs_call_center_sk]

           , [cs_catalog_page_sk]

           , [cs_ship_mode_sk]

           , [cs_warehouse_sk]

           , [cs_item_sk]

           , [cs_promo_sk]

           , [cs_order_number]

           , [cs_quantity]

           , [cs_wholesale_cost]

           , [cs_list_price]

           , [cs_sales_price]

           , [cs_ext_discount_amt]

           , [cs_ext_sales_price]

           , [cs_ext_wholesale_cost]

           , [cs_ext_list_price]

           , [cs_ext_tax]

           , [cs_coupon_amt]

           , [cs_ext_ship_cost]

           , [cs_net_paid]

           , [cs_net_paid_inc_tax]

           , [cs_net_paid_inc_ship]

           , [cs_net_paid_inc_ship_tax]

           [cs_net_profit])

Иллюстрация производительности

На компьютере 32-логический процессор с 256 ГБ оперативной памяти мы побежали этот запрос на предварительной сборки Denali, с и без columnstore индекс для таблицафактов:

Выберите w_city, w_state, d_year, сумма(cs_sales_price) как cs_sales_price

от склад, catalog_sales, date_dim

где w_warehouse_sk = cs_warehouse_sk

и cs_sold_date_sk = d_date_sk

и w_state в («SD»,«OH»)

and d_year in (2001,2002,2003)

group by w_city, w_state, d_year

order by d_year, w_state, w_city;

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

Этот спектакль действительно потрясающий. SQL Server columnstore индекс технология дает время вблизи мгновенное отклика для звезды соединение запрос из 1,44 млрд строк таблицана относительно экономической системы SMP. Вы могли бы пойти на кофе в то время он принимает для выполнения запрос без хранилище столбец . Это все более впечатляющим, потому что SQL Server 2008 R2 и ранее обработки запрос очень эффективного и конкурентоспособного потенциала для хранения данных, внедрив сжатия и звезда соединение запрос улучшений в SQL Server 2008.

В средах, хватает памяти при columnstore работы, набор вписывается в оперативной памяти, но хранилище строк работает набор не совпадение, легко продемонстрировать Тысячекратное speedups. Когда хранилище столбец и строки хранятся совпадение в ОЗУ, различия меньше, но, как правило, в разделе 6 X 100 X диапазон для запросов звезда соединение с группирование и агрегат. Ваши результаты, конечно будет зависеть от данных, рабочей нагрузки и оборудование.

Характеристики производительности

Обработка запрос индекс Columnstore наиболее сильно оптимизирован для звезды соединение запросов, но многие типы запросов может принести пользу. Факт факт таблица соединений и multi -столбец соединение запросы могут извлечь пользу менее columnstore индексы или не Талль. Запросы OLTP-стиля, включая точки поиска и выбирает каждый столбец строки широкий обычно не будет выполнять также с индексом columnstore с индексом B-дерева. Columnstore индексы не всегда улучшают производительность запрос к хранилищу данных. Когда они этого не делают, как правило, оптимизатор запрос будет пользоваться кучи или сбалансированного дерева для доступа к данным. Если оптимизатор выбирает columnstore индекс, когда на самом деле с помощью основной кучи или сбалансированного дерева выполняет лучше для запрос, разработчик может использовать подсказки для настройки запрос вместо этого использовать кучи или сбалансированного дерева.

Результаты сжатия

Мы наблюдали фактор 4 коэффициент 15 сжатия с разными таблицами фактов содержащие реальных данных пользователей. Columnstore это вторичный индекс; хранилище строк по-прежнему присутствует, хотя во время обработки запрос это часто не требуется и в конечном итоге быть выгружена. Columnstore кластеризованный индекс, который будет главную копию данных, планируется в будущем. Это даст значительная экономия места в дополнение к уже представленной выигрыш в производительности.

Загрузка данных

В Denali релиз, столы с columnstore индексы не могут быть обновлены с помощью INSERT, UPDATE, DELETE и MERGE заявления, или массовых операций загрузки. Чтобы переместить данные в columnstore таблица можно перейти в раздел или отключить индекс columnstore, обновления таблицаи перестроить индекс. Columnstore индексы на секционированных таблицах должны быть, выровненных по секциям. Большинство клиентов хранилища данных имеют ежедневный загрузить цикла и считать хранилище данных только для чтения в течение дня, так что они почти наверняка сможете использовать индексы columnstore.

Также можно создать представление, использующее инструкцию UNION ALL для объединения таблица с индексом столбец хранилища и обновляемые таблица без индекса columnstore в одну логическую таблица. Эта точка зрения может затем ссылаются запросы. Это позволяет динамической вставки новых данных одной логической фактов таблица сохраняя большую часть преимуществ columnstore возможности.

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

Время создания индекса для индекса columnstore наблюдались быть 2-3 раза дольше, чем время, чтобы построить кластеризованный индекс B-дерева на тех же данных на предварительной сборки. Клиенты необходимо будет учесть эту разницу во времени в ходе их процессов ETL. Однако эти клиенты обычно больше не понадобится сводные статистические выражения, которые может занять много времени, чтобы построить, поэтому на самом деле, может снизить время ETL.

Преимущества Columnstore индексов

Основным преимуществом columnstore индексы является, что они могут позволить пользователям получить намного больше стоимости бизнеса от их данных путем поощрения их к интерактивно изучить ее. Отличная производительность, столбец магазины делает это возможным. Вы можете получить время Интерактивные отклика для запросов к миллиарды строк на экономичный SMP сервер с достаточно оперативной памяти для хранения часто используемых данных.

Columnstore индексы также может снизить нагрузку на его и сократить время ETL путем снижения зависимости от встроенных сводные статистические выражения, будь то индексированные представления, определяемые пользователем сводные таблицы или Кубы OLAP. Проектирование и обслуживание агрегатов является зачастую сложных и трудоемких задач. Один columnstore индекс можно заменить десятки агрегатов. Столбец магазинов являются менее хрупким, чем агрегатов потому, что если запрос изменяется незначительно, columnstore по-прежнему может поддержать его, тогда как конкретные агрегат могут больше не быть полезным для ускорения запрос.

Пользователи, которые были с использованием систем OLAP только чтобы получить производительность быстрых запрос , но которые предпочитают использовать язык T-SQL для создания запросов, могут найти, они могут иметь один меньше движущихся частей в их среде, снижение стоимости и сложности. Пользователи, которым нравится сложные инструменты отчетности, мерного моделирования потенциал, прогнозирование объектов и языки конкретных запрос поддержки принятия решений, продолжающих предложение средства OLAP можно извлечь из них пользу. Кроме того они теперь могут иметь возможность использовать ROLAP против индексированных columnstore SQL Server хранилища данных и отвечают или превышают показатели, они были использованы в прошлом с OLAP, но сэкономить время путем устранения процесса построения куб .

Источник преимущества производительности Columnstore

Как указывалось выше, функция индекс columnstore улучшает производительность, уменьшив необходимость извлечения данных с диска в память, из-за сжатия и тот факт, что большинство запросов касаются нескольких столбцов таблица. Но это лишь один источник выигрыша в производительности. Работают несколько других собственнических методов. Эти методы будут оставаться тайной, но ясно одно-производительность, которую они оказывают весьма реальна!

Заключение

Columnstore индекс и обработки потенциал в SQL Server Denali связанного запрос являются новаторские технологии, которые дают преимущества неслыханных производительности для обработки запрос хранилища данных. Ваши конечные пользователи будут конечными пользователями; Теперь они могут получить дополнительную ценность для бизнеса от их данные в гораздо меньше времени, используя их любимый средства отчетности.