Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Фрагментации  [new]
Alex_MA
Member

Откуда: Вологда
Сообщений: 190
Здравствуйте!

Подскажите пожалуйста простым языком что такое логическая фрагментация индекса и фрагментация экстента (Литературы почитал, хотелось бы подитожить информацию)?
Спасибо за внимание.
19 мар 14, 11:43    [15751463]     Ответить | Цитировать Сообщить модератору
 Re: Фрагментации  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Alex_MA,

Что такое фрагментация и какая она бывает

Фрагментация, это процесс разделения какой-либо целостной сущности, на множество разрозненных частей — фрагментов. Чем больше фрагментов, тем выше фрагментация.

Говоря о БД, я бы условно разделил виды фрагментации таким образом:

  • 1. Фрагментация файлов БД в файловой системе
  • 2. Фрагментация внутри БД
  • 2.1 Фрагментация внешняя
  • 2.1.1 Логическая фрагментация страниц
  • 2.1.2 Фрагментация экстентов
  • 2.2 Фрагментация внутренняя (плотность страниц)

  • Теперь вкратце по паре слов по каждой из них.

    1. Фрагментация файлов БД в файловой системе
    Вспомним, что БД, физически представляет из себя обычные файлы. А как мы знаем, файлы в файловой системе могут подвергаться фрагментации. И многие знают, что в Windows даже есть встроенная утилита «Дефрагментация диска» для борьбы с этим. Этот вид фрагментации в данной заметке нас не интересует.

    2. Фрагментация внутри базы данных.
    Вспомним, что мы оперируем такими понятиями как таблицы, индексы, страницы, экстенты и т.д. Данные относящиеся к разным объектам не обязательно располагаются в файле упорядоченно и непрерывно. Это и вызывает такого рода фрагментацию. Теперь конкретнее.

    2.1.Внешняя фрагментация.
    Внешняя фрагментация представляет собой фрагментацию страниц листового уровня индекса или фрагментацию экстентов, если таблица является кучей. Подробнее написано в документации, поэтому чтобы ее не пересказывать, скажу вкратце своими словами.

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

    Фрагментация экстентов — когда физически за экстентом А следующим является экстент В не принадлежащий к тому же объекту что экстент А (т.е. грубо говоря экстенты разных таблиц в файле перемешаны между собой).

    2.2 Внутренняя фрагментация.
    Внутренняя фрагментация, это фрагментация данных на уровне страниц. Здесь не имеется в виду то, что строки идут неупорядочено. Они запросто могут идти неупорядочено, порядок поддерживается на уровне массива указателей на строки. Т.е. при добавлении новой строки, проще изменить порядок указателей в массиве и добавить строку в любое свободное место на странице, чем сдвигать все строки по странице. Это не будет фрагментацией. Здесь под фрагментацией понимается насколько плотно заполнены страницы. Страницы могут быть заполнены не полностью, например, из-за обновлений приводящих к переносу строк или расщеплений страницы в случае индексов и т.д. Понятно, что чем менее плотно лежат данные на странице, тем больше страниц потребуется для представления одной и той же информации.
    19 мар 14, 12:23    [15751819]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    А хранение таблицы в заранее отсортированном виде - это кластеризованная таблица ? (т.е. таблица у которой есть кластерный индекс ?)
    19 мар 14, 14:26    [15752905]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Не могли бы вы еще прояснить некоторые моменты:

    (1)Индекс - это всегда отсортированные данные по ключу индекса ?

    (2)Когда имеет смысл задавать много ключей кластерного индекса ?
    Как я понимаю, то это необходимо, когда к базе делаются запросы на получение данных,
    где в условии WHERE используется как раз поля ключи кластерного индекса ?


    (3)Если на таблице есть кластерный индекс и не кластерный, то при попытке выборки в условии WHERE используется поле ключ не кластерного индекса, то
    данные будут все равно получены методом сканирования таблицы ?
    А если в условии WHERE используется поле ключ не кластерного индекса (у которого INCLUDE включены определенные поля - которые являются ключами кластерного индекса),
    то получение данных будет происходить по этому ключу кластерного индекса ?
    19 мар 14, 14:42    [15753005]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    (4)Не кластеризованный индекс должен хранить в себе ссылку на ключ кластеризованного индекса ?
    А так же может хранить идентификатор строки таблицы в случае таблицы в куче ?
    19 мар 14, 14:47    [15753042]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Что то я не пойму:

    Из msdn:

    Кластеризованный
    >>Кластеризованные индексы сортируют и хранят строки данных в таблицах или представлениях на основе их ключевых значений.
    У кластеризованного индекса может быть несколько столбцов ключей индекса. => несколько сортировок данных по этим полям ???
    19 мар 14, 15:03    [15753184]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    Alex_MA,

    У вас очень много вопросов по базовым вещам, может быть вам начать с того, что еще раз литературы прочитать, т.к. складывается впечатление что вы упустили смысл многих вещей.

    Может другие источники посмотреть, более наглядные? Вот есть серия видео, где отвечают на многие ваши вопросы, в картинках с презентациями и по-русски.
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 1 из 3)
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 2 из 3)
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 3 из 3)
    19 мар 14, 15:40    [15753493]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    SomewhereSomehow
    Alex_MA,

    У вас очень много вопросов по базовым вещам, может быть вам начать с того, что еще раз литературы прочитать, т.к. складывается впечатление что вы упустили смысл многих вещей.

    Может другие источники посмотреть, более наглядные? Вот есть серия видео, где отвечают на многие ваши вопросы, в картинках с презентациями и по-русски.
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 1 из 3)
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 2 из 3)
    Все, что Вы всегда хотели знать об индексах, но боялись спросить (часть 3 из 3)


    Я только начал изучение этой темы.
    Спасибо за помощь.
    19 мар 14, 15:51    [15753594]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    По кластерному индексу:

    Правильно ли я понимаю, что данные таблицы хранятся физически в указанном порядке (в файле ), а отдельные строки в таблице однозначно идентифицируются с помощью кластеризованного ключа - так называются столбцы, которые определяют кластеризованный индекс ?

    Например:
    http://gyazo.com/10d8fccfa645920407c77cebc36fcb8b

    физическое хранение данных индекса
    http://gyazo.com/02764a6f44e02f4229e1bb62bc27a5bf
    19 мар 14, 18:03    [15754666]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    o-o
    Guest
    Alex_MA
    По кластерному индексу:

    Правильно ли я понимаю, что данные таблицы хранятся физически в указанном порядке (в файле )


    явно кто-то не хочет почитать на тему.

    неправильно понимаете.
    и выше уже было написано:
    на странице строки могут лежать как угодно.
    а что они "упорядочены" означает лишь то, что упорядочены row offset-ы -- указатели на начало каждой строки
    т.е. первым в массиве указателей (row offset array) будет указатель на "первую в заданном порядке" строку, 2-ой на 2-ую и т.д:

    The row offset array indicates the logical order of rows on a page. For example, if a table
    has a clustered index, SQL Server stores the rows in the order of the clustered index key. This
    doesn’t mean the rows are physically stored on the page in the order of the clustered index
    key. Rather, slot 0 in the offset array refers to the fi rst row in the clustered index key order,
    slot 1 refers to the second row, and so forth. As we’ll see shortly when we examine an actual
    page, the physical location of these rows can be anywhere on the page.

    сами страницы тоже не обязаны идти в определенном порядке:
    SomewhereSomehow
    Логическая фрагментация страниц, это когда в двусвязном списке индекса, логически следующая по списку страница, не является следующей в физическом смысле.
    19 мар 14, 18:38    [15754827]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    >>явно кто-то не хочет почитать на тему.

    Очень даже читаю и смотрю. Просто хочу разобраться, не всем же дано с первого раза все понять.
    А вот тут я нашел то, о чем вы говорите что я не прав:

    Автор Роберт Виейра
    http://gyazo.com/0599860ab5390aec94af45c3d896c835
    20 мар 14, 07:58    [15756735]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Еще просмотрел видео по оптимизации баз данных в MS SQL Server 2005
    http://gyazo.com/e499b0aa164b271932899baaf9ce8821

    Здесь коричневым нарисован кластерный индекс, автор курса говорит что все данные в файле хранятся в упорядоченном виде.
    Если в кластерный индекс идет какая то вставка (если не в конец индекса), то возникает дорогостоящая операция раздвигание страниц "Page Split".
    20 мар 14, 08:03    [15756749]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Будьте добры, подскажите пожалуйста, правильно ли я понимаю, что если есть кластерный и не кластерный индекс, то в не кластерном индексе на листовом уровне будет храниться указатель по которому будут искаться данные уже в кластерном индексе ?

    Картинка с другого сайта.

    Не кластерный индекс нарисован под кластерным.

    1. Ищутся данные по ключу не кластерного ключа;
    2. После того как они найдены, определяется ключ кластерного ключа (КЛЮЧ КЛАСТЕРНОГО КЛЮЧА ВСЕГДА ЕСТЬ В НЕ КЛАСТЕРНОМ ИНДЕКСЕ ПРИ УСЛОВИИ ЧТО ОН СОЗДАН НА КЛАСТЕРИЗОВАННОЙ ТАБЛИЦЕ - ВЕДЬ ТАК ???);
    3. Спускаемся до листового уровня кластерного ключа и получаем данные.
    20 мар 14, 09:22    [15756955]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Почитав еще немного сложилась следующая картина:

    Кратко про индексы

    Все индексы организованы в виде B-деревьев;

    а) Кластерный индекс - это дерево с данными на листовых узлах; При создании кластерного индекса физическое расположение данных перестраивается в соответствии со структурой индекса; (ЗДЕСЬ ВОПРОС: Что за физическое расположение ? Я так понимаю место в файле данных, mdf например);
    б)Столбцы ключа индекса должны обеспечивать уникальность записей в таблице; Столбцы ключа не должны очень часто меняться - это может вызвать каскадное изменение B-дерева; Желательно, чтобы ключ индекса был автоинкрементирован (либо все время уникальный) - избавляемся от разбиения страниц индекса, а так же как возможное следствие разбиения страниц - каскадного изменения B-дерева; Каскадное изменение достаточно дорогостоящая операция.
    в)Таблица без кластерного индекса является "КУЧЕЙ или heap таблицей".

    г)Heap-таблица может иметь не кластерный индекс. Для не кластерного индекса справедливо описание из п.(б);
    Указатель листового уровня ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру не кластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице;

    д)Если на кластерной таблице построен не кластерный индекс, то на листовых узлах B-дерева имеется кластеризованный ключ, при помощи которого происходит поиск по кластерному индексу.

    Теперь по фрагментации:

    В процессе эксплуатации базы к ней постоянно выполняются запросы чтения, записи...
    В процессе записи какой либо строки данных в таблицу могут происходить разбиения страниц (если на начальной странице не хватало места).
    Потом эти данные могут удаляться, таким образом возникает не заполненное место на страницах - ЭТО как я понимаю называется внутрестраничной фрагментацией. В результате для поиска данных требуется поднимать большее количество страниц (т.к. хранение данных является разряженным), соответственно больше операций i/o и ОЗУ может использоваться не эффективно (ВОПРОС: Могут ли быть полностью пустые страницы ?);

    В советах msdn нашел таблицу, где написано, если фрагментация индекса <30% - нужно делать реорганайз таблицы - это затрагивает только листовой уровень индекса. Хотя я не могу понять как можно уменьшить фрагментированность листовых узлов не затрагивая родительские узлы. Т.е. если необходимо переместить данные из одной страницы на другую, а у них есть один родитель => указатели родительской страницы должны быть перестроены.

    >30% полный ребилд;

    Внешняя - не знаю
    Но есть такое определение: страницы хранятся не в логической последовательности. -Не понимаю. Помогите разобраться.
    20 мар 14, 14:56    [15760092]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    o-o
    Guest
    Alex_MA
    г)Heap-таблица может иметь не кластерный индекс. Для не кластерного индекса справедливо описание из п.(б);
    Указатель листового уровня ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру не кластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице;


    сами себе и противоречите.
    если пункт г) описывает кучу, то о каком кластерном индексе идет речь
    в некластерном индексе на этой куче? "Указатель листового уровня" как раз и есть физический указатель, RID называется.
    указывает на страницы самой кучи.
    20 мар 14, 15:05    [15760178]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    o-o
    Alex_MA
    г)Heap-таблица может иметь не кластерный индекс. Для не кластерного индекса справедливо описание из п.(б);
    Указатель листового уровня ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру не кластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице;


    сами себе и противоречите.
    если пункт г) описывает кучу, то о каком кластерном индексе идет речь
    в некластерном индексе на этой куче? "Указатель листового уровня" как раз и есть физический указатель, RID называется.
    указывает на страницы самой кучи.


    Я забыл отступ абзаца сделать.
    20 мар 14, 16:12    [15760793]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Mind
    Member

    Откуда: Лучший город на Земле
    Сообщений: 2322
    Alex_MA
    Будьте добры, подскажите пожалуйста, правильно ли я понимаю, что если есть кластерный и не кластерный индекс, то в не кластерном индексе на листовом уровне будет храниться указатель по которому будут искаться данные уже в кластерном индексе ?
    Да
    Alex_MA
    2. После того как они найдены, определяется ключ кластерного ключа (КЛЮЧ КЛАСТЕРНОГО КЛЮЧА ВСЕГДА ЕСТЬ В НЕ КЛАСТЕРНОМ ИНДЕКСЕ ПРИ УСЛОВИИ ЧТО ОН СОЗДАН НА КЛАСТЕРИЗОВАННОЙ ТАБЛИЦЕ - ВЕДЬ ТАК ???);
    Да

    Alex_MA
    При создании кластерного индекса физическое расположение данных перестраивается в соответствии со структурой индекса; (ЗДЕСЬ ВОПРОС: Что за физическое расположение ? Я так понимаю место в файле данных, mdf например);
    Спрашивайте у того кто написал эту статью, что он имеет ввиду. Но порядок расположение данных на диске никак не гарантируется логическими структурами SQL Server'а, будь то индекс или кластерный индекс.

    Alex_MA
    (ВОПРОС: Могут ли быть полностью пустые страницы ?);
    Могут

    Alex_MA
    реорганайз таблицы - это затрагивает только листовой уровень индекса. Хотя я не могу понять как можно уменьшить фрагментированность листовых узлов не затрагивая родительские узлы.
    Читайте внимательнее:
    "It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes."
    Разницу между затрагивает и дефрагментирует понимаете?

    Alex_MA
    Внешняя - не знаю
    Но есть такое определение: страницы хранятся не в логической последовательности. -Не понимаю. Помогите разобраться.
    Вам SomewhereSomehow уже написал, что конкретно непонятно то?
    20 мар 14, 22:11    [15762716]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    Mind
    Alex_MA
    Будьте добры, подскажите пожалуйста, правильно ли я понимаю, что если есть кластерный и не кластерный индекс, то в не кластерном индексе на листовом уровне будет храниться указатель по которому будут искаться данные уже в кластерном индексе ?
    Да

    Нет.

    Точнее, не только.

    Ключ кластерного индекса хранится также и на нелистовом уровне некластерного.

    https://www.sql.ru/blogs/shcherbinin/1011
    20 мар 14, 23:46    [15763247]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Minamoto
    Member

    Откуда: Москва
    Сообщений: 1162
    Alex_MA
    ЗДЕСЬ ВОПРОС: Что за физическое расположение ? Я так понимаю место в файле данных, mdf например);

    Представьте себе кластерный индекс как некластерный, только с INCLUDE всех полей. Примерно так. Ключ кластерного индекса содержится в нелистовых страницах кластерного индекса, а все данные - в листовых страницах.

    Только при создании кластерного индекса данные переносятся из кучи в кластерный индекс, т.е. единственное "физическое" их место хранения - это индекс. С точки зрения содержимого файла mdf ничего особо не меняется - это все те же страницы внутри файла.
    20 мар 14, 23:51    [15763264]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Алекс1990
    Guest
    SomewhereSomehow
  • 2.1 Фрагментация внешняя
  • 2.1.1 Логическая фрагментация страниц

  • SomewhereSomehow
    Логическая фрагментация страниц, это когда в двусвязном списке индекса, логически следующая по списку страница, не является следующей в физическом смысле.

    SomewhereSomehow, о чем тут идет речь ? О смешанных экстентах ? Вроде файл базы нарасчивается экстентами и заполняется тоже экстентами => байты одного экстента всегда лежат последовательно(не считая фрагментации файловой системы). Т.е. страницы одного объекта всегда лежат одна за одной в рамках одного экстента. НЕ понятна ваша мысль, как один экстент может быть разбросан по всему файлу ?:)

    Кстати, а данные сортируются по ключу в рамках каждой страницы или каждого экстента ?
    21 мар 14, 23:40    [15769592]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Mind
    Member

    Откуда: Лучший город на Земле
    Сообщений: 2322
    Алекс1990
    SomewhereSomehow
  • 2.1 Фрагментация внешняя
  • 2.1.1 Логическая фрагментация страниц

  • SomewhereSomehow
    Логическая фрагментация страниц, это когда в двусвязном списке индекса, логически следующая по списку страница, не является следующей в физическом смысле.

    SomewhereSomehow, о чем тут идет речь ? О смешанных экстентах ? Вроде файл базы нарасчивается экстентами и заполняется тоже экстентами => байты одного экстента всегда лежат последовательно(не считая фрагментации файловой системы). Т.е. страницы одного объекта всегда лежат одна за одной в рамках одного экстента. НЕ понятна ваша мысль, как один экстент может быть разбросан по всему файлу ?:)
    Причем тут смешанные экстенты?

    Может вот так будет понятнее что такое логическая фрагментация страниц:
    Картинка с другого сайта.

    Алекс1990
    Кстати, а данные сортируются по ключу в рамках каждой страницы или каждого экстента ?
    Если я правильно понял ваш вопрос, то во время перестроения индекса данные сортируются в рамках экстента, равно как и за его пределами, но потом этот порядок никто не гарантирует. Это собственно и есть фрагментация индексов.
    22 мар 14, 02:16    [15770022]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Алекс1990
    Guest
    Mind
    Может вот так будет понятнее что такое логическая фрагментация страниц:

    Да, так понятнее) Страницы принадлежат одному объекту, но при этом находятся не последовательно в экстентах. Непонятно когда такая ситуация возникает ? При Page split ?

    Mind
    Если я правильно понял ваш вопрос, то во время перестроения индекса данные сортируются в рамках экстента, равно как и за его пределами, но потом этот порядок никто не гарантирует. Это собственно и есть фрагментация индексов.

    Ответ понял) Данные сортируются в каждой странице данных. Точнее не данные, а смещения в заголовках.
    22 мар 14, 11:31    [15770461]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    В одном экстенте данные относятся только к одному объекту ?
    23 мар 14, 13:15    [15773168]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31960
    Alex_MA
    В одном экстенте данные относятся только к одному объекту ?
    Зависит от размера объектов. Для больших объектов - да, к одному.
    23 мар 14, 14:48    [15773376]     Ответить | Цитировать Сообщить модератору
     Re: Фрагментации  [new]
    Alex_MA
    Member

    Откуда: Вологда
    Сообщений: 190
    Если в процессе вставки записи на одну из страниц экстента обнаружилось что на этой странице не достаточно места, а на других страницах экстента достаточно (эти страницы были созданы ранее по необходимости, а потом с них данные были удалены), то будет задействованы свободные страницы текущего экстента ?
    23 мар 14, 15:08    [15773404]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить