SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Вопрос на злобу дня - Просмотр кластеризованного индекса - Часть II

ПУБЛИКАЦИИ  

По материалам статьи Itzik Ben-Gan: Quaere Verum - Clustered Index Scans - Part II

Какие методы доступа используются в SQL Server для полного просмотра (сканирования) данных в таблице, когда не определено предложение ORDER BY?

  1. Упорядоченный просмотр кластеризованного индекса

    Отслеживается список связей на листовом уровне кластеризованного индекса с его начала и до конца. Эффективность этого метода доступа зависит от уровня логической фрагментации кластеризованного индекса. Выше фрагментация - медленнее метод доступа. Чуть позже это будет продемонстрировано.

  2. Неупорядоченный просмотр кластеризованного индекса / таблицы

    Список связей на листовом уровне кластеризованных индексов является единственным источником информации, по которому SQL Server может определить местонахождение данных в таблице. Но есть и другой способ, с помощью которого SQL Server может получить запрашиваемые данные, используя для этого карту распределения индекса, называемую IAM (сокращение от Index Allocation Map). В этой статье мы не будет вдаваться в подробности IAM, мы рассмотрим только то, что важно для темы статьи. Страницы IAM, это битовые маски, которые отображают / отслеживают находящиеся на листовом уровне кластеризованных индексов или кучи (таблица без кластеризованного индекса) экстенты, в соответствии с их порядком в файле. SQL Server может иметь одну и более страниц IAM. На странице IAM для каждого экстента в файле имеется по одному биту, который устанавливается в 1, если отображаемый экстент принадлежит объекту на странице IAM, а если нет, то устанавливается в 0 . Каждая страница IAM отображает диапазон файла размером до 4 GB.
    Когда возникает необходимость полного просмотра данных таблицы, у SQL Server есть техническая возможность по страницам IAM таблицы определить, какие страницы/экстенты принадлежат этой таблице. При использовании страниц IAM, просмотр выполняется в соответствии с порядком экстентов в файле (в отличие от порядка по списку связей). Эффективность такого просмотра зависит от фрагментации файловой системы, но, как говорилось в первой части этого цикла статей, мы рассматриваем упрощенный сценарий, по которому имеется только один файл данных, и нет фрагментации файловой системы. По такому сценарию, получается, что неупорядоченный просмотр кластеризованного индекса / таблицы будет лучшим выбором, чем упорядоченный просмотр кластеризованного индекса. При отсутствии логической фрагментации работа обоих методов доступа будет проходить одинаково. Но существенная логическая фрагментация будет снижать производительность упорядоченного просмотра, и, в то же время, не будет влиять на производительность неупорядоченного просмотра.
    Всё это будет справедливо для кучи и для кластеризованной таблицы, а также и для просмотра листового уровня некластеризованных индексов. В случае с кучей, IAM является единственным доступным механизмом, для полного просмотра данных таблицы (неупорядоченный просмотр). В случае с кластеризованной таблицей (или любого индекса), есть два варианта: использование списка связей (упорядоченный просмотр) или использование страниц IAM (неупорядоченный просмотр).
    Обратите внимание, что даже если в плане исполнения показан неупорядоченный просмотр индекса (Ordered: False), это не обязательно подразумевает, что SQL Server будет использовать для просмотра данных страницы IAM, скорее это означает, что SQL Server не обязан просматривать данные в порядке списка связей.

  3. Расширенный просмотр

    Ниже представлен отрывок из Books Online, который поясняет, что такое расширенный просмотр:

    "Одна из функций усовершенствованного компонента просмотра SQL Server выпуска Enterprise Edition позволяет нескольким задачам совместно выполнять полный просмотр таблиц. Если план выполнения инструкции Transact-SQL требует просмотра страниц данных таблицы, а механизм реляционной базы данных обнаружил, что таблица уже просматривается для другого плана выполнения, то компонент Database Engine соединяет второй просмотр с первым в текущем расположении второго просмотра. Компонент Database Engine считывает каждую страницу один раз и передает строки таблицы обоим планам выполнения до конца таблицы.
    К этому моменту первый план выполнения получает полный результат просмотра, в то время как второму плану еще не переданы страницы данных, расположенные до точки соединения. Просмотр для второго плана выполнения возвращается к первой странице данных и выполняется до точки соединения. Таким образом, можно объединять любое количество просмотров. Компонент Database Engine будет продолжать циклическое считывание страниц данных до завершения всех просмотров. Этот метод также иногда называют "циклическим просмотром". По той причине, что порядок результатов инструкции SELECT без предиката не гарантируется
    ".

Мы не будем рассматривать основные принципы, а сразу перейдём к тому моменту, который я хотел бы обсудить …

Недавнее открытие / разоблачение

Вернёмся к самому первому вопросу в первой части этой серии статей: "Имея таблицу T1 с кластеризованным индексом по col1, будет ли представленный ниже запрос гарантировать возвращение данных в порядке кластеризованного индекса?".

SELECT * FROM T1;

Будучи знакомым с основными принципами, сформулированными много лет назад, и поддерживаемыми ANSI SQL, в течение многих лет я полагал, что ответом является - нет. Стандарт заставляет SQL Server возвращать данные в произвольном порядке.
С кучей всё ясно, есть только один разумный способ просмотра неупорядоченных данных путём просмотра, основанного на страницах IAM. В случае с кластеризованной таблицей, хотя SQL Server может использовать список связей или страницы IAM, кажется вполне естественным полагать, что всегда будет использоваться последний способ, так как он более эффективен. До недавнего времени я даже не пытался это проверить, что бы убедиться в истинности такого предположения…
Хотелось бы подчеркнуть, что я все еще убеждён в том, что такой запрос не гарантирует возвращение данных в порядке кластеризованного индекса. Но ниже я продемонстрирую, что всё не так просто, как кажется.
Момент истины для меня случился недавно, когда я пробовал продемонстрировать, что показанный чуть выше запрос возвращает неупорядоченные данные. Машинально я написал подобный показанному ранее код, создающий таблицу T1 и заполняющий её случайными значениями, и выполнил SELECT * без предложения ORDER BY. У меня на языке уже вертелось предложение: "Обращаю Ваше внимание, что данные возвращены неупорядоченными, поскольку SQL Server для определения местонахождения данных использовал страницы IAM". Но к моему удивлению, данные вернулись в порядке кластеризованного индекса:

col1 filler ----------- ------- 5 a 11 a 13 a 14 a 15 a 21 a 22 a 30 a 31 a 32 a 33 a 34 a 36 a 45 a 50 a …

Текстовое представление плана исполнения:

|--Clustered Index Scan(OBJECT:([testdb].[dbo].[T1].[idx_cl_col1]))

В плане не было никакого упоминания, что это была упорядоченная операция (ORDERED FORWARD или ORDERED BACKWARD).
Графический план исполнения показывал просмотр кластеризованного индекса с Ordered: False.
До этого я полагал, что просмотр индекса с Ordered: False просто означает неупорядоченный просмотр, основанный на страницах IAM. Но результирующий набор запроса был упорядочен.
Я проверил фрагментацию, используя DBCC IND и DBCC PAGE, которые показали, что данные находятся в файле в другом порядке, чем определено в списке связей.
Я попробовал заполнить таблицу несколькими другими способами, с разным числом строк, уровнем фрагментации и т.д.; но во всех случаях данные продолжали возвращаться упорядоченными, до тех пор, пока я не попробовал указать подсказку NOLOCK (уровень изоляции - read uncommitted):

SELECT * FROM dbo.T1 WITH (NOLOCK);

И вдруг данные возвратились неупорядоченными:

col1 filler ----------- ------- 5 a 11 a 13 a 14 a 484 a 484 a 358 a 359 a 366 a 845 a 846 a 854 a 858 a 210 a 214 a …

Проведённое после этого расследование с помощью DBCC IND и DBCC PAGE подтвердило, что теперь для просмотра данных SQL Server использовал страницы IAM (порядок распределения).
Не будучи в состоянии объяснить подобное несоответствие, я обратился за советом к моим наставникам из команды разработки подсистемы хранилища Лабору и Срайкамару.

Оказалось, что просмотр в порядке распределения (по страницам IAM) используются для чтения данных в двух случаях: когда определена подсказка NOLOCK или TABLCOK. Так что следующий запрос также вернёт данные в порядке распределения:

SELECT * FROM dbo.T1 WITH (TABLOCK);

col1 filler ----------- ------- 5 a 11 a 13 a 14 a 484 a 484 a 358 a 359 a 366 a 845 a 846 a 854 a 858 a 210 a 214 a …

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

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

В случае с кучей, вставка новых записей или изменение имеющихся не может приводить к расщеплению страниц или перемещению данных. SQL Server ищет первое достаточно большое свободное пространство для размещения новой строки, используя для этого битовые маски, называемые PFS (page free space), и, если свободного пространства недостаточно, он распределяет новую страницу. Когда вследствие изменения записи нужно сохранить более длинное значение, для которого недостаточно места на имеющейся странице, SQL Server перемещает такую непомещающуюся запись в новое место, но сохраняет на старой странице указатель-ссылку на новое местоположение записи. Именно поэтому в куче "безопасно" читать данные, выполняя просмотр в порядке распределения.
Однако, когда речь идёт о данных индекса, всё становиться другим. Данные могут перемещаться по индексу из-за расщеплений страниц, изменения ключа или изменений в столбцах нефиксированной длинны, в результате которых значение существенно увеличивается.
Просмотр в порядке распределения невозможен, если есть вероятность перемещения данных (из-за изменений в своей или в параллельной транзакции). SQL Server может потерять последовательность просмотра, если из-за расщеплений снова перейдёт на уже просмотренную строку.
Следовательно, чтобы гарантировать последовательность, во всех случаях, кроме тех, когда определены подсказки NOLOCK или TABLOCK, SQL Server просматривает данные индекса в порядке их следования в списке связей.

[В начало]

Перевод: Александра Гладченко  2007г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013