Использование индексов и выполнения сканирования таблицы являются двумя фундаментальными подходами к доступу к данным в базе данных, каждый из которых имеет различные компромиссы в зависимости от ситуации.
Индекс в базе данных - это структура данных, которая обеспечивает более быстрый поиск, позволяя системе быстро найти строки без сканирования каждой строки в таблице. Большинство реляционных баз данных используют структуры B+ дерева для индексов, которые организуют ключи и указатели в форме дерева. Это позволяет поиски, вставки и удаления в логарифмической сложности времени $$ o (\ log n) $$, что обычно намного быстрее, чем сканирование всей таблицы со сложностью $$ o (n) $$. Индексы могут быть кластеризованы или не кластеризованы, с кластерными индексами, хранящими данные в физически отсортированном порядке, улучшая производительность сканирования диапазона за счет дополнительных накладных расходов на модификации данных. Индексы также могут быть составными, частичными, фильтрованными или на основе хеш, настроенных на конкретные шаблоны запросов.
Напротив, сканирование таблицы (или полное сканирование таблицы) считывает каждую строку в таблице последовательно, независимо от селективности запроса. Это включает в себя сканирование всех блоков данных таблицы и часто считается наиболее дорогостоящим методом доступа, поскольку он обрабатывает больше данных, чем необходимо. Тем не менее, сканирование таблиц может хорошо работать в некоторых случаях. Например, когда запросы получают большой процент рядов, накладные расходы использования индекса (который часто требует дополнительных поисков для фактических строк) может превышать стоимость сканирования всей таблицы один раз. Таблицы могут использовать многоблоки, которые позволяют читать большие куски данных с меньшим количеством операций ввода-вывода, тем самым снижая задержку по сравнению с чтением многих отдельных блоков, случайным образом требуемым при индексном сканировании.
Один крупный компромисс включает селективность и размер набора данных, возвращаемого запросом. Если запрос фильтрует до небольшого количества строк (высокая селективность), индексы обычно превосходят сканирование таблицы, поскольку им необходимо только получить доступ к соответствующим данным. Но по мере увеличения процента возвращаемых строк стоимость сканирования индекса повышается, поскольку может потребоваться многочисленные поиски ключей, и двигатель базы данных должен выполнять дополнительные случайные операции ввода -вывода. При некотором пороге, часто около 10-20% рядов таблицы, но зависит от ширины и оборудования данных, полное сканирование таблицы становится более эффективным. Это связано с тем, что затраты на сканирование остаются постоянными независимо от селективности, просто прочитать таблицу последовательно один раз.
Индексные сканирования обычно читают меньше страниц, чем табличное сканирование, когда покрытые столбцы меньше или более компактны, чем строки полной таблицы. Например, индекс может включать только индексированные столбцы без полных данных строк таблицы, что делает его более тонким и позволяя большему количеству строк на каждой странице базы данных. Это уменьшает накладные расходы ввода/вывода при сканировании индекса по сравнению с сканированием всей таблицы данных. Кроме того, некоторые индексы могут быть отфильтрованы (частичные индексы), чтобы исключить нерелевантные строки, что еще больше уменьшило след сканирования.
С другой стороны, полные сканирования таблицы записывают меньше бремени на стороне обслуживания базы данных. Индексы вводят накладные расходы во время операций модификации данных, таких как вставка, обновление и удаление. Каждое изменение в таблице требует обновления индексов, иногда приводит к увеличению задержки записи и накладных расходов на хранение, особенно если в таблице существует много индексов. Эти накладные расходы также могут повлиять на параллелизм и привести к споре в условиях тяжелых записей. Таким образом, сканирование таблиц, которые просто считывают данные в его естественном порядке без дополнительного обслуживания структуры, избегайте этой стоимости.
Другим важным соображением является эффект кэширования и характеристик оборудования. Таблицы получают выгоду от последовательного ввода -вывода и предварительного получения, позволяя системе эффективно читать несколько смежных блоков, часто из памяти, если кэшируются. И наоборот, индексные сканирования несут случайный ввод -вывод, чтобы получить разнородные блоки данных, особенно если индексное сканирование должно искать указатели строк в хранение кучи. Это может привести к более медленному сканированию индекса на системах с более медленной производительности ввода/вывода, хотя SSD и большие пулы памяти сокращают этот разрыв. Ситуация также может зависеть от специфики, таких как параллелизм и многопоточные возможности двигателя базы данных, где параллельные таблицы могут значительно повысить пропускную способность.
Кроме того, внутренняя фрагментация и физическая компоновка хранения влияют на компромиссы производительности. Сканирование таблиц на таблицах, организованных в кучах, может пострадать от перенаправленных записей, где ряды перешли на разные страницы из-за обновлений, ухудшая эффективность сканирования. Кластерные индексы, которые хранят данные, отсортированные по ключам, могут избежать этой проблемы и иногда сделать «табличное сканирование» эквивалентным кластерному сканированию индекса. Тем не менее, преимущества связаны с стоимостью дорогостоящих изменений строк во время тяжелого излучения данных.
С точки зрения оптимизатора запросов, решение между индексным сканированием и сканированием таблицы обычно принимается с помощью моделей оценки на основе затрат с учетом статистики по распределению данных, количеству строк и затрат на оборудование. Оптимизатор уравновешивает процессор, ввода -вывод и затраты на память, чтобы выбрать наиболее эффективный путь доступа. На эти решения могут влиять такие факторы, как доступная память, состояние кэширования и модели запросов. Не существует фиксированного порога между тем, когда использовать один или другой; Точка кроссовера варьируется в соответствии с системой и рабочей нагрузкой.
Таким образом, компромиссы между использованием индексов и таблицы включают:
- Производительность против объема данных: индексы превосходят при фильтрации до нескольких строк; Таблицы могут быть лучше для крупного поиска данных.
- Паттерны ввода/вывода: индексное сканирование вызывает случайные чтения ввода/вывода; Таблицы получают выгоду от последовательных считываний ввода/вывода и мультиблока.
- Накладные расходы на техническое обслуживание: индексы увеличивают затраты на операцию на записи из -за обновлений индексных структур; Сканирование таблиц не несет этого.
- Эффективность хранения: индексы могут быть компактны, покрывая меньше столбцов; Таблицы обрабатывают полные строки и потенциально больше данных.
- Эффекты кэширования: таблицы могут эффективно использовать кэширование данных, особенно с большими последовательными считываниями; Индексное сканирование может не принести пользу так сильно из -за случайного доступа.
- Оптимизатор Решение: Оптимизаторы запросов на основе затрат Динамически выбирают между этими параметрами на основе статистики запросов и специфики рабочей нагрузки.
- Влияние макета данных: таблицы кучи могут нести штрафы, такие как перенаправленные записи во время сканирования; Кластерные индексы организуют данные физически, но увеличивают затраты на обновление.