Начало работы с индексами columnstore для оперативной аналитики в режиме реального времени

Область применения:SQL ServerБаза данных SQL AzureУправляемый экземпляр Azure SQLБаза данных SQL в Microsoft Fabric

В SQL Server 2016 (13.x) реализована операционная аналитика в режиме реального времени, возможность одновременного выполнения аналитики и рабочих нагрузок OLTP в одних и том же таблицах базы данных. Помимо выполнения аналитики в реальном времени, можно отказаться от необходимости в ETL и хранилище данных.

Описана операционная аналитика в режиме реального времени

Традиционно предприятия использовали отдельные системы для операционных (т. е. OLTP) и аналитических рабочих нагрузок. Для таких систем задания извлечения, преобразования и загрузки (ETL) регулярно перемещали данные из операционного хранилища в хранилище аналитики. Аналитические данные обычно хранятся в хранилище или киоске данных, предназначенном для выполнения аналитических запросов. Хотя такое решение являлось стандартным, у него есть три существенных недостатка:

• Сложность. Реализация извлечения, преобразования и загрузки может потребовать значительных объемов кода, особенно для загрузки только измененных строк. Определение того, какие именно строки изменены, может оказаться непростой задачей.
• Стоимость. Реализация ETL требует затрат на приобретение дополнительного оборудования и лицензий на программное обеспечение.
• Задержка данных. Реализация извлечения, преобразования и загрузки добавляет задержку для проведения аналитики. Например, если задание ETL выполняется в конце каждого рабочего дня, аналитические запросы будут использовать данные, которые устарели по меньшей мере на день. Для многих организаций такая задержка недопустима, поскольку их бизнес зависит от анализа данных в реальном времени. Например, для выявления мошенничества требуется анализ рабочих данных в реальном времени.

Операционная аналитика в реальном времени предлагает решение этих проблем.

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

Аналитика в режиме реального времени использует обновляемый некластеризованный индекс columnstore в таблице rowstore. Индекс columnstore хранит копию данных, так что аналитические и OLTP рабочие нагрузки работают с отдельными копиями данных. Это сводит к минимуму влияние одновременно выполняющихся рабочих нагрузок на производительность. Ядро СУБД автоматически сохраняет изменения индекса, поэтому изменения OLTP всегда up-to-date для аналитики. Такой подход позволяет удобно анализировать актуальные данные в реальном времени. Он работает как с дисковыми, так и с оптимизированными для памяти таблицами.

Пример начала работы

Чтобы начать работу с операционной аналитикой в реальном времени, выполните следующее.

1. Определите те таблицы в рабочей схеме, которые содержат данные для анализа.

2. Для каждой таблицы удалите все индексы дерева B, которые в первую очередь предназначены для ускорения существующей аналитики рабочей нагрузки OLTP. Замените их одним некластеризованным индексом columnstore. Это может повысить общую производительность рабочей нагрузки OLTP, так как для обслуживания меньше индексов.

   --This example creates a nonclustered columnstore index on an existing OLTP table.
   --Create the table
   CREATE TABLE t_account (
       accountkey int PRIMARY KEY,
       accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int
   );
   
   --Create the columnstore index with a filtered condition
   CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
   ON t_account (accountkey, accountdescription, unitsold)
   ;
   

   Columnstore-индекс в памяти оптимизированной таблице позволяет проводить оперативную аналитику за счет интеграции технологий In-memory OLTP и columnstore, обеспечивая высокую производительность как для OLTP, так и для аналитических рабочих нагрузок. Индекс columnstore в таблице, оптимизированной для памяти, должен быть кластеризованным индексом, другими словами, он должен включать все столбцы.

   -- This example creates a memory-optimized table with a columnstore index.
   CREATE TABLE t_account (
       accountkey int NOT NULL PRIMARY KEY NONCLUSTERED,
       Accountdescription nvarchar (50),
       accounttype nvarchar(50),
       unitsold int,
       INDEX t_account_cci CLUSTERED COLUMNSTORE
       )
       WITH (MEMORY_OPTIMIZED = ON );
   

Теперь все готово для запуска операционной аналитики в реальном времени без внесения изменений в приложение. Запросы аналитики будут выполняться в индексе columnstore, а операции OLTP будут работать с индексами OLTP B-дерева. Рабочие нагрузки OLTP продолжают выполняться, но при этом некоторые дополнительные издержки по поддержанию индекса columnstore. В следующем разделе описаны процессы оптимизации производительности.

Записи блогов

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

• Экономическое обоснование для операционной аналитики в реальном времени (Business case for real-time operational analytics)

• Использование некластеризованного индекса columnstore для операционной аналитики в реальном времени (Using a nonclustered columnstore index for real-time operational analytics)

• Простой пример использования некластеризованного columnstore индекса

• Как SQL Server поддерживает колонночный некластеризованный индекс при транзакционной рабочей нагрузке

• Минимизация влияния некластеризованного индекса с помощью отфильтрованного индекса (Minimizing the impact of nonclustered columnstore index maintenance by using a filtered index)

• Минимизация влияния некластеризованного индекса с помощью задержки сжатия (Minimizing the impact of nonclustered columnstore index maintenance by using compression delay)

• Минимизация влияния некластеризованного индекса с помощью задержки сжатия — показатели производительности (Minimizing impact of a nonclustered columnstore index maintenance by using compression delay - performance numbers)

• Операционная аналитика в реальном времени с таблицами, оптимизированными для памяти

• Индексы columnstore и политика слияния для групп строк (Columnstore index and the merge policy for rowgroups)

Видео

Видеосерия "Data Exposed" представляет подробности о некоторых возможностях и аспектах.

• Часть 1. Включение оперативной аналитики в режиме реального времени (HTAP) в SQL Azure
• Часть 2. Оптимизация существующих баз данных и приложений с помощью операционной аналитики
• Часть 3. Создание операционной аналитики с помощью функций окна.

Совет по повышению производительности 1. Для улучшения производительности запросов пользуйтесь отфильтрованными индексами

Выполнение операционной аналитики в реальном времени может повлиять на производительность рабочей нагрузки OLTP. Это влияние должно быть минимальным. Пример A показывает, как использовать отфильтрованные индексы, чтобы свести к минимуму влияние некластеризованного индекса columnstore на транзакционные рабочие нагрузки, обеспечивая аналитику в режиме реального времени.

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

Аналитические запросы прозрачно обращаются как к теплым, так и к горячим данным для обеспечения аналитики в реальном времени. Если значительная часть рабочей нагрузки касается "горячих" данных, эти операции не требуют дополнительного обслуживания индекса columnstore. Для столбцов, используемых в определении отфильтрованного индекса, рекомендуется иметь кластеризованный индекс rowstore. Ядро СУБД использует кластеризованный индекс для быстрого сканирования строк, которые не соответствовали отфильтрованном условию. Без этого кластеризованного индекса для поиска этих строк требуется полное сканирование таблицы rowstore, что может негативно повлиять на производительность аналитических запросов. В отсутствие кластеризованного индекса можно создать дополнительный отфильтрованный некластеризованный индекс B-дерева, чтобы определить такие строки, но не рекомендуется, так как доступ к большому диапазону строк через некластеризованные индексы дерева B-дерева дорого.

Пример A: Доступ к горячим данным из индекса B-дерева и тёплым данным из колоночного индекса.

В этом примере используется отфильтрованное условие (accountkey > 0) для определения строк, включенных в индекс columnstore. Цель заключается в разработке условия фильтрации и последующих запросов для доступа к часто изменяющимся "горячим" данным из индекса дерева B+ и для доступа к более стабильным "теплым" данным из колоночного индекса.

-- Use a filtered condition to separate hot data in a rowstore table
-- from "warm" data in a columnstore index.

-- create the table
CREATE TABLE orders (
AccountKey int not null,
CustomerName nvarchar (50),
OrderNumber bigint,
PurchasePrice decimal (9,2),
OrderStatus smallint not null,
OrderStatusDesc nvarchar (50)
);

-- OrderStatusDesc  
-- 0 => 'Order Started'  
-- 1 => 'Order Closed'  
-- 2 => 'Order Paid'  
-- 3 => 'Order Fulfillment Wait'  
-- 4 => 'Order Shipped'  
-- 5 => 'Order Received'  

CREATE CLUSTERED INDEX orders_ci ON orders(OrderStatus);

--Create the columnstore index with a filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders  (accountkey, customername, purchaseprice, orderstatus)
WHERE OrderStatus = 5;

-- The following query returns the total purchase done by customers for items > $100 .00
-- This query will pick  rows both from NCCI and from 'hot' rows that are not part of NCCI
SELECT TOP (5) CustomerName, SUM(PurchasePrice)
FROM orders
WHERE PurchasePrice > 100.0
GROUP BY CustomerName;

Запрос аналитики выполняется со следующим планом запроса. Вы можете увидеть, что строки, не соответствующие условию фильтрации, доступны через кластеризованный индекс B-дерева.

Дополнительные сведения см. в блоге: отфильтрованный некластеризованный колонночный индекс.

Совет по оптимизации производительности №2: Разгрузите аналитику в доступную для чтения вторичную реплику "AlwaysOn".

Хотя обработку индекса columnstore можно минимизировать с помощью отфильтрованного индекса columnstore, аналитические запросы могут по-прежнему потреблять значительные вычислительные ресурсы (ЦП, ввода-вывода, памяти), что негативно влияет на производительность рабочей нагрузки. Для самых критически важных рабочих нагрузок рекомендуется использовать конфигурацию AlwaysOn. В этой конфигурации вы можете исключить влияние выполнения анализа, перераспределив его на читаемую вторичную реплику.

Совет по оптимизации #3: Уменьшение фрагментации индекса за счет сохранения горячих данных в delta-группах строк.

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

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

Для минимизации фрагментации индекса можно использовать параметр COMPRESSION_DELAY.

-- Create a sample table
CREATE TABLE t_colstor (
accountkey int not null,
accountdescription nvarchar (50) not null,
accounttype nvarchar(50),
accountCodeAlternatekey int
);

-- Creating nonclustered columnstore index with COMPRESSION_DELAY. 
-- The columnstore index will keep the rows in closed delta rowgroup 
-- for 100 minutes after it has been marked closed.
CREATE NONCLUSTERED COLUMNSTORE INDEX t_colstor_cci ON t_colstor
(accountkey, accountdescription, accounttype)
WITH (DATA_COMPRESSION = COLUMNSTORE, COMPRESSION_DELAY = 100);

Дополнительные сведения см. в блоге: задержка сжатия.

Ниже приведены рекомендуемые методики.

• Рабочая нагрузка insert/Query: Если рабочая нагрузка в основном вставляет данные и запрашивает ее, рекомендуется использовать значение по умолчанию COMPRESSION_DELAY 0. Новые строки будут сжиматься после вставки 1 миллиона строк в отдельную разностную группу строк. Некоторые примеры таких рабочих нагрузок являются традиционной рабочей нагрузкой DW или анализом выбора потока, когда необходимо проанализировать шаблон выбора в веб-приложении.

• Рабочая нагрузка OLTP: если рабочая нагрузка DML тяжела (т. е. тяжелый набор обновлений, удаления и вставки), можно увидеть фрагментацию индекса columnstore, проверив динамическое sys.dm_db_column_store_row_group_physical_statsадминистративное представление. Если вы видите, что > 10% строк помечены как удаленные в недавно сжатых группах строк, можно использовать COMPRESSION_DELAY параметр для добавления задержки времени, когда строки становятся допустимыми для сжатия. Например, если для рабочей нагрузки только что вставленный объект остается "горячим" (т. е. обновляется несколько раз) в течение 60 минут, следует выбрать COMPRESSION_DELAY значение 60.

Значение COMPRESSION_DELAY параметра по умолчанию должно работать для большинства клиентов.

Для расширенных пользователей рекомендуется выполнить следующий запрос и собрать % удаленных строк за последние семь дней.

SELECT row_group_id,
       CAST(deleted_rows AS float)/CAST(total_rows AS float)*100 AS [% fragmented],
       created_time
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactOnlineSales2')
      AND state_desc = 'COMPRESSED'
      AND deleted_rows > 0
      AND created_time > DATEADD(day, -7, GETDATE())
ORDER BY created_time DESC;

Если число удаленных строк в сжатых > группах строк 20%, плато в старых группах строк с < 5% вариантом (называемые холодными группами строк), то задайте COMPRESSION_DELAY = (youngest_rowgroup_created_time - current_time). Этот подход лучше всего подходит для стабильной и относительно однородной рабочей нагрузки.

Связанный контент

• Общие сведения об индексах columnstore
• Индексы columnstore. Руководство по загрузке данных
• Индексы columnstore - производительность запросов
• Колонные индексы в хранилище данных
• Оптимизация обслуживания индексов позволяет повысить производительность запросов и снизить уровень потребления ресурсов