(Возврат на основную страницу)
Бурзин Пател, Санджай Мишра
Таблицы с кластеризованными индексами в сравнении с кучами
Группа Microsoft по работе с фирмами-разработчиками, использующими SQL Server
Знакомство с «перемещенными записями» (Forwarded Records) может помочь диагностировать трудные для обнаружения проблемы производительности
Таблицы с кластеризованными индексами в сравнении с кучами
Применимо к версии SQL Server 2005 SP1
В версии
SQL Server 2005 любая таблица либо может иметь
кластеризованные индексы, либо может быть организована как куча (без
кластеризованного индекса).
В этом техническом документе суммируются преимущества и недостатки, различия в
показателях производительности и другие функциональные проявления, свойственные
таблицам со списочной упорядоченностью (кластеризованные индексы), а также
кучам. Подробно описаны результаты наблюдений, сделанных при измерении
производительности в условиях шести различных сценариев, в которых применительно
к таблицам указанных типов выполнялись DML-операции.
Даются рекомендации относительно наиболее эффективного использования преимуществ
каждого из двух типов организации таблиц, и кроме того, приводятся примеры
ситуаций, в которых вы могли бы отдать предпочтение тому или иному типу.
Введение
В СУБД Microsoft SQL Server любая таблица может иметь или не иметь кластеризованный индекс и она также может не иметь ни одного или иметь несколько некластеризованных индексов. Строки данных в таблице с кластеризованным индексом организованы как список, отсортированный в порядке, определяемом столбцами кластеризованного индекса. Строки данных в таблице без кластеризованного индекса не упорядочены и обычно их называют кучей (heap).
Как можно предположить, явные преимущества и явные недостатки есть и у таблиц, организованных как упорядоченные списки, и у таблиц, организованных как кучи. В этом техническом документе суммируются преимущества и недостатки, различия в показателях производительности и другие функциональные проявления, свойственные таблицам со списочной упорядоченностью (кластеризованные индексы), а также кучам. Подробно описаны результаты наблюдений, сделанных при измерении производительности в условиях шести различных сценариев, в которых применительно к таблицам указанных типов выполнялись DMLоперации. Даются рекомендации относительно наиболее эффективного использования преимуществ каждого из двух типов организации таблиц, и кроме того, приводятся примеры ситуаций, в которых вы могли бы отдать предпочтение тому или иному типу.
Кластеризованные индексы и кучи
Кластеризованные индексы и кучи — это два разных способа организации данных в таблицах. Кластеризованный индекс состоит из страниц индексов и страниц данных. Это значит, хотя название «кластеризованный индекс» и подсказывает, что речь идет об индексе, что это не просто индекс, но такой, который содержит табличные данные. Кластеризованный индекс организован как сбалансированное дерево (Btree), в котором узлы, не являющиеся листьями, это страницы индексов, а листовые узлы — это страницы данных. Данные в кластеризованном индексе упорядочены по столбцу(ам), образующему этот кластеризованный индекс. В сбалансированном дереве страницы любого уровня (как листового, так и нелистового) связаны с предыдущей и последующей страницами того же уровня. Таблица может иметь только один кластеризованный индекс, и в таблицах каталогов (таких как sys.indexes, sys.partitions и т. д.) этот индекс всегда идентифицируется с помощью значения в столбце index_id = 1. Дополнительные сведения об организации кластеризованных индексов можно найти в разделе Clustered Index Structures в документации SQL Server 2005 Books Online.
Куча состоит только из страниц данных. Ни для страниц данных, ни для физического расположения этих страниц не гарантируется какойлибо конкретный порядок. В таблицах каталогов куча всегда идентифицируется с помощью значения в столбце index_id = 0. Дополнительную информацию об организации куч ищите в разделе Heap Structures в документации SQL Server 2005 Books Online.
Независимо от того, как организована таблица — как куча, или у нее есть кластеризованный индекс, — она может не иметь ни одного или иметь несколько некластеризованных индексов. Некластеризованный индекс организован как сбалансированное дерево. В отличие от кластеризованного, некластеризованный индекс состоит только из страниц индексов. Листовые узлы в некластеризованном индексе не являются страницами данных, но содержат указатели на отдельные строки, находящиеся на страницах данных. Некластеризованный индекс идентифицируется в таблицах каталогов по значению столбца index_id, превышающему единицу. Дополнительную информацию об организации некластеризованных индексов ищите в разделе Nonclustered Index Structures в документации SQL Server 2005 Books Online.
Цели тестирования
Целью нашего тестирования было получение характеристик производительности и функционального поведения DMLопераций, выполняемых применительно к одному и тому же набору табличных данных, организованных как:
• Куча с некластеризованным индексом по заданному набору столбцов.
• Таблица с кластеризованным индексом по тому же набору столбцов в отсутствие какоголибо другого некластеризованного индекса.
Рассмотрение функционального поведения таблицы, не имеющей никаких индексов, или такой, у которой есть несколько индексов, выходит за рамки данной статьи.
Мы хотели получить ответ на следующие основные вопросы:
• Всем ли таблицам нужны кластеризованные индексы?
• Какой выигрыш или какие потери для операций INSERT, UPDATE, DELETE и SELECT, выполняемых построчно применительно к большой таблице, обеспечивают наличие кластеризованного индекса или его отсутствие (куча) в условиях рабочей нагрузки с высокой пропускной способностью?
• Чем отличается выполнение запроса, возвращающего диапазон записей, применительно к таблице с кластеризованным индексом от выполнения этого же запроса применительно к таблице с соответствующим некластеризованным индексом?
• К каким эффектам приводит наличие такого индекса, у которого монотонно возрастают значения в первом столбце? Этот тест проводился с целью измерить производительность и определить максимальное количество записей, одновременная вставка которых не приводит к возникновению такого «узкого места», как соперничество за блокировку по причине превращения в «горячую точку» места вставки.
• Каковы показатели, характеризующие использование дискового пространства, когда записи вставляются (INSERT) и удаляются (DELETE) из таблицы с кластеризованным индексом и из таблицы без кластеризованного индекса (куча)?
Знакомство с «перемещенными записями» (Forwarded Records) может помочь диагностировать трудные для обнаружения проблемы производительности
Представьте себе пользователя, использующего приложение от независимого поставщика (ISV), в котором информация об определенном товаре хранится в столбце varchar (200) в таблице базы данных SQL Server. Когда эта система только начинала использоваться, никто никогда не вводил описание товара, длина которого превышала бы десять символов. Однако со временем в перечень были добавлены новые товары, для которых потребовалось использовать в описании префикс. На уровне SQL Server это было сделано с помощью операции обновления, в результате выполнения которой и был добавлен соответствующий префикс. Помимо этого, произошло слияние данной фирмы с другой фирмой и записи с описанием товаров снова изменились: в среднем их длина составила 35 символов. Чтобы внести соответствующие изменения, снова было использовано предложение update. Разумеется, проблем с сохранением изменившихся значений не возникло, поскольку столбец, где хранилось описание товара, был определен как varchar (200).
К сожалению, заказчик обнаружил существенное, не поддающееся объяснению замедление в работе системы. Почему это произошло?
• При обновлении таблицы ее индексы остались без изменений.
• Таблица product использовалась очень интенсивно, но анализ пользовательских запросов подтвердил корректность планов запросов, а также то, что в последнее время эти планы не подвергались никаким изменениям.
• Аппаратура работала без проблем, и количество одновременно работающих пользователей не увеличилось.
• Увеличение размера таблицы изза добавления новых записей не должно было слишком сильно сказаться на производительности, потому что запросы всегда обеспечивали выборку ограниченного количества записей с помощью индекса.
Так что же оставалось в качестве возможной первопричины? Более пристальное знакомство с показаниями нескольких счетчиков производительности показало, что наблюдалось необычайно большое количество операций логического чтения из таблицы product.
А причина вот в чем…
Изза описаний новых товаров возросла длина строк. Обновления строк привели к появлению прямых указателей (forward pointer), потому что строки больше не умещались в старых, отведенных им на странице полях. Этот факт никак не влияет на указатели на строки данных в индексах, но приводит к добавлению прямых указателей в таблицу данных (кучу). Если новая строка оказалась слишком длинной, SQL Server переместит ее в новое поле на странице, а на прежнем месте оставит указатель. Следовательно, впоследствии поиск такой строки потребует дополнительных затрат. Поиск строки данных в куче уже не сводится к прямому обращению по адресам страницы и поля. Вместо того чтобы получить строку данных, серверу, возможно, придется сначала пройти по прямому указателю.
В версии SQL Server 2000 использовалась dbccкоманда showcontig, чтобы доказать эту теорию. Но вам придется воспользоваться опцией «with tableresults», чтобы получить информацию о «перемещенных записях». Распознать эту проблему в предусмотренных по умолчанию выходных данных dbccкоманды showcontig вы не сможете.
С другой стороны, версия SQL Server 2005 предлагает динамическое административное представление (DMV) (sys.dm_db_index_physical_stats()), которое предоставляет информацию о «перемещенных записях» (столбец «forwarded_record_count» в результирующем наборе). В обоих случаях, указанное в выходном наборе количество строк может быть не совсем верным, поскольку это сумма «реального» количества строк и количества «перемещенных записей». Команда select count(*), выполненная применительно к этой таблице, попрежнему, возвращает «реальное», предполагаемое вами, количество строк.
(Возврат на основную страницу)