В первой части этой серии я описал структуру хранения и шаблоны доступа для традиционной структуры хранения SQL Server: классических индексов хранения строк (on-disk rowstore) на диске (кучи и B-деревья).
Columnstore-индексы были представлены в SQL Server 2012. В той версии поддерживались только некластерные columnstore-индексы (то есть они хранили копию данных во включённых столбцах, в то время как фактические данные таблицы по-прежнему хранились в куче или кластерном индексе rowstore). И они делали таблицу доступной только для чтения! Это ограничение было снято в SQL Server 2014, когда также были добавлены кластерные columnstore-индексы. Затем в SQL Server 2016 добавили возможность создавать дополнительные некластерные индексы (rowstore) на кластерном columnstore-индексе. И, также начиная с SQL Server 2016, у нас теперь есть упорядоченные columnstore-индексы — на мой взгляд, несколько вводящее в заблуждение название.
Примечание: Columnstore-индексы, оптимизированные для памяти (memory-optimized), доступные с SQL Server 2016, будут описаны в отдельной записи.
Группы строк
Данные в columnstore-индексе подразделяются на одну или несколько групп строк (rowgroups). Если columnstore-индекс секционирован, то каждая секция подразделяется на группы строк.
Когда columnstore-индекс создаётся или перестраивается, SQL Server пытается заполнить каждую группу строк как можно ближе к её максимальному размеру в 1 048 576 (2 в степени 20) строк. Но существует множество обстоятельств, из-за которых группы строк могут хранить меньше строк, чем это максимальное число.
Существует несколько типов групп строк, которые будут описаны ниже.
Сжатая группа строк
Преимущество в производительности, которым обладают columnstore-индексы для аналитических запросов (много строк, но всего несколько столбцов из широкой и большой таблицы фактов), достигается благодаря высоко оптимизированной структуре хранения в сжатых группах строк.
В каждой группе строк размером до 1 048 576 строк данные хранятся в сегментах. Сегмент содержит данные для одного столбца всех строк в группе строк. Таким образом, один сегмент хранит все данные для первого столбца, второй сегмент содержит данные для второго столбца и так далее. Каждый из этих сегментов сжимается, что значительно снижает требуемый объём операций ввода-вывода при чтении этих сегментов. Фактическая низкоуровневая структура хранения, используемая для этих сегментов — это страницы больших двоичных объектов (BLOB). Вот почему в выводе STATISTICS IO для columnstore-индекса всегда присутствуют логические чтения больших объектов (lob logical reads).
Поскольку каждый сегмент хранит данные одного столбца, вероятность одинаковых или похожих значений высока, что значительно повышает эффективность многих методов сжатия (таких как run-length encoding (RLE) и словари или их вариации). SQL Server дополнительно улучшает это, сортируя строки внутри группы строк для максимальной эффективности сжатия.
Однако эту сортировку нельзя выполнить по столбцам. Поскольку столбцы хранятся индивидуально, единственный способ для SQL Server узнать, какие значения принадлежат одной строке, — это их позиция внутри группы строк. Другими словами, SaleDate в позиции №384 306 группы строк 2 находится в той же строке, что и BillDate и CustomerKey в позиции №384 306 группы строк 2. Каждый из них находится в своём собственном сегменте, но все они должны иметь одну и ту же позицию. Это означает, что сортировка, выполняемая при перестроении columnstore-индекса, пытается найти лучшее сжатие по всем столбцам, но не может оптимизировать каждый отдельный столбец.
Для каждого сегмента SQL Server отслеживает минимальное и максимальное значение во всех из до 1 048 576 строк в этом сегменте. Это хранится в метаданных этого сегмента и может использоваться для исключения групп строк (описано ниже).
Хранилище изменений (Дельта-хранилище)
Изменение данных в сжатой группе строк было бы чрезвычайно неэффективным. Обновление столбца потребовало бы изменения затрагиваемых значений в соответствующем сегменте. Из-за используемого сжатия это также могло бы повлиять на хранимые данные всех других данных в сегменте. Для удаления строки были бы затронуты все сегменты в группе строк, поскольку все столбцы удаляются. Добавление строк относительно менее затратно, но всё равно довольно дорого: хотя новые значения можно было бы добавить в конец сжатой группы строк, в которой ещё есть место, всё равно требуется много работы для обеспечения правильного сжатия новых данных, поскольку многие методы сжатия основаны на предыдущих значениях.
Такая высокая стоимость изменения сжатых групп строк — причина, по которой таблицы с columnstore-индексами были доступны только для чтения в SQL Server 2012. Чтобы изменить таблицу, нам приходилось удалять columnstore-индекс, выполнять изменение, а затем снова пересоздавать индекс. Что, конечно, было очень обременительно. Вот почему Microsoft сняла ограничение «только для чтения» в SQL Server 2014. Однако изменение сжатых групп строк по-прежнему непомерно дорого. По этой причине Microsoft «схитрила». Сжатые группы строк по-прежнему доступны только для чтения, а изменения отслеживаются другим способом.
Когда строки вставляются, они добавляются в так называемую открытую группу строк. Это означает, что они хранятся в обычном кластерном индексе строкового хранения на диске. Ключ для кластерного индекса — это не какой-либо столбец из данных, а номер строки, который генерируется внутренне. Как и сжатые группы строк, открытые группы строк также ограничены 1 048 576 строками. Как только это число достигается, группа строк помечается как закрытая, и для новых вставок создаётся новая открытая группа строк.
Асинхронный процесс, называемый перемещателем кортежей (tuple mover), ищет закрытые группы строк и сжимает их. Это заменяет закрытую группу строк сжатой группой строк.
Обратите внимание, что описанный выше процесс применяется только для так называемой «поточной вставки» (trickle insert). Для любой массовой загрузки, при которой через Bulk Insert API добавляется 102 400 строк или более, они будут помещены в новую группу строк и мгновенно сжаты. Меньшие размеры пакетов, даже через Bulk Insert API, по-прежнему добавляются в открытую группу строк.
Когда вы удаляете строку, которая находится в сжатой группе строк, она фактически не удаляется из сжатых сегментов. Вместо этого они отслеживаются в битовой карте удалённых строк (deleted bitmap). Это название немного вводит в заблуждение: это на самом деле не битовая карта, а B-дерево с составным столбцом: позиция каждой удалённой строки в группе строк. Итак, если бы мы запустили удаление строки, хранящейся в позиции №384 306 группы строк 2, значение (2; 384 306) будет сохранено в B-дереве битовой карты удалённых. Данные строки по-прежнему находятся в сжатой группе строк (чтобы нам не пришлось изменять данные здесь), но помечены как удалённые, что означает, что операторы плана выполнения не будут обрабатывать эту строку.
Обратите внимание, что минимальные и максимальные значения затронутых сегментов, хранящиеся в метаданных, не затрагиваются. Даже если удалённая строка оказалась минимальным или максимальным значением, SQL Server не будет повторно сканировать весь сегмент, чтобы найти новое минимальное или максимальное значение.
Когда строки в сжатой группе строк обновляются, SQL Server просто комбинирует методы удаления и вставки данных. Существующая строка помечается как удалённая в битовой карте удалённых строк, а новая строка с актуальными значениями добавляется в открытую группу строк. Не существует процесса массового обновления, который бы мгновенно сжимал группу строк, поэтому даже если обновление затрагивает миллионы строк, все они добавляются через процесс поточной вставки, а затем асинхронно сжимаются перемещателем кортежей.
Когда было изменено много данных, может быть полезно перестроить columnstore-индекс. Это фактически удалит данные для всех удалённых и обновлённых строк, повторно отсортирует каждую группу строк для оптимального сжатия и найдёт новые правильные минимальное и максимальное значения в каждом сегменте.
Всё вышеперечисленное (открытые и закрытые группы строк, а также битовая карта удалённых строк) в совокупности называется хранилищем изменений (дельта-хранилищем, delta store). Каждый columnstore-индекс имеет дельта-хранилище, а секционированный columnstore-индекс даже имеет отдельное дельта-хранилище для каждой секции.
Кластерный или некластерный
Все вышеприведённые описания одинаково применимы как к кластерным, так и к некластерным columnstore-индексам. Хотя существуют некоторые незначительные технические различия в обработке битовой карты удалённых строк, они выходят за рамки данной статьи.
Упорядоченный columnstore
Распространённой лучшей практикой является принудительное упорядочение данных в определённом порядке перед построением (или перестроением) columnstore-индекса, например, сначала создав кластерный rowstore индекс. Это не приводит к сохранению этого порядка данных внутри каждой группы строк (помните, каждая группа строк упорядочена для достижения оптимального сжатия). Но это влияет на то, какие строки попадают в какую группу строк, что, при правильном проектировании, может повысить эффективность исключения групп строк (см. ниже).
Чтобы упростить этот процесс, Microsoft представила упорядоченные columnstore-индексы в SQL Server 2016 (для кластерных columnstore-индексов) и SQL Server 2025 (для некластерных columnstore-индексов). Несмотря на то, что предполагает название, данные в этих индексах по-прежнему неупорядочены. Единственное, что отличает упорядоченный columnstore-индекс от обычного, — это то, что SQL Server автоматически упорядочит данные перед построением (или перестроением) сжатых групп строк, чтобы получить оптимальные минимальные и максимальные значения в каждом сегменте для более эффективного исключения групп строк. Внутри каждой группы строк данные затем по-прежнему переупорядочиваются для оптимизации сжатия. И будущие изменения по-прежнему обрабатываются точно так же, как и для обычных columnstore-индексов. Только перестроение затем, ещё раз, приведёт к сортировке данных перед построением новых сжатых групп строк.
Чтение из columnstore-индекса
Теперь, когда мы знаем, как структурированы данные в columnstore-индексе, давайте посмотрим, как данные могут быть прочитаны в план выполнения.
Сканирование Columnstore-индекса
Когда вы видите Сканирование Columnstore-индекса (Columnstore Index Scan) в плане выполнения, интерфейсный инструмент, который вы используете, на самом деле обманывает вас. В XML плана выполнения это просто Сканирование индекса (Index Scan), но со свойством Storage, установленным в Columnstore. Некоторые интерфейсные инструменты распознают это свойство Storage, чтобы затем отображать другой значок и имя оператора.
Сканирование Columnstore-индекса минимизирует необходимую работу двумя способами:
- Исключение столбцов (Column elimination): на основе свойств
Output ListиPredicateоно обращается только к сегментам, которые хранят данные для столбцов, которые необходимо вернуть, или которые используются в фильтре, протолкнутом в сканирование. Сегменты для всех других столбцов вообще не используются. Исключение столбцов применяется только к сжатым группам строк, поскольку открытые и закрытые группы строк хранятся построчно в B-дереве. - Исключение групп строк (часто ошибочно называемое исключением сегментов): для каждого условия в свойстве
PredicateSQL Server смотрит на минимальное и максимальное значение в метаданных каждой сжатой группы строк. Если это такое, что ни одна из строк в группе строк не может соответствовать условию, то вся группа строк пропускается. Исключение групп строк применяется только к сжатым группам строк, поскольку минимальное и максимальное значение не хранятся в метаданных для открытых и закрытых групп строк.
Для оставшихся групп строк все соответствующие сегменты плюс битовая карта удалённых строк считываются синхронно, и каждая комбинация значений из них возвращается, если только битовая карта удалённых строк не помечает строку как удалённую или свойство Predicate не приводит к отбрасыванию строки. При работе в режиме пакетной обработки (batch mode) данные возвращаются пакетами, а не строка за строкой.
Открытые и закрытые группы строк, конечно, также сканируются и возвращаются. Здесь процесс точно такой же, как и для сканирования индекса (Index Scan) обычного rowstore индекса.
Обратите внимание, что Сканирование Columnstore-индекса не отображает свои логические чтения в своём свойстве Actual I/O Statistics. Значения здесь всегда отображаются как ноль. Чтобы увидеть объём работы, выполненной Сканированием Columnstore-индекса, вам необходимо использовать SET STATISTICS IO ON. И в выводе обязательно смотрите не только на логические чтения (logical reads), но и на логические чтения больших объектов (lob logical reads).
Поскольку данные в columnstore-индексе не упорядочены (даже для упорядоченного columnstore-индекса), свойство Ordered Сканирования Columnstore-индекса всегда имеет значение False.
Нет поиска по Columnstore-индексу
Columnstore-индексы не предназначены для быстрого извлечения конкретных строк. Как следствие, SQL Server не поддерживает операторы поиска (seek) на columnstore-индексах.
Обратный поиск по ключу
Обратный поиск по ключу (Key Lookup) фактически аналогичен поиску по кластерному индексу (Clustered Index Seek). Как таковой, можно было бы ожидать, что обратный поиск по ключу также не поддерживается на columnstore-индексе. И до SQL Server 2014 это было правдой. Однако это было изменено в SQL Server 2016.
Некластерный индекс на таблице с кластерным columnstore-индексом будет на своих листовых страницах хранить местоположение каждой строки внутри columnstore-индекса. В планах выполнения это называется ColStoreLoc. Хотя это не документировано, я предполагаю, что это комбинация номера группы строк и порядкового номера внутри группы строк.
Обратный поиск по ключу на columnstore-индексе сначала выполнит то же исключение столбцов и групп строк, что и сканирование Columnstore-индекса. Однако, поскольку обратный поиск по ключу всегда вызывается для одного ColStoreLoc, он пытается исключить только указанную конкретную группу строк, и в случае успеха вообще ничего не читает.
Если группу строк нельзя исключить, обратный поиск по ключу прочтёт необходимые группы строк и распакует их. Из-за того, как работает сжатие, это включает как минимум распаковку всех строк до требуемой. Так что если мы ищем строку 20 в группе строк, это не огромная проблема. Но для строки 964 491 это огромная задача! Как следствие, оптимизатор выберет стратегию обратного поиска по ключу только если поддерживающий некластерный индекс обеспечивает чрезвычайно избирательный фильтр.
Так же, как и для сканирования Columnstore-индекса, обратный поиск по ключу на columnstore-индексе не даёт никакой информации в свойстве Actual I/O Statistics; вам нужно смотреть на логические чтения (logical reads) и логические чтения больших объектов (lob logical reads) в выводе SET STATISTICS IO ON.
Заключение
SQL Server поддерживает не более одного columnstore-индекса на таблице. Хотя кластерная версия обычно предпочтительна, некластерная также поддерживается.
Данные в columnstore-индексе сжаты и оптимизированы для быстрого извлечения подмножества столбцов для большого количества строк — без фильтра или с не очень избирательным фильтром. Это затем выполняется с помощью сканирования Columnstore-индекса, которое сканирует каждую группу строк, которую нельзя исключить.
Хотя обратный поиск по ключу на кластерном columnstore-индексе поддерживается, это очень неэффективный способ доступа к данным, и он будет выбран оптимизатором только в том случае, если он оценивает фильтр по некластерному индексу как чрезвычайно избирательный.
На этом завершается этот очень общий и широкий обзор columnstore-индексов. В следующем эпизоде я расскажу об индексах, оптимизированных для памяти.
Комментариев нет:
Отправить комментарий