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

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

ПУБЛИКАЦИИ  

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

С Вами когда-либо случалось, что бы Вы верили во что то так сильно, что никогда даже не пытались проверить, что это так на самом деле, потому что это казалось настолько очевидным, и однажды Вам вдруг стало понятно, что Вы были неправы? Что-то подобное случилось со мной недавно…
Прежде, чем обнародовать подробности этого случая, я хотел бы выразить мою самую глубокую благодарность Лабору Коллару (Lubor Kollar) и Срайкамару Рангараджану (Srikumar Rangarajan), которые помогли мне добраться до сути вещей, объяснили мне, как работает то, что я не смог понять самостоятельно.
Лабор также дал мне много разъяснений и советов!
Всё нижеизложенное относится к SQL Server: 2000 Dev/SP4 и 2005 Dev/SP1
Я начну с, казалось бы, простого вопроса: "Если для таблицы T1 с кластеризованным индексом по полю col1 выполнить показанный ниже запрос, есть ли гарантия, что возвращаемые данные будут отсортированы по кластеризованному индексу?".

SELECT * FROM T1;

Пробуйте ответить на этот вопрос, и взвесить все доводы возможного ответа.
Очевидно, ответ не будет простым, и причина в том, что он связан с глубинными процессами сервера.
Наберитесь немного терпения, поскольку я должен вначале рассмотреть некоторые критически важные принципы, которые могут быть Вам уже известны, но они необходимы для того, чтобы приступить к главной теме, и для понимания получаемых результатов.
Я хотел бы подчеркнуть позицию по этому вопросу ANSI SQL стандарта. Согласно этому стандарту, такой запрос должен возвращать результирующий набор, и поскольку для набора не указано упорядочивание строк, результат считают правильным независимо от порядка, в котором строки возвращены. Если нужно гарантировать определённый порядок, необходимо добавить предложение ORDER BY.
Применительно к SQL Server, это означает, что возможно получение неупорядоченных данных, поскольку такое поведение соответствует стандарту.
В этой статье будут предложены некоторые наблюдения, касающиеся темы статьи, а также проанализированы распространённые мнения и развенчаны некоторые мифы.

Кластеризованный индекс физически упорядочивает данные (распространённое заблуждение - миф)

Часто полагают, что создание кластеризованного индекс для таблицы приводит к физическому упорядочиванию данных на диске в соответствии с порядком ключа индекса.
Термин "физический" сам по себе вызывает сомнения, поскольку данные таблицы/индекса располагаются на страницах, которые группируются в экстенты, которые спрятаны в файлах. Файлы на диске не всегда непрерывны из-за фрагментации файловой системы, не говоря уже о файлах, которые находятся на RAID-массиве, и/или когда для базы данных создано несколько файлов. Для простоты, будем работать с базой данных, которая имеет только один файл для данных, помещенный на единственный диск, и будем игнорировать фрагментацию файловой системы; то есть предполагается, что файл только один и он храниться непрерывным куском.
Обратите внимание, что даже записи на одной странице не всегда физически отсортированы. SQL Server организует сортировку записей на странице посредством массива смещений строк, который размещён в конце каждой страницы.
Продемонстрируем, что страницы на листовом уровне кластеризованного индекса не всегда упорядочены в файле в том порядке, который диктует логика ключа индекса (поддерживаемая списком связей листового уровня индекса). Для этого, создадим таблицу T1 с кластеризованным индексом по полю col1; заполним таблицу в цикле, который за каждую итерацию вставляет по одной строке со случайным значением для поля col1:

SET NOCOUNT ON; USE master; GO IF DB_ID('testdb') IS NULL CREATE DATABASE testdb; GO USE testdb; GO IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( col1 INT NOT NULL, filler CHAR(2000) NOT NULL DEFAULT('a') ); CREATE CLUSTERED INDEX idx_cl_col1 ON dbo.T1(col1); GO DECLARE @i AS INT; SET @i = 1; WHILE @i <= 300 BEGIN INSERT INTO dbo.T1(col1) VALUES(1 + ABS(CHECKSUM(NEWID()) % 1000)); SET @i = @i + 1; END

Случайные значения нужны для того, чтобы строки записывались в кластеризованный индекс случайным образом, провоцируя разбиение страниц, которое в свою очередь будет порождать логическую фрагментацию. Логическая фрагментация выражена как процент не соответствующих порядку ключа индекса страниц; то есть несоответствие между логическим порядком, продиктованным списком связей индекса, с тем порядком, в котором страницы записываются в файл.
Миф о том, что кластеризованный индекс физически упорядочивает данные, можно опровергнуть существованием логической фрагментации; или другими словами можно сказать, что если бы миф был правдой, то тогда логической фрагментации не существует.
Показанный ниже код запускался на SQL Server 2005, он показывает уровень логической фрагментации в кластеризованном индексе (в SQL Server 2000 для этого используется DBCC SHOWCONTIG):

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID('testdb'), OBJECT_ID('dbo.T1'), 1, NULL, NULL );

Был получен результат 96.5217391304348, который означает, что имеется более 96 процентов не соответствующих порядку ключа индекса страниц. Если нужны дополнительные доказательства того, что страницы не всегда находятся в файле в том же порядке, как это прописано в списке связей, используйте команду DBCC IND, которая показывает указатели страниц в списке связей:

DBCC IND ('testdb', 'dbo.T1', 0);

Чтобы облегчит понимание листринга, возвращаемого этой командой, запустите представленный ниже скрипт, который выводит строки с адресами страниц в том порядке, в каком они прописаны в списке связей:

CREATE TABLE #DBCCIND ( PageFID INT, PagePID INT, IAMFID INT, IAMPID INT, ObjectID INT, IndexID INT, PartitionNumber INT, PartitionID BIGINT, iam_chain_type VARCHAR(100), PageType INT, IndexLevel INT, NextPageFID INT, NextPagePID INT, PrevPageFID INT, PrevPagePID INT ); INSERT INTO #DBCCIND EXEC ('DBCC IND(''testdb'', ''dbo.T1'', 0)'); WITH LinkedList AS ( SELECT 1 AS RowNum, PageFID, PagePID FROM #DBCCIND WHERE IndexID = 1 AND IndexLevel = 0 AND PrevPageFID = 0 AND PrevPagePID = 0 UNION ALL SELECT PrevLevel.RowNum + 1, CurLevel.PageFID, CurLevel.PagePID FROM LinkedList AS PrevLevel JOIN #DBCCIND AS CurLevel ON CurLevel.PrevPageFID = PrevLevel.PageFID AND CurLevel.PrevPagePID = PrevLevel.PagePID ) SELECT CAST(PageFID AS VARCHAR(MAX)) + ':' + CAST(PagePID AS VARCHAR(MAX)) + ' ' AS [text()] FROM LinkedList ORDER BY RowNum FOR XML PATH('') OPTION (MAXRECURSION 0); DROP TABLE #DBCCIND;

Вот такой результат был получен при запуске этого скрипта на компьютере автора:

1:109 1:1567 1:1531 1:1488 1:1541 1:1508 1:1523 1:1558 1:1529 1:1501 1:1570 1:1536 1:1569 1:1491 1:1525 1:1539 1:1555 1:1486 1:1480 1:1571 1:1526 1:80 1:1527 1:1528 1:1557 1:1502 1:1548 1:1587 1:1510 1:1498 1:1483 1:1568 1:1554 1:1500 1:1519 1:1564 1:1530 1:1489 1:1575 1:1524 1:1562 1:1550 1:41 1:1583 1:1534 1:1549 1:1492 1:1566 1:1540 1:1515 1:1538 1:1551 1:1503 1:1490 1:1504 1:1559 1:174 1:1560 1:1514 1:89 1:1537 1:1563 1:1495 1:1509 1:1580 1:1505 1:1496 1:1542 1:1556 1:1573 1:1512 1:1577 1:1533 1:1485 1:1494 1:1520 1:1521 1:1547 1:1543 1:1516 1:1565 1:1497 1:1552 1:1578 1:1579 1:1581 1:1522 1:110 1:1586 1:1532 1:1507 1:1576 1:1481 1:1518 1:1487 1:1582 1:1506 1:1544 1:1511 1:1572 1:1553 1:77 1:1574 1:1546 1:1585 1:1517 1:1482 1:1493 1:1513 1:1499 1:1561 1:1535 1:1484 1:1584 1:1545

Здесь ясно видно, что многие страницы идут не по порядку (следующая по порядку списка страница оказывается расположенной в файле раньше).
Обратите внимание, что при создании или пересоздании индекса для существующей таблицы, SQL Server старается сделать его непрерывным (с минимальной фрагментацией), но это не гарантируется, особенно если не определена опция SORT_IN_TEMPDB, или если операция выполняется параллельно. Когда Вы определяете опцию SORT_IN_TEMPDB, и создание/пересоздание индекса выполняется в одном потоке, этим можно добиться высокой непрерывности расположения экстентов индекса; но всё равно не будет никаких гарантий, что в результате получится полностью не фрагментированный индекс.
Ниже представлен отрывок из Books Online, который объясняет, почему такое возможно:

"Благодаря параметру SORT_IN_TEMPDB может быть повышена непрерывность экстентов индекса, особенно если параллельно не обрабатывается операция CREATE INDEX. Экстенты рабочей области сортировки освобождаются довольно беспорядочно относительно их местоположения в базе данных. Если рабочие области сортировки содержатся в целевой файловой группе, то по мере освобождения экстентов сортировки они могут быть задействованы по запросу для хранения структуры индекса в процессе ее построения. Это может привести к некоторому разупорядочению местонахождений экстентов индексов. Если кластеры страниц сортировки хранятся раздельно в tempdb, то последовательность их освобождения не влияет на местонахождение экстентов индекса. Кроме того, если промежуточные операции сортировки хранятся в tempdb вместо целевой файловой группы, то в целевой файловой группе имеется больше доступного пространства. В результате увеличивается вероятность того, что экстенты индекса будут последовательными".

Заключение: наличие у таблицы кластеризованного индекс не гарантирует того, что данные в файле будут упорядочены в соответствии с порядком ключа индекса.

[В начало]

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

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