Алексей Князев Колоночные индексы

Содержание SQL Server 2012 (Denali) Индексы – Кластерный – Некластерный – Full Text – XML – Spatial Колоночные СУБД Columnstore Index – Проект Appolo – Ключевые особенности – Демо – Ограничения использования Заключение Вопросы? 2

SQL Server 2012 Codename Denali

4 Версии SQL Server РелизКодовое названиеГод выхода SQL Server 1.0 (16bit) (OS/2) 1989 SQL Server 1.1 (16bit) (OS/2) 1990 SQL Server 4.2 под Microsoft OS/ SQL Server 4.21 под Windows NT SQLNT1993 SQL Server 6.0 SQL SQL Server 6.5 Hydra1996 SQL Server 7.0 Sphinx1998 SQL Server 7.0 OLAP Plato1999 SQL Server bit (8.0) Shiloh2000 SQL Server bit (8.0) Liberty2003 SQL Server 2005 (9.0) Yukon2005 SQL Server 2008 (10.0) Katmai2008 SQL Server 2008 R2 (10.5) Kilimanjaro2010 SQL Server 2012 (11.0) Denali2012

5 SQL Server 2012 НазваниеВерсияДата выхода SQL Server Denali CTP SQL Server Denali CTP SQL Server 2012 RC SQL Server 2012 RC SQL Server 2012 RTM ?Март – апрель 2012 (???) CTP - Community Technical Preview RC - Release Candidate RTM - Release To Manufacturing

Кодовые названия SQL Server 2000 – x32 Shiloh - National Military Park (США, Теннесси) – x64 Liberty - State Park (США, Нью-Джерси) SQL Server 2005 – Yukon - заповедник в Канаде SQL Server 2008 – Katmai - гора на Аляске - высота 2,286м. SQL Server 2008 R2 – Kilimandjaro - самая высокая гора в Африке, высота - 5,895м. (4 место в мире) SQL Server 2012 – Denali - высочайшая гора Аляски (Мак-Кинли), высота - 6,194м. (3 место в мире) ??? 6

Что дальше? Аконкагуа – 2 место – Это самая высокая горная вершина Южноамериканских Анд. Высота Аконкагуа – 6962 м. Также это гора является самым высоким потухшим вулканом на нашей планете. Туман, который вы видите внизу на фотографии, на самом деле является гигантским снежным вихрем. Эверест – 1 место – Это самая высокая гора в мире. Жители Тибета называют ее Джомолунгма, а непальцы – Сагарматха. Ученые пока окончательно не определили истинную высоту пика и по разным данным высота Эвереста составляет от 8844 до 8852 м. 7

Индексы

Что такое индекс? Индекс (англ. index) объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск - например, сбалансированного дерева. %81_(%D0%B1%D0%B0%D0%B7%D1%8B_%D0%B4%D0%B0%D0%BD%D0%B D%D1%8B%D1%85) 9

Кластерный индекс (Clustered Indexes) Может быть только 1 кластерный индекс на таблицу Является частью таблицы Может содержать в себе максимум 16 столбцов – 15 столбцов, если в таблице имеется один или несколько индексов XML Для ограничения PRIMARY KEY создается уникальный кластеризованный индекс по умолчанию Хранится в той же файловой группе, где и сама таблица Кластерный индекс должен быть как можно меньше насколько возможно Необязательный, но рекомендуемый. 10

Кластерный индекс (Clustered Indexes) 11

Некластерный индекс (Non-clustered Indexes) Количество некластеризованных индексов у таблицы 999, начиная с SQL Server 2008 – 255 в SQL Server 2005 и ниже Может содержать в себе максимум 16 столбцов Может содержать в себе большее количество неключевых столбцов Некластерные индексы всегда содержат столбцы кластерного индекса (когда у таблицы есть кластерный индекс) Если таблица является «кучей», то каждый некластерный индекс содержит в себе идентификатором строки в таблице (RID) Может быть создан в любой файловой группе данной БД Может быть с условием (фильтром), чтобы иметь меньший размер. 12

Некластерный индекс (Non-clustered Indexes) 13

Полнотекстовый индекс (Full Text Indexes) Не используется при обычных запроса T-SQL Для использования служат предикаты: – CONTAINS – CONTAINSTABLE – FREETEXT – FREETEXTTABLE Может использоваться, чтобы осуществлять поиск по файлам (doc, docx, xls, pdf), сохраненным в БД Для таблицы или индексированного представления допускается только один полнотекстовый индекс Полнотекстовый индекс может содержать не более 1024 столбцов Может индексировать XML-документы, но только индексирует значения, не теги Natural Language Search. 14

XML индекс (XML Indexes) Позволяет индексировать определенные узлы XML- документа 249 XML индексов на одну таблицу Требует Кластерного индекса на таблице У каждого ХМL столбца может быть единственный основной (Primary) индекс XML и множество вторичных индексов XML XML индексы могут быть созданы на единственном XML столбце Нет on-line перестроения Не доступны для переменных XML. Только используются на таблицах. 15

Пространственный индекс (SPATIAL Indexes) Пространственный индекс можно создать только на столбце типа geometry или geography Пространственные индексы можно определить только для таблицы, у которой имеется первичный ключ Можно создать до 249 пространственных индексов для каждого пространственного столбца в таблице Для построения индексов нельзя воспользоваться доступным параллелизмом процессов Пока для таблицы определен пространственный индекс, изменить метаданные первичного ключа невозможно Пространственные индексы не могут быть определены для индексированных представлений 16

Колоночные СУБД

Строчное хранение и колоночное хранение 18 Под построчным хранением данных обычно понимается физическое хранение всей строки таблицы в виде одной записи, в которой поля идут последовательно одно за другим, а за последним полем записи в общем случае идет первое следующей записи. Приблизительно так: [A1, B1, C1], [A2, B2, C2], [A3, B3, C3]… где A, B и С это поля (столбцы), а 1,2 и 3 номер записи (строки). Колоночное хранение - с точки зрения SQL-клиента данные представлены как обычно в виде таблиц, но физически эти таблицы являются совокупностью колонок, каждая из которых по сути представляет собой таблицу из одного поля. При этом физически на диске значения одного поля хранятся последовательно друг за другом приблизительно так: [A1, A2, A3], [B1, B2, B3], [C1, C2, C3] и т.д.

Плюсы и минусы 19 Таким образом, колоночное хранение являются подходящими для интенсивных чтений Строчное хранениеКолоночное хранение (+) Простая модификация данных (+) Чтение только нужных блоков данных (-) Избыточные чтения данных (-) Записи кортежа требуют множественных обращений к данным

Почему колоночное хранение выгоднее 20 Большинство запросов не обрабатывает все атрибуты определенной таблицы Пример запроса: В запросе используются только три (name, address, region) столбца таблицы CUSTOMES. При этом у таблицы может быть на много больше колонок При колоночном хранении данных, при чтении операции IO являются более эффективными, т.к. они считывают, только те атрибуты, которые указаны в запросе Select c.name and c.address From CUSTOMES as c Where c.region=Mumbai;

Когда стоит использовать 21 Может работать значительно быстрее чем строчное хранение данных для ряда приложений – В выборку попадают только столбцы, указанные в запросе – Более эффективная работа с кэшем – Лучшая степень сжатия (данные в столбцах, как правило однотипные) При этом ряд задач может работать медленнее – OLTP с большим количеством операций INSERT и т.д.

Сравнение с классическими реляционными СУБД 22 Реляционные СУБД: соблюдается целостность данных (foreign keys, транзакционность) четко структурированные данные запросы по всей структуре БД, используя стандартизованный язык результат запроса это небольшой (относительно БД) кусок данных быстрое извлечение нужной структуры (в том числе быстрая реализация запроса) Колоночные БД: хранят большие объемы данных, упрощённый шардинг, репликация и пр. данные гораздо менее структурированы данные обрабатываются большим блоком (параллельно, massive parallel processing), или вообще сразу вся база оптимальным будет индивидуальный подход к обработке, тесная интеграция с данными худшая (ручная) поддержка целостности данных

Список колоночных СУБД Commercial – 1010data's Tenbase database 1010data – Alterian's Engine Alterian's Engine – Aster Data Systems Aster Data Systems – Calpont InfiniDB Enterprise Edition Calpont InfiniDB – EXASOL EXASOL – FAME FAME – FluidDB FluidDB – Greenplum Greenplum – Hive Intelligence Ltd Hex Engine Hive Intelligence Ltd – Infobright Enterprise Edition Infobright – KDB KDB – Kickfire Kickfire – Oracle Retail Predictive Application Server (RPAS) Oracle Retail Predictive Application Server (RPAS) – Paraccel Analytic Database Paraccel – SAND CDBMS SAND CDBMS – SAP HANA SAP HANA – SenSage SenSage – Sybase IQ Sybase IQ – Microsoft SQL Server 2012 (Enterprise Edition) Microsoft SQL Server – Vectorwise Vectorwise – Vertica Vertica Free and open source software – Calpont InfiniDB Community Edition Calpont InfiniDB – Infobright Community Edition Infobright – Greenplum Community Edition Greenplum – LucidDB LucidDB – Metakit Metakit – MonetDB MonetDB – C-Store C-Store – S programming language and GNU R incorporate column-oriented data structures for statistical analyses S programming languageGNU R 23

Колоночные индексы (Columnstore) SQL Server 2012

«Аполлон» (Apollo) 25 «Аполлон-11» (англ. Apollo 11) пилотируемый космический корабль серии «Аполлон», в ходе полёта которого люди впервые в истории совершили посадку на поверхность другого небесного тела Луны.

«Аполлон» (Apollo) 26 Apollo – кодовое имя нового оптимизатора запросов, ориентированного на нужные поля результирующей выборки. В действительности, это одна из самых мощных особенностей SQL Server Denali. Компания Microsoft считает, что производительность запросов с новым механизмом оптимизации в ряде случаев может быть увеличена в 10 раз. Такой прирост производительности обеспечивается сущностью «Columnstore Indexes». Суть инновации заключается в том, что списки полей хранятся не только в таблицах данных, но еще и в специальных страницах. Считывание из базы данных производится только по тем полям, которые реально нужны для составления результата запроса (часто это менее 15% всех полей в таблице) Данные легче сжимаются, благодаря их избыточности в пределах одного поля Увеличивается эффективность использования буфера. Во-первых, уменьшен объем извлекаемых данных, а во-вторых, производится анализ частоты обращения к полям – и редко запрашиваемые поля выгружаются из памяти.

Колоночные индексы Цель: Уменьшить TCO (Total cost of ownership) существенным ускорением запросов хранилища данных (data warehouse) Две новые технологии в SQL Server 2012 – Колоночные индексы – Векторное выполнение запроса (пакетная обработка) – кратное ускорение Уменьшаются аппаратные потребности Уменьшается время агрегации Меньшее время отклика для конечного пользователя Более полезные данные за короткое время

Когда мне нужны колоночные индексы Создайте колоночный индекс, если у вас Рабочая нагрузка дала выигрыш в производительности Большинство обновлений добавляет новые данные Обычно ночная загрузка новых данных Большинство запросов соответствует образцу звездообразного объединения или влечет за собой сканирование и агрегацию больших объемов данных Большая таблица фактов или измерений Не создавайте колоночный индекс, если у вас Частые обновления Большое количество мелких (точечных) запросов Строчное хранение (B-tree) индексов может дать большую производительность Рабочая нагрузка не дала прироста производительности 28

Увеличение производительности 29 Cold Buffer PoolWarm Buffer Pool CPUElapsedCPUElapsed Row store259 s20 s206 s3.1 s Columnstore + batch19.8 s0.8 s16.3 s0.3 s Speedup13 X25 X13 X10 X SELECT w_city, w_state, d_year, SUM(cs_sales_price) AS cs_sales_price FROM warehouse, catalog_sales, date_dim WHERE w_warehouse_sk = cs_warehouse_sk and cs_sold_date_sk = d_date_sk and w_state = 'SD and d_year = 2002 GROUP BY w_city, w_state, d_year ORDER BY d_year, w_state, w_city; and c.address Зависит от данных, запросов и т.д. Обусловлено тем, на сколько эффективный план запроса, сколько используется столбцов из колоночного индекса и используется ли пакетная обработка (до 100 кратного ускорения) 1 TB version of TPC-DS DB 32 proc, 256 GB RAM

Рабочая нагрузка хранилища данных (DataStorage) 30 Основные запросы – это чтение Загружаются большие объёмы данных Все данные монотонно увеличиваются ( process_datetime, id- identity ) Обновление происходит крайне редко Данные хранятся в течении времени (например 5 лет с помесячной разбивкой) Доступ к данным в режиме sliding window

Sliding window ( «скользящее окно» )

Схемы хранилища данных и запросы 32 Схема «звезда» Большая таблица фактов создаём колоночные индексы Небольшие таблицы измерений Звездообразные объединения (Star joins) Большинство запросов – это агрегация данных

Схема «звезда» 33 FactSales DimCustomer FactSales( CustomerKey int, ProductKey int, EmployeeKey int, StoreKey int, OrderDateKey int, SalesAmount money ) DimCustomer( CustomerKey int, FirstName nvarchar(50), LastName nvarchar(50), Birthdate date, Address nvarchar(50) ) DimProduct … DimDate DimEmployee DimStore

Запросы типа «звезда» SELECT TOP 10 p.ModelName, p.EnglishDescription, SUM(f.SalesAmount) as SalesAmount FROM FactResellerSalesPart f, DimProduct p, DimEmployee e WHERE f.ProductKey=p.ProductKey AND e.EmployeeKey=f.EmployeeKey AND f.OrderDateKey >= AND p.ProductLine = 'M' -- Mountain AND p.ModelName LIKE '%Frame%' AND e.SalesTerritoryKey = 1 GROUP BY p.ModelName, p.EnglishDescription ORDER BY SUM(f.SalesAmount) desc; 34

«Типичные» запросы хранилища данных 35 Запросы к большому объёму данных Создание отчётов Медленная обработка данных (минуты, а то и часы) DBA/DBD прилагают значительные усилия для исправления ситуации Создание новых (возможно временных) индексов Оптимизация запросов Создание сводных таблиц Создание индексированных представлений OLAP - кубы

Columnstore Index Demo SQL Server 2012

Структура колоночных индексов 37 Uses VertiPaq compression C1 C2 C3 C5C6C4 Pages Патентованная технология Microsoft VertiPaq

Уменьшаем IO за счет колоночных индексов 38 Чтение с диска только необходимых столбцов Столбцы сжаты Меньшее число операций IO Улучшенная работа с буфером C1 C2 C4 C5 C6 C3 SELECT region, sum (sales) …

Технология выполнения сложного запроса 39 Пакетное выполнение некоторых операций Группы пакетных обработок в плане запроса Пакетная обработка строк Эффективное представление данных Очень эффективные алгоритмы Улучшенный параллелизм

Сегменты столбца 40 Сегмент столбца содержит значения одного столбца для 1М строк Сегмент столбца в сжатом виде Каждый сегмент столбца сохранен в отдельном LOB Сегмент столбца – единица хранения и обращения к данным с диска C1 C2 C3 C5C6C4 Set of about 1M rows Column Segment

Ограничения на использование с другими индексами и секционированием 41 Таблица может быть либо кластеризованной либо кучей Колоночный индекс: Может быть только некластерным Только один на таблицу Для секционированной таблицы должен быть выровненным Не может быть создан на индексированном представлении Не может быть с условием

Создание колоночного индекса 42 Создаём таблицу Вставляем данные Создаём некластерный колоночный индекс для всех или определённых столбцов таблицы CREATE NONCLUSTERED COLUMNSTORE INDEX ncci ON myTable(OrderDate, ProductID, SaleAmount) Object Explorer

Создание колоночного индекса через SSMS 43

Оптимизация запросов с колоночными индексами 44 Оценка стоимости Оптимизатор использует колоночный индекс Оптимизатор использует пакетную обработку

Подсказки для работы с колоночными индексами 45 Используем колоночный индекс Используем другой индекс Игнорируем колоночный индекс select distinct (SalesTerritoryKey) from dbo.FactResellerSales with (index (ncci)) select distinct (SalesTerritoryKey) from dbo.FactResellerSales with (index (ci)) select distinct (SalesTerritoryKey) from dbo.FactResellerSales option(ignore_nonclustered_columnstore_index)

Работа с памятью 46 Управление памятью происходит автоматически Колоночный индекс хранится на диске Только необходимые столбцы поднимаются в память Данные с диска считываются в память сегментами стобца SELECT C2, SUM(C4) FROM T GROUP BY C2; T.C2 T.C4 T.C2 T.C4 T.C2 T.C1 T.C3 T.C4

Новые элементы плана выполнения для колоночных индексов и пакетной обработки 47

48

Новые системные представления для работы с колоночными индексами 49

Колоночные индексы: функциональная совместимость с остальной частью SQL-сервера 50 Backup and restore Mirroring Log shipping SSMS Administration, tools Partitions

Ограничения: типы данных 51 Неподдерживаемые типы данных decimal > 18 digits Binary BLOB (n)varchar(max) Uniqueidentifier Date/time types > 8 bytes CLR

Ограничения: производительность запросов 52 Outer joins Unions Подходят для запросов: Inner joins Star joins Aggregation

Вставка новых данных в колоночный индекс 53 Колоночный индекс делает таблицу read-only Переключение секций в секционированных таблицах поддерживается Операторы INSERT, UPDATE, DELETE и MERGE не работают Два рекомендованных метода загрузки данных: Disable, update, rebuild Partition switching

Вставка новых данных в колоночный индекс Метод 1 – Отключение индекса 54 Отключить (или удалить) индекс Обновить таблицу Перестроить колоночный индекс ALTER INDEX my_index ON MyTable DISABLE ALTER INDEX my_index ON MyTable REBUILD

Вставка новых данных в колоночный индекс Метод 2 - Секционирование 55 Загрузить данные в промежуточную таблицу Постройте на ней колоночный индекс Создать на основной таблице новую секцию (SPLIT) Переключить промежуточную таблицу в новую секцию основной таблицы CREATE NONCLUSTERED COLUMNSTORE INDEX my_index ON StagingT(OrderDate, ProductID, SaleAmount) ALTER TABLE StagingT SWITCH TO T PARTITION 5

Заключение

Q&A Ваши вопросы

Ресурсы General SQL Server Information – SQL Server 2012 Developer Training Kit – Microsoft® SQL Server® 2012 RC0 – b3d1-9884d46c897c b3d1-9884d46c897c RC0 Books Online – Columnstore Indexes for Fast DW QP – 3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Serve r%2011.pdf 3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Serve r%2011.pdf Колоночные СУБД принцип действия, преимущества и область применения – Наш сайт – Мой блог –