Оказывается ли индекс покрытия, когда данные соответствуют порядковому индексу? Ms sql покрывающий индекс

sql - Оказывается ли индекс покрытия, когда данные соответствуют порядковому индексу?

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

Прежде всего, если у вас есть нездоровое увлечение тем, как работает SQL Server под обложками (как и я), исходным источником является "Microsoft SQL Server Internals", Delaney et al. Вам не нужно читать все ~ 1000 страниц, главы на движке хранения достаточно интересны сами по себе.

Я не буду касаться вопроса о том, полезен ли этот конкретный индекс покрытия в этом конкретном случае, потому что, как я думаю, другие ответы хорошо охватывали это (не каламбур), включая рекомендацию использовать INCLUDE для столбцов, которые не нужно индексировать себя.

Второй индекс будет значительно меньше, чем кластеризованный index, SQL Server пришлось бы пройти через меньшее количество страниц на HD, что обеспечит лучшую производительность чтения. Это правильно и вы увидите разница?

Если вы предполагаете, что выбор - это либо чтение страниц кластерного индекса, либо страниц индекса покрытия, индекс покрытия меньше 1 что означает меньшее количество операций ввода-вывода, лучшую производительность, все это любезность. Но запросы не выполняются в вакууме - если это не единственный запрос в таблице, пул буферов может уже содержать большинство или весь кластеризованный индекс, и в этом случае производительность чтения на диске может быть отрицательно повлияна на необходимость чтения менее часто используемый индекс покрытия. Общая производительность также может быть уменьшена за счет общего увеличения страниц данных. Оптимизатор рассматривает только отдельные запросы; он не будет тщательно настраивать использование пула буферов на основе всех запросов в сочетании (удаление страниц происходит с помощью простой политики LRU). Поэтому, если вы слишком сильно создаете индексы, особенно индексы, которые используются нечасто, пострадает общая производительность. И это даже не учитывая внутренние накладные расходы индексов при вставке или обновлении данных.

Даже если мы предположим, что индекс покрытия является чистой выгодой, вопрос "вы видите разницу" (как и в случае, если производительность заметно увеличится) может быть эффективно решена только эмпирически. SET STATISTICS IO ON - ваш друг здесь (а также DBCC DROPCLEANBUFFERS, в тестовой среде). Вы можете попытаться угадать, основываясь на предположениях, но поскольку результат зависит от плана выполнения, размера ваших индексов, объема памяти SQL Server в целом, характеристик ввода-вывода, нагрузки на все базы данных и шаблонов запросов приложений, я бы не сделал этого за пределами приблизительного предположения о том, может ли этот индекс быть полезным. В общем, конечно, если у вас очень широкий стол и небольшой индекс покрытия, нетрудно понять, как это окупается. И в общем, вы скорее увидите плохую производительность из-за недостаточного количества индексов, чем из-за слишком большого количества индексов. Но реальные базы данных не работают на обобщениях.

Чтобы загрузить данные в память, я предполагаю, что SQL Server должен будет загрузите всю строку в память, а затем выберите нужные столбцы. Разве это не увеличило бы потребление памяти?

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

Мое предположение заключается в том, что вы не увидите измеримой разницы в ЦП использование, так как извлечение строки из столбцов само по себе не является CPU операция. Правильно?

Использование ЦП обычно не зависит от размера строки. Время выполнения (и это, в свою очередь, влияет на использование в зависимости от того, сколько запросов вы хотите запускать параллельно). После того, как вы покрыли узкое место ввода-вывода, предоставив вашему серверу много памяти, все равно необходимо проверить данные в памяти.

Я понимаю, что вы не увидите большой разницы в кешировании, потому что SQL Server будет кэшировать только те данные, которые он возвращает, а не весь ряд. Или я не прав?

Строки хранятся на страницах, а SQL Server кэширует страницы, которые он читает в пуле буферов. Он не кэширует результирующие наборы или любые промежуточные данные, созданные как часть выполнения запроса, или отдельные строки. Если вы дважды выполняете запрос в пуле пустых пулов изначально, второй, как правило, быстрее, потому что нужные ему страницы уже находятся в памяти, но это единственный источник ускорения.

С учетом этого, см. ответ на ваш первый вопрос - да, кэширование затронуто, потому что страницы вашего индекса покрытия, если они используются, кэшируются отдельно со страниц кластерного индекса, если они используются.

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

qaru.site

Оказывается ли индекс покрытия, когда данные соответствуют порядковому индексу?

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

Прежде всего, если у вас есть нездоровое увлечение тем, как работает SQL Server под обложками (как и я), исходным источником является «Microsoft SQL Server Internals», Delaney et al. Вам не нужно читать все ~ 1000 страниц, главы на движке хранения достаточно интересны сами по себе.

Я не буду затрагивать вопрос о том, полезен ли этот конкретный индекс покрытия в данном конкретном случае, потому что, как я думаю, другие ответы хорошо охватывали это (каламбур не был предназначен), включая рекомендацию использовать INCLUDE для столбцов, которые не нужны индексироваться.

Второй индекс будет значительно меньше, чем кластеризованный индекс, SQL Server должен будет пройти меньше страниц на HD, что обеспечит лучшую производительность чтения. Это правильно и вы видите разницу?

Если вы предполагаете, что выбор – либо между страницами чтения кластеризованного индекса, либо страницами индекса покрытия, индекс покрытия меньше 1 , что означает меньшее количество операций ввода-вывода, лучшую производительность и всю эту привлекательность. Но запросы не выполняются в вакууме – если это не единственный запрос в таблице, пул буферов может уже содержать большинство или весь кластеризованный индекс, и в этом случае производительность чтения на диске может быть отрицательно повлияна на необходимость чтения менее часто используемый индекс покрытия. Общая производительность также может быть уменьшена за счет общего увеличения страниц данных. Оптимизатор рассматривает только отдельные запросы; он не будет тщательно настраивать использование пула буферов на основе всех запросов в сочетании (удаление страниц происходит с помощью простой политики LRU). Поэтому, если вы слишком сильно создаете индексы, особенно индексы, которые используются нечасто, пострадает общая производительность. И это даже не связано с внутренними издержками индексов при вставке или обновлении данных.

Даже если мы предположим, что индекс покрытия является чистой выгодой, вопрос «вы видите разницу» (как и в случае, если производительность заметно возрастает) может быть эффективно решена только эмпирически. SET STATISTICS IO ON – ваш друг здесь (а также DBCC DROPCLEANBUFFERS , в тестовой среде). Вы можете попытаться угадать, основываясь на предположениях, но поскольку результат зависит от плана выполнения, размера ваших индексов, объема памяти SQL Server в целом, характеристик ввода-вывода, нагрузки на все базы данных и шаблонов запросов приложений, я бы не сделал этого за пределами приблизительного предположения о том, может ли этот индекс быть полезным. В общем, конечно, если у вас очень широкий стол и небольшой индекс покрытия, нетрудно понять, как это окупается. И в общем, вы скорее увидите плохую производительность из-за недостаточного количества индексов, чем из-за слишком большого количества индексов. Но реальные базы данных не работают на обобщениях.

Чтобы загрузить данные в память, я полагаю, SQL Server должен будет загрузить всю строку в память, а затем выбрать нужные столбцы. Разве это не увеличит потребление памяти?

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

Мое предположение заключается в том, что вы не увидите измеримой разницы в использовании ЦП, так как извлечение строки из столбцов не само по себе является работой ЦП. Верный?

Использование ЦП обычно не зависит от размера строки. Время выполнения (и это, в свою очередь, влияет на использование в зависимости от того, сколько запросов вы хотите запускать параллельно). После того, как вы покрыли узкое место ввода-вывода, предоставив вашему серверу много памяти, все еще остается вопрос сканирования данных в памяти.

Я понимаю, что вы не увидите большой разницы в кешировании, потому что SQL Server будет кэшировать только те данные, которые он возвращает, а не всю строку. Или я ошибаюсь?

Строки хранятся на страницах, а SQL Server кэширует страницы, которые он читает в пуле буферов. Он не кэширует результирующие наборы или любые промежуточные данные, созданные как часть выполнения запроса, или отдельные строки. Если вы дважды выполняете запрос в пуле пустых пулов, то второй, как правило, быстрее, потому что требуемые страницы уже находятся в памяти, но это единственный источник ускорения.

Имея это в виду, см. Ответ на свой первый вопрос – да, кеширование затронуто, потому что страницы вашего индекса покрытия, если они используются, кэшируются отдельно со страниц кластерного индекса, если они используются.

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

Нет, вам не нужен этот индекс покрытия.

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

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

Обновить:

Да, будет 10 должностей в каждой категории. Поэтому, если у вас есть типы категорий N, набор результатов возврата составляет не более 10 * N записей.

Другое руководство по индексу: создайте индекс, если вы часто хотите получить менее 15 процентов строк в большой таблице. (Мой инструктор по настройке SQL предлагает нам 5 процентов) Если более 15 процентов, окончательный план выполнения не будет оптимальным, если мы будем использовать Index.

Рассмотрим два крайних случая о вашей таблице POST:

Почтовая таблица имеет всего 10 * N записей, и каждый тип категории попадает в сообщение 10 раз. Таким образом, окончательный план выполнения будет полностью проверять таблицу POST вместо использования любого индекса.
Количество столбцов таблицы больше, чем (10 * N / 15%), поэтому оно будет извлекать менее 15% строк в таблице Post. Оптимизатор будет использовать поле Post ID для выполнения операции соединения. И это должно быть хеш-соединение.

Таким образом, даже вы создали индекс покрытия, Optimizer никогда не будет использовать его, если вы не используете подсказку.

Обновлено:

Описанные кластерные и некластеризованные индексы

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

Сделав шаг назад, учитывая, что ваш предикат соединения – это только [Сообщения]. [Id], добавляя столбцы [Key] и [SummaryText], поскольку ключевые столбцы в индексе не нужны. Вместо этого они должны быть добавлены как столбцы без ключа:

CREATE NONCLUSTERED INDEX [IX_Posts_Covering] ON [dbo].[Posts] ([Id]) INCLUDE ([Key], [SummaryText]) GO

Для Microsoft: MSDN – создание индексов с включенными столбцами

Редизайн некластеризованных индексов с большим размером ключа ключа, так что только столбцы, используемые для поиска и поиска, являются ключевыми столбцами. Сделайте все остальные столбцы, которые охватывают запрос, в столбцы без ключа. Таким образом, у вас будут все столбцы, необходимые для покрытия запроса, но сам индексный ключ является небольшим и эффективным.

Включите столбцы nonkey в некластеризованный индекс, чтобы избежать превышения ограничений по размеру текущего индекса максимум из 16 ключевых столбцов и максимального размера ключа ключа в 900 байт. Механизм Database Engine не рассматривает столбцы без ключа при расчете количества столбцов индексного ключа или размера ключа ключа.

По сути, индекс покрытия делает дубликат таблицы [dbo]. [Posts], исключая столбцы [CategoryId] и [Text]. Поскольку в индексе покрытия будет меньше столбцов, SQL должен иметь возможность заполнять больше строк на индексную страницу. Исходя из этого предположения (которое, по общему признанию, может потребовать тщательного изучения), поскольку SQL пересекает b-дерево, ищет страницы, чтобы найти соответствующие строки, он может выполнять номинально лучше по индексу покрытия, поскольку он имеет меньше страниц для загрузки и просмотра ,

Независимо от выбора индекса, вы также можете рассмотреть возможность размещения вашего соединения в таблице [Сообщений] в кресте. Это, скорее всего, приведет к поиску, хотя состав ваших данных будет определять эффективность.

CREATE VIEW [dbo].[TopPosts] AS SELECT c.[Id] AS [CategoryId], cp.[PostId], cp.[Key], cp.[SummaryText], cp.[Value] AS [Score] FROM [dbo].[Categories] c CROSS APPLY ( SELECT TOP 10 s.[PostId], s.[Value], p.[Key], p.[SummaryText] FROM [dbo].[Scores] s INNER JOIN [dbo].[Posts] p ON s.[PostId] = p.[Id] WHERE s.[CategoryId] = c.[Id] ORDER BY s.[Value] DESC ) AS cp

В конце концов, это будет зависеть от размера ваших данных, дискового ввода-вывода, оперативной памяти и т. Д. Вам нужно будет решить, будет ли дополнительное пространство, используемое индексом покрытия, оправдать номинальный прирост производительности, если таковой имеется.

Большая разбивка использования индекса: https://dba.stackexchange.com/a/42568/2916

sqlserver.bilee.com

Повесть о кластеризованном индексе / Хабр

После перехода на SQL Server с Oracle удивляет многое. Трудно привыкнуть к автоматическим транзакциям – после update не нужно набирать commit (что приятно), зато в случае ошибки не сможешь набрать rollback (что просто кошмарно). Трудно привыкнуть к архитектуре, в которой журнал используется и для отката, и для наката транзакций. Трудно привыкнуть к ситуации «писатель блокирует читателей, читатель блокирует писателей», а когда привыкнешь – ещё труднее отвыкнуть. И совсем не последнее место в рейтинге трудностей играет засилье кластеризованных индексов. По умолчанию первичный ключ таблицы – именно кластеризованный индекс, и поэтому почти у всех таблиц он есть.

На самом деле зверь этот совсем нестрашный и даже очень полезный. Давайте попробуем разобраться, зачем он нужен и как его использовать.

Файлы, страницы, RID

Данные любой таблицы физически сохранены в файле базы данных. Файл БД делится на страницы (page) – логические единицы хранения для сервера. Страница в MS SQL Server обязательно имеет размер 8 килобайт (8192 байта), из них под данные отдано 8060 байт. Для каждой строки можно указать её физический адрес, так называемый Row ID (RID): в каком файле она находится, в какой по порядку странице этого файла, в каком месте страницы. Страницу таблица присваивает целиком – на одной странице могут быть данные только одной таблицы. Более того, при необходимости считать/записать строку сервер считывает/записывает всю страницу, поскольку так получается гораздо быстрее.

Как устроен B-tree индекс?

B-tree означает balanced tree, «сбалансированное дерево». Индекс содержит ровно одну корневую страницу, которая является точкой входа для поиска. Корневая страница содержит значения ключей и ссылки на страницы следующего уровня для данных значений индекса. При поиске по индексу находится последнее значение, которое не превосходит искомое, и происходит переход на соответствующую страницу. На последнем, листьевом уровне дерева перечислены все ключи, и для каждого из них указана ссылка (bookmark) на данные таблицы. Естественным кандидатом на роль ссылки является RID, и он в самом деле используется в этом качестве для случая кучи. На следующем рисунке буквы B обозначают ссылки на строки таблицы.

При добавлении записи в таблицу её необходимо также добавить в индекс. Новая запись индекса, ссылающаяся на запись таблицы, вставляется в страницу листьевого уровня. При этом может оказаться, что на этой странице нет свободного места. Тогда:

Индексу выделяется новая страница – тоже на листьевом уровне.
Половина записей из прежней страницы переносится на новую (чтобы при последовательном добавлении не напороться на ситуацию, когда для следующей строки снова придётся выделять страницу). Новая страница встраивается в горизонтальные ссылки: вместо Прежняя Следующая настраиваются ссылки Прежняя Новая Следующая.
В родительскую страницу заносится ссылка на новую страницу, снабжённая соответствующим ключом. При этом может переполниться и родительская страница – тогда процесс разделения данных повторится на более высоком уровне. Если переполнение дойдёт до самого верха, то разделится надвое корневая страница, и появится новый корень, а высота дерева увеличится на 1.

Понятно, что добавление записей в таблицу при наличии индекса становится заметно более дорогостоящим процессом – каждое разбиение страницы требует обновления как минимум 4 страниц (разделяемую, следующую за разделяемой, новую, родительскую). При этом наличие индекса резко ускоряет поиск данных: вместо сплошного сканирования можно вести двоичный поиск, спускаясь по дереву. Также за счёт наличия горизонтальных ссылок на страницы одного уровня пройти диапазон ключей индекса можно очень быстро. И мы плавно подходим к основным задачам выборки: поиск одного значения и сканирование диапазонов.

Куча мала

Рассмотрим некоторую модельную таблицу, организованную в виде кучи: какого-то определённого порядка в записях нет. RID, который запись получает в самом начале, остаётся с ней почти всегда. В редких случаях записи в куче могут перемещаться на другую страницу – это происходит, когда после обновления строка перестаёт помещаться на то место, которое она занимала. Но в таком случае на прежнем месте остаётся ссылка на новое размещение – то есть, зная RID, полученный строкой при добавлении, строку можно найти всегда. Поэтому для индексов на куче наилучший выбор для ссылки на данные – именно RID.

Предположим, в таблице 200 тысяч записей, и в каждую страницу помещается от 48 до 52 записей. Будем считать, что таблица занимает 4000 страниц. Допустим, нам нужно найти все записи, в которых поле [City] имеет значение 'Perm'. Также допустим, что их всего 3, но мы об этом пока не знаем.

Серверу придётся просканировать все 4000 страниц. Даже если сервер найдёт все 3 записи, ему всё равно придётся идти до конца – ведь нет гарантии, что больше нужных записей нет. Итак, для выполнения запроса понадобится 4000 логических чтений страницы. А если у нас есть индекс, в котором можно искать двоичным поиском – скажем, дерево высоты 3? Тогда серверу потребуется 3 чтения индексных страниц для того, чтобы найти адрес первой записи. Адреса второй и третьей записей будут лежать рядом – либо в той же странице, либо в следующей: страницы индекса по горизонтали соединены ссылками. То есть после максимум 4 чтений сервер наверняка знает RID всех трёх записей. Если сильно не повезёт, все 3 записи лежат в разных страницах. Таким образом, при наличии индекса после 7 логических чтений страницы все 3 записи наверняка будут найдены. 7 против 4000 – впечатляет.

Но так хорошо будет, когда записей мало. А если это не 'Perm', а 'Moscow', и нужных записей не 3, а 20 тысяч? Это не очень много, всего 10 процентов от общего количества записей. Но картина быстро становится не столь радужной.

За 3 чтения сервер найдёт первую запись. А затем ему потребуется считать 20 тысяч RID и 20 тысяч раз прочитать страницу, чтобы получить строку: мы помним, что сервер читает данные только целыми страницами. Вполне может получиться так, что нужные записи рассеяны по всей таблице, и для обеспечения 20 тысяч логических чтений потребуется считать большую часть страниц с диска. Ещё хорошо, если не все. Вместо 4 тысяч логических чтений мы получаем 20 тысяч.

Индекс очень хорошо работает на поиске небольшого количества значений, но плохо работает на прохождении больших диапазонов.

Оптимизатор запросов прекрасно осведомлён об этом. Поэтому если он ожидает, что поиск по индексу даст достаточно большой диапазон, вместо поиска по индексу он выберет полное сканирование таблицы. Это, кстати, одно из редких мест, где Оптимизатор может ошибиться, даже имея правильные статистики. Если на самом деле требуемые данные расположены очень компактно (например, 20 тысяч логических чтений – это 60 раз прочесть блок с диска и 19940 раз прочесть блок в кэше), то принудительное использование индекса даст выигрыш в памяти и в скорости.

А как же быть с диапазонами?

Проблема именно в том, что в конце поиска по индексу сервер получает не данные, а только адрес, по которому они лежат. Серверу ещё нужно идти по этому адресу и брать данные оттуда. Вот было бы здорово, если бы в конце пути сразу лежали данные! Некоторые, собственно, и лежат. Значения ключей, например, лежат именно в индексе – за ними идти не нужно. Только за неключевыми полями. А что будет, если неключевые поля тоже положить в индекс? Ну допустим, не все, а только те, которые нужны сканирующему запросу?

А будет в таком случае индекс с добавочными (included) столбцами. Он проигрывает обычному индексу по размеру: его листьевые страницы содержат не только ключи и адреса строк, но и часть данных. В поиске одиночного значения такой индекс работает не хуже, а в сканировании диапазонов – намного, намного лучше. Если индекс покрывает запрос (то есть содержит все столбцы, перечисленные в запросе), то для выполнения запроса таблица не нужна вообще. Возможность взять все требуемые данные из индекса, не обращаясь к закладкам, даёт громадный выигрыш.

Вернёмся к нашему модельному примеру. Предположим, что требуемые для запроса столбцы включены в индекс. Для простоты предположим, что в листьевую страницу индекса попадают ровно 50 записей (ключи, добавленные столбцы, ссылки на записи). Тогда сканирование 20 тысяч записей потребует чтения всего лишь 400 страниц индекса – вместо 20 тысяч логических чтений для непокрывающего индекса.

400 против 20 тысяч – разница в 50 раз. Оптимизатор запросов недаром любит предлагать включить в индекс те или иные столбцы. А может, стоит добавить в индекс все столбцы? Тогда любой запрос будет покрыт индексом обязательно. Более того, тогда в листьях даже не нужны RID, потому что ни за какими данными такой индекс не будет обращаться в таблицу, у него всё под рукой. Да в таком случае становится не нужна и сама таблица!

И мы пришли к концепции кластеризованного индекса. Он устроен как обычное B-дерево, но в его листьевых страницах вместо ссылок на записи таблицы расположены сами данные, а отдельной от него таблицы больше нет. Таблица не может иметь кластеризованный индекс, она может быть кластеризованным индексом. Любое сканирование по ключу в кластеризованном индексе будет лучше, чем полный просмотр таблицы. Даже если просканировать нужно 97% всех записей.

Где подвох?

Первый – понятно где. Кластеризованный индекс – это таблица, а таблица может быть только одна. Сервер должен иметь мастер-копию данных, и только из одного индекса он готов выбросить все закладки и оставить только сами данные. Если есть ещё один индекс, в который включены все поля – в нём всё равно будут и адреса строк.

Есть и второй подвох. При наличии кластеризованного индекса в качестве адреса строки уже нельзя использовать RID. Записи в кластеризованном индексе отсортированы (физически – в пределах страницы, логически – горизонтальными ссылками между страницами). При добавлении записи или изменении ключевых полей запись перемещается в правильное место – часто в пределах страницы, но возможно и перемещение на другую страницу. Иными словами, RID в кластеризованном индексе перестаёт идентифицировать запись однозначно. Поэтому в качестве адреса строки, однозначно её идентифицирующего, используется ключ кластеризованного индекса.

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

Представим себе сканирование диапазона в 20 тысяч записей по некластеризованному индексу, построенному на кластеризованном. Теперь понадобится выполнить не 20 тысяч логических чтений страницы по известному RID, а 20 тысяч поисков в кластеризованном индексе – и каждый поиск потребует 3, а то и более, логических чтений.

А если ключ кластеризованного индекса не уникален? А так не бывает. Для сервера он обязательно уникален. Если пользователь попросил создать неуникальный кластеризованный индекс, сервер к каждому ключу припишет 4-байтовое целое число, которое обеспечит уникальность ключа. Делается это прозрачно для пользователей: сервер не только не сообщает им точного значения числа, но и не выдаёт сам факт его наличия. Уникальность ключа нужна именно для возможности однозначной идентификации записей – чтобы ключ кластеризованного индекса мог служить адресом строки.

Так делать или не делать?

Вооружённые теорией, мы можем описать рациональную процедуру построения кластеризованного индекса. Следует выписать все индексы, которые нужны таблице, и выбрать из них кандидата на кластеризацию. Не нужно делать кластеризованный индекс только для того, чтобы он был. Если по ключу индекса не предполагается сканирование – это не очень хороший кандидат для кластеризации (если по другим индексам сканирование предполагается – то даже очень плохой кандидат). Неправильный выбор кандидата на кластеризацию ухудшит производительность, потому что все остальные индексы станут работать хуже, чем работали на куче.

Предлагается следующий алгоритм выбора:

Определить все индексы, по которым происходит поиск одиночного значения. Если такой индекс единственный – его и нужно кластеризовать. Если несколько – перейти к следующему шагу.
Добавить к индексам с предыдущего шага все индексы, по которым предполагается сканирование диапазонов. Если таковых нет – кластеризованный индекс не нужен, несколько индексов на куче будут работать лучше. Если есть – каждый из них следует сделать покрывающим, добавив все столбцы, которые нужны сканирующим запросам по этому индексу. Если такой индекс единственный – его следует кластеризовать. Если их больше одного – перейти к следующему шагу.
Однозначно лучшего выбора кандидата на кластеризацию среди всех покрывающих индексов нет. Следует кластеризовать какой-то из этих индексов, принимая во внимание следующее:
- Длина ключа. Ключ кластеризованного индекса является ссылкой на строку и хранится на листьевом уровне некластеризованного индекса. Меньшая длина ключа означает меньше места на хранение и более высокую производительность.
- Степень покрытия. Кластеризованный индекс содержит все поля «бесплатно», и покрывающий индекс с самым большим набором полей – хороший кандидат на кластеризацию.
- Частота использования. Поиск одиночного значения в покрывающем индексе – самый быстрый возможный поиск, а кластеризованный индекс – покрывающий для любого запроса.

Постскриптум. Почему он единственный?

Когда я начинал писать эту статью – я прекрасно понимал, почему у таблицы не может быть больше одного кластеризованного индекса. В середине написания я понимать это перестал и теперь уже не понимаю (хотя, что смешно, по-прежнему могу это объяснить). Сейчас у меня есть только гипотезы.

Кластеризованный индекс, во-первых, содержит все данные в листьевых вершинах, а во-вторых, не содержит никаких ссылок на данные таблицы (потому что никакой внешней по отношению к нему таблицы нет). Ну и что мешает завести несколько так устроенных индексов – содержащих все поля и не содержащих ссылки? Я не знаю. Могу только предложить.

Прежде всего – мы же можем завести сколько угодно индексов, в которые будут включены все поля. Значит, весь выигрыш, который нам сулит наличие нескольких кластеризованных индексов, относительно невелик: на листьевом уровне добавочных индексов не будет ссылок на данные, то есть мы сэкономим немного места. А какие проблемы повлечёт за собой создание нескольких кластеризованных индексов?

Ключи кластеризованного индекса представляют собой ссылки на данные, которые хранятся в листьях некластеризованных индексов. Если бы кластеризованных индексов могло бы быть несколько, среди них всё равно пришлось бы выделять «основной», тот, ключи которого являются идентификаторами данных.
Если в кластеризованный индекс нужно добавить неключевой столбец, то индекс придётся полностью перестроить, а некластеризованные индексы на нём не нужно менять вообще. Если бы кластеризованных индексов было несколько, перестраивать пришлось бы все, и невозможно было бы заранее определить, сколько времени это бы заняло.
Возникло бы множество ситуаций, чреватых ошибками. Например, если при наличии нескольких кластеризованных индексов пользователь удаляет мастер-индекс (тот, ключи которого служат ссылками на данные в некластеризованных индексах), то серверу придётся автоматически выбрать новый мастер-индекс.

Сейчас я склоняюсь к мысли, что запрет множественных кластеризованных индексов связан с тем, что реализация этой концепции затратна и чревата ошибками (то есть понижением надёжности), а выгод принесла бы относительно мало. Иными словами, сделать несколько кластеризованных индексов технически можно, но дорого, неудобно и ни к чему. Возможно, что я не вижу какие-нибудь соображения, вследствие которого делать несколько кластеризованных индексов нельзя. Буду очень признателен, если кто-то укажет мне эти соображения.

Удачи всем в кластеризации ваших индексов!

habr.com

Важен ли порядок при создании покрывающего индекса в Microsoft SQL?

Для начала предлагаю разобраться что такое покрывающий индекс, приведу выдержку из статьи на Хабре:

Зачем использовать покрывающий индекс взамен составного индекса? Во-первых, давайте убедимся, что мы понимаем различие между ними. Составной индекс это просто обычный индекс, в который включено больше одного столбца. Несколько ключевых столбцов может использоваться для обеспечения уникальности каждой строки таблицы, также возможен вариант, когда первичный ключ состоит из нескольких столбцов, обеспечивающих его уникальность, или вы пытаетесь оптимизировать выполнение часто вызываемых запросов к нескольким столбцам. В общем, однако, чем больше ключевых столбцов содержит индекс, тем менее эффективна работа этого индекса, а значит составные индексы стоит использовать разумно.

Как было сказано, запрос может извлечь огромную выгоду, если все необходимые данные сразу расположены на листьях индекса, как и сам индекс. Это не проблема для кластеризованного индекса, т.к. все данные уже там (вот почему так важно хорошенько подумать когда вы создаете кластеризованный индекс). Но некластеризованный индекс на листьях содержит только ключевые столбцы. Для доступа ко всем остальным данным оптимизатору запросов необходимы дополнительные шаги, что может вызвать значительные дополнительные накладные расходы для выполнения ваших запросов.

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

Таким образом покрывающий индекс не должен содержать все выбираемые столбцы запроса в структуре дерева индекса, а только те, которые будут использованы для фильтрации или группировки данных в запросе, остальные столбцы из секции SELECT должны быть помещены в INCLUDE раздел индекса.

Возможно вам будет полезен ответ из другого вопроса на StackOverflow

В приведенном примере использован составной индекс из 3 полей, а не покрывающий индекс, код для создания покрывающего индекса будет выглядеть следующим образом:

CREATE NONCLUSTERED INDEX [ix_Customer_Email] ON [dbo].[Customers] ( [Last_Name] ASC ) INCLUDE ([First_Name], [Email_Address]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Отвечая на ваш вопрос:

для покрывающего индекса порядок столбцов в секции INCLUDE не важен, но важен порядок столбцов для составного индекса, т.к. данные колонок помещаются в дерево индекса по порядку перечисления столбцов и оптимизатор запроса не сможет использовать индекс из 2 колонок для поиска значений только 2 колонки. Наглядный пример того, как будет выглядеть структура индекса из 2 колонок (EMPLOYEE_ID, SUBSIDIARY_ID) вы можете увидеть на рисунке:

Рисунок заимствован из англоязычной статьи про составные индексы за авторством Markus Winand

qa-help.ru

Будет ли включен индекс покрытия, если поля, индексированные отдельно MS SQL Server

В базе данных SQL Server 2005 у меня есть много таблиц, подобных этой таблице продуктов

… где есть первичный ключ, внешний ключ, а затем куча других полей. Другим признаком такого типа таблицы является то, что в большинстве запросов используются только 2 поля ID (ProductID, ProductCategoryID), например, Employees JOIN EmployeeProductJoin JOIN Products JOIN ProductCategories JOIN ProductDepartments .

Если ProductID и ProductCategoryID уже проиндексированы, стоит ли добавить еще один индекс для ProductID, ProductCategoryID?

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

Это таблицы определения, которые не огромны, поэтому я не беспокоюсь о добавлении дополнительного времени к INSERT и т. Д.

Является ли первичный ключ сгруппированным? Если это так, то добавление нового индекса ничего не добьется, потому что индекс ProductCategoryID уже будет содержать значения ProductID, поэтому он эффективно «охватывает» оба столбца.

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

Возможно, вы не имеете в виду «индекс покрытия», хотя …

Только планы запросов (с дополнительными индексами и без них, а также таблицы, содержащие реалистичные суммы и виды данных) могут точно сказать, помогут ли дополнительные индексы; все дело в том, чтобы помочь оптимизатору запросов найти более разумный план, но вы можете только помочь до сих пор, и вполне возможно, что он может не найти нужный вам план (это всего лишь эвристический «позвольте мне попытаться оптимизировать» движок, в конце концов). Вот почему просмотр планов запросов настолько важен (и вам нужны реалистичные данные, потому что это обычно влияет на эвристику!).

Короче Да, это улучшит производительность запросов.

Используя индекс покрытия, все столбцы, необходимые в вашем запросе, присутствуют в структуре данных индекса. Это означает, что SQL-серверу нужно запросить только один индекс, чтобы предоставить результаты для вашего запроса.

Если у вас есть сценарий из нескольких столбцов, индексированных отдельно, для обслуживания этого запроса SQL Server, скорее всего, придется выполнять поиск / сканирование многочисленных индексов, а не только одного. Это, конечно, потенциально создает больше активности ввода-вывода.

Имеют смысл?

Я определенно могу помочь, особенно если ваши описания велики. Было бы легко ориентироваться и убедиться в этом сами. Этот новый индекс может быть намного меньше, чем кластерный. Но вам нужен только этот узкий индекс, если у вас есть важные вопросы, которые вам нужно ускорить, несмотря ни на что.

Да, это может помочь определенным образом. Идея индекса покрытия заключается в том, что он имеет некоторые избыточные поля, которые вы используете в запросах. Если индекс может удовлетворять требованиям к данным запроса без запроса, попадающего в базовую таблицу, вы можете сэкономить на вводе-выводе, получив данные из индекса.

Если у вас есть два индекса, как показано выше, СУБД придется ударить по таблице, а также разрешить два обращения к индексу.

Если результаты вашего запроса широко разбросаны по таблице, но вместе взяты по индексу, вы могли бы сэкономить немало ввода-вывода на большом запросе. Таким образом, индексы покрытия также могут использоваться как своего рода «второй кластеризованный индекс» на таблице.

sqlserver.bilee.com

Кластерный против индекса покрытия MS SQL Server

Рассмотрим следующую таблицу в SQL Server 2008:

LanguageCode varchar(10) Language nvarchar(50)

LanguageCode участвует в отношениях, поэтому я не могу создать индекс первичного ключа, который включает оба столбца (LanguageCode, Language).

Если я помещаю основной кластерный ключ в LanguageCode, я не могу включить Language в индекс (индекс покрытия). Это означает, что мне нужно будет создать второй индекс для языка или риск наличия дубликатов в нем (плюс принудительное сканирование таблицы для получения его значения).

Кроме того, документация MS (а также эксперты по этому вопросу) указывают на то, что таблица в идеале должна иметь кластеризованный индекс.

В этом случае некластеризованный индекс покрытия (LanguageCode, Language) не только гарантирует уникальность языка, но и позволит избежать сканирования таблицы. Однако не было бы «идеального» кластеризованного индекса.

Является ли это одним из тех случаев, когда отсутствие кластеризованного индекса фактически является идеальным?

Изменить на основе обратной связи:

Единственный запрос, который я хочу запустить:

SELECT Language, LanguageCode FROM Languages where Language="EN"

Solutions Collecting From Web of "Кластерный против индекса покрытия"

Кластерный индекс, по определению, охватывает все столбцы.

Если вы создадите PRIMARY KEY CLUSTERED на LanguageCode и UNIQUE INDEX на Language , это позволит вам искать язык как по его коду, так и по имени с помощью одного поиска и, кроме того, сделать Language уникальным.

Нет необходимости включать столбцы в кластерный индекс. Поскольку кластеризованный индекс – это «данные», все столбцы автоматически включаются.
Если вам нужно искать по языку и / или обеспечить его уникальность, то обязательно создайте дополнительный индекс на нем.

Основываясь на характере субъекта (который я предполагаю, это языки, на которых говорят люди), индексирование на производительность будет неактуальным. Если у вас было 100 языков, и каждая строка занимала 120 байт (psuedo-факторинг в заголовках varchar, нулевых битмашках и еще что-то), у вас было бы 12 000 байт данных, которые бы соответствовали двум 8k страницам. SQL не будет пытаться использовать индексы на чем-то маленьком, он просто сканирует всю вещь (2 страницы) и грубую силу, требуя меньше времени, чем можно легко измерить.

Индексирование для обеспечения уникальности, конечно, я делаю это все время. Но для производительности, пока вы не нажмете 3 страницы (или это 4), это просто не имеет значения. (Что произойдет, если вы отслеживаете языки программирования, потому что в неделю есть около десятка новых.)

sqlserver.bilee.com

Покрывающие индексы

Индексы являются средством эффективного поиска строк, но MySQL может также использовать индекс для извлечения данных, не считывая строку таблицы. В конце концов, листовые узлы индекса содержат те значения, которые они индексируют. Зачем просматривать саму строку, если чтение индекса уже может дать нужные данные? Индекс, который содержит (или «покрывает») все данные, необходимые для формирования результатов запроса, называется покрывающим индексом.

Покрывающие индексы могут служить очень мощным инструментом и значительно увеличивать производительность. Рассмотрим преимущества считывания индекса вместо самих данных.

• Записи индекса обычно компактнее полной строки, поэтому, если MySQL читает только индекс, то обращается к значительно меньшему объему данных. Это очень важно в случае кэшированной рабочей нагрузки, когда время отклика определяется в основном копированием данных. Это также полезно в случае большого количества операций ввода/вывода, поскольку индексы меньше, чем данные, и лучше помещаются в памяти (что особенно справедливо в отношении подсистемы хранения MyISAM, которая может упаковывать индексы, дополнительно уменьшая их размер).

• Индексы отсортированы по индексируемым значениям (по крайней мере, внутри страницы), поэтому для поиска по диапазону, характеризуемому большим объемом ввода/вывода, потребуется меньше операций обращения к диску по сравнению с извлечением каждой строки из произвольного места хранения. Для некоторых подсистем, например MyISAM, вы можете даже оптимизировать (OPTIMIZE) таблицу и получить полностью отсортированные индексы, вследствие чего для простых запросов по диапазону доступ к индексу будет вообще последовательным.

• Большинство подсистем хранения кэширует индексы лучше, чем сами данные (заметным исключением является Falcon). Некоторые подсистемы хранения, например MyISAM, кэшируют в памяти MySQL только индексы. Поскольку кэширование данных для My-ISAM выполняет операционная система, доступ к ним обычно требует системного вызова. Это может оказать огромное влияние на производительность, особенно в случае кэшированной рабочей нагрузки, когда системный вызов является самой дорогостоящей частью доступа к данным.

• Покрывающие индексы особенно полезны в случае таблиц InnoDB из-за кластерных индексов. Вторичные индексы InnoDB хранят значения первичного ключа строки в листовых узлах. Таким образом, вторичный индекс, который «покрывает» запрос, позволяет избежать еще одного поиска по первичному индексу.

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

Не каждый тип индекса может выступать в роли покрывающего. Индекс должен хранить значения индексируемых столбцов. Хеш-индексы, пространственные индексы и полнотекстовые индексы такие значения не хранят, поэтому MySQL может использовать в качестве покрывающих только B-Тгее-индексы. Кроме того, различные подсистемы хранения реализуют покрывающие индексы по-разному, а некоторые не поддерживают их вовсе (на момент написания книги подсистемы Memory и Falcon не поддерживали покрывающие индексы).

Запустив команду EXPLAIN для запроса, «покрываемого» индексом, вы увидите в столбце Extra сообщение «Using index»1. Например, таблица sakila.inventory имеет многостолбцовый индекс по (store_id, film_id). MySQL может использовать этот индекс для ответа на запросы, в которых упоминаются только эти два столбца, например:

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

«Покрываемые» индексом запросы имеют тонкости, которые могут отключить эту оптимизацию. Оптимизатор MySQL перед выполнением запроса принимает решение, покрывает ли его какой-либо индекс. Предположим, индекс покрывает условие WHERE, но не весь запрос. Даже если условие во фразе WHERE не выполняется, MySQL 5.1 и более ранние версии в любом случае извлекут строку, даже несмотря на то, что она не нужна и будет впоследствии отфильтрована.

Давайте посмотрим, почему это может произойти и как переписать запрос, чтобы обойти данную проблему. Начнем со следующего запроса:

mysql> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY’

-> AND title like '%APOLLO%’\G

id: 1

select_type: SIMPLE table: products type: ref possible_keys: ACTOR,IX_PROD_ACTOR key: ACTOR key_len: 52 ref: const rows: 10 Extra: Using where

Индекс не может покрыть этот запрос по двум причинам:

• Ни один индекс не покрывает запрос, поскольку мы выбрали все столбцы из таблицы, а ни один индекс не покрывает все столбцы. Однако есть обходной маневр, который MySQL теоретически может использовать: во фразе WHERE упоминаются только столбцы, которые покрываются индексом, поэтому MySQL может использовать индекс для поиска актера, проверить, соответствует ли заданному критерию название, и только потом считывать всю строку.

• MySQL не может выполнять операцию LIKE в индексе. Это ограничение API подсистемы хранения, которое допускает в операциях с индексами только простые сравнения. MySQL может выполнять сравнения LIKE по префиксу, поскольку допускает преобразовывание их в простые сравнения, однако наличие метасимвола в начале шаблона не позволяет подсистеме хранения провести сопоставление. Таким образом, самому серверу MySQL придется выполнять извлечение и сравнение значений из строки, а не из индекса.

Существует способ обойти обе проблемы путем комбинирования разумного индексирования и переписывания запроса. Мы можем расши рить индекс, чтобы он покрывал столбцы (artist, title, prod_id), и переписать запрос следующим образом:

mysql> EXPLAIN SELECT *

-> FROM products -> JOIN (

SELECT prod_id -> FROM products

-> WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%’

-> ) AS t1 ON (t1.prod_id=products.prod_id)\G

id: 1

select_type: PRIMARY

table: <derived2>

...пропущено...

************************** 2. row ************************** id: 1

select_type: PRIMARY table: products

...пропущено...

************************** 3. row ************************** id: 2

select_type: DERIVED table: products type: ref

possible_keys: ACTOR,ACTOR_2,IX_PROD_ACTOR key: ACTOR_2 key_len: 52 ref: rows: 11

Extra: Using where; Using index

Теперь MySQL использует покрывающий индекс на первом этапе запроса, когда ищет строки в подзапросе во фразе FROM. Он не использует индекс для покрытия всего запроса, но это лучше, чем ничего.

Эффективность такой оптимизации зависит от того, сколько строк отвечает условию WHERE. Предположим, таблица products содержит миллион строк. Давайте посмотрим, как эти два запроса выполняются с тремя различными наборами данных, каждый из которых содержит миллион строк:

1. В первом примере в 30 000 записей в столбце actor указан Sean Carrey, а 20 000 из них содержат Apollo в столбце title.

2. Во втором примере в 30 000 записей в столбце actor указан Sean Carrey, а 40 из них содержат Apollo в столбце title.

3. В третьем примере в 50 записях в столбце actor указан Sean Carrey, а 10 из них содержат Apollo в столбце title.

Мы использовали эти три набора данных для эталонного тестирования обоих вариантов запроса и получили результаты, показанные в табл. 3.3.

Таблица 3.3. Результаты тестирования для запросов, покрываемых и не покрываемых индексами

Набор данных	Исходный запрос, запросов в секунду	Оптимизированный запрос, запросов в секунду
Пример 1	5	5
Пример 2	7	35
Пример 3	2400	2000

Интерпретировать эти результаты нужно следующим образом:

• В примере 1 запрос возвращает большой результирующий набор, поэтому мы не видим эффекта от оптимизации. Большая часть времени тратится на считывание и отправку данных.

• В примере 2, где второе условие фильтрации оставляет только небольшой результирующий набор, видно, насколько эффективна предложенная оптимизация: производительность возрастает в пять раз. Эффективность достигается за счет того, что считывается всего 40 полных строк вместо 30 000 в первом примере.

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

Эта оптимизация иногда становится эффективным способом избежать считывания ненужных строк в MySQL 5.1 и более ранних версиях. СУБД MySQL 6.0 сама умеет избегать этой дополнительной работы, поэтому при обновлении до указанной версии вы сможете упростить свои запросы.

В большинстве подсистем хранения индекс может «покрывать» только запросы, которые обращаются к столбцам, являющимся частью индекса. Однако InnoDB позволяет немного развить эту оптимизацию. Вспомните, что вторичные индексы InnoDB хранят в листовых узлах значения первичного ключа. Это означает, что вторичные индексы имеют «дополнительные столбцы», которые можно использовать для «покрытия» запросов.

Например, по столбцу last_name таблицы sakila.actor типа InnoDB построен индекс, который может покрывать запросы, извлекающие столбец первичного ключа actor_id, хотя этот столбец технически не является частью индекса:

mysql> EXPLAIN SELECT actor_id, last_name

-> FROM sakila.actor WHERE last_name = 'HOPPER'\G

id: 1

select_type: SIMPLE table: actor type: ref

possible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 2

Extra: Using where; Using index

⇐Кластерные индексы | MySQL. Оптимизация производительности | Использование просмотра индекса для сортировки⇒