Что нужно учесть выбирая поколоночный тип хранения данных
SAP HANA: Что такое полноценная in-memory?
Принципиальное отличие настоящих систем вычислений в оперативной памяти (in-memory) как нельзя лучше сформулировал ведущий аналитик Gartner Дональд Фейнберг: база данных in-memory не должна хранить данные на диске ни для каких целей, кроме обеспечения надежности системы, – все операции должны проводиться исключительно в оперативной памяти. Именно этому принципу и следовали разработчики при создании SAP HANA – база данных in-memory хранит информацию, проводит обработку всех запросов и осуществляет расчеты исключительно в оперативной памяти, используя диски только для логирования и хранения «снимков системы» с целью ее быстрого и полного восстановления. Это технологическое преимущество SAP HANA заметно отличает ее от большинства систем, существующих сегодня на рынке баз данных in-memory.
В отличие от традиционных баз данных, создававшихся для работы с жестким диском, в SAP HANA вся оптимизация работы системы изначально была направлена на взаимодействие оперативной памяти и процессора. При этом используется поколоночный метод хранения данных, который помимо высокой скорости чтения информации дает возможность эффективно применять механизмы компрессии данных. Таким образом, SAP HANA работает непосредственно со сжатыми данными, не тратя ресурсы на процесс разархивирования.
Новый подход позволяет снизить объемы хранимой в системе информации. Это необходимо учитывать при оценке миграции с традиционных баз данных на платформу вычислений в оперативной памяти SAP HANA. В ряде проведенных в СНГ проектов коэффициент сжатия достигал 15–20 раз. Ярким примером эффективности технологии стал проект, проведенный в крупном телеком-операторе стран СНГ, в рамках которого была достигнута компрессия базы данных с операциями клиентов в 30 раз, что позволило сильно сократить стоимость всего проекта.
Дополнительное снижение объемов хранимой информации происходит за счет удаления из системы индексов и агрегатов, что стало возможным благодаря внутренней структуре поколоночного хранения и реализованным функциям эффективного агрегирования данных «на лету». Благодаря такому подходу пользователи систем, функционирующих на SAP HANA, могут строить аналитические запросы и работать на самом детальном уровне информации, что повышает точность и качество принимаемых решений. Все эти механизмы позволяют кардинально сократить общую стоимость владения и резко повышают привлекательность использования технологий in-memory в ИТ-ландшафте компании.
Традиционно, недостатком поколоночного хранения является низкая скорость записи данных. Это связано с тем, что для корректного внесения информации в систему необходимо большое число операций. В SAP HANA эта проблема решена. Для этого был создан механизм Delta Storage. Он позволяет моментально вносить поступающую информацию в систему и учитывать ее в запросах и расчетах. При этом перенос поступившей в поколоночное хранение информации происходит позже в фоновом режиме. Такой подход позволяет применить технологию хранения данных в оперативной памяти SAP HANA не только для аналитических, но и для транзакционных систем. В итоге производительность растет в десятки раз, а с ней повышается и эффективность работы бизнес-пользователей.
Поколоночное хранение и Delta Storage являются лишь основой методики хранения данных в оперативной памяти, применяемой в SAP HANA. На базе используемых в SAP HANA технологий в Hasso Platner Institute (Германия) проводится полугодовой курс бесплатных онлайн-лекций, в рамках которого можно подробнее узнать о теории хранения и обработки информации in-memory.
Новые грани бизнес-аналитики
Размещение всей хранимой в SAP HANA информации в оперативной памяти дает возможность эффективно проводить вычисления непосредственно в базе данных. Предприятия различных индустрий накопили и продолжают накапливать большие объемы разнородных данных, в том числе и неструктурированных. Возникают совершенно новые задачи обработки информации с целью извлечения выгоды для бизнеса и приобретения новых конкурентных преимуществ.
В SAP HANA реализован богатый набор возможностей для работы с данными. Он включает библиотеку функции PAL (Predictive Analytics Library) для задач прогнозной аналитики, библиотеку бизнес-функций BFL (Business Function Library), функции по обработке неструктурированных текстов Text Analysis, набор функций по работе с геолокационной информацией, а также ряд других встроенных модулей. Все эти функции могут быть использованы для создания моделей хранения и обработки данных и написания внутренних хранимых процедур на специализированном языке SQLScript. Такой подход дает возможность провести всю необходимую обработку на стороне платформы и максимально сократить объем передаваемых данных между сервером приложений и сервером базы данных SAP HANA, что является критичным фактором с учетом объемов данных, которые накоплены в бизнес-системах.
Именно такой подход используется для дополнительной оптимизации работы систем SAP Business Suite в случае их размещения на платформе SAP HANA, где большинство ресурсоемких функций уровня приложений отдается на выполнение платформе. Благодаря этому появилась возможность ускорения транзакционных вычислений в сотни раз. Примером такой оптимизации является внедрение в компании НЛМК. В рамках этого проекта в системе, в которой работает более 5000 пользователей, удалось ускорить ряд транзакций в десятки–сотни раз, а транзакция по «запасам партий» была ускорена в 13 тыс. раз. Применение технологий in-memory позволило бизнес-пользователям проводить более комплексный анализ и вычисления.
Общее ускорение работы системы и повышение производительности транзакций и отчетов дает возможность также реализовывать более сложные аналитические запросы непосредственно в учетной системе. Те отчеты, которые раньше приходилось выносить в отдельную аналитическую систему, теперь могут быть реализованы непосредственно в транзакционной системе и базироваться на онлайн-данных. Если раньше пользователям для принятия управленческих решений необходимо было дождаться отражения последних изменений в аналитической системе, то с использованием SAP HANA появляется возможность обрабатывать текущие бизнес-процессы в транзакционной системе в режиме реального времени, начиная с общей картины и агрегированных ключевых показателей эффективности (КПЭ) до детальных транзакционных данных, а также встроенной прогнозной аналитики.
Таким образом, SAP HANA представляет собой не только базу данных in-memory, использующую все преимущества хранения в оперативной памяти, но и полноценную платформу для ускорения существующих систем и создания совершенно нового класса приложений и моделей для обработки данных. Использование платформы в качестве базы данных для аналитических и транзакционных систем дает ряд неоспоримых преимуществ для бизнес-пользователей и повышает эффективность их работы. Этот подход уже доказал свою эффективность и активно применяется многими заказчиками в России и по всему миру.
Сергей Кузин,
архитектор бизнес-решений SAP СНГ
SPBDEV Blog
Я работал со многими формами данных на протяжении своей карьеры. За это время мне приходилось создавать или запрашивать существующие базы данных для получения статистических данных. Традиционные базы данных обычно предназначены для быстрого запроса конкретных данных из базы данных и поддержки транзакционных операций. Хотя существует возможность работать со статистическими агрегатами, они обычно не очень быстры. Особенно, когда вы работаете с базами данных, содержащими сотни миллионов строк.
Для поддержки чисто аналитического доступа к базе данных было создано колоночное хранение.
Чем полезно колоночное хранение?
Этот подход оптимален, когда вы ищете некоторую или большую часть информации в записи или строке. Это не очень удобно, если вы хотите вычислить статистику по определенному столбцу. Если бы я хотел вычислить средний возраст в приведенной выше таблице, мне пришлось бы читать и передавать всю информацию об ID и именах, чтобы получить информацию о возрасте, которая сама может быть легко сохранена в одном байте для каждой записи. Что делает колоночное хранение, так это устанавливает значение в имени. Вместо хранения данных в строках или записях данные хранятся в столбцах. Таким образом, когда вы хотите вычислить средний возраст, вам нужно только прочитать данные с информацией о возрасте в памяти, а не все данные в каждой строке. Хранимые данные в итоге выглядят примерно так:
Колоночное хранение не подходит для транзакционных операций или выбора содержимого определенной строки или записи, но оно отлично подходит для создания статистики по определенному столбцу в таблице.
Если вы собираетесь хранить данные в столбцах, а не в строках, стоимость одиночных вставок очень высока, а количество необработанных байтов намного больше, чем вы обычно храните. Большинство колоночных хранилищ традиционно загружаются партиями, которые могут оптимизировать процесс хранения столбцов, и данные практически всегда сжимаются на диске для преодоления увеличенного объема данных, которые необходимо сохранить. В дополнение к сжатию большинство современных колоночных хранилищ хранят данные столбцов в блоках с несколькими предварительно вычисленными статистическими данными, которые заставляют запросы работать намного быстрее, позволяя производить вычисления, которые выдают гораздо меньше данных.
В качестве примера того, какую разницу в производительности может обеспечить колоночное хранение, приведу историю из личного опыта. В прошлом году я работал с клиентской базой данных, содержащей двадцать миллионов записей, связанных с информацией о покупке. Типичный запрос на покупку в таблицу заказов с использованием MySQL MyISAM займет 30-40 минут. Те же запросы с использованием MariaDB AX (вариант колоночного хранения MySQL) занимали от 0,1 секунды до 8 секунд.
Это технология, которая отображает возможности BI в реальном времени.
Два вида колоночных хранилищ
Оказывается, существует два очень разных типа колоночных баз данных.
Cassandra и HBase хранят каждый столбец отдельно по причинам, сильно отличающихся от других колоночных хранилищ. В результате производительность тоже сильно отличается. Концепция колоночных баз данных HBase и Cassandra не предполагает проведение быстрого анализа. Вместо этого они хранят отдельные столбцы, сгруппированные вместе, чтобы поддерживать свою распределенную таблицу (большую таблицу) и быструю запись. Когда вы слышите или читаете рекомендации об этих типах баз данных как о колоночных, помните, что они не обеспечат вам должную аналитическую производительность. Поскольку и Hbase, и Cassandra так плохи в отношении аналитики, они не поддерживают большинства возможностей построения агрегатов в целом.
Некоторые примеры специфических колоночных баз данных с открытым исходным кодом.
Несмотря на то, что существует много примеров запатентованных колоночных баз данных, поскольку я глубоко вовлечен в мир открытого исходного кода, мои примеры связаны именно с таким типом баз данных.
MariaDB AX
MariaDB AX является относительным новичком в мире колоночного хранения, хотя исходный код, который они используют, существует довольно давно. Корпорация MariaDB приняла кодовую базу от Infinidb: https://en.wikipedia.org/wiki/InfiniDB
MariaDB AX использует интерфейс движка хранилища, чтобы позволить пользователям использовать диалект MySQL SQL для распределенного колоночного хранилища. Одной из приятных особенностей использования MariaDB AX является создание таблиц с использованием механизма хранения Transactional InnoDB и объединение транзакционных и аналитических рабочих нагрузок в одной и той же базе данных. Хотя вы не будете проводить их в одних и тех же таблицах.
Parquet
Parquet это не база данных. Это файловый формат, который можно использовать для хранения таблиц базы данных в распределенных файловых системах, таких как HDFS, CEPH или AWS S3. Данные хранятся в Parquet в областях, которые содержат блоки данных столбцов, позволяющие разбить файл Parquet. Хранение файла на многих распределенных хостах позволяет ему осуществлять параллельную обработку. Вы можете получить доступ к файлам Parquet, используя Apache Spark, Hive, Pig, Apache Drill и Impala Cloudera.
Clickhouse
Clickhouse была создана российской компанией Yandex для поддержки своих собственных внутренних требований по предоставлению аналитики своим клиентам. В отличие от MariaDB AX или Parquet у него есть собственный диалект SQL и его собственные API. Тем не менее, он быстро становится очень популярным, потому что он очень быстрый.
Citus Data
Greenplum
Бизнес Greenplum долгое время относился к сфере BI и хранилищу данных. Их продукт, такой как Citus Data, основан на PostgreSQL. Хотя есть гораздо более старая версия PostgreSQL. Они предлагают как проприетарную версию, так и версию с открытым исходным кодом.
Все остальные
Те, что я перечислил здесь, это те, с которыми я имел дело в последние пару лет. Есть несколько технологий баз данных в категории хранилища столбцов, которые я здесь не упоминал, но вы можете узнать о них здесь: https://en.wikipedia.org/wiki/List_of_column-oriented_DBMSes
Заключение
Колоночные базы данных хранят данные в столбцах вместо строк. Они позволяют вычислять статистику по этим столбцам на один-два порядка или больше, быстрее, чем это делается на традиционных, основанных на строках, базах данных.
Столбцово-ориентированная таблица очень хороша для аналитики, но обычно ужасна для традиционных транзакционных нагрузок.
Большинство, хотя и не все, колоночные хранилища предназначены для работы на распределенном кластере серверов
SPBDEV Blog
Я работал со многими формами данных на протяжении своей карьеры. За это время мне приходилось создавать или запрашивать существующие базы данных для получения статистических данных. Традиционные базы данных обычно предназначены для быстрого запроса конкретных данных из базы данных и поддержки транзакционных операций. Хотя существует возможность работать со статистическими агрегатами, они обычно не очень быстры. Особенно, когда вы работаете с базами данных, содержащими сотни миллионов строк.
Для поддержки чисто аналитического доступа к базе данных было создано колоночное хранение.
Чем полезно колоночное хранение?
Этот подход оптимален, когда вы ищете некоторую или большую часть информации в записи или строке. Это не очень удобно, если вы хотите вычислить статистику по определенному столбцу. Если бы я хотел вычислить средний возраст в приведенной выше таблице, мне пришлось бы читать и передавать всю информацию об ID и именах, чтобы получить информацию о возрасте, которая сама может быть легко сохранена в одном байте для каждой записи. Что делает колоночное хранение, так это устанавливает значение в имени. Вместо хранения данных в строках или записях данные хранятся в столбцах. Таким образом, когда вы хотите вычислить средний возраст, вам нужно только прочитать данные с информацией о возрасте в памяти, а не все данные в каждой строке. Хранимые данные в итоге выглядят примерно так:
Колоночное хранение не подходит для транзакционных операций или выбора содержимого определенной строки или записи, но оно отлично подходит для создания статистики по определенному столбцу в таблице.
Если вы собираетесь хранить данные в столбцах, а не в строках, стоимость одиночных вставок очень высока, а количество необработанных байтов намного больше, чем вы обычно храните. Большинство колоночных хранилищ традиционно загружаются партиями, которые могут оптимизировать процесс хранения столбцов, и данные практически всегда сжимаются на диске для преодоления увеличенного объема данных, которые необходимо сохранить. В дополнение к сжатию большинство современных колоночных хранилищ хранят данные столбцов в блоках с несколькими предварительно вычисленными статистическими данными, которые заставляют запросы работать намного быстрее, позволяя производить вычисления, которые выдают гораздо меньше данных.
В качестве примера того, какую разницу в производительности может обеспечить колоночное хранение, приведу историю из личного опыта. В прошлом году я работал с клиентской базой данных, содержащей двадцать миллионов записей, связанных с информацией о покупке. Типичный запрос на покупку в таблицу заказов с использованием MySQL MyISAM займет 30-40 минут. Те же запросы с использованием MariaDB AX (вариант колоночного хранения MySQL) занимали от 0,1 секунды до 8 секунд.
Это технология, которая отображает возможности BI в реальном времени.
Два вида колоночных хранилищ
Оказывается, существует два очень разных типа колоночных баз данных.
Cassandra и HBase хранят каждый столбец отдельно по причинам, сильно отличающихся от других колоночных хранилищ. В результате производительность тоже сильно отличается. Концепция колоночных баз данных HBase и Cassandra не предполагает проведение быстрого анализа. Вместо этого они хранят отдельные столбцы, сгруппированные вместе, чтобы поддерживать свою распределенную таблицу (большую таблицу) и быструю запись. Когда вы слышите или читаете рекомендации об этих типах баз данных как о колоночных, помните, что они не обеспечат вам должную аналитическую производительность. Поскольку и Hbase, и Cassandra так плохи в отношении аналитики, они не поддерживают большинства возможностей построения агрегатов в целом.
Некоторые примеры специфических колоночных баз данных с открытым исходным кодом.
Несмотря на то, что существует много примеров запатентованных колоночных баз данных, поскольку я глубоко вовлечен в мир открытого исходного кода, мои примеры связаны именно с таким типом баз данных.
MariaDB AX
MariaDB AX является относительным новичком в мире колоночного хранения, хотя исходный код, который они используют, существует довольно давно. Корпорация MariaDB приняла кодовую базу от Infinidb: https://en.wikipedia.org/wiki/InfiniDB
MariaDB AX использует интерфейс движка хранилища, чтобы позволить пользователям использовать диалект MySQL SQL для распределенного колоночного хранилища. Одной из приятных особенностей использования MariaDB AX является создание таблиц с использованием механизма хранения Transactional InnoDB и объединение транзакционных и аналитических рабочих нагрузок в одной и той же базе данных. Хотя вы не будете проводить их в одних и тех же таблицах.
Parquet
Parquet это не база данных. Это файловый формат, который можно использовать для хранения таблиц базы данных в распределенных файловых системах, таких как HDFS, CEPH или AWS S3. Данные хранятся в Parquet в областях, которые содержат блоки данных столбцов, позволяющие разбить файл Parquet. Хранение файла на многих распределенных хостах позволяет ему осуществлять параллельную обработку. Вы можете получить доступ к файлам Parquet, используя Apache Spark, Hive, Pig, Apache Drill и Impala Cloudera.
Clickhouse
Clickhouse была создана российской компанией Yandex для поддержки своих собственных внутренних требований по предоставлению аналитики своим клиентам. В отличие от MariaDB AX или Parquet у него есть собственный диалект SQL и его собственные API. Тем не менее, он быстро становится очень популярным, потому что он очень быстрый.
Citus Data
Greenplum
Бизнес Greenplum долгое время относился к сфере BI и хранилищу данных. Их продукт, такой как Citus Data, основан на PostgreSQL. Хотя есть гораздо более старая версия PostgreSQL. Они предлагают как проприетарную версию, так и версию с открытым исходным кодом.
Все остальные
Те, что я перечислил здесь, это те, с которыми я имел дело в последние пару лет. Есть несколько технологий баз данных в категории хранилища столбцов, которые я здесь не упоминал, но вы можете узнать о них здесь: https://en.wikipedia.org/wiki/List_of_column-oriented_DBMSes
Заключение
Колоночные базы данных хранят данные в столбцах вместо строк. Они позволяют вычислять статистику по этим столбцам на один-два порядка или больше, быстрее, чем это делается на традиционных, основанных на строках, базах данных.
Столбцово-ориентированная таблица очень хороша для аналитики, но обычно ужасна для традиционных транзакционных нагрузок.
Большинство, хотя и не все, колоночные хранилища предназначены для работы на распределенном кластере серверов
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Индексы поколоночного хранения впервые были введены в SQL Server 2012. Это новый тип хранения данных таблицы, который улучшает производительность запросов определенных типов, по крайней мере, в десять раз. Они особенно полезны для фактологических таблиц в хранилищах данных.
Теперь я признаю, что когда индексы поколоночного хранения впервые появились, я нашла их пугающими. В то время вы не могли обновить таблицу с поколоночным индексом без его предварительного удаления. К счастью, с тех пор произошло много изменений к лучшему. Для меня всякое упоминание поколоночного хранения давало сигнал тревоги, говорящий «погоди, остановись, это слишком сложно.» Поэтому я постараюсь упростить эту возможность для вас. Такие индексы очень полезны при работе с данными хранилищ и большими таблицами. Они улучшают производительность некоторых запросов в 10 раз, поэтому знание о них и понимание, как они работают, существенно, если вы работаете в области больших масштабируемых данных. На их изучение стоит потратить время.
Архитектура индексов поколоночного хранения
Сначала необходимо усвоить терминологию и разницу между индексом поколоночного хранения и индексом построчного хранения (обычный способ, который мы все используем). Начну с терминологии.
Поколоночное хранение просто означает новый способ хранения данных в индексе. Вместо обычных индексов Построчного хранения или B-Tree, когда данные логически и физически организуются и сохраняются как таблица со строками и столбцами, данные в индексах поколоночного хранения физически сохраняются в столбцах, а логически организуются в строках и столбцах. Вместо сохранения всей строки или строк на странице, на странице хранится один столбец из множества строк. Это то различие в архитектуре, которое обеспечивает индексу поколоночного хранения очень высокий уровень сжатия наряду с уменьшением занимаемого пространства и существенным улучшением производительности чтения.
(Рисунок от Майкрософт)
Представьте, что это таблица с 2.1 миллионами строк и шестью столбцами. Это означает, что имеется две строковых группы по 1048576 строк в каждой плюс остаток из 2848 строк, который называется дельта-группой. Поскольку каждая строковая группа содержит минимум 102400 строк, группа строк дельта используется для хранения всех оставшихся индексных записей, пока их не наберется для создания еще одной строковой группы. Вы можете иметь множество групп строк дельта, дожидающихся перемещения в хранилище столбцов. Множество групп дельта хранятся в дельта-хранилище, и оно фактически представляет собой индекс B-tree, использующийся дополнительно к хранилищу столбцов. В идеале в вашем индексе должны быть группы строк, содержащие около 1 миллиона строк, чтобы уменьшить накладные расходы на операции сканирования.
Следующий шаг более сложный, этот процесс, который запускает перемещение групп строк дельта из дельта-хранилища в индекс поколоночного хранения, называется процессом перемещения кортежей (tuple-mover process). Он проверяет, что группы закрыты, т.е. группа содержит максимум 1 миллион записей и готова для сжатия и добавления в индекс. Как показано на картинке, индекс поколоночного хранения теперь содержит две строковые группы, которые затем делятся на столбцовые сегменты для каждого столбца таблицы. Это создает шесть столбиков по 1 миллиону строк на строковую группу, что в сумме дает 12 столбцовых сегментов. В чем смысл? Это именно те столбцовые сегменты, которые сжимаются порознь для записи на диск. Движок берет эти столбики и использует их для очень быстрого параллельного сканирования данных. Вы также можете вызвать процесс перемещения кортежей, выполнив перестройку вашего поколоночного индекса.
Для более быстрого доступа к данным, только значения Min и Max для группы строк сохраняются в заголовке страницы. Помимо этого, обработка запроса, относящаяся к хранилищу столбцов, использует пакетный режим (Batch mode), позволяющий движку обрабатывать множество строк одновременно. Это также позволяет в некоторых случаях обрабатывать строки исключительно быстро, давая выигрыш в производительности в 2-4 раза при обработке отдельного запроса. Например, если вы выполняете агрегацию, это происходит очень быстро, т.к. только агрегируемая строка считывается в память, и, используя группы строк, движок может пакетно обработать группы в 1 миллион строк. В SQL Server 2019 пакетный режим также будет введен для некоторых индексов хранилища строк и планов выполнения.
Другим интересным различием между индексами поколоночного хранения и индексами B-tree является то, что поколоночные индексы не имеют ключей. Вы можете также добавить все столбцы таблицы, если это позволяет тип данных, в некластеризованный поколоночный индекс. И здесь нет понятия включенных (included) столбцов. Это принципиально новый способ мышления, если вы привыкли настраивать традиционные индексы.
Пример поколоночного хранения
Теперь, я надеюсь, вы понимаете в общих чертах, что представляет собой индекс поколоночного хранения. Давайте посмотрим, как его создать, узнаем, какие ограничения имеются при использовании этих индексов, а также увидим индекс в действии по сравнению с построчным индексом.
В примере будет использоваться база данных AdventureworksDW2016CTP3 и таблица FactResellerSalesXL (см. скрипт ниже), в которой содержится 11,6 миллионов строк. Простой запрос будет выбирать ProductKey и возвращать некоторые агрегаты, сгруппированные по различным ключам продуктов.
Сначала выполним запрос при отсутствии индекса поколоночного хранения и наличии только текущего кластеризованного построчного (обычного) индекса. Обратите внимание, что я включила параметры STATISTICS IO и TIME. Эти два оператора SET помогут лучше проиллюстрировать преимущества, обеспечиваемые поколоночным индексом. SET STATISTICS IO выводит статистику по количеству затронутых запросом страниц. Это даст нам такую важную информацию, как логические, физические чтения, сканирования, логические и физические чтения LOB. SET STATISTICS TIME отображает количество времени, необходимое для разбора, компиляции и выполнения каждого оператора в запросе. Вывод содержит время в миллисекундах для выполнения каждой операции. Это позволит нам увидеть разницу в цифрах.
Как видно из результатов ниже, выполняется 5 сканирований, 318076 логических чтений, два физических чтения и 291341 упреждающих чтений. Время процессора (CPU Time) 8233 миллисекунд, а затраченное время 5008 миллисекунд. Оптимизатор выбирает сканирование существующего построчного кластеризованного индекса со стоимостью 91%. При этом просканировано 11,6 миллионов записей, и возвращено 395 записей в результирующем наборе.
Важно заметить, что если поместить курсор над оператором Clustered Index scan, вы сможете увидеть что типом хранения индекса являются строки (Row), и фактический режим выполнения (Actual Execution Mode) также Row.
Создадим теперь поколоночный индекс на этой таблице. Используя графический интерфейс, выполним щелчок правой кнопкой на индексах и выберем команду Новый индекс (New Index), затем Кластеризованный поколоночный индекс (Clustered Columnstore Index).
Поскольку используется сжатие данных, создание поколоночного индекса может интенсивно использовать процессор. Чтобы уменьшить нагрузку, SQL Server предлагает опцию (на вкладке Options) для изменения текущей установки сервера MaxDop для параллельного процесса построения. Это иногда следует иметь в виду при создании поколоночных индексов на рабочем сервере. В данном примере оставим значение по умолчанию. С другой стороны, если вы строите этот индекс во время простоя, имейте в виду, что операции поколоночного хранения масштабируются линейно по производительности вплоть до MaxDOP 64, и это может помочь быстрей завершить процесс построения индекса за счет обычного параллелизма.
Из документации Майкрософт
Если вы запустите этот оператор, то получите ошибку, которая сообщает о невозможности создания индекса, поскольку вы не можете создать более одного кластерного индекса на таблице.
Заметим, что база данных AdventureWorksDW2016CTP3 содержит таблицу с именем dbo.FactResellerSalesXL_CCI, которая уже имеет созданный кластеризованный поколоночный индекс. Вы можете заскриптовать её, чтобы увидеть в точности то, что вы пытаетесь создать. Для простоты вместо создания индекса используйте эту таблицу, которая идентична таблице FactResellerSalesXL и отличается только отсутствием/наличием поколоночного индекса.
Взглянем сначала на план выполнения. Легко увидеть, что оптимизатор от восьми операций пришел только к пяти, чтобы выполнить транзакцию, и он использует Index scan кластеризованного поколоночного индекса, но теперь при стоимости только 36% он читает ноль из 11,6 миллионов записей.
Теперь давайте смотреть на цифры. Чтобы разница было видна отчетливо, я ниже привожу оба результата для сравнения.
Построчное хранение
Поколоночное хранение
Когда использовать поколоночные индексы
Теперь большие возможности требуют большой ответственности. Поколоночные индексы разработаны для больших нагрузок в хранилищах данных, но не для обычных таблиц OLTP-систем. Только потому, что эти индексы работают эффективно, не означает, что вам следует добавлять их повсюду. Исследуйте и тестируйте поколоночные индексы перед созданием их в рабочей системе.
Сначала нужно знать данные
Являются ли ваши данные волатильными, т.е. часто ли они изменяются? Главное правило говорит, что вам нужны таблицы, в которых данные модифицируются редко, или, более конкретно, когда изменяется менее 10% строк вообще. Большое число удалений может вызвать фрагментацию, которая неблагоприятно повлияет на степень сжатия, тем самым уменьшая эффективность индекса. Что касается обновлений, то это дорогая операция, поскольку она выполняется как удаление с последующей вставкой, что отрицательно повлияет на производительность процесса загрузки.
Какого типа данные, содержатся в вашей таблице? Есть несколько типов данных, которые не поддерживаются поколоночным индексом. Данные типов varchar(max), nvarchar(max) или varbinary(max) не поддерживались до версии SQL Server 2017, и обычно они не очень подходят для данного типа рабочей нагрузки, особенно по причине их вероятно плохого сжатия. Кроме того, если вы используете уникальные идентификаторы (GUID), то не сможете создать индекс, поскольку они до сих пор не поддерживаются.
Далее, что вы делаете в своих запросах?
Вы считаете агрегатные значения, или выполняете аналитические запросы к данным, или ищете конкретные значения? Стандартные построчные индексы B-tree наилучшим образом подходят для поиска конкретных значений и иногда используются в тандеме с поколоночным индексом. Если вы используете индекс для покрытия предложения WHERE, которое не ищет диапазон значений, а лишь является фильтрующим предикатом, тогда поколоночное хранение не даст преимуществ. Это особенно справедливо, если вам нужно «покрыть» этот запрос включенными столбцами, т.к. поколоночный индекс не допускает включенных столбцов. Однако поколоночное хранение предназначено для быстрого процесса агрегации, особенно на сгруппированном диапазоне значений. Поэтому, если вы выполняете агрегацию или аналитику, обычно поколоночное хранение может дать вам существенный выигрыш в производительности, поскольку он может очень быстро выполнить полное сканирование таблицы для вычисления агрегатных значений.
Являются ли данные в хранилище таблицей фактов или же измерения? Как вы знаете, таблица измерений обычно используется для поиска конкретных значений,
в основном это поиск для сопоставления с агрегированным значением из таблицы фактов. Если это таблица измерений, обычно предпочтительно использовать модели на основе B-tree, за исключением очень больших измерений. Наилучшим вариантом является использование поколоночного хранения на таблице фактов в хранилище данных, поскольку эти таблицы обычно являются источником агрегатов.