Когда запрос выполняется медленно, это часто вызвано неэффективным доступом к данным. Поэтому наша работа по настройке очень часто сводится к выяснению того, как были прочитаны данные, и затем к корректировке наших запросов или структур базы данных, чтобы заставить SQL Server обращаться к данным более эффективным способом.
Итак, мы рассматриваем просмотры (scans), поиски (seeks) и обратные поиски (lookups). Мы знаем, что просмотры хороши, когда мы обращаемся к большей части данных. Или, в случае упорядоченного просмотра, чтобы избежать необходимости сортировки данных. Мы знаем, что поиск предпочтительнее, когда в запросе есть фильтр. И мы знаем, что обратный поиск представляет собой хороший компромисс между лучшей производительностью и слишком большим количеством индексов, но только если фильтр высокоизбирательный.
Всё вышесказанное верно. И всё это очень обобщённо. И, следовательно, часто недостаточно верно, чтобы быть действительно полезным.
SQL Server за годы своего развития научился поддерживать ошеломляющее количество различных структур хранения. Мы все знаем о кучах (heaps), а также о кластерных и некластерных B-деревьях (индексах). Но есть гораздо больше. Columnstore-индексы. Индексы, оптимизированные для памяти. Специализированные структуры индексов для поддержки определённых типов данных, таких как XML, JSON или векторы. И многое другое.
На высоком уровне общие описания просмотров, поисков и обратных поисков всегда применимы. Но вы по-настоящему поймёте влияние каждого из этих операторов на конкретный объект хранения, только если будете знать тип структуры хранения... и знать детали реализации этой структуры хранения.
Это первая из серии статей в блоге, в которых я планирую описать все структуры хранения, которые в настоящее время поддерживает SQL Server, и объяснить, как это влияет на просмотры, поиски и обратные поиски.
Для некоторых из вас это, возможно, уже знакомо. Но хорошее повторение никогда не помешает! Для других всё это может быть новым. Надеюсь, это поможет вам лучше понять, что происходит в планах выполнения ваших плохо ведущих себя запросов!
Классическое строковое хранение на диске
До выхода SQL Server 2012 существовал только один способ хранения таблиц. Тогда у него не было названия. Этот метод, который до сих пор является стандартным, теперь называется классическим хранением строк на диске (on-disk rowstore data), чтобы отличать его от columnstore-индексов и таблиц, оптимизированных для памяти.
Классическое rowstore использует три различных типа структур. Два — для хранения фактических данных таблицы: кучи (heaps) и кластерные индексы. И один, некластерные индексы, для хранения всех вспомогательных индексов. Все три хранят данные на страницах размером 8 КБ. Количество строк на странице зависит от того, сколько байт занимает каждая строка.
В SQL Server 2008 уже было несколько дополнительных типов структур хранения для специализированных целей: индекс XML, пространственный индекс и полнотекстовый индекс. Они будут описаны в следующих статьях блога.
Куча
Для каждой таблицы на диске её данные хранятся либо в кластерном индексе, либо в куче (heap). Кластерный индекс может быть создан явно, или SQL Server может автоматически создать его для вас, когда вы определяете ограничение PRIMARY KEY или UNIQUE. Если ни один из этих методов не используется, другими словами, если у таблицы нет кластерного индекса, то такая таблица автоматически является кучей, также называемой таблицей-кучей.
Хорошая визуализация кучи — взять книгу. Возьмите очень острый нож и отрежьте страницы от обложки. Аккуратно держите стопку отдельных страниц в руках. Встаньте посередине комнаты. Затем подбросьте стопку бумаги как можно выше в воздух и подождите, пока все страницы упадут на пол. Теперь вы смотрите на кучу страниц из книги.
Вообще говоря, эта куча страниц имеет много общего с кучами в SQL Server. Для быстрого поиска данных эта структура абсолютно бесполезна. Хотя есть некоторые случаи, когда куча является хорошим выбором, они очень редки. Когда я проверяю проект базы данных, я принимаю кучу только в том случае, если разработчик, который предлагает этот проект, может дать мне очень вескую причину, почему это правильный выбор, и почему это действительно лучше в данном случае, чем кластерный индекс.
Когда данные изменяются, мы можем столкнуться с ситуацией, когда новые данные больше не помещаются в доступном пространстве на своей странице. В этом случае данные для этой строки перемещаются на другую страницу, где ещё есть достаточно свободного места. Однако могут существовать другие структуры, которые хранят местоположение строки. Microsoft не хотела идти на удар по производительности для поиска строки во всех этих структурах. Поэтому вместо этого она заменила данные строки в исходном местоположении перенаправляющим указателем (forwarding pointer) — указателем на её новое местоположение. Обратите внимание, что если строке позже снова придётся переехать, она не сформирует цепочку перенаправляющих указателей. В таком случае перенаправляющий указатель в исходном местоположении будет изменён так, чтобы указывать на новое местоположение. Однако, как только перенаправляющий указатель появится, он никогда не исчезнет, даже если данные позже будут изменены и снова поместятся в своё исходное местоположение. Единственный способ удалить перенаправляющие указатели — перестроить таблицу.
Поскольку куча не имеет никакой структуры, невозможно выполнить поиск данных (seek). Это оставляет два варианта. Чтение через все страницы с помощью Table Scan (просмотр таблицы). Или поиск конкретной строки на основе её местоположения (найденного в некластерном индексе) с помощью RID Lookup (обратный поиск по идентификатору строки).
Просмотр таблицы (Table Scan) обращается к данным в куче через карту распределения индексов (Index Allocation Map). Это позволит читать страницы в порядке распределения, что близко к физическому порядку на диске. В эпоху вращающихся дисков это было небольшим облегчением боли от использования куч. С SSD даже это маленькое утешение исчезло.
На практике, однако, даже это небольшое преимущество сканирования кучи вряд ли когда-либо достигалось, если только таблица не была полностью предназначена только для чтения. Причина в том, что для защиты от некорректных результатов, вызванных параллельными обновлениями, SQL Server должен следовать по каждому перенаправляющему указателю, как только встречает его при сканировании. Это затем является чтением не по порядку, которое разрушает быстрый последовательный доступ в никогда не обновляемой куче.
И, пожалуйста, не думайте, что таблица с «всего несколькими изменениями» почти не затрагивается. Эффект даже относительно низкой частоты изменений может быть огромным! Я уже писал об этом в 2006 году. В таблице из 20 000 строк я обновил 5 000 случайно выбранных строк. Таким образом, 75% данных остались неизменными. Тем не менее, даже это небольшое количество обновлений уже привело к тому, что каждая страница в таблице считывалась в среднем 10 раз! Это делает сканирование крайне неэффективным.
Обратный поиск по RID (RID Lookup) находит данные на основе «идентификатора строки» (называемого Bmknnnn — мнемоника относится к термину «закладка», используемому до 2005 года). Это скрытый столбец, который хранит номер файла (в случае базы данных с несколькими файлами), номер страницы и номер слота (позицию на странице) строки. В идеальном случае это означает, что для получения запрошенных данных необходимо получить всего одну страницу. Однако, если строка когда-либо перемещалась из-за обновления, то эта выборка вернёт только перенаправляющий указатель, и потребуется ещё одна выборка, чтобы найти местоположение фактических данных.
Изменения в куче, как правило, довольно дёшевы. Единственный несколько затратный сценарий — когда обновлённую строку необходимо переместить, потому что в этом случае движку также необходимо создать или изменить перенаправляющий указатель в её исходном местоположении.
Кучи могут быть хорошим выбором, например, для промежуточных таблиц (staging tables) в хранилище данных или для таблиц журналирования, которые обычно предназначены только для записи и будут читаться только в редких ситуациях.
Кластерный индекс
SQL Server позволяет создавать максимум один кластерный индекс для каждой таблицы. Причина этого ограничения в том, что кластерный индекс — это не дополнительная структура. Он определяет, как будут храниться данные таблицы. А мы можем хранить их только одним способом.
Хорошая аналогия для кластерного индекса — это та же самая книга, о которой я говорил ранее, но теперь до того, как вы взяли свой острый нож и испортили её. То есть со страницами всё ещё по порядку, переплетёнными в обложке. Существует логический порядок страниц, определённый номерами страниц. И этот порядок затем становится физическим благодаря переплёту страниц в этом порядке. Это облегчает поиск определённой страницы, если вы знаете номер нужной страницы.
В SQL Server страницы фактически не хранятся в физическом порядке. Так что здесь аналогия с книгой даёт сбой. Каждая страница содержит указатели на предыдущую и следующую страницы, и по индексу можно перемещаться в логическом порядке, следуя этим указателям. Фактическое хранение может быть где угодно на диске!
Чтобы ускорить поиск конкретных записей индекса, SQL Server строит структуру индекса, называемую B-деревом, поверх этой цепочки связанных страниц. Это B-дерево всегда имеет одну корневую страницу (root page), которая разбивает полный диапазон значений в ключевых столбцах на несколько меньших диапазонов. Например, для символьного столбца диапазоны могут быть A-H, I-P и Q-Z. Каждый из этих диапазонов имеет указатель на данные для этого диапазона.
Ниже корневой страницы находятся ноль, один или более промежуточных уровней. Эти страницы индекса имеют ту же структуру и разбивают свой диапазон на ещё более мелкие диапазоны. Например, промежуточная страница для диапазона A-H может иметь записи для A-Ano, Anp-Ber, Bes-Chr и т.д.
На самом нижнем уровне находятся листовые страницы (leaf pages). Это уже упомянутые выше страницы. Они хранят отдельные строки со всеми их данными. И они связаны в порядке индекса указателями на предыдущую и следующую страницы.
Кластерный индекс должен быть уникальным. Это не значит, что вам нужно объявлять его таковым. Если вы создаёте кластерный индекс, который не объявлен как уникальный, SQL Server сделает дублирующиеся записи уникальными, добавив внутренний 4-байтовый столбец, так называемый уникафикатор (uniqueifier).
Когда мы хотим все или большую часть данных, мы можем сканировать индекс с помощью Clustered Index Scan (сканирование кластерного индекса). Но мы также можем искать конкретные строки, используя Clustered Index Seek (поиск по кластерному индексу) или Key Lookup (обратный поиск по ключу).
Сканирование кластерного индекса (Clustered Index Scan) может читать данные тремя способами: упорядоченно в прямом направлении, упорядоченно в обратном направлении или неупорядоченно. Первый начинается с навигации через корневую страницу и промежуточные страницы к (логически) первой странице, а затем продолжает следовать указателю на следующую страницу. Это гарантирует возврат строк в порядке индекса. Упорядоченное сканирование в обратном направлении аналогично, но начинается с последней страницы и следует указателям на предыдущую страницу, чтобы вернуть данные в порядке, обратном порядку, определённому индексом. Очевидное преимущество упорядоченного сканирования, конечно, в том, что данные упорядочены. Это может устранить необходимость в дорогостоящем операторе сортировки (Sort) в плане выполнения, если такой порядок данных является требованием.
Наконец, неупорядоченное сканирование похоже на просмотр таблицы (Table Scan): оно использует карту распределения индексов (Index Allocation Map), чтобы найти все страницы, распределённые для кластерного индекса. Поскольку перенаправляющих указателей нет, каждая страница читается ровно один раз. Включая корневую и промежуточные страницы, которые на самом деле не нужны. Но в типичном B-дереве они составляют лишь крошечную долю страниц. Хотя неупорядоченное сканирование для больших таблиц немного дешевле, чем упорядоченное (если порядок данных не важен), у него есть большая проблема: оно не гарантирует корректных результатов, если есть параллельные изменения. Вот почему SQL Server фактически будет использовать неупорядоченное сканирование только если запрос блокирует всю таблицу, если таблица предназначена только для чтения, или если вы явно разрешаете некорректные результаты, используя NOLOCK или уровень изоляции транзакции READUNCOMMITTED.
Поиск по кластерному индексу (Clustered Index Seek) ищет одну или несколько строк на основе значений ключевого столбца(ов). Он начинается с корневой страницы и находит правильный диапазон. Это повторяется для каждой промежуточной страницы, пока не будет найдена листовая страница и просканирована для поиска (первой) запрошенной строки. Поиск по равенству по всем столбцам (включая уникафикатор, если применимо) может вернуть не более одной строки; это называется поиском одного элемента (singleton lookup). Любой поиск по неравенству, а также любой поиск по подмножеству столбцов индекса может вернуть более одной строки. Это называется поиском по диапазону (range seek) или частичным сканированием (partial scan). В этом случае корневые и промежуточные страницы используются для поиска первой совпадающей строки, а затем оставшиеся совпадающие строки находятся с помощью указателей на следующую страницу, как при сканировании кластерного индекса.
Обратный поиск по ключу (Key Lookup) на самом деле — это просто поиск по кластерному индексу (Clustered Index Seek), выполняющий поиск одного элемента. Он называется Key Lookup, когда значения ключевых столбцов найдены в некластерном индексе той же таблицы. Но он делает то же самое.
Поскольку данные в кластерном индексе всегда (логически) упорядочены, изменения могут быть более дорогими. Большой объём вставок в конец индекса может вызвать горячую точку вставки (insert hot spot). И наоборот, вставки в случайные места индекса, а также обновления, которые вызывают рост строки, могут привести к разделению страниц (page splits): когда на странице нет места для новой строки, данные распределяются по двум страницам, и указатели на предыдущую и следующую страницы должны быть изменены, а указатель на новую страницу должен быть вставлен в соответствующую промежуточную или корневую страницу. А обновления, изменяющие значения ключа, приводят к перемещению строки: она будет удалена из своего исходного местоположения и вставлена в новое местоположение (с, конечно, риском разделения страницы).
Вообще говоря, наличие кластерного индекса в таблице почти всегда лучше, чем хранение данных в виде кучи. Хотя существует множество соображений относительно «идеального кластерного индекса», просто наличие любого кластерного индекса уже лучше, чем отсутствие кластерного индекса. Вот почему, если вы не переопределите параметры по умолчанию, SQL Server автоматически создаст кластерный индекс, когда вы объявите ограничение PRIMARY KEY или первое ограничение UNIQUE для таблицы, у которой ещё нет кластерного индекса.
Некластерный индекс
Любая таблица на диске в SQL Server может иметь практически неограниченное количество некластерных индексов.
В эквиваленте с книгой некластерный индекс похож на предметный указатель в конце книги. Если вы хотите найти информацию по конкретной теме, вы используете алфавитный порядок указателя, чтобы быстро найти ключевое слово. Чтобы найти фактическую информацию, вы затем используете перечисленные номера страниц, чтобы найти страницы, которые вам нужно прочитать.
Некластерные индексы хранятся так же, как и кластерные. То есть у нас снова есть структура B-дерева с корневыми, промежуточными и листовыми страницами. И указатели на следующую и предыдущую страницы, чтобы соединить листовые страницы в их логическом порядке. Единственное различие между кластерным индексом и некластерным индексом заключается в том, что именно хранится на этих листовых страницах.
В то время как кластерный индекс хранит данные таблицы (все столбцы), некластерный индекс хранит только подмножество столбцов. На каждой листовой странице вы найдёте: ключевые столбцы этого индекса, ключевые столбцы кластерного индекса (если есть), RID (если нет кластерного индекса) и любые столбцы, которые были добавлены в индекс с ключевым словом INCLUDE.
Поскольку структура некластерного индекса идентична структуре кластерного индекса, мы можем обращаться к ней теми же способами. Мы можем прочитать все или большую часть данных с помощью Index Scan (сканирование индекса), или мы можем найти конкретные строки с помощью Index Seek (поиск по индексу). Обратные поиски (lookups) для некластерных индексов не поддерживаются.
Сканирование индекса (Index Scan) логически совпадает со сканированием кластерного индекса. Но поскольку на листовых страницах меньше столбцов, больше строк помещается на каждой странице, что, в свою очередь, означает, что для сканирования индекса требуется прочитать меньше страниц. Это может быть огромным преимуществом, если индекс покрывает запрос (т.е. если все требуемые столбцы находятся на листовых страницах).
Поиск по индексу (Index Seek) фактически аналогичен поиску по кластерному индексу. Здесь уменьшенный размер листовых страниц вряд ли когда-либо даёт какое-либо преимущество в производительности. Но это по-прежнему имеет тот же недостаток — отсутствие всех необходимых столбцов. Это может потребовать в плане выполнения комбинации поиска по индексу (Index Seek) с обратным поиском по ключу (Key Lookup) или RID (RID Lookup), чтобы найти оставшиеся требуемые столбцы.
Вышеупомянутые соображения относительно изменений в кластерном индексе применимы и к некластерным индексам. Однако из-за обычно меньшего размера этих индексов разделение страниц и горячие точки, как правило, в большинстве случаев представляют меньшую проблему.
С другой стороны, каждый дополнительный некластерный индекс вводит дополнительную работу для движка при изменении данных. Вставленные и удалённые строки необходимо добавлять или удалять из каждого некластерного индекса. Обновлённые строки требуют обновления в любом некластерном индексе, который имеет один или несколько затронутых столбцов на своих листовых страницах. И эти обновления могут привести к перемещению записи индекса, если обновление изменяет один из индексированных столбцов.
Добавление некластерных индексов всегда является компромиссом между затратами и преимуществами. Индексы могут значительно повысить производительность запросов, которые осуществляют поиск или сортировку по индексированным столбцам, особенно если индекс покрывает запрос. Но слишком много некластерных индексов замедляет ваши изменения, увеличивает размер базы данных и, следовательно, время резервного копирования и восстановления, а также усилия по обслуживанию индексов, и увеличивает стоимость компиляции. Искусство настройки производительности во многом заключается в искусстве нахождения достаточно небольшого набора индексов, которые приносят пользу достаточно большому количеству запросов. В то время как длинный список включённых (INCLUDEd) столбцов может увеличить шансы найти индекс, покрывающий запрос, это также увеличивает размер этого индекса и количество изменений, которые его затрагивают.
Заключение
Классические индексы строк на диске — это стандартная и наиболее часто используемая структура хранения в SQL Server. Общая рекомендация — по умолчанию использовать кластерный индекс и использовать кучи только в очень специфических случаях, а затем добавлять достаточно небольшой набор некластерных индексов, чтобы помочь как можно большему количеству ваших релевантных запросов, не замедляя при этом изменения слишком сильно.
Планы выполнения в идеале должны использовать операторы сканирования, когда необходимо прочитать все или большую часть данных в таблице, или когда данные необходимо прочитать в порядке индекса. Для запросов, которые фильтруют по конкретным столбцам, операторы поиска по поддерживающему индексу могут быть отличными. Если фильтр достаточно избирательный, то обратный поиск (lookup) после поиска (seek) часто является более приемлемой ценой, чем бесконечный список включённых столбцов.
Комментариев нет:
Отправить комментарий