Алексей Князев a.knyazev@t-sql.ru http ://www.t-sql.ru

1. http://www.EkbIT.Pro Алексей Князев a.knyazev@t-sql.ru http://www.t-sql.ru Колоночные индексы

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

3. SQL Server 2012 Codename “Denali”

4. Версии SQL Server

5. SQL Server 2012 CTP - Community Technical Preview RC - Release Candidate RTM - Release To Manufacturing

6. Кодовые названия • 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 место в мире) • ???

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

8. Индексы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

27. Колоночные индексы 11001010010100101001110101100101001 • Цель: Уменьшить TCO (Totalcostofownership) существенным ускорением запросов хранилища данных (data warehouse) • Две новые технологии в SQL Server 2012 • Колоночные индексы • Векторное выполнение запроса (пакетная обработка) • 10-100 - кратноеускорение • Уменьшаются аппаратные потребности • Уменьшается время агрегации • Меньшее время отклика для конечного пользователя • Более полезные данные за короткое время • http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-performance-tuning.aspx • http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx

28. Когда мне нужны колоночные индексы

29. Увеличение производительности • Зависит от данных, запросов и т.д. • Обусловлено тем, на сколько эффективный план запроса, сколько используется столбцов из колоночного индекса и используется ли пакетная обработка (до 100 кратного ускорения) • 1 TB version of TPC-DS DB 32 proc, 256 GB RAM • 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

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

31. Sliding window ( «скользящее окно» ) 2011 2010 2010 2010 2009 2009 2009 2008 2008 2008 2007 2007

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

33. Схема «звезда» FactSales FactSales(CustomerKeyint , ProductKeyint , EmployeeKeyint , StoreKeyint , OrderDateKeyint , SalesAmount money ) DimCustomer(CustomerKeyint , FirstNamenvarchar(50) , LastNamenvarchar(50) , Birthdate date , EmailAddressnvarchar(50) ) DimProduct … DimDate DimEmployee DimCustomer DimStore

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

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

36. Columnstore Index Demo SQL Server 2012

37. Структура колоночных индексов Row store: … C1 C4 C5 C6 C3 C2 Pages Column store: Uses VertiPaq compression Патентованная технология Microsoft VertiPaq

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

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

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

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

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

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

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

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

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

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